Experiment 5 Subquery
one , Experimental purpose
1. Master the representation of sub query .
2. Further grasp SQL Server How to use query analyzer , Deepen pair SQL Understanding of nested query statements in language
two , Experiment content
Implement query ：
1） Ask for the student number and name of students who have taken advanced mathematics ;
SQL sentence ：
select snO, sname from student where sno in ( select sno from student course
where cno in(select cno from course where cname=' Advanced mathematics ')) ;
2） seek C1 The student number and grades of the course are higher than those of Zhang San ;
SQL sentence ：
select sno, score from student course where exists (select sno from s tudent
student course where sname= ' Zhang San 'and cno='C1 ');
3） Find information about students in other departments who are younger than a student in the computer department （ That is to say, the oldest student in the computer department is the younger one in other departments ）;
SQL sentence ：
select * from student where sage< (select MAX (sage) from student where spo=
'CS ' ) and spo<>'cs '
4） Ask for information about students younger than computer students in other departments ;
sql sentence ：
select * from student where sage< < al1 . (select sage from student where spo=
'Cs ' ) and spo<>'cs '
5） Please take an elective course C2 Student name of the course ;
sql sentence ：
select sname from student where exists ( select * from student course where sno
=student.sno and cno=' C2 ' )
6） Please don't take elective courses C2 Student name of the course ;
sql sentence ：
select sname from student where not exists (select * from student course Where
sno=student.snO and cno= 'C2’ )
three , Experimental summary
This test mainly requires us to master the representation of sub queries , enter - Step master SQL Server
How to use query analyzer , And deepen the pair SQL Understanding of nested query statements in language . Through this experiment, I am right again SQL Better understanding of the sentence , Consolidated SQL
grammar , More familiar SQLServer2005 Grammatical structure of , Have a deeper grasp of nested queries .
Technology