数据库实验三-SQL语言-SELECT查询操作
一、实验目的
表或视图数据的各种查询(与统计)SQL命令操作，具体分为：
①了解查询的概念和方法；
②掌握SQL Server集成管理器查询子窗口中执行SELECT操作的方法；
③掌握SELECT语句在单表查询中的应用；
④掌握SELECT语句在多表查询中的应用；
⑤掌握SELECT语句在复杂查询中的使用方法。
二、实验内容
(1)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询:
①检索年龄大于23岁的男学生的学号和姓名;
②检索至少选修一门课程的女学生姓名;
③检索王同学不学的课程的课程号;
④检索至少选修两门课程的学生学号;
⑤检索全部学生都选修的课程的课程号与课程名;
⑥检索选修了所有3学分课程的学生学号。
(2)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询:
①统计所有学生选修的课程门数;
②求选修4号课程的学生的平均年龄;
③求学分为3的每门课程的学生平均成绩;
④统计每门课程的学生选修人数，超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列;
⑤检索学号比王非同学大,而年龄比他小的学生姓名;
⑥检索姓名以王打头的所有学生的姓名和年龄;
⑦在SC中检索成绩为空值的学生学号和课程号;
⑧求年龄大于女同学平均年龄的男学生的姓名和年龄;
⑨求年龄大于所有女同学年龄的男学生的姓名和年龄;
⑩检索所有比“王华”年龄大的学生姓名、年龄和性别;
⑪检索选修2号课程的学生中成绩最高的学生的学号;
⑫检索学生姓名及其所选修课程的课程号和成绩;
⑬检索选修4门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。
(3)设有如下4个基本表(表结构与表内容是假设的)，如表4-1、表4-2、表4-3、表4-4所示，请先创建数据库及根据表内容创建表结构，并添加表记录，实践以下各题的SQL命令操作：
①查询选修课程“8105”且成绩在80到90之间的所有记录;
②查询成绩为79,89或99的记录;
③查询9803班的学生人数;
④查询至少有20名学生选修的并以8开头的课程的平均成绩:
⑤查询最低分大于80,最高分小于95的SNO与平均分:
⑥查询9803班学生所选各课程的课程号及其平均成绩:
⑦查询选修“8105”课程的成绩高于“9809”号同学成绩的所有同学的记录:
⑧查询与学号为“9808”的同学同岁的所有学生的SNO、SNAME和AGE:
⑨查询“钱军”教师任课的课程号,选修其课程学生的学号和成绩;
⑩查询选修某课程的学生人数多于20人的教师姓名;
⑪查询同学选修编号为“8105”课程且成绩至少高于其选修编号为“8245"课程的同学的SNO及“8105”课程成绩，并按成绩从高到低次序排列;
⑫查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE;
⑬列出所有教师和同学的姓名、SEX、AGE;
⑭查询成绩比该课程平均成绩高的学生的成绩表;
⑮列出所有任课教师的TNAME和DEPT;
⑯列出所有未讲课教师的TNAME和DEPT;
⑰列出至少有4名男生的班号;
⑱查询不姓“张"的学生的记录;
⑲查询每门课最高分的学生的SNO、CNO、GRADE;
⑳查询与“李华”同性别并同班的同学的SNAME;
㉑查询“女”教师及其所上的课程;
㉒查询选修“数据库系统”课程的“男”同学的成绩表;
㉓查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄;
㉔查询不讲授“8104”号课程的教师姓名。
三、实验结果
--1.1检索年龄大于23岁的男学生的学号和姓名;
select SNO,SNAME
from STUDENT
where SAGE>23 AND SSEX = '男';
--1.2检索至少选修一门课程的女学生姓名;
select SNAME
from STUDENT,SC
where STUDENT.SNO=SC.SNO
AND SSEX='女'
group by SNAME
having count(Cno)>=1;
--1.3检索王同学不学的课程的课程号;
SELECT Cno
FROM Course
WHERE Cno != ALL
(SELECT Course.Cno
FROM Student, SC,Course
WHERE (Sname LIKE '王%' AND Student.Sno = SC.Sno AND SC.Cno = Course.Cno));
--1.4检索至少选修两门课程的学生学号;
select SNO
from SC
group by SNO
having count(Cno)>=2;
--1.5检索全部学生都选修的课程的课程号与课程名;
SELECT SC.CNO, CNAME
FROM COURSE left join SC
on COURSE.CNO=SC.CNO
GROUP BY SC.CNO, CNAME
HAVING COUNT()=(
SELECT COUNT()
FROM STUDENT);
--1.6检索选修了所有3学分课程的学生学号。
SELECT SNO
FROM SC
WHERE CNO IN (
SELECT CNO
FROM COURSE
WHERE CCREDIT=3)
GROUP BY SNO
HAVING COUNT()=(
SELECT COUNT()
FROM COURSE
WHERE CCREDIT=3);
--2.1统计所有学生选修的课程门数;
SELECT COUNT(DISTINCT CNO)'所有学生选修课的课程门数'
FROM SC;
--2.2求选修4号课程的学生的平均年龄;
SELECT AVG(SAGE)'选修4号课程的学生的平均年龄'
FROM SC,STUDENT
WHERE STUDENT.SNO=SC.SNO
AND CNO=4;
--2.3求学分为3的每门课程的学生平均成绩;
SELECT AVG(GRADE) '学分为3的每门课程的学生的平均成绩'
FROM SC, COURSE
WHERE SC.Cno = Course.Cno AND Course.Ccredit = 3;
--2.4统计每门课程的学生选修人数，超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列;
SELECT CNO, COUNT(SNO) Num
FROM SC
GROUP BY CNO
HAVING COUNT(SNO) > 3
ORDER BY COUNT(SNO) DESC, CNO ASC;
--2.5检索学号比王非同学大,而年龄比他小的学生姓名;
SELECT SNAME
FROM STUDENT
WHERE Sno > (SELECT Sno FROM Student WHERE Sname = '王非')
AND Sage < (SELECT Sage FROM Student WHERE Sname = '王非');
--2.6检索姓名以王打头的所有学生的姓名和年龄;
SELECT SNAME,SAGE
FROM STUDENT
WHERE SNAME LIKE '王%';
--2.7在SC中检索成绩为空值的学生学号和课程号;
SELECT SNO,CNO
FROM SC
WHERE GRADE IS NULL;
--2.8求年龄大于女同学平均年龄的男学生的姓名和年龄;
SELECT SNAME, SAGE
FROM Student
WHERE Ssex = '男' AND Sage > (
SELECT AVG(Sage)
FROM Student
WHERE Ssex = '女');
--2.9求年龄大于所有女同学年龄的男学生的姓名和年龄;
SELECT SNAME, SAGE
FROM Student
WHERE Ssex = '男' AND Sage > ALL(
SELECT Sage
FROM Student
WHERE Ssex = '女');
--2.10检索所有比“王华”年龄大的学生姓名、年龄和性别;
SELECT SNAME, SAGE, SSEX
FROM STUDENT
WHERE Sage >
(SELECT Sage
FROM Student
WHERE Sname = '王华');
--2.11检索选修2号课程的学生中成绩最高的学生的学号;
SELECT SNO
FROM SC
WHERE CNO=2
AND GRADE=(
SELECT MAX(GRADE)
FROM SC
WHERE CNO=2);
--2.12检索学生姓名及其所选修课程的课程号和成绩;
SELECT SNAME,CNO,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO;
--2.13检索选修4门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。
SELECT SUM(GRADE) '总成绩'
FROM SC
WHERE GRADE>=60
GROUP BY SNO
HAVING COUNT(CNO)>=4
ORDER BY '总成绩' DESC;
--3.1查询选修课程“8105”且成绩在80到90之间的所有记录;
SELECT *
FROM SC
WHERE CNO='8105'
AND GRADE between 80 and 90;
--3.2查询成绩为79,89或99的记录;
SELECT *
FROM SC
WHERE GRADE IN(79,89,99);
--3.3查询9803班的学生人数;
SELECT COUNT(*) '人数'
FROM STUDENT
WHERE CLASS='9803'
--3.4查询至少有20名学生选修的并以8开头的课程的平均成绩:
SELECT CNO,AVG(GRADE)AS 平均成绩
FROM SC
WHERE CNO LIKE '8%'
GROUP BY CNO
HAVING COUNT( DISTINCT SNO)>=20;
--3.5查询最低分大于80,最高分小于95的SNO与平均分;
select SNO,avg(GRADE) 平均成绩
from SC
group by SNO
having min(SC.GRADE) > 80 and max(SC.GRADE) <= 95
--3.6查询9803班学生所选各课程的课程号及其平均成绩:
select sc.sno, SC.CNO,avg(GRADE) as 平均成绩
from SC,STUDENT
where Student.CLASS = '9803'AND SC.SNO=STUDENT.SNO
group by SC.CNO,sc.sno
--3.7查询选修“8105”课程的成绩高于“9809”号同学成绩的所有同学的记录;
select STUDENT.*
from student,sc
where sc.sno=student.sno
and sc.cno='8105'
and grade >(
select grade
from sc
where sno='9809');
--3.8查询与学号为“9808”的同学同岁的所有学生的SNO、SNAME和AGE;
select SNO,SNAME,AGE
FROM STUDENT
WHERE AGE IN(
SELECT AGE
FROM STUDENT
WHERE STUDENT.SNO='9809');
--3.9查询“钱军”教师任课的课程号,选修其课程学生的学号和成绩;
select SC.*
from Course,TEACHER,SC
WHERE Teacher.TNO = Course.TNO
AND SC.CNO = Course.CNO
AND Teacher.TNAME = '钱军'
--3.10查询选修某课程的学生人数多于20人的教师姓名;
select distinct Teacher.TNAME
from Teacher,COURSE,SC
WHERE Course.TNO = Teacher.TNO
AND Course.CNO = SC.CNO
AND SC.CNO in(
select SC.CNO
from SC
group by SC.CNO
having count(*) > 20);
--3.11查询同学选修编号为“8105”课程且成绩至少高于其选修编号为“8245"课程的同学的SNO及“8105”课程成绩，并按成绩从高到低次序排列;
select SC.SNO,SC.GRADE
from SC
where SC.CNO = '8105'
and SC.GRADE > (
select GRADE
from SC as S2
where S2.SNO = SC.SNO
and S2.CNO = '8245')
order by SC.GRADE desc;
--3.12查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE;
select CNO,SC.SNO,GRADE
from SC
inner join Student
on Student.SNO = SC.SNO
where SC.CNO = '8105'
and SC.GRADE > (
select max(SC.GRADE)
from SC
where SC.CNO = '8245');
--3.13列出所有教师和同学的姓名、SEX、AGE;
select Student.SNAME as 姓名,Student.SEX as 性别,Student.AGE as 年龄
from Student
union
select Teacher.TNAME as 姓名,Teacher.SEX as 性别,Teacher.AGE as 年龄
from Teacher;
--3.14查询成绩比该课程平均成绩高的学生的成绩表;
select *
from sc a
where a.grade not between 0 and (
select avg(grade)
from sc b
where a.cno=b.cno);
--3.15列出所有任课教师的TNAME和DEPT;
select distinct Teacher.TNAME,Teacher.DEPT
from Teacher,Course
where Course.TNO = Teacher.TNO
and Course.TNO in (
select distinct Course.TNO
from Course);
--3.16列出所有未讲课教师的TNAME和DEPT;
select TNAME,DEPT
from Teacher
where Teacher.TNO not in (
select Course.TNO
from Course);
--3.17列出至少有4名男生的班号;
select Student.CLASS
from Student
where SEX = '男'
group by Student.CLASS
having count(*) >= 4;
--3.18查询不姓“张"的学生的记录;
select *
from Student
where SNAME not like '张%'
--3.19查询每门课最高分的学生的SNO、CNO、GRADE;
select SNO,s1.CNO,GRADE
from SC as s1
where s1.GRADE IN (
select max(s2.GRADE)
from SC as s2
where s1.CNO = s2.CNO);
--3.20查询与“李华”同性别并同班的同学的SNAME;
select SNAME
from Student
where Student.SEX = (
select Student.SEX
from Student
where Student.SNAME = '李华')
and Student.CLASS = (
select Student.CLASS
from Student
where Student.SNAME = '李华');
--3.21查询“女”教师及其所上的课程;
select Teacher.TNAME,Course.CNO,Course.CNAME
from Teacher
inner join Course
on Course.TNO = Teacher.TNO
where Teacher.SEX = '女';
--3.22查询选修“数据库系统”课程的“男”同学的成绩表;
select distinct SC.SNO,Student.SNAME,Student.SEX,Course.CNAME,SC.GRADE
from SC
inner join Student on Student.SNO = SC.SNO
inner join Course on Course.CNO = SC.CNO
where Student.SEX = '男'
and Course.CNAME = '数据库系统';
--3.23查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄;
select Teacher.TNAME,AGE as 年龄
from Teacher
where AGE > (
select AGE
from Teacher
where Teacher.TNAME = '刘涛')
or Teacher.TNAME = '刘涛';
--3.24查询不讲授“8104”号课程的教师姓名。
select TNAME
from Teacher
where TNAME not in(
select TNAME
from Teacher
join Course
on Course.TNO = Teacher.TNO
where Course.CNO = '8104');
四、实验小结
今天，我们进行了数据库的SQL语言-SELECT查询操作实验，主要学习了如何使用SELECT语句从数据库中查询数据。在实验过程中，我们实践了简单的SELECT查询、带有WHERE子句的查询以及使用AND和OR运算符的组合查询。
实验中，我们首先创建了一个简单的数据库表，并向其中插入了一些数据。接着，使用SELECT语句来查询表中的数据。刚开始，我们执行了一些简单的查询操作，例如查询所有列或特定列的数据。之后，我们学习了如何使用WHERE子句来筛选数据，包括使用比较运算符（如=、>、<等）和逻辑运算符（如AND和OR）。
通过本次实验，我们掌握了SQL语言的基本查询功能，了解了如何通过SELECT语句从数据库中检索数据。同时，我们也认识到了实践操作的重要性，只有在实践中不断练习和总结，才能更好地掌握这些技能。
浙公网安备 33010602011771号