DBMS SQL DAY 14

DAY - 14

5.     DQL: Data Query Language

Command	Description
select	retrieve records from one or more table

WHERE clause

Where clause is used to specify condition while retrieving data from table. Where clause is used mostly with Select, Update and Delete query. If condition specified by where clause is true then only the result from 

 table is returned.

Syntax for WHERE clause

SELECT column-name1,

 column-name2,

 column-name3,

 column-nameN

from table-name WHERE [condition];

Example using WHERE clause

Consider a Student table,

s_id	s_Name	age	address
101	Adam	15	Noida
102	Alex	18	Delhi
103	Abhi	17	Rohtak
104	Ankit	22	Panipat

Now we will use a SELECT statement to display data of the table, based on a condition, which we will add to the SELECT query using WHERE clause.

SELECT s_id, s_name, age, address

from Student  

WHERE s_id=101;

s_id	s_Name	age	address
101	Adam	15	Noida

SELECT Query

Select query is used to retrieve data from a tables. It is the most used SQL query. We can retrieve complete tables, or partial by mentioning conditions using WHERE clause.

Syntax of SELECT Query

SELECT column-name1, column-name2, column-name3, column-nameN from table-name;

Example for SELECT Query

Conside the following Student table,

S_id	S_Name	age	address
101	Adam	15	Noida
102	Alex	18	Delhi
103	Abhi	17	Rohtak
104	Ankit	22	Panipat

SELECT s_id, s_name, age from Student.

The above query will fetch information of s_id, s_name and age column from Student table

S_id	S_Name	age
101	Adam	15
102	Alex	18
103	Abhi	17
104	Ankit	22

Example to Select all Records from Table

A special character asterisk * is used to address all the data(belonging to all columns) in a query. SELECT statement uses * character to retrieve all records from a table.

SELECT * from student;

The above query will show all the records of Student table, that means it will show complete Student table as result.

S_id	S_Name	age	address
101	Adam	15	Noida
102	Alex	18	Delhi
103	Abhi	17	Rohtak
104	Ankit	22	Panipat

Example to Select particular Record based on Condition

SELECT * from Student WHERE s_name = 'Abhi';

103

Abhi

17

Rohtak

Example to Perform Simple Calculations using Select Query

Conside the following Employee table.

eid	Name	age	salary
101	Adam	26	5000
102	Ricky	42	8000
103	Abhi	22	10000
104	Rohan	35	5000

SELECT eid, name, salary+3000  from Employee;

The above command will display a new column in the result, showing 3000 added into existing salaries of the employees.

eid	Name	salary+3000
101	Adam	8000
102	Ricky	11000
103	Abhi	13000
104	Rohan	8000

Like clause

Like clause is used as condition in SQL query. Like clause compares data with an expression using wildcard operators. It is used to find similar data from the table.

Wildcard operators

There are two wildcard operators that are used in like clause.

• Percent sign % : represents zero, one or more than one character.
• Underscore sign _ : represents only one character.

Example of LIKE clause

Consider the following Student table.

s_id	s_Name	age
101	Adam	15
102	Alex	18
103	Abhi	17

SELECT * from Student where s_name like 'A%';

The above query will return all records where s_name starts with character 'A'.

s_id	s_Name	age
101	Adam	15
102	Alex	18
103	Abhi	17

Example

SELECT * from Student where s_name like '_d%';

The above query will return all records from Student table where s_name contain 'd' as second character.

s_id	s_Name	age
101	Adam	15

Example

SELECT * from Student where s_name like '%x';

The above query will return all records from Student table where s_name contain 'x' as last character.

s_id	s_Name	age
102	Alex	18

Order By Clause

Order by clause is used with Select statement for arranging retrieved data in sorted order. The Order by clause by default sort data in ascending order. To sort data in descending order DESC keyword is used with Order by clause. 

---

---

---

Syntax of Order By

SELECT column-list|* from table-name order by asc|desc; 
 

---

---

---

Example using Order by

Consider the following Emp table,

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	10000
405	Tiger	35	8000

SELECT * from Emp order by salary;

The above query will return result in ascending order of the salary.

eid	name	age	salary
403	Rohan	34	6000
402	Shane	29	8000
405	Tiger	35	8000
401	Anu	22	9000
404	Scott	44	10000

---

---

---

Example of Order by DESC

Consider the Emp table described above,

SELECT * from Emp order by salary DESC;

The above query will return result in descending order of the salary.

eid	name	age	salary
404	Scott	44	10000
401	Anu	22	9000
405	Tiger	35	8000
402	Shane	29	8000
403	Rohan	34	6000

Group By Clause

Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the result from one or more tables.

Syntax for using Group by in a statement.

SELECT column_name, function(column_name)

FROM table_name

WHERE condition

GROUP BY column_nam

Example of Group by in a Statement

Consider the following Emp table.

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	9000
405	Tiger	35	8000

Here we want to find name and age of employees grouped by their salaries

SQL query for the above requirement will be,

SELECT name, age

from Emp group by salary

Result will be,

name	age
Rohan	34
shane	29
anu	22

Example of Group by in a Statement with WHERE clause

Consider the following Emp table

eid	name	age	salary
401	Anu	22	9000
402	Shane	29	8000
403	Rohan	34	6000
404	Scott	44	9000
405	Tiger	35	8000

SQL query will be,

select name, salary

from Emp

where age > 25

group by salary

Result will be.

name	salary
Rohan	6000
Shane	8000
Scott	9000

You must remember that Group By clause will always come at the end, just like the Order by clause.

HAVING Clause

having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group based SQL functions, just like WHERE clause.

Syntax for having will be,

select column_name, function(column_name)

FROM table_name

WHERE column_name condition

GROUP BY column_name

HAVING function(column_name) condition

---

Example of HAVING Statement

Consider the following Sale table.

oid	order_name	previous_balance	customer
11	ord1	2000	Alex
12	ord2	1000	Adam
13	ord3	2000	Abhi
14	ord4	1000	Adam
15	ord5	2000	Alex

Suppose we want to find the customer whose previous_balance sum is more than 3000.

We will use the below SQL query,

SELECT * from sale group customer

having sum(previous_balance) > 3000

Result will be,

oid	order_name	previous_balance	customer
11	ord1	2000	Alex

Distinct keyword

The distinct keyword is used with Select statement to retrieve unique values from the table. Distinct removes all the duplicate records while retrieving from database.

---

Syntax for DISTINCT Keyword

SELECT distinct column-name from table-name;

---

Example

Consider the following Emp table.

eid	name	age	salary
401	Anu	22	5000
402	Shane	29	8000
403	Rohan	34	10000
404	Scott	44	10000
405	Tiger	35	8000

select distinct salary from Emp;

The above query will return only the unique salary from Emp table

salary

5000

8000

10000

AND & OR operator

AND and OR operators are used with Where clause to make more precise conditions for fetching data from database by combining more than one condition together. 

---

---

---

AND operator

AND operator is used to set multiple conditions with Where clause

Example of AND

Consider the following Emp table

eid	name	age	salary
401	Anu	22	5000
402	Shane	29	8000
403	Rohan	34	12000
404	Scott	44	10000
405	Tiger	35	9000

SELECT * from Emp WHERE salary < 10000 AND age > 25

The above query will return records where salary is less than 10000 and age greater than 25.

eid	name	age	salary
402	Shane	29	8000
405	Tiger	35	9000

---

---

---

OR operator

OR operator is also used to combine multiple conditions with Where clause. The only difference between AND and OR is their behaviour. When we use AND to combine two or more than two conditions, records satisfying all the condition will be in the result. But in case of OR, atleast one condition from the conditions specified must be satisfied by any record to be in the result. 

---

Example of OR

Consider the following Emp table

eid	name	age	salary
401	Anu	22	5000
402	Shane	29	8000
403	Rohan	34	12000
404	Scott	44	10000
405	Tiger	35	9000

SELECT * from Emp WHERE salary > 10000 OR age > 25

The above query will return records where either salary is greater than 10000 or age greater than 25.

402	Shane	29	8000
403	Rohan	34	12000
404	Scott	44	10000
405	Tiger	35	9000

Division Operator in SQL

The division operator is used when we have to evaluate queries which contain the keyword 'all'.

Some instances where division operator is used are:

1. Which person has account in all the banks of a particular city?
2. Which students have taken all the courses required to graduate?

In all these queries, the description after the keyword 'all' defines a set which contains some elements and the final result contains those units who satisfy these requirements. Another way how you can identify the usage of division operator is this logical implication of if...then. In context of the above two examples, we can see that the queries mean that

1. If there is a bank in that particular city, that person must have an account in that bank.
2. If there is a course in the list of courses required to be graduated, that person must have taken that course.

We shall see the last example in detail.

Table 1: Course_Taken - It consists of the names of Students against the courses that they have taken.

Table 2: Course_Required - It consists of the courses that one is required to take in order to graduate.

Query: Find all the students who can graduate (i.e. who have taken all the subjects required for one to graduate)

Unfortunately, there is no direct way in which we can express the division operator. Let's walk through step by step how to write the query for the division operator.

1. Find all the students

Create a set of all students that have taken courses. This can be done easily by the following command.

Create table AllStudents as SELECT DISTINCT Student_Name FROM Course_Taken

This gives the following table AllStudents:

2. Find all students and the courses required to graduate

Next, we will create a set of students and the courses they need to graduate. We can express this in the form of Cartesian Product of AllStudents and Course_Required by the following command.

CREATE table StudentsAndRequired AS SELECT AllStudents.Student_Name, Course_Required.Course

FROM AllStudents, Course_Required

The table StudentsAndRequired is as follows:

 

3. Find all students and the required courses they have not taken

Here, we are taking our first step for finding the students who cannot graduate. The idea is to simply find the students who have not taken certain courses they require to graduate and hence they cannot graduate. This is simply all those tuples which are present in StudentsAndRequired and not present in Course_Taken.

CREATE  table StudentsAndNotTaken AS SELECT * FROM StudentsAndRequired WHERE NOT

EXISTS (Select * from Course_Taken WHERE StudentsAndRequired.Student_Name = Course_Taken.Student_Name

AND StudentsAndRequired.Course = Course_Taken.Course)

The table StudentsAndNotTaken comes out to be:
 

4. Find all students who cannot graduate

All the students who are present in the table StudentsAndNotTaken are the ones who cannot graduate. Therefore, we can find the students who cannot graduate as

CREATE table CannotGraduate AS SELECT DISTINCT Student_Name FROM StudentsAndNotTaken

5. Find all students who can graduate

The students who can graduate are simply those who are present in AllStudents but not in CannotGraduate. This can be done by the following query:

CREATE Table CanGraduate AS SELECT * FROM AllStudents WHERE NOT

EXISTS (SELECT * FROM CannotGraduate WHERE CannotGraduate.Student_name = 
AllStudents.Student_name)

The results are as follows:
 

Hence we see how these steps can lead us to the final answer. Now let us see how to write all these 5 steps in one single query which does not create so many tables.

SELECT DISTINCT  x.Student_Name FROM Course_Taken AS x WHERE NOT

EXISTS(SELECT * FROM Course_Required AS y WHERE NOT

EXISTS(SELECT * FROM Course_Taken AS z WHERE z.Student_name = x.Student_name AND z.Course = y.Course ))

This gives us the same result as those 5 steps above.