数据库实验(SQL SERVER)_使用sql语句,分别为学生表、课程表、选课表插入数据

数据库实验二

实验内容：
使用各种查询条件完成指定的查询操作
实验步骤
1、创建学生表student、课程表course和选课表SC,并输入数据（注意数据的完整性。）;（可以使用实验一中已经建立的表和数据）
2、对各表中的数据进行不同条件的查询；
包括的运算：投影、选择、比较运算符、逻辑运算符、字符匹配运算符、匹配列表范围、算术运算符、内部函数、排序、分组、分组函数使用
（1）查询全体学生的学号和姓名
（2）查询全体学生的详细记录
（3）查询软件学院的学生姓名、年龄、系别
（4）查询所有选修过课程的学生学号（不重复）
（5）查询考试不及格的学生学号（不重复）
（6）查询不是软件学院、计算机系的学生性别、年龄、系别
（7）查询年龄18－20岁的学生学号、姓名、系别、年龄；
（8）查询姓刘的学生情况
（9）查询姓刘或姓李的学生情况
（10）查询姓刘且名字为两个字的学生情况
（11）查询1983年以后出生的学生姓名。
（12）创建表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)
计算学生各科总成绩并赋予别名
（13）利用内部函数 year()查找软件学院学生的出生年份
（14）利用字符转换函数实现字符联接。
Select sname + ‘年龄为’+cast(sage as char(2))+’岁’
From student
（15）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。
（16）查询学生总人数。
（17）查询选修了课程的学生人数。
（18）查询选修了7号课程的学生总人数和平均成绩
（19）查询选修6号课程学生的最好成绩
（20）查询每个系的系名及学生人数。
（21）查找每门课的选修人数及平均成绩
（22）查找没有先修课的课程情况
实验要求
1、将上述任务中完整的SQL语句调试并使之运行正确
实验具体代码：
1：创建数据库TEST2并使用该数据库

CREATE DATABASE TEST2;
go
USE TEST2;
go
1
2
3

2：创建三个基本表

--创建学生表
CREATE TABLE student(
sno char(8) PRIMARY KEY,
sname char(6) UNIQUE,
ssex char(2),
sage int,
sdept char(10),
Constraint sex_1 check(ssex IN ('男','女'))
);
go
--创建课程表
CREATE TABLE course(
cno char(2) PRIMARY KEY,
cname char(30),
credit int,
cpno char(3),
);
go
--创建选修课表
CREATE TABLE sc(
sno char(8),
cno char(2),
grade int CHECK (grade>=0 and grade<=100),
PRIMARY KEY(sno,cno),
Constraint FK_SNO FOREIGN KEY (sno) REFERENCES student(sno),
Constraint FK_CNO FOREIGN KEY (cno) REFERENCES course(cno)
);
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

3：向三个表中插入数据：

--向学生表插入数据
INSERT INTO student VALUES('001','李华','男',20,'CS');
INSERT INTO student VALUES('002','刘晨','女',19,'CS');
INSERT INTO student VALUES('003','王敏','女',18,'MA');
INSERT INTO student VALUES('004','张立','男',19,'IS');
INSERT INTO student VALUES('005','张三','男',20,'IS');
INSERT INTO student VALUES('006','刘平','女',20,'CS');
INSERT INTO student VALUES('007','李四','男',20,'SW');
INSERT INTO student VALUES('008','刘三世','女',16,'MA');
INSERT INTO student VALUES('009','王麻子','男',19,'SW');
INSERT INTO student VALUES('010','赵敏','女',18,'SW');
go
--向课程表插入数据
INSERT INTO course VALUES('1','数据库',4,'5');
INSERT INTO course (cno,cname,credit) VALUES('2','数学',2);
INSERT INTO course VALUES('3','信息系统',4,'1');
INSERT INTO course VALUES('4','操作系统',3,'6');
INSERT INTO course VALUES('5','数据结构',4,'7');
INSERT INTO course (cno,cname,credit) VALUES('6','数据处理',2);
INSERT INTO course VALUES('7','PASCAL语言',4,'6');
go
--向选修课程表插入数据
INSERT INTO sc VALUES('001','1',98);
INSERT INTO sc VALUES('001','2',55);
INSERT INTO sc VALUES('002','2',68);
INSERT INTO sc VALUES('002','5',90);
INSERT INTO sc VALUES('004','1',92);
INSERT INTO sc VALUES('004','6',86);
INSERT INTO sc VALUES('005','3',94);
INSERT INTO sc VALUES('005','7',45);
INSERT INTO sc VALUES('006','1',50);
INSERT INTO sc VALUES('006','5',58);
INSERT INTO sc VALUES('007','1',68);
INSERT INTO sc VALUES('008','2',78);
INSERT INTO sc VALUES('008','6',68);
INSERT INTO sc VALUES('009','1',82);
INSERT INTO sc VALUES('009','7',76);
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

4：查询全体学生的学号和姓名

SELECT sno,sname
FROM student;
go
1
2

5：查询全体学生的详细记录

SELECT *
FROM student;
go
1
2

6：查询软件学院的学生姓名、年龄、系别

SELECT sname,sage,sdept
FROM student
WHERE sdept='SW';
go
1
2
3

7：查询所有选修过课程的学生学号（不重复）

SELECT DISTINCT sno
FROM sc;
go
1
2

8：查询考试不及格的学生学号（不重复）

SELECT DISTINCT sno
FROM sc
WHERE grade<=60;
go
1
2
3

9：查询不是软件学院、计算机系的学生性别、年龄、系别

SELECT ssex,sage,sdept
FROM student
WHERE sdept<>'SW' and sdept<>'CS';
go
1
2
3

10：查询年龄18－20岁的学生学号、姓名、系别、年龄；

SELECT sno,sname,sdept,sage
FROM student
WHERE sage>=18 and sage<=20;
go
1
2
3

11：查询姓刘的学生情况

SELECT *
FROM student
WHERE sname LIKE '刘%';
go
1
2
3

12：查询姓刘或姓李的学生情况

SELECT *
FROM student
WHERE sname LIKE '[刘李]%';
go
1
2
3

13：查询姓刘且名字为两个字的学生情况

SELECT *
FROM student
WHERE sname LIKE '刘_';
go
1
2
3

14：当前年为2020年，查询1983年以后出生的学生姓名

SELECT sname
FROM student
WHERE 2020-sage>1983;
go
1
2
3

15：创建表studentgrad(sno,mathgrade,englishigrade,chinesegrade)计算学生各科总成绩并赋予别名

CREATE TABLE studentgrade(
sno char(8) primary key,
mathgrade int,
englishgrade int,
chinesegrade int,
);
go
INSERT INTO studentgrade values( '001',85,95,74);
INSERT INTO studentgrade values( '002',86,91,70);
INSERT INTO studentgrade values( '003',80,92,71);
INSERT INTO studentgrade values( '004',81,91,75);
INSERT INTO studentgrade values( '005',87,97,78);
INSERT INTO studentgrade values( '006',70,90,84);
INSERT INTO studentgrade values( '007',76,80,79);
INSERT INTO studentgrade values( '008',75,95,52);
INSERT INTO studentgrade values( '009',96,70,69);
INSERT INTO studentgrade values( '010',66,100,89);
go
SELECT SUM(mathgrade+chinesegrade+englishgrade) AS 学生总成绩
FROM studentgrade
GROUP BY sno;
go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

16：用内部函数 year()查找软件学院学生的出生年份

SELECT sname,(year(getdate())-student.sage )
from student
where sdept='CS';
go
1
2
3

17：字符转换函数实现字符联接

SELECT sname + '年龄为'+cast(sage as char(2))+'岁'
FROM student;
go
1
2

18：查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列

SELECT *
FROM student
ORDER BY sdept ASC,sage DESC;
go
1
2
3

19：查询学生总人数

SELECT COUNT(SNO)
FROM student;
go
1
2

20：查询选修了课程的学生人数

SELECT COUNT(DISTINCT sno)
FROM sc;
go
1
2

21：查询选修了7号课程的学生总人数和平均成绩

SELECT COUNT(sno),AVG(grade)
FROM sc
WHERE cno='7'
go
1
2
3

22：查询选修6号课程学生的最好成绩

SELECT max(grade) as maxgrade
FROM sc
WHERE cno='6';
go
1
2
3

23：查询每个系的系名及学生人数

SELECT sdept,COUNT(*)
FROM student
GROUP BY sdept;
go
1
2
3

24：查找每门课的选修人数及平均成绩

SELECT cno,COUNT(sno),AVG(grade)
FROM sc
GROUP BY cno;
go
1
2
3

25：查找没有先修课的课程情况

SELECT *
FROM course
WHERE cpno is null;
go
1
2
3

注意：
1：在选取不重复的记录时要加入DISTINCT关键词
2：在匹配字符串时所遇到的通配符"_“和”%"，要求别两者，前者代表一个字符，后者代表无穷字符。
3：对SQL内部函数的调用和每个内部函数的意义需理解清楚。