CO2 Session 11
CO2 Session 11
CO2 Session 11
TOPIC:
COMPLEX QUERIES WITH SYNTAX
Session - 11
1
AIM OF THE SESSION
INSTRUCTIONAL OBJECTIVES
LEARNING OUTCOMES
At the end of this session, you should be able to understand the basic concepts of Subqueries and learn
how to write complex subqueries with PostgreSQL commands.
2
SESSION INTRODUCTION
3
SUBQUERY SYNTAX
•The subquery (inner query) executes once before the main query (outer
query) executes.
4
SUBQUERY EXAMPLE
For example, List the name of the employees, paid more than 'Alexander' from
employees.
5
SUBQUERY EXAMPLE
6
SUBQUERY GUIDELINES
7
TYPES OF SUBQUERIES
8
POSTGRESQL SUBQUERIES: USING COMPARISONS
A subquery can be used before or after any of the comparison operators. The subquery
can return at most one value. The value can be the result of an arithmetic expression or
a column function. SQL then compares the value that results from the subquery with the
value on the other side of the comparison operator. You can use the following
comparison operators:
9
POSTGRESQL SUBQUERIES: USING COMPARISONS(Contd..)
For example, suppose you want to find the employee id, first_name, last_name, and
salaries for employees whose average salary is higher than the average salary throughout
the company.
10
POSTGRE SUBQUERIES: USING IN OPERATOR
11
POSTGRESQL SUBQUERIES: USING ALL OPERATOR
The ALL operator compares value to every value returned by the subquery. The right-
hand side is a parenthesized subquery, which must return exactly one column. The left-
hand expression is evaluated and compared to each row of the subquery result using the
given operator. For example, the following query selects the department with the highest
average salary. The subquery finds the average salary for each department, and then the
main query selects the department with the highest average salary.
SELECT department_id, AVG(SALARY) FROM employees GROUP BY
department_id HAVING AVG(SALARY) >= ALL (SELECT AVG(SALARY) FROM
employees GROUP BY department_id);
12
POSTGRE SUBQUERIES: USING ANY OPERATOR
The ANY operator compares the value to each value returned by the subquery. Therefore
ANY keyword (which must follow a comparison operator) returns TRUE if the
comparison is TRUE for ANY of the values in the column that the subquery returns.
The following query selects any employee who works in the location 1700. The subquery
finds the department id in the 1700 location, and then the main query selects the
employees who work in any of these departments.
13
POSTGRE SUBQUERIES WITH EXISTS
14
CORRELATED SUBQUERIES
A correlated subquery is a subquery that contains a reference to a table (in the parent query) that
also appears in the outer query. PostgreSQL evaluates from inside to outside. For example,
following correlated subquery find all employees who earn more than the average salary in their
department.
SELECT last_name, salary, department_id FROM employees outer WHERE salary > (SELECT
AVG(salary) FROM employees WHERE department_id = outer.department_id);
15
SUBQUERIES WITH THE INSERT STATEMENT
Subqueries also can be used with INSERT statements. The INSERT statement uses the
data returned from the subquery to insert into another table. For example, to insert all
records into ‘employee1' table from ‘employee' table, the following SQL statement can be
used:
We can insert records using subqueries with where clause also. For example, to insert
those records into ‘employee1' table from ‘employee' table who gets more than 5000
salary, the following SQL statement can be used:
16
SUBQUERIES WITH THE UPDATE STATEMENT
The subquery can be used in conjunction with the UPDATE statement. Either single or
multiple columns in a table can be updated when using a subquery with the UPDATE
statement. The following example updates SALARY by 0.50 times in the employee table
for all the employees, who work in the department ‘Accounts’.
17
SUBQUERIES WITH THE DELETE STATEMENT
The subquery can also be used with the DELETE statement. Either single or multiple
columns in a table can be deleted when using a subquery with the DELETE statement. The
following example deletes all employees except those employees, who work in the
department ‘HR’.
18
IMPORTANT FACTS RELATED TO THE SESSION
• Subqueries are generally used with the SELECT statement but these queries can
also be used with the INSERT Statement, UPDATE Statement, and the
DELETE Statement.
19
SUMMARY
In this section, we discussed the complex sql queries, general guidelines for
creating these complex subqueries.
We also discussed the different types of subqueries in detail with its syntax and
examples.
20
SELF-ASSESSMENT QUESTIONS
(a) SELECT
(b) WHERE
(c) ORDER BY
(d) GROUP BY
21
SELF-ASSESSMENT QUESTIONS
(a) IN
(b) ANY
(c) ALL
(d) ALL OF THE ABOVE
22
TERMINAL QUESTIONS
PostgreSQL.
23
REFERENCES FOR FURTHER LEARNING OF THE
SESSION
Reference Books:
1. Database System Concepts, Sixth Edition, Abraham Silberschatz, Yale University Henry, F. Korth
Lehigh University, S. Sudarshan Indian Institute of Technology, Bombay.
2. An Introduction to Database Systems by Bipin C. Desai
3. Fundamentals of Database Systems, 7th Edition, RamezElmasri, University of Texas at Arlington,
Shamkant B. Navathe, University of Texasat Arlington.
24
THANK YOU
Team – DBMS
25