CREATE TABLE Sailors sid INTEGER NOT NULL sname

ﺟﺪﻭﻝ ﻗﺎیﻘﺮﺍﻥ ﻫﺎ CREATE TABLE "Sailors" ( sid INTEGER NOT NULL, sname CHARACTER VARYING(30), age INTEGER, rating INTEGER, PRIMARY KEY (sid ) )

ﺟﺪﻭﻝ ﻗﺎیﻖ ﻫﺎ CREATE TABLE "Boats" ( bid INTEGER NOT NULL, color CHARACTER VARYING(10), bname CHARACTER VARYING(10), PRIMARY KEY (bid ) )

( ﺟﺪﻭﻝ ﺭﺯﺭﻭ )ﺍﺭﺗﺒﺎﻁ ﻗﺎیﻘﺮﺍﻥ ﻫﺎ ﻭ ﻗﺎیﻖ ﻫﺎ CREATE TABLE "Reserves" ( sid INTEGER NOT NULL, bid INTEGER NOT NULL, date DATE NOT NULL, PRIMARY KEY (sid , bid , date ), FOREIGN KEY (bid) REFERENCES "Boats" (bid), FOREIGN KEY (sid) REFERENCES "Sailors" (sid) )

ﺍﺳﺘﺮﺍﺗژی ﺍﺭﺯیﺎﺑی ﻣﻔﻬﻮﻣی SELECT FROM WHERE S. sname Sailors S, Reserves R S. sid=R. sid AND R. bid=103

A Note on Range Variables ﺯﻣﺎﻧی کﻪ ﺩﻭ ﺭﺍﺑﻄﻪ ﺻﻔﺖ ﻫﺎی ﻫﻢ ﻧﺎﻡ ﺩﺍﺷﺘﻪ ﺑﺎﺷﻨﺪ یﺎ یک ﺭﺍﺑﻄﻪ آﻤﺪﻩ ﺑﺎﺷﺪ FROM چﻨﺪ ﺑﺎﺭ ﺩﺭ ﻗﺴﻤﺖ OR SELECT FROM WHERE S. sname Sailors S, Reserves R S. sid=R. sid AND bid=103 SELECT FROM WHERE sname Sailors, Reserves Sailors. sid=Reserves. sid AND bid=103 � It is good style, however, to use range variables always!

Find sailors who’ve reserved at least one boat SELECT S. sname FROM Sailors S, Reserves WHERE S. sid=R. sid � Would R 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?

Find sid’s of sailors who’ve reserved a red or a green boat Can be used to compute the union of any two unioncompatible sets of tuples (which are themselves the result of SQL queries). � If we replace OR by AND in the first version, what do we get? � Also available: EXCEPT (What do we get if we replace UNION by EXCEPT? ) � UNION: SELECT S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid=R. sid AND R. bid=B. bid AND (B. color=‘red’ OR B. color=‘green’) SELECT S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid=R. sid AND R. bid=B. bid AND B. color=‘red’ UNION SELECT S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid=R. sid AND R. bid=B. bid AND B. color=‘green’

Find sid’s of sailors who’ve reserved a red and a green boat Can be used Boats B 2, Reserves R 2 to compute the WHERE S. sid=R 1. sid AND R 1. bid=B 1. bid intersection of any two AND S. sid=R 2. sid AND R 2. bid=B 2. bid union-compatible sets AND B 1. color=‘red’ AND B 2. color=‘green’ of tuples. Key field! � Included in the SQL/92 SELECT S. sid FROM Sailors S, Boats B, Reserves R standard, but some systems don’t support it. WHERE S. sid=R. sid AND R. bid=B. bid AND B. color=‘red’ � Contrast symmetry of INTERSECT the UNION and INTERSECT SELECT S. sid queries with how much FROM Sailors S, Boats B, Reserves R the other versions differ. WHERE S. sid=R. sid AND R. bid=B. bid � INTERSECT: SELECT S. sid FROM Sailors S, Boats B 1, Reserves R 1, AND B. color=‘green’

(Nested Query) پﺮﺱ ﻭ ﺟﻮی ﺗﻮﺩﺭﺗﻮ Find names of sailors who’ve reserved boat #103: �A SELECT S. sname FROM Sailors S WHERE S. sid IN (SELECT R. sid FROM Reserves R WHERE R. bid=103) very powerful feature of SQL: a WHERE clause can itself contain an SQL query! (Actually, so can FROM and HAVING clauses. ) � To find sailors who’ve not reserved #103, use NOT IN. � To understand semantics of nested queries, think of a nested loops evaluation: For each Sailors tuple, check the qualification by computing the subquery.

Nested Queries with Correlation Find names of sailors who’ve reserved boat #103: SELECT S. sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R. bid=103 AND S. sid=R. sid) � EXISTS is another set comparison operator, like IN. � 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; * denotes all attributes. Why do we have to replace * by R. bid? ) � Illustrates why, in general, subquery must be recomputed for each Sailors tuple.

More on Set-Comparison Operators � We’ve already seen IN, EXISTS and UNIQUE. Can also use NOT IN, NOT EXISTS and NOT UNIQUE. � Also available: op ANY, op ALL, op IN � Find sailors whose rating is greater than that of some sailor called Horatio: SELECT * FROM Sailors S WHERE S. rating > ANY (SELECT S 2. rating FROM Sailors S 2 WHERE S 2. sname=‘Horatio’)

Rewriting INTERSECT Queries Using IN Find sid’s of sailors who’ve reserved both a red and a green boat: SELECT S. sid FROM Sailors S, Boats B, Reserves R WHERE S. sid=R. sid AND R. bid=B. bid AND B. color=‘red’ AND S. sid IN (SELECT S 2. sid FROM Sailors S 2, Boats B 2, Reserves R 2 WHERE S 2. sid=R 2. sid AND R 2. bid=B 2. bid AND B 2. color=‘green’) � Similarly, EXCEPT queries re-written using NOT IN. find names (not sid’s) of Sailors who’ve reserved both red and green boats, just replace S. sid by S. sname in SELECT clause. (What about INTERSECT query? ) � To

Division in SQL (1) Find sailors who’ve reserved all boats. � Let’s do it the hard way, without EXCEPT: SELECT S. sname FROM Sailors S WHERE NOT EXISTS ((SELECT B. bid FROM Boats B) EXCEPT (SELECT R. bid FROM Reserves R WHERE R. sid=S. sid)) (2) SELECT S. sname FROM Sailors S WHERE NOT EXISTS (SELECT B. bid FROM Boats B WHERE NOT EXISTS (SELECT R. bid Sailors S such that. . . FROM Reserves R WHERE R. bid=B. bid there is no boat B without. . . AND R. sid=S. sid)) a Reserves tuple showing S reserved B

ﻋﻤگﺮﻫﺎی ﺗﺠﻤیﻊ COUNT (*) Aggregate COUNT ( [DISTINCT] A) Operators SUM ( [DISTINCT] A) ﺍﻓﺰﺍیﺶ چﺸﻤگیﺮ ﺩﺭ ﻗﺎﺑﻠیﺖﻫﺎی ﺟﺒﺮ ﺭﺍﺑﻄﻪ ﺍی SELECT COUNT (*) FROM Sailors S SELECT AVG (S. age) FROM Sailors S WHERE S. rating=10 � AVG ( [DISTINCT] MAX (A) MIN (A) A) �� SELECT S. sname FROM Sailors S WHERE S. rating= (SELECT MAX(S 2. rating) FROM Sailors S 2) SELECT COUNT (DISTINCT FROM Sailors S WHERE S. sname=‘Bob’ S. rating) SELECT AVG ( DISTINCT S. age) FROM Sailors S WHERE S. rating=10

Conceptual Evaluation cross-product of relation-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 groupqualification must have a single value per group! � The ◦ In effect, an attribute in group-qualification that is not an argument of an aggregate op also appears in groupinglist. (SQL does not exploit primary key semantics here!) � One answer tuple is generated per qualifying group.

Find age of the youngest sailor with age>=18, for each rating with at least 2 such sailors.

Find age of the youngest sailor with age>=18, for each rating with at least 2 such sailors and with every sailor under 60. HAVING COUNT (*) > 1 AND EVERY (S. age <=60) What is the result of changing EVERY to ANY?

Find age of the youngest sailor with age>=18, for each rating with at least 2 sailors between 18 and 60. S. rating, MIN (S. age) AS minage FROM Sailors S WHERE S. age >= 18 AND S. age <= 60 GROUP BY S. rating HAVING COUNT (*) > 1 SELECT Answer relation: Sailors instance:

For each red boat, find the number of reservations for this boat SELECT B. bid, COUNT (*) AS scount FROM Sailors S, Boats B, Reserves R WHERE S. sid=R. sid AND R. bid=B. bid AND GROUP BY B. bid � Grouping B. color=‘red’ over a join of three relations. � What do we get if we remove B. color=‘red’ from the WHERE clause and add a HAVING clause with this condition? � What if we drop Sailors and the condition involving S. sid?

Find age of the youngest sailor with age > 18, for each rating with at least 2 sailors (of any age) SELECT S. rating, MIN (S. age) FROM Sailors S WHERE S. age > 18 GROUP BY S. rating HAVING 1 < (SELECT COUNT (*) FROM Sailors S 2 WHERE S. rating=S 2. rating) ( ﺷﻮﺩ subquery) ﻣی ﺗﻮﺍﻧﺪ ﺷﺎﻣﻞ ﺯیﺮ پﺮﺱﻭﺟﻮ HAVING ﻗﺴﻤﺖ ﺭﺍ ﺑﺎ ﻋﺒﺎﺭﺕ ﺯیﺮ ﺟﺎیگﺰیﻦ کﻨیﻢ چﻪ ﺍﺗﻔﺎﻕ ﺧﻮﺍﻫﺪ HAVING ﺍگﺮ ﻗﺴﻤﺖ : ﺍﻓﺘﺎﺩ HAVING COUNT(*) >1 ◦ � �

Find those ratings for which the average is the minimum over all ratings : ﺍﺷﺘﺒﺎﻩ. ﻋﻤﻠیﺎﺕ ﺗﺠﻤیﻊ ﻧﻤیﺗﻮﺍﻧﺪ ﺗﻮ ﺩﺭ ﺗﻮ ﺍﺳﺘﻔﺎﺩﻩ گﺮﺩﺩ SELECT S. rating FROM Sailors S WHERE S. age = (SELECT MIN (AVG (S 2. age)) FROM Sailors S 2) v Correct solution (in SQL/92): 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) �

JOIN � SELECT * FROM "Sailors" JOIN "Reserves" ON "Sailors". sid = "Reserves". sid ; � SELECT COUNT(*), sname FROM "Sailors" JOIN "Reserves" ON "Sailors". sid = "Reserves". sid GROUP BY "Sailors". sid;

Equi or Natural Join � Equi-Join: A special case of condition join where the condition c contains only equalities. � Natural Join: Equijoin on all common fields. � SELECT * FROM "Sailors" NATURAL JOIN "Reserves" ;

LEFT OUTER JOIN � SELECT * FROM "Sailors" LEFT OUTER JOIN "Reserves" ON "Sailors". sid = "Reserves". sid ; � SELECT COUNT(*), sname FROM "Sailors" LEFT OUTER JOIN "Reserves" ON "Sailors". sid = "Reserves". sid GROUP BY "Sailors". sid;

Null Values values in a tuple are sometimes unknown (e. g. , a rating has not been assigned) or inapplicable (e. g. , no spouse’s name). � Field ◦ SQL provides a special value null for such situations. � The E. g. : ◦ ◦ ◦ presence of null complicates many issues. Special operators needed to check if value is/is not null. Is rating>8 true or false when rating is equal to null? What about AND, OR and NOT connectives? We need a 3 -valued logic (true, false and unknown). Meaning of constructs must be defined carefully. (e. g. , WHERE clause eliminates rows that don’t evaluate to true. ) New operators (in particular, outer joins) possible/needed.

Integrity Constraints (Review) IC describes conditions that every legal instance of a relation must satisfy. � An ◦ ◦ Inserts/deletes/updates that violate IC’s 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 < 200) � Types of IC’s: Domain constraints, primary key constraints, foreign key constraints, general constraints. ◦ Domain constraints: Field values must be of right type. Always enforced.

CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), � CHECK ( rating >= 1 AND rating <= CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, � day DATE, PRIMARY KEY (bid, day), CONSTRAINT no. Interlake. Res CHECK (`Interlake’ <> ( SELECT B. bname FROM Boats B WHERE B. bid=bid))) ﻣﺤﺪﻭﺩیﺖﻫﺎی ﻋﻤﻮﻣی General Constraints ﺑﺮﺍی ﺑیﺎﻥ ﻣﺤﺪﻭﺩیﺖﻫﺎی ﺟﺎﻣﻌیﺘی ﻋﻼﻭﻩ ﺑﺮ کﻠیﺪﻫﺎ ﺑﻪ کﺎﺭ ﻣیﺭﻭﺩ ﺑﺮﺍی ﻣﺤﺪﻭﺩیﺖﻫﺎ ﺍﺯ ﻧیﺰ ﻣیﺗﻮﺍﻥ query ﺍﺳﺘﻔﺎﺩﻩ ﻧﻤﻮﺩ 10 )

General Constraints: CHECK conditional-expression. � The conditional expression captures more general ICs than keys. � The conditional expressions can use queries. � The conditional expressions required to hold only if the associated table is nonempty. � A CHECK constraint may be expressed over several tables; however, it is often expressed over one single table. ﻫﺮ ﻣﺤﺪﻭﺩیﺖ ﻣیﺗﻮﺍﻧﺪ ﻧﺎﻡ ﻧیﺰ ﺩﺍﺷﺘﻪ ﺑﺎﺷﺪ � Syntax: ◦ CONSTRAINT My. Constraint CHECK conditional-expression �

CHECK Constraints: Examples Constraint: Rating must be in the range 1 to 10 CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( rating >= 1 AND rating <= 10 ))

CHECK Constraints: Examples Constraint: Interlake boats cannot be reserved CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (bid, day), CONSTRAINT no. Interlake. Res CHECK (`Interlake’ <> ( SELECT B. bname FROM Boats B WHERE B. bid=bid)))

Constraints Over Multiple Relations CREATE TABLE Sailors ﺗﻌﺪﺍﺩ ﻗﺎیﻖﻫﺎ ﺑﻪ ﻋﻼﻭﻩ ﺗﻌﺪﺍﺩ ﻗﺎیﻖﺭﺍﻥﻫﺎ ( sid INTEGER, ﺑﺎﺷﺪ 100 ﺑﺎیﺪ کﻤﺘﺮ ﺍﺯ sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( (SELECT COUNT (S. sid) FROM Sailors S) + (SELECT COUNT (B. bid) FROM Boats B) < 100 ) CREATE ASSERTION small. Club CHECK ( (SELECT COUNT (S. sid) FROM Sailors S) + (SELECT COUNT (B. bid) FROM Boats B) < 100 )

General Constraints: ASSERTION Constraint: Number of boats plus number of sailors is < 100 CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( (SELECT COUNT (S. sid) FROM Sailors S) + (SELECT COUNT (B. bid) FROM Boats B) < 100 ) � This solution is awkward and wrong: ◦ It is wrongfully associated only with Sailors, though it involves both Sailors and Boats. ◦ If Sailors is empty, the number of Boats tuples can be anything, since the conditional expression is not required to hold in such case!

General Constraints: ASSERTION Constraint: Number of boats plus number of sailors is < 100 CREATE ASSERTION small. Club CHECK ( (SELECT COUNT (S. sid) FROM Sailors S) + (SELECT COUNT (B. bid) FROM Boats B) � The < 100 ) assertion is not associated with any one of the tables involved. � ASSERTION is the right solution; not associated with either table.

General Constraints: Further Examples Schema: Students(sid: int, sname: string, age: int, cgpa: real) Works(sid: int, pct_time: int) Projects(did: int, budget: real, ta: int) � Write SQL integrity constraints (domain, key, foreign key, or CHECK constraints; or assertions) for given requirements: ◦ Students must have a minimum cgpa of 5. ◦ Every TA must also be a student. ◦ The total percentage of all assignments to projects for a given student must be at most 100%. ◦ A TA must have a cgpa higher than any student that she coaches.

General Constraints: Further Examples (1) Constraint: Students must have a minimum cgpa of 5. CREATE TABLE Students ( sid INTEGER, sname CHAR(10), age REAL, cgpa REAL, PRIMARY KEY (sid), CHECK ( cgpa >= 5)) Constraint: Every TA must also be a student. CREATE ASSERTION TAis. Student CHECK ( (SELECT COUNT (*) FROM Projects P WHERE P. ta_id NOT IN (SELECT sid FROM Students)) =0 )

General Constraints: Further Examples (2) Constraint: The total percentage of all assignments to projects for a student must be at most 100%. CREATE TABLE Works ( sid INTEGER, pct_time INTEGER, PRIMARY KEY (sid, pid), FOREIGN KEY (sid) REFERENCES Students, FOREIGN KEY (pid) REFERENCES Projects, CHECK ((SELECT COUNT (W. stid) FROM Works W GROUP BY W. stid HAVING SUM(pct_time) > 100) = 0)

General Constraints: Further Examples (3) Constraint: A TA must have a cgpa higher than any student that she coaches. CREATE ASSERTION TAHigher. CGPA CHECK ((SELECT COUNT(S. stid) FROM Students S, Students TA, Works WHERE S. sid=W. sid AND W. pid=P. pid AND P. ta=TA. sid AND S. cgpa > TA. cgpa) =0) W, Projects P

Triggers ﺍﺻﻠی ﺍﺟﺮﺍ event ﻗﺒﻞ ﺍﺯ ﺍﺟﺮﺍی trigger ﻋﻤﻠیﺎﺕ ﺩﺍﺧﻞ : BEFORE � ﻣیﺷﻮﺩ ﺍﺻﻠی ﺍﺟﺮﺍ event ﺑﻌﺪ ﺍﺯ ﺍﺟﺮﺍی trigger ﻋﻤﻠیﺎﺕ ﺩﺍﺧﻞ : AFTER � ﻣیﺷﻮﺩ ﺍﺻﻠی ﺍﺟﺮﺍ event ﺑﻪ ﺟﺎی ﺍﺟﺮﺍی trigger ﻋﻤﻠیﺎﺕ ﺩﺍﺧﻞ : INSTEAD � trigger ﺍﺻﻠی ﺍﺟﺮﺍی ﻧﻤیﺷﻮﺩ ﻭ ﻋﻤﻠیﺎﺕ ﺩﺍﺧﻞ Event یﻌﻨی. ﻣیﺷﻮﺩ ﺍﺟﺮﺍ ﻣیﺷﻮﺩ � Row-level trigger: executed once per modified row (that satisfies the trigger condition). � Statement-level trigger: executed once per modifying statement. � Transition variables: NEW, OLD, NEW TABLE, OLD TABLE.

Triggers: Example (SQL: 92) Increment a count for each newly inserted sailor whose age < 18. CREATE TRIGGER incr_count AFTER INSERT ON Sailors WHEN (new. age < 18) FOR EACH ROW BEGIN count: =count+1; END ﺑﺮﺍی ﺩﺳﺘﺮﺳی ﺑﺎ ﺩﺍﺩﻩﻫﺎی ﺍﺿﺎﻓﻪ ﺷﺪﻩ ﺍﺧیﺮ یﺎ ﻣﻘﺎﺩیﺮ ﺟﺪیﺪ ﺑﻪ NEW ﺍﺯ یک ﺭﺍﺑﻄﻪ ﺭﻭﺯ ﺷﺪﻩ ﺍﺳﺘﻔﺎﺩﻩ ﻣیﺷﻮﺩ

Triggers: Example (SQL: 1999) Save newly inserted sailors aged < 18 in a special table. CREATE TRIGGER young. Sailors. Update AFTER INSERT ON Sailors REFERENCING NEW TABLE AS New. Sailors FOR EACH STATEMENT /* This is the default */ INSERT INTO Young. Sailors(sid, name, age, rating) SELECT sid, name, age, rating FROM New. Sailors N WHERE N. age <= 18 v Illustrates use of NEW TABLE to refer to a set of newly inserted tuples v Exists since SQL: 1999

Triggers: More Elaborated Example Whenever a student is given a (bonus) raise on his cgpa, the TA’s cgpa must be increased to be at least as high. CREATE TRIGGER bump. TAcgpa AFTER UPDATE ON Students WHEN OLD. cgpa < NEW. cgpa FOR EACH ROW BEGIN UPDATE Students S SET S. cgpa = NEW. cgpa WHERE S. cgpa < NEW. cgpa AND S. sid IN (SELECT P. ta FROM STudents S 1, Works W, Projects P WHERE S 1. sid = NEW. sid AND S 1. sid = W. sid AND W. sid = P. sid); END v Illustrates use of Oracle PL/SQL syntax in the action part.

ﺩﺳﺘﻮﺭﺍﺕ ﺗﻌﺮﻳﻒ ﺩﺍﺩﻩﻫﺎ . 1 : ﺗﻌﺮﻳﻒ ﺷﻤﺎ 1 - 1 CREATE SCHEMA AUTHORIZATION USER {base – table definition , view definition , grant-operation }

ﺩﺳﺘﻮﺭﺍﺕ ﺗﻌﺮﻳﻒ ﺩﺍﺩﻩﻫﺎ CHARACTER[(length)] INTEGER DECIMAL [(precision[, scale])] SMALLINT DOUBLE PRECISION REAL FLOAT [(precision)] NUMERIC [(precision[, scale])] . 1 : ﺍﻧﻮﺍﻉ ﺩﺍﺩﻩﺍﻱ 1 - 2 -

ﺩﺳﺘﻮﺭﺍﺕ پﺮﺩﺍﺯﺵ ﺩﺍﺩﻩ ﻫﺎ 2: ( SELECT ) ﺩﺳﺘﻮﺭ ﺑﺎﺯﻳﺎﺑﻲ 2 - 1 SELECT [ALL | DISTINCT] item(s)-list FROM table(s)-name [WHERE condition(s)] [GROUP BY column(s)] [HAVING conditions(s)]