Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Monday, June 8, 2020

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;

Friday, June 7, 2019

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 1st query sorts the result set in ascending order on Region field. The same is achieved in the 2nd one by explicitly mentioning the ASC keyword after the field on which sort is applied. In 3rd 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;

Hot Topics