4實(shí)驗(yàn)四-復(fù)雜查詢

《4實(shí)驗(yàn)四-復(fù)雜查詢》由會(huì)員分享，可在線閱讀，更多相關(guān)《4實(shí)驗(yàn)四-復(fù)雜查詢（11頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

實(shí)驗(yàn)四 復(fù)雜查詢 一、實(shí)驗(yàn)?zāi)康? 掌握兩個(gè)表以上的連接查詢的應(yīng)用，包括嵌套查詢。 二、實(shí)驗(yàn)內(nèi)容 （1）查詢比“林紅”年紀(jì)大的男學(xué)生信息。 select * from Student where Sex = 男 and YEAR(Birth)-(select YEAR(Birth) from Student where Sname =林紅)<0 （2）檢索所有學(xué)生的選課信息，包括學(xué)號(hào)、姓名、課號(hào)、課程名、成績(jī)。 select SC.Sno,Sname,Sex,Classno,Cname,Grade from Student s,SC,Course c where s.Sno=SC.Sno and SC.cno=c.cno （3）查詢已選課學(xué)生的學(xué)號(hào)、姓名、課程名、成績(jī)。 select SC.Sno,Sname,Cname,Grade from Student s,course c,SC where s.sno=SC.sno and c.cno=SC.cno （4）查詢選修了“C語(yǔ)言程序設(shè)計(jì)”的學(xué)生的學(xué)號(hào)和姓名。 select sc.Sno,Sname from Student s,course c,sc where c.Cname=C語(yǔ)言程序設(shè)計(jì) and s.Sno=sc.Sno and sc.Cno=c.Cno （5）查詢與“張虹”在同一個(gè)班級(jí)的學(xué)生學(xué)號(hào)、姓名、家庭住址。 a.用子查詢 select Sno,Sname,Home_addr from Student where Classno=051 and Sname!=張虹 b.用連接查詢 select Sno,Sname,Home_addr from Student where Classno=(select Classno from Student where Sname=張虹) and Sname!=張虹 （6）查詢其他班級(jí)中比“051”班所有學(xué)生年齡大的學(xué)生的學(xué)號(hào)、姓名。 select Sno,Sname from Student where Classno <> 051 and Birth < all(select Birth from Student where Classno = 051) （7）(選作)查詢選修了全部課程的學(xué)生姓名。 本題使用除運(yùn)算的方法。 由題意可得另一種語(yǔ)言，沒(méi)有一個(gè)選了課的學(xué)生沒(méi)有選course表里的課程。那么，我們需要兩個(gè)NOT EXISTS表示雙重否定；另一種思路可詳見(jiàn)書例4.52 select Sname from Student where not exists( select * from Course where not exists( select * from SC where Sno=Student.sno and cno=Course.cno)) （8）(選作)查詢至少選修了學(xué)生“20110002”選修的全部課程的學(xué)生的學(xué)號(hào)，姓名。 select Sno,Sname from Student where Sno in( select distinct Sno from SC as SC1 where not exists( select * from SC as SC2 where SC2.Sno=20110002 and not exists( select * from SC as SC3 where SC3.Sno=SC1.Sno and SC3.cno=SC2.cno)) ) （9）檢索學(xué)生的學(xué)號(hào)、姓名、學(xué)習(xí)課程名及課程成績(jī)。 select s.Sno,Sname,Cname,Grade from Student s,Course c,SC where s.Sno=sc.Sno and sc.Cno=c.Cno （10）檢索選修了“高數(shù)”課且成績(jī)至少高于選修課程號(hào)為“002”課程的學(xué)生的學(xué)號(hào)、課程號(hào)、成績(jī)，并按成績(jī)從高到低次序排列。 由題意得，選修了高數(shù)課的學(xué)生的成績(jī)要高于選修002課號(hào)課程的學(xué)生的成績(jī) select distinct Sno,Cno,Grade from SC where Cno in( select Cno from Course where Cname=高數(shù)) and Grade>(select MAX(Grade) from SC where cno=002) order by Grade desc （11）檢索選修3門以上課程的學(xué)生的學(xué)號(hào)、總成績(jī)（不統(tǒng)計(jì)不及格的課程），并要求按總成績(jī)的降序排列出來(lái)。 select Sno,sum(grade) as 總成績(jī) from SC where Sno in( select Sno from SC group by Sno having count(*)>3) and Grade>=60 group by Sno order by 總成績(jī) desc （12）檢索多于3名學(xué)生選修的并以3結(jié)尾的課程號(hào)的平均成績(jī)。 select avg(Grade) as 平均成績(jī) from SC where Cno like %3 group by Cno having count(Cno)>3 （13）檢索最高分與最低分之差大于5分的學(xué)生的學(xué)號(hào)、姓名、最高分、最底分。 select distinct SC.Sno 學(xué)號(hào),Sname 姓名, max(grade) as 最高分,min(grade)as 最低分 from Student,SC where SC.Sno=Student.Sno group by SC.Sno,Sname having max(grade)-min(grade)>5 （14）外連接 對(duì)實(shí)驗(yàn)二中的表6和表7做一個(gè)外連接查詢，顯示每門課程的課號(hào)、課名、選修該門課的學(xué)號(hào)、成績(jī)，沒(méi)有同學(xué)選修的課程（如Visual_Basic）也要在查詢結(jié)果中。 select c.Cno 課號(hào),Cname 課名,Sno 學(xué)號(hào),Grade 成績(jī) from Course c left outer join SC on (c.Cno=SC.Cno) （15）創(chuàng)建一個(gè)表Student_other，結(jié)構(gòu)同Student,輸入若干記錄，部分記錄和Student表中的相同。 創(chuàng)建過(guò)程： create table Student_other( Sno char(8) primary key, Sname varchar(8) not null, Sex char(2) not null, Birth smalldatetime not null, Classno char(3) not null, Entrance_date smalldatetime not null, Home_addr varchar(40), Sdept char(2) not null, Postcode char(6) ) 隨意輸入幾條Student表中沒(méi)有的信息，完成創(chuàng)建 a.查詢同時(shí)出現(xiàn)在Student表和Student_other表中的記錄 select * from student_other so,Student s where so.Sno=s.Sno b. 查詢Student表和Student_other表中的全部記錄 select * from student union select * from student_other （16）（選作）創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)Student_info_other,參數(shù)自定。 創(chuàng)建過(guò)程： 新建數(shù)據(jù)庫(kù) 名稱確定，參數(shù)自定義，然后“確定”即可 a.當(dāng)前數(shù)據(jù)庫(kù)為Student_info，將Student_info數(shù)據(jù)庫(kù)中的Student_other復(fù)制到Student_info_other中。 select * into Student_info_other.dbo.Student_other from Student_info.dbo.Student_other b.查詢同時(shí)出現(xiàn)在Student表和Student_info_other數(shù)據(jù)庫(kù)Student_other表中的記錄。 select * from Student_info_other.dbo.student_other so, Student_info.dbo.Student s where so.sno=s.sno 11