2003B Q5

1
2003B Q5

• Suppliers(sid, sname, city)

Select sid from suppliers where cityjerusalem
or sname like Smith
Select sid from suppliers where cityjerusalem
Union all (select sid from suppliers where
sname like Smith)
?????? ?? Q2 ???? ????? ?? ?????? ?? Q1 ???
?????. ???? ?? ?? ?????? ??????
(11,J. Smith,jerusalem)

2
2003B Q11
Create table T( A integer primary key, B
integer) Create table S( A integer references
T(A) on delete cascade, C integer)

• ????? T ???? ???? ??? (2,1) ?????? S ?? ??? ?????
  (2,1), (2,2)
• ??? ?? ????? ???? ???

Create trigger TT after insert on S for each row
Begin Delete from T End /
?????? ?? ?????? Insert into S
values(3,3) ?? ???? ????? ?? S? TT?
????? ????? ???? ????!
3
2003A Q2 (modified)
Aircrafts(aid, aname, cruisingrange)

• ???? ?????? ? SQL ?????? ????? ???? ?? ??????
  ????? ????? ???? ???? ?????? ?? ????? ? boeing
  747.

Select a.aid from aircrafts a Where
a.cruisingrangegtall(select a2.cruisingrange from

aircrafts a2 where
a2.anameBoeing 747)
4
2003A Q5
Aircrafts(aid, aname, cruisingrange)
Create view v as Select aname, cruisingrange From
aircrafts Where cruisingrangegt10,000

• Insert into v values(Boeing 707,9999)
• ?????- aid ???? ????
• Update v set cruisingrange10000 where
  cruisingrange9999
• ?? ???? ???

5
2003A Q7

• Support60, confidence30
• ?? ???? ???? ?? ? support ??- confidence ?? ????
• Supp(a,b-gtc) supp(a,b,c) lt supp(a,c)
  supp(a-gtc)
• Conf(a,b-gtc) supp(a,b,c)/ supp(a,b)
• Conf(a-gtc) supp(a,c)/ supp(a)

a
c
6
2004A Q3
Flights(fno, from, to, distance, departs, arrives)
??? ?? ?? ????? ??????? ????? ????? ??? ??? ?????
????? ?????? ??? ????. ??????? ?? ????? ?to
(Flights)-?to (?fromTelAviv (Flights)) ?
SQL Select distinct to from flights Minus /where
to not in Select distinct to from flights where
fromTelAviv
7
2004A Q11
(Select A from R where B12) Intersect (select
A from R where C2)
?? ???? ??? ??????? ?? 1 ? -2?
R(A,B,C,D)
2
1
Select A from R where B12 and C2
D C B A
2 12 1
2 12 1
1 1
1
D C B A
3 12 2
2 1 2
2
null