Main menu

Media Coverage

<div class="small-4 columns">media 1</div>

<div class="small-4 columns">media 2</div>

<div class="small-4 columns">media 3</div>

Reach to Us

SQL

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

ma'am pls explain 4th point 

shivani1234's picture

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

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

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

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