mysql初学者题目及答案

声明：该文档主要根据网上一份题目整理而成。给出的答案仅供参考。

假设有下面4张表

student(sid,sname,sage,ssex) 学生表

course(cid,cname,tid) 课程表

grade(sid,cid,score) 成绩表

teacher(tid,Tname) 教师表

 

问题：

1、  查询“001”课程比“002”课程成绩高的所有学生的学号

 #方式1

select a.sid

from grade a,grade b

where a.sid=b.sidand a.cid=2 and b.cid=3 and a.score>b.score;

 #方式2

select a.sid

from (select sid,score from grade where cid=2) a,

(select sid,score from grade where cid=3) b

where a.sid=b.sidand a.score>b.score;

2、  查询平均成绩大于60分的同学的学号和平均成绩；

select sid,avg(score) as avg

from grade

group by sid

havingavg>60;

 

3、  查询所有同学的学号、姓名、选课数、总成绩；

 #方式1 

select s.sid,s.sname,count(cid),sum(score)

from student s

left join grade g

on s.sid=g.sid

group by s.sid;

 

#方式2

select s.sid,s.sname,a.num,a.totals

from student s

left join (select sid,count(cid) as num,

sum(score) as totals from grade group by sid)as a

on s.sid=a.sid;

 

#方式3 

select s.sid,s.sname,(select count(cid)from grade where sid=s.sid) as num,

(select sum(score) from grade where sid=s.sid)as totals

from student s;

 

4、  查询姓“李”的老师的个数；

select count(tname)

from teacher

where tname like '李%';

 

5、查询没学过“ta”老师课的同学的学号、姓名；

#方式1

select sid,sname

from student

where sid not in

(select sid from grade where cid in

(select cid from course c,teacher t wherec.tid=t.tid and t.tname='ta')

);

 #方式2

select sid,sname

from student

where sid not in

(selectdistinct sid from grade g,course c,teacher t where g.cid=c.cid and c.tid=t.tidand t.tname='ta');

 

6、查询学过“002”并且也学过编号“003”课程的同学的学号、姓名；

 #方式1

select sid,sname

from student

where sid in (

select a.sid

from (select sid from grade where cid=2) a,(select sid from grade where cid=3) b

wherea.sid=b.sid);

 #方式2

select sid,sname

from student

where sid in (select sid from grade wherecid=2)

and

sid in (select sidfrom grade where cid=3);

 

#方式3

select s.sid,sname

from student s,grade g

where s.sid=g.sid and g.cid=2 and

s.sid in (select sid from grade wherecid=3);

 

 #方式4

 

select s.sid, sname

from student s,grade g

where s.sid=g.sid and g.cid=2 and

exists( Select * from grade as g2 where g2.sid=g.sidand g2.cid=3);

 

 

7、查询学过“tb”老师所教的所有课的同学的学号、姓名；

 

#方式1

#利用sum统计每个同学上tb老师课的门数，选出那些门数=tb所教课的门数

 

Select sid,sname

from student

where sid in

(select sid

from grade

group by sid

having sum(case when cid in (select cidfrom Course c,Teacher t where T.tid=C.tid and Tname='tb') then 1 else 0 end)

=(select count(cid) from Course c,Teacher twhere T.tid=C.tid and Tname='tb')

);

 

 #方式2

select sid,sname

fr