sql複雑な検索文のまとめ
9841 ワード
私はデータベース検索の中のいくつかの比較的複雑なクエリをまとめました。例えば、チェーン検索、マルチサブクエリ、連表検索＋サブクエリなど、技術が限られていますので、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 ， 。
#
#
#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 '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';