Complete Format of SELECT Command

Complete Format of SELECT Command is

SELECT       [DISTINCT]<attribute or function list>
FROM                   <table list>
[WHERE                 <Condition (s)>]
[GROUP BY              <grouping attributes (s)>]
[HAVING                <group condition>]
[ORDER BY              <attribute list>[DESC/ASC]
Mandatory Clauses In SQL :

Only the first two clauses SELECT & FROM are mandatory. Other clauses enclosed between square brackets[…] are optional.

Translating SQL into Relational Algebra : CLICK for Relational Algebra
  • From ≅ × (Cross Product)
  • Select ≅ π (Projection)
  • Where ≅ σ (Condition)
  • Example :
    Complete Format of SELECT Command and Relational Algebra
Order Of Execution in Relational Algebra:
  1. × : Cross Product i.e. FROM
  2. σ : Condition i.e. WHERE
  3. π : Projection i.e. SELECT
Order Of Execution in SQL:
  1. FROM
  2. WHERE
  3. GROUP BY
  4. SELECT

Consider the Following Relation :

Employees
Eno Fname Lname Address City
1000 Ankit Mittal MC Colony Bhiwani
1001 Pooja Garg JainChowk Bhiwani
1002 Komal Gupta Babra town Rohtak
1003 Surender Jain Model Town Rohtak

Distinct keyword:

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you may want to listen on a different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct different values.

Example  : SELECT DISTINCT City FROM Employees;

Solution : The result will be 'BHIWANI' and 'ROHTAK'.

The WHERE Clause:

The WHERE Clause is used to accept only those records that fulfill the specified criteria.

Example  : SELECT Fname FROM Employees WHERE City = 'Bhiwani';

Solution : 'Ankit' and 'Pooja'
Operators Allowed in WHERE Clause
Operator Description
= Equal
!= Not Equal
> Greater Than
< Less Than
>= Greater Than Equal To
<= Less Than Equal To
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
AND Operators Displays record if Both the Conditions are TRUE.
OR Operator Displays record if Either a Condition is TRUE.

The ORDER BY Clause

The ORDER BY keyword is used to sort the result set alone are specified column. The result of query can be ordered  either in ascending order or in descending order. The ORDER BY keyword sorts the records in ascending order by default. If you want to show the records in a descending order, you can use the DESC keyword.
It is applied on Independent columns but not on group of columns.

Example  : SELECT Eno FROM Employees
           ORDER BY Lname ASC;
 
Solution : The Result will be 'Garg', 'Gupta', 'Jain', 'Mittal'.
Example of Some Queries that Use ORDER BY Clause

Consider the relation Student(Rollno, Name, Age, Marks,BranchID,Branch).

  • Query 1 : Show the student details order by name
  • SELECT * FROM Student 
    ORDER BY Name;
  • SELECT Rollno, Name, Age FROM Student    //Doesn't Produce any error
    WHERE age>15 ORDER BY 1,2;              //But applied on Independent 
                                               columns but not the group
  • SELECT Rollno, Name, Age FROM STUDENT    //Doesn't Produce any error
    where Age>10                            //But applied on Independent 
    ORDER BY Rolno DESC, Name ASC.          columns but not on the group
  • Query 2: Find the maximum marks of students having rollno 101,110,115.
  • SELECT Rollno, MAX(Marks) FROM Student 
    GROUP BY Rollno 
    HAVING Rollno IN (101,110,115);
  • Query 3: Find the total number of students in a branch order by their Roll Numbers?
  • The following Query is written in Wrong Form :
    SELECT BranchID, COUNT(Rollno) FROM Student   ×
    GROUP BY BranchID 
    ORDER BY COUNT(Rollno)                         
    
    The Correct Form of the Above Query is :
    SELECT BranchID, COUNT(Rollno) a FROM Student √
    GROUP BY BranchID 
    ORDER BY a;
    
    Result : We cannot use aggregate functions in ORDER BY Clause.
  • Query 4 : Find total strength of each branch and display the details of branches where the total number of students is more than 45 and display them in ascending order.
  • Wrong Query :
    SELECT BranchID, COUNT(Rollno) a FROM STUDENT  ×
    GROUP BY BranchID 
    HAVING a>45 
    ORDER BY a;
    
    Correct Query :
    SELECT BranchID, COUNT(Rollno) a FROM STUDENT  √
    GROUP BY BranchID 
    HAVING COUNT(Rollno)>45 
    ORDER BY a;
    
    Result : The main motive of this query is to tell you that we cannot use aliases in HAVING Clause.

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. It allow one to partition the result into a number of groups that hold rows of a group have the same value in some specified column.
Whenever group by is used the phrase where is to be replaced by having. The meaning of having the same as where except that the condition is new applicable to each group.

Example : Consider the following Orders table :
Orders
OID OrderDate OrderPrice Customer
1 2015/08/24 1000 Ankit
2 2015/07/25 1600 Pooja
3 2015/06/24 700 Ankit
4 2015/05/24 300 Ankit
5 2015/12/24 2000 Surender
6 2015/7/24 100 Pooja
Query : SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer ;

Solution :
Customer OrderPrice
Ankit 2000
Pooja 1700
Surender 2000
Total number of groups = 3

Some Examples of Queries in which GROUP BY Clause is used :

Consider the relation Student(Rollno, Name, Age, Marks,BranchID,Branch).

  • Query 1 : Find the student details who got second maximum or third maximum or in general nth maximum marks ?
  • Query 2 :Find all the total number of students in each and every branch?
  • SELECT BranchID, COUNT(Rollno) FROM Student 
    GROUP BY BranchID;

The HAVING clause

  • The HAVING clause was added to SQL because the where keyword could not be used with aggregate functions.
  • It exists only with GROUP Clause.
Example 1: Consider the table Orders(OID, OrderDate, OrderPrice, Customer)

Query    : SELECT Customer, SUM (OrderPrice) 
           From Orders 
           GROUP BY Customer 
           HAVING SUM(OrderPrice) <2000;

Solution : The result will be : 'Pooja' and 2000.

Example 2: Retrieve students whose branch average marks is greater than equal to 65.

Query    : SELECT * 
           FROM Student
           GROUP BY (Branch)
           HAVING AVG(Marks)>65;

Solution : It calculates average of the groups and then prints all the attributes of the table for the group whose AVG(Marks) ≥ 65.

Restriction for GROUP BY :

  • If SELECT list consists of non-aggregate column with an aggregate function, then the query associated with the GROUP BY clause must consists of non-aggregate columns in the group by list.
  • For example :
    
    Wrong Form of Query :
    SELECT Age,BranchID, COUNT(Rollno) FROM Student   ×
    GROUP BY COUNT(Rollno);
    
    Wrong Form of Query :
    SELECT Age,BranchID, COUNT(Rollno) a FROM Student
    GROUP BY a;
    
    Correct Form of Query :
    SELECT Age,BranchID, COUNT(Rollno) a FROM Student
    GROUP BY BranchID;
  • Whenever we apply GROUP BY, aggregation operator must be there.
  • We cannot use aggregate function in HAVING Clause rather than GROUP BY clause.
  • Example  : Find out the branch detail whose total strength is more
               than 50 students?
    Query :    SELECT BranchID, COUNT(Rollno) FROM Student  ×
               GROUP BY BranchID                            
               HAVING COUNT(Rollno)>50.                      
    
    This Query is WRONG due to an aggregation operator in SELECT clause [COUNT(Rollno)].
  • Group by is applied on Independent rows. Having is applied among groups.
  • Example  :
    Query    : Find the details of student who paid more than 20 Rupees
               as a total fine?
    Solution : SELECT Rollno, SUM(Fine) FROM Library 
               GROUP BY Rollno 
               HAVING SUM(Fine)>20;
  • We can use WHERE with GROUP BY as well.
  • SELECT Branch, MAX(Marks)
    FROM Student
    WHERE marks>50
    GROUP BY(BRANCH);
    
    What the query says is :
    On the result of WHERE Clause, if any grouping is possible, then it is performed.

Difference Between WHERE Clause and HAVING clause

WHERE Clause HAVING Clause
It is used to filter rows i.e. at tuple level. It is used to filter groups i.e. checks a condition for a Group.
Aggregate functions cannot be used. Aggregate functions can be used.
It is mandatory i.e. There is no alternative for WHERE Clause. It is optional i.e. the results given by HAVING Clause can also be applied without it.

Previous Home Next
Primary Key and Foreign Key Integrity Constraint Query and Subquery in SQL

     

Incoming search terms:

  • DBMS roll no Details

Leave a Reply