SQL複雜查詢語句總結

表結構

/***學生表***/

create table student(

sno varchar2(10) primary key, --學號

sname varchar2(20), --姓名

sage number(2), --年齡

ssex varchar2(5) --性別

);

/***教師表***/

create table teacher(

tno varchar2(10) primary key, --教師編號

tname varchar2(20) --教師姓名

);

/***課程表***/

create table course(

cno varchar2(10), --課程編號

cname varchar2(20), --課程名

tno varchar2(20), --教師編號

constraint pk_course primary key (cno,tno)

);

/***成績表***/

create table sc(

sno varchar2(10), --學號

cno varchar2(10), --課程編號

score number(4,2), --成績

constraint pk_sc primary key (sno,cno)

);

#/*******初始化學生表的數據******/

insert into student values ("s001","張三",23,"男");

insert into student values ("s002","李四",23,"男");

insert into student values ("s003","吳鵬",25,"男");

insert into student values ("s004","琴沁",20,"女");

insert into student values ("s005","王麗",20,"女");

insert into student values ("s006","李波",21,"男");

insert into student values ("s007","劉玉",21,"男");

insert into student values ("s008","蕭蓉",21,"女");

insert into student values ("s009","陳蕭曉",23,"女");

insert into student values ("s010","陳美",22,"女");

commit;

#/******************初始化教師表***********************/

insert into teacher values ("t001", "劉陽");

insert into teacher values ("t002", "諶燕");

insert into teacher values ("t003", "胡明星");

commit;

#/***************初始化課程表****************************/

insert into course values ("c001","J2SE","t002");

insert into course values ("c002","Java Web","t002");

insert into course values ("c003","SSH","t001");

insert into course values ("c004","Oracle","t001");

insert into course values ("c005","SQL SERVER 2005","t003");

insert into course values ("c006","C#","t003");

insert into course values ("c007","JavaScript","t002");

insert into course values ("c008","DIV+CSS","t001");

insert into course values ("c009","PHP","t003");

insert into course values ("c010","EJB3.0","t002");

commit;

#/***************初始化成績表***********************/

insert into sc values ("s001","c001",78.9);

insert into sc values ("s002","c001",80.9);

insert into sc values ("s003","c001",81.9);

insert into sc values ("s004","c001",60.9);

insert into sc values ("s001","c002",82.9);

insert into sc values ("s002","c002",72.9);

insert into sc values ("s003","c002",81.9);

insert into sc values ("s001","c003",59);

常用SQL查詢語句

#注意：以下練習中的數據是根據初始化到資料庫中的數據來寫的SQL 語句，請大家務必注意。

#

#

#1、查詢「c001」課程比「c002」課程成績高的所有學生的學號；

select * from sc a, sc b

where a.sno=b.sno and a.cno="c001" and b.cno="c002" and a.score>b.score;

#2、查詢平均成績大於60 分的同學的學號和平均成績；

select st.sname,ss.sco

from student st,(select sno,avg(score)sco from sc group by sno) ss

where st.sno=ss.sno and sco>=60;

#3、查詢所有同學的學號、姓名、選課數、總成績；

select st.sname,ss.*

from student st,(select sno,count(score)sco,sum(score) from sc group by sno) ss

where st.sno=ss.sno;

#4、查詢姓「劉」的老師的個數；

select count(*) from teacher where tname like "%劉%";

#5、查詢沒學過「諶燕」老師課的同學的學號、姓名；

select sc.sno,acno.*

from sc sc,(select co.cno from course co,

(select tno from teacher where tname="諶燕") tn where co.tno<>tn.tno) acno

where acno.cno=sc.cno;

#6、查詢學過「c001」並且也學過編號「c002」課程的同學的學號、姓名；

select sa.sname,sc.sno

from sc sc,(select st.sname,s.* from student st,

(select sno,cno from sc where cno in("c001"))s where st.sno=s.sno)sa

where sc.sno=sa.sno and sc.cno="c002";

#7、查詢學過「諶燕」老師所教的所有課的同學的學號、姓名；

select distinct st.sname

from student st,sc sc

join (select cno from teacher te join course co on(te.tno=co.tno) where te.tname="諶燕")aca

on(sc.cno=aca.cno)

where st.sno=sc.sno;

#9、查詢所有課程成績小於60 分的同學的學號、姓名；

select st.sname,sc.sno

from student st

join sc sc on(st.sno=sc.sno)

where sc.score<60;

#10、查詢沒有學全所有課的同學的學號、姓名；

select sname,st.sno,scn.cn

from student st

join (select distinct sno,count(*)cn from sc group by sc.sno)scn

on(st.sno=scn.sno)

where scn.cn=(select distinct count(cno) from course);

select st.sname,st.sno

from student st

join (select sc.sno sn,count(sc.cno) cou from sc group by sc.sno) scs

on st.sno=scs.sn

where scs.cou <(select count(cno) from course);

#11、查詢至少有一門課與學號為「s001」的同學所學相同的同學的學號和姓名；

select distinct st.sno, sname

from student st join sc sc on (st.sno = sc.sno)

where sc.cno in (select cno from sc where sno = "s001") and sc.sno<>"s001";

#13、把「SC」表中「諶燕」老師教的課的成績都更改為此課程的平均成績；

#update sc sc set sc.score=update sc sc set sc.score=(select tc.cno,round(avg(sc.score),2)av

from (select *

from teacher te

join course co on(te.tno=co.tno)

where te.tname="諶燕")tc

join sc sc on(tc.cno=sc.cno) group by tc.cno)cnn.score

where sc.cno=cnn.av;

select tc.cno,round(avg(sc.score),2)

from (select *

from teacher te

join course co on(te.tno=co.tno)

where te.tname="諶燕")tc

join sc sc on(tc.cno=sc.cno) group by tc.cno;

#14、查詢和「s001」號的同學學習的課程完全相同的其他同學學號和姓名；

select cno from sc where sno="s001";

#15、刪除學習「諶燕」老師課的SC 表記錄；

delete from sc

where sc.cno

in(select cno from teacher te join course co on(te.tno=co.tno) where te.tname="諶燕")

#17、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

select cno,max(score),min(score) from sc group by cno;

#18、按各科 平均成績 從 低到高 和 及格率的百分數 從高到低順序

#select count(score) from sc where score>=60 group by cno ;

select cno,avg(score)acs,savg.cnu,(count(score)-savg.cnu)/count(score)*100

from sc,(select count(score)cnu,avg(score)acs2 from sc

where score<60 order by acs2 desc)savg

group by savg.cnu,cno

order by acs desc;

#21、查詢各科成績前三名的記錄:(不考慮成績並列情況)

select *

from (select S.*,rownum rn from (select * from sc order by score desc)S

where rownum<=3)where rn>=1;

#22、查詢每門課程被選修的學生數

select cno,count(cno) from sc group by cno;

#23、查詢出只選修了一門課程的全部學生的學號和姓名

select st.sname,st.sno

from student st

join (select distinct sc.*

from sc sc

join (select sno,count(cno)cnum from sc group by sno)ss

on(sc.sno=ss.sno)

where ss.cnum=1)ssb

on(st.sno=ssb.sno);

#24、查詢男生、女生人數

select ssex,count(ssex) from student st group by ssex;

#25、查詢姓「張」的學生名單

select sname from student where sname like "%張%";

#26、查詢同名同性學生名單，並統計同名人數

select st.*

from student st

join (select sname,count(sname)cnum from student group by sname)scomm

on(st.sname=scomm.sname)

where scomm.cnum>1;

#27、1981 年出生的學生名單(註：Student 表中Sage 列的類型是number)

select * from student where to_char(sysdate,"yyyy")-sage ="1981";

#28、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列

select cno,avg(score)lim from sc group by cno order by lim asc;

#29、查詢平均成績大於85 的所有學生的學號、姓名和平均成績

select st.sname,st.sno,savg.lim

from student st

join (select sno,avg(score)lim from sc group by sno)savg

on(st.sno=savg.sno)

where savg.lim>85;

#30、查詢課程名稱為「資料庫」，且分數低於60 的學生姓名和分數

select distinct st.sname,cs.score

from student st

join (select *

from course co

join sc sc

on(co.cno=sc.cno))cs

on(st.sno=cs.sno)

where cs.score<60 and cs.cname="資料庫";

#31、查詢所有學生的選課情況；

select distinct st.sname,cs.cname

from student st

join (select * from course co join sc sc on(co.cno=sc.cno))cs

on(st.sno=cs.sno);

#32、查詢任何一門課程成績在70 分以上的姓名、課程名稱和分數；

select distinct st.sname,cs.score,cs.cname

from student st

join (select * from course co join sc sc on(co.cno=sc.cno))cs

on(st.sno=cs.sno)

where cs.score>70;

#33、查詢不及格的課程，並按課程號從大到小排列

select * from sc where score<60 order by sno desc;

#34、查詢課程編號為c001 且課程成績在80 分以上的學生的學號和姓名；

select st.sname,st.sno

from student st

join (select * from sc where cno="c001" and score>80)snum

on(st.sno=snum.sno);

#35、求選了課程的學生人數

select count(ss.sno) from (select distinct sno from sc)ss;

#36、查詢選修「諶燕」老師所授課程的學生中，成績最高的學生姓名及其成績

select st.sname,cnum.score

from student st

join(select distinct * from sc sc where cno

in(select distinct cno

from course co

join (select tno from teacher where tname="諶燕")ten

on(co.tno=ten.tno))

order by score desc)cnum

on(st.sno=cnum.sno)

where rownum=1;

#37、查詢各個課程及相應的選修人數

#(select cno,count(cno) from sc group by cno);

select co.cname,cns.cnum

from course co

join (select cno,count(cno)cnum from sc group by cno)cns

on(co.cno=cns.cno);

#39、查詢每門功課成績最好的前兩名

select cno from sc order by score desc group by cno;

#40、統計每門課程的學生選修人數（超過10 人的課程才統計）。

要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

#41、檢索至少選修兩門課程的學生學號

select sno from (select sno,count(cno)cnum from sc group by sno) where cnum>=2;

#42、查詢全部學生都選修的課程的課程號和課程名

#select count(*) from (select distinct sno from sc);

#select cno,count(cno)cns from sc group by cno;

#select cno

from (select count(*)num from (select distinct sno from sc))cs1,

(select cno,count(cno)cns from sc group by cno)cs2

where cs2.cns=cs1.num;

select cname,scs.cno

from course co

join (select cno from (select count(*)num from (select distinct sno from sc))cs1,

(select cno,count(cno)cns from sc group by cno)cs2 where cs2.cns=cs1.num)scs

on(co.cno=scs.cno);

#43、查詢沒學過「諶燕」老師講授的任一門課程的學生姓名

#select co.cno

from course co

join (select tno from teacher where tname="諶燕")tn

on(co.tno=tn.tno);

select sname

from student

where sno

not in(select distinct sno

from sc where cno

in(select co.cno from course co

join (select tno from teacher where tname="諶燕")tn

on(co.tno=tn.tno)));

#44、查詢兩門以上不及格課程的同學的學號及其平均成績

#select sno,count(sno),avg(score)

from sc where score < 60 group by sno;

select *

from (select sno,count(sno)cnum,avg(score)

from sc where score < 60 group by sno)avs

where avs.cnum>1;

#45、檢索「c004」課程分數小於60，按分數降序排列的同學學號

select sno from sc where cno="c004" and score<60 order by score desc;

#46、刪除「s002」同學的「c001」課程的成績

delete from sc where sno="s002" and cno="c001";

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！