mysql練習題

表結構和數據：http://www.cnblogs.com/wupeiqi/articles/5748496.html

題目：
2、查詢“生物”課程比“物理”課程成績高的所有學生的學號；
1)
select A.student_id,生物分數,物理分數 from
(select student_id,num as 生物分數
from score
where course_id=(select cid from course where cname='生物')) as A
left join
(select student_id,num as 物理分數
from score
where course_id=(select cid from course where cname='物理')) as B
on A.student_id = B.student_id where 生物分數 > if(isnull(物理分數),0,物理分數);
2)
select A.student_id,生物分數,物理分數 from
(select student_id,num as 生物分數 from score left join course on score.course_id = course.cid where course.cname = '生物') as A
left join
(select student_id,num as 物理分數 from score left join course on score.course_id = course.cid where course.cname = '物理') as B
on A.student_id = B.student_id where 生物分數 > if(isnull(物理分數),0,物理分數);

3、查詢平均成績大於60分的同學的學號和平均成績；
select student_id,avg(num) as 平均成績 from score group by student_id having 平均成績 > 60;


4、查詢所有同學的學號、姓名、選課數、總成績；
select student.sid,sname,count(student_id),sum(num)
from score right join student
on student.sid=student_id
group by student_id;


5、查詢姓“李”的老師的個數；
select count(*) from teacher where tname like '李%';

6、查詢沒學過“李平老師”老師課的同學的學號、姓名；
select distinct student.sid,sname from student where sid not in (
select distinct student_id
from score
where course_id in(
select cid
from course left join teacher
on course.teacher_id=teacher.tid
where tname = '李平老師')
);

7、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名；
1)
select A.sid,A.sname from
(select student.sid,sname from score right join student
on student.sid=student_id
where course_id='001') as A
inner join
(select student.sid,sname from score right join student
on student.sid=student_id
where course_id='002') as B
on A.sid=B.sid
2)
select student_id,sname from
(select student_id,course_id from score where course_id = 1 or course_id = 2) as B
left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1

8、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；
select student.sid,sname from score right join student
on student.sid=student_id
where course_id in(
select cid from course left join teacher
on course.teacher_id=teacher.tid
where tname='李平老師'
) group by student.sid HAVING count(student.sid) = (
select count(cid) from course left join teacher
on course.teacher_id=teacher.tid
where tname='李平老師'
);

9、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學的學號、姓名；
select sname,sid from student inner join (
select A.student_id from
(select student_id,num as s1 from score left join course on score.course_id = course.cid where course.cid = '001') as A
left join
(select student_id,num as s2 from score left join course on score.course_id = course.cid where course.cid = '002') as B
on A.student_id = B.student_id where s1 > if(isnull(s2),0,s2)
) as C
on C.student_id=student.sid;

10、查詢有課程成績小於60分的同學的學號、姓名；
select distinct student.sid,sname from student left join score
on student.sid=score.student_id
where num<60;

11、查詢沒有學全所有課的同學的學號、姓名；
select student.sid,sname from score right join student
on student.sid=score.student_id
group by student_id
having count(student_id) != (
select count(*) from course
);

12、查詢至少有一門課與學號為“001”的同學所學相同的同學的學號和姓名；
select student.sid,sname,count(sname) from score right join student
on student.sid=student_id
where student_id !='001' and course_id in (
select course_id from score where student_id='001'
) group by student_id;

13、查詢至少學過學號為“001”同學所有課的其他同學學號和姓名；
select student.sid,sname from score right join student
on student.sid=student_id
where student_id !='001' and course_id in (
select course_id from score where student_id='001'
) group by student_id
having count(sname) >= (
select count(*) from score
where student_id='001');

14、查詢和“002”號的同學學習的課程完全相同的其他同學學號和姓名；

select student.sid,sname from score right join student
on student.sid=student_id where student_id in(
select student_id from score where student_id != 2 group by student_id HAVING count(course_id) = (select count(course_id) from score where student_id = 2))
and course_id in (
select course_id from score where student_id='002'
) group by student_id
having count(course_id) = (
select count(course_id) from score
where student_id='002');

15、刪除學習“葉平”老師課的score表記錄；
delete from score where course_id in (
select cid from course left join teacher on course.teacher_id=tid where tname='李平老師'
)

16、向SC表中插入一些記錄，這些記錄要求符合以下條件：①沒有上過編號“002”課程的同學學號；②插入“002”號課程的平均成績；
insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)
from student where sid not in (
select student_id from score where course_id = 2
)

17、按平均成績從低到高 顯示所有學生的“生物”、“物理”、“體育”、“美術”4門的課程成績，按如下形式顯示：學生ID,生物,物理,體育,美術,有效課程數,有效平均分；（不會這種查詢語法）
select student_id,
(select num from score s left join course c on s.course_id=c.cid where cname='生物' and sc.student_id=s.student_id) as 生物分數,
(select num from score s left join course c on s.course_id=c.cid where cname='物理' and sc.student_id=s.student_id) as 物理分數,
(select num from score s left join course c on s.course_id=c.cid where cname='體育' and sc.student_id=s.student_id) as 體育分數,
(select num from score s left join course c on s.course_id=c.cid where cname='美術' and sc.student_id=s.student_id)as 美術分數,
count(course_id),avg(num)
from score sc
group by student_id
order by avg(num);

18、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；
select course_id,max(num),min(num) from score group by course_id;

19、按各科平均成績從低到高和及格率的百分數從高到低順序；
1)
select avg(num),score.course_id,課程人數,課程及格人數,(課程及格人數/課程人數*100) as 及格率 from score left join
(select course_id,count(student_id) as 課程人數 from score group by course_id order by course_id) as A
on score.course_id=A.course_id
left join
(select course_id,count(student_id) as 課程及格人數 from score where num >=60 group by course_id order by course_id) as B
on A.course_id=B.course_id
group by score.course_id
having
order by avg(num),及格率 desc;
2)
select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;

20、課程平均分從高到低顯示（現實任課老師）；
select tname,avg(課程平均分) from
(select tname,c.cid from teacher t right join course c on t.tid=c.teacher_id) as A
left join
(select avg(num) as 課程平均分,course_id from score group by course_id order by avg(num) desc) as B
on A.cid=B.course_id
group by tname
order by avg(課程平均分) desc;

21、查詢各科成績前5名的記錄:(不考慮成績併列情況)；
select distinct cname,course_id,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 2,1) as third_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as fourth_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 4,1) as fifth_num
from score s1 left join course c on c.cid=s1.course_id;

22、查詢每門課程被選修的學生數；
select course_id,count(student_id) from score group by course_id;

23、查詢出只選修了一門課程的全部學生的學號和姓名；
select student_id,sname from student st left join score sc on st.sid=sc.student_id
group by student_id
having count(course_id)=1;

27、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列；
select course_id,avg(if(isnull(num),0,num)) from score group by course_id order by avg(num),course_id desc;

28、查詢平均成績大於85的所有學生的學號、姓名和平均成績；
select student_id,sname,avg(num) from score sc left join student st on sc.student_id=st.sid group by student_id;

29、查詢課程名稱為“生物”，且分數低於60的學生姓名和分數；
select sname,num from score sc left join student st on sc.student_id=st.sid
left join course c on c.cid=sc.course_id
where c.cname='生物' and num<60;

31、求選了課程的學生人數
select count(distinct student_id) from score

32、查詢選修“張磊老師”老師所授課程的學生中，成績最高的學生姓名及其成績；
select sname,max(num) from score sc
left join student st on sc.student_id=st.sid
where course_id in (
select cid from course c
left join teacher t on c.teacher_id=t.tid
where tname='張磊老師'
);

33、查詢各個課程及相應的選修人數；
select cname,count(student_id) from score sc
left join course c on c.cid=sc.course_id
group by course_id;

34、查詢不同課程但成績相同的學生的學號、課程號、學生成績；
select student_id,course_id,num from score where sid in (
select s1.sid from score s1
inner join score s2 on s1.num=s2.num and s1.course_id != s2.course_id)

35、查詢每門課程成績最好的前兩名；
select distinct cname,course_id,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num
from score s1 left join course c on c.cid=s1.course_id;

36、檢索至少選修兩門課程的學生學號；
select student_id from score sc group by student_id having count(course_id)>1;

37、查詢全部學生都選修的課程的課程號和課程名；
select course_id,cname from score sc
right join course c on c.cid=sc.course_id
group by course_id
having count(student_id)=(
select count(distinct sid) from student
);

38、查詢沒學過“葉平”老師講授的任一門課程的學生姓名；
select dictinct sname from score sc
left join student st on sc.student_id=st.sid
where student_id not in (
select student_id from score where course_id in (
select cid from course c left join teacher t on c.teacher_id=t.tid where tname='張磊老師'
)
);

39、查詢兩門以上不及格課程的同學的學號及其平均成績；
select student_id,avg(num) from score where student_id in (
select student_id from score where num<60 group by student_id having count(course_id)>1
) group by student_id;

40、檢索“004”課程分數小於60，按分數降序排列的同學學號；
select student_id from score where course_id=4 and num<60 order by num desc;