本文共 11041 字,大约阅读时间需要 36 分钟。
Student(Sid,Sname,Sage,Ssex)学生表Sid:学号Sname:学生姓名Sage:学生年龄Ssex:学生性别Course(Cid,Cname,T#)课程表Cid:课程编号Cname:课程名称Tid:教师编号SC(Sid,Cid,score)成绩表Sid:学号Cid:课程编号score:成绩Teacher(Tid,Tname)教师表Tid:教师编号:Tname:教师名字 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
问题:
select a.sid from (select sid,score from sc where cid='001')a,(select sid,score from sc where cid='002')b where a.sid = b.sid and a.score>b.score; 1 2 3 4
select sid,avg(score) from scgroup by sid having avg(score)>60; 1 2 3
select s.sid,s.sname,count_cid as 选课数, sum_score as 总成绩from student sleft join (select sid,count(cid) as count_cid,sum(score) as sum_score from sc group by sid )scon s.sid = sc.sid; 1 2 3 4 5 6 7
select count(tname)from teacher where tname like '李%'; 1 2 3
select s.sid,s.sname from student as s where s.sid not in ( select DISTINCT sid from sc as sc where sc.cid in ( select cid from course as c left join teacher as t on c.tid = t.tid where t.tname = '叶平')); 1 2 3 4 5 6 7 8 9 10 11
select s.sid,s.sname from student as s where s.sid in ( select distinct sc.sid from sc as sc where sc.cid in ( select cid from course as c left join teacher as t on c.tid = t.tid where t.tname = '叶平') group by sc.sid HAVING count(cid)= (select count(cid) from course as c left join teacher as t on c.tid = t.tid where t.tname = '叶平')); 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SELECT s.sid,s.sname from student as s left join sc as sc on s.sid = sc.sidwhere sc.cid = '001'and EXISTS( select * from sc as sc_2 where sc.sid = sc_2.sid and sc_2.cid='002');select s.sid,s.snamefrom student as s left join sc as sc on sc.sid = s.sidwhere sc.cid = '001'and s.sid in ( select sid from sc as sc_2 where sc_2.cid='002' and sc_2.sid = sc.sid); 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
select sid,snamefrom (select student.sid,student.sname,score, (select score from sc as sc_2 where sc_2.sid = student.sid and sc_2.cid = '002') as score2 from student,sc where student.sid=sc.sid and cid = '001') s_2where score2
select sid,snamefrom studentwhere sid not in (select s.sid from student s,sc where s.sid=sc.sid and score>60 );select sid,snamefrom student swhere not EXISTS (select s.sid from sc where sc.sid = s.sid and sc.score>60); 1 2 3 4 5 6 7 8 9 10 11 12
select s.sid,s.snamefrom student s ,sc sc where s.sid = sc.sidgroup by s.sid,s.snamehaving count(sc.cid)<(select count(cid) from course);select s.sid,s.snamefrom student s right join sc sc on s.sid = sc.sidgroup by s.sid,s.snamehaving count(sc.cid)<(select count(cid) from course); 1 2 3 4 5 6 7 8 9 10 11 12 13 14
select student.sid,snamefrom student,sc where student.sid = sc.sidand cid in (select cid from sc where sid='1001');select s.sid,s.snamefrom sc sc left join student as son sc.sid = s.sidwhere sc.cid in (select cid from sc where sid='1001');select sc_1.sid,s.snamefrom sc sc_1 left join student as son sc_1.sid = s.sidwhere exists (select sc_2.cid from sc as sc_2 where sc_1.cid = sc_2.cid and sc_2.sid = '1001'); 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
update sc set score = (select avg(sc_2.score) from sc sc_2 where sc_2.cid = sc.cid)where cid in (select c.cid from course c left join teacher t on t.tid = c.tid where t.tname = '叶平'); 1 2 3 4 5 6 7
select sc_1.sid from (select cid from sc where sid='1002')aleft join sc sc_1 on a.cid = sc_1.cidwhere sc_1.sid<>'1002' group by sc_1.sid having count(sc_1.cid) = (select count(cid) from sc where sid='1002');select a.sid,s.sname from (select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc where sid='1002')b,(select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc group by sid)aleft join student s on a.sid = s.sidwhere a.cid_str = b.cid_str and a.sid<>'1002'; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
delete from sc WHEREcid in (select c.cid from course c LEFT JOIN teacher t on c.tid=t.tid where t.tname = '叶平'); 1 2 3 4 5
insert into sc select sid,'002',(select avg(score) from sc where cid='0022')from student where sid not in (select sid from sc where cid='002'); 1 2 3 4
select sid as 学生id,(SELECT score from sc where sc.sid = t.sid and cid='004') as 数据库,(select score from sc where sc.sid = t.sid and cid='001') as 企业管理,(select score from sc where sc.sid = t.sid and cid='015') as 英语,count(cid) as 有效课程数, avg(t.score) as 平均成绩from sc as t group by sidorder by avg(t.score); 1 2 3 4 5 6 7 8 9 10 11
select l.cid as 课程id,l.score as 最高分,r.score as 最低分from sc l,sc rwhere l.cid = r.cidand l.score = (select max(t.score) from sc t where l.cid = t.cid group by t.cid)and r.score = (select min(t.score) from sc t where r.cid = t.cid group by t.cid)order by l.cid;select cid as 课程id,max(score) as 最高分,min(score) as 最低分from sc group by cid; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SELECT t.cid as 课程号,c.cname as 课程名,COALESCE(avg(score),0) as 平均成绩,100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*) as 及格百分数from sc tleft join course c on t.cid = c.cidgroup by t.cidorder by 100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*); 1 2 3 4 5 6 7 8 9 10 11 12 13
select t.tid as 教师id,t.tname as 教师姓名,sc.cid as 课程id,avg(score) as 平均成绩from sc as scLEFT JOIN course c on sc.cid = c.cidleft join teacher t on c.tid = t.tidgroup by sc.cid order by avg(sc.score) desc; 1 2 3 4 5 6 7 8 9
select sc.cid as 课程id,cname as 课程名称,sum(case when score between 85 and 100 then 1 else 0 end) as '[100-85]',sum(case when score between 70 and 85 then 1 else 0 end) as '[85-70]',sum(case when score between 60 and 70 then 1 else 0 end) as '[70-60]',sum(case when score<60 then 1 else 0 end) as '[60-0]'from sc as sc left join course as con sc.cid = c.cidgroup by sc.cid; 1 2 3 4 5 6 7 8 9
select 1+(select count(distinct 平均成绩) from (select sid,avg(score) as 平均成绩 from sc group by sid)t1 where 平均成绩>t2.平均成绩) as 名次,sid as 学生学号,平均成绩 from (select sid,avg(score) 平均成绩 from sc group by sid) as t2order by 平均成绩 desc; 1 2 3 4 5 6 7
select sid,cid,scorefrom sc sc_1where (select count(3) from sc sc_2 where sc_1.cid = sc_2.cid and sc_2.score>=sc_1.score)<=2 order by sc_1.cid); 1 2 3 4 5 6 7 8
select cid, count(sid)from sc group by cid; 1 2 3
select sc.sid,s.sname,count(sc.cid) as 课程数from sc as scLEFT JOIN student as son sc.sid = s.sidgroup by sc.sidhaving count(sc.cid)=1; 1 2 3 4 5 6 7
select count(ssex) as 男生人数from studentgroup by ssexhaving ssex = '男';select count(2) from studentwhere ssex = '女'; 1 2 3 4 5 6
select sid,snamefrom student where sname like '张%'; 1 2 3
select sname,count(8)from student group by snamehaving count(8)>1; 1 2 3 4
select s.sname,sc.sid,avg(sc.score) as 平均成绩from sc as scleft join student as s on sc.sid = s.sidgroup by sc.sid having avg(sc.score)>85; 1 2 3 4 5 6
select cid,avg(score)from sc group by cidorder by avg(score),cid desc; 1 2 3 4
select c.cname,s.sid,s.sname,sc.scorefrom course cleft join sc on sc.cid = c.cidLEFT JOIN student s on s.sid = sc.sidwhere c.cname = '数据库' and sc.score<60; 1 2 3 4 5
select sc.sid,sc.cid,s.sname,c.cnamefrom sc LEFT JOIN course c on sc.cid = c.cidleft join student s on sc.sid = s.sid; 1 2 3 4
select distinct s.sid,s.sname,sc.cid,sc.scorefrom sc left join student s on sc.sid = s.sidleft join course c on sc.cid = c.cidwhere sc.score>70; 1 2 3 4 5
select cidfrom sc where score<60ORDER BY cid; 1 2 3 4
select sc.sid,s.sname from sc left join student s on sc.sid = s.sidwhere sc.cid = '003' and sc.score>80; 1 2 3 4
select count(2) from (select distinct sid from sc)a; 1 2
select s.sname,sc.scorefrom sc sc left join student s on sc.sid = s.sidleft join course c on sc.cid = c.cidleft join teacher t on c.tid = t.tidwhere t.tname = '叶平'and sc.score = (select max(score) from sc sc_1 where sc.cid = sc_1.cid); 1 2 3 4 5 6 7 8 9 10
select cid,count(*) from sc group by cid; 1
select DISTINCT a.sid,a.cid,a.scorefrom sc as a ,sc as b where a.score = b.scoreand a.cid <> b.cid; 1 2 3 4
select cid as 课程号,count(8) as 选修人数from scgroup by cidHAVING count(sid)>10order by count(8) desc,cid; 1 2 3 4 5
select sidfrom scgroup by sidhaving count(8)>=2; 1 2 3 4
select cid,cnamefrom course where cid in (select cid from sc group by cid); 1 2 3
select sname from student where sid not in ( select sid from sc,course,teacher where course.tid = teacher.tid and sc.cid = course.cid and teacher.tname='叶平'); 1 2 3 4 5 6 7 8
select sid,avg(COALESCE(score,0))from scwhere sid in ( select sid from sc where score<60 group by sid having count(8)>2)group by sid; 1 2 3 4 5 6 7 8 9 10
select sid,scorefrom scwhere cid='004'and score<60order by score desc; 1 2 3 4 5
delete from sc where sid = '002' and cid = '001'; 1 2 3
转载地址:http://gafci.baihongyu.com/