数据库实验：学生管理系统数据库。

数据库表各个表数据如下

建表语句

向tab_student中插入数据
INSERT INTO tab_student VALUES(‘S001’,‘李四’,‘男’,‘2000.1.1’,‘16010101’,2016,‘0101’)
INSERT INTO tab_student VALUES(‘S002’,‘李三’,‘男’,‘2000.3.5’,‘16010101’,2016,‘0101’)
INSERT INTO tab_student VALUES(‘S003’,‘李五’,‘男’,‘1999.3.2’,‘16010101’,2016,‘0101’)
INSERT INTO tab_student VALUES(‘S004’,‘赵刚’,‘男’,‘1999.10.2’,‘16010201’,2016,‘0102’)
INSERT INTO tab_student VALUES(‘S005’,‘赵水’,‘女’,‘1999.9.9’,‘16010201’,2016,‘0102’)
INSERT INTO tab_student VALUES(‘S006’,‘赵火’,‘女’,‘2000.9.9’,‘16010101’,2016,‘0101’)
INSERT INTO tab_student VALUES(‘S007’,‘刘芳’,‘女’,‘1999.6.1’,‘19020101’,2016,‘0201’)

向tab_score中插入数据
INSERT INTO tab_score VALUES(‘S001’,‘C002’,‘T003’,90)
INSERT INTO tab_score VALUES(‘S001’,‘C003’,‘T001’,85)
INSERT INTO tab_score VALUES(‘S001’,‘C010’,‘T005’,95)
INSERT INTO tab_score VALUES(‘S002’,‘C002’,‘T003’,80)
INSERT INTO tab_score VALUES(‘S002’,‘C003’,‘T001’,65)
INSERT INTO tab_score VALUES(‘S003’,‘C002’,‘T003’,60)
INSERT INTO tab_score VALUES(‘S003’,‘C003’,‘T001’,95)
INSERT INTO tab_score VALUES(‘S004’,‘C010’,‘T005’,77)
INSERT INTO tab_score VALUES(‘S005’,‘C011’,‘T001’,66)
INSERT INTO tab_score VALUES(‘S006’,‘C002’,‘T003’,100)
INSERT INTO tab_score VALUES(‘S006’,‘C003’,‘T001’,65)
INSERT INTO tab_score VALUES(‘S007’,‘C010’,‘T005’,55)

连接查询

查询全校的教师的工号、姓名、所在学院名

Select tno,tname,instname From tab_teacher,tab_institute where tab_teacher.instno=tab_institute.instno

查询授课教师的工号、姓名、讲授课程名

Select tab_teacher.tno,tname,tab_course.cname from tab_teacher,tab_course,tab_score where tab_teacher.tno=tab_score.tno

查询 “数据结构”课程的学生的学号、姓名、课程名、成绩、任课教师名
Select tab_student.sno,tab_student.sname,tab_course_cname,tab_score.score,tab_teacher.tname from tab_student,tab_course,tab_score,tab_teacher where tab_student.sno=tab_score.sno AND tab_course.cno=tab_score.cno AND tab_teacher.tno=tab_score.tno AND tab_course.cname=’数据结构’

查询“计算机学院”所有学生的学号、姓名、课程名、成绩、任课教师名

Select tab_student.sno,tab_student.sname,tab_course.cname,tab_score.score,tab_teacher.tname from tab_student,tab_course,tab_score,tab_teacher,tab_major,tab_institue where tab_institue.instname=’计算机学院’ AND tab_institue.instno=tab_major.instno AND tab_student.sno=tab_score.sno AND tab_teacher.tno=tab_score.tno AND tab_course.cno=tab_score.cno AND tab_major.majorno=tab_student.majorno

查询所有授课的“教授”的工号、姓名和授课课程名
Select tab_teacher.tno,tab_teacher.tname,tab_course.cname from tab_teacher,tab_course,tab_score where tab_teacher.title=’教授’ AND tab_teacher.tno=tab_score.tno AND tab_score.cno=tab_course.cno

嵌套查询的操作练习

Select * from tab_teacher where tab_teacher.instno IN (select tab_institue.instno from tab_institue where tab_institue.instname=’计算机学院’)

2）查询“计算机学院”的课程记录
Select * from tab_course where tab_course.majoyno IN (select tab_major.majoyno from tab_major where tab_major.instno =(select tab_institue.instno from tab_institue where tab_institue.instname=’计算机学院’) ))

3）查询讲授“数据库”课程的教师记录
Select * from tab_teacher where tab_teacher.tno =(select tab_score.tno from tab_score where tab_score.cno =(select tab_course.cno from tab_course where tab_course.cname=’数据库’))

4）查询选修“数据结构”课程的学生记录
Select * from tab_student where tab_student.sno IN (select tab_score.sno from tab_score where tab_score.cno =(select tab_course.cno from tab_course where tab_course.cname=’数据结构’))

5）查询“C002”课程成绩第一名的学生的学号与姓名

SELECT sno,sname FROM tab_student WHERE sno=(SELECT sno FROM tab_score WHERE score=(SELECT MAX(score) FROM tab_score WHERE cno=‘C002’))

6）查询“C001”课程成绩高于“李四”的学生的学号与成绩
（这个SQL语句不太会写）

以下这一句SQL有误，但暂时我不知道错在哪，该如何改。
SELECT sno,score FROM tab_score WHERE cno=‘C002’ AND score IN (SELECT score FROM tab_score WHERE score>(SELECT score FROM tab_score WHERE cno=‘C002’ AND sno=(SELECT sno FROM tab_student WHERE sname=‘李四’))

7）查询所有授课的“教授”的工号、姓名和授课课程

Select distinct tab_teacher.tno,tab_teacher.tname,tab_course.cname from tab_teacher,tab_course,tab_score
where tab_course.cno IN
(select tab_score.cno from tab_score where tab_score.tno IN
(select tab_teacher.tno from tab_teacher where tab_teacher.title=‘教授’)) AND tab_teacher.title=‘教授’