要求按要求形成一段完整的代码不用分开

以下是根据实验要求编写的完整T-SQL代码：

-- 实验一 T-SQL数据操作

-- 创建数据库
CREATE DATABASE StudentScore;
GO

-- 使用数据库
USE StudentScore;
GO

-- 创建Student表
CREATE TABLE Student (
    Sno CHAR(7) PRIMARY KEY,
    Sname NVARCHAR(10) NOT NULL,
    Ssex CHAR(2) CHECK (Ssex IN ('男', '女')),
    Sage INT CHECK (Sage BETWEEN 15 AND 45),
    Sdept NVARCHAR(20) DEFAULT '计算机系'
);
GO

-- 创建Course表
CREATE TABLE Course (
    Cno CHAR(10) PRIMARY KEY,
    Cname NVARCHAR(20) NOT NULL,
    Ccredit INT CHECK (Ccredit IN (1, 2, 3, 4))
);
GO

-- 创建Sc表
CREATE TABLE Sc (
    Sno CHAR(7),
    Cno CHAR(10),
    Grade INT CHECK (Grade BETWEEN 0 AND 100),
    CONSTRAINT PK_Sc PRIMARY KEY (Sno, Cno),
    CONSTRAINT FK_Sc_Student FOREIGN KEY (Sno) REFERENCES Student(Sno) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT FK_Sc_Course FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE CASCADE
);
GO

-- 创建规则对象
CREATE RULE AgeRule AS @age BETWEEN 15 AND 45;
EXEC sp_bindrule 'AgeRule', 'Student.Sage';
GO

-- 创建默认值对象
CREATE DEFAULT DefaultDept AS '计算机系';
EXEC sp_bindefault 'DefaultDept', 'Student.Sdept';
GO

-- 插入示例数据
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES
('2021001', '张三', '男', 20, '计算机系'),
('2021002', '李四', '女', 22, '计算机系'),
('2021003', '王五', '男', 21, '数学系');

INSERT INTO Course (Cno, Cname, Ccredit) VALUES
('C001', '数据库', 3),
('C002', '操作系统', 4),
('C003', '网络', 2);

INSERT INTO Sc (Sno, Cno, Grade) VALUES
('2021001', 'C001', 85),
('2021001', 'C002', 90),
('2021002', 'C001', 78),
('2021003', 'C003', 80);
GO

-- 1. 将STUDENT表中计算机系的学生信息放入一张名为JSJSTUDENT的新表中
SELECT Sno, Sname, Ssex, Sage INTO JSJSTUDENT FROM Student WHERE Sdept = '计算机系';
GO

-- 2. 查询JSJSTUDENT表，将年龄最大的3位学生的信息放入一张本地临时表中
SELECT TOP 3 * INTO #TempJSJSTUDENT FROM JSJSTUDENT ORDER BY Sage DESC;
SELECT * FROM #TempJSJSTUDENT;
DROP TABLE #TempJSJSTUDENT;
GO

-- 3. 查询计算机系学生选修课程的信息
-- 显式内连接
SELECT s.Sno, s.Sname, s.Ssex, 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 = '计算机系';

-- 派生表查询
SELECT t1.Sno, t1.Sname, t1.Ssex, t1.Cname, t1.Grade 
FROM (
    SELECT s.Sno, s.Sname, s.Ssex, 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 
) t1 
WHERE t1.Sdept = '计算机系';
GO

-- 4. 查询没有选修课程的学生学号，姓名和所在系
SELECT s.Sno, s.Sname, s.Sdept 
FROM Student s 
LEFT JOIN Sc sc ON s.Sno = sc.Sno 
WHERE sc.Sno IS NULL;
GO

-- 5. 查询有哪些课程没有人选修
-- 外连接
SELECT c.Cno, c.Cname, c.Ccredit 
FROM Course c 
LEFT JOIN Sc sc ON c.Cno = sc.Cno 
WHERE sc.Cno IS NULL;

-- 嵌套查询
SELECT Cno, Cname, Ccredit 
FROM Course 
WHERE Cno NOT IN (SELECT DISTINCT Cno FROM Sc);
GO

-- 6. 统计每名学生选修课程的门数和平均分，显示学生学号，姓名，所在系和选课门数及课程平均分
SELECT s.Sno, s.Sname, s.Sdept, COUNT(sc.Cno) AS CourseCount, AVG(sc.Grade) AS AvgGrade 
FROM Student s 
LEFT JOIN Sc sc ON s.Sno = sc.Sno 
GROUP BY s.Sno, s.Sname, s.Sdept 
ORDER BY CourseCount DESC 
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
GO

-- 7. 统计除2号课程外的其它课程的选课人数和平均分，显示课程号，课程名，学分，选课人数及平均分
SELECT c.Cno, c.Cname, c.Ccredit, COUNT(sc.Sno) AS StudentCount, AVG(sc.Grade) AS AvgGrade 
FROM Course c 
LEFT JOIN Sc sc ON c.Cno = sc.Cno 
WHERE c.Cno != 'C002' 
GROUP BY c.Cno, c.Cname, c.Ccredit 
UNION ALL
SELECT 'C002', '操作系统', 4, 0, 0;
GO

-- 8. 查询选修每门课程的男女生人数和平均分
SELECT c.Cno, s.Ssex, COUNT(sc.Sno) AS StudentCount, AVG(sc.Grade) AS AvgGrade 
FROM Course c 
INNER JOIN Sc sc ON c.Cno = sc.Cno 
INNER JOIN Student s ON sc.Sno = s.Sno 
GROUP BY c.Cno, s.Ssex WITH ROLLUP;
GO

-- 9. 查询所有学生的信息，结果统计总人数和平均年龄
SELECT *, COUNT(*) OVER () AS TotalStudents, AVG(Sage) OVER () AS AvgAge 
FROM Student;
GO

-- 10. 查询每个学生选修课程的信息，并统计每个学生选修的课程数和平均分
SELECT s.Sno, s.Sname, c.Cno, sc.Grade, COUNT(c.Cno) OVER (PARTITION BY s.Sno) AS CourseCount, AVG(sc.Grade) OVER (PARTITION BY s.Sno) AS AvgGrade 
FROM Student s 
LEFT JOIN Sc sc ON s.Sno = sc.Sno 
LEFT JOIN Course c ON sc.Cno = c.Cno;
GO

-- 11. 查询选修了 “数据库”课程的计算机系学生信息，显示学生学号，姓名和成绩
SELECT s.Sno, s.Sname, sc.Grade, 
       CASE 
           WHEN sc.Grade >= 90 THEN '优秀'
           WHEN sc.Grade >= 80 THEN '良好'
           WHEN sc.Grade >= 70 THEN '中等'
           WHEN sc.Grade >= 60 THEN '及格'
           ELSE '不及格'
       END AS GradeLevel
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 s.Sdept = '计算机系';
GO

-- 12. 将计算机系学生的所有成绩都降低5分
UPDATE Sc 
SET Grade = Grade - 5 
FROM Sc sc 
INNER JOIN Student s ON sc.Sno = s.Sno 
WHERE s.Sdept = '计算机系';

-- 使用子查询
UPDATE Sc 
SET Grade = Grade - 5 
WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = '计算机系');
GO

-- 13. 删除计算机系成绩不及格学生的选课记录
DELETE Sc 
FROM Sc sc 
INNER JOIN Student s ON sc.Sno = s.Sno 
WHERE s.Sdept = '计算机系' AND sc.Grade < 60;

-- 使用子查询
DELETE Sc 
WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = '计算机系') 
AND Grade < 60;
GO

这段代码涵盖了实验要求的所有操作，包括创建数据库、表、插入示例数据以及执行各种查询和更新操作。请在SQL Server Management Studio (SSMS) 中运行这些代码，并根据需要调整示例数据。