Important rules for GroupBy, Aggregate functions, OrderBy, Use of Null values, DISTINCT and PROJECT

Certain rules in SQl:

  • Attributes present in GroupBy clause must be present in SELECT statement.
  • Aggregate functions are : min, max , avg, sum , count , count(*).
  • All aggregate functions except count(*) ignore NULL values.
  • SELECT , FROM, WHERE, GROUPBY , HAVING is the syntactical sequence followed in SQL where the logical sequence is SELECT, FROM,WHERE,GROUP BY,HAVING and then ORDER BY if it is present.
  • SQL doesn't remove duplicates unless specified by keyword 'DISTINCT'.
  • For not equal to we use '<>' comparator.
  • For comparison with NULL values , we use keyword 'IS NULL'.
  • By default ORDER BY is considered ASCENDING , unless specified by DESC for descending.
  • SELECT DISTINCT in SQL is same as PROJECT in Relational Algebra.

Contributor's Info

Created: Edited:
5Comments
set2018 @setgate 14 Sep 2017 08:49 am

ma'am pls explain 4th point 

shivani @shivani1234 14 Sep 2017 09:03 am

it means if any changes to this logical sequence then it will give error.

set2018 @setgate 16 Sep 2017 09:53 am
The employee information in a company is stored in the relation

Employee (name, sex, salary, deptName)
Consider the following SQL query

Select deptName
From Employee
Where sex = ‘M’
Group by deptName
Having avg(salary) >
(select avg (salary) from Employee)
It returns the names of the department in which

the average salary is more than the average salary in the company

the average salary of male employees is more than the average salary of all male employees in the company

the average salary of male male employees is more than the average salary of employees in same the department

the average salary of male employees is more than the average salary in the company

set2018 @setgate 16 Sep 2017 09:54 am

MA'AM pls explain how to apply ordering in this example

Vishal Rawat @vishalrawat 16 Sep 2017 12:35 pm

I think logical sequence will be FROM, WHERE, GROUP BY, HAVING, ORDER BY(if present) and SELECT