附加teaching数据库，完成下列查询，并将语句拷贝到实验报告保存。 二、在teaching数据库中完成下列查询 1、查询电话号码中有‘7’的学生的学号、姓名及电话号码。 2、查询选修了c05109课程的学生学号和期末成绩。 3、查询教师人数小于三人的学院名称和人数。 4查询选修了c05109课程的学生姓名和期末成绩。 5、查询选修了“数据结构”课程的学生姓名和期末成绩。 6、查询与‘何影’在同一个班级的同学信息。（嵌套查询） 7、查询每个学生超过他选修课程期末考试平均成绩的课程号及考试成绩。

1、查询电话号码中有‘7’的学生的学号、姓名及电话号码。

SELECT SNO, SNAME, TEL FROM STUDENT WHERE TEL LIKE '%7%';

2、查询选修了c05109课程的学生学号和期末成绩。

SELECT SC.SNO, SC.GRADE FROM SCORE AS SC, COURSE AS C WHERE SC.CNO = C.CNO AND C.CNAME = 'c05109';

3、查询教师人数小于三人的学院名称和人数。

SELECT D.DNAME, COUNT(T.TNO) AS TEACHER_NUM FROM TEACHER AS T, DEPARTMENT AS D WHERE T.DNO = D.DNO GROUP BY D.DNAME HAVING COUNT(T.TNO) < 3;

4、查询选修了c05109课程的学生姓名和期末成绩。

SELECT S.SNAME, SC.GRADE FROM STUDENT AS S, SCORE AS SC, COURSE AS C WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND C.CNAME = 'c05109';

5、查询选修了“数据结构”课程的学生姓名和期末成绩。

SELECT S.SNAME, SC.GRADE FROM STUDENT AS S, SCORE AS SC, COURSE AS C WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND C.CNAME = '数据结构';

6、查询与‘何影’在同一个班级的同学信息。（嵌套查询）

SELECT * FROM STUDENT WHERE SCLASS = (SELECT SCLASS FROM STUDENT WHERE SNAME = '何影');

7、查询每个学生超过他选修课程期末考试平均成绩的课程号及考试成绩。

SELECT SC.SNO, SC.CNO, SC.GRADE FROM SCORE AS SC 
WHERE SC.GRADE > (SELECT AVG(GRADE) FROM SCORE WHERE SNO = SC.SNO AND CNO = SC.CNO);