目錄

• 前言

前言

本篇可當做例題練習,

1.查詢比”林紅”年紀大的男學生信息
語句:

select *from Studentwhere Sex="男" and 	year(Birth)-(select year(Birth)from Student--這里是需要告訴查詢的表名,相當于嵌套	where Sname="林紅")<0

2.檢索所有學生的選課信息,包括學號、姓名、課程名、成績,性別.
語句:

select sc.sno,sname, course.Cno,Cname,Grade,Sex--這里如果兩個表中都有同一個屬性,則需要標明在哪個表,如sc.snofrom student,sc,Coursewhere student.Sno=sc.Sno and Sc.Cno=course.Cno

3.查詢已經選課的學生的學號、姓名、課程名、成績.
語句:

select sc.sno ,sname , Cname , Gradefrom student s , course c, scwhere s.sno=sc.sno and c.cno=sc.cno

(4)查詢選修了“C語言程序設計”的學生的學號與姓名
–a.用內連接查詢
語句:

select sc.Sno,sname from student inner join sc onstudent.Sno=sc.Sno inner join course on sc.Cno =course.cnoand Cname="C語言程序設計"

–b.用連接查詢
語句:

select sc.Sno,sname from student,sc,course wherestudent .Sno=sc.Sno and sc.Cno =course.cnoand Cname="C語言程序設計"

–c.用子查詢
語句:

select Sno,sname from student where Sno in(select Sno from sc where Cno=(select cno from course where Cname ="C語言程序設計"))

(5)查詢與”張虹”在同一個班級的學生學號、姓名、家庭住址
–a．用連接查詢
語句:

select a.Sno,a.sname,a.Home_addr from student a,student b where a.Classno =b.Classno and b.Sname ="張虹" and a.Sname!="張虹"

–b．用子查詢
語句:

select Sno,sname,Home_addr  from student whereclassno=(select classno from student where sname="張虹")and sname!="張虹"

(6)查詢其他班級中比”051”班所有學生年齡大的學生的學號、姓名
代碼1:

select Sno,sname,Home_addr  from student whereclassno!="051" and Birth<all (select Birth  from student where classno="051")

代碼2:

select Sno,sname,Home_addr  from student whereclassno!="051" and Birth<(select min(Birth)  from student where classno="051")

(7）(選作)查詢選修了全部課程的學生姓名。本題使用除運算的方法。
–由題意可得另一種語言，沒有一個選了課的學生沒有選course表里的課程。那么，我們需要兩個NOT EXISTS表示雙重否定;
語句:

select Sname from studentwhere not exists (select * from coursewhere not exists (select * from scwhere sno=student. snoand cno=Course.cno))

(8）查詢至少選修了學生“20110002”選修的全部課程的學生的學號，姓名。
語句:

select Sno, Sname from studentwhere sno in (select distinct sno from sc as sc1where not exists (select * from sc as sc2 where sc2.sno="20110002"and not exists (select * from sc as sc3 where sc3.Sno=sc1.sno andsc3.cno=sC2.cno) ))

(9）檢索選修了“高數”課且成績至少高于選修課程號為“002"課程的學生的學號、課程號、成績，并按成績從高到低排列。
語句:

select sc.Sno, sc.cno , grade from sc wheregrade >all(select grade from sc where cno="002" ) andCno= (select Cnofrom course where Cname="高數")order by Grade desc

(10)檢索選修了至少3門以上課程的學生的學號、總成績(不統計不及格的成績)，并要求按總成績降序排列。
語句:

select sno,SUM(grade) from sc where sno in (select Sno from sc group by snohaving COUNT(*)>=3) and Grade>=60 group by snoorder by SUM (grade) desc

(12）檢索多于3名學生選修的并以3結尾的課程號的平均成績。
語句:

select avg(Grade) as 平均成績from scwhere Cno like "%3" group by cnohaving count (Cno)>3

(13）檢索最高分與最低分之差大于5分的學生的學號、姓名、最高分、最底分。

select distinct sc.sno 學號,sname 姓名,max (grade) as最高分,min (grade) as最低分from student,scwhere sc.sno=student.Sno group by sc.sno , Snamehaving max(grade) -min (grade) >5

(14)創建一個表Student_other，結構同student,輸入若干記錄，部分記錄和student表中的相同。
–創建過程:

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表中沒有的數據:

–a.查詢同時出現在Student表和student_other表中的記錄
語句:

select * from student__other so ,student swhere so.sno=s.sno

----b．查詢Student表和Student_other表中的全部記錄
代碼:

select * from studentunionselect * from student__other

到此這篇關于教你使用SQL語句進行數據庫復雜查詢的文章就介紹到這了,更多相關sql復雜查詢語句內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！