SQL Server Functions

SELECT USER_NAME();
SELECT SYSTEM_USER;
SELECT SESSION_USER;
SELECT CURRENT_USER;
SELECT PATINDEX('%schools%', 'W3Schools.com');
SELECT QUOTENAME('abcdef');
SELECT QUOTENAME('abcdef', '()');
SELECT REPLICATE('SQL', 5);
SELECT REVERSE('SQL Tutorial');
SELECT STR(65);
SELECT ASCII('Ajeet');
SELECT CHAR(65);
SELECT UPPER('Ajeet');
SELECT LOWER('Ajeet');
SELECT SUBSTRING('SQL Tutorial', 5, 5) AS ExtractString;

SQL- Query

EXAMPLE – 01 SELECT ALL FIELDS USING WILDCARD * (MENTALLY READ * AS ‘ALL FIELDS’)

SELECT *

FROM Mobiles;

The merits/demerits of wildcard * are as follow:

1.       It works even if the field names are changed

2.       It works even if any field is deleted

3.       It works even if a new field is added

EXAMPLE – 02 SELECT ALL FIELDS BY MENTIONING THEM

SELECT ID, Region, Brand, Color, Units, Price

FROM Mobiles;

EXAMPLE – 03 SELECT ONLY DESIRED FIELDS

SELECT Region, Brand, Units, Price

FROM Mobiles;

EXAMPLE – 04 REORDER THE FIELDS

SELECT Brand, Color, Region, Price, Units

FROM Mobiles;

EXAMPLE – 05 NOT DISTINCT VALUES OF FIELD WITHOUT DISTINCT KEYWORD

SELECT Region

FROM Mobiles;

EXAMPLE – 06 SORT FIELD USING ORDER BY CLAUSE

SELECT Region

FROM Mobiles

ORDER BY Region;

EXAMPLE – 07 DISTINCT VALUES OF FIELD

SELECT DISTINCT Region

FROM Mobiles

ORDER BY Region;

EXAMPLE – 08 DISTINCT RECORDS USING DISTINCTROW KEYWORD

SELECT DISTINCTROW Region

FROM Mobiles

ORDER BY Region;

EXAMPLE – 09 REPEATING THE FIELDS DOES NOT THROW ERROR

SELECT Brand, Brand, Region, Price, Units, Units

FROM Mobiles;

REMARKS: The output will contain another label for duplicate fields.

EXAMPLE – 10 CALCULATED FIELDS

SELECT Brand, Region, Price, Units, Price*Units

FROM Mobiles;

EXAMPLE – 11 MAKING SENSE BY RELABELING FIELDS USING AS KEYWORD

SELECT Brand, Region As Area, Price, Units, Price*Units As Revenue

FROM Mobiles;

EXAMPLE – 12 RELABELING FIELD WITH SPACE BY WRITING INSIDE []

SELECT Brand, Region, Price, Units, Price*Units As [Total Revenue]

FROM Mobiles;

EXAMPLE – 13 CONSTANT OR EXPRESSION AS FIELDS

SELECT Brand, Region, Price, Units,  2016 AS YEAR, Price*1.2 As [Price After Rise by 20%]

FROM Mobiles;

EXAMPLE – 14 RENAME FIELD TO AVOID CIRCULAR REFERENCE

SELECT Brand, Region, Price*1.2 AS PRICE, Units

FROM Mobiles;

Note: The above query throws error as shown below:

SELECT Brand, Region, Price*1.2 AS NEWPRICE, Units

FROM Mobiles;

WHERE CLAUSE TO SUBSET RECORDS

Filter recordset using various comparison operators such as =, >, >=,<,<=,<>, like, in, is null, is not null, between and etc.

EXAMPLE – 15 SUBSETTING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region, Price, Units, Price*Units As [Total Revenue]

FROM Mobiles

WHERE Region='South';

REMARK: If you copy-paste code in Access SQL code window, note that the quote character is correct. If the quote-character is not proper, it will throw error as shown below. In fact, it is not an error but the query is converted into parametric query. The ‘South’ becomes a parameter or variable which value is assigned to Region field when user enters a value:

EXAMPLE – 16 FILTERING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region, Price, Units, Price*Units As [Total Revenue]

FROM Mobiles

WHERE Region=’south’;

EXAMPLE – 17 FILTERING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region, Price, Units, Price*Units As [Total Revenue]

FROM Mobiles

WHERE Region=’SOUTH’;

EXAMPLE – 18 PARAMETRIC QUERY

SELECT Brand, Region, Price, Units, Price*Units As [Total Revenue]

FROM Mobiles

WHERE Region= [Enter the Region];

EXAMPLE – 19 FILTERING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE Region=’South’ OR Region=’WEST’ OR Region=’NORTH’;

EXAMPLE – 20 FILTERING RECORDSET USING IN OPERATOR

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE Region IN (’South’,’NORTH’,’WEST’);

EXAMPLE – 21 FILTERING RECORDSET USING IN OPERATOR

SELECT Region, Brand, Price, Units

FROM Mobiles

WHERE Region IN (’South’,’NORTH’,’WEST’);

EXAMPLE – 22 FILTERING RECORDSET USING COMPARISON OPERATOR =

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE Price = 20000;

EXAMPLE – 23 FILTERING RECORDSET USING COMPARISON OPERATOR <

SELECT Brand, Region, Price, Units, Price*Units As [Total Revenue]

FROM Mobiles

WHERE Price < 20000;

EXAMPLE – 24 FILTERING RECORDSET USING COMPARISON OPERATOR <=

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE Price <= 20000;

EXAMPLE – 25 FILTERING RECORDSET USING COMPARISON OPERATOR >=

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE Price >= 20000;

EXAMPLE – 26 FILTERING RECORDSET USING COMPARISON OPERATOR <>

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE Price <> 20000;

EXAMPLE – 27 FILTERING RECORDSET USING BETWEEN AND OPERATOR

SELECT Brand, Region, Price, Units, Price*Units As [Total Revenue]

FROM Mobiles

WHERE Price BETWEEN 20000 AND 30000;

EXAMPLE – 28 FILTERING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE COLOR IS NULL;

EXAMPLE – 29 FILTERING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region, Price, Units

FROM Mobiles

WHERE COLOR IS NOT NULL;

EXAMPLE – 30 FILTERING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region,Color, Price, Units

FROM Mobiles

WHERE COLOR LIKE "Bl*"

EXAMPLE – 31 FILTERING RECORDSET USING WHERE CLAUSE

SELECT Brand, Region, Color, Price, Units

FROM Mobiles

WHERE COLOR NOT LIKE "Bl*"

EXAMPLE – 32 FILTERING RECORDSET USING WHERE CLAUSE

Brand name begins with either D or E or H

SELECT Brand, Region, Color, Price, Units

FROM Mobiles

WHERE Brand LIKE "[d-h]*"

UNDERSTANDING GROUP BY CLAUSE

SELECT Region, Brand, Color

FROM Mobiles

GROUP BY Region, Brand;

The question is why does the above query throw error?

Answer: When the records are grouped using Region and Brand, the individual existence of Color is lost. For example, if the Mobile table has 100 records and after grouping using Region and Brand, the records are reduced to 70 (say), the individual 100 values of Color can no longer exists. Either we get the aggregated result of color and in this case we must include the COLOR field also in the GROUP BY CLAUSE or we should apply some aggregate function on COLOR if COLOR is not included in the GROUP BY clause. As a rule, whenever you include a column in table, it must exist in the GROUP BY clause or it must be argument of any aggregate function. This is shown in below query:

SELECT Region, Brand, Count(Color) As [Color Count]

FROM Mobiles

GROUP BY Region, Brand;

ANOTHER EXAMPLE

SELECT *

FROM Mobiles

GROUP BY Region, Brand;

Even if you include all fields in GROUP BY Clause, it will throw error. The explanation is as given above.

SELECT *

FROM Mobiles

GROUP BY ID, Region, Brand, Color, Price, units;

When GROUP BY clause is used, SELECT clause should have an aggregate function or one or more GROUP BY variables or a combination of both. For example,

SELECT Count(*) As [Region Wise Record Counts]

FROM Mobiles

GROUP BY Region;

You can also write

SELECT Region, Count(*) As [Region Wise Record Counts]

FROM Mobiles

GROUP BY Region;

You can apply projection on grouped records:

SELECT Region

FROM Mobiles

GROUP BY Region, Brand;

But below is incorrect:

SELECT Color

FROM Mobiles

GROUP BY Region, Brand;

But below is CORRECT:

SELECT COUNT(Color)

FROM Mobiles

GROUP BY Region, Brand;

SELECT Variable must be part of GROUP BY variable. The following query will return error:

SELECT Region

FROM Mobiles

GROUP BY Brand, Color;

The corrected query is

SELECT Region

FROM Mobiles

GROUP BY Region, Brand, Color;

The following query has error:

SELECT count(*) "Total Records"

FROM Mobiles;

The corrected query is

SELECT count(*) As "Total Records"

FROM Mobiles;

To avoid double quotes in label, modify the query as follows:

SELECT count(*) as [Total Records]

FROM Mobiles;

The same output is possible using following query as well:

SELECT count([ID]) as [Total Records]

FROM Mobiles;

To find out the duplicate records

SELECT Region, Brand, Color, Units, Price

FROM Mobiles

GROUP BY Region, Brand, Color, Units, Price

HAVING COUNT(*)>1;

SELECT Mobiles.Units, [Units]/(SELECT Sum(Units) FROM Mobiles) AS [% Units]

FROM Mobiles;

SELECT Mobiles.Units, [Price]*[Units] As [Calculated Revenue]

FROM Mobiles;

SELECT SUM( [Price]*[Units] ) As [Sum of Calculated Revenue]

FROM Mobiles;

Single Level Grouping:

SELECT Region, SUM( [Price]*[Units] ) As [Region Wise Revenue]

FROM Mobiles

GROUP BY Region;

Multi Level Grouping:

SELECT Region, Brand, SUM( [Price]*[Units] ) As [Region Brand Wise Revenue]

FROM Mobiles

GROUP BY Region, Brand;

The following is correct query but not meaningful report is generated.

SELECT SUM( [Price]*[Units] ) As [Region Brand Wise Revenue]

FROM Mobiles

GROUP BY Region, Brand;

TOP N QUERIES

To find out the top 5 sales revenue(Incorrect):

SELECT TOP 5 Region, Brand , SUM( [Price]*[Units] ) As [Top Revenue]

FROM Mobiles

GROUP BY Region, Brand;

To find out the top 5 sales revenue(Correct):

SELECT TOP 5 Region, Brand , SUM( [Price]*[Units] ) As [Top Revenue]

FROM Mobiles

GROUP BY Region, Brand

ORDER BY SUM( [Price]*[Units] ) DESC;

DOMAIN FUNCTIONS

1.       Find total records in Mobiles table

SELECT DCOUNT("[ID]","MOBILES") AS [TOTAL RECORDS]

2.       Find total records of ACER brand mobiles in Mobiles table

SELECT DLOOKUP("[ID]","MOBILES","[BRAND]='ACER'")

3.       Find brand name which ID is 77 in Mobiles table

SELECT DLOOKUP("[BRAND]","MOBILES","[ID]=77")

4.       Find average price of mobiles of all brands

SELECT DAVG("[PRICE]","MOBILES")

5.       Find average price of mobiles of ACER brand

SELECT DAVG("[PRICE]","MOBILES", "[BRAND]='ACER'")

6.       Find total price of mobiles of ACER brand

SELECT DSUM("[PRICE]","MOBILES", "[BRAND]='ACER'")

7.       Find total revenue from mobiles of ACER brand SELECT DSum("[PRICE]*[UNITS]","MOBILES","[BRAND]='ACER'") AS [ACER TOTAL REVENUE];

8.       Find minimum price of Acer mobiles

SELECT DMIN("[PRICE]","MOBILES", "[BRAND]='ACER'")

9.       Find maximum price of Acer mobiles

SELECT DMAX("[PRICE]","MOBILES", "[BRAND]='ACER'")

10.   Find record ID of Acer mobiles which appears first time in the table

SELECT DFIRST("[ID]","MOBILES", "[BRAND]='ACER'")

11.   Find record ID of Acer mobiles which appears last time in the table

SELECT DLAST("[ID]","MOBILES", "[BRAND]='ACER'")

12.   Find variance of units sold of Acer mobiles

SELECT DVAR("[UNITS]","MOBILES", "[BRAND]='ACER'")

13.   Find number of units sold of Acer mobiles which are sold at more than 30000

SELECT DCOUNT("[ID]","MOBILES", "[PRICE]>30000")

14.   Find number of units sold of Acer mobiles which are sold between 30000 and 40000

SELECT DCOUNT("[ID]","MOBILES", "[PRICE] BETWEEN 30000 AND 40000")

15.   SELECT DCOUNT("[SALE]","TABLE1","[DATES]>#1/1/2016#") AS [TOTAL SALES AFTER JAN1]

Remarks: Note that all the Domain functions arguments are written inside quotes.

SQL Basic Part-2

Some facts about Access queries:

• When table is opened in Design view, you cannot execute query related to the table.
• SQL Queries are NOT case sensitive.
• SQL queries can be written in free form way.
• SQL queries are terminated by semi-colon; but in MS-Access we need not have to do it.
• MS-Access provides its propriety clause TRANSFORM to create Two-Way table. It is not part of ANSI SQL.
• Domain function arguments are strings and hence they must be written inside double quotes.
• We should write Fields inside square brackets [] in QBE (Query-By-Example window), else errors may be generated. The reason is that the field without brackets is converted into strings surrounded by double quotes.
• To create field alias, we use AS keyword before the alias name.
• The alias should not be used in WHERE or HAVING clause etc because projection operation is done at last before the sorting operation while sub-setting operations occur before.
• The sorting operation used by help of ORDER BY clause is not part of SQL theory propounded by E.F. Codd. The reason is that ORDER BY operation on relation does not lead to any new relation. Simply the tuples are re-ordered. No new set is generated.
• In Access, TRUE and FALSE logical values are equivalent to -1 and 0. To convert -1 into 1, use ABS function.

SQL- Basic Part-1

By Ajeet Kumar

The first important step in SQL is to know these two basic points:

1.     In SQL, we write the SQL clauses in following order:

SELECTàFROMàWHEREàGROUP BYàHAVINGàORDER BY

But the logical/ internal processing order of SQL query is as follows:

FROMàWHEREàGROUP BYàHAVINGàSELECTàORDER BY

2.     In SQL, the operand is a table or query. It means that operation is performed on recordset and not on a record. Records of a table are technically called Tuples.

Table	Row	Column
Relation	Tuple	Attribute
Recordset	Record	Field

The SQL operations on record-set are as follows:

·         Projection π is all about restricting one or more columns of a table.

·         Selection σ is all about filtering the records using some criteria.

·         Join is all about joining two or more tables which need not be distinct.

·         Union is all about vertically merging the records of two or more tables which have common fields with compatible data types.

·         And some others…

PROJECTION OPERATION π

This operation returns a vertical subset of relation. It is used either to reduce the attributes of relation or reorder them.

The relation is called Table and attributes are its columns or fields.

SELECT CLAUSE is used for projection operation on a record-set [TABLE/QUERY]. The projection operation is used to decide which fields should be part of the record-set.

SELECT VS. SELECT DISTINCT

Suppose we want to retrieve the data values of Region field of Mobiles table. To perform this task, the SQL query will be as follows: SELECT Region FROM Mobiles; This query will return 100 data values from the Region Field as 100 records exist in the Mobiles table. But our aim is to know only the distinct regions where the mobiles were sold. For this our query will be SELECT DISTINCT Region FROM Mobiles; the DISTINCT keyword is used to remove the duplicate values of the Region field. We can also do the same using GROUP BY clause without using DISTINCT keyword. SELECT Region FROM Mobiles GROUP BY Region; thus, there can be more than one way to do a task in SQL. In both ways, we retrieve 5 records.  We can do multilevel grouping as well. This is illustrated below:

SELECT Region, Brand FROM Mobiles;

We get 100 data values for Region-Brand combination. 

To get distinct values, we use:

SELECT DISTINCT Region FROM Mobiles;

We get 23 distinct data values for Region-Brand combination.

SELECT Region, Brand FROM Mobiles GROUP BY Region, Brand;

It does the same. We get 23 distinct data values for Region-Brand combination.

SINGLE Vs. MULTI LEVEL SORTING

Sorting Fields: To sort fields of a table, we use ORDER BY clause in SQL.

Single Level Sorting: In this case, only one field is sorted using ORDER BY clause. This is illustrated below:

• 1.       SELECT DISTINCT Region FROM Mobiles ORDER BY Region;
• 2.       SELECT DISTINCT Region FROM Mobiles ORDER BY Region ASC;
• 3.       SELECT DISTINCT Region FROM Mobiles ORDER BY Region DESC;
• 4.       SELECT DISTINCT Region, Brand FROM Mobiles ORDER BY Region;
• 5.       SELECT DISTINCT Region, Brand FROM Mobiles ORDER By Brand;

The 1^st query sorts the result set in ascending order on Region field. The same is achieved in the 2^nd one by explicitly mentioning the ASC keyword after the field on which sort is applied. In 3^rd one query, data is sorted in descending order on Region field by using DESC keyword.

In case of multi-level sorting we use multiple fields in the ORDER BY clause along with required keyword DESC and ASC. 

For example:

• 1.       SELECT DISTINCT Region, Brand FROM Mobiles ORDER BY Region DESC, Brand;
• 2.       SELECT DISTINCT Region, Brand FROM Mobiles ORDER BY Region DESC, Brand DESC;

REMARK:

We can achieve the same result using GROUP BY clause. We retrieve the same 23 records.

SELECT Region, Brand

FROM Mobiles

GROUP BY Region, Brand

ORDER BY Region DESC, Brand;