Select a result to preview

数据库例题 - 期末

数据库及表的创建

用SQL创建符合如下条件的数据库：数据库名为：StudentsDB

选用已建立的“StudentsDB”，写出创建满足表C-1到C-3条件的表的SQL语句，并执行所写代码

表C-1 Student表结构

列名 说明 数据类型 约束
Sno 学号 普通编码定长字符串，长度为7 主键
Sname 姓名 普通编码定长字符串，长度为10 非空
Ssex 性别 普通编码定长字符串，长度为2 取值范围：
Sage 年龄 微整型（tinyint） 取值范围：15 - 45
Sdept 所在系 普通编码不定长字符串，长度为20 默认值为“计算机系”
Sid 身份证号 普通编码定长字符串，长度为 取值不重
Sdate 入学日期 小日期时间 默认为系统当前日期 
CREATE TABLE Student (
	Sno		CHAR(7)		PRIMARY KEY,
    Sname	CHAR(10)	NOT NULL,
    Ssex	CHAR(2)		CHECK (Ssex IN ('男','女')),
    Sage	TINYINT		CHECK (Sage BETWEEN 15 AND 45),
    Sdept	VARCHAR(20)	DEFAULT '计算机系',
    Sid		CHAR(10)	UNIQUE,
    Sdate	SMALLDATETIME	DEFAULT GETDATE()
);

表C-2 Course表结构

列名 说明 数据类型 约束
Cno 课程号 普通编码定长字符串，长度为10 主键
Cname 课程名 普通编码不定长字符串，长度为20 非空
Credit 学时数 整型 取值大于0
Semester 学分 小整型 
CREATE TABLE Course (
	Cno			CHAR(10)		PRIMARY KEY,
    Cname		VARCHAR(20)		NOT NULL,
    Credit		INT				CHECK (Credit > 0)
    Semester	SMALLINT
);

表C-3 SC表结构

列名 说明 数据类型 约束
Sno 学号 普通编码定长字符串，长度为7 主键，引用Student的外键
Cno 课程号 普通编码定长字符串，长度为10 主键，引用Course的外键
Grade 成绩 小整型 取值范围为0-100 
CREATE TABLE SC (
    Sno		CHAR(7),
    Cno		CHAR(10),
    Grade	SMALLINT CHECK (Grade BETWEEN 0 AND 100),
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

单表查询

查询缺考的学生的学号和课程号

SELECT Sno, Cno
FROM SC
WHERE Grade IS NULL;

查询年龄为空值的学生的学号和姓名

SELECT Sno, Sname
FROM Student
WHERE Sage IS NULL;

查询计算机系 20 岁以下的学生学号和姓名

WHERE Sdept = '计算机系' AND Sage < 20;

查询计算机系、数学系、信息系的学生姓名、性别

SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('计算机系', '数学系', '信息系');

查询选修 C001 课程的学生的学号和成绩，按分数降序

SELECT Sno, Grade
FROM SC
WHERE Cno = 'C001'
ORDER BY Grade DESC;

查询选修 C001 课程的所有学生的总成绩

SELECT SUM(Grade) AS TotalGrade
FROM SC
WHERE Cno = 'C001';

查询 C001 课程的最高分和最低分

SELECT 
    MAX(Grade) AS MaxGrade,
    MIN(Grade) AS MinGrade
FROM SC
WHERE Cno = 'C001';

查询全体学生，按系升序，同系按年龄降序

SELECT *
FROM Student
ORDER BY Sdept ASC, Sage DESC;

查询学生总人数

SELECT COUNT(*) AS StudentCount
FROM Student;

查询计算机系的男学生总人数

SELECT COUNT(*) AS MaleCSCount
FROM Student
WHERE Sdept = '计算机系' AND Ssex = '男';

查询选修了课程的学生人数（去重）

SELECT COUNT(DISTINCT Sno) AS StudentWithCourse
FROM SC;

计算选修 C001 课程的学生平均成绩

SELECT AVG(Grade) AS AvgGrade
FROM SC
WHERE Cno = 'C001'

查询各课程及选课人数

SELECT Cno, COUNT(*) AS StudentCount
FROM SC
GROUP BY Cno;

多表查询

（1）查询每个选修了课程的学生学号、姓名、所在系及其选修课程编号。

SELECT s.Sno, s.Sname, s.Sdept, sc.Cno
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno;

（2）查询选修了C002课程且成绩在60分以上的所有学生的学号和姓名。

SELECT s.Sno, s.Sname
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
WHERE sc.Cno = 'C002' AND sc.Grade > 60;

（3）查询每个学生选修的课程名及其成绩

SELECT s.Sname, c.Cname, sc.Grade
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
INNER JOIN Course c ON sc.Cno = c.Cno;

（4）查询“计算机系”每个学生的学号、姓名、选修课程的课程名及成绩

SELECT s.Sno, s.Sname, c.Cname, sc.Grade
FROM Student s
LEFT JOIN SC sc ON s.Sno = sc.Sno
LEFT JOIN Course c ON sc.Cno = c.Cno
WHERE s.Sdept = '计算机系';

（5）统计每一年龄选修课程的学生人数。

SELECT s.Sage, COUNT(DISTINCT s.Sno) AS StudentCount
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
GROUP BY s.Sage;

（6）查询选了C002课程的学生姓名和所在系。

SELECT s.Sname, s.Sdept
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
WHERE sc.Cno = 'C002';

（7）查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。

SELECT s.Sname, sc.Cno, sc.Grade
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
WHERE sc.Grade > 80
ORDER BY sc.Grade DESC;

（8）查询与“数据结构”在同一学期开设的课程的课程名和开课学期。

SELECT Cname, Semester
FROM Course
WHERE Semester = (
    SELECT Semester
    FROM Course 
    WHERE Cname = '数据结构')
  AND Cname != '数据结构';

（9）查询与李勇年龄相同的学生的姓名、所在系和年龄。

SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage = (SELECT Sage FROM Student WHERE Sname = '李勇')
  AND Sname != '李勇';

（10）查询哪些课程没有学生选修，列出课程号和课程名。

SELECT c.Cno, c.Cname
FROM Course c
LEFT JOIN SC sc ON c.Cno = sc.Cno
WHERE sc.Cno IS NULL;

（11）查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。

SELECT s.Sno, s.Sname, sc.Cno
FROM Student s
LEFT JOIN SC sc ON s.Sno = sc.Sno;

（12）查询计算机系哪些学生没有选课，列出学生姓名。

SELECT s.Sname
FROM Student s
LEFT JOIN SC sc ON s.Sno = sc.Sno
WHERE s.Sdept = '计算机系' AND sc.Sno IS NULL;

嵌套查询

（1）查询选修了"C001"课程的学生姓名。

SELECT DISTINCT s.Sname
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
WHERE sc.Cno = 'C001';

（2）查询与"李勇"在同一个系学习的学生姓名，系别和性别。

SELECT Sname, Sdept, Ssex
FROM Student
WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = '李勇')
  AND Sname != '李勇';

（3）查询选修课程名为"高等数学"的学生学号和姓名。

SELECT s.Sno, s.Sname
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
INNER JOIN Course c ON sc.Cno = c.Cno
WHERE c.Cname = '高等数学';

（4）与第2题相同，查询与"李勇"在同一个系学习的学生姓名，系别和性别。

（5）与第3题相同，查询选修课程名为"高等数学"的学生学号和姓名。

（6）查询计算机系成绩80分以上的学生的学号和姓名。

SELECT s.Sno, S.Sname
FROM Student s
JOIN SC sc on s.Sno = sc.Sno
WHERE s.Sdept = '计算机系' AND sc.Grade > 80;

（7）查询计算机系考试成绩最高的学生的姓名。

-- 方法1：使用子查询
SELECT TOP 1 s.Sname
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
WHERE s.Sdept = '计算机系'
ORDER BY sc.Grade DESC;

-- 方法2：使用MAX函数
SELECT s.Sname
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
WHERE s.Sdept = '计算机系' 
  AND sc.Grade = (
    SELECT MAX(Grade) 
    FROM SC 
    WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = '计算机系')
  );

（8）查询年龄最大的男学生的姓名和年龄。

-- 方法1
SELECT TOP 1 Sname, Sage
FROM Student
WHERE Ssex = '男'
ORDER BY Sage DESC;

-- 方法2
SELECT Sname, Sage
FROM Student
WHERE Ssex = '男' 
  AND Sage = (SELECT MAX(Sage) FROM Student WHERE Ssex = '男');

（9）查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。

SELECT sc.Sno, sc.Grade
FROM SC sc
WHERE sc.Cno = 'C001' 
  AND sc.Grade > (SELECT AVG(Grade) FROM SC WHERE Cno = 'C001')
ORDER BY sc.Grade DESC;

（10）查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。

SELECT s.Sname, c.Cname, sc.Grade
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
INNER JOIN Course c ON sc.Cno = c.Cno
WHERE s.Sdept = '计算机系'
  AND sc.Grade > (
    SELECT AVG(sc2.Grade)
    FROM Student s2
    INNER JOIN SC sc2 ON s2.Sno = sc2.Sno
    WHERE s2.Sdept = '计算机系'
  )
ORDER BY sc.Grade DESC;

（11）查询"高等数学"课程考试成绩高于"高等数学"平均成绩的学生姓名和"高等数学"成绩。

SELECT s.Sname, sc.Grade
FROM Student s
INNER JOIN SC sc ON s.Sno = sc.Sno
INNER JOIN Course c ON sc.Cno = c.Cno
WHERE c.Cname = '高等数学'
  AND sc.Grade > (
    SELECT AVG(sc2.Grade)
    FROM SC sc2
    INNER JOIN Course c2 ON sc2.Cno = c2.Cno
    WHERE c2.Cname = '高等数学'
  )
ORDER BY sc.Grade DESC;

（12）查询没选"高等数学"的学生姓名和所在系。

-- 方法1：使用NOT EXISTS
SELECT s.Sname, s.Sdept
FROM Student s
WHERE NOT EXISTS (
    SELECT 1
    FROM SC sc
    INNER JOIN Course c ON sc.Cno = c.Cno
    WHERE sc.Sno = s.Sno AND c.Cname = '高等数学'
);

-- 方法2：使用NOT IN
SELECT Sname, Sdept
FROM Student
WHERE Sno NOT IN (
    SELECT sc.Sno
    FROM SC sc
    INNER JOIN Course c ON sc.Cno = c.Cno
    WHERE c.Cname = '高等数学'
);

规范化理论

查询至少选修了1门课程的学生的姓名（用相关子查询实现）

SELECT Sname
FROM Student
WHERE Sno IN (
	SELECT DISTINCT sc.Sno
	FROM Sc sc
)

查询没有选修任何课程的学生的学号和姓名

SELECT Sno,Sname
FROM Student
WHERE Sno NOT IN (
	SELECT DISTINCT Sno
    FROM SC
);

查询选修了"数据库原理"课程的学生的平均成绩

SELECT AVG(sc.Grade) AS 数据库原理平均分
FROM SC sc
INNER JOIN Course c ON sc.Cno = c.Cno
WHERE c.Cname = '数据库原理';

查询平均成绩高于全体学生总平均成绩的学生学号和平均分，并按平均分降序排列

SELECT s.Sno, AVG(sc.Grade) AS 平均分
FROM Student s
INNER JOIN SC sc ON sc.Sno = s.Sno
GROUP BY s.Sno
HAVING AVG(sc.Grade) > (
	SELECT AVG(Grade)
	FROM SC 
)
ORDER BY AVG(sc.Grade) DESC

查询每个学生取得最高分的那门课程的成绩信息，显示学号、课程号、成绩

SELECT sc1.Sno, sc1.Cno, sc1.Grade
FROM SC sc1
WHERE sc1.Grade = (
	SELECT MAX(sc2.Grade)
	FROM SC sc2
	WHERE sc1.Sno = sc2.Sno
);