DS 5110 – Lecture 4
SQL Part II
Roi Yehoshua
Agenda
Subqueries
Common table expressions (CTEs)
Recursive queries
Query execution plans
Views
Stored procedures
Functions
Transactions
Triggers
Indexes
2 Roi Yehoshua, 2024
Subqueries
A subquery is a SELECT query nested inside another query
Subqueries allow you to perform SQL operations in multiple steps
The subquery is executed before its parent query and its results are passed to the outer query
A subquery may be nested inside
The SELECT clause
The FROM clause
The WHERE clause
The HAVING clause
Subqueries must be enclosed inside brackets ()
Subqueries can return an individual value or a list of records
3 Roi Yehoshua, 2024
Subqueries
Example: Find the name of the instructor with the highest salary
This query cannot be written without sorting the whole table
The way to write it with a subquery:
4 Roi Yehoshua, 2024
Correlated Subqueries
A correlated subquery references one or more columns from the outer query
A correlated subquery is executed once for each candidate row in the outer query
Example: Find all instructors whose salary is higher than the average salary in their
own department
5 Roi Yehoshua, 2024
Multiple-Row Subqueries
If the subquery returns more than one row, you cannot use it with the regular
comparison operators like <, >, =
There are 4 additional operators that allow to use the results of these subqueries:
IN / NOT IN
ALL
ANY
EXISTS / NOT EXISTS
6 Roi Yehoshua, 2024
The IN Operator
Checks whether a column contains a value that is within the results of the subquery
Example: Find all the courses that were offered both in Fall 2017 and Spring 2018
7 Roi Yehoshua, 2024
The IN Operator
You can compare more than one column with the results of the subquery
Example: Find the total number of (distinct) students who have been taught by Katz
8 Roi Yehoshua, 2024
The NOT IN Operator
Checks if the column doesn’t contain any value within the results of the subquery
Example: Find all the courses that were offered in Fall 2017 but not in Spring 2018
9 Roi Yehoshua, 2024
Class Exercise
Print the id and names of students that have taken a course in Computer Science,
but have never taken a course in Physics
10 Roi Yehoshua, 2024
Solution
11 Roi Yehoshua, 2024
The ALL Operator
The ALL operator returns true if all of the subquery results meet the condition
Used in conjunction with one of the comparison operators (=, <>, >, >=, <, or <=)
Example: Find the department with the highest number of courses
12 Roi Yehoshua, 2024
The ALL Operator
Note that the following attempt to write the same query won’t work
You cannot layer aggregate functions on top of each other in the same SELECT
13 Roi Yehoshua, 2024
The ANY Operator
The ANY operator returns true if any of the subquery results meets the condition
Used in conjunction with one of the comparison operators (=, <>, >, >=, <, or <=)
Example: Find all the instructors whose salary is higher than any of the instructors in
the Biology department
14 Roi Yehoshua, 2024
The EXISTS Operator
The EXISTS operator tests the existence of any result in the subquery
It returns true if the subquery returns one or more rows
For example, another way to write the query “Find all the courses that were offered
both in Fall 2017 and Spring 2018”
15 Roi Yehoshua, 2024
The NOT EXISTS Operator
You can also use NOT EXISTS to check if the subquery didn’t return any rows
Example: Find instructors who haven’t taught any course
16 Roi Yehoshua, 2024
The NOT EXISTS Operator
Example: Find students who have taken all the courses in Music
17 Roi Yehoshua, 2024
The NOT EXISTS Operator
Another way to write the same query:
18 Roi Yehoshua, 2024
Class Exercise
Print the instructors that taught all the courses in their department
19 Roi Yehoshua, 2024
Subqueries in the FROM Clause
SQL allows you to write subqueries in the FROM clause
In MySQL, the subquery must be given a name using the AS keyword
The subquery results can be joined with other tables or other subqueries
Example: Find the highest number of courses offered in any given department
20 Roi Yehoshua, 2024
Subqueries in the SELECT Clause
You can write subqueries in the select list of another SELECT clause
These subqueries need to return a single value for each row of the outer SELECT
Example: List all departments along with the number of instructors in each one
21 Roi Yehoshua, 2024
Updates with Subqueries
You can also use subqueries in UPDATE and DELETE statements
Example: recompute and update the tot_cred value for each student
UPDATE student AS s
SET tot_cred = (
SELECT SUM(c.credits)
FROM takes AS t
JOIN course AS c
WHERE t.course_id = c.course_id
AND s.id = t.id
AND t.grade IS NOT NULL
AND t.grade <> 'F'
);
Sets tot_cred to NULL for students who have not taken any course
Instead of SUM(credits) can use COALESCE(SUM(credits), 0)
This would return SUM(credits) if it is not NULL, and 0 otherwise
22 Roi Yehoshua, 2024
Common Table Expressions (CTEs)
CTE is a named temporary result set that only exists for the duration of the query
CTEs are useful for breaking down complex queries into simpler parts
Basic syntax of CTE:
WITH cte_name (column_name1, column_name2, ...)
AS (
-- Your SQL query here
)
-- Your main SQL statement using the CTE here
23 Roi Yehoshua, 2024
Common Table Expressions (CTEs)
Example: find all the instructors that earn more than the average salary
24 Roi Yehoshua, 2024
Common Table Expressions (CTEs)
Show the distribution of number of courses taken by students
i.e., how many students took only one course, how many students took two courses, etc.
25 Roi Yehoshua, 2024
Recursive Queries
A recursive query is a query that refers back to itself
Used to deal with hierarchical or tree-structured data
A recursive query is written using a CTE that consists of two subqueries:
The base (non-recursive) query provides the starting point of the recursion
The recursive query which has a reference to the CTE itself
26 Roi Yehoshua, 2024
Recursive Queries
Example: Find for each course all its prerequisites (both direct and indirect)
27 Roi Yehoshua, 2024
Summary: SQL Execution Order
28 Roi Yehoshua, 2024
Query Execution Plans
A query execution plan is an ordered set of steps used to access data in the database
Defines which algorithm to use for each operation, and how to coordinate the operations
When a query is submitted to the database, the query optimizer evaluates the
different plans for executing the query and returns what it considers the best option
Cost difference between execution plans for a query can be enormous
Estimation of plan cost is based on:
Statistical information about tables
e.g., number of rows, number of distinct values in a column
Cost of different algorithms (e.g., join vs. subquery)
Also computed using statistics
Sometimes we need to manually examine and tune the optimizer plans
29 Roi Yehoshua, 2024
Query Execution Plans
To view the execution plan, click on Execution Plan next to the query results
30 Roi Yehoshua, 2024
Class Exercise
Write the following query: Find IDs and names of all the instructors whose salary is
greater than the salary of the instructor Katz
Using join
Using subselect
Examine the execution plans of these queries
Which query is more efficient?
31 Roi Yehoshua, 2024
Solution
Using join
32 Roi Yehoshua, 2024
Solution
Using subselect
33 Roi Yehoshua, 2024
Views
A view is a virtual table based on the results set of an SQL statement
Reasons to use views:
Data security – allows you to hide certain data from view of certain users
e.g., some users only need to know the instructor IDs and names, but not their salaries
Hiding complexity of the underlying tables
A view is created with the CREATE VIEW statement:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
A view always shows up-to-date data
The database engine recreates the view whenever it is used in a query
34 Roi Yehoshua, 2024
Views
Example: A view that shows all Physics instructors without their salary
CREATE VIEW physics_faculty AS
SELECT id, name
FROM instructor
WHERE dept_name = 'Physics';
We can query the view above as follows:
View names may appear in a query anywhere a table name may appear
35 Roi Yehoshua, 2024
Views
You can see all the available views in the database under the View folder:
To update a view use the CREATE OR REPLACE VIEW command:
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
To delete a view use the DROP VIEW command:
DROP VIEW view_name;
36 Roi Yehoshua, 2024
Updatable Views
Some views are updatable
i.e., you can use them in statements like INSERT or UPDATE to update the underlying tables
For a view to be updatable, there must be a one-to-one relationship between the
rows in the view and the rows in the underlying table
More specifically, the view isn’t updatable if it contains any of the following:
Aggregate functions like MIN() or AVG()
DISTINCT
GROUP BY or HAVING
Certain joins are not allowed
Subquery in SELECT
Subquery in the WHERE clause that refers to a table in the FROM clause
UNION or UNION ALL
37 Roi Yehoshua, 2024
Stored Procedures
A stored procedure (SP) is a group of SQL statements that is stored in the database
You can also pass parameters to a stored procedure
The benefits of using stored procedures:
Reusable code that can be used by different applications
Allows a single point of change in case the business rules change
Faster execution
Reduce the network traffic
More secure than ad-hoc queries
38 Roi Yehoshua, 2024
Creating a Stored Procedure
The syntax for creating a new stored procedure in MySQL:
DELIMITER $$ Redefine the delimiter temporarily
so that ; can be used to separate
CREATE PROCEDURE procedure_name (
statements inside the procedure
IN | OUT | INOUT parameter1 datatype,
IN | OUT | INOUT parameter2 datatype,
...
) Use ; in the body of the
BEGIN stored procedure
-- SQL statements
END $$ Use the delimiter $$ to end the
DELIMITER ; stored procedure
Change the default delimiter back to ;
Parameter modes:
in (default) – the parameter’s value is passed into the SP and cannot be changed inside the SP
out – the parameter’s value is passed back to the calling program, must be a variable
inout – the parameter’s value is passed to the SP and a new value can be assigned to it
39 Roi Yehoshua, 2024
Creating a Stored Procedure
Example: a stored procedure that returns all courses in a given department
40 Roi Yehoshua, 2024
Creating a Stored Procedure
You can view your procedure under the Stored Procedures folder of the schema
41 Roi Yehoshua, 2024
Calling a Stored Procedure
To execute the store procedure, you use the CALL keyword:
CALL procedure_name(parameter1, parameter2, ...);
For example, let’s call the get_courses_by_dept with Comp. Sci. as the argument:
42 Roi Yehoshua, 2024
Calling a Stored Procedure
You can also execute the SP by clicking on the Execute button next to its name
43 Roi Yehoshua, 2024
OUT Parameter
A stored procedure that returns the number of students in a given department
44 Roi Yehoshua, 2024
OUT Parameter
To call a SP with an OUT parameter you need to pass a session variable to it
This variable will receive the return value from the procedure
Session variables are variables that start with @
Don’t require any declaration
Can be used inside any SQL query or statement
Exist until the end of the current session
Assignments to the variable are performed using a SET statement
To display the value of a variable use the SELECT statement
45 Roi Yehoshua, 2024
InOut Parameter Example
The following example demonstrates how to use an inout parameter in a SP:
46 Roi Yehoshua, 2024
InOut Parameter Example
The following statements illustrate how to call the update_counter SP:
47 Roi Yehoshua, 2024
Class Exercise
Create a stored procedure that gets a student ID and returns the IDs and titles of the
courses he/she has taken
48 Roi Yehoshua, 2024
Solution
49 Roi Yehoshua, 2024
Programming Language Constructs
Stored procedures support programming language constructs like ifs and loops:
IF boolean expression THEN
statement_list
[ELSEIF boolean expression THEN
statement_list]
...
[ELSE statement_list]
END IF;
WHILE boolean expression DO
statement_list
END WHILE;
REPEAT
statement_list
UNTIL boolean expression
END REPEAT;
50 Roi Yehoshua, 2024
Programming Language Constructs
The following procedure registers a student to a course section only if there is
enough room left in the classroom allocated for this section
51 Roi Yehoshua, 2024
Programming Language Constructs
52 Roi Yehoshua, 2024
Programming Language Constructs
Calling the procedure:
53 Roi Yehoshua, 2024
Functions
A function is a special type of a stored program that returns a single value
You can use a stored function in SQL statements wherever an expression is used
The syntax for creating a new stored function in MySQL:
DELIMITER $$
CREATE FUNCTION function_name ( • For a function, parameters are always
parameter1 datatype, regarded as in parameters
parameter2 datatype, • A function can only return values of type
... {STRING|INTEGER|REAL|DECIMAL}
)
RETURNS datatype
• A deterministic function always returns
[NOT] DETERMINISTIC
BEGIN the same result for the same parameters
-- SQL statements • A non-deterministic function returns
END $$ different results for the same parameters
• MySQL uses the not deterministic option
DELIMITER ; by default
54 Roi Yehoshua, 2024
Function Example
The following function returns the number of students in a given department
55 Roi Yehoshua, 2024
Calling a Function
You can call a function inside a SELECT statement:
Or you can set the result of the function into a session variable:
56 Roi Yehoshua, 2024
Transactions
A transaction is a sequence of SQL statements that represents a single unit of work
For example, consider a Bank amount transfer, which involves two operations:
Withdrawal of money from account A
Deposit money to Account B
If the system crashes after subtracting the amount from A but before adding it to B,
the bank balances will be inconsistent
57 Roi Yehoshua, 2024
ACID
A set of properties of database transactions intended to guarantee data validity
Atomicity – All the operations of a transaction either succeed completely or fail completely
Consistency – Execution of a transaction preserves the consistency of the database
Isolation – Multiple transactions may execute independently without interference
Durability – The changes made by a successful transaction persist in the database even in the
case of a system failure
58 Roi Yehoshua, 2024
Transactions
To define a new transaction, use the START TRANSACTION statement
All the subsequent SQL statements will belong to the transaction
The transaction must end with one of the following statements:
COMMIT: The updates performed by the transaction become permanent in the database
ROLLBACK: All updates performed by the transaction are undone
59 Roi Yehoshua, 2024
Transactions
In MySQL, transactions need to be written inside a stored procedure
So you can automatically rollback the transaction whenever an error occurs
The following statement needs to be written in the beginning of the procedure:
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
The following procedure updates a student’s grade in a course
This procedure needs to update both the takes table and the student’s tot_cred
These two updates should be performed in a single transaction
60 Roi Yehoshua, 2024
Transaction Example
61 Roi Yehoshua, 2024
Transaction Example
62 Roi Yehoshua, 2024
Transaction Example
Calling the procedure with valid parameters results in a successful commit:
63 Roi Yehoshua, 2024
Transaction Example
Calling the procedure with invalid parameters results in a rollback:
64 Roi Yehoshua, 2024
Triggers
A trigger is a statement that is executed automatically by the database
as a side effect of performing a modification on the database
The syntax for creating a trigger in MySQL:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON
table_name
FOR EACH ROW
BEGIN
trigger_body;
END
The triggering event can be INSERT, UPDATE or DELETE
The trigger action time can be either before or after the event
The statements following FOR EACH ROW execute once for each row affected by the event
Values of attributes before and after the update can be referenced using OLD and NEW
65 Roi Yehoshua, 2024
Trigger Example
A trigger that keeps the tot_cred attribute of a student up-to-date when a new grade
is assigned to them in the takes relation
CREATE TRIGGER credits_earned AFTER UPDATE ON takes
FOR EACH ROW
BEGIN
IF NEW.grade <> 'F' AND NEW.grade IS NOT NULL
AND (OLD.grade = 'F' OR OLD.grade IS NULL) THEN
UPDATE student
SET tot_cred = tot_cred + (
SELECT credits FROM course
WHERE course.course_id = NEW.course_id
)
WHERE student.id = NEW.id;
END IF;
END
66 Roi Yehoshua, 2024
Triggers in MySQL
You can also define a trigger from the MySQL Workbench menu
Click on the tools icon next to the table
Click on the Triggers tab and choose the desired trigger type
Enter the trigger’s code in the code editor
67 Roi Yehoshua, 2024
Triggers in MySQL
Click Apply
68 Roi Yehoshua, 2024
Triggers in MySQL
You can view all the triggers in the database using the command show triggers
69 Roi Yehoshua, 2024
When Not to Use Triggers
In the past, triggers were used for tasks such as
Maintaining summary data (e.g., total salary of each department)
Replicating databases by recording changes to special delta relations
There are better ways of doing these now:
Databases today provide built-in materialized view facilities to maintain summary data
Databases provide built-in support for replication
There are situations where you want to disable execution of triggers, e.g.,
When loading data from a backup copy
When replicating updates at a remote site
70 Roi Yehoshua, 2024
Indexes
An index is a data structure that can speed up queries/searches
e.g., searching for instructors in a specific department
Drawbacks of indexes
Causes updates to the table to become slower (since the index also needs to be updated)
Storage space
71 Roi Yehoshua, 2024
Creating Indexes
We create an index using the CREATE INDEX command
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...);
A UNIQUE index creates a constraint such that all values in the index must be distinct
For example, let’s define an index on the name column in the instructor table:
CREATE INDEX idx_name
ON instructor (name);
You can see the new index under the Indexes folder of the table:
72 Roi Yehoshua, 2024
Clustered Indexes
A clustered index defines the physical order in which table records are stored
There can be only one clustered index per table
By default a clustered index is created on a primary key column
Accessing a row through the clustered index is fast
Since the index search leads directly to the page that contains the row data
73 Roi Yehoshua, 2024
