实验内容
1.创建如下三个表,内容任意(至少10条记录)。
STUDENT(sno, sname, age, address)//学号,姓名,年龄,籍贯
SC(sno, cno, grade, credit)//学号,课程号,成绩,学分
COURSE(cno, cname, teacher)//课程号,课程名字,授课教师
(请自行创建好)
2.在SQL Server中实现下列操作的SQL查询语句。
(1)检索”liu”老师所授课程的所有信息。
select *from COURSE where teacher='liu'
(2)查询输出“山东”籍的学生人数。
select count(*) from STUDENT where address='山东'
(3)检索年龄大于20岁学生姓名和籍贯信息。
select sname,address from STUDENT where age>20
(4)检索学号为“001”号学生选修的课程的课程名字及授课教师名字。
select cname,teacher from COURSE,SC
where COURSE.cno=SC.cno
and sno='001'
(5)统计“database”这门课的平均成绩。
select avg(grade) from SC where cno in
(select cno from COURSE where cname='database')
(6)统计“c01”号课程成绩在70分以上的及格人数。
select count(*) from SC where cno='c01' and GRADE>=70
(7)统计“database”这门课的最高成绩、最低成绩和平均成绩。
select MAX(grade),MIN(grade),AVG(grade) from sc where cno in
(select cno from course where cname ='database');
(8)检索“wang”同学没有选修的课程的课程号;
select cno from COURSE where cno not in
(select cno from SC where sno in (select sno from STUDENT where sname='wang'))
(9)检索“wang”同学没有选修的课程的课程名字;
select cname from COURSE where cno not in
(select cno from SC where sno in (select sno from STUDENT where sname='wang'))
(10)检索“zhang”同学已经获得学分的课程的名字。
select cname from COURSE where cno in
(select cno from SC where sno in
(select sno from STUDENT where sname='zhang') and grade>60)
(11)检索已经获得8个以上学分的学生详细信息。
select * from student where sno =
(select sno from sc where cno =
(select cno from course
group by cno having sum(credit)>4)
)
(12)检索至少选修了三门课程的学生的学号、姓名。
select sno,sname from student where sno in
(select sno from sc group by sno having COUNT (*) >=3)
(13)检索全部学生都选修了的课程的课程号和课程姓名。
select cno,cname from course where cno in
(select cno from sc group by cno having COUNT (*) =
(select COUNT(*) from student))
(14)检索至少选修“liu”老师一门所授课程的女学生的姓名。
select sname from student where sno in
(select sno from sc,course where sc.cno=course.cno and teacher='liu')
(15)检索选修了“liu”老师所授全部课程的学生的学号。
select sno from sc where cno in
(select cno from course where teacher='liu')