• Declarative way of specifying what you need
• Procedural part of SQL → Nested Query

SELECT

FROM

WHERE ATTRIBUTE OPERATOR (=, <>) [Query 2]

• IN/ NOT IN
• ANY
• ALL
• EXIST/ NOT
• EXIST

<aside> 📌 STUDENT (SID, NAME, SURNAME, CITIZENSHIP) EXAM (SID, CID, MARK) COURSE (CID, NAME, AREA)

</aside>

Select the student who passed at least one exam and are Italian

SELECT SID
FROM EXAM JOIN STUDENT ON EXAM.SID = STUDENT.SID
WHERE CITEZENSHIP = 'ITALIAN'

is equivalent to

# Query 1
SELECT SID
FROM EXAM
WHERE SID IN (
	# Query 2
	SELECT SID
	FROM STUDENT
	WHERE CITIZENSHIP = 'ITALIAN'
)

Select the student who got at least over 30

SELECT NAME, SURNAME
FROM STUDENT
WHERE SID IN (
	SELECT DISTINCT(SID)
	FROM STUDENT
	WHERE MARK = 30
)

Select the students who have never got a 30

SELECT NAME, SURNAME
FROM STUDENT
WHERE SID NOT IN (
	SELECT DISTINCT(SID)
	FROM STUDENT
	WHERE MARK = 30
)

SELECT SID
FROM STUDENT

EXCEPT

SELECT DISTINCT(SID)
FROM STUDENT
WHERE MARK = 30

Select the students with name and surname equal to another student

SELECT
	S1.NAME,
	S1.SURNAME
FROM STUDENT S1, STUDENT S2
WHERE
	S1.SID <> S2.SID 
	AND S1.NAME = S2.NAME 
	AND S1.SURNAME = S2.SURNAME