ORDER BY Clause

• The output of SELECT queries do not have any specific order. The ORDER
BY clause allows us to specify order for the query output.
• ORDER BY clause is used with SELECT statement for arranging retrieved
data in sorted order.
• The ORDER BY clause by default sorts the retrieved data in ascending
order [ASC].
• To sort the data in descending order, DESC keyword is used with ORDER
BY clause.
Syntax:
SELECT column_list
FROM table_name
[WHERE conditions ]
ORDER BY column1, column2…….column N [ASC | DESC]
 ORDER BY Clause

Table: Emp
 ORDER BY Clause

Query 1: To display Emp table in ascending order by salary.

SELECT *
FROM Emp
ORDER BY Salary;
Query 2: To sort details of Emp table in ascending order by Name and Salary.
SELECT *
FROM Emp
ORDER BY Name, Salary;
Note: in ORDER BY clause multiple column can be used.
In this case
1st column is used as primary ordering field
2nd column is used as secondary ordering field.
 ORDER BY Clause

Query 3: To sort Emp table in descending order by Name.

SELECT *
FROM Emp
ORDER BY Name DESC;
 SQL Joins

In DBMS, a join statement is mainly used to combine two tables based on a

specified column field between them.
Join = cross product + condition ( or select statement)

JOINS can be divided into two type

• Inner Join
• Outer join
Inner Join: Inner join is a join that can be used to return all the values that
have matching values in both the tables.
 NATURAL Joins

Natural join is an inner join that returns the values of two tables on the basis
of a common attribute that has the same name and domain.
Table: Emp Table: Dept
E_no E_name Address D_no D_name E_no
1 Ram Delhi D1 HR 1
2 Varun Chd D2 IT 2
3 Ravi Chd D3 MKT 4
4 Amrit Delhi
 NATURAL Joins

Query: Find the employee name who is working in a department.

SELECT E_name
FROM Emp, Dept
WHERE Emp.E_no = Dept. E_no ;

OR
SELECT E_name
FROM Emp NATURAL JOIN Dept;
 EQUI Join

Equi join is an inner join that uses the equivalence condition for fetching the
value of two tables
Query: Find the employee name who work in a department having the
location same as their address.
Table: Emp Table: Dept

E_no E_name Address D_no Location E_no

1 Ram Delhi D1 Delhi 1
2 Varun Chd D2 Pune 2
3 Ravi Chd D3 Patna 4
4 Amrit Delhi
 EQUI Join

Table: Emp X Dept

E_no E_name Address D_no Location E_no
1 Ram Delhi D1 Delhi 1
1 Ram Delhi D2 Pune 2
1 Ram Delhi D3 Patna 4
2 Varun Chd D1 Delhi 1
2 Varun Chd D2 Pune 2
2 Varun Chd D3 Patna 4
3 Ravi Chd D1 Delhi 1
3 Ravi Chd D2 Pune 2
3 Ravi Chd D3 Patna 4
4 Amrit Delhi D1 Delhi 1
4 Amrit Delhi D2 Pune 2
4 Amrit Delhi D3 Patna 4
 EQUI Join

SELECT Emp.E_name
FROM Emp, Dept
WHERE (Emp. E_no = Dept. E.no) AND ( Emp.Address = Dept. Location);

E_no E_name Address D_no Location E_no

1 Ram Delhi D1 Delhi 1

Final Output

E_name
Ram
 Left Outer Join

It gives the matching rows and the rows which are in the left table but not in
the right table.
Table: Emp Table: Dept
E_no E_name D_no D_no D_name Location
E1 Varun D1 D1 IT Delhi
E2 Amrit D2 D2 HR Hyd
E3 Ravi D3 D3 Finance Pune
E4 Nitin NULL

SELECT Emp.E_no, Emp. E_name, Dept. D_name, Dept. Location

FROM Emp LEFT OUTER JOIN Dept
ON ( Emp. D_no = Dept. D_no);
 Left Outer Join

SELECT Emp.E_no, Emp. E_name, Dept. D_name, Dept. Location

FROM Emp LEFT OUTER JOIN Dept Table: Dept
ON ( Emp. D_no = Dept. D_no); D_no D_name Location
Table: Emp D1 IT Delhi
E_no E_name D_no D2 HR Hyd
E1 Varun D1 D3 Finance Pune

E2 Amrit D2
Output
E3 Ravi D1
E4 Nitin NULL E_no E_name D_name Location
E1 Varun IT Delhi
E2 Amrit HR Hyd
E3 Ravi IT Delhi
E4 Nitin NULL NULL
 Right Outer Join

It gives the matching rows and the rows which are in right table but not in the
left table.
Table: Emp Table: Dept

E_no E_name D_no D_no D_name Location

E1 Varun D1 D1 IT Delhi
E2 Amrit D2
D2 HR Hyd
E3 Ravi D3
D3 Finance Pune
D4 Testing Noida
 Right Outer Join

SELECT Emp.E_no, Emp. E_name, Dept. D_name, Dept. Location

FROM Emp RIGHT OUTER JOIN Dept Table:
Dept D_no D_name Location
ON ( Emp. D_no = Dept. D_no); D1 IT Delhi
Table: Emp D2 HR Hyd
E_no E_name D_no D3 Finance Pune
E1 Varun D1 D4 Testing Noida
E2 Amrit D2
E3 Ravi D3 Output
E_no E_name D_name Location
E1 Varun IT Delhi
E2 Amrit HR Hyd
E3 Ravi Finance Pune
NULL NULL Testing Noida
 Full Outer Join

In full outer join , all the tuples from both left relation R1and right relation R2
are included in the resulting relation. The tuples of both relations R1 and R2
which do not satisfy join conditions , theirs respective unmatched attributes
are made NULL.

,
 Full Outer Join

SELECT *
FROM Courses FULL JOIN HOD ; Table: HOD
CID Name
Table: Courses 100 Rohan
102 Sara
CID Course 104 Jia
100 Database
101 Mechanics
Output
102 Electronics
CID Course Name
100 Database Rohan
101 Mechanics NULL
102 Electronics Sara
104 NULL Jia
 Self Join

A self join is join that can be used to join a table with itself. Hence it is a
unary relation.
In a self join, each row of the table is joined with itself and all the other rows
of the same table. Thus, a self join is mainly used to combine and compare
the rows of the same table in the database.
But whenever we perform self join, it creates ambiguity because we have to
use the name of the same table again. In the query, if we write same table
name twice, it will give an error. So as to avoid these circumstances, we have
to use ‘alias’ names for the same tables. And alias name simply provides a
table with different name for performing such operations. ‘AS’ keyword can
be used to provide alias names to the tables.
Syntax:
SELECT t1.column1, t2.column2
FROM table AS t1, table AS t2
WHERE condition;
 Self Join

Table: t1 X t2 Table: Study t1

S_id C_id S_id C_id S_id C_id Since
S1 C1 S1 C1 S1 C1 2016
S2 C2 2017
S1 C1 S2 C2
S1 C2 2017
S1 C1 S1 C2
S2 C2 S1 C1
S2 C2 S2 C2
Table: Study t2
S2 C2 S1 C2 S_id C_id Since
S1 C1 2016
S1 C2 S1 C1
S2 C2 2017
S1 C2 S2 C2
S1 C2 2017
S1 C2 S1 C2
 Self Join

Table: t1 X t2 Table: Study t1

S_id C_id S_id C_id S_id C_id Since
S1 C1 S1 C1 S1 C1 2016
S1 C1 S2 C2 S2 C2 2017
S1 C1 S1 C2 S1 C2 2017
S2 C2 S1 C1
S2 C2 S2 C2 Table: Study t2
S2 C2 S1 C2 S_id C_id Since
S1 C2 S1 C1 S1 C1 2016
S1 C2 S2 C2 S2 C2 2017
S1 C2 S1 C2 S1 C2 2017
Q. Find the student id who is enrolled in at least two courses
SELECT t1.S_id FROM study AS t1 , study AS t2
WHERE t1.S_id = t2.S_id AND t1.C_id < > t2. C_id;
 Self Join

Table: t1 X t2
S_id C_id S_id C_id
S1 C1 S1 C2
S1 C2 S1 C1

Q. Find the student id who is enrolled in at least two courses

SELECT t1.S_id FROM study AS t1 , study AS t2
WHERE t1.S_id = t2.S_id AND t1.C_id < > t2. C_id;
Final Output
S_id
S1
 Questions on SQL

Q. Consider the following relation and attributes.

BOOK-INFORMATION
Book_ID varchar(5)
Book_title varchar(40)
Price number(5)
Query 1 : Develop DDL in SQL to implement above schema.
CREATE TABLE BOOK-INFORMATION
(Book_ID varchar(5),
Book_title varchar(40) ,
Price number(5 ) );
 Questions on SQL

Query 2 : Develop SQL queries to list all the books whose unit price is greater
than 500.
SELECT *
FROM BOOK-INFORMATION
WHERE Price > 500;
Query 3 : Develop SQL queries to list all the books whose title starts with ‘A’
SELECT *
FROM BOOK-INFORMATION
WHERE Book_title LIKE ‘ A%’ ;
 Questions on SQL

Query 4 : Develop SQL queries to insert the following piece of data into
BOOK-INFORMATION
Book_ID = 20 , Book_title = ‘Introduction to SQL’ , Price = 750

INSERT INTO BOOK-INFORMATION ( Book_ID , Book_title, Price)

VALUES ( 20, ‘ Introduction to SQL ‘ , 750 );
Query 5 : Develop SQL queries to list the most expensive book in the
database.
SELECT *
FROM BOOK-INFORMATION
WHERE Price = (select max(Price) from BOOK-INFORMATION) ;
 Questions on SQL

Q : Consider an employee database where primary keys are underlined.

employee ( employee_name(PK) , street, city)

works ( employee_name (PK) , company_name , salary)
company ( company_name(PK), city )
manages ( employee_name(PK) , manager_name)
Give an experssion in SQL for each of the following queries.

Query 1: find name of all employees who work for ABC Bank
SELECT employee_name
FROM works
WHERE company_name = ‘ABC Bank’ ;
 Questions on SQL

Query 2: find names and cities of residence of all employees who work for
ABC Bank
SELECT e. employee_name , e. city
FROM employee AS e , works AS w
WHERE w. company_name = ‘ABC Bank’
AND w. employee_name = e. employee_name;
 Questions on SQL

Query 3: find all employees in the database who live in the same cities and
on the same streets as do their managers.
SELECT P. employee_name
FROM employee AS P , emplooyee AS R , manages AS M
WHERE P. employee_name = M. employee_name
AND M. manager_name = R. employee_name
AND P.street = R.street
AND P.city = R.city ;
 Questions on SQL

Query 4: give all employee of ABC Bank a 10 % raise.

SELECT employee_name , salary
FROM works
WHERE (company_name = ‘ABC Bank’ ) AND (salary = salary + 0.1 * salary) ;
 Questions on SQL

Q: Write SQL queries for following set of tables:

EMPLOYEE (EnpNo, Name, DoB, Address, Gender, Salary, Dnumber)
DEPARTMENT ( Dnumber, Dname, ManagerEmpNo, ManagerStartDate)
Query 1: Display name of employee who earns highest salary.
SELECT Name, MAX(Salary) AS Salary
FROM Employee;
Query 2: Display all employees in department named ‘ Marketing’.
SELECT e.Name, d.Dname
FROM EMPLOYEE AS e, DEPARTMENT AS d
WHERE d.Dname = ‘Marketing’ AND e.Dnumber = d.Dnumber;
 Questions on SQL

Query 3: Increment salary of all employees by 5% working ‘sales’ department