1.实验中要使用包括如下三个表的“教学管理”数据库JXGL:
(1)学生表Student,由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成,记作:Student(Sno,Sname,Ssex,Sage,Sdept),其中主码为Sno。
(2)课程表Course,由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成,记作:Course(Cno,Cname,Cpno,Ccredit),其中主码为Cno。
(3)学生选课SC,由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成,记作:SC(Sno,Cno,Grade),其中主码为(SNO,CNO)。
基于“教学管理”数据库JXGL,试用SQL的查询语句表达下列查询:
- 检索年龄大于23岁的男学生的学号和姓名
SELECT Sno,Sname
FROM Student
WHERE Sage>23 AND Ssex='男'
- 检索至少选修两门课程的学生学号
select sno
from sc
group by sno
having count(*)>1
- 统计有学生选修的课程门数
select count(distinct cno)
from SC
(4)统计每门课程的学生选修人数,超过3人的课程才统计。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
select CNO,count(CNO)
from SC
group by CNO
having count(CNO)>3
order by count(CNO) desc,CNO asc
(5)检索姓名以王打头的所有学生的姓名和年龄
select Sname,Sage
from Student
where Sname like'王%'
(6)在SC中检索成绩为空值的学生学号和课程号
select Sno,Cno
from SC
where Grade is NULL
(7)检索选修2号课程的学生中成绩最高的学生的学号
select top 1 with ties Sno
from SC
where Cno='2'
order by Grade desc
- 检索学生姓名及其所选修课程的课程号和成绩
select Student.Sname,SC.Cno,SC.Grade
from Student,SC
where Student.Sno=SC.Sno
- 检索选修及格的4门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来
select Sno,sum(Grade)
from SC
where Grade>=60
group by Sno
having count(*)>=4
order by sum(Grade) desc
2.设有如下4个基本表(表结构与表内容是假设的),请先创建数据库及根据表内容创建表结构,并添加表记录,实践以下SQL命令操作:
- 查询选修课程'8105'且成绩在80到90之间的所有记录
select *
from SC
where CNO='8105'and GRADE between 80 and 90
- 查询成绩为79、89或99的记录
select *
from SC
where GRADE=79 or GRADE=89 or GRADE=99
- 查询9803班的学生人数
select count(SNO)
from STUDENT
where CLASS='9803'
- 查询至少有20名学生选修的并以8开头的课程的平均成绩
select CNO,avg(GRADE)
from SC
where CNO like '8%'
group by CNO
having count(SNO)>=20
- 查询最低分大于80,最高分小于95的SNO与平均分
select SNO,avg(GRADE)
from SC
group by SNO
having min(GRADE)>80 and max(GRADE)<95
- 查询9803班学生所选各课程的课程号及其平均成绩
select CNO,avg(GRADE)
from SC
where SNO like'9803%'
group by CNO
- 列出所有教师和同学的姓名、SEX、AGE
select SNAME,SEX,AGE
from STUDENT
union
select TNAME,SEX,AGE
from TEACHER
- 列出至少有4名男生的班号
select class
from student
where SEX='男'
group by CLASS
having count(sex)>=4
- 查询不姓“张”的学生记录
select *
from STUDENT
where SNAME not like '张%'