z-notes

SQL SERVER-练习题目1

一、题目素材

1.基础表结构

-- 学生表
CREATE TABLE Student(
    SId VARCHAR(10) PRIMARY KEY,
    Sname VARCHAR(10) NOT NULL,
    Sage DATE NOT NULL,
    Seex CHAR(2) NOT NULL
);
INSERT INTO Student VALUES('01','张三','2000-01-01','男'),('02','李四','2001-05-12','女'),('03','王五','1999-08-20','男');

-- 课程表
CREATE TABLE Course(
    CId VARCHAR(10) PRIMARY KEY,
    Cname VARCHAR(20) NOT NULL,
    TId VARCHAR(10) NOT NULL
);

INSERT INTO Course VALUES('3-105','数学','T01'),('3-245','语文','T02'),('6-166','英语','T03');

-- 成绩表
CREATE TABLE SC(
    SId VARCHAR(10) REFERENCES Student(SId),
    CId VARCHAR(10) REFERENCES Course(CId),
    Score DECIMAL(5,1) NOT NULL,
    PRIMARY KEY(SId,CId)
);
INSERT INTO SC VALUES ('01', '3-105', 85.5),('01', '3-245', 72.0),('02', '3-105', 90.0),('03', '6-166', 88.5);

-- 教师表（Teacher）
CREATE TABLE Teacher (
    TId VARCHAR(10) PRIMARY KEY,
    Tname VARCHAR(10) NOT NULL,
    Depart VARCHAR(20) NOT NULL
);
INSERT INTO Teacher VALUES ('T01', '赵老师', '数学系'),('T02', '钱老师', '中文系');

二、经典习题与要求

1.基础查询

–题目1：查询所有学生的学号、姓名及其年龄（精确到年）。 – 题目2：查询成绩表中分数在 80 到 90 分之间的记录，按分数降序排列。

2.多表关联查询

–题目3：查询选修了“数学”课程的学生姓名及成绩。 – 题目4：查询每位教师的姓名及其所授课程的名称。

3. ‌聚合与分组

–题目5‌：统计每门课程的平均分，并显示课程名和平均分（保留一位小数）。 – 题目6‌：查询选修超过 2 门课程的学生学号及选课数量。

4.高级查询

–题目7：查询每科成绩的前两名学生（使用窗口函数 ROW_NUMBER()）。 – 题目8：查询“数学”课程成绩高于该课程平均分的学生信息。

5.数据操作

–题目9：将学号为 03 的学生年龄修改为 25 岁。 – 题目10：删除成绩表中分数低于 60 分的记录

三、参考题目

1.基础查询答案

-- 题目1、查询所有学生的学号、姓名及其年龄（精确到年）。
select SId as '学号',Sname as '姓名',DATEDIFF(YEAR,Sage,GETDATE()) AS '年龄' FROM Stuedent;

优化写法：
SELECT 
    SId AS 学号,
    Sname AS 姓名,
    DATEDIFF(YEAR, Sage, GETDATE()) - 
        CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, Sage, GETDATE()), Sage) > GETDATE() 
             THEN 1 
             ELSE 0 
        END AS 年龄
FROM Student;

-- 题目2、查询成绩表中分数在 80 到 90 分之间的记录，按分数降序排列。
select * from SC where Score between 80 and 90 order by Score desc;

优化写法：没有

-- 题目3、查询选修了“数学”课程的学生姓名及成绩。
select Student.Sname as '学生姓名',Sc.Score as '成绩' from Student left outer join Sc on Student.SId = Sc.SId where Cid = (select CId from Course where Cname = '数学');

优化写法：
select S.Sname,SC.score from SC JOIN course C on C.Cid = SC.Cid and C.Cname = '数学' Join Student S on S.SId = SC.SId;

-- 题目4、查询每位教师的姓名及其所授课程的名称。
select Teacher.Tname as '教师姓名',Course.Cname as '课程' from teacher left outer join Course on Teacher.Tid = Course.Tid;

优化写法：
select T.Tname as '教师姓名',COALESCE(C.Cname,'无授课') as '课程' from teacher T left outer join Course C on T.Tid = C.Tid;

-- 题目5、统计每门课程的平均分，并显示课程名和平均分（保留一位小数）。
Select Course.Cname as '课程',CONVERT(DECIMAL(5,1),AVG(SC.Score)) as '平均分' from Course left outer join SC on Course.Cid = SC.CId GROUP BY Course.Cname;

优化写法：没有
select SId as '学生学号',count(CId) as '课程数量' FROM SC GROUP BY Sid having count(CId) > 2;

-- 题目6、查询选修超过 2 门课程的学生学号及选课数量。
Select Student.SId as '学生学号',count(SC.CId) as '课程数量' from Student left outer join SC on Student.SId = SC.SId GROUP BY Student.SId having count(SC.CId) > 2 ; 

优化写法：select SId as '学生学号',count(CId) as '课程数量' FROM SC GROUP BY Sid having count(CId) >= 2;

-- 题目7、查询每科成绩的前两名学生（使用窗口函数 `ROW_NUMBER()`）。
select L.Sname,Course.Cname,L.Score FROM Course left outer join (select Student.Sname,B.Cid,B.Score FROM Student left outer JOIN (select CId,Sid,Score FROM (select *,ROW_NUMBER() OVER (PARTITION BY Cid ORDER BY Score Desc) as RANK FROM SC) as T where RANK <= 2) as B on Student.Sid = B.Sid) AS L on Course.Cid = L.Cid;

WITH RankedScores AS (
    SELECT 
        S.Sname,
        C.Cname,
        SC.Score,
        ROW_NUMBER() OVER (PARTITION BY SC.CId ORDER BY SC.Score DESC) AS RN
    FROM SC
    JOIN Student S ON SC.SId = S.SId
    JOIN Course C ON SC.CId = C.CId
)
SELECT Sname, Cname, Score
FROM RankedScores
WHERE RN <= 2;

-- 题目8、查询“数学”课程成绩高于该课程平均分的学生信息。
select A.SId,A.CId,A.Score FROM (select SC.SId,Course.CId,SC.Score FROM SC LEFT OUTER JOIN course on SC.Cid = course.CId) AS A Left outer Join (select Course.Cname,Course.Cid,T.Score FROM Course left outer join (select SC.CId,AVG(SC.Score) as Score FROM SC GROUP BY SC.CId) as T on Course.CId = T.CId where Course.Cname= '数学') AS B ON A.CId = B.CId where A.Score > B.Score;

优化写法：
select S.SId,S.Sname,SC.Score,SC.CId FROM Student S Join SC ON S.SId = SC.SId Join Course C ON SC.CId = C.CId where C.Cname='数学' AND SC.Score > (SELECT AVG(SC.Score) FROM SC JOIN course ON SC.CId = course.CId WHERE COURSE.CNAME='数学');

--题目9、将学号为 `03` 的学生年龄修改为 `25` 岁。
UPDATE STUDENT SET SAGE = DATEADD(YEAR,-25,GETDATE()) WHERE SID = '03';

--题目10、删除成绩表中分数低于 60 分的记录
delete from SC where score < 60;