30天学会JAVA—练习题(2021韩顺平)——Day20

数据库练习题

-- 班级表
CREATE TABLE CLASS(
classid VARCHAR(3) PRIMARY KEY,
`subject` VARCHAR(20) NOT NULL DEFAULT '',
deptname VARCHAR(20) NOT NULL DEFAULT '',
enrolltime YEAR NOT NULL DEFAULT 9,
num INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname)
);

-- 学生表
CREATE TABLE STUDENTS(
studentid VARCHAR(4) PRIMARY KEY,
`name` VARCHAR(20) NOT NULL DEFAULT '',
age INTEGER NOT NULL DEFAULT 0,
classid VARCHAR(3),
FOREIGN KEY (classid) REFERENCES CLASS(classid));


-- 系表
CREATE TABLE DEPARTMENT(
departmentid VARCHAR(4) PRIMARY KEY,
deptname VARCHAR(20) UNIQUE NOT NULL DEFAULT '');

-- 插入数据
INSERT INTO DEPARTMENT VALUES(001,'数学');
INSERT INTO DEPARTMENT VALUES(002,'计算机');
INSERT INTO DEPARTMENT VALUES(003,'化学');
INSERT INTO DEPARTMENT VALUES(004,'中文');
INSERT INTO DEPARTMENT VALUES(005,'经济');

INSERT INTO class VALUES(101,'软件','计算机',1995,20);
INSERT INTO class VALUES(102,'微电子','计算机',1996,30);
INSERT INTO class VALUES(111,'无机化学','化学',1995,29);
INSERT INTO class VALUES(112,'高分子化学','化学',1996,25);
INSERT INTO class VALUES(121,'统计数学','数学',1995,20);
INSERT INTO class VALUES(131,'现代语言','中文',1996,20);
INSERT INTO class VALUES(141,'国际贸易','经济',1997,30);
INSERT INTO class VALUES(142,'国际金融','经济',1996,14);

INSERT INTO hsp_student VALUES(8101,'张三',18,101);
INSERT INTO hsp_student VALUES(8102,'钱四',16,121);
INSERT INTO hsp_student VALUES(8103,'王玲',17,131);
INSERT INTO hsp_student VALUES(8105,'李飞',19,102);
INSERT INTO hsp_student VALUES(8109,'赵四',18,141);
INSERT INTO hsp_student VALUES(8110,'李可',20,142);
INSERT INTO hsp_student VALUES(8201,'张飞',18,111);
INSERT INTO hsp_student VALUES(8302,'周瑜',16,112);
INSERT INTO hsp_student VALUES(8203,'王亮',17,111);
INSERT INTO hsp_student VALUES(8305,'董庆',19,102);
INSERT INTO hsp_student VALUES(8409,'赵龙',18,101);

-- 3.1
SELECT * FROM STUDENTS
	WHERE `name` LIKE '李%'
	
-- 3.2
SELECT COUNT(DISTINCT subject) AS nums, deptname FROM class
	GROUP BY deptname 
	HAVING nums > 1
	
-- 3.3 列出人数大于等于30的系的编号和名字。
-- 1. 先查出各个系有多少人, 并得到 >= 30 的系
SELECT SUM(num) AS nums, deptname  FROM class 
	GROUP BY  deptname 
	HAVING nums >= 30
	
-- 2. 将上面的结果看成一个临时表 和 department 联合查询即可
SELECT  tmp.*, department.departmentid
	FROM department , (
		SELECT SUM(num) AS nums, deptname  
		FROM class 
		GROUP BY  deptname 
		HAVING nums >= 30
	) tmp 
	WHERE department.deptname = tmp.deptname;
	
-- (4) 学校又新增加了一个物理系，编号为006
-- 添加一条数据
INSERT INTO department VALUES('006','物理系');

-- (5) 学生张***学，请更新相关的表
-- 分析：1. 张三所在班级的人数-1 
-- 2. 将张三从学生表删除 
-- 3. 需要使用事务控制
-- 开启事务
START TRANSACTION;
-- 张三所在班级的人数-1 
UPDATE class SET num = num - 1
	WHERE classid = (
		SELECT classid FROM students 
			WHERE NAME = '张三'
	);

DELETE FROM students
	WHERE NAME = '张三';
	
-- 提交事务
COMMIT;