‘SQL, BI, IT news’ Archives
Correlated subquery
Correlated subquery (also known as a repeating subquery) is a subquery where selection criterion of inner query refers to values in the outer query. One of the restriction is that inner query cannot return more than one row matching the outer row; Example; USE pubs SELECT au_lname, au_fname FROM authors WHERE 100 IN (SELECT royaltyper [...]
IF Then Else – SQL
This post is about if then else statement examples using PUBS database. Examples A. Use one IF...ELSE block This example shows an IF condition with a statement block. If the average price of the title is not less than $15, it prints the text: Average title price is more than $15. USE pubs IF (SELECT AVG(price) FROM titles WHERE type = [...]
Pivot Tables In Excel
we are presenting an example of Data of North Wind Traders 'sales by category' tabel exported to Excel and then utilizing a Pivot tabel tool which enables us to display summarized data in just about any way possible. Data for pivot table comes from database, table or query. We experianced that sometimes reporting in Reporting tools become [...]
SQL Language Cross-Tab query
The query below is very useful for making for making Crystal reports in cross-tab format. We actually utilized the SQL query below for creating Crystal reports. We found this query more flexible than cross-tab query through wizard in Crystal reports. However it greatly depends on what is actually being accomplished. Simple Cross-tab query in [...]
Data Scrubbing / Cleansing
Data scrubbing also called data cleansing, is the process of amending or removing incorrect data in a database. Sometimes data incomplete, improperly formatted like postal code should be in this format "N9B 1W7" not in “N9B1W7"or first name and last name in 1 field, instead separate fields or duplicated. Sometimes zip codes are missing or in [...]
Difference between DMX and MDX
DMX stands for Data Mining Extensions. The DMX is similar to SQL language and contains statements for data defination and data manipulation. the data defination language includes statements for model creation and data manipulation language contains statements for training the model which includes inserting data into the model and retrieving [...]
Difference between MDX and SQL Language
MDX is language that allows you to query multidimensional databases where as SQL is query language for Relational databases. MDX extract information from Analyses services cubes or dimensions. SQL data is along rows and column, where as MDX data is along multiple axes.
Using Derived tables
In Sql language this is very common to use views instead of tables directly, for example we can create view; View_MarketingEmployees ; CREATE VIEW View_MarketingEmployees AS SELECT * FROM Employee WHERE department = "marketing" GO Then If we wanted to see all of the Employees from Marketing with the First name John, ordered [...]
