增

1.

​x
CREATE TABLE Jobs (
 Jid   char(6) PRIMARY KEY,   --在列级定义主键
Descp  nchar(20) NOT NULL,
 EduReq  nchar(6)  DEFAULT '本科', 
MinSalary  int ,
 MaxSalary  int ,
 CHECK( MaxSalary >= MinSalary )     --多列的CHECK约束必须定义在表级
)

2

xxxxxxxxxx
-- 创建员工表，存储员工基本信息
CREATE TABLE Employees (
    -- 员工ID，10位定长字符，作为主键
    Eid char(10) PRIMARY KEY,  
    
    -- 员工姓名，最多20个字符的Unicode字符串，不允许为空
    Ename nvarchar(20) NOT NULL,
    
    -- 性别，1个字符的Unicode字符串，只能是'男'或'女'
    Sex nchar(1) CHECK (Sex = '男' OR Sex = '女'),
    
    -- 出生日期，日期类型
    BirthDate date,
    
    -- 入职日期，日期时间类型，默认为当前系统时间
    JobDate datetime DEFAULT GETDATE(),
    
    -- 身份证号，18位定长字符
    Sid char(18),
    
    -- 职位ID，10位定长字符（此处缺少外键约束定义）
    Jid char(10),
    
    -- 联系电话，11位定长字符
    Tel char(11)
);

3

xxxxxxxxxx
ALTER TABLE Employees
 ADD Salary INT

4

xxxxxxxxxx
-- 修改Jobs表中Descp列的数据类型为NCHAR(40)
ALTER TABLE Jobs          -- 指定要修改的表名为Jobs
ALTER COLUMN Descp       -- 指定要修改的列名为Descp
NCHAR(40);                -- 将列的数据类型更改为定长Unicode字符串，最大长度40个字符

5

xxxxxxxxxx
ALTER TABLE Jobs
 ADD CHECK( MinSalary >= 3000 )

删

1

xxxxxxxxxx
-- 修改Employees表结构：如果存在Tel列，则删除该列
ALTER TABLE Employees          -- 指定要修改的表名为Employees
DROP COLUMN                   -- 声明要删除列的操作
IF EXISTS Tel;                 -- 检查列是否存在，存在则删除Tel列

2

xxxxxxxxxx
 DROP TABLE Employees

查

1

xxxxxxxxxx
SELECT Sno, Sname FROM Student;

2

xxxxxxxxxx
SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student;=SELECT  *  FROM Student;

3

xxxxxxxxxx
-- 查询学生姓名(Sname)、固定字符串'年份'，以及计算列(2009减去学生年龄Sage)
SELECT 
    Sname,                -- 学生姓名列
    '年份' AS YearLabel,  -- 固定字符串'年份'，并赋予别名YearLabel
    2009 - Sage AS BirthYear  -- 计算列：用2009减去学生年龄(Sage)，推测是推算出生年份，并赋予别名BirthYear
FROM 
    Student;              -- 数据来源：Student表

4

xxxxxxxxxx
SELECT * FROM Student  WHERE Sdept = '计算机系';

5

xxxxxxxxxx
SELECT Sname, Sage FROM Student WHERE Sage < 20

6

xxxxxxxxxx
SELECT DISTINCT Sno  FROM SC WHERE Grade < 60

7

xxxxxxxxxx
select Sname,Sage,Sdept from student where Sage between 20 and 23;

8

xxxxxxxxxx
select Sname,Sage,Sdept from student where Sage not between 20 and 23;=SELECT Sname, Sdept, Sage  FROM Student WHERE Sage <20 OR Sage>23

9

xxxxxxxxxx
SELECT title_id, type, price, pubdate FROM titles 
WHERE pubdate BETWEEN '1991/6/1' AND '1991/6/30'

10

xxxxxxxxxx
select Sname,Ssex from student where Sdept in 
('计算机系' ,'通信工程系','信息管理系')=select Sname,Ssex from student where Sdept='计算机系' or 
Sdept='通信工程系' or Sdept='信息管理系'

11

xxxxxxxxxx
select Sname,Ssex from student where Sdept not in 
('计算机系' ,'通信工程系','信息管理系')=SELECT Sname, Ssex  FROM Student 
WHERE Sdept!= '信息管理系' AND Sdept!= '通信工程系' AND Sdept!= '计算机系'

12

xxxxxxxxxx
select Sname,Sdept from student where Sname like '张%'

13

xxxxxxxxxx
select Sname,Sdept from student where Sname like '[张刘李]%'

14

xxxxxxxxxx
select Sname,Sno from student where Sname like '_[大小]%'

15

xxxxxxxxxx
select * from student where Sname not like '刘%'

16

xxxxxxxxxx
SELECT * FROM Student WHERE Sno  not LIKE '%[235]' 

17

xxxxxxxxxx
WHERE field1 LIKE '%30!%%' ESCAPE '!'

18

xxxxxxxxxx
WHERE field1 LIKE '%!_%' ESCAPE '!

19

xxxxxxxxxx
SELECT Sno, Cno FROM SC WHERE Grade IS NULL

20

xxxxxxxxxx
SELECT * FROM SC WHERE Grade IS not NULL

21

xxxxxxxxxx
SELECT Sname FROM Student WHERE Sdept = '计算机系' AND Sage < 20

22

xxxxxxxxxx
SELECT Sno, Sname, Sdept, Sage 
FROM Student
 WHERE (Sdept = '计算机系' 
OR Sdept = '信息管理系') 
AND Sage between 18 and 20;

23

xxxxxxxxxx
SELECT Sno, Sname, Sdept, Sage
 FROM Student
 WHERE Sdept in( '计算机系', '信息管理系')  
AND Sage between 18 and 20;

24

xxxxxxxxxx
SELECT * FROM Student ORDER BY Sage

25

xxxxxxxxxx
SELECT Sno, Grade FROM SC
 WHERE Cno = 'C002' 
ORDER BY Grade DESC

26

xxxxxxxxxx
SELECT * FROM Student 
ORDER BY Sdept, Sage DESC

27

xxxxxxxxxx
SELECT count(*) FROM Student; 

28

xxxxxxxxxx
SELECT COUNT(DISTINCT Sno) as num FROM SC; 

29

xxxxxxxxxx
SELECT SUM(Grade) FROM SC WHERE Sno = '0811101';

30

xxxxxxxxxx
SELECT MAX(Grade) 最高分, MIN(Grade) 最低分 FROM SC WHERE Cno = 'C001';

31

xxxxxxxxxx
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;

32

xxxxxxxxxx
SELECT Sdept, COUNT(*) AS 学生人数, AVG(Sage) AS 平均年龄 FROM Student GROUP BY 
Sdept

33

xxxxxxxxxx
SELECT Sdept, Ssex, Count(*) 人数, Max(Sage) 最大年龄 FROM Student 
GROUP BY Sdept, Ssex ORDER BY Sdept

34

xxxxxxxxxx
SELECT Sno, Count(*) 选课门数 FROM SC GROUP BY Sno HAVING COUNT(*) > 3

35

xxxxxxxxxx
SELECT Sno, AVG(Grade) 平均成绩, COUNT(*) 选课门数 FROM SC GROUP BY Sno 
HAVING COUNT(*) >= 4

36

xxxxxxxxxx
SELECT sdept, COUNT (*)  FROM Student WHERE Sdept in ( '计算机系', '信息管理系')
 GROUP BY Sdept

37

xxxxxxxxxx
SELECT Sname, Cno, Grade  
FROM Student JOIN SC  
ON Student.Sno = SC.Sno
 WHERE Sdept = '计算机系'

38

xxxxxxxxxx
SELECT Sname, Grade
 FROM  Student s
 JOIN  SC ON s.Sno = SC. Sno
 JOIN  Course c ON c.Cno = SC.Cno
 WHERE Sdept = '信息管理系' 
AND Cname = '计算机文化学'

39

xxxxxxxxxx
SELECT Sname, Sdept FROM Student S 
JOIN SC ON S.Sno = SC. Sno
 JOIN Course C ON C.Cno = SC.cno
 WHERE Cname = 'Java'

40

xxxxxxxxxx
ELECT Sdept, 
AVG(grade) as AverageGrade
 FROM student S JOIN SC 
ON S.Sno = SC.Sno
 GROUP BY Sdept

41

xxxxxxxxxx
SELECT Cno, COUNT(*) AS Total, 
AVG(Grade) as AvgGrade, 
MAX(Grade) as MaxGrade, 
MIN(Grade) as MinGrade
 FROM Student S JOIN SC ON S.Sno = SC.Sno
 WHERE Sdept = '计算机系' 
GROUP BY Cno

42

xxxxxxxxxx
SELECT S2.Sname, S2.Sdept  
FROM Student S1 JOIN Student S2 
ON S1.Sdept = S2.Sdept
 WHERE S1.Sname = '刘晨'
 AND S2.Sname != '刘晨'

43

xxxxxxxxxx
SELECT C1.Cname, C1.Semester  
 FROM Course C1 JOIN Course C2 
 ON C1.Semester = C2.Semester
 WHERE C2.Cname = '数据结构'

44

xxxxxxxxxx
ELECT DISTINCT a.Cno  
FROM SC a JOIN SC b 
ON a.Cno = b.Cno
 AND a.Sno != b.Sno

45

xxxxxxxxxx
-- 查询所有学生的学号、姓名及其选课信息（包括未选课的学生）
SELECT 
    Student.Sno,    -- 学生学号（来自Student表）
    Sname,          -- 学生姓名（来自Student表）
    Cno,            -- 课程编号（来自SC表，可能为NULL）
    Grade           -- 成绩（来自SC表，可能为NULL）
FROM 
    SC              -- 右表：学生选课表（可能包含NULL）
RIGHT JOIN         -- 右连接：以左表(Student)为基准，保留左表所有记录
    Student         -- 左表：学生信息表（基准表）
ON 
    Student.Sno = SC.Sno  -- 连接条件：通过学号(Sno)关联两表

46

xxxxxxxxxx
-- 查询从未被任何学生选修的课程名称
SELECT 
    C.Cname  -- 课程名称（来自Course表）
FROM 
    Course C  -- 左表：课程信息表（基准表）
LEFT JOIN  -- 左连接：保留左表(Course)所有记录，即使右表(SC)无匹配
    SC      -- 右表：学生选课表
ON 
    C.Cno = SC.Cno  -- 连接条件：通过课程编号(Cno)关联两表
WHERE 
    SC.Cno IS NULL  -- 筛选条件：只选择右表中无匹配的记录（即未被选修的课程）xxxxxxxxxx -- 查询从未被任何学生选修的课程名称SELECT     C.Cname  -- 课程名称（来自Course表）FROM     Course C  -- 左表：课程信息表（基准表）LEFT JOIN  -- 左连接：保留左表(Course)所有记录，即使右表(SC)无匹配    SC      -- 右表：学生选课表ON     C.Cno = SC.Cno  -- 连接条件：通过课程编号(Cno)关联两表WHERE     SC.Cno IS NULL  -- 筛选条件：只选择右表中无匹配的记录（即未被选修的课程）SELECT Cname FROM Course C LEFT JOIN SC ON C.Cno = SC.Cno WHERE SC.Cno IS NULL

47

xxxxxxxxxx
SELECT Sname,Ssex
 FROM Student S LEFT JOIN SC 
ON S.Sno = SC.Sno
 WHERE Sdept = '计算机系'
 AND SC.Sno IS NULL

48

xxxxxxxxxx
SELECT S.Sno AS 学号,
 COUNT(SC.Cno) AS 选课门数
FROM Student S LEFT JOIN SC 
ON S.Sno = SC.Sno
 WHERE Sdept = '计算机系'
 GROUP BY S.Sno

49

xxxxxxxxxx
SELECT S.Sno AS 学号,COUNT(SC.Cno) AS 门数
FROM Student S LEFT JOIN SC 
ON S.Sno = SC.Sno
 WHERE Sdept = '信息管理系'
 GROUP BY S.Sno
 HAVING COUNT(SC.Cno) < 3
 ORDER BY COUNT(SC.Cno) ASC

50

xxxxxxxxxx
SELECT TOP 3 WITH TIES Sname, Sage, Sdept 
FROM Student 
ORDER BY Sage DESC

51

xxxxxxxxxx
SELECT TOP 3 WITH TIES Sname, Sdept, Grade
 FROM Student S JOIN SC on S.Sno = SC.Sno 
JOIN Course C ON C.Cno = SC.Cno
 WHERE Cname = 'Java'
 ORDER BY Grade DESC

52

xxxxxxxxxx
SELECT TOP 2 WITH TIES Cno, COUNT(*) 选课人数
FROM SC
 GROUP BY Cno
 ORDER BY COUNT(Cno) ASC

53

xxxxxxxxxx
SELECT TOP 2 WITH TIES S.Sno, 
COUNT(*) 选课门数,AVG(Grade) 平均成绩
FROM Student S JOIN SC ON S.Sno = SC.Sno
 WHERE Sdept = '计算机系'
 GROUP BY S.sno
 HAVING COUNT(*) > 2
 ORDER BY AVG(Grade) DESC

xxxxxxxxxx

54

xxxxxxxxxx
-- 查询学生学号、姓名，并对系别进行转换显示缩写，以及成绩
SELECT 
  s.Sno 学号,  -- 从Student表中选择学号列，并设置别名为“学号”
  Sname 姓名,  -- 从Student表中选择姓名列，并设置别名为“姓名”
  CASE sdept  -- 使用CASE语句根据系别(sdept)的值进行条件判断
     WHEN '计算机系' THEN 'CS'  -- 如果系别为“计算机系”，则显示为“CS”
     WHEN '信息管理系' THEN 'IM'  -- 如果系别为“信息管理系”，则显示为“IM”
     WHEN '通信工程系' THEN 'COM'  -- 如果系别为“通信工程系”，则显示为“COM”
  END AS 所在系,  -- 结束CASE语句，并将结果列命名为“所在系”
  Grade 成绩  -- 从SC表中选择成绩列，并设置别名为“成绩”
FROM 
  Student s  -- 从Student表开始查询，设置别名为s
JOIN 
  SC ON s.Sno = SC.Sno  -- 将Student表与SC表通过学号(Sno)进行内连接
JOIN 
  Course c ON c.Cno = SC.Cno  -- 将SC表与Course表通过课程号(Cno)进行内连接
WHERE 
  Cname = 'Java'  -- 筛选条件：课程名称为“Java”^[1]^

55

xxxxxxxxxx
SELECT Sno, Grade,
  CASE 
    WHEN Grade >= 90 THEN '优'
    WHEN Grade between 80 and 89 THEN '良'
    WHEN Grade between 70 and 79 THEN '中'
    WHEN Grade between 60 and 69 THEN '及格'
    WHEN Grade <60 THEN '不及格'
  END AS 成绩等级
  FROM SC
  WHERE Cno = 'C001'

56

xxxxxxxxxx
SELECT S.Sno, COUNT(SC.Cno) 选课门数,CASE 
  WHEN COUNT(SC.Cno) > 4 THEN '多'
  WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN '一般'
  WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN '少'
  WHEN COUNT(SC.Cno) = 0 THEN '未选'
END AS 选课情况
FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno
WHERE Sdept = '计算机系'
GROUP BY S.Sno
ORDER BY COUNT(SC.Cno) DESC

57

xxxxxxxxxx

SELECT S.Sno, COUNT(SC.Cno) 选课门数,CASE WHEN COUNT(SC.Cno) > 4 THEN '多' WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN '一般' WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN '少' WHEN COUNT(SC.Cno) = 0 THEN '未选' END AS 选课情况 FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = '计算机系' GROUP BY S.Sno ORDER BY COUNT(SC.Cno) DESC

58

xxxxxxxxxx