一個(gè)項(xiàng)目涉及到的個(gè)sql語(yǔ)句 弄懂后做項(xiàng)目沒壓力了

1、/p/1668756067說明：以下五十個(gè)語(yǔ)句都按照測(cè)試數(shù)據(jù)進(jìn)行過測(cè)試，最好每次只單獨(dú)運(yùn)行一個(gè)語(yǔ)句。 問題及描述： -1.學(xué)生表 Student(Sno,Sname,Sage,Ssex) -Sno 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別 -2.課程表 Course(Cno,Cname,Tno) -Cno -課程編號(hào),Cname 課程名稱,Tno 教師編號(hào) -3.教師表 Teacher(Tno,Tname) -Tno 教師編號(hào),Tname 教師姓名 -4.成績(jī)表 SC(Sno,Cno,Score) -Sno 學(xué)生編號(hào),Cno 課程編號(hào),score 分?jǐn)?shù) */-創(chuàng)建

2、測(cè)試數(shù)據(jù) -1.學(xué)生表 Student(Sno,Sname,Sage,Ssex)create table Student(Sno varchar2(10),Sname varchar2(10),Sage date,Ssex varchar2(10)insert into Student(sno,sname,sage,ssex) values(01 , 趙雷 , 1-1月-1990 ,男); insert into Student(sno,sname,sage,ssex) values(02 , 錢電 , 21-12月-1990 ,男); insert into Student(sno,snam

3、e,sage,ssex) values(03 , 孫風(fēng) , 20-5月-1990 ,男); insert into Student(sno,sname,sage,ssex) values(04 , 李云 , 6-8月-1990 ,男); insert into Student(sno,sname,sage,ssex) values(05 , 周梅 , 1-12月-1991 , 女); insert into Student(sno,sname,sage,ssex) values(06 , 吳蘭 , 1-3月-1992 , 女); insert into Student(sno,sname,sa

4、ge,ssex) values(07 , 鄭竹 , 1-7月-1989 , 女); insert into Student(sno,sname,sage,ssex) values(08 , 王菊 , 20-1月-1990 , 女); -2.課程表 Course(Cno,Cname,Tno)create table Course(Cno varchar2(10),Cname nvarchar2(10),Tno varchar2(10)insert into Course(Cno,Cname,Tno)values(01 , 語(yǔ)文 , 02); insert into Course(Cno,Cnam

5、e,Tno) values(02 , 數(shù)學(xué) , 01); insert into Course(Cno,Cname,Tno) values(03 , 英語(yǔ) , 03); -3.教師表 Teacher(Tno,Tname)create table Teacher(Tno varchar2(10),Tname varchar2(10)insert into Teacher(Tno,Tname) values(01 , 張三); insert into Teacher(Tno,Tname) values(02 , 李四); insert into Teacher(Tno,Tname) values(

6、03 , 王五); -4.成績(jī)表 SC(Sno,Cno,Score) create table SC(Sno varchar2(10),Cno varchar2(10),score decimal(18,1)insert into SC(Sno,Cno,score) values(01 , 01 , 80); insert into SC(Sno,Cno,score) values(01 , 02 , 90); insert into SC(Sno,Cno,score) values(01 , 03 , 99); insert into SC(Sno,Cno,score) values(02

7、, 01 , 70); insert into SC(Sno,Cno,score) values(02 , 02 , 60); insert into SC(Sno,Cno,score) values(02 , 03 , 80); insert into SC(Sno,Cno,score) values(03 , 01 , 80); insert into SC(Sno,Cno,score) values(03 , 02 , 80); insert into SC(Sno,Cno,score) values(03 , 03 , 80); insert into SC(Sno,Cno,score

8、) values(04 , 01 , 50); insert into SC(Sno,Cno,score) values(04 , 02 , 30); insert into SC(Sno,Cno,score) values(04 , 03 , 20); insert into SC(Sno,Cno,score) values(05 , 01 , 76); insert into SC(Sno,Cno,score) values(05 , 02 , 87); insert into SC(Sno,Cno,score) values(06 , 01 , 31); insert into SC(S

9、no,Cno,score) values(06 , 03 , 34); insert into SC(Sno,Cno,score) values(07 , 02 , 89); insert into SC(Sno,Cno,score) values(07 , 03 , 98);-1、查詢01課程比02課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)-1.1、查詢同時(shí)存在01課程和02課程的情況select a.* , b.score 課程01的分?jǐn)?shù),c.score 課程02的分?jǐn)?shù)from Student a , SC b , SC c where a.Sno = b.Sno and a.Sno = c.Sno

10、 and b.Cno =01and c.Cno =02and b.score c.score-1.2、查詢同時(shí)存在01課程和02課程的情況和存在01課程但可能不存在02課程的情況(不存在時(shí)顯示為null)(以下存在相同內(nèi)容時(shí)不再解釋)select a.* , b.score 課程01的分?jǐn)?shù),c.score 課程02的分?jǐn)?shù)from Student a leftjoin SC b on a.Sno = b.Sno and b.Cno =01leftjoin SC c on a.Sno = c.Sno and c.Cno =02where b.score isnull(c.score,0)-2、查詢

11、01課程比02課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)-2.1、查詢同時(shí)存在01課程和02課程的情況select a.* , b.score 課程01的分?jǐn)?shù),c.score 課程02的分?jǐn)?shù)from Student a , SC b , SC c where a.Sno = b.Sno and a.Sno = c.Sno and b.Cno =01and c.Cno =02and b.score c.score-2.2、查詢同時(shí)存在01課程和02課程的情況和不存在01課程但存在02課程的情況select a.* , b.score 課程01的分?jǐn)?shù),c.score 課程02的分?jǐn)?shù)from Student

12、a leftjoin SC b on a.Sno = b.Sno and b.Cno =01leftjoin SC c on a.Sno = c.Sno and c.Cno =02whereisnull(b.score,0) =60orderby a.Sno-4、查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)-4.1、查詢?cè)趕c表存在成績(jī)的學(xué)生信息的SQL語(yǔ)句。select a.Sno , a.Sname , cast(avg(b.score) asdecimal(18,2) avg_scorefrom Student a , sc bwhere a.Sno = b.Snogrou

13、pby a.Sno , a.Snamehavingcast(avg(b.score) asdecimal(18,2) 60orderby a.Sno-4.2、查詢?cè)趕c表中不存在成績(jī)的學(xué)生信息的SQL語(yǔ)句。select a.Sno , a.Sname , isnull(cast(avg(b.score) asdecimal(18,2),0) avg_scorefrom Student a leftjoin sc bon a.Sno = b.Snogroupby a.Sno , a.Snamehavingisnull(cast(avg(b.score) asdecimal(18,2),0) 60

14、orderby a.Sno-5、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)-5.1、查詢所有有成績(jī)的SQL。select a.Sno 學(xué)生編號(hào), a.Sname 學(xué)生姓名, count(b.Cno) 選課總數(shù), sum(score) 所有課程的總成績(jī)from Student a , SC bwhere a.Sno = b.Snogroupby a.Sno,a.Snameorderby a.Sno-5.2、查詢所有(包括有成績(jī)和無成績(jī))的SQL。select a.Sno 學(xué)生編號(hào), a.Sname 學(xué)生姓名, count(b.Cno) 選課總數(shù), sum(score) 所有課程

15、的總成績(jī)from Student a leftjoin SC bon a.Sno = b.Snogroupby a.Sno,a.Snameorderby a.Sno-6、查詢李姓老師的數(shù)量-方法1selectcount(Tname) 李姓老師的數(shù)量from Teacher where Tname like N李%-方法2selectcount(Tname) 李姓老師的數(shù)量from Teacher whereleft(Tname,1) = N李-7、查詢學(xué)過張三老師授課的同學(xué)的信息selectdistinct Student.*from Student , SC , Course , Teach

16、erwhere Student.Sno = SC.Sno and SC.Cno = Course.Cno and Course.Tno = Teacher.Tno and Teacher.Tname = N張三orderby Student.Sno-8、查詢沒學(xué)過張三老師授課的同學(xué)的信息select m.*from Student m where Sno notin (selectdistinct SC.Sno from SC , Course , Teacher where SC.Cno = Course.Cno and Course.Tno = Teacher.Tno and Teache

17、r.Tname = N張三) orderby m.Sno-9、查詢學(xué)過編號(hào)為01并且也學(xué)過編號(hào)為02的課程的同學(xué)的信息-方法1select Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno =01andexists (Select1from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =02) orderby Student.Sno-方法2select Student.*from Student , SC where Student.Sno = SC.Sno and S

18、C.Cno =02andexists (Select1from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =01) orderby Student.Sno-方法3select m.*from Student m where Sno in(select Sno from(selectdistinct Sno from SC where Cno =01unionallselectdistinct Sno from SC where Cno =02) t groupby Sno havingcount(1) =2)orderby m.Sno-10、查詢

19、學(xué)過編號(hào)為01但是沒有學(xué)過編號(hào)為02的課程的同學(xué)的信息-方法1select Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno =01andnotexists (Select1from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =02) orderby Student.Sno-方法2select Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno =01and Student.Sno noti

20、n (Select SC_2.Sno from SC SC_2 where SC_2.Sno = SC.Sno and SC_2.Cno =02) orderby Student.Sno-11、查詢沒有學(xué)全所有課程的同學(xué)的信息-11.1、select Student.*from Student , SCwhere Student.Sno = SC.Snogroupby Student.Sno , Student.Sname , Student.Sage , Student.Ssex havingcount(Cno) (selectcount(Cno) from Course)-11.2sele

21、ct Student.*from Student leftjoin SCon Student.Sno = SC.Snogroupby Student.Sno , Student.Sname , Student.Sage , Student.Ssex havingcount(Cno) (selectcount(Cno) from Course)-12、查詢至少有一門課與學(xué)號(hào)為01的同學(xué)所學(xué)相同的同學(xué)的信息selectdistinct Student.*from Student , SC where Student.Sno = SC.Sno and SC.Cno in (select Cno fr

22、om SC where Sno =01) and Student.Sno 01-13、查詢和01號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息select Student.*from Student where Sno in(selectdistinct SC.Sno from SC where Sno 01and SC.Cno in (selectdistinct Cno from SC where Sno =01)groupby SC.Sno havingcount(1) = (selectcount(1) from SC where Sno=01)-14、查詢沒學(xué)過張三老師講授的任一門課程的

23、學(xué)生姓名select student.*from student where student.Sno notin(selectdistinct sc.Sno from sc , course , teacher where sc.Cno = course.Cno and course.Tno = teacher.Tno and teacher.tname = N張三)orderby student.Sno-15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī) select student.Sno , student.sname , cast(avg(score) asdecimal(

24、18,2) avg_score from student , sc where student.Sno = SC.Sno and student.Sno in (select Sno from SC where score =2)groupby student.Sno , student.sname-16、檢索01課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息select student.* , sc.Cno , sc.score from student , sc where student.Sno = SC.Sno and sc.score =60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀

25、為：=90-方法1select m.Cno 課程編號(hào), m.Cname 課程名稱,max(n.score) 最高分,min(n.score) 最低分,cast(avg(n.score) asdecimal(18,2) 平均分,cast(selectcount(1) from SC where Cno = m.Cno and score =60)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 及格率(%),cast(selectcount(1) from SC where Cno = m.Cno and sco

26、re =70and score =80and score =90)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 優(yōu)秀率(%)from Course m , SC nwhere m.Cno = n.Cnogroupby m.Cno , m.Cnameorderby m.Cno-方法2select m.Cno 課程編號(hào), m.Cname 課程名稱,(selectmax(score) from SC where Cno = m.Cno) 最高分,(selectmin(score) from SC where Cn

27、o = m.Cno) 最低分,(selectcast(avg(score) asdecimal(18,2) from SC where Cno = m.Cno) 平均分,cast(selectcount(1) from SC where Cno = m.Cno and score =60)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 及格率(%),cast(selectcount(1) from SC where Cno = m.Cno and score =70and score =80and score

28、 =90)*100.0/ (selectcount(1) from SC where Cno = m.Cno) asdecimal(18,2) 優(yōu)秀率(%)from Course morderby m.Cno-19、按各科成績(jī)進(jìn)行排序，并顯示排名-19.1 sql 2000用子查詢完成-Score重復(fù)時(shí)保留名次空缺select t.* , px = (selectcount(1) from SC where Cno = t.Cno and score t.score) +1from sc t orderby o , px-Score重復(fù)時(shí)合并名次select t.* , px = (selec

29、tcount(distinct score) from SC where Cno = t.Cno and score = t.score) from sc t orderby o , px-19.2 sql 2005用rank,DENSE_RANK完成-Score重復(fù)時(shí)保留名次空缺(rank完成)select t.* , px = rank() over(partition by cno orderby score desc) from sc t orderby t.Cno , px-Score重復(fù)時(shí)合并名次(DENSE_RANK完成)select t.* , px = DENSE_RANK(

30、) over(partition by cno orderby score desc) from sc t orderby t.Cno , px-20、查詢學(xué)生的總成績(jī)并進(jìn)行排名-20.1 查詢學(xué)生的總成績(jī)select m.Sno 學(xué)生編號(hào) ,m.Sname 學(xué)生姓名 ,isnull(sum(score),0) 總成績(jī)from Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Snameorderby總成績(jī)desc-20.2 查詢學(xué)生的總成績(jī)并進(jìn)行排名，sql 2000用子查詢完成，分總分重復(fù)時(shí)保留名次空缺和不保留名次空缺兩種。

31、select t1.* , px = (selectcount(1) from(select m.Sno 學(xué)生編號(hào) ,m.Sname 學(xué)生姓名 ,isnull(sum(score),0) 總成績(jī)from Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Sname) t2 where 總成績(jī) t1.總成績(jī)) +1from(select m.Sno 學(xué)生編號(hào) ,m.Sname 學(xué)生姓名 ,isnull(sum(score),0) 總成績(jī)from Student m leftjoin SC n on m.Sno = n.Snogro

32、upby m.Sno , m.Sname) t1orderby pxselect t1.* , px = (selectcount(distinct 總成績(jī)) from(select m.Sno 學(xué)生編號(hào) ,m.Sname 學(xué)生姓名 ,isnull(sum(score),0) 總成績(jī)from Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Sname) t2 where 總成績(jī) = t1.總成績(jī)) from(select m.Sno 學(xué)生編號(hào) ,m.Sname 學(xué)生姓名 ,isnull(sum(score),0) 總成績(jī)fro

33、m Student m leftjoin SC n on m.Sno = n.Snogroupby m.Sno , m.Sname) t1orderby px-20.3 查詢學(xué)生的總成績(jī)并進(jìn)行排名，sql 2005用rank,DENSE_RANK完成，分總分重復(fù)時(shí)保留名次空缺和不保留名次空缺兩種。select t.* , px = rank() over(orderby總成績(jī)desc) from(select m.Sno 學(xué)生編號(hào) , m.Sname 學(xué)生姓名 ,isnull(sum(score),0) 總成績(jī)from Student m leftjoin SC n on m.Sno = n.

34、Sno groupby m.Sno , m.Sname) torderby pxselect t.* , px = DENSE_RANK() over(orderby總成績(jī)desc) from(select m.Sno 學(xué)生編號(hào) , m.Sname 學(xué)生姓名 ,isnull(sum(score),0) 總成績(jī)from Student m leftjoin SC n on m.Sno = n.Sno groupby m.Sno , m.Sname) torderby px-21、查詢不同老師所教不同課程平均分從高到低顯示select m.Tno , m.Tname , cast(avg(o.sc

35、ore) asdecimal(18,2) avg_scorefrom Teacher m , Course n , SC owhere m.Tno = n.Tno and n.Cno = o.Cnogroupby m.Tno , m.Tnameorderby avg_score desc-22、查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)-22.1 sql 2000用子查詢完成-Score重復(fù)時(shí)保留名次空缺select*from (select t.* , px = (selectcount(1) from SC where Cno = t.Cno and score t.score

36、) +1from sc t) m where px between2and3orderby o , m.px-Score重復(fù)時(shí)合并名次select*from (select t.* , px = (selectcount(distinct score) from SC where Cno = t.Cno and score = t.score) from sc t) m where px between2and3orderby o , m.px -22.2 sql 2005用rank,DENSE_RANK完成-Score重復(fù)時(shí)保留名次空缺(rank完成)select*from (select

37、t.* , px = rank() over(partition by cno orderby score desc) from sc t) m where px between2and3orderby m.Cno , m.px-Score重復(fù)時(shí)合并名次(DENSE_RANK完成)select*from (select t.* , px = DENSE_RANK() over(partition by cno orderby score desc) from sc t) m where px between2and3orderby m.Cno , m.px-23、統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程

38、編號(hào),課程名稱,100-85,85-70,70-60,0-60及所占百分比-23.1 統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,100-85,85-70,70-60,0-60-橫向顯示select Course.Cno 課程編號(hào) , Cname as課程名稱 ,sum(casewhen score =85then1else0end) 85-100,sum(casewhen score =70and score =60and score 70then1else0end) 60-70,sum(casewhen score =85then85-100when n.score =70and n.s

39、core =60and n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score =60and n.score 70then60-70else0-60end)orderby m.Cno , m.Cname , 分?jǐn)?shù)段-23.2 統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,100-85,85-70,70-60,60及所占百分比-橫向顯

40、示select m.Cno 課程編號(hào), m.Cname 課程名稱,(selectcount(1) from SC where Cno = m.Cno and score 60) 0-60,cast(selectcount(1) from SC where Cno = m.Cno and score =60and score =60and score =70and score =70and score =85) 85-100,cast(selectcount(1) from SC where Cno = m.Cno and score =85)*100.0/ (selectcount(1) fr

41、om SC where Cno = m.Cno) asdecimal(18,2) 百分比(%)from Course morderby m.Cno-縱向顯示1(顯示存在的分?jǐn)?shù)段)select m.Cno 課程編號(hào) , m.Cname 課程名稱 , 分?jǐn)?shù)段 = (casewhen n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score =60and n.score =85then85-100when n.score =70and n.score

42、 =60and n.score =85then85-100when n.score =70and n.score =60and n.score t1.平均成績(jī)) +1from (select m.Sno 學(xué)生編號(hào) , m.Sname 學(xué)生姓名 ,isnull(cast(avg(score) asdecimal(18,2),0) 平均成績(jī)from Student m leftjoin SC n on m.Sno = n.Sno groupby m.Sno , m.Sname) t1orderby pxselect t1.* , px = (selectcount(distinct 平均成績(jī)) from (select m.Sno 學(xué)生編號(hào) , m.Sname 學(xué)生姓名 ,isnull(cast(avg(score) asdecimal(18,2),0) 平均成績(jī)from Student m leftjoin SC n on m.Sno = n.Sno groupby m.Sno , m.Sname) t2 where 平均成績(jī) = t1.平均成績(jī)) from (select m.Sno 學(xué)生編號(hào) , m.Sname 學(xué)生姓名 ,isnull(cast(avg(sc