sixth day

 1  Noncorrelated Subqueries

A noncorrelated subquery is a subquery that is independent of the outer query. In other words, the subquery

could be executed on its own. The following is an example of a query that is not correlated:

--从表中得到等级为A的学生的名字

SELECT  s.sname

FROM    Student s

WHERE   s.stno IN

(SELECT gr.student_number
 

FROM    Grade_report gr
             

WHERE   gr.grade = 'A')

The first part of the preceding query (the first three lines) is the main, 
 
outer query, and the second part (the part in parentheses) is the subquery 
 
(also referred to as an inner, nested, or embedded query). To demonstrate 
 
that this subquery is an independent entity, you could run it by itself:
 
SELECT   gr.student_number
 
FROM    Grade_report gr
 
WHERE   gr.grade = 'A'
 
两个表是相互独立的两部分 能够独立的进行运行
 
 
2   Correlated Subqueries
 
 a correlated subquery is an inner subquery whose information is referenced 
 
 by the main, outer query such that the inner query may be thought of as being 
 
 executed repeatedly.
 
 The correlated subquery cannot stand alone, as it depends on the outer query; 
 
therefore, completing the subquery prior to execution of the outer query is not
 
an option. The efficiency of the correlated subquery varies; it may be worthwhile 
 
to test the efficiency of correlated subqueries versus joins or sets.
 
 
The correlated subquery acts like a nested DO loop in a programming language, 
 
where the first row from the Student table is SELECTed and tested against all
 
 the rows in the Grade_report table, and then the second Student row is SELECTed 
 
and tested against all rows in the Grade_report table. The following is the DO loop
 
 in pseudocode:
 
LOOP1: For each row in Student  s  DO
      
LOOP2: For each row in Grade_report  gr  DO
             
IF (gr.student_number = s.stno) THEN
                   
IF (gr.grade = 'B') THEN TRUE
      
END LOOP2;
     
IF TRUE, THEN Student row is SELECTed

END LOOP1

for example
--一个Correlated Subqueries例子
SELECT  s.sname

FROM    Student s

WHERE   s.stno IN
 
(
SELECT      gr.student_number
  
FROM        Grade_report gr, Section
  
WHERE       Section.section_id = gr.section_id
  
AND           Section.course_num LIKE 'COSC%'
 
AND           gr.student_number = s.stno
 
AND           gr.grade = 'B')
 
 
3