mysql数据库自测题-----多表查询习题

1.根据表的结构创建以下四个表

表一. student（学生表）

表二. teacher（教师表）

表三. course（课程表）

表四. score（成绩表）

2.分别向以上四个表中插入如下数据

Student表：

Sno	     Sname	    Ssex	 Sbirthday	    class
108	     曾华	     男	    1977-09-01	    95033
105	     匡明	     男	    1975-10-02	    95031
107	     王丽	     女	    1976-01-23	    95033
101	     李军	     男	    1976-02-20	    95033
109	     王芳	     女	    1975-02-10	    95031
103	     陆君	     男	    1974-06-03	    95031

代码如下：

insert into student values('108','曾华','男','1977-09-01','95033'),('105','匡明','男','1975-10-02','95031'),('107','王丽','女','1976-01-23','95033'),('101','李军','男','1976-02-20','95033'),('109','王芳','女','1975-02-10','95031'),('103','陆君','男','1974-06-03','95031'); 

Teacher表：

Tno	Tname	  Tsex	    Tbirthday	Prof	      Depart
804	李诚	男	1958-12-02     	       副教授	       计算机系
856	张旭	男	1969-03-12	              讲师	       电子工程系
825	王萍	女	1972-05-05	              助教	       计算机系
831	刘冰	女	1977-08-14	              助教	       电子工程系

代码如下：

insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系'),('856','张旭','男','1969-03-12','讲师','电子工程系'),('825','王萍','女','1972-05-05','助教','计算机系'),('831','刘冰','女','1977-08-14','助 教','电子工程系'); 

Course表：

Cno	     Cname	    Tno
3-105	计算机导论	825
3-245	操作系统	   804
6-166	数字电路	   856
9-888	高等数学	   831

代码如下：

 insert into course values('3-105','计算机导论','825'),('3-245','操作系统','804'),('6-166','数字电路','856'); 
Score表：
Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

代码如下：”

 insert into score values('103','3-245',86),('105','3-245',75),('109','3-245',68),('103','3-105',92),('105','3-105',88),('109','3-105','76'),('101','3-105',64),('107','3-105',91),('108','3-105',78),('101','6-166',85),('107','6-166',79),('108','6-166',81); 

3.查询‘95031’班级的学生数

select count(sname) from student where class=95031;

4.查询成绩表中的最高分的学生学号和课程号
select sno,cno,degree from score ORDER BY degree desc LIMIT 1

5.查询每门课的平均成绩

S

ELECT
	Cname,
	AVG(Degree)
FROM
	course
LEFT JOIN score ON course.Cno = score.Cno
GROUP BY
	Cname;

6.查询成绩表中至少有5名学生选修的并且以3开头的课程的平均分数

select cno,count(sno),avg(degree) from score group by cno having count(sno)>5;

7.查询所有学生的sname，cno，degree的列

select sname,cno,degree from student as s LEFT JOIN score as c on s.Sno=c.Sno;

8.查询95033班级学生的平均分

select avg(s.Degree) as '平均分' from score as s LEFT JOIN student s1 on s.Sno=s1.Sno where Class=95033;

9.查询张旭教师任课的学生成绩

SELECT Sname, Degree FROM teacher AS t INNER JOIN course AS c INNER JOIN score AS s INNER JOIN student AS s2 ON t.Ton = c.Tno AND s.Cno = c.Cno AND s.Sno = s2.Sno AND Tname = '张旭'

10.查询选修某课程的同学人数多于5人的教师姓名

select te.tname from teacher as te  INNER JOIN  course co INNER JOIN score sc INNER JOIN student st ON
 te.Ton=co.tno  and co.cno=sc.cno and sc.sno=st.sno  
GROUP BY te.tname having count(te.tname)>5;

或者：

select te.tname from teacher as te  INNER JOIN  course co INNER JOIN score sc INNER JOIN student st ON
 te.Ton=co.tno  and co.cno=sc.cno and sc.sno=st.sno  
GROUP BY sc.cno having count(sc.sno)>5;

11.查找计算机系教师所教课程的成绩表

SELECT tname, sname,degree FROM teacher AS t INNER JOIN course AS c INNER JOIN score AS s INNER JOIN student AS s2 ON t.Ton = c.Tno AND s.Cno = c.Cno AND s.Sno = s2.Sno AND Depar='计算机系'

12.查询所有选修‘计算机导论’课程的‘男’同学的成绩表

SELECT Sname,Degree FROM teacher AS t INNER JOIN course AS c INNER JOIN score AS s INNER JOIN student AS s2 ON t.Ton = c.Tno AND s.Cno = c.Cno AND s.Sno = s2.Sno AND Cname = '计算机导论'
 and Ssex='男';