SQL: Queries, Constraints, Triggers - PowerPoint PPT Presentation

1 / 41
About This Presentation
Title:

SQL: Queries, Constraints, Triggers

Description:

Triggers are actions executed by DBMS whenever changes to the database meet ... stands for any one character and `%' stands for 0 or more arbitrary characters ... – PowerPoint PPT presentation

Number of Views:106
Avg rating:3.0/5.0
Slides: 42
Provided by: RaghuRamak217
Category:

less

Transcript and Presenter's Notes

Title: SQL: Queries, Constraints, Triggers


1
SQL Queries, Constraints, Triggers
  • Chapter 5

2
Overview aspects of SQL
  • DML Data Management Language. Pose queries (Ch.
    5) and insert, delete, modify rows (Ch. 3)
  • DDL Data Definition Language. Creation, deletion
    and modification of tables or views (Ch. 3)
  • Triggers and Advanced Integrity Constraints (Ch
    5)
  • Triggers are actions executed by DBMS whenever
    changes to the database meet conditions specified
    in the trigger
  • Embedded and dynamic SQL (Ch. 6). Allow SQL to be
    called from a host language such as C
  • Client-server execution and remote database
    access (Ch. 7)
  • Transaction management (Ch. 21)
  • Security
  • Advanced features
  • OO, recursive, decision support, data mining,
    spatial data, text, XML

3
Example Instances
  • Sailors(sid integer, sname string, rating
    integer, age real)
  • Boats(bid integer, bname string, color string)
  • Reserves(sid integer, bid integer, day date)

4
enter data into MySql
  • Schema on p133, tables on p134
  • create table sailors(sid integer, sname char(20),
    rating integer, age real, primary key(sid))
  • create table boats(bid integer, bname char(20),
    color char(20), primary key(bid))
  • create table reserves(sid integer, bid integer,
    day date, primary key(sid,bid,day), foreign
    key(sid) references sailors, foreign key(bid)
    references boats)
  • insert into sailors(sid,sname,rating,age)
    values(22,"Dustin",7,45)
  • insert into boats(bid,bname,color)
    values(101,"Interlake","blue")
  • insert into reserves(sid,bid,day)
    values(22,101,"1998-10-10")

5
Basic SQL Query
SELECT DISTINCT select-list FROM
from-list WHERE qualification
  • from-list A list of relation names, specifying a
    cross-product of tables
  • select-list A list of attributes of relations in
    from-list, specifying columns to be retained in
    the result
  • qualification optional. Specifies select
    conditions on the tables mentioned in the
    from-list
  • DISTINCT optional keyword indicating that the
    answer should not contain duplicates
  • By default duplicates are not eliminated

6
Conceptual Evaluation Strategy
  • Semantics of an SQL query defined in terms of
    the following conceptual evaluation strategy
  • Compute the cross-product of from-list
  • Discard resulting tuples if they fail
    qualifications
  • Delete attributes that are not in select-list
  • If DISTINCT is specified, eliminate duplicate
    rows
  • This strategy is probably the least efficient way
    to compute a query
  • optimizer will find more efficient strategies to
    compute the same answers

7
Example of Conceptual Evaluation
SELECT S.sname FROM Sailors S, Reserves
R WHERE S.sidR.sid and R.bid103
select from Sailors, Reserves select from
Sailors S, Reserves R where S.sidR.sid and
R.bid103 select S.sname from Sailors S,
Reserves R where S.sidR.sid and R.bid103
8
A Note on Range Variables
  • from-list table name can be followed by a range
    variable
  • select-list column name can be prefixed by a
    range variable
  • When is it necessary?
  • Really needed only if the same relation appears
    twice in from-list. Previous query can also be
    written as
  • However, as a good style, use range variables
    always!

SELECT sname FROM Sailors, Reserves WHERE
Sailors.sidReserves.sid AND
bid103
9
Some examples
  • Find the sids of sailors whove reserved a red
    boat
  • Find the names of sailors whove reserved a red
    boat
  • Find the colors of boats reserved by Lubber

SELECT R.sid FROM Boats B, Reserves R WHERE
B.bidR.bid and B.colorred
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid and B.bidR.bid and
B.colorred
SELECT B.color FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid and B.bidR.bid and
S.nameLubber
10
Find sailors whove reserved at least one boat
SELECT S.sid FROM Sailors S, Reserves R WHERE
S.sidR.sid
  • Would adding DISTINCT to this query make a
    difference?
  • What is the effect of replacing S.sid by S.sname
    in the SELECT clause? Would adding DISTINCT to
    this variant of the query make a difference?

11
Expressions and Strings
SELECT S.age, age1S.age-5, 2S.age AS age2 FROM
Sailors S WHERE S.sname LIKE B_B
  • Illustrates use of arithmetic expressions and
    string pattern matching Find triples (of ages
    of sailors and two fields defined by expressions)
    for sailors whose names begin and end with B and
    contain at least three characters.
  • AS and are two ways to name fields in result
  • LIKE is used for string matching. _ stands for
    any one character and stands for 0 or more
    arbitrary characters

12
Find names of sailors whove reserved a red or a
green boat
  • UNION Can be used to compute the union of any
    two union-compatible sets of tuples (which are
    themselves the result of SQL queries)

SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid
AND (B.colorred OR B.colorgreen)
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred UNION SELECT
S2.sname FROM Sailors S2, Boats B2, Reserves
R2 WHERE S2.sidR2.sid AND R2.bidB2.bid
AND B2.colorgreen
13
Find names of sailors whove reserved a red and a
green boat
SELECT S.sname FROM Sailors S, Boats B1,
Reserves R1, Boats B2, Reserves
R2 WHERE S.sidR1.sid AND R1.bidB1.bid AND
S.sidR2.sid AND R2.bidB2.bid AND
(B1.colorred AND B2.colorgreen)
  • INTERSECT Can be used to compute the
    intersection of any two union-compatible sets of
    tuples
  • Included in the SQL/92 standard, but some systems
    dont support it
  • Upper query hard to understand, more intuitive to
    use intersect
  • Problem?

SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid
AND B.colorred INTERSECT SELECT
S2.sname FROM Sailors S2, Boats B2, Reserves
R2 WHERE S2.sidR2.sid AND R2.bidB2.bid
AND B2.colorgreen
14
Find sids of sailors whove reserved red but not
green boats
SELECT R.sid FROM Boats B, Reserves R WHERE
R.bidB.bid AND B.colorred EXCEPT SELECT
R2.sid FROM Boats B2, Reserves R2 WHERE
R2.bidB2.bid AND B2.colorgreen
15
Find all sids of sailors who have a rating of 10
or reserved boat 104
  • any two union-compatible sets of tuples
  • For UNION, INTERSECTION and EXCEPT, by default
    duplicates are eliminated

SELECT S.sid FROM Sailors S WHERE S.rating
10 UNION SELECT R.sid FROM Reserves R WHERE
R.bid104
16
Nested Queries
Find names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid
FROM Reserves R
WHERE R.bid103)
  • Powerful feature of SQL a WHERE clause can
    itself contain an SQL query! (Actually, so can
    FROM and HAVING clauses.)
  • Why can be nested?
  • IN tests whether a value is in a set
  • To find sailors whove not reserved 103, use NOT
    IN.

17
Nested Queries
Find names of sailors whove reserved a red boat
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid
FROM Reserves R
WHERE R.bid IN (SELECT B.bid
FROM Boats B WHERE B.colorred )
  • Find names of sailors who have not reserved a red
    boat
  • How about replace both to NOT IN

18
Nested Queries with Correlation
Find names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE EXISTS
(SELECT FROM
Reserves R WHERE
R.bid103 AND S.sidR.sid)
  • EXISTS is another set comparison operator, like
    IN
  • Tests whether a set is non-empty
  • NOT EXISTS tests whether a set is empty
  • If UNIQUE is used, and is replaced by R.bid,
    finds sailors with at most one reservation for
    boat 103
  • UNIQUE checks for duplicate tuples
  • Why do we have to replace by R.bid?

19
Correlated vs. Non-correlated
  • Two types of sub query
  • correlated
  • non-correlated.
  • Difference
  • A correlated subquery is evaluated once per row
    processed by the parent query
  • A non-correlated subquery is only executed once
    and the result set is kept in memory (if the
    result set is small)

20
More on Set-Comparison Operators
  • Weve already seen IN, EXISTS and UNIQUE. Can
    also use NOT IN, NOT EXISTS and NOT UNIQUE
  • Also available op ANY, op ALL
  • op
  • Find sailors whose rating is greater than some
    sailor called Horatio
  • Find sailors whose rating is greater than every
    sailor called Horatio

SELECT FROM Sailors S WHERE S.rating gt ANY
(SELECT S2.rating
FROM Sailors S2
WHERE S2.snameHoratio)
21
Find the sailors with the highest rating
  • IN equivalent to ANY
  • NOT IN equivalent to ltgt ALL

SELECT FROM Sailors S WHERE S.rating gt ALL
(SELECT S2.rating
FROM Sailors S2)

22
Rewriting INTERSECT Queries Using IN
Find the names of sailors whove reserved both a
red and a green boat
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sidR.sid AND R.bidB.bid AND
B.colorred AND S.sid IN (SELECT
S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sidR2.sid
AND R2.bidB2.bid
AND B2.colorgreen)
  • Similarly, EXCEPT queries re-written using NOT
    IN.

23
Using INTERSECT
Find names of sailors whove 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.bidB.bid AND B.colorred) INTERSEC
T (SELECT R2.sid FROM Boats B2, Reserves
R2 WHERE R2.bidB2.bid AND B2.colorgreen))
24
Division in SQL
Find sailors whove reserved all boats
SELECT S.sname FROM Sailors S WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B) EXCEPT
(SELECT R.bid FROM
Reserves R WHERE R.sidS.sid))
25
Aggregate Operators
  • Significant extension of relational algebra
  • In addition to simply retrieving data, we often
    want to perform some computation and summarization

COUNT () num of tuples COUNT ( DISTINCT A)
num of (unique) values in column A SUM (
DISTINCT A) sum of all (unique) values in
column A AVG ( DISTINCT A) average of
(unique) values in column A MAX (A) max value
in column A MIN (A) min value in column A
26
Aggregate Examples
SELECT COUNT () FROM Sailors S
SELECT COUNT (DISTINCT S.sname) FROM Sailors S
SELECT AVG (S.age) FROM Sailors S WHERE
S.rating10
SELECT S.sname, MAX (S.age) FROM Sailors
S Illegal! If the SELECT clause uses an
aggregate, then it must use only aggregate unless
the query contains GROUP BY
SELECT S.sname, S.age FROM Sailors S WHERE
S.age (SELECT MAX(S2.age)
FROM Sailors S2)
27
Find the sailors with the highest rating
SELECT FROM Sailors S WHERE S.rating gt ALL
(SELECT S2.rating
FROM Sailors S2)

SELECT FROM Sailors S WHERE S.rating gt
(SELECT MAX (S2.rating)
FROM Sailors S2)

28
GROUP BY and HAVING
  • So far, weve applied aggregate operators to all
    (qualifying) tuples. Sometimes, we want to apply
    them to each of several groups of tuples
  • Find the age of the youngest sailor for each
    rating level
  • In general, we dont know how many rating levels
    exist, and what the rating values for these
    levels are
  • Suppose we know that rating values go from 1 to
    10 we can write 10 queries that look like this

For i 1, 2, ... , 10
SELECT MIN (S.age) FROM Sailors S WHERE
S.rating i
29
Queries With GROUP BY and HAVING
SELECT DISTINCT select-list FROM
from-list WHERE qualification GROUP BY
grouping-list HAVING group-qualification
SELECT S.rating, MIN (S.age) FROM Sailors
S GROUP BY S.rating
  • The select-list contains (i) column names (ii)
    terms with aggregate operations (e.g., MIN
    (S.age))
  • The column names in (i) must appear in the
    grouping-list
  • Intuitively, each answer tuple corresponds to a
    group, and these attributes must have a single
    value per group
  • Aggregates always return a single value
  • A group is a set of tuples having the same value
    for all attributes in grouping-list
  • If not subset, i.e., a column appears in list (i)
    but not grouping list, there can be multiple rows
    within a group that have different values in this
    column, and it is not clear what value should be
    assigned to this column in an answer row
  • If GROUP BY is omitted, entire table is
    considered as a single group

30
Conceptual Evaluation
  • The cross-product of from-list is computed,
    tuples that fail qualification are discarded,
    unnecessary fields are deleted, and the
    remaining tuples are partitioned into groups by
    the value of attributes in grouping-list
  • The group-qualification is then applied to
    eliminate some groups. Expressions in
    group-qualification must have a single value per
    group!
  • One answer tuple is generated per qualifying
    group
  • If DISTINCT, duplicates are eliminated

31
Find the age of the youngest sailor with age
18, for each rating with at least 2 such sailors
SELECT S.rating, MIN (S.age) FROM Sailors
S WHERE S.age gt 18 GROUP BY S.rating HAVING
COUNT () gt 1
  • Only S.rating and S.age are mentioned in the
    SELECT, GROUP BY or HAVING clauses other
    attributes unnecessary
  • Use AS to rename 2nd column
  • SELECT S.rating, MIN (S.age) AS minage

32
More examples of aggregate queries
Find the age of the youngest sailor with age gt
18, for each rating with at least 2 sailors (of
any age)
SELECT S.rating, MIN (S.age) FROM Sailors
S WHERE S.age gt 18 GROUP BY S.rating HAVING 1
lt (SELECT COUNT ()
FROM Sailors S2 WHERE
S.ratingS2.rating)
  • Shows HAVING clause can also contain a subquery
  • Compare this with the query where we considered
    only ratings with at least 2 sailors over 18

33
For each red boat, find the number of
reservations for this boat
SELECT B.bid, COUNT () AS reservationcount FROM
Boats B, Reserves R WHERE R.bidB.bid AND
B.colorred GROUP BY R.bid
34
Find those ratings for which the average age is
the minimum over all ratings
SELECT Temp.rating, Temp.avgage FROM (SELECT
S.rating, AVG (S.age) AS avgage FROM
Sailors S GROUP BY S.rating) AS
Temp WHERE Temp.avgage (SELECT MIN
(Temp.avgage)
FROM Temp)
Not supported by MySql! How to solve? How about
Oracle?
SELECT Temp.rating, Temp.avgage FROM (SELECT
S.rating, AVG (S.age) AS avgage FROM
Sailors S GROUP BY S.rating) AS
Temp WHERE Temp.avgage (SELECT MIN
(Temp2.avgage2)
FROM (SELECT S2.rating, AVG (S2.age)
AS avgage2
FROM Sailors S2 GROUP BY
S2.rating) AS Temp2)
35
Null Values
  • Field values in a tuple are sometimes unknown
    (e.g., a rating has not been assigned) or
    inapplicable (e.g., no spouses name)
  • SQL provides a special value null for such
    situations
  • The presence of null complicates many issues
  • Special operators needed to check if value is/is
    not null
  • Is ratinggt8 true or false when rating is equal to
    null?
  • We need a 3-valued logic (true, false and
    unknown)
  • Meaning of constructs must be defined carefully
    (e.g., WHERE clause eliminates rows that dont
    evaluate to true)
  • NOT NULL
  • Implicit for each field in PRIMARY KEY constraint

36
Integrity Constraints (Review)
  • An IC describes conditions that every legal
    instance of a relation must satisfy
  • Inserts/deletes/updates that violate ICs are
    disallowed
  • Can be used to ensure application semantics
    (e.g., sid is a key), or prevent inconsistencies
    (e.g., sname has to be a string, age must be lt
    200)
  • Types of ICs Domain constraints, primary key
    constraints, foreign key constraints, general
    constraints (table constraints, assertions)

37
Table Constraints
CREATE TABLE Sailors ( sid INTEGER, sname
CHAR(10), rating INTEGER, age REAL, PRIMARY
KEY (sid), CHECK ( rating gt 1 AND rating
lt 10 )
Rating must be in the range of 1 to 10
CREATE TABLE Reserves ( sid
INTEGER bid INTEGER, day
DATE, PRIMARY KEY (sid,bid,day),
FOREIGN KEY (sid) REFERENCES Sailors
FOREIGN KEY (bid) REFERENCES Boats CONSTRAINT
noInterlakeRes CHECK (Interlake ltgt (
SELECT B.bname FROM Boats B WHERE
B.bidReserves.bid)))
Interlake boats cannot be reserved
  • Can use queries to express constraint
  • Can be named

38
Assertions
  • Constraints over multiple tables
  • Not associated with a single table as table
    constraints

Number of boats plus number of sailors is lt 100
CREATE ASSERTION smallClub CHECK ( (SELECT
COUNT (S.sid) FROM Sailors S) (SELECT COUNT
(B.bid) FROM Boats B) lt 100 )
39
Triggers
  • Trigger procedure that starts automatically if
    specified changes occur to the DBMS
  • Three parts
  • Event (activates the trigger)
  • Condition (tests whether the triggers should run)
  • Action (what happens if the trigger runs)
  • Useful in practice
  • MySql 5.0 new feature

40
Summary
  • SQL was an important factor in the early
    acceptance of the relational model more natural
    than earlier, procedural query languages
  • Relationally complete in fact, significantly
    more expressive power than relational algebra
  • Many alternative ways to write a query optimizer
    should look for most efficient evaluation plan
  • In practice, users need to be aware of how
    queries are optimized and evaluated for best
    results

41
Summary (Contd.)
  • NULL for unknown field values brings many
    complications
  • SQL allows specification of rich integrity
    constraints
  • Triggers respond to changes in the database
Write a Comment
User Comments (0)
About PowerShow.com