Database Fundamentals. SQL part 3

1.

Database
Fundamentals
SQL part 3
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

2.

SUBQUERIES
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

3.

SUBQUERIES
Get all movies with specific genre
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

4.

SUBQUERIES
SELECT film_name, year
FROM movies.films
WHERE genre =
(SELECT id_genre
FROM movies.genres
WHERE genre_name = 'Фантастика')
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

5.

SUBQUERIES
Subqueries must be enclosed within parentheses.
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the
subquery to compare its selected columns.
An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP
BY command can be used to perform the same function as the ORDER BY in a subquery.
Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
A subquery cannot be immediately enclosed in a set function.
The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the
subquery.
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

6.

SUBQUERIES
SELECT film_name
FROM movies.films
WHERE film_director IN
(SELECT id_director
FROM directors
WHERE director_name IN ('С. Спилберг', 'Дж.Дж. Абрамс'))
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

7.

UNION, UNION ALL, EXCEPT, INTERSECT
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

8.

UNION/UNION ALL/INTERSECT/EXCEPT
Syntax:
SELECT columnName(s) FROM table1
UNION
SELECT columnName(s) FROM table2;
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

9.

UNION, UNION ALL, INTERSECT, EXCEPT
Rules:
each SELECT statement must have
The same number of columns selected
The same number of column expressions
The same data type and
Have them in the same order
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

10.

UNION
SELECT City
FROM dbo.Employees
UNION
SELECT City
FROM dbo.Customers
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

11.

UNION ALL
SELECT City
FROM dbo.Employees
UNION ALL
SELECT City
FROM dbo.Customers
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

12.

INTERSECT
SELECT City
FROM dbo.Employees
INTERSECT
SELECT City
FROM dbo.Customers
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

13.

EXCEPT
SELECT City
FROM dbo.Employees
EXCEPT
SELECT City
FROM dbo.Customers
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

14.

EXCEPT
SELECT City
FROM dbo.Customers
EXCEPT
SELECT City
FROM dbo.Employees
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

15.

JOINS
JOINS
INNER
OUTER
LEFT
RIGHT
FULL
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

16.

JOINS
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

17.

JOINS
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

18.

INNER JOIN
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

19.

INNER JOIN
SELECT select_list
FROM table1
INNER JOIN table2
ON table1.column_id = table2.column_id
WHERE condition(s)
SELECT select_list
FROM table1
JOIN table2
ON table1.column_id = table2.column_id
WHERE condition(s)
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

20.

INNER JOIN
SELECT select_list
FROM table1 t1
INNER JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
SELECT select_list
FROM table1 t1
JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

21.

INNER JOIN
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON
employee.DepartmentID = department.DepartmentID;
Employee.DepartmentID
Department.Department
Name
Rafferty
31
Sales
Jones
33
Engineering
Heisenberg
33
Engineering
Robinson
34
Clerical
Smith
34
Clerical
Employee.LastName
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

22.

INNER JOIN
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

23.

LEFT OUTER JOIN
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

24.

LEFT OUTER JOIN
SELECT select_list
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
SELECT select_list
FROM table1 t1
LEFT JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

25.

LEFT OUTER JOIN
SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID =
department.DepartmentID;
--can be w/o OUTER
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

26.

RIGHT OUTER JOIN
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

27.

RIGHT OUTER JOIN
SELECT select_list
FROM table1 t1
RIGHT OUTER JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
SELECT select_list
FROM table1 t1
RIGHT JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

28.

RIGHT OUTER JOIN
SELECT *
FROM employee
RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

29.

FULL OUTER JOIN
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

30.

FULL OUTER JOIN
SELECT select_list
FROM table1 t1
FULL OUTER JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
SELECT select_list
FROM table1 t1
FULL JOIN table2 t2
ON t1.column_id = t2.column_id
WHERE condition(s)
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

31.

FULL OUTER JOIN
Because FULL JOIN doesn't support in several kind of DB e.g. MySQL, RIGHT JOIN united with LEFT
JOIN will provide similar results.
SELECT * FROM employee E LEFT JOIN department D
ON E.DepartmentID = D.DepartmentID
UNION ALL
SELECT * FROM employee E RIGHT JOIN department D
ON E.DepartmentID = D.DepartmentID
WHERE NOT EXISTS (SELECT 1 FROM employee E
WHERE E.DepartmentID = D.DepartmentID);
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

32.

CROSS JOIN
SELECT select_list
FROM table1 t1
CROSS JOIN table2 t2
WHERE condition(s)
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

33.

CROSS JOIN
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

34.

CROSS OUTER JOIN
SELECT * FROM employee CROSS JOIN department;
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

35.

SELF JOIN
SELECT select_list
FROM table1 t1
JOIN table1 t2
WHERE condition(s)
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

36.

SELF JOIN
SELECT
e.first_name + ' ' + e.last_name employee,
m.first_name + ' ' + m.last_name manager
FROM
sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id =
e.manager_id
ORDER BY
manager;
CONFIDENTIAL | © 2020 EPAM Systems, Inc.

37.

QUESTIONS?
CONFIDENTIAL | © 2020 EPAM Systems, Inc.
37