SQL class notes-nested queries

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.