· 8 years ago · May 29, 2018, 02:52 PM
1TEMPLATE
2ALTER TABLE ADD CONSTRAINT [<cName>] <cBody>
3ALTER TABLE DROP CONSTRAINT <cName>
4alter table tableName
5add constraint constraintName
6check (attName in ('C1' ,'C2' ,'C3'));
7check (attName <= 5000);
8check ( NOT EXISTS (SELECT *
9 FROM tableName t)
10 WHERE (t.att1 < 20) AND (t.att2 >= 50) );
11Example: Printer with smaller model number must always have a smaller price assuming both made by same manufacturer
12alter table Printer
13add constraint model_price_relationship
14check ( NOT EXISTS (SELECT *
15 FROM Printer p1, Printer p2, Product po1, Product po2
16 WHERE (p1.model = po1.model) AND (p2.model=po2.model)
17 AND (po1.manufacturer= po2.manufacturer) AND (p1.model < p1.model)
18 AND (p1.price >= p2.price) ));
19TEMPLATE
20CREATE OR REPLACE TRIGGER triggerName
21BEFORE INSERT OR UPDATE ON tableName (OR BEFORE UPDATE OF attName ON tableName)
22REFERENCING OLD AS oldTuple NEW as newTuple
23FOR EACH ROW
24DECLARE numCount INT; errorMsg VARCHAR(50);
25BEGIN
26 IF ((:new.att1 < 5) OR (:new.att2 IN ('C1' ,'C2' ,'C3')) ) THEN
27 RAISE_APPLICATION ERROR(-20004, 'this is the error message');
28 END IF;
29 IF (:new.att3 >5) THEN
30 SELECT COUNT(att?) INTO numCount
31 FROM tableName t
32 WHERE (t.att? > :new.att4) AND NOT(t.att? = 'PC');
33 IF(numCount>0) THEN
34 RAISE_APPLICATION ERROR(-20004, 'this is the error message');
35 END IF;
36 END IF;
37 IF(numCount!=0) THEN
38 errorMsg := 'soemthing' || 'something more'
39 INSERT INTO table2Name SELECT FROM table1Name WHERE (TRUE) AND (TRUE);
40 INSERT INTO table2Name VALUES (att1, att2, att3);
41 INSERT INTO table2Name(model) VALUES(:new.model);
42 END IF;
43END;
44.
45RUN;
46
47CREATE TRIGGER salaryRestrictions
48AFTER INSERT OR UPDATE ON Professor
49FOR EACH ROW
50BEGIN
51IF (INSERTING AND :new.salary < 60000) THEN RAISE_APPLICATION_ERROR (-20004, 'below min salary'); END IF;
52IF (UPDATING AND :new.salary < :old.salary) THEN RAISE_APPLICATION_ERROR (-20004, ‘Salary Decreasing !!'); END IF;
53END;
54
55TEMPLATE Updating tuples
56UPDATE Student SET professor=‘ER’ WHERE sNumber=‘6’
57
58CREATE VIEW viewName AS (modelNum, costNameTag) SELECT model, price FROM Printer;
59Single-relation view, deletion will correctly proceed, insertion can unambigiously proceed as 'model' is the key of this relation, this insert will not cause any constrain violation. That is, all other fileds of the base relation not specified by this insert can be set to NULL.If primary key is NULL the insertion is rejected by the DBMS.
60Multi-relation view, the join view exposes the primary keys of both relations and because a key-foreign-key connection is used to conduct the join between those 2 tables. We are guaranteed that those keys 'model' serve as key in the view table. Hence, a delete would be 1-1 mapped to base tuples and thus can be unambigiously executed. Insertion is allowed because view exposes the primary keys of both relations, use a key-foreign-key connection to join the two base tables and no other NOT-NULL constraints exist on attributes not visiblee via the view.
61
62Relational Algebra
63Suppose that R and S are bags and that tuple t appears n times in R and m time in S. In the bag union R U S, , tuple t appears n + m times. Bag intersection of R and S tuple t appears min(n, m) times. Difference R - S, max(0, n-m) times. SELECT c and d of R = SELECTc of R intersect SELECTd of R true SELECT c or d of R = SELECTc of R union SELECTd of R false because of dumplicates
64
65Idempotent property Operation applied twice gives same result as when applied once. For SETS Idepotent in difference, intersection and union. For BAGS only INTERSECTION.
66SELECT sNumber || sName AS info For set semantics, use UNION, INTERSECT, EXCEPT. For bag semantics, use UNION ALL, INTERSECT ALL, EXCEPT ALL
67
68Duplicate Elimination .
69SELECT DISTINCT address FROM Student WHERE sNumber >= 1;
70
71SELECT * FROM Student JOIN Professor ON professor=pNumber;
72SELECT * FROM Student , Professor WHERE Student.pnumber = Professor.pnumber ;
73SELECT * FROM Student NATURAL JOIN Professor
74SELECT * FROM Student WHERE sNumber >= 1 ORDER BY sNumber, sName
75SELECT * FROM Student WHERE professor = (SELECT pName FROM Professor WHERE pNumber=1)
76Note: The inner subquery returns a relation, but SQL runtime ensures that subquery returns a relation with one column and with one row, otherwise it is a run-time error.
77We can use IN, EXISTS, NOT IN, and NOT EXISTS ALL, ANY can be used with comparisons
78
79
80SELECT * FROM Student WHERE (sNumber, professor) IN (SELECT pNumber, pName FROM Professor)
81SELECT * FROM Student WHERE sNumber > ALL (SELECT pNumber FROM Professor)
82
83SELECT address, COUNT (sNumber)
84FROM Student WHERE sNumber > 1
85GROUP BY address HAVING COUNT (sNumber) > 1;
86
87TEMPLATE
88SELECT [DISTINCT] a1, a2, …, an FROM R1, R2, …, Rm [WHERE C1] [GROUP BY g1, g2, …, gl [HAVING C2]][ORDER BY o1, o2, …, oj]
89
90
91
92PRACTICE EXAM
93SELECT sname
94FROM Suppliers
95WHERE sid IN ( SELECT sid
96 FROM Catalog
97 GROUP BY sid
98 HAVING COUNT(*) = (SELECT COUNT(*) FROM Parts));
99
100SELECT pname
101FROM Part P
102WHERE pid IN ( SELECT pid
103 FROM Catalog c, Parts p
104 WHERE c.pid=p.pid AND
105
106
107
108 )
109
110
111SELECT DISTINCT sid
112FROM Catalog c
113WHERE 'red' = ALL ( SELECT color
114 FROM Catalog c2, Parts p
115 WHERE c2.pid= p.pid AND c.sid=c2.sid);
116
117SELECT sid
118FROM Catalog c,
119WHERE sid IN (
120 )
121
122
123
124
125
126
127Find out left outer joint
128HW4.2.3 and 4.2.4
129Copy whole hw will be the best idea.
130
131SELECT DISTINCT(prod.manufacturer)
132FROM product prod
133WHERE prod.model IN (SELECT model
134FROM PC
135WHERE price = ( SELECT MAX(price) FROM PC));
136
137Find the manufacturer of the highest priced PC product. (Note: there could be multiple PCs
138with this same lowest price).
139SELECT DISTINCT(prod.manufacturer)
140FROM product prod
141WHERE prod.model IN (SELECT model
142FROM PC
143WHERE price = ( SELECT MAX(price) FROM PC));
144
145 For each manufacturer, find the cost of the most expensive product made by that manufacturer
146and indicate its type (PC/Labtop/etc) and return its price.
147SELECT manufacturer, max-price, type
148FROM
149product p,
150( SELECT pc1.model,pc1.price FROM pc pc1
151UNION
152SELECT lp1.model,lp1.price FROM laptop lp1) items2,
153(SELECT manufacturer, max(price) as max-price
154FROM
155product p,
156(SELECT pc1.model,pc1.price FROM pc pc1
157UNION
158SELECT lp1.model,lp1.price FROM laptop lp1) items
159WHERE p.model = items.model
160GROUP BY manufacturer ) max-table
161WHERE (p.manufacturer = max-table.manufacturer)
162AND (p.model = items2.model)
163AND (p.price = max-table.max-price)
164
165
166For each manufacturer, return the number of PCs they sell, and the average price they are
167asking for, as well as the number of labtops they sell, and the average price they are asking
168for those.
169SELECT * FROM
170(SELECT manufacturer as m1, count(*) as pc-count, avg (price) as pc-price
171
172
173FROM Product p1, PC pc1 WHERE p1.model = pc1.model and p1.type = 'PC'
174GROUP BY manufacturer)
175FULL OUTER JOIN
176(SELECT manufacturer as m2, count(*) as laptop-count, avg (price) as laptop-price
177FROM Product p1, laptop lp1
178WHERE p1.model = lp1.model and p1.type = 'Laptop'
179GROUP BY manufacturer)
180ON m1=m2;
181
182Of PCs with the same speed and ram and harddisk size (hd), find the cost and model number
183of the cheapest among those PC.
184SELECT pc1.speed, pc1.ram, pc1.hd, pc1.price, pc1.model
185FROM pc pc1
186NATURAL JOIN
187(SELECT speed, ram, hd, min(pc2.price) as cheap-price
188FROM pc pc2
189GROUP BY pc2.speed, pc2.ram, pc2.hd ) temp
190WHERE pc1.price = temp.cheap-price;
191
192Among the laptop (s) with the smallest price, find the ones with the largest screen size. (Note:
193There could be multiple rows in the result). Also, note that the outer min(price) will simply
194return us the price collection we need for comparison purposes.
195SELECT model
196FROM laptop l
197WHERE (l.screen, l.price) IN
198(SELECT max(screen), min(price)
199FROM laptop l2
200WHERE l2.price = (SELECT min(price) FROM Laptop));
201
202
203
204SELECT p1.part-id, (p1.cost + sum(p2.cost) + sum(p3.cost) as totalcost
205FROM ((Part p1 LEFT OUTERJOIN Part p2 on p1.part-id = p2.is-contained-in)
206. LEFT OUTERJOIN Part p3 on p2.part-id = p3.is-contained-in)
207GROUPBY p1.part-id)
208
209
210For each part, report the number of functions it has, and how many of those functions are
211required ones.
212SQL:
213SELECT p.part-id, num-fcts, num-required
214FROM
215SELECT p.part-id, count(*) as num-fcts
216FROM Parts p, Functions f
217WHERE (p.part-id = f.part-id)
218GROUPBY part-id
219LEFT-OUTER-JOIN
220SELECT p.part-id, count(*) as num-required
221FROM Parts p, Functions f
222WHERE (p.part-id = f.part-id) and (f.optional-or-required = "required")
223GROUPBY part-id