Databases Exam Topic Summary

SQL summary
1




Basic SQL Joins of multiple tables

retrieve addresses return the table with the name and
of all students of persons

SELECT
SELECT P.name C. kind
address ,




FROM Persons P classes c. Takes classes T
FROM students ,




WHERE P id .
= T person.
-
id AND T .
class - id =
C. id

return all students with first name a

SELECT east

FROM students

self joins
'
'
WHERE first George
-
=



'


return that shows with
a
query all persons b


Non -
monotonic Queries SELECT distinct PI.name

(Not) In FROM Persons P ,
Persons PI ,
Persons P2 , Likes L

id L
WHERE PI person A- id AND P2 id =L person
=
returns the names of people who do not
-
-
.
-




take classes


SELECT distinct P.name returns the name at
of all persons that like l

FROM Persons P Takesclasses .
C SELECT distinct PI.name

WHERE P id .
NOT IN ( SELECT C. person - id FROM FROM Persons P, Persons P1 .
Persons P2 ,
Persons P3

Takesclasses C WHERE C. person -
id =P id .
) WHERE PI id =L person A- id
.
.
AND P2 . id =L -

person B

P1 id .
= LI .
person A- id AND P3 . id =
LI .

person B- id AND

L .
personB- id <>
LI .
personB- id



returns the name of all male persons that pla
returns the name of all persons who SELECT distinct PI . name


do not know who takes classes FROM Persons Pd Persons P2 sport Together S
anyone , ,




SELECT distinct P.name WHERE P1 id .
= 5. person A- id AND P2 id .
= S .

personB-id
" " "

P2
"

FROM Persons P ,
Persons P2 S .
sport
= soccer AND .

gender =
female OR P2 -




PI
"
id
"
"
WHERE P id. NOT IN ( SELECT K -
persona -
AND .

gender =
male AND 5.sport =
soccer
"




FROM Takesclasses T ,
knows K WHERE

( Inner) Join
K . PersonB- id =
T .
person -
id)

SELECT A. name .
B. name

FROM TableA A JOIN Tablets B ON A. id = B. id




( Not ) Exists Left Counter) Join

returns the name all persons that SELECT A. name B. name
of ,




aoe at least as old as everybody else FROM Table A A LEFT JOIN Tablets B ON A. id -_ Bid

SELECT P.name

FROM Persons P Right cover ) joins
SELECT A. name B. name
WHERE NOT EXISTS ( SELECT PS.name ,




Bid
age )
< PI FROM TARKA A RIGHT Tablets B ON A. id
FROM Persons PI WHERE JOIN
=
P .

age .




Full Counter) Joins

returns the name like
SELECT A. name B. name
of all persons that ,




FROM Table A A Tablets B ON A. id Bid
everyone they FULL JOIN -_
know

SELECT distinct P.name

Persons P , Likes L For All All ,
FROM ,
knows K
Any , Som

WHERE NOT EXISTS ( SELECT P id.
FROM Persons return the name of all persons which stud

PI Likes L Knows K K person A- id that are more than 5 older homework
WHERE years
-

. .