DS 5110 – Lecture 4
SQL Part II
Roi Yehoshua
Agenda
Views
Stored procedures
Functions
Transactions
Triggers
Indexes
Accessing SQL from Python
SQL injections
Recursive queries
Query execution plans
Authorization
2 Roi Yehoshua, 2022
Views
Sometimes we don’t want all users to see the entire set of tables in the database
e.g., users that only need to know the instructor IDs and names, but not their salaries
A view provides a mechanism to hide certain data from the view of certain users
Views can also be used to create a personalized collection of “virtual relations” that
are better matched to a certain user’s data needs
A view is defined using the create view statement which has the form
create view v as <query expression>
Example: a view of instructors without their salary
create view faculty as
select ID, name, dept_name
from instructor
3 Roi Yehoshua, 2022
Views
Example: a view of all course sections offered by the Physics department in Fall 2017
with the building and room number of each section
create view physics_fall_2017 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = 'Physics'
and [Link] = 'Fall'
and [Link] = '2017'
4 Roi Yehoshua, 2022
Using Views
Whenever a view is accessed, its tuples are created by computing the query result
View names may appear in a query any place where a relation name may appear
Example: Find all instructors in the Biology department
select name
from faculty
where dept_name = 'Biology'
Views may also be used in the expression defining another view
Example: a view of all Physics courses offered in Fall 2017 in the Watson building
create view physics_fall_2017_watson as
select course_id, room_number
from physics_fall_2017
where building = 'Watson'
5 Roi Yehoshua, 2022
Updates on Views
There are a number of issues involved with updating a view
For example, consider a view based on a join operation:
create view instructor_info as
select ID, name, building
from instructor, department
where instructor.dept_name = department.dept_name
Consider the following insertion through this view:
insert into instructor_info
values ('69987', 'White', 'Taylor')
Issues
Which department to use if there are multiple departments in the Taylor building?
What if there is no department in Taylor building?
6 Roi Yehoshua, 2022
Updates on Views
Most SQL implementations allow updates only on simple views
The from clause has only one database relation
The select clause contains only attribute names of the relation, and doesn’t have any
expressions, aggregates, or distinct specification
Any attribute not listed in the select clause can be set to null
The query doesn’t have a group by or having clause
7 Roi Yehoshua, 2022
Stored Procedures
A stored procedure (SP) is a group of SQL statements 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
8 Roi Yehoshua, 2022
Creating a Stored Procedure
The syntax for creating a new stored procedure in MySQL:
create procedure procedure_name (in | out | inout parameter1 datatype,
in | out | inout parameter2 datatype,
…)
begin
SQL statements
end
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
9 Roi Yehoshua, 2022
Stored Procedure Example
Example: a stored procedure that returns all the courses in a given department
We define a different delimiter like $$ which is used to define the end of the entire procedure
Inside the procedure individual statements are each terminated by ;
10 Roi Yehoshua, 2022
Stored Procedure Example
You can view the stored procedure under the Stored Procedures folder of the
university schema
11 Roi Yehoshua, 2022
Calling a Stored Procedure
To execute the store procedure, you use the call keyword:
call procedure_name (parameter1, parameter2, …)
For example, calling the get_courses_by_department SP:
12 Roi Yehoshua, 2022
Calling a Stored Procedure
You can also execute the SP by clicking on the Execute button next to its name
13 Roi Yehoshua, 2022
Out Parameter Example
The following stored procedure returns the number of students by department
14 Roi Yehoshua, 2022
Session Variables
To call a stored procedure with an out parameter you need to pass a session variable
to receive the return value
A session variable is a user-defined variable that starts with @
Doesn’t require declaration
Can be used in any SQL query or statement
Exists until the end of the current session
Assignments to variables are performed using a set statement
You can assign a string or numeric value to the same session variable
To display the value of a variable use the select statement
15 Roi Yehoshua, 2022
Session Variables Example
16 Roi Yehoshua, 2022
Out Parameter Example
Calling get_enrollment_by_department:
17 Roi Yehoshua, 2022
InOut Parameter Example
The following example demonstrates how to use an inout parameter in a SP:
18 Roi Yehoshua, 2022
InOut Parameter Example
The following statements illustrate how to call the update_counter SP:
19 Roi Yehoshua, 2022
Class Exercise
Create a stored procedure that gets a student ID and returns the IDs and titles of the
courses he/she has taken
20 Roi Yehoshua, 2022
Solution
21 Roi Yehoshua, 2022
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:
create function function_name (parameter1 datatype,
parameter2 datatype, For a function, parameters are always
regarded as in parameters
…)
A function can only return values of type
returns datatype
{STRING|INTEGER|REAL|DECIMAL}
[not] deterministic
begin • A deterministic function always returns
the same result for the same parameters
SQL statements
• A non-deterministic function returns
end different results for the same parameters
• The default in MySQL is not deterministic
22 Roi Yehoshua, 2022
Function Example
The following function returns the number of instructors in a given department
23 Roi Yehoshua, 2022
Calling a Function
You can call a function from a set or a select statement:
24 Roi Yehoshua, 2022
Programming Language Constructs
SQL provides additional programming language constructs for procedures/functions
Conditional statements:
if Boolean expression then
statement or compound statement
elseif Boolean expression then
statement or compound statement
else statement or compound statement
end if;
A compound statement is of the form begin … end, and may contain multiple SQL
statements and definition of local variables
25 Roi Yehoshua, 2022
Language Constructs for Procedures and Functions
Loops:
while Boolean expression do
sequence of statements;
end while;
repeat
sequence of statements;
until Boolean expression
end repeat;
26 Roi Yehoshua, 2022
Example
The following procedure registers a student in a course section after ensuring the
capacity of the classroom allocated to that section is not exceeded
27 Roi Yehoshua, 2022
Example (Cont.)
28 Roi Yehoshua, 2022
Example (Cont.)
Calling the procedure:
29 Roi Yehoshua, 2022
Transactions
A transaction consists of a sequence of operations that must succeed/fail together
For example, a Bank amount transfer 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
30 Roi Yehoshua, 2022
Transactions
A new transaction begins with the statement start transaction
The transaction ends with one of the following commands:
commit: The updates performed by the transaction become permanent in the database
rollback: All updates performed by the SQL statements in the transaction are undone
In many databases (including MySQL), by default each SQL statement is taken to be a
transaction on its own, and gets committed as soon as it is executed
To force MySQL not to commit changes automatically, you can specify:
set autocommit = off
In MySQL, there is no automatic rollback on errors
Therefore, we typically write transactions within stored procedures
31 Roi Yehoshua, 2022
Transaction Example
When a student completes a course, we need to update both the takes relation and
their tot_cred attribute
create procedure update_grade(s_id varchar(5), c_id varchar(8), s_id varchar(5), sec_id varchar(8),
semester varchar(6), year decimal(4), grade varchar(2))
begin
-- If an error occurs, the entire transaction will be rolled back automatically
declare exit handler for sqlexception rollback;
start transaction;
update takes
set grade = grade
where id = s_id and course_id = c_id and sec_id = sec_id and semester = semester and year = year
update student
set tot_cred = tot_cred + (select credits from course
where course_id = c_id)
where id = s_id;
commit;
end
32 Roi Yehoshua, 2022
Transaction Example
Calling the procedure with valid parameters results in a successful commit:
33 Roi Yehoshua, 2022
Transaction Example
Calling the procedure with invalid parameters results in a rollback:
34 Roi Yehoshua, 2022
Triggers
A trigger is a statement that is executed automatically by the system as a side effect
of a modification to the database
To create a trigger, use the create trigger statement
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
35 Roi Yehoshua, 2022
Trigger Example
A trigger that keeps the tot_cred attribute of a student up-to-date when a new grade
is assigned to her in the takes relation
create trigger credits_earned after update on takes
for each row
begin
if [Link] <> 'F' and [Link] is not null
and ([Link] = 'F' or [Link] is null) then
update student
set tot_cred = tot_cred +
(select credits
from course
where course.course_id = new.course_id)
where [Link] = [Link];
end if;
end
36 Roi Yehoshua, 2022
Triggers in MySQL
To create a new trigger in MySQL Workbench, click on the tools icon next to the table
37 Roi Yehoshua, 2022
Triggers in MySQL
Click on the Triggers tab and choose the desired trigger type
38 Roi Yehoshua, 2022
Triggers in MySQL
Enter the trigger’s code in the code editor:
39 Roi Yehoshua, 2022
Triggers in MySQL
Click Apply
40 Roi Yehoshua, 2022
Triggers in MySQL
You can view all the triggers in the database using the command show triggers
41 Roi Yehoshua, 2022
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
42 Roi Yehoshua, 2022
Indexes
An index is a data structure speeds up access to desired data
e.g., searching for instructors in a specific department
Drawbacks of indexes
Overhead of updating the index when the table is updated
Storage space
43 Roi Yehoshua, 2022
Creating Indexes
We create an index with the create index command
create index <name> on <table-name> (attribute)
For example, to define an index on the instructor table with name as the search key:
To drop an index use the drop index command
44 Roi Yehoshua, 2022
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
45 Roi Yehoshua, 2022
Database Drivers
A database driver is a software that allows an application to interact with a DBMS
Open Database Connectivity (ODBC) is a specification for database API
Independent of any one DBMS or operating system
46 Roi Yehoshua, 2022
Python Database API
Python Database API (DB-API) is a standard interface for Python access to databases
Various Python packages implement this interface for different DBs
e.g., sqlite3 for SQLite, pyodbc for ODBC, [Link] for MySQL
47 Roi Yehoshua, 2022
MySQL Connector/Python
MySQL Connector/Python is the recommended driver for interacting with a MySQL
database from a Python application
You can install it with pip:
$ pip install mysql-connector-python
The general workflow of a Python program that interacts with a MySQL database is:
Connect to the MySQL server
Execute a SQL query
Fetch the results
Inform the database if any changes are made to a table (by committing the changes)
Close the connection to the server
48 Roi Yehoshua, 2022
Establishing a Connection with MySQL Server
To connect to a MySQL server, call the connect() function in [Link] module
This function has 4 parameters: host, user, password and database name
It returns a MySQLConnection object
You should always close the connection in the end by calling [Link]()
You should never hard-code your login credentials directly in a Python script
49 Roi Yehoshua, 2022
The Cursor Object
In order to execute SQL queries in Python, you need a cursor object
A cursor object allows you to traverse over database records
To create a cursor, use the cursor() method of your connection object
Then you execute a SQL query by calling [Link](query)
If the query returns rows, you can retrieve them using one of cursor’s fetch methods:
fetchall() - retrieves all the rows from the result as a list of tuples
fetchone() - retrieves the next row of the result as a tuple
Returns None if no more rows are available
fetchmany(n) - retrieves the next n rows from the result as a list of tuples (n defaults to 1)
Returns an empty list if no more rows are available
50 Roi Yehoshua, 2022
Reading Records from a Table
The following example selects all the records from the instructor table:
• The result variable holds the
records returned [Link]()
• It’s a list of tuples representing
individual records from the table
51 Roi Yehoshua, 2022
Reading Records from a Table
To get specific attributes in each row, specify their indexes in the returned tuples
For example, to print only the instructor names:
52 Roi Yehoshua, 2022
Cursor as an Iterator
To process the rows in the result one at a time, you can use the cursor as an iterator:
53 Roi Yehoshua, 2022
More Complex Queries
You can make your select queries as complex as you want using the same methods
Example: Find the courses that were taken by the highest number of students
54 Roi Yehoshua, 2022
Cursor Properties
The cursor has a few useful properties that provide information about the result set
column_names – returns the list containing the column names of the result set
rowcount – the number of rows in the result set
55 Roi Yehoshua, 2022
SQL Injection Attack
A common attack that enables execution of malicious SQL statements in the DB
By insertion (“injection”) of a SQL query via the input data from the client
Suppose you write a script that checks if a given username exists in the users table
You construct the following SQL query:
username = # read from the input
query = "SELECT * FROM users WHERE username = '" + username + "'"
If the user, instead of entering their username, enters:
Then the query becomes:
query = "SELECT * FROM users WHERE username = '' or 1 = 1 --'"
The where clause is now always true and the entire users table is returned
56 Roi Yehoshua, 2022
SQL Injection Attack
Most databases allow execution of multiple SQL statements separated by semicolon
This allows the hacker to inject whole SQL statements into the query
For example, the hacker could enter the following string:
The resulting query would be:
query = "SELECT * FROM users WHERE username = ''; drop table users; --"
This query would result in deleting the entire users table!
Solution: use parameterized queries whenever user input is involved in the query
57 Roi Yehoshua, 2022
SQL Injection Attack
Example:
58 Roi Yehoshua, 2022
Parameterized Query
A parameterized query is a query that uses placeholders (%) for attribute values
Strings passed to the placeholders are correctly escaped by the library at runtime
e.g., each quotation mark is doubled
You can pass a parameterized query to [Link]() as follows:
59 Roi Yehoshua, 2022
Parameterized Query
If a user tries to sneak in some problematic characters, the resulting statement will
cause no harm since each quotation mark will be doubled:
select * from instructor where name = 'X'' or ''Y'' = ''Y'
This query will return an empty set
60 Roi Yehoshua, 2022
Inserting New Records
To insert data, pass the insert into command to the cursor’s execute() method
For example, to add a new instructor:
You must call [Link]() at the end, otherwise your changes will be lost!
Unless you turn on automatic commits by setting [Link] = True
61 Roi Yehoshua, 2022
Inserting New Records
Verifying that the new record was added to the table:
62 Roi Yehoshua, 2022
Inserting New Records
If the values to be inserted come from an external source (e.g., the user), again you
should use parameters in the SQL statement:
63 Roi Yehoshua, 2022
Inserting a Bulk of Records
You can insert multiple records using the executemany() method
It accepts two parameters:
A query that contains placeholders for the records that need to be inserted
A list that contains all records that you wish to insert
64 Roi Yehoshua, 2022
Update and Delete
Updating and deleting work the same way, just pass the SQL to [Link]()
You can use the cursor rowcount attribute to check how many records were affected:
65 Roi Yehoshua, 2022
Managing Database Transactions
MySQL Connector provides the following methods to manage database transactions:
commit(): sends a commit statement to the MySQL server
rollback(): reverts the changes made by the current transaction
autoCommit(): enable or disable the auto-commit feature of MySQL (it’s false by default)
66 Roi Yehoshua, 2022
Calling Stored Procedures from Python
To call a stored procedure use the callproc() method of the Cursor object
[Link](procedure_name, args=())
Then, you can call the stored_results() method to get an iterator with the result set
The rows in the result can be read by calling the fetchall() method
Example:
67 Roi Yehoshua, 2022
Loading Data into a DataFrame
Pandas provides a read_sql() method that reads an SQL query into a DataFrame
For example, loading the instructor table into a DataFrame:
68 Roi Yehoshua, 2022
Loading Data into a DataFrame
You can use the params argument to pass a list of parameters to the query
For example, the following displays all the students that took the course CS-319
69 Roi Yehoshua, 2022
Recursive Queries
SQL:1999 permits recursive view definition, where a view (or temporary view) is
expressed in terms of itself
Example: Find which courses are a prerequisite, whether directly or indirectly, for a
specific course
with recursive rec_prereq(course_id, prereq_id) as (
select course_id, prereq_id
from prereq
union
select rec_prereq.course_id, prereq.prereq_id
from rec_rereq, prereq
where rec_prereq.prereq_id = prereq.course_id
)
select ∗
from rec_prereq
70 Roi Yehoshua, 2022
Recursive Queries
Any recursive view must be defined as the union of two subqueries:
a base query that is nonrecursive
a recursive query that uses the recursive view
First compute the base query and add all the resultant tuples to the recursively
defined view relation rec_prereq (which is initially empty)
Next compute the recursive query using the current contents of the view relation,
and add all the resulting tuples back to the view relation
Keep repeating the above step until no new tuples are added to the view relation
71 Roi Yehoshua, 2022
Recursive Queries
72 Roi Yehoshua, 2022
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
Query optimization involves the following steps
Generate logically equivalent expressions to the query operators
Annotate resultant expressions in alternative ways to generate alternative query plans
Choose the cheapest plan based on estimated cost
Estimation of plan cost is based on:
Statistical information about relations (e.g., number of tuples, number of distinct values)
Cost of different algorithms (e.g., join vs. subquery), computed using statistics
Sometimes we need to manually examine and tune the optimizer plans
73 Roi Yehoshua, 2022
Viewing Query Execution Plans
Most DBs can show you their execution plans using explain <query>
74 Roi Yehoshua, 2022
Viewing Query Execution Plans
To view the execution plan visually, select Execution Plan within the query results tab
75 Roi Yehoshua, 2022
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?
76 Roi Yehoshua, 2022
Authorization
SQL allows you to set the user privileges for specific data objects or actions
The grant statement is used to grant specific privileges to users or groups of users
grant <privilege list> on <relation or view> to <user list>
<privilege list> may include select, insert, update, delete or all privileges
<user list> may include:
user ids
user roles
public, which allows all valid users the privilege granted
Examples:
grant select on department to Amit, Satoshi
grant update(budget) on department to Amit, Satoshi
79 Roi Yehoshua, 2022
Roles
A role defines a group of users
Roles can be created with the create role command
Example: create role instructor
To assign users to a specific role use:
grant <role> to <users>
Example:
grant instructor to Amit
Then privileges can be granted to the role:
grant select on takes to instructor
80 Roi Yehoshua, 2022
Revoking Privileges
The revoke statement is used to revoke authorization
revoke <privilege list> on <relation or view> from <user list>
<privilege-list> may be all to revoke all privileges the user may hold
Examples:
revoke select on department from Amit, Satoshi
revoke update(budget) on department from Amit, Satoshi
81 Roi Yehoshua, 2022