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
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.
No comments:
Post a Comment