Subquery with EXISTS predicate
EXISTS predicate
Existential quantifier_
Subqueries with EXISTS predicates do not return any data and only produce a logical true value or a logical false value.

The outer WHERE clause returns true values if the inner query result is not empty
The outer WHERE clause returns false values if the inner query result is empty
Subqueries derived from EXISTS usually use the * target list expression because subqueries with EXISTS only return true or false values, giving column names is meaningless.

NOT EXISTS predicate
The outer WHERE clause returns false values if the inner query result is not empty
The outer WHERE clause returns true value if the inner query result is empty

[Example 3.60] Query the names of all the students who took Course 1.
Idea analysis:
1. This query involves Student and SC relationships
2. Check SC tables with Sno values of each tuple in turn in Student
3. If such a tuple exists in the SC, its Sno value is equal to this Student.Sno Value, and its Cno='1', then take this Student.Sname Feed into Result Table

SELECT Sname
FROM Student
WHERE EXISTS(SELECT *
             FROM SC
             WHERE Sno=Student.Sno AND Cno='1');

[Example 3.61] Query the names of students who did not take Lesson 1.

Replacement between different forms of queries

Some subqueries with EXISTS or NOT EXISTS predicates cannot be replaced equivalently by other forms of subqueries
All subqueries with IN predicate, comparison operator, ANY and ALL predicate can be replaced equivalently with subqueries with EXISTS predicate
[Example 3.55] Query students who study in the same department as Liu Chen.
You can replace it with a subquery with an EXISTS predicate:

SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS(SELECT *
             FROM Student S2
             WHERE S2.Sdept=S1.Sdept AND S2.Sname='Liu Chen');

*Using EXISTS/NOT EXISTS to implement full-term quantifiers (difficulties)**_Neodymium_Neodymium_Neodymium
There is no full quantifier For all in the SQL language
Predicates with full quantifiers can be converted to equivalent predicates with existing quantifiers:
(∀x)P≡￢(∃x(￢P))
[Example 3.62] Query the names of students taking all courses.

SELECT Sname
FROM Student
WHERE NOT EXISTS(SELECT *
                 FROM Course
                 WHERE NOT EXISTS(SELECT *
                                  FROM SC
                                  WHERE Sno=Student.Sno 
                                  AND Cno=Course.Cno
                                )
               );

Expressions after escape: There is no course he does not take
_Neodymium_Neodymium_** Implement Logical Implication (Difficulty) with EXISTS/NOT EXISTS**_Neodymium_Neodymium_Neodymium
MinkNo implication logical operation in SQL language
MinkPredicate calculus can be used to convert logical implication predicates to be equivalent:
p →q ≡→￢p∨q
[Example 3.63] Query the number of students who have taken at least all the courses chosen by students in 201215122.
Ideas:
1. Express with logical implication: Query the students whose student number is X. For all courses y, as long as the students take course y in 201215122, x also takes course y.
2. Formal representation:
1) Use P to denote the predicate "Student took course y in 201215122"
2) q denotes the predicate "Student x took course y"
3) Then the above query is: (y) p_q
3. Equivalent transformation: (y) p_q_y (p_q)
4. Post-transformation semantics: there is no such course y, students take y in 201215122 and students don't take x.

SELECT DISTINCT Sno
       FROM SC SCX
       WHERE NOT EXISTS(SELECT *
                      FROM SC SCY
                      WHERE SCY.Sno='201215122' AND
                            NOT EXISTS(SELECT *
                                     FROM SC SCZ
                                     WHERE SCZ.Sno=SCX.Sno AND
                                           SCZ.Cno=SCY.Cno));

The number of columns in each query result participating in a set operation must be the same
The corresponding items must have the same data type
[Example 3.64] Query computer science students and students younger than 19 years of age.

SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

[Example 3.65] Query students who have taken Course 1 or Course 2.

SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno= '2';

[Example 3.66] Query the intersection of computer science students and students younger than 19 years of age.`

SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19

//Actually it's not older than in the Query Computer Science Department19A student aged 18. 
//Solve this problem with a join query:
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage<=19;

SELECT *
FROM student
WHERE Sdept = 'CS'
AND Sage <=19

```![Insert a picture description here](https://img-blog.csdnimg.cn/20200622005554351.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dqcGVpMTIz,size_16,color_FFFFFF,t_70)
[example 3.67]Query for selected courses1Another course was chosen2Students.`

```sql
SELECT Sno
FROM SC
WHERE Cno = '1'
AND Sno
IN (
SELECT Sno
FROM SC
WHERE Cno = '2'
)

, with (EXISTS)

SELECT Sno
FROM SC SCX
WHERE Cno = '1'
AND EXISTS (
SELECT Sno
FROM SC SCY
WHERE Cno = '2'
AND SCX.Sno = SCY.Sno
)

Use (JOIN ON)

SELECT *
FROM SC SCX
JOIN SC SCY ON ( SCX.Cno = '1'
AND SCY.Cno = '2'
AND SCX.Sno = SCY.Sno )

It can also be expressed as a nested query:

SELECT Sno
          FROM    SC
          WHERE Cno=' 1 ' AND Sno IN
                              (SELECT Sno
                               FROM SC
                               WHERE Cno=' 2 ');

[Example 3.68] Query the difference between a computer science student and a student younger than 19 years of age.

SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT  *
FROM Student
WHERE Sage<=19;

//Actually, in the Query Computer Science Department, you are older than19A year old student
//Should be a multiple-criteria query in a single-form query
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;

[Example 3.57] Find out the number of each student who has exceeded the average score of his own optional course

SELECT Sno, Cno
FROM SC,(SELECT Sno,Avg(Grade) 
          FROM SC 
		  GROUP BY Sno)   /*Average score per student*/
		  AS Avg_sc(avg_sno,avg_grade)   /*Rising Name*/
WHERE SC.Sno = Avg_sc.avg_sno
                    AND SC.Grade>=Avg_sc.avg_grade

SELECT Sno, Cno    /*Related Subqueries*/
FROM SC x
WHERE Grade>=(SELECT AVG(Grade) 
		      FROM  SC y
		      WHERE y.Sno=x.Sno);

[Example 60] Query the names of all the students who have taken Lesson 1, which can be completed with the following query:

SELECT Sname
    FROM     Student,  
                   (SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1
    WHERE  Student.Sno=SC1.Sno;

``SELECT Sname
FROM Student
WHERE EXISTS(SELECT *
             FROM SC
             WHERE Sno=Student.Sno AND Cno='1');

SELECT Sname
FROM Student,SC
WHERE SC.Sno=Student.Sno AND Cno='1';

```sql
//Insert a code snippet here