sql綜合練習題

一、表關系
年級表：class_grade
create table class_grade(gid int primary key auto_increment,gname varchar(20) not null);
insert into class_grade(gname) values('一年級'),('二年級'),('三年級');
班級表：class
create table class(cid int primary key auto_increment,caption varchar(30) not null,grade_id int not null,constraint class_name foreign key(grade_id)references class_grade(gid)on delete cascadeon update cascade);
insert into class(caption,grade_id) values('一年一班',1),('二年一班',2),('三年二班',3);學生表：student
create table student(sid int primary key auto_increment,sname varchar(20) not null,gender enum('女','男'),class_id int not null,constraint student_name foreign key(class_id)references class(cid)on delete cascadeon update cascade);
insert into student(sname,gender,class_id) values('喬丹','女',1),('艾弗森','女',1),('科比','女',2);老師表：teacher
create table teacher(tid int primary key auto_increment,tname varchar(30) not null);
insert into teacher(tname) values('張三'),('李四'),('王五');課程表：course
create table course(cid int primary key auto_increment,cname varchar(30) not null,teacher_id int not null,constraint teacher_name foreign key(teacher_id)references teacher(tid)on delete cascadeon update cascade
);
insert into course(cname,teacher_id) values('生物',1),('體育',1),('物理',2);成績表：score
create table score(sid int primary key auto_increment,student_id int not null,course_id int not null,score int not null,foreign key(student_id) references student(sid)on delete cascadeon update cascade,foreign key(course_id) references course(cid)on delete cascadeon update cascade
);insert into score(student_id,course_id,score) values(1,1,60),(1,2,59),(2,2,99);班級任職表：teach2cls
create table teach2cls(tcid int primary key auto_increment,tid int not null,cid int not null,foreign key(tid) references teacher(tid)on delete cascadeon update cascade,foreign key(cid) references class(cid)on delete cascadeon update cascade
);insert into teach2cls(tid,cid) values(1,1),(1,2),(2,1),(3,2);2、查詢學生總人數
select count(sname) 總人數 from student;3、查詢’生物‘課程和’物理‘課程成績都及格的學生id和姓名
select sid,sname
fromstudent
inner join(select student_idfrom scorewherecourse_id in (selectcidfromcoursewherecname in ('生物','物理')) and  score >= 60) as t1 on t1.student_id = student.sid;4、查詢每個年級的班級數，取出班級數最多的前三個年級;
selectclass_grade.gid,class_grade.gname
fromclass_grade
inner join
(select grade_id,count(cid)fromclassgroup by grade_idorder by grade_id desclimit 3
) as t1 on t1.grade_id = class_grade.gid;5、查詢平均成績最高和最低的學生的id和姓名以及平均成績;
selectstudent.sid,student.sname,avg_score
fromstudent
inner join(selectstudent_id,avg(score) as avg_scorefromscoregroup bystudent_idhaving avg(score) in((selectavg(score) as max_scorefromscoregroup by student_idorder byavg(score) desclimit 1),(selectavg(score) as min_scorefromscoregroup bystudent_idorder byavg(score)limit 1))) as t1 on t1.student_id = student.sid;6、查詢每個年級的學生人數
select class_grade.gname,count_cid
from class_grade
inner join(selectgrade_id,count(cid) as count_cidfromclass,studentwhereclass.cid = class_idgroup by grade_id) as t1 on t1.grade_id = class_grade.gid;7、查詢每位學生的學號、姓名、選課數、平均成績;
selectstudent.sid,student.sname,course_count,avg_score
fromstudent
left join(selectstudent_id,count(course_id) as course_count,avg(score) as avg_scorefromscoregroup by student_id) as t1 on t1.student_id = student.sid;8、查詢學生編號為‘2’的學生的姓名，該學生成績最高的課程名、成績最低的課程名及分數;
selectstudent.sname,student.sid,t1.score
from
(selectstudent_id,course_id,scorefrom scorewhere student_id = 2 and score in((selectmax(score)fromscorewhere student_id = 2),(select min(score)fromscorewhere student_id =2)) 
)as t1
inner join student on t1.student_id = student.sid
inner join course on t1.course_id = course.cid;9、查詢‘李’的老師的個數和所帶班級數;
selectcount(teacher.tname) as '李%個數',count(teach2cls.cid) as '班級數量'
from teacher left join teach2cls on teach2cls.tid = teacher.tid
where teacher.tname like '李%';10、查詢班級數小于5年級的id和年級名;
selectgid,gname,count(cid)
fromclass_grade
inner join class on gid = grade_id
group bygid
havingcount(cid) < 5
11、查詢班級信息，包括班級id、班級名稱、年級、年級級別（12
為低年級，34為中年級，56為高年級）
selectclass.cid as '班級id',class.caption as '班級名稱',class_grade.gid as '年級',
casewhen class_grade.gid between 1 and 2 then '低'when class_grade.gid between 3 and 4 then '中'when class_grade.gid between 5 and 6 then '高' else 0 end as '年級級別'
fromclass
left join class_grade on class_grade.gid=class.grade_id;12、查詢學過“張三”老師2門課以上的同學的學號、姓名；
select sid,sname
fromstudent
where sid in 
(
selectstudent_id
fromscore
left join course on course_id = course.cid
where course.teacher_id in (    select tidfrom teacherwhere tname = '張三')
group bystudent_id
having count(course.cid)>=2
)13、查詢教授課程超過2門的老師的id和姓名;
selecttid,tname
fromteacher
inner join (selectteacher_id,count(cid)fromcoursegroup byteacher_idhaving count(cid) >=2
) as t1 on t1.teacher_id = teacher.tid;14、查詢學過編號‘1’課程和編號2課程的同學的學號、姓名;
selectsid,sname
fromstudent
where sid in 
(
select course_id
fromscore
group bycourse_id
havingcourse_id in (1,2)
)15、查詢沒有帶過高年級的老師id和姓名;
selecttid,tname
fromteacher
where tid  in
(
select tid
fromteach2cls
where tid in 
(selectcid
fromclass
where grade_id in (5,6)));16、查詢學過'張三'老師所教的所有課的同學的學號、姓名;
selectsid,sname
fromstudent
where sid in (selectstudent_idfromscorewhere course_id in (    selectcidfromcourseinner join teacher on teacher_id = teacher.tidwhere teacher.tname ='張三'));17、查詢帶過超過2個班級的老師的id和姓名;
selecttid,tname
fromteacher
where tid in (
selecttid
fromteach2cls
group bytid
havingcount(cid) >2);18、查詢課程編號’2‘的成績比課程’1‘課程低的所有同學的學號、姓名
select sid,sname
fromstudent
where sid in (
select t1.student_id
from
(selectstudent_id,scorefromscorewhere course_id =2
) as t1
inner join
(selectstudent_id,scorefromscorewhere course_id =1
) as t2 on t1.student_id = t2.student_id
where t1.score < t2.score);19、查詢所帶班級數最多的老師id和姓名;
selecttid,tname
fromteacher
where tid =
(selecttid
fromteach2cls
group bytid
order by count(cid) desc
limit 1);
20、查詢有課程成績小于60分的同學的學號、姓名;
selectsid,sname
fromstudent
where sid in (
selectstudent_id
fromscore
where score <60);
21、查詢沒有學全所有課的同學的學號、姓名;
selectsid,sname
fromstudent
where sid not in (
selectstudent_id
fromscore
group bystudent_id
havingcount(course_id) = (select count(cid) from course));
22、查詢至少有一門課與學號為“1”的同學所學相同的同學的學號和姓名；
selectsid,sname
fromstudent
where sid in 
(
selectstudent_id
fromscore
where course_id in 
(selectcourse_id
fromscore
where student_id = 1));23、查詢至少學過學號為“1”同學所選課程中任意一門課的其他同學學號和姓名；
selectsid,sname
fromstudent
where sid in 
(
selectstudent_id
fromscore
where course_id in 
(selectcourse_id
fromscore
where student_id = 1)
havingstudent_id !=1
);24、查詢和‘2’號同學學習的課程完全相同的其它同學的學號和姓名
selectsid,sname
fromstudent
where sid in 
(selectstudent_idfromscore,(select course_id fromscorewhere student_id = 2) as t1where score.course_id = t1.course_id and score.student_id !=2group byscore.student_idhavingcount(score.course_id)=(select count(course_id) from scorewhere student_id =2)
);
25、刪除學習‘張三’老師課的score表記錄;
delete
fromscore
where course_id in (
select cid
fromcourse
where course.teacher_id = (
select tid
from teacher
where teacher.tname = '張三'));26、向score表中插入一些記錄，這些記錄要求符合以下條件：
1、沒有上過編號‘2’課程的同學學號
2、插入’2‘號課程的平均成績insert into score(student_id,course_id,score) select t1.sid,2,t2.avg from (select sid from student where sid not in (select student_id from score where course_id = 2)) as t1,(select avg(score) as avg from score group by course_id having course_id =2) as t2;27、按平均成績從低到高顯示所有學生的“語文”、“數學”、“英語”三門的課程成績，按如下形式顯示： 學生ID,語文,數學,英語,有效課程數,有效平均分；select sc.student_id as  學生ID,(select score.score from score left join course on score.course_id = course.cid where course.cname = '生物' and score.student_id = sc.student_id) as 生物,(select score.score from score left join course on score.course_id = course.cid where course.cname = '體育' and score.student_id = sc.student_id) as 體育,(select score.score from score left join course on score.course_id = course.cid where course.cname = '物理' and score.student_id = sc.student_id) as 物理,count(sc.course_id) as '有效課程數',avg(sc.score) as '有效平均分'from score as sc group by sc.student_id order by avg(sc.score);28、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，高低分;select course_id as "課程ID",max(score) as "最高分",min(score) as "最低分" from scoregroup by course_id29、按各科平均成績從低到高和及格率的百分數從高到低順序；SELECT course_id as '課程ID',AVG(score) as '平均成績',sum(CASE WHEN score > 60 then 1 ELSE 0 END)/COUNT(1)*100 as '及格率'from scoreGROUP BY course_id ORDER BY '平均成績' ASC,'及格率' desc;30、課程平均分從高到低顯示（現實任課老師）；
SELECT score.course_id as '課程ID',avg(score) as '平均分' from score
inner join course on score.course_id = course.cid
GROUP BY course_id
ORDER BY avg(score) DESC31、查詢各科成績前三名的記錄(不考慮成績并列情況)
SELECT score.sid,score.course_id,score.score,t1.first_score,t1.sencond_score,t1.third_score from score LEFT JOIN
(SELECT sid,(select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 0,1) as first_score,(select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 1,1) as sencond_score,(select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 2,1) as third_score
from score as s1)as t1 on score.sid = t1.sid
WHERE score.score in (t1.first_score,t1.sencond_score,t1.third_score)32、查詢每門課程被選修的學生數；
SELECT score.course_id as '課程ID',count(student_id) as '學生數' from score
GROUP BY course_id33、查詢選修了2門以上課程的全部學生的學號和姓名；
SELECT student.sid,student.sname from student WHERE sid in (
SELECT score.student_id from score
GROUP BY student_id
HAVING count(course_id) >=2);34、查詢男生、女生的人數，按倒序排列；
select gender,count(sid) as sum from student
group by gender
order by sum desc35、查詢姓“張”的學生名單；
SELECT sname from student WHERE sname like '張%'36、查詢同名同姓學生名單，并統計同名人數；
SELECT sname as '名字',count(sname) as '同名人數' from student
GROUP BY sname
HAVING count(sname) >137、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列；
select score.course_id,avg(score) as avg from score
INNER JOIN course on course.cid = course_id
GROUP BY course_id
ORDER BY avg ASC,course_id DESC;38、查詢課程名稱為“數學”，且分數低于60的學生姓名和分數；
SELECT sid,sname from student
where sid in (
SELECT score.student_id  from score
INNER JOIN course on course.cid = score.course_id
WHERE course.cname = '體育' and score.score <60);39、查詢課程編號為“3”且課程成績在80分以上的學生的學號和姓名；
SELECT sid,sname from  student
WHERE sid in (
SELECT score.student_id  from score
INNER JOIN course on course.cid = course_id
WHERE course_id = 3 and score.score > 80);40、求選修了課程的學生人數
select course_id as '課程ID',count(student_id) as '學生人數' from score group by course_id;41、查詢選修“王五”老師所授課程的學生中，成績最高和最低的學生姓名及其成績；
SELECT student.sname,max(score),min(score) from score
INNER JOIN student on score.student_id = student.sid
WHERE course_id in (
SELECT cid from course
WHERE teacher_id in (
SELECT tid FROM teacher
WHERE tname = '王五'))
GROUP BY student_id
ORDER BY max(score) DESC,MIN(score) ASC
LIMIT 242、查詢各個課程及相應的選修人數；
SELECT score.course_id as 'ID',course.cname as '課程',count(student_id) as '人數' from score
LEFT JOIN course on score.course_id = course.cid
GROUP BY course_id43、查詢不同課程但成績相同的學生的學號、課程號、學生成績；
select DISTINCT s1.course_id,s2.course_id,s1.score,s2.score from score as s1, score as s2 where s1.score = s2.score and s1.course_id != s2.course_id;44、查詢每門課程成績最好的前兩名學生id和姓名；
SELECT score.sid,score.course_id,score.score,t1.first_score,t1.sencond_score from score LEFT JOIN
(SELECT sid,(select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 0,1) as first_score,(select  score from score as s2 where s2.course_id=s1.course_id ORDER BY score desc LIMIT 1,1) as sencond_score
from score as s1)as t1 on score.sid = t1.sid
WHERE score.score <= t1.first_score and score.score >= t1.sencond_score45、檢索至少選修兩門課程的學生學號；
SELECT score.student_id as '學生ID',count(course_id) as '課程ID' from score
GROUP BY student_id
HAVING count(course_id)>=246、查詢沒有學生選修的課程的課程號和課程名；
SELECT cid,cname from course
WHERE cid not IN(
SELECT score.course_id from score
GROUP BY score.course_id)47、查詢沒帶過任何班級的老師id和姓名；
SELECT tid,tname from teacher
WHERE tid not in(
SELECT teach2cls.cid from teach2cls
GROUP BY teach2cls.cid)48、查詢有兩門以上課程超過80分的學生id及其平均成績；
SELECT score.student_id as '學生ID',avg(score) as '平均成績' from score
WHERE score > 30 
GROUP BY student_id
HAVING count(course_id) >=249、檢索“3”課程分數小于60，按分數降序排列的同學學號；
select score.student_id from score
WHERE score < 60 and course_id = 3
ORDER BY score DESC50、刪除編號為“2”的同學的“1”課程的成績
delete from score where score.student_id = 2 and score.course_id = 1;51、查詢同時選修了物理課和生物課的學生id和姓名；
SELECT sid,sname from student
WHERE sid in(
SELECT score.student_id from score
WHERE course_id in (
SELECT cid from course
WHERE course.cname in ('生物','物理'))
GROUP BY student_id
HAVING count(course_id) =2)

?