oracle 版sql五十題，親手驗證過

- --1.學生表 Student(S,Sname,Sage,Ssex) --S 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別 --2.課程表 Course(C,Cname,T) --C --課程編號,Cname 課程名稱,T 教師編號 --3.教師表 Teacher(T,Tname) --T 教師編號,Tname 教師姓名 --4.成績表 SC(S,C,score) --S 學生編號,C 課程編號,score 分數

------------------------------------------------------------------------ */ --創建測試數據

-------- create table Student(S varchar(10),Sname varchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values(01 , 趙雷 , 1990-01-01 , 男) insert into Student values(02 , 錢電 , 1990-12-21 , 男) insert into Student values(03 , 孫風 , 1990-05-20 , 男) insert into Student values(04 , 李雲 , 1990-08-06 , 男) insert into Student values(05 , 周梅 , 1991-12-01 , 女) insert into Student values(06 , 吳蘭 , 1992-03-01 , 女) insert into Student values(07 , 鄭竹 , 1989-07-01 , 女) insert into Student values(08 , 王菊 , 1990-01-20 , 女) create table Course(C varchar(10),Cname,varchar(10),T varchar(10)) insert into Course values(01 , 語文 , 02) insert into Course values(02 , 數學 , 01) insert into Course values(03 , 英語 , 03) create table Teacher(T varchar(10),Tname,varchar(10)) insert into Teacher values(01 , 張三) insert into Teacher values(02 , 李四) insert into Teacher values(03 , 王五) create table SC(S varchar(10),C varchar(10),score decimal(18,1)) insert into SC values(01 , 01 , 80) insert into SC values(01 , 02 , 90) insert into SC values(01 , 03 , 99) insert into SC values(02 , 01 , 70) insert into SC values(02 , 02 , 60) insert into SC values(02 , 03 , 80) insert into SC values(03 , 01 , 80) insert into SC values(03 , 02 , 80) insert into SC values(03 , 03 , 80) insert into SC values(04 , 01 , 50) insert into SC values(04 , 02 , 30) insert into SC values(04 , 03 , 20) insert into SC values(05 , 01 , 76) insert into SC values(05 , 02 , 87) insert into SC values(06 , 01 , 31) insert into SC values(06 , 03 , 34) insert into SC values(07 , 02 , 89) insert into SC values(07 , 03 , 98) --1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數

--------------------------------- Select * from student t1,( Select a.s,a.score from (select s,score,c from sc where sc.c=』01』 ) a, Select a.s,a.score from (select s,score,c from sc where sc.c=』02』 ) b Where a.s=b.s and a.score<b.score) t2 Where t1.s=t2.s --2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數

--------------------------------- Select a.*,c.* From ( Select a.* from (select * from sc where sc.c in (『01』))a Left join (select * from sc where sc.c in (『02』)) b On a.s=b.s where a.score,b.score) a, student c Where a.s=c.s --3、查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

----------------------------------- Select T2.sname,t2.s,t3.e from student t2, (select t1.s,t1.e from (select s,avg(score) e from sc group by s) t1 Where t1.e>60) t3 Where t2.s=t3.s --4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績

--------------------------------- Select t2.sname,t2.s,t3.e from student t2,(select t1.s,t1.e from (select s,avg(score) e from sc group by s) t1 Where t1.e<60 ) t3 Where t2.s=t3.s --5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

---------------------------------- Select t.s,t.sname, count(c),sum(score) From(select st.s, st.sname,sc.c,sc.score from student st inner join sc on st.s=sc.s) t Group by t.s,t.sname Order by t.s --6、查詢"李"姓老師的數量

--------------- SELECT COUNT(a.Tname) FROM (SELECT Tname Tname FROM teacher WHERE Tname LIKE 李%) a --7、查詢學過"張三"老師授課的同學的信息

---------------------- Select st.* from course c Inner join teacher t on c.t=t.t Inner join sc on sc.c=c.c Inner join student st on st.s=sc.s Where t.tname =』張三』 --8、查詢沒學過"張三"老師授課的同學的信息

----------------------- Select t2.s,t2.sname,t2.sage,t2.ssex From student t2 where t2.s not in ( Select st.s from course c Inner join teacher t on c.t=t.t Inner join sc on sc.c=c.c Inner join student st on st.s=sc.c Where t.tname=』張三』 ) --9、查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息

------------------------------------ Select * From ( Select sc.c from sc where sc.s=』01』) t1 Inner join (select sc.s from sc where sc.c=』02』) t2 on t1.s=t2.s Inner join student st on t1.s=st.s --10、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

-------------------------------------- Select st.* from ( select sc.s from sc where sc.c=』01』 and sc.s not in ( select sc.s from sc wehre sc.c=』02』)) t1 Inner join student st on t1.s=st.s --11、查詢沒有學全所有課程的同學的信息

--------------------- Select st.* from (select sc.s from sc group by sc.s having count (sc.s)<3)t1, Student st where t1.s=st.s --12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

---------------------------------- Select * from student st ,(Select distinct s from sc where sc.c In (Select sc.c from sc where sc.s =』01』)) t2 Where st.s=t2.s --13、查詢和"01"號的同學學習的課程完全相同的其他同學的信息

--------------------------------- Select sc.s from sc where sc.s!=』01』 and sc.c in (select sc.c from sc.c where sc.s=』01』) Group by sc.s Having count(sc.s) =(select count(*) from sc where sc.s=』01』) --14、查詢沒學過"張三"老師講授的任一門課程的學生姓名

----------------------------- Select * from student st where st.s not in ( select sc.c from sc,(select c.c,c.cname from teacher t inner join course c on t.t=c.t where t.tname=』張三』 )where t1.c=sc.c ) --15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

--------------------------------- Select st.*,t1.score from student st , (select sc.s,avg(sc.score) score from sc where sc.score <60 group by sc.s having count(sc.score)>1) t1 where st.s=t1.s --16、檢索"01"課程分數小於60，按分數降序排列的學生信息

-------------------------------- Select t1.*,st.* from student st, (select sc.s,sc.score from sc where sc.c=』01』 and sc.score<60 Order by sc.score desc)t1 Where st.s=t1.s --17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

---------------------------------- Select st.*,sc.c,sc.score ,t1.score from student st inner join sc on sc.s=st.s Inner join ( select sc.s,avg(sc.score) score from sc group by sc.s)t1 on st.s=t1.s Order by t1.score desc - -18、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率 --及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

------------------------------------------------------------------------ Select c.c 課程ID, c.cname課程name, t1.sx 最高分, t1.sn最低分, t1.sa 平均分, t1.t60 及格率, t1.t70 中等率, t1.t80 優良率, t1.t90 優秀率 From course c Left join ( select sc.c,max(sc.score) sx, min(sc.score) sn, avg(sc.score)sa, (select count(*) from sc where sc.score>=60)*100/(select count(*) from sc) t60, (select count(*) from sc where sc.score>=70 and sc.score<80)*100/(select count(*) from sc) t70, (select count(*) from sc where sc.score>=80 and sc.score<90)*100/(select count(*) from sc) t80, (select count(*) from sc where sc.score>=90)*100/(select count(*) from sc) t90 from sc group by sc.c) t1 on t1.c=c.c --19、按各科成績進行排序，並顯示排名

-------------------- Select t1.*,rownum From (select sc.c,avg(sc.score) avgscore from sc group by sc.c Order by avgscore desc )t1 --20、查詢學生的總成績並進行排名

------------------ Select t1.*,rownm from (select sc.s,sum(sc.score) from sc Group by sc.s Order by sum(sc.score) desc ) t1 --21、查詢不同老師所教不同課程平均分從高到低顯示

-------------------------- Select t.t,t.tname,t1.name, t1.score From teacher t ,(select distinct t.t ,avg(sc.score) score From sc inner join course c on sc.c=c.c Inner join teacher t on t.t=c.t Group by t.t,c.c) t1 Where t.t=t1.t Order by t1.score desc --22、查詢所有課程的成績第2名到第3名的學生信息及該課程成績

-------------------------------- Select St.sname, T1.c, T1.score, T1.rn From ( select sc.s, sc.c, sc.score, Dense_rank()over(partition by sc.c order by sc.score desc) rn From sc) t1,student st Where st.s=t1.s and t1.rn>=2 and t1.rn< 4 --23、統計各科成績各分數段人數：課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]及所佔百分比

----------------------------------------------------------------- Select c.c 課程ID, c.cname課程name, t1.t60 60-0佔比, t1.t70 70-60佔比, t1.t85 85-70佔比, t1.t100 100-85佔比 From course c Left join ( select sc.c, (select count(*) from sc where sc.score<60)*100/(select count(*) from sc) t60, (select count(*) from sc where sc.score>=60 and sc.score<70)*100/(select count(*) from sc) t70, (select count(*) from sc where sc.score>=70 and sc.score<85)*100/(select count(*) from sc) t85, (select count(*) from sc where sc.score>=85)*100/(select count(*) from sc) t100 from sc group by sc.c) t1 on t1.c=c.c --24、查詢學生平均成績及其名次

----------------- Select t1.*,rownum From (select sc.s,avg(sc.score) avgscore from sc Group by sc.s Order by avgscore desc) t1 --25、查詢各科成績前三名的記錄

----------------- Select c.c,c.cname t1.rn from course c, (select sc.c,sc.score, Row_number()over(partition by sc.c order by sc.score desc) rn From sc) t1 Where c.c=t1.c and rn<=3 --26、查詢每門課程被選修的學生數

------------------ Select c.cname,http://t1.co from course c, (select sc.c,count(sc.s) co group by sc.c) t1 Where c.c=t1.c --27、查詢出只有兩門課程的全部學生的學號和姓名

------------------------- Select s.s s.sname from student s where s.s in (select sc.s from sc group by sc.s having count(sc.c)>2) --28、查詢男生、女生人數

-------------- Select s.ssex,count(*) from student s group by s.ssex --29、查詢名字中含有"風"字的學生信息

--------------------- S Select s.* from student s where s.sname like 『%風%』 --30、查詢同名同性學生名單，並統計同名人數 SELECT s.Sname ,s.Ssex ,COUNT(*) countnum from student s GROUP BY s.Sname,s.Ssex HAVING countnum>=2 --31、查詢1990年出生的學生名單(註：Student表中Sage列的類型是datetime)

------------------------------------------------- SELECT * FROM student WHERE To_char(Sage,』yyyy』) LIKE 1990% --32、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號

------------------------------------------ Select sc.c,avg(sc.score) from sc group by sc.c order by avg(sc.score),sc.c --33、查詢平均成績大於等於85的所有學生的學號、姓名和平均成績

--------------------------------- Select st.*,t1.avgs From student st,( Select sc.s,avg(sc.score) avgs from sc Group by sc.s Having avg(sc.score)>85) t1 Where st.s=t1.s --34、查詢課程名稱為"數學"，且分數低於60的學生姓名和分數

-------------------------------- Select s.sname, sc.score from sc,course c student s where sc.c=c.c and c.cname=』數學』 and sc.s=s.s and sc.score<60 --35、查詢所有學生的課程及分數情況；

-------------------- -------------------- SELECT * FROM student a ,course b ,sc c WHERE a. S=c.S AND b.C =c.C --36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；

--------------------------------- Select s.s,s.sname,sc.c,c.cname,sc.score From student s,sc,course c Where sc.s=s.s and sc.c=c.c and s.s not int ( Select sc.s from sc where sc.score <70 ) order by s.s --37、查詢不及格的課程

------------- SELECT b.Sname ,c.Cname ,a.score FROM sc a, student b,course c WHERE a.score<60 AND a.C =c.C AND a.S =b.S --38、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名；

----------------------------------- Select s.* From student s where s.s in ( Select sc.s from sc where sc.c=01 and sc.score>=80 ) ) --39、求每門課程的學生人數

--------------- Select sc.c, count(s) from sc group by sc.s --40、查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績

------------------------------------- Select s.* Sc.score From student s,sc Where sc.score in ( Select max(sc.score) from teacher t ,course c,sc where t.t=c.t and t.tname=』張三』 and c.c=sc.c) and s.s=sc.s --41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

--------------------------------- Select sc.s,sc.c,sc.score From sc,(select sc.score from sc group by sc.score having count(sc.score)>1) b Where sc.score=b.score --42、查詢每門功成績最好的前兩名

------------------ Select t1.c,t1.score,t1.rn from ( Select c, score, row_number()over(partition by sc.c order by sc.score desc) rn From sc )t1 Where t1.rn in (1,2) --43、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

---------------------------------------------------------------------- Select c.* from ( Sc.c,count(sc.s) from sc group by sc.c having count(sc.s)>4) t1 Inner join course c on t1.c=c.c --44、檢索至少選修兩門課程的學生學號

-------------------- Select st.* from ( Select sc.s,count(sc.c) From sc group by sc.s having count(sc.c)>1)t1 Inner join student st on t1.s=st.s --45、查詢選修了全部課程的學生信息

------------------- Select st.* from (select sc.s,count(sc.c) from sc group by sc.s having count(sc.s)>2)t1 Inner join student st on t1.s=st.s --46、查詢各學生的年齡

------------- Select st.s,st.sname,st.sage, (to_char(sysdate,』yyyy』)- to_char(st.sage,』yyyy』)) From student st --47、查詢本周過生日的學生

--------------- Select st.s,st.sname,to_char(st.sage,』yyyymmdd』),st.ssex From student st Where to_char(st.sage,』 mmdd』)>=to_char(trunc(sysdate,』iw』),』mmdd』) and to_char(st.sage,』 mmdd』)<to_char(trunc(sysdate,』iw』)+6,』 mmdd』) --48、查詢下周過生日的學生

--------------- Select st.s,st.sname,to_char(st.sage,』yyyymmdd』),st.ssex From student st Where to_char(st.sage,』 mmdd』)>=to_char(trunc(sysdate,』iw』)+7,』mmdd』) and to_char(st.sage,』 mmdd』)<to_char(trunc(sysdate,』iw』)+13,』 mmdd』) --49、查詢本月過生日的學生

--------------- Select st.s,st.sname,to_char(st.sage,』yyyymmdd』),st.ssex From student st Where to_char(st.sage,』mm』)=to_char(sysdate,』mm』); --50、查詢下月過生日的學生

--------------- Select st.s,st.sname,to_char(str.sage,』yyyymmdd』),st.ssex From student st Where to_char(st.sage,』mm』)=to_char(add_months(sysdate,1),』mm』); 微信公眾號：weiran201606

推薦閱讀：