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