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
Order Of Execution in Relational Algebra:
- × : Cross Product i.e. FROM
- σ : Condition i.e. WHERE
- π : Projection i.e. SELECT
Order Of Execution in SQL:
- GROUP BY
Consider the Following Relation :
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
|>=||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 :
Query : SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer ; Solution :
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.|
|Primary Key and Foreign Key Integrity Constraint||Query and Subquery in SQL|
Incoming search terms:
- nine operators allowed in the WHERE clause