title: 09-Division-and-Correlated-Subquery
date: 2023-12-27	
status: DONE
tags:
  - MySQL
  - NOTE
  - Lec9
author:
  - AllenYGY
created: 2023-12-27T18:49
updated: 2024-03-21T21:51
publish: True

Division-and-Correlated-Subquery

Division

Division operator is used for queries involving “all”.

Example

Query: “Retrieve (all) course names that is/are taught by all programes.”

The solution to the query is

Database is taught by all programmes, i.e. both CST and DS.

Correlated Subquery

SELECT 𝐶1.𝑐_𝑛𝑎𝑚𝑒
FROM 𝑐𝑎𝑡𝑎𝑙𝑜𝑔𝑢𝑒 AS 𝐶1
WHERE NOT EXISTS (  // 遍历C1
  (SELECT 𝑝_𝑛𝑎𝑚𝑒  //Divisor
  FROM 𝑝𝑟𝑜𝑔𝑟𝑎𝑚𝑚𝑒)
    EXCEPT
  (SELECT 𝑝_𝑛𝑎𝑚𝑒 //Dividend
  FROM 𝑐𝑎𝑡𝑎𝑙𝑢𝑔𝑢𝑒 AS 𝐶2
  WHERE 𝐶2.𝑐_𝑛𝑎𝑚𝑒=𝐶1.𝑐_𝑛𝑎𝑚𝑒)// 得到C1.c_name对应的p_name集合
)

NOT EXISTS: 检查集合是不是NULL

If true return true; else return false;

第一个SELECT选择的是除数
第二个SELECT选择的是被除数
EXCEPT 去除集合的公共元素

SELECT 𝐶1.𝑐_𝑛𝑎𝑚𝑒
FROM 𝑐𝑎𝑡𝑎𝑙𝑜𝑔𝑢𝑒 AS 𝐶1
WHERE NOT EXISTS (
  SELECT *
  FROM 𝑝𝑟𝑜𝑔𝑟𝑎𝑚𝑚𝑒
  WHERE 𝑝_𝑛𝑎𝑚𝑒 NOT IN(
    SELECT 𝐶2.𝑝_𝑛𝑎𝑚𝑒
    FROM 𝑐𝑎𝑡𝑎𝑙𝑜𝑔𝑢𝑒 AS 𝐶2
    WHERE 𝐶2.𝑝_𝑛𝑎𝑚𝑒=𝐶1.𝑝_𝑛𝑎𝑚𝑒
  )
)

Example-for-Correlated-Subquery

// sID is a foreign key to student.sID. cID is a foreign key to course.cID.
// iID is a foreign key to instructor.iID. cID is a foreign key to course.cID.

Find the students who have enrolled all courses. Display your answer by sID and sname.

SELECT s1.sID,s1.sname
FROM student AS s1
WHERE NOT EXISTS(
  (SELECT cID FROM course)
  EXCEPT
  (SELECT cID 
  FROM enroll AS e1
  WHERE s1.sID=e1.sID
  )
)

SELECT s1.sID,s1.sname
FROM student AS s1
WHERE NOT EXISTS(
  SELECT cID FROM course
  WHERE cID NOT IN(
    SELECT cID 
    FROM enroll AS e1
    WHERE s1.sID=e1.sID
  )
)

^[1]

Find the students who have taken all courses instructed by Goliath (instructor’s name and assuming that there is no other instructor who is called Goliath). Display your answer by sID and sname.

Divisor: course that taught by Goliath
Dividend: enroll

SELECT s1.sID,s1.sname
FROM student AS s1
WHERE NOT EXISTS(
  (SELECT cID FROM course  WHERE cname='Goliath') 
  EXCEPT
  (SELECT cID FROM enroll AS e1
  WHERE e1.sID=s1.sID)
)

SELECT s1.sID,s1.sname
FROM student AS s1 
WHERE NOT EXISTS(
    (SELECT cID 
    FROM course 
    JOIN teach USING(cID) 
    JOIN instructor USING(iID) 
    WHERE iname='Goliath'
    AND cID NOT IN 
        (SELECT cID FROM enroll AS e1
        WHERE e1.sID=s1.sID)
    )
  );

不需要将student，enroll，course合起来↩︎