SQL SERVER 测试题

测试题

Student表的定义

字段名

字段描述

数据类型

主键

外键

非空

唯一

自增

id

学号

Int

 

 

name

姓名

Varchar(20)

 

 

 

 

sex

性别

Varchar(4)

 

 

 

 

 

birth

出生日期

date

 

 

 

 

 

department

院系

Varchar(20)

 

 

 

 

address

家庭住址

Varchar(50)

 

 

 

 

 

 

Score表的定义

字段名

字段描述

数据类型

主键

外键

非空

唯一

自增

id

编号

Int

 

 

stu_id

学号

Int

 

 

 

 

c_name

课程名

Varchar(20)

 

 

 

 

 

grade

分数

Int

 

 

 

 

 

系统初始了以下脚本数据

向student表插入记录的INSERT语句如下:

INSERT INTO student VALUES( 901,'张老大', '','1985-05-11','计算机系', '北京市海淀区');

INSERT INTO student VALUES( 902,'张老二', '','1986-05-11','中文系', '北京市昌平区');

INSERT INTO student VALUES( 903,'张三', '','1990-05-11','中文系', '湖南省永州市');

INSERT INTO student VALUES( 904,'李四', '','1990-05-11','英语系', '辽宁省阜新市');

INSERT INTO student VALUES( 905,'王五', '','1991-05-11','英语系', '福建省厦门市');

INSERT INTO student VALUES( 906,'王六', '','1988-05-11','计算机系', '湖南省衡阳市');

score表插入记录的INSERT语句如下:

INSERT INTO score VALUES(1,901, '计算机',98);

INSERT INTO score VALUES(2,901, '英语', 80);

INSERT INTO score VALUES(3,902, '计算机',65);

INSERT INTO score VALUES(4,902, '中文',88);

INSERT INTO score VALUES(5,903, '中文',95);

INSERT INTO score VALUES(6,904, '计算机',70);

INSERT INTO score VALUES(7,904, '英语',92);

INSERT INTO score VALUES(8,905, '英语',94);

INSERT INTO score VALUES(9,906, '计算机',90);

INSERT INTO score VALUES(10,906, '英语',85);

试题

1、创建studentscore表语句

CREATE TABLE student(
    id              INT PRIMARY KEY Identity(1,1),
    name            VARCHAR(20) NOT NULL,
    sex             VARCHAR(4),
    birth           DATE,
    department      VARCHAR(20) NOT NULL,
    address         VARCHAR(50)
);
CREATE TABLE score(
    id        INT PRIMARY KEY Identity(1,1),
    stu_id    INT NOT NULL,
    c_name    VARCHAR(20),
    grade     INT
);

2、查询student表的所有记录

SELECT * FROM student;

3、查询student表的第2条到4条记录

select top (2) id from student
where id not in (
select top 1 id from student
)

4、从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息

SELECT id AS '学号',name AS '姓名',department AS '院系' FROM student;

5、从student表中查询计算机系和英语系的学生的信息(用 IN 关键字)

SELECT * FROM student WHERE department IN ('计算机系','英语系');

6、从student表中查询出生年份在1990到1995之间的学生信息(用 BETWEEN AND

SELECT * FROM student WHERE YEAR(birth) BETWEEN 1990 AND 1995;

7、从student表中查询每个院系有多少人

SELECT department AS '院系',COUNT(*) AS '该院人数' FROM student GROUP BY department;

8、从score表中查询每个科目的最高分

SELECT c_name AS '科目名称',MAX(grade) AS '最高分' FROM score GROUP BY c_name;

9、查询李四的考试科目(c_name)和考试成绩(grade

SELECT s.name AS '姓名',c.c_name AS '课程名称',c.grade AS '课程分数' 
FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE s.name = '李四';

10、用连接的方式查询所有学生的信息和考试信息

SELECT * FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id;

11、计算每个学生的总成绩

SELECT s.name AS '姓名',SUM(c.grade) AS '总成绩' FROM score AS c
LEFT JOIN student AS s ON c.stu_id = s.id GROUP BY s.name;

12、计算每个考试科目的平均成绩

SELECT c_name AS '课程名称',AVG(grade) AS '平均成绩' FROM score GROUP BY c_name;

13、查询计算机成绩低于95的学生信息

-- 第一种
SELECT s.* FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE c.c_name = '计算机' AND c.grade < 95;

-- 第二种
SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name = '计算机' AND grade < 95);

14、查询同时参加计算机和英语考试的学生的信息

-- 第一种
SELECT * FROM student WHERE id IN 
(SELECT stu_id FROM score WHERE c_name = '计算机' OR c_name = '英语' GROUP BY stu_id HAVING COUNT(*) = 2);

-- 第二种
SELECT * FROM student WHERE id IN
(SELECT stu_id FROM score WHERE stu_id IN(SELECT stu_id FROM score WHERE c_name = '计算机') AND c_name = '英语');

-- 第三种
SELECT s.* FROM student AS s, score AS b ,score AS c WHERE s.id = b.stu_id AND b.c_name = '计算机' AND s.id = c.stu_id AND c.c_name = '英语';

15、将计算机考试成绩按从高到低进行排序

SELECT grade AS '计算机考试成绩' FROM score WHERE c_name = '计算机' ORDER BY grade DESC;

16、从student表和score表中查询出学生的学号,然后合并查询结果

SELECT id AS '学号' FROM student
UNION
SELECT DISTINCT stu_id AS '学号' FROM score;

17、查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

SELECT s.name AS '姓名',s.department AS '院系',c.c_name AS '考试科目',c.grade AS '成绩'
FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE s.name LIKE '张%' OR s.name LIKE '王%';

18、查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

SELECT s.name AS '姓名',s.birth AS '出生日期',s.department AS '院系',c.c_name AS '考试科目',c.grade AS '成绩'
FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE s.address LIKE '湖南%';

 

 

 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值