数据库 实验三 SQL数据定义及更新

1. 使用实验一建立的三张数据表，录入若干数据。

2. 增加一个地区表area(id,pid,name)分别表示行政区ID，上级行政区ID，名称。输入若干省、市、县、乡。

建立地区表area(id,pid,name)

CREATE TABLE area (
    id INT PRIMARY KEY,
    pid INT,
    name NVARCHAR(255)
);

-- 插入省级行政区域数据

INSERT INTO area (id, pid, name) VALUES (1, NULL, '某某省');
INSERT INTO area (id, pid, name) VALUES (2, NULL, '某某省');
INSERT INTO area (id, pid, name) VALUES (3, NULL, '某某省');

-- 插入市级行政区域数据

INSERT INTO area (id, pid, name) VALUES (11, 1, '某某市');
INSERT INTO area (id, pid, name) VALUES (12, 1, '某某市');
INSERT INTO area (id, pid, name) VALUES (13, 1, '某某市');
INSERT INTO area (id, pid, name) VALUES (22, 2, '某某市');
INSERT INTO area (id, pid, name) VALUES (23, 2, '某某市');
INSERT INTO area (id, pid, name) VALUES (24, 2, '某某市');
INSERT INTO area (id, pid, name) VALUES (31, 3, '某某市');
INSERT INTO area (id, pid, name) VALUES (32, 3, '某某市');
INSERT INTO area (id, pid, name) VALUES (33, 3, '某某市');

-- 插入县级行政区域数据

INSERT INTO area (id, pid, name) VALUES (111, 11, '某某县');
INSERT INTO area (id, pid, name) VALUES (112, 11, '某某县');
INSERT INTO area (id, pid, name) VALUES (121, 12, '某某县');
INSERT INTO area (id, pid, name) VALUES (122, 12, '某某县');
INSERT INTO area (id, pid, name) VALUES (131, 13, '某某县');
INSERT INTO area (id, pid, name) VALUES (132, 13, '某某县');
INSERT INTO area (id, pid, name) VALUES (221, 22, '某某县');
INSERT INTO area (id, pid, name) VALUES (222, 22, '某某县');
INSERT INTO area (id, pid, name) VALUES (231, 23, '某某县');
INSERT INTO area (id, pid, name) VALUES (232, 23, '某某县');

5. 检索选修人数最多的课程

SELECT * FROM Course WHERE Cno=(SELECT Top 1 cno FROM SC GROUP BY cno ORDER BY COUNT(*) DESC);

6. 检索平均分最高的学生

SELECT TOP 1 Sno 学号, AVG(Grade)平均值 FROM SC GROUP BY Sno

7. 检索每门课程的最高分

SELECT Cname 课程名,C.Cno 课程ID,MAX(Grade) 最高分 FROM SC SC,Course C WHERE SC.Cno=C.Cno GROUP BY C.Cno,Cname ORDER BY MAX(Grade) DESC;

8. 查询每位同学高于自己平均分的课程。

SELECT * FROM SC S1 WHERE Grade>=(SELECT AVG(Grade) FROM SC S2 WHERE S1.Sno=S2.Sno);

9.. 查询全部同学都选修了的课程。

SELECT CName FROM Course WHERE Cname IN (SELECT Cname FROM SC GROUP BY Sno HAVING COUNT(DISTINCT Cno) = (SELECT COUNT(*) FROM Student)

10. 查询至少选修了学号为“´´´´´´´”同学选修全部课程的学生。

SELECT DISTINCT Sno 学号 FROM SC scx 
WHERE NOT EXISTS(SELECT * FROM SC scy WHERE scy.Sno = '202110810' AND NOT EXISTS(SELECT * FROM SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));

11. 用递归查询某个省的所有下级结点。查询某个乡的所有上级节点。

查询上节点

DECLARE @ProvinceName NVARCHAR(255) = '某某省';
WITH RecursiveCTE AS (
    SELECT id, name, pid
    FROM area
    WHERE area.name = @ProvinceName
    UNION ALL
    SELECT R.id, R.name, R.Pid
    FROM area R
    INNER JOIN RecursiveCTE CTE ON R.id = CTE.id
)
SELECT PID,ID 
FROM RecursiveCTE

查询下节点

DECLARE @TownName NVARCHAR(111) = '某某县';
WITH RecursiveCTE AS (
    SELECT id, name, Pid
    FROM area
    WHERE area.name = @TownName
    UNION ALL
    SELECT R.id, R.name, R.pid
    FROM area R
    INNER JOIN RecursiveCTE CTE ON R.id = R.pid
)
SELECT ID, PID
FROM RecursiveCTE