SQL class notes-nested queries

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