2017.11.15
Six: Nested queries
Nested queries Overview:
A select-from-where statement is called a query block
A query that nests one query block in the conditions of another query block's WHERE clause or having phrase is called a nested query
Example 1: Query the student's name in course 2nd:
Internal connection: Select Sname from STUDENT,SC where Student.sno=sc.sno and cno=2
Or: Select Sname from student where Sno in--outer query/parent query
(select Sno from SC where cno=2)--Inner query/subquery
The predicate to elicit the subquery:
subquery with in predicate
Subqueries with comparison operators
Subqueries with any or all predicates
Subquery with EXISTS predicate
1. Subqueries with an in predicate
Example 2: Query with ' Liu Chen ' students in the same professional study: (2 sheets involved)
Select Sno,sname,sdept from student where sdept in (select Sdept from student where Sname= ' Liu Chen ')
Inquire with ' Liu Chen ' students in the same professional study but do not include ' Liu Chen ':
Select Sno,sname,sdept from student where sdept on (select sdept from student where Sname= ' Liu Chen ') and Sname<> ' Liu Chen '
Example 3: The inquiry took the student number and name of the course entitled ' Information System ': (3 sheets involved)
Select Sno,sname from student where Sno in--3. Finally, student and Sno are taken out of the sname relationship.
(select Sno from SC where CNO in--2. Then find out the number and name of the elective course in SC.
(select CNO from course where cname= ' Information system ')); --1. First identify the ' Information system ' course number in the course relationship
Select Sno,sname from Student,sc,course where Student.sno=sc.sno ....
2. Subqueries with comparison operators
Example 4: Query with ' Liu Chen ' students in the same professional learning:
Select Sno,sname,sdept from student where sdept = (select sdept from student where Sname= ' Liu Chen ')
3. Subqueries with any or all predicates:
>any >min
>all
<any <max
<all
>=any >=min
>=all
<=any <=max
<=all
=any
=all
! = (<>) any does not exist
! = (<>) all isn't in
Example 5: Query the name and age of a student who is younger than the computer in any other major (one of them)
Select Sname,sage from student where Sage<any (select sage from student where sdept= ' computer ') and sdept<> ' computer ';
Example 6: Query the name and age of a student who is younger than any computer (one of them) in other professions (using aggregate functions):
Select Sname,sage from student where sage< (select (max) sage from student where sdept= ' computer ') and sdept<> ' computer ';
Example 7: Query ...
4. Subquery with EXISTS predicate:
1.exists predicates:
Does not return any data, only produces logical true truth ' or ' false ' or logical false value
Returns true if the inner query result is NOT NULL
If the inner query result is empty, the false value is returned
A subquery derived from exists, whose target column expression is usually used *, because a subquery with exists returns only true or false values, giving the row a name of five practical significance
2.not exists predicate:
Do not return result set to True
Example 8: Check the names of all students who have enrolled in course number 1th:
Select Sname from Studnet where Sno in (select Sno from SC where cno=1)
Select Sname from studnet where exists (SELECT * from SC where sc.sno=student.sno and cno= ' 1 ')
Example 9: Query the name of a student who does not have elective course No. 1th:
Select Sname from Studnet where isn't exists (SELECT * from SC where sc.sno=student.sno and cno= ' 1 ')
Inquire with ' Liu Chen ' students in the same professional study:
Select Sno,sname,sdept from Studnet S1 where exists (SELECT * from student S2 where s2.sdept=s1.sdept and Sname= ' Liu Chen ')
Check the name of the student who has enrolled in all courses:
Method 1:select sname from student where not exists
(SELECT * from course where NOT exists
(SELECT * from SC where sc.sno=studnet.sno and Sc.sno=course.cno))
Method 2:select sname from student where Sno in
(Select Sno from SC GROUP by SNO have count (*) = (select count (*) from course))
Practice:
Use students
Go
--1 the names and ages of all students larger than "Wang Min" with nested queries.
Select Sname,sage from student where sage >all (select sage from student where sname= ' Wang Min ')
--2, with nested queries, took the name of the student in class 3rd.
Select Sname from student where Sno in (select Sno from SC where cno=3)
--3, with nested query, took the course named "Math" student number and name.
Select Sno,sname from student where Sno in (select Sno from SC where sdept= ' math ')
--4, inquire about the names and ages of the students of any of the other departments in the school.
Select Sname,sage from student where Sage<any (select sage from student where sdept= ' electrons ') and sdept<> ' electrons '
--5, inquire about the names and ages of students in other departments who are younger than all students of the electronic department.
-select sname,sage from student where sage <all (select sage from student where sdept= ' electrons ') and sdept<> ' electrons ';
Select Sname,sage from student where Sage < (select MIN (sage) from student where sdept= ' electrons ') and sdept<> ' electrons ';
--6, use exists to query all the names of students who have enrolled in the 1th course.
Select Sname from student where exists (SELECT * from SC where sc.sno=student.sno and cno=1)
--7 the name of the student who has enrolled at least one course.
--select sname from student where isn't exists (SELECT * from SC where sc.sno=student.sno and Sdept=null)
Select Sname from student where Sno on (select Sno from SC)
--8 and inquires the names of students who have enrolled in two or more courses.
Select Sname from student where Sno in (select Sno from SC GROUP by SC.SNO have Count (*) >=2)
--select sname from Student A join SC B on A.sno=b.sno where have count (B.sno) >=2
--9, check the names of students who do not have any courses.
Select sname from student where isn't exists (SELECT * from SC where student.sno=sc.sno)
--or select sname from student where not in (select Sno from SC)
--10, the inquiry has at least two elective course number and course name.
Select Cno,cname from Course where CNO in (select CNO from SC GROUP by CNO have Count (*) >=2)
--11, inquires the name of the student who took all the courses.
Select sname from student where not exists
(SELECT * from course where NOT exists
(SELECT * from SC where sc.sno=student.sno and Sc.sno=course.cno))
--12, check the course information that all students have enrolled.
Select Cno,cname from course where NOT exists
(SELECT * from student where NOT exists
(SELECT * from SC where sc.sno=student.sno and Sc.cno=course.cno))
SQL class notes-nested queries