|
|
SQL:Queries,Triggers
Chapter Outline
- Data Manipulation Language (DML)
- SELECT statement
- SELECT statement – DISTINCT
- SELECT statement > 1 table
- SELECT statement – calculated field
- SELECT statement – BETWEEN
- SELECT statement – IN
- SELECT statement - LIKE
- SELECT statement – ORDER BY
- SELECT statement – UNION, INTERSECT, EXCEPTION
- SELECT statement – nested queries
- SELECT statement – aggregate operators
- SELECT statement – GROUP BY
- SELECT statement – OUTER JOIN
- Triggers
Data Manipulation Language
Allows users to pose queries and to insert, delete and update rows.
We already talked about insert, delete, and update in Chapter 3.
We will study SELECT statement in more details in this chapter.
Table definitions
For examples in this chapter, we use
Sailors(sid:integer, sname:string, rating:integer, age:real)
Boats(bid:integer, bname:string, color:string)
Reserves(sid:integer, bid:integer, day:date)
SELECT statement
SELECT [DISTINCT|ALL] {*|column_expression [AS new_name]][,…]}
FROM table_name [alias][,…]
[WHERE condition]
[GROUP BY column_list][HAVING condition]
[ORDER BY column_list];
*** 1. [ ] means optional and {} means required
2. condition : expression op expression (op -> comparison operators)
use logical connectives AND, OR, NOT to combine expressions
expression is a column name, a constant, or an (arithmetic or string) expression
SELECT statement – DISTINCT
DISTINCT -> eliminates duplicate rows
ex SELECT S.sname, S.age
FROM Sailors S; (alias)
ex SELECT DISTINCT S.sname, S.age
FROM Sailors S;
SELECT statement - >1 tables
Find the names of sailors who have reserved boat number 103
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = 103;
SELECT statement
-Calculated fields
Ex Compute increments for the ratings of persons who have sailed two different boats on the same day
SELECT S.sname, S.rating+1 AS rating
FROM Sailors S, Reserves R1, Reserves R2
WHERE S.sid = R1.sid AND S.sid = R2.sid
AND R1.day = R2.day AND R1.bid <> R2.bid;
(AS -> rename)
SELECT statement – BETWEEN
Range search condition (BETWEEN / NOT BETWEEN)
ex SELECT S.sname
FROM Sailors S
WHERE S.rating BETWEEN 7 AND 10;
easier than writing
WHERE S.rating >= 7 AND S.rating <= 10
SELECT statement – IN
Set membership search condition(IN/NOT IN)
ex SELECT S.sname
FROM Sailors S
WHERE S.rating IN (7, 10);
easier than writing
WHERE S.rating = 7 OR S.rating = 10
SELECT statement – LIKE
Pattern match search condition (LIKE / NOT LIKE)
% represents any sequence of zero or more characters (wildcard)
_ represents any single character
ex address LIKE ‘H%’
‘H_ _ _’
‘%e’
‘%Bangkok%’
NOT LIKE ‘H%’
SELECT statement – NULL
Null search condition (IS NULL/IS NOT NULL)
ex SELECT S.sname
FROM Sailors S
WHERE S.age IS NULL;
SELECT statement - ORDER BY
Sorting results (ORDER BY clause)
The ORDER BY clause consists of a list of column identifiers that the result is to be sorted on, separated by commas.
ASC (ascending), DESC (descending)
field to be ORDERED must be in the SELECT list
null --> up to the DBMS
Ex SELECT S.sname, S.rating, S.age
FROM Sailors S
ORDER BY S.rating;
SELECT S.sname, S.rating, S.age
FROM Sailors S
ORDER BY S.rating, S.age DESC;
SELECT statement - UNION, INTERSECT, EXCEPT
Combining result tables (UNION, INTERSECT, EXCEPT)
two tables are union compatible
eliminate duplicate rows
use ALL to show duplicate rows (UNION ALL, …)
ex Find the names of sailors who have reserved a red or a green boat
ex Find the names of sailors who have reserved both a red and a green boat
ex Find the sids of all sailors who have reserved red boats but not green boats
ex Find all sids of sailors who have a rating of 10 or reserved boat 104
SELECT statement-Nested queries
A nested query is a query that has another query embeded within it. The embeded query is called a subquery. It appears as part of the main query.
A subquery can appears within the WHERE clause, and sometimes appear in the FROM clause and HAVING clause.
Ex Find the names of sailors who have reserved boat 103
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = 103;
Use nested query
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid = 103);
How it computes?
use = instead of IN if the subquery returns a single value
Ex Find the names of sailors who have reserved a red boat
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid
AND B.color = ‘red’;
Write a nested query for this example
Write a nested query for this example (have not reserved a red boat)
These are examples of nested queries that inner subquery is independent of the outer query
The inner subquery depends on the row currently being examined in the outer query. So, the subquery must be recomputed for each outer row.
EXISTS and NOT EXISTS
EXISTS is true if and only if there exists at least one row in the result table returned by the subquery; it is false if the subquery returns an empty result table.
Use SELECT * to check whether a qualifying row exists. Do not really want to retrieve any columns from the row.
Ex Find the names of sailors who have reserved boat 103
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid = 103
AND R.sid = S.sid);
- S.sid is called a correlation
- Keywords ANY (or SOME) and ALL
- ALL - the condition will only be true if it is satisfied by all values produced by the subquery
- ANY - the condition will only be true if it is satisfied by any (one or more) values produced by the subquery
Ex Find sailors whose rating is better than some sailor called Horatio.
SELECT S.sid
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = ‘Horatio’);
- change some to every --> change ANY to ALL
- Find the results from the example instance.
Ex Find sailors with the highest rating
SELECT S.sid
FROM Sailors S
WHERE S.rating >= ALL (SELECT S2.rating
FROM Sailors S2);
Ex Find the names of sailors who have reserved both a red and a green boat.
SELECT S.sname
FROM Sailors S
WHERE S.sid IN ((SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND B.color = ‘red’)
INTERSECT
(SELECT R2.sid
FROM Boats B2, Reserves R2
WHERE R2.bid = B2.bid AND B2.color = ‘green’));
- If the DBMS does not support INTERSECT
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’
AND S.sid IN (SELECT S2.sid
FROM Sailors S2, Reserves R2, Boats B2
WHERE S2.sid = R2.sid
AND R2.bid = B2.bid
AND B2.color = ‘green’);
SELECT statement - Aggregate operators
COUNT ([DISTINCT] A)
SUM ([DISTINCT] A)
AVG ([DISTINCT] A)
MAX (A)
MIN (A)
note that an aggregate function can be used only in the SELECT list and in the HAVING clause
except for COUNT(*), every function eliminates null values before computing
Ex Find the average age of all sailors
SELECT AVG(S.age)
FROM Sailors S;
Ex Count the number of sailors
SELECT COUNT(*)
FROM Sailors S;
Ex Count the number of different sailor names
SELECT COUNT(DISTINCT S.sname)
FROM Sailors S;
Ex Find the name of sailors who are older than the oldest sailor with a rating of 10
SELECT S.sname
FROM Sailors S
WHERE S.age > (SELECT MAX(S2.age)
FROM Sailors S2
WHERE S2.rating = 10);
- Can we write this statement using keyword ALL?
SELECT statement - GROUP BY
Ex Find the age of the youngest sailor for each rating level
SELECT S.rating, MIN (S.age)
FROM Sailors S
GROUP BY S.rating;
- Every column that appears in SELECT must also appear in GROUP BY
Ex Find the age of the youngest sailor who is eligible to vote (at least 18 years old) for each rating level with at least two such sailors
SELECT S.rating, MIN(S.age) AS minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT(*) > 1;
Ex For each red boat, find the number of reservations for this boat
SELECT B.bid, COUNT(*) AS reservationcount
FROM Boats B, Reserves R
WHERE B.bid = R.bid AND B.color = ‘red’
GROUP BY B.bid;
- Can we write this way?
WHERE B.bid = R.bid
GROUP BY B.bid
HAVING B.color = ‘red’
Ex Find the average age of sailors for each rating level that has at least two sailors.
SELECT S.rating, AVG(S.age) AS avgage
FROM Sailors S
GROUP BY S.rating
HAVING COUNT(*) > 1;
- Write in the other way using nested query in HAVING clause
SELECT statement - Outer joins
- To keep some tuples that do not match in the join condition to show in the result with null values
- left / right / full outer join
Ex List whether each sailor reserves boats
SELECT S.sid, R.bid
FROM Sailors S NATURAL LEFT OUTER JOIN Reserves R
Trigger
- A trigger is a procedure that is automatically invoked by the DBMS in response to specified changes to the database
- A trigger description contains 3 parts :
- Event : A change to the database that activates the trigger
- Condition : A query or test that is run when the trigger is activated
- Action : A procedure that is executed when the trigger is activated and its condition is true
Ex CREATE TRIGGER init_count BEFORE INSERT ON Students /*Event*/
DECLARE
count INTEGER;
BEGIN /*Action*/
count := 0;
END
CREATE TRIGGER incr_count AFTER INSERT ON Students /*Event*/
WHEN (new.age < 18)
/*Condition; ‘new’ is just-inserted tuple*/
FOR EACH ROW
BEGIN /*Action*/
count := count + 1 ;
END
|