西安哪个公司网站建设好,制作网站的全过程,品牌公关案例,php主机网站一. 简答题#xff08;共1题#xff0c;100分#xff09; 1. (简答题) 存在数据库test#xff0c;数据库中有如下表#xff1a; 1.学生表 Student(Sno,Sname,Sage,Ssex) --Sno 学号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 主键Sno 2.教师表 Teacher(Tno,Tname) --T… 一. 简答题共1题100分 1. (简答题) 存在数据库test数据库中有如下表 1.学生表 Student(Sno,Sname,Sage,Ssex) --Sno 学号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 主键Sno 2.教师表 Teacher(Tno,Tname) --Tno 教师编号,Tname 教师姓名 主键Tno 3.课程表 Course(Cno,Cname,Tno) --Cno --课程编号,Cname 课程名称,Tno 教师编号 主键Cno 外键Tno参照教师表 4.成绩表 SC(Sno,Cno,score) --Sno 学生编号,Cno 课程编号,score 分数 主键Sno,Cno 外键Sno,Cno分别参照学生表和课程表 按要求写出如下SQL语句 1、查询选了3门课程的同学信息 2、查询没有学全所有课程的同学的信息要显示没有选课的同学信息 3、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩 4、查询选修吴大老师所授课程的学生中成绩最高的学生信息及其成绩 当最高分只有一个时 5、创建一个选了2门课程的学生的学号、姓名及平均成绩的视图V1 6、查询选了2门课程且平均成绩在60分以下的学生的姓名与平均成绩 我的答案
1.
select s.*
from student s
inner join ( select Sno from SC group by SC.Sno having count(Sno) 3
) c on s.Snoc.Sno
2.
select s.*
from student s,SC c
where s.Snoc.Sno
group by c.Sno
having count(c.Sno) (select count(*) from course)
3.
select s.Sno,s.Sname,AVG(c.score)
from Student s
inner join SC c on s.Sno c.Sno
where c.score60
group by s.Sno,s.Sname
having count(* ) 2 4.
select s.*
from student s
join SC c on s.Sno c.Sno
join Course co on c.Cno co.Cno
join Teacher t on co.Tno t.Tno
where t.Tname 吴大
order by c.score desc
limit 1
5.
create view V1
as
select s.Snos.Sname,avg_score
from student s
inner join ( select Sno ,AVG(SC.score) avg_score from SC group by SC.Sno having count(Sno) 2
) c on s.Snoc.Sno
6.
查询选了2门课程且平均成绩在60分以下的学生的姓名与平均成绩
select s.Sname,avg_score
from student s
inner join ( select Sno,AVG(score) avg_score from SC where count(Sno) 2 group by Sno having AVG(score)60
)c on s.Snoc.Sno
这里也可以使用第五题中创建的视图
select s.Sname,avg_score
from V1
where avg_score60 标准参考答案
1、select student.*
from student,sc
where student.snosc.Sno
GROUP BY Sno
HAVING count(sc.Cno)2; 2、select student.*
from student LEFT OUTER JOIN sc
ON student.snosc.Sno
GROUP BY Sno
HAVING count(sc.Cno)(SELECT count(*) from course); 3、SELECT student.sno,student.Sname,avg(sc.score)
FROM student,sc
WHERE student.snosc.Sno and student.sno in (
SELECT sno FROM sc where score60
GROUP BY Sno
HAVING count(*)2)
GROUP BY student.Sno; 4、SELECT student.*,sc.score
FROM student,sc,course,teacher
WHERE teacher.Tnocourse.Tno AND course.Cnosc.Cno AND sc.Snostudent.Sno
AND teacher.Tname吴大 ORDER BY sc.score DESC
LIMIT 0,1;
当最高分出现多个时
SELECT student.*,sc.score
FROM student,sc,course,teacher
WHERE teacher.Tnocourse.Tno AND course.Cnosc.Cno AND sc.Snostudent.Sno
AND teacher.Tname吴大
AND sc.score(SELECT MAX(sc.score)
FROM sc,course,teacher
WHERE teacher.Tnocourse.Tno AND course.Cnosc.Cno AND teacher.Tname吴大); 5、
CREATE VIEW v1(sno,sname,ascore)
AS
SELECT student.Sno, student.Sname,AVG(sc.score)
from student,sc
where student.Snosc.Sno
GROUP BY student.Sno
HAVING count(*)2; 6、SELECT sname ,ascore from v1
where ascore60;
