SQL data Query--nested query

I. Description of the concept

In the SQL language, a select-from-where statement is called a query block. A query that nests a query block in the WHERE clause of another query block or in the condition of a having phrase is called a nested query. For example:

SELECT Sname  /* outer query or parent query * /from StudentWHERE in    (SELECT Sno  /* inner query or subquery */      from SC      WHERE Cno='2');

The SQL language allows multiple layers of nested queries, meaning that other subqueries can also be nested within a subquery.

Note: The ORDER BY clause cannot be used in a SELECT statement for a subquery because the ORDER BY clause can only sort the results of the final query.

Second, subqueries with in predicates

In a nested query, the result of a subquery is often a collection, so predicate in is the most frequently used predicate in a nested query.

Query with "Liu Chen" students studying in the same department

SELECT sno,sname,sdept  from Student WHERE inch     (SELECT  sdept     from  Student     WHERE Sname=' Liu Chen ');

Enquiry the student number and name of the course entitled "Information System"

SELECTSno,sname/*finally take out Sno and Sname in the student relationship.*/ fromStudentWHERESnoinch    (SELECTSno/*and then find the student number in the SC relationship that took the No. 3rd course .*/      fromSCWHERECnoinch        (SELECTCno/*First, the "Information System" course number is found in the course relationship, the result is number 3rd*/          fromCourseWHERECname='Information Systems'        )    );

This query can also be implemented with a connection query:

SELECT Student.sno,sname  from Student,sc,course WHERE Student.sno=and    SC. Cno=and    course.cname=' information system ' ;

Iii. subqueries with comparison operators

A subquery with a comparison operator refers to a connection between a parent query and a subquery using a comparison operator. When the user knows for sure that the inner query returns a single value, you can use the comparison operator >, <, =, >=, <=,! =, or <>.

Find out the number of courses each student has averaged over his own elective course

SELECT Sno,cno  from SC X WHERE >= (SELECTAVG(Grade)               from  SC y               WHERE y.sno  =X.sno);

Iv. subqueries with any (SOME) or all predicates

a subquery can use a comparison operator when it returns a single value, but returns a multivalued value with any (system with some) or the all predicate modifier. Using the any or all predicates, you must also use the comparison operator. its semantics are as follows:

>any	is greater than one of the values in the subquery result
>all	is larger than all values in the subquery result
<any	is less than one of the values in the subquery result
<all	is smaller than all values in the subquery results
>=any	Greater than or equal to a value in subquery results
>=all	Greater than or equal to all values in subquery results
<=all	is less than or equal to all values in subquery results
<=any	Greater than or equal to a value in subquery results
=any	equals a value in the subquery result
=all	equals all values in the subquery result (usually with no actual meaning)
! =(or <>) any	is not equal to a value in the subquery result
! =(or <>) All	is not equal to any one of the values in the subquery result

Check the name and age of a student in a non-computer science department who is younger than any other computer science student.

SELECT Sname,sage  from Student WHERE Sage<any (SELECT  sage                from  Student                WHERE Sdept='CS')and<>'  CS';

Querying the names and ages of students in non-computer science departments who are younger than all students of computer science Department

 select   sname,sage  from   Student  where  Sage<all   ( select   Sage  from   Student  Span style= "color: #0000ff;" >where  sdept=   " cs   " )   sdept <>   " cs  ; 

tip: This query can also be implemented with the aggregation function

 select   sname,sage  from   Student  where  Sage <   ( select  min   from   Student  where  sdept=   '   

The corresponding relationship of any, all and aggregate functions is shown here

Iv. Subqueries with EXISTS predicates

A subquery with the EXISTS predicate does not return any data, producing only the logical truth "true" or the logical false value "false".

Check the names of all students who have enrolled in Course No. 1th

SELECT Sname  from Student WHERE EXISTS     (SELECT*from       SC     WHERE Sno=   and Cno='1');

With the presence of quantifier exists, if the inner query result is empty, then the WHERE clause of the outer layer returns TRUE, otherwise false values are returned.

Check the name of a student who does not have an elective No. 1th course

SELECT Sname  from Student WHERE  not EXISTS     (SELECT*from       SC     WHERE Sno=   and Cno='1');

Check the name of the student who took all the courses

Because there is no universal quantifier, the meaning of the title can be converted into the equivalent of the existence of quantifiers in the form of: query such a student, there is no course he does not elective.

SELECTSname fromStudentWHERE  not EXISTS    (SELECT *      fromCourseWHERE  not EXISTS        (SELECT *          fromSCWHERESno=Student.sno andCno=COURSE.CNO));

The student number at least 201215122 elective courses are enrolled in the enquiry

SELECT DISTINCTSno fromSC SCXWHERE  not EXISTS    (SELECT *      fromSC SCYWHERESCY. Sno='201215122'  and         not EXISTS        (SELECT *          fromSC SCZWHERESCZ. Sno=SCX. Sno andSCZ. Cno=SCY. Cno));

V. Summary

This study and collation of SQL related to nested query procedures

• With in predicate
• With comparison operators
• With any or all
• with EXISTS predicate

In particular, we should attach great importance to the correspondence between any and all and the aggregation function, as well as the logic judgment with exists.

SQL data Query--nested query