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;