第一题

1. 删除classno列
2. 修改tel的列名为phone
3. Liu Liu's student information does not exist,删除相关信息
4. Delete the language score in85到90之间的学生信息
5. Delete the information of students whose home address is in Tongzhou District and whose grades fail
6. 删除学号为1,3,6, 9的学生.
7. 修改学号为5The address is Zhengzhou Eighth Street and the credits88分
   

第二题

2. 把下面的sql写出来

```sql /*Table structure for table `course` */ DROP TABLE IF EXISTS `course`; //检测是否存在表`course`,存在删掉,不存在不执行 CREATE TABLE `course` ( `cs_id` int(11) NOT NULL COMMENT '课程编号', `cs_name` varchar(50) NOT NULL COMMENT '课程名称', `cs_credit` tinyint(255) unsigned DEFAULT NULL COMMENT '课程学分', `cs_type` char(12) DEFAULT NULL COMMENT '课程类别', `cs_depart` char(6) DEFAULT NULL COMMENT '院系名称', PRIMARY KEY (`cs_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `course` */ insert into `course`(`cs_id`,`cs_name`,`cs_credit`,`cs_type`,`cs_depart`) values (5200313,'数据库原理及应用',4,'核心专业','信工'),(5203314,'计算机导论',4,'通识教育','信工'),(5219314,'数据结构',5,'专业核心','信工'),(5223013,'大学物理',4,'专业基础','信工'),(5227614,'毕业实习',4,'集中实践','信工'),(5230912,'云计算',2,'共同选修','信工'),(5236212,'机器学习',2,'共同选修','信工'),(5237514,'c语言',4,'专业基础','信工'),(5245112,'区块链',2,'任意选修','信工'),(7200422,'知识产权法',2,'任意选修','文法'),(20201833,'概率论',3,'专业基础','基础'),(20202336,'高等数学',6,'专业基础','基础'),(29299131,'劳动教育',1,'集中实践','学务');



1. 查询全部课程的信息

2. 查询信工学院开设的课程名、课程号及学分

3.查询学分超过3学分的课程代码、课程名和开课单位


4.查询学分在2到4之间课程的信息


5.查询课程名称中带“数据”的课程名、课程号及开课单位

6.查询不是信工学院开设的集中实践课的开课单位和课程名称

7.查询信工学院开设的课程的类型有哪些

-- 1. 查询全部课程的信息
select * from course;

-- 2. 查询信工学院开设的课程名、课程号及学分
select cs_name,cs_id,cs_credit from course;

-- 3.查询学分超过3学分的课程代码、课程名和开课单位
select cs_id,cs_name,cs_depart from course where cs_credit>3;

-- 4.查询学分在2到4之间课程的信息
select * from course where cs_credit between 2 and 4;

-- 5.查询课程名称中带“数据”的课程名、课程号及开课单位
select cs_name,cs_id,cs_depart from course where cs_name like '%数据%';

-- 6.查询不是信工学院开设的集中实践课的开课单位和课程名称
select cs_depart , cs_name from course where cs_depart != '信工' and cs_type='集中实践';

-- 7.查询信工学院开设的课程的类型有哪些
select cs_type from course where cs_depart='信工';

第三题

/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;		//检测是否存在表`student`,存在删掉,不存在不执行

CREATE TABLE `student` (
  `stu_id` bigint(11) unsigned NOT NULL COMMENT '学号',
  `stu_name` char(12) NOT NULL COMMENT '姓名',
  `stu_sex` enum('男','女') DEFAULT NULL COMMENT '性别',
  `stu_age` tinyint(255) unsigned DEFAULT NULL COMMENT '年龄',
  `stu_major` char(9) DEFAULT NULL COMMENT '专业',
  `stu_college` char(12) DEFAULT NULL COMMENT '学院',
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */
insert into `student`(`stu_id`,`stu_name`,`stu_sex`,`stu_age`,`stu_major`,`stu_college`) values (201804550101,'郭奎','男',22,'计科','信工学院'),(201804550102,'吕宇航','男',18,'计科','信工学院'),(201804550103,'张豪辉','女',19,'计科','信工学院'),(201804550107,'丁志杰','男',17,'金融学','金贸学院'),(201804550109,'范伟','男',19,'金融学','金贸学院'),(201804550116,'张依婷','女',17,'大数据','信工学院'),(201804550120,'张维','男',19,'计科','信工学院'),(201804550121,'朱柳阳','女',20,'计科','信工学院'),(201804550144,'谭兵炎','男',20,'大数据','信工学院'),(201804550153,'杨志强','男',17,'大数据','信工学院');

1.查询计科专业和大数据专业的学生信息


2.查询不是信工学院的学生姓名和学号

3.查询年龄是17,18,19的学生姓名和专业

4.查询信工学院的的专业有哪些

5.查询年龄为空的学生信息

6.查询学生的信息,查询结果按姓名升序排序

-- 1.查询计科专业和大数据专业的学生信息
select * from stu where stu_major='计科' or stu_major='大数据';

-- 2.查询不是信工学院的学生姓名和学号
select stu_name,stu_id from stu where stu_college != '信工学院';

-- 3.查询年龄是17,18,19的学生姓名和专业
select stu_name,stu_major from stu where stu_age in (17,18,19);

-- 4.查询信工学院的的专业有哪些
select  distinct stu_major  from stu where stu_college='信工学院'; 

-- 5.查询年龄为空的学生信息
select * from stu where stu_age is null;

-- 6.查询学生的信息,查询结果按姓名升序排序
select * from stu order by stu_name asc;

第四题

create table student
(
    id int(10) primary key,
    name varchar(20) not null,
    sex varchar(4),
    birth year,
    department varchar(20) not null,
    address varchar(50)
);

create table score
(
    id int(10) primary key,
    stu_id int(10) not null,
    c_name varchar(20),
    grade int(10)
);
insert into student(id,name,sex,birth,department,address)
    values(901,'张老大','男',1985,'计算机系','北京市海淀区'),
        (902,'张老二','男',1986,'中文系','北京市昌平区'),
        (903,'张三','女',1990,'中文系','湖南省永州市'),
        (904,'李四','男',1990,'英语系','辽宁省阜新市'),
        (905,'王五','女',1991,'英语系','福建省厦门市'),
        (906,'王六','男',1988,'计算机系','湖南省衡阳市');
insert into score(id,stu_id,c_name,grade)
    values(1,901,'英语',80),
        (2,902,'计算机',65),
        (3,902,'中文',88),
        (4,903,'中文',95),
        (5,904,'计算机',70),
        (6,904,'英语',92),
        (7,905,'英语',94),
        (8,906,'计算机',90),
        (9,901,'计算机',null),
        (10,901,'中文',null),
        (11,902,'英语',null);
        
a) 查询student表的所有记录
b) 查询student表的第2条到4条记录
c) 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
d) 从student表中查询计算机系和英语系的学生的信息（Write two answers）
f) 从student表中查询每个院系有多少人
g) 从score表中查询每个科目的最高分
h) 计算每个考试科目的平均成绩
i) 将计算机考试成绩按从高到低进行排序    

-- a) 查询student表的所有记录
select * from student;

-- b) 查询student表的第2条到4条记录
select * from student limit 2,3;

-- c) 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
select id,name,department from student;

-- d) 从student表中查询计算机系和英语系的学生的信息（Write two answers）
select * from student where department='计算机系' or department='英语系';
select id,name,sex,birth,department,address from student where department='计算机系' || department='英语系';

-- f) 从student表中查询每个院系有多少人
select department , count(*) from student group by department;

-- g) 从score表中查询每个科目的最高分
select c_name,max(grade) from score group by c_name;

-- h) 计算每个考试科目的平均成绩
select c_name,avg(grade) from score group by c_name;

-- i) 将计算机考试成绩按从高到低进行排序 
select * from score where c_name='计算机' order by grade desc;

第五题

create table tb_emp(
    id int primary key auto_increment COMMENT '员工编号',
		name varchar(20) COMMENT '员工姓名',
		age int COMMENT '员工年龄',
		job varchar(20) COMMENT '岗位',
		salary int COMMENT '薪水',
		entrydate date COMMENT '员工入职时间',
		managerid int COMMENT '员工领导编号',
		dept_id int COMMENT '员工所在部门编号'
);
create table tb_dept(
    id int primary key auto_increment,
		name varchar(20)
);
insert into tb_dept(name) values('研发部'),('市场部'),('财务部'),('销售部'),('总经办');

insert into tb_emp values 
(null,'金庸',66,'总裁',20000,'2000-01-01',null,5),
(null,'张无忌',20,'项目经理',12500,'2005-12-01',1,1),
(null,'杨逍',33,'开发',8400,'2000-11-03',2,1),
(null,'韦一笑',48,'开发',11000,'2002-03-05',2,1),
(null,'常豫川',43,'开发',10500,'2004-09-01',2,1),
(null,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),
(null,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
(null,'周芷若',60,'会计',48000,'2006-06-01',7,3),
(null,'丁敏君',19,'出纳',4000,'2009-06-01',7,3),
(null,'赵敏',23,'市场总监',14000,'2009-06-01',1,2),
(null,'鹿仗客',56,'职员',3750,'2009-06-01',10,2),
(null,'鹤比翁',19,'职员',3750,'2009-06-01',10,2),
(null,'方东白',19,'职员',3750,'2009-06-01',10,2),
(null,'张三丰',88,'销售总监',14000,'2004-06-01',1,4),
(null,'玉莲舟',38,'销售',4600,'2009-06-01',14,4),
(null,'宋远桥',40,'销售',4600,'2009-06-01',14,4),
(null,'陈友谅',42,null,2000,'2011-06-01',1,null);
create table salglrade(
grade int,
losal int,
hisal int
) comment '薪资等级表;
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);


1：Query the employee ID of the employee with the highest salary in the employee table、员工姓名、工资和部门号.


2：Query each employee and their department name

3：Look up the rank of each employee's name and salary

4：Query employee name2letters are not the names of Min's employees、Department name、Salary level.

5：Look up the name of each employee and their manager

6：Look up the name of each employee and their manager（Including the company owner himself（There is no manager on him））

7：Look up the name of each employee and the department name of the department they belong to（Include departments without employees）

8：Look up the name of the highest paid person in each department、薪水和部门编号

9：Query the grade of the average salary of each department

10．查询员工的姓名、年龄、职位、部门信息―（隐式内连接)

11．查询年龄小于30岁的员工的姓名、年龄、职位、部门信息〈显式内连接）

12．查询拥有员工的部门ID、部门名称

13．查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来


14.查询所有员工的工资等级

15．查询“研发部”所有员工的信息及工资等级

16．查询“研发部”员工的平均工资


17．查询工资比“灭绝”高的员工信息.

18．查询比平均薪资高的员工信息

19．查询所有的部门信息,并统计部门的员工人数

-- 1：Query the employee ID of the employee with the highest salary in the employee table、员工姓名、工资和部门号.
select id,name,salary,dept_id from tb_emp where salary=(select max(salary) from tb_emp);

-- 2：Query each employee and their department name
select tb_emp.name,tb_dept.`name` from tb_emp join tb_dept where tb_emp.dept_id = tb_dept.id;

-- 3：Look up the rank of each employee's name and salary
select e.`name`,s.grade from tb_emp e, salgrade s where e.salary between s.losal and s.hisal;

-- 4：Query employee name2letters are not the names of Min's employees、Department name、Salary level.

select e.id,e.`name`,s.grade,d.`name` from tb_emp e, salgrade s ,tb_dept d where e.salary between s.losal and s.hisal and e.dept_id=d.id and substring(e.name,2,1)!='敏' order by e.id;

-- 5：Look up the name of each employee and their manager
select a.name ,b.name from tb_emp a join tb_emp b on a.managerid = b.id;

-- 6：Look up the name of each employee and their manager（Including the company owner himself（There is no manager on him））
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid = b.id;

-- 7：Look up the name of each employee and the department name of the department they belong to（Include departments without employees）
select tb_emp.`name`,tb_dept.`name` from tb_emp right join tb_dept on tb_emp.dept_id = tb_dept.id; 

-- 8：Look up the name of the highest paid person in each department、薪水和部门编号
-- a)Find the highest salary in each department
select max(tb_emp.salary) from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id group by tb_dept.name;

-- b)Find people who belong to these salaries from the employee table
select e.name,e.salary,e.dept_id from tb_emp e where e.salary in(select max(tb_emp.salary) from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id group by tb_dept.name);


-- 9：Query the grade of the average salary of each department
-- a)求每个部门的平均薪水
select avg(tb_emp.salary),tb_emp.dept_id from tb_emp group by tb_emp.dept_id;

-- b)From the result set into a temporary table In this table and the salary table to connect 筛选平均工资 later grades
select emp.*,g.grade from(
select avg(tb_emp.salary) ag,tb_emp.dept_id from tb_emp group by tb_emp.dept_id)emp 
join salgrade g on emp.ag between g.losal and g.hisal;



-- 10．查询员工的姓名、年龄、职位、部门信息―（隐式内连接)
select tb_emp.`name`,tb_emp.age,tb_emp.job,tb_dept.`name` from tb_emp ,tb_dept where tb_emp.dept_id=tb_dept.id;

-- 11．查询年龄小于30岁的员工的姓名、年龄、职位、部门信息〈显式内连接）
select tb_emp.`name`,tb_emp.age,tb_emp.job,tb_dept.`name` from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_emp.age < 30;

-- 12．查询拥有员工的部门ID、部门名称
select e.name,e.dept_id,a.name from tb_emp e join tb_dept a on e.dept_id = a.id; 

-- 13．查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来
select e.name,e.age,a.name from tb_emp e left join tb_dept a on e.dept_id = a.id where e.age>40; 

-- 14.查询所有员工的工资等级
select tb_emp.name,tb_emp.salary,salgrade.grade from tb_emp,salgrade where tb_emp.salary between salgrade.losal and salgrade.hisal;

-- 15．查询“研发部”所有员工的信息及工资等级
-- a)Look up the name of the R&D department
select tb_emp.name from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_dept.name='研发部';

select e.*,s.grade from tb_emp e,salgrade s where e.salary between s.losal and s.hisal and e.name in(select tb_emp.name from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_dept.name='研发部') order by e.id ;

-- 第二种写法
select e.*,s.grade from tb_emp e 
left join salgrade s on e.salary BETWEEN s.losal and s.hisal 
left join tb_dept d on e.dept_id = d.id 
where d.name='研发部'
;

-- 16．查询“研发部”员工的平均工资
select avg(salary) from tb_emp join tb_dept on tb_emp.dept_id=tb_dept.id where tb_dept.name='研发部';

-- 17．查询工资比“灭绝”高的员工信息.
-- a)Check Extinction's wages first
select max(salary) from tb_emp where name='灭绝';

select * from tb_emp where salary>(select max(salary) from tb_emp where name='灭绝');

-- 18．查询比平均薪资高的员工信息
-- a)Check the average salary first
select avg(salary) from tb_emp;
-- b)Look for people with higher-than-average salaries
select tb_emp.* from tb_emp where tb_emp.salary>(select avg(salary) from tb_emp);
-- 19．查询所有的部门信息,并统计部门的员工人数
	select tb_dept.name,count(*),tb_emp.dept_id from tb_emp right join tb_dept  on tb_emp.dept_id=tb_dept.id group by tb_emp.dept_id;

第六题

create table readers(
  reader_id int(7) primary key,-- 读者id
  name varchar(8),-- 读者姓名
  sex varchar(2) default '男' check(sex in('男','女')),
  dept varchar(16),-- The reader's department
  status varchar(8),-- 学历
  address varchar(30)-- 地址
);
-- 图书信息表
create table books(
 book_id int(6) primary key,-- 图书编号
 type_id varchar(3),-- 类型编号
 book_name varchar(50),-- 图书名
 author varchar(50),-- 作者
 publisher varchar(50),-- 出版社
 price int(3)-- 价格
);
-- 借阅信息表
create table borrow_info(
  reader_id int(7),-- 读者id
  book_id int(6),-- 图书Id
  borrow_time date,-- 借书时间
  notes varchar(200),-- 标志
 primary key(reader_id,book_id)
);

-- 添加数据readers
insert into readers values('0034103','Marshal Fan','男','艺术学院','本科生','South Campus of Normal University');
insert into readers values('0034301','杨凡','男','电信学院','教师','Bali Tailu Community');
insert into readers values('0034429','许丹丹','女','艺术学院','本科生','South Campus of Normal University');
insert into readers values('0134101','陈超','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134102','Fan Jinliang','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134103','Guohao','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134104','He Yunlong','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134105','刘德文','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134106','彭俊','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134107','Peng Zhicheng','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134108','Shen lift','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134109','田强','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134110','王旭','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134111','Wu Wei','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134112','熊敏','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134113','Yan Dong','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134114','Yin Jianpeng','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134115','周春林','男','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134116','陈洁','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134117','陈琪','女','计算机学院','研究生','South Campus of Normal University');
insert into readers values('0134118','Ding Lu','女','计算机学院','研究生','South Campus of Normal University');
insert into readers values('0134119','丁岩','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134120','Dong Lei','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134121','何芳','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134122','Jiang Sha','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134123','李颖','女','计算机学院','研究生','South Campus of Normal University');
insert into readers values('0134124','李芬','女','计算机学院','研究生','South Campus of Normal University');
insert into readers values('0134125','Li Jinping','女','计算机学院','教授','South Campus of Normal University');
insert into readers values('0134126','Li Xiaoxi','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134127','Li Yuejiu','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134128','Li Sha','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134129','Liao Ying','女','计算机学院','本科生','South Campus of Normal University');
insert into readers values('0134130','林琳','女','计算机学院','教师','South Campus of Normal University');
insert into readers values('0134131','林敏','女','计算机学院','本科生','South Campus of Normal University');

insert into books values('109101','BAS','高等数学','Li Fangjian','清华大学出版社','20');
insert into books values('109104','BAS','离散数学','Sun Defeng','天津大学出版社','22');
insert into books values('109107','ELC','数字电路','刘国庆','高等教育出版社','13');
insert into books values('109109','CMP','C语言程序设计','谭浩强','清华大学出版社','15');
insert into books values('109110','CMP','数据结构','王志国','高等教育出版社','32');
insert into books values('109111','CMP','操作系统','王志国','高等教育出版社','25');
insert into books values('109112','CMP','计算机组成原理','Zhang Xiaomin','NTU Student Press','27');
insert into books values('109113','CMP','微机原理与接口技术','刘国庆','人民大学出版社','34');
insert into books values('109114','CMP','数据库原理','Penrader','高等教育出版社','16');
insert into books values('109115','CMP','计算机网络','Ma Guolu','人民大学出版社','11');
insert into books values('109116','CMP','计算机网络','谭浩强','NTU Student Press','13');
insert into books values('109117','CMP','编译原理','方刚','清华大学出版社','38');
insert into books values('209101','CMP','VB与WINDOWS程序设计','谭浩强','清华大学出版社','30');
insert into books values('209102','CMP','C++with object-oriented techniques','谭浩强','人民大学出版社','19');
insert into books values('209103','CMP','Javaand web programming','付勇','高等教育出版社','20');
insert into books values('209106','CMP','单片机原理及应用','刘国庆','人民大学出版社','22');
insert into books values('209107','CMP','PLCPrinciples and application development','刘国庆','NTU Student Press','16');
insert into books values('209111','CMP','人工智能导论','丁宝康','高等教育出版社','18');
insert into books values('209116','CMP','Introduction to Information Theory and Coding','丁宝康','高等教育出版社','21');
insert into books values('209117','CMP','密码学基础','Zhang Shunzhi','人民大学出版社','25');
insert into books values('209123','CMP','InternetApp and web design','李朋','高等教育出版社','16');
insert into books values('209124','CMP','多媒体技术及应用','谭浩强','高等教育出版社','15');
insert into books values('209130','CMP','PBApplication development with database','Zhang Huaqiang','清华大学出版社','42');
insert into books values('209132','CMP','计算机图形学','徐志超','人民大学出版社','27');
insert into books values('309102','CMP','Computer Science Teaching Theory','丁宝康','高等教育出版社','12');
insert into books values('309103','CMP','Research and analysis of computer textbooks for middle school','徐志超','人民大学出版社','14');
insert into books values('309104','CMP','信息技术概论','付勇','天津大学出版社','24');
insert into books values('309105','CMP','数据库分析与设计','丁宝康','人民大学出版社','30');
insert into books values('309106','CMP','数据库导论','付勇','清华大学出版社','29');


insert into borrow_info values('0034103','109101','2005-6-2 00:00:00','NULL');
insert into borrow_info values('0034301','209130','2005-6-14 00:00:00','NULL');
insert into borrow_info values('0034429','109107','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134101','109109','2005-6-3 00:00:00','NULL');
insert into borrow_info values('0134102','109110','2005-6-30 00:00:00','NULL');
insert into borrow_info values('0134103','109111','2005-6-13 00:00:00','NULL');
insert into borrow_info values('0134104','109112','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134105','109113','2005-6-14 00:00:00','NULL');
insert into borrow_info values('0134106','109114','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134107','109115','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134108','109116','2005-6-12 00:00:00','NULL');
insert into borrow_info values('0134109','109117','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134110','209101','2005-6-22 00:00:00','NULL');
insert into borrow_info values('0134111','209102','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134112','209103','2005-6-16 00:00:00','NULL');
insert into borrow_info values('0134113','209106','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134114','209107','2005-6-11 00:00:00','NULL');
insert into borrow_info values('0134115','209111','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134116','209106','2005-6-14 00:00:00','NULL');
insert into borrow_info values('0034301','209132','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0034301','309102','2005-6-9 00:00:00','NULL');
insert into borrow_info values('0034301','309103','2005-6-21 00:00:00','NULL');
insert into borrow_info values('0034301','309104','2005-6-14 00:00:00','NULL');
insert into borrow_info values('134101','109101','2005-6-1 00:00:00','NULL');
insert into borrow_info values('134102','109104','2005-6-8 00:00:00','NULL');
insert into borrow_info values('134104','109107','2005-6-1 00:00:00','NULL');
insert into borrow_info values('134105','109109','2005-6-1 00:00:00','NULL');
insert into borrow_info values('0134426','109110','2005-6-15 00:00:00','NULL');
insert into borrow_info values('0134427','109111','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134428','109112','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134429','109113','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134430','109114','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134431','109115','2005-6-1','NULL');
insert into borrow_info values('0134432','109116','2005-6-14','NULL');
insert into borrow_info values('0134433','109117','2005-6-1','NULL');
insert into borrow_info values('0134434','209101','2005-6-2','NULL');
insert into borrow_info values('0134435','209102','2005-6-1','NULL');
insert into borrow_info values('0134430','209103','2005-6-5','NULL');
insert into borrow_info values('0134430','209106','2005-6-5','NULL');
insert into borrow_info values('0134430','209107','2005-6-21','NULL');
insert into borrow_info values('0134430','209111','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134430','209116','2005-6-5 00:00:00','NULL');
insert into borrow_info values('0134430','209124','2005-6-5 00:00:00','NULL');

-- 1、检索读者“杨凡”所在单位
select dept from readers where name='杨凡';

-- 2、Retrieve all information for all readers
select * from readers;

-- 3、Search the titles and publishers of all books in the library
select book_name,publisher from books;

-- 4、检索“人民大学出版社”All titles and unit prices,The results are sorted in descending order by unit price
select book_name,price from books where publisher='人民大学出版社' order by price desc;

-- 5、检索价格在10元至15The name of the book between the meta、作者、Unit price and classification number,The results are sorted in ascending order by category number and unit price
select book_name,author,price,type_id from books order by type_id,price;

-- 6、检索“人民大学出版社”和“清华大学出版社”The names and authors of all books in 
select book_name,author from books where publisher in('人民大学出版社','清华大学出版社');

-- 7、Search for book titles starting with “数据库”The title and author of all books that start with
select book_name,author from books where book_name like '数据库%';

-- 8、The search also borrowed the total number for209116和209124Library card numbers for both books
select reader_id from borrow_info where book_id=209116;

select * from borrow_info where  book_id=209124 and reader_id=(select reader_id from borrow_info where book_id=209116);

-- 9、Retrieve the names and affiliations of all readers who have borrowed books
select name,dept from readers;

-- 10、检索“扬凡”Titles and dates of all books borrowed
select reader_id from readers where name='杨凡'; 

select   bk.book_name,br.borrow_time from borrow_info br join books bk on br.book_id=bk.book_id
where br.reader_id =  (select reader_id from readers where name='杨凡');

-- 11、检索价格在20Books that cost more than RMB and have been loaned,The results are sorted in descending order by unit price
select distinct bk.book_name,bk.price from books bk join borrow_info  br on bk.book_id = br.book_id where bk.price>20
order by bk.price desc;


-- 12、检索借阅了“C语言程序设计”The name and affiliation of the reader of the book

select book_id from books where book_name='C语言程序设计';

select r.name,r.address from readers r join borrow_info br on r.reader_id = br.reader_id where br.book_id=(select book_id from books where book_name='C语言程序设计');

-- 13、检索与“杨凡”The name and affiliation of the reader who borrowed the book on the same day
-- a)Yang Fan borrowed time
select br.borrow_time from borrow_info br join readers r on br.reader_id = r.reader_id where r.name='杨凡';

select distinct r.name,r.dept from readers r join borrow_info br on br.reader_id = r.reader_id where br.borrow_time in
(select br.borrow_time from borrow_info br join readers r on br.reader_id = r.reader_id where r.name='杨凡') and r.name!='杨凡';

-- 14、Search the collection of Bibliography“高等教育出版社”Books with a higher unit price for all books
-- a)Find the maximum price for a book from a higher education press
select max(price) from books where publisher = '高等教育出版社';

select book_name from books where price >(select max(price) from books where publisher = '高等教育出版社');

-- 15、Search all and all in the collection“数据库导论”或“数据库原理”Books published by the same publisher

select publisher from books where book_name in('数据库导论','数据库原理');

select book_name from books where publisher in(select publisher from books where book_name in('数据库导论','数据库原理'));


-- 16、Find the total number of books in the library

select count(*) from books;

-- 17、求“高等教育出版社”The highest price of any book、Lowest price and average price
select max(price),min(price),avg(price) from books where publisher='高等教育出版社';

-- 18、求“计算机学院”The number of readers who currently have books on loan
select dept,count(*) from readers r join borrow_info br on r.reader_id=br.reader_id where dept='计算机学院';

-- 19、Find the highest price for each publisher、最低价格、平均价格
select max(bk.price),min(bk.price),avg(bk.price),bk.publisher from books bk
group by bk.publisher;

-- 20、Find the number of readers currently borrowing books for each unit
select dept,count(*) from readers join borrow_info on readers.reader_id=borrow_info.reader_id  group by dept;

-- 21、Ask for the booklet of each publisher、价格总额,And sort by total price in descending order,If the total price is the same, they are sorted in descending order by publisher name
select publisher,count(*),sum(price) from books group by publisher order by sum(price) desc,publisher desc;

-- 22、Retrieval is currently at least borrowed5The name and affiliation of the reader of this book

-- a)求超过5person's name
select r.name from readers r join borrow_info br on r.reader_id=br.reader_id group by r.name having count(br.book_id)>=5;

-- b)
select name,dept from readers where name in(select r.name from readers r join borrow_info br on r.reader_id=br.reader_id group by r.name having count(br.book_id)>=5);


-- 23、Find out more than the number of borrowers10The unit and number of individuals
select r.dept,count(distinct r.reader_id) from readers r join borrow_info br on r.reader_id=br.reader_id
group by r.dept having count(r.reader_id)>10;

-- 24、Retrieve the names and affiliations of readers who have not borrowed any books
select distinct r.name,r.dept from readers r join borrow_info br on r.reader_id = br.reader_id;

select r.name,r.dept from readers r where r.name not in(select distinct r.name from readers r join borrow_info br on r.reader_id = br.reader_id);