=Q2:ect A, Ae. . . . .
,
An
from R,R2, ...,
R
where P; one
1 correspondrelation
DA., Ar. . . . .
,
An(Op (R,xR2 X...
XRm))
->
Union intersect
minus
and or not
& Find the name of all branches in deposit relation.
selection
=> -> selectdistinct -nique
[Link]
fill at
cast
from Deposit;
Ifind all customers who have account at 127 branch.
=>[Link]
from Deposit
where [Link]="127';
I find all customers
having a loan, on account or both
at IIT branch.
=>
([Link]
from Deposit
where [Link]=(217)
union
I selectconame
from Borrow
where be name='177);
Ifind all customers
having a loan at branch of their
any
city.
=> Customer ([Link], street, [Link])
Deposit (B. name, ac. No., [Link], balance)
Borrow (B. name, loan no., [Link], amount) 3
Branch ([Link], B. City, Assets
client (coname, [Link])
selectC. sname, select
[Link]
from customer C, Branch B from
where where
C. sname =
B. sname;
branch
(and [Link]="127- ffonly
f 117
IFind all customers who have both
loan and account at
II7 branch.
=> [Link]
from Deposit
where [Link]='117
and coname in
([Link]
from Borrow
where boname="277"
all customers who have at
account 127 branch
Ifind
butno loan from IIT branch.
=> [Link]
from Deposit
where boname='177
and [Link] not in
([Link]
from Borrow
where biname="117")
a. find all branches thathave more assets than any branch
=
located in KGP.
=>
select [Link]
from Branch 7,Branch s
where T. asset, Sasset
[Link] KGP'
=
[Link] >any
from Branch Cany
where asset ↳> any
any
(selectasset
> all
from Branch <all
where bicity='kap')
Ifind all customers with balance lies between 50000000000.
[Link]
=>
from Deposit
where balance 50000 and balance <=60000
Customer ([Link], street, [Link])
Deposit (B. name, ac. No., [Link], balance)
Borrow (B. name, loan no., [Link], amount)
Branch ([Link], BBCity, Assets
client (coname, [Link])
[Link]
notween
from
where
Deposit
balance between 50000 and 60000
I v
① find in alphabetical order all the customers who have
=
loan in 117 branch.
=> [Link]
communalescendingor
one
I
from Borrow
where biname '227 =
order by [Link]
ary, min, max, sum, count
the balance all
at branches.
Ifind average account
=> selectbiname,aug (balance)
from Deposit
[Link]
group by
wewantonlyas
an
having arg(balance
I of tuples in customer relation.
find no
selectcount (*)
=>
from Customer
① find all who
customers have depositat 227 branch but
for whom there is no
entry in customer relation.
[Link]
=>
⑨
from Deposit D
where
-
of Il' and came not in
([Link]
not exists
I
from customer)
(selectcustomer a
where [Link] [Link]]
=
⑭
midsern, problem compulsory one - t
t
submit
&
-
Telephone subjecttitle
us Teacher (T. name, Dept., [Link]., Sititle)
m
Student (S. name, course, hall)
Study (s. title, so name, level, status, marks)
Subjeame
Ifind
the name of teachers of Math dept. Who teach
200 level subjects.
students of living in SN hall
find
I
app course VS or
who study. No subjecttaught by Prof. XY2.
selectso name
->
from student
where course-spp' and
Chall='Ys' or hall= 'N')
and [Link] not in
(selectso name
from Teacher I, study s
where to name='XYZ'
and T. s. title=s.s. title]
the aug marks the taught by prof.
① find in subjects XYZ.
selectS. [Link], and (marks)
=>
from study S, Teacher
where is title 5. stitle
=
and [Link]='xYz'
group by 5.S. title
the of thh such that
all students
Ifind name subjects studying
the subjectsecure more than 80 marks.
=>
Teacher (name, Dept., T. No., title)
SS
Student ([Link], course, hall)
~
Study (s. title, so name, level, status, marks)
[Link]
from study
group bb S. title
having min (marks) >80
Ifind the name
of students who secure equal marks in
2
differentsubjects.
=> [Link]
from student
where sname in ([Link]
fromstudys, study, came
and
and
S. S. title
s marks
he
T.S. tifle
T. marks.
=
3 your cannotbeen
&Find the number of students in each subjecttaught
by Prof. XY2.
Teacher (name, Dept., [Link].,
=> S. title)
Student (S. name, course, hall)
Study (s. title, so name, l
llll
lstatus, marks)
V v
[Link], count ([Link])
from studys, Teacher-
where to name='XY2' and
3. s. title=7. s. title
bb s.s. title
group
=>
RCA, B, c) Is it in 3NF?
F GA c,B
=
-
c3
+
u 3NF
decomposition
-
EY:AB
Yes
R. (A, C), ReCB, C), RECA, B)
BONF decomposition:
R, CA, C), R2 (A, B)
No
->
In saw we can use nested queries.