测试-子查询及数据更新

1、修改borrow表增加一列；修改日期数据（两条语句完成）

题目

修改borrow表增加借书日期bdate列，列类型为datetime；

将机械系的同学的借书日期值修改为还书日期的前两个月的时间。

用两条语句完成，日期的修改可以用date_add( )或adddate( )。

原表结构如下：

card(借书卡) 表：cno 卡号，name 姓名，class 班级

borrow(借书记录)表 ：cno 借书卡号，bno 书号，rdate 还书日期

代码

alter table borrow
add column bdate datetime;
update borrow
set bdate = date_add(rdate, interval - 2 month)
where cno in
(select cno
from card
where deptName ='机械系');

题解

• ALTER TABLE borrow ADD COLUMN bdate DATETIME;：这个语句用于在borrow表中添加一个名为bdate的DATETIME类型的列。
• UPDATE borrow SET bdate = DATE_ADD(rdate, INTERVAL -2 MONTH)：这个语句用于更新borrow表中的所有记录，将bdate列的值设置为对应记录的rdate值减去2个月。DATE_ADD()函数用于将时间值加上指定的时间间隔。在这个语句中，使用了负数的时间间隔来表示减去2个月。
• WHERE cno IN (SELECT cno FROM card WHERE deptName = '机械系')：这个语句用于筛选出card表中所在系别为“机械系”的所有借阅卡号，并将对应的borrow表中的借阅记录的bdate列值设置为对应记录的rdate减去2个月。这个语句中使用了子查询来获取符合条件的卡号。

2、 SQL更新：删除-删除“吴宾”的所有成绩记录

题目

有student、course、sc表；删除“吴宾”的所有成绩记录。

student：

sc:

course:

代码

delete from sc where sno in (select sno from student where sname= '吴宾');

3、SQL查询：查询没有被订购的商品

题目

相关表结构：

1、商品表：product

2、类别表：category

3、订单表：order

4、订单明细：order_detail

代码

select product_id,product_name,category_id
from product
where product.product_id not in (select order_detail.product_id from order_detail);

4、插入图书信息

题目

将图书信息插入到book表中，其中书号 7, 书名 组合数学, 作者 刘迪, 价格 36.70, 数量 37。

表结构如下：

book(图书) 表：bno 书号，bname 书名，author 作者，price 单价，quantity 库存数

代码

insert
into book(bno,bname,author,price,quantity)
values('7','组合数学','刘迪','36.70',37);

5、 查询现有图书中价格最高的图书信息

题目

查询现有图书中价格最高的图书（可能不止一种书），输出书名及作者。

表结构如下：

book(图书) 表：bno 书号，bname 书名，author 作者，price 单价，quantity 库存数

代码

select bname,author
from book
where price = (select max(price) from book);

6、向student表中一次插入多行数据

题目

一次向student表中插入两条记录，其中王大力的系别用缺省值赋值。
两行数据的学号，姓名，性别，年龄和系别分别如下：
9520103,王敏,女,20,信息系;
9520104,王大力,男,19。

student表结构：

代码

insert into student
values ('9520103','王敏','女',20,'信息系'),
('9520104','王大力','男',19,'计算机系');

7、插入计算机系学生C01课程的选课记录

题目

在sc表中插入计算机系所有学生C01课程的选课记录。

student表结构：

sc表结构：

代码

INSERT INTO sc (sno, cno, grade)
SELECT sno, 'C01', NULL
FROM student
WHERE sdept = '计算机系';

8、查询所有人都借阅过的图书信息

题目

查询所有人都借阅过的图书信息，列出书号，书名，作者。

表结构如下：

card(借书卡) 表：cno 卡号，name 姓名，class 班级

book(图书) 表：bno 书号，bname 书名，author 作者，price 单价，quantity 库存数

borrow(借书记录)表 ：cno 借书卡号，bno 书号，rdate 还书日期

代码

select b.bno, b.bname, b.author
from book b
where not exists (SELECT c.cnofrom card cwhere not exists (select *from borrow bowhere bo.cno = c.cno and bo.bno = b.bno)
);

题解

• SELECT b.bno, b.bname, b.author FROM book b：这个语句用于从book表中选取图书编号（bno）、书名（bname）和作者（author）这三个列。
• WHERE NOT EXISTS (SELECT c.cno FROM card c WHERE NOT EXISTS (SELECT * FROM borrow bo WHERE bo.cno = c.cno AND bo.bno = b.bno))：这个语句是一个嵌套的子查询，用于筛选出所有没有被借阅的图书。具体解释如下：
• SELECT c.cno FROM card c：这个子查询用于从card表中选取所有的借阅卡号（cno）。
• WHERE NOT EXISTS (SELECT * FROM borrow bo WHERE bo.cno = c.cno AND bo.bno = b.bno)：这个子查询用于判断某个卡号是否借阅了某本图书。如果这个卡号没有借阅某本图书，则返回TRUE，否则返回FALSE。这个子查询中使用了borrow表，通过bo.cno = c.cno和bo.bno = b.bno两个条件将borrow表和card表关联起来。如果这个子查询的结果集为空，则说明某本图书没有被某个借阅卡号借阅，因此这本图书符合条件。
• WHERE NOT EXISTS：这个语句用于判断是否存在一个记录使得子查询的结果集为空。如果子查询的结果集为空，则NOT EXISTS返回TRUE，否则返回FALSE。因此，这个WHERE子句的作用是筛选出card表中所有没有借阅某本图书的卡号。
• 最终这个WHERE子句的作用是筛选出不存在没有借阅某本图书的借阅卡号的所有图书记录。也就是说，这个WHERE子句返回的图书记录都是已被所有借阅卡号借出的图书。

9、SQL更新：修改-“张海”的“计算机导论”课程成绩为70。

题目

有student、course、sc表；修改“张海”的“计算机导论”课程成绩为70。

student：

sc:

course:

代码

UPDATE sc
SET grade=70
WHERE sno IN (SELECT sno FROM student WHERE sname='张海')
AND cno IN (SELECT cno FROM course WHERE cname='计算机导论');

10、借了"计算方法"，但没有借"计算方法习题集"的读者，输出其借书卡号

题目

查询当前借了"计算方法"，但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

表结构如下：

book(图书) 表：bno 书号，bname 书名，author 作者，price 单价，quantity 库存数

borrow(借书记录)表 ：cno 借书卡号，bno 书号，rdate 还书日期

代码

SELECT cno FROM borrow
WHERE bno = (SELECT bno FROM book WHERE bname = '计算方法')
AND cno NOT IN
(SELECT cno FROM borrow WHERE bno = (SELECT bno FROM book WHERE bname = '计算方法习题集'))
ORDER BY cno DESC;

题解

1. SELECT cno FROM borrow：这个语句用于从borrow表中选取借阅卡号（cno）这一列。
2. WHERE bno = (SELECT bno FROM book WHERE bname = '计算方法')：这个子查询用于从book表中选取书名为“计算方法”的图书编号（bno），并将borrow表中的bno列值与之进行比较。这个条件筛选出了所有借阅了《计算方法》的借阅记录。
3. AND cno NOT IN (SELECT cno FROM borrow WHERE bno = (SELECT bno FROM book WHERE bname = '计算方法习题集'))：这个子查询用于从book表中选取书名为“计算方法习题集”的图书编号（bno），并从borrow表中筛选出所有借阅了《计算方法习题集》的借阅记录，并将它们的借阅卡号（cno）放入一个子查询中。这个条件使用了NOT IN操作符，将借阅了《计算方法习题集》的借阅卡号从所有借阅了《计算方法》的借阅卡号中排除，从而得到借阅了《计算方法》但是没有借阅《计算方法习题集》的读者卡号。
4. ORDER BY cno DESC：这个语句用于按照借阅卡号倒序排列查询结果。

11、 SQL查询：查询每个学生高于他自己选修平均分的那门课程的成绩，输出学号，课程号，课程成绩，他所有课程的平均分，并按学号升序排列

题目

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT sc.sno,cno,grade,avggrade 
FROM sc,(SELECT sno,ROUND(AVG(grade)) AS avggrade FROM sc GROUP BY sno) AS avg_sc
WHERE sc.sno=avg_sc.sno AND sc.grade>avg_sc.avggrade
ORDER BY sc.sno;

题解

1. SELECT sc.sno, cno, grade, avggrade：这个语句用于从sc表中选择学号（sno）、课程号（cno）、成绩（grade）以及该学生的平均成绩（avggrade）这四个列。
2. (SELECT sno,ROUND(AVG(grade)) AS avggrade FROM sc GROUP BY sno) AS avg_sc：这个子查询用于计算每个学生的平均成绩。首先，使用AVG函数计算出每个学生的平均成绩，并使用ROUND函数将结果四舍五入为整数。然后，使用GROUP BY子句将结果按照学号分组，并将结果作为一个子查询命名为avg_sc。
3. WHERE sc.sno=avg_sc.sno AND sc.grade>avg_sc.avggrade：这个WHERE子句用于将sc表和avg_sc子查询连接起来，并筛选出那些成绩高于该学生平均成绩的记录。具体来说，将avg_sc子查询的结果集中的每个记录与sc表中的记录进行比较，如果学号相同且成绩高于平均成绩，则将该记录包含在查询结果中。
4. ORDER BY sc.sno：这个语句用于按照学号升序排列查询结果。

12、插入新的部门记录

题目

插入一个新的部门记录：部门编号-1009；部门名称-“培训部”

代码

insert into department(dept_id,dept_name)
value ("1009","培训部");

13、修改编号33的“安志杰”的部门编号为1005，职位为“业务员”。

题目

代码

update employee
set dept_id = 1005,job_title = "业务员"
where employee_id = 33;

14、查询计算机系平均成绩前三名的学号，姓名、平均成绩

题目

有课程表，学生表，成绩表如下，查询计算机系平均成绩前三名的学号，姓名、平均成绩。

course

列名	数据类型	约束	说明
cno	char(4)	主键非空	课程号
cname	varchar(40)	非空	课程名
cpno	char(4)	参照course(cno)	先修课
ccredit	tinyint		学分

student

列名	数据类型	约束	说明
sno	char(7)	主键非空	学号
sname	char(10)	非空	学生姓名
ssex	enum(‘男’,‘女’)	默认‘男’	性别
sage	tinyint		年龄
sdept	char(20)	默认’计算机系’	系别

sc

列名	数据类型	约束	说明
sno	char(7)	主键非空，参照student(sno)	学号
cno	char(4)	主键非空，参照course(cno)	课程号
grade	decimal(5,1)		成绩
ccredit	tinyint

代码

select student.sno,sname,avg(grade)
from student,sc
where student.sno=sc.sno and sdept = "计算机系"
group by student.sno
order by avg(grade) desc
limit 3;

题解

1. 使用 SELECT 子句选择要查询的字段，包括学生表的学生编号（sno）和学生姓名（sname），以及选课表的成绩（grade）的平均值（avg(grade)）。
2. 使用 FROM 子句指定要查询的表格，即学生表和选课表。
3. 使用 WHERE 子句指定查询条件，即学生表的专业是计算机系（sdept = “计算机系”）并且学生表和选课表之间的学生编号相同（student.sno=sc.sno）。
4. 使用 GROUP BY 子句按照学生编号对结果进行分组，以便计算每个学生的平均成绩。
5. 使用 ORDER BY 子句按照平均成绩降序排序，以便找到平均成绩排名前三的学生。
6. 使用 LIMIT 子句限制结果集大小为三，以便只返回平均成绩排名前三的学生记录。

15、SQL查询：查询人员信息：包括员工和客户

题目

查询人员信息：包括员工和客户。(员工数据在前）

输出“id”、“name”、“type”，若为员工，type为“员工”，若为客户，type为“客户”。

员工表：employee

客户表：customer

查询输出形式如下：

代码

select employee_id,employee_name,"员工" as "type" from employee
union 
select customer_id,customer_name,"客户" as "type" from customer;