56、mysql的作业

蔚落 2024-03-30 13:54 117阅读 0赞

作业中要用到的两张表dept，emp，找不到原来的sql语句了，所以只有表的内容

![e4b93f3df1e9436f9589519c039b1cf6.png][]![a7311bb50dac4b978b123a2ff8a92227.png][]

**1、选择题**

> （1）以下哪条语句是错误的？
> 
> A. SELECT empno, ename name, sal salary FROM emp;
> 
> B. SELECT empno, ename name, sal AS salary FROM emp;
> 
> C. SELECT ename, sal\*12 AS "Annual Salary" FROM emp;
> 
> D. SELECT ename, sal\*12 Annual Salary FROM emp;

解析：选D

A和B，"ename name"中间省略了"as"

C，"Annual Salary"是别名

D，Annual Salary 默认别名是Annual，而Salary被当成列名，会报错，把它们括起来就行了

--------------------

> （2）某用户希望显示补助非空的所有雇员信息，应该使用哪条语句？
> 
> A. SELECT ename, sal, comm FROM emp WHERE comm<>null；
> 
> B. SELECT ename, sal, comm FROM emp WHERE comm IS NOT null；
> 
> C. SELECT ename, sal, comm FROM emp WHERE comm<>0;

解析：选B

A，<>是不等于的意思

C，<>0 表示不等于0

--------------------

> （3）以下哪条语句是错误的？
> 
> A. SELECT ename, sal salary FROmp ORDER BY sal；
> 
> B. SELECT ename, sal salary FROM emp ORDER BY salary;
> 
> C. SELECT ename, sal salary FROM emp ORDER BY 3;

解析：选C

C，“3”无法识别

--------------------

**2．写出查看DEPT表和EMP表的结构的sql语句**

![30b53bd422e54c389aaf4bde5746c462.png][]

--------------------

**3．使用简单查询语句完成：**

**（1）显示所有部门名称。**

**（2） 显示所有雇员名及其全年收入 13月（工资＋补助），并指定列别名＂年收入＂**

![338dc66191344a4a8133f57af0d0c7bd.png][]

--------------------

**4．限制查询数据**

**（1）显示工资超过2850的雇员姓名和工资。**

**（2）显示工资不在1500到2850之间的所有雇员名及工资。**

**（3）显示编号为7566的雇员姓名及所在部门编号。**

**（4） 显示部门10和30中工资超过1500的雇员名及工资。**

**（5）显示无管理者的雇员名及岗位。**

![f327c014b7654432a474b123ba60ee78.png][]

--------------------

**5．排序数据**

**（1） 显示在1991年2月1日到1991年5月1日之间雇用的雇员名，岗位及雇佣日期，并以雇佣日期进行排序。**

**（2） 显示获得补助的所有雇员名，工资及补助，并以工资降序排序**

![4739c23dadf143d8b3b6e4ba7a239ab6.png][]

--------------------

**6. 根据： emp员工表写出正确SQL**

*  **选择部门30中的所有员工.**
 *  **列出所有办事员（CLERK）的姓名，编号和部门编号.**
 *  **找出佣金高于薪金的员工.**
 *  **找出佣金高于薪金60%的员工.**
 *  **找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料.**
 *  **找出部门10中所有经理（MANAGER），部门20中所有办事员（CLERK），还有既不是经理又不是办事员，但其薪金大于或等于2000的所有员工的详细资料.**
 *  **找出收取佣金的员工的不同工作.**
 *  **找出不收取佣金或收取的佣金低于100的员工**
 *  **找出各月倒数第3天受雇的所有员工**
 *  **找出早于12年前受雇的员工.**
 *  **以首字母小写的方式显示所有员工的姓名.**
 *  **显示正好为5个字符的员工的姓名.**

#选择部门30中的所有员工.
    SELECT * FROM 
    	emp
    	WHERE deptno=30
    	
    #列出所有办事员（CLERK）的姓名，编号和部门编号.
    SELECT ename,empno,deptno,job
    	FROM emp
    	WHERE job='CLERK'
    	
    #找出佣金高于薪金的员工.
    SELECT * FROM
    	emp
    	WHERE IFNULL(comm,0)>sal
    
    #找出佣金高于薪金60%的员工.
    SELECT * FROM
    	emp
    	WHERE IFNULL(comm,0)>sal*0.6
    
    	
    #找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料.
    SELECT * FROM 
    	emp
    	WHERE (deptno=10 AND job='MANAGER') OR 
    		(deptno=20 AND job='CLERK')
    
    #找出部门10中所有经理（MANAGER），部门20中所有办事员（CLERK），还有既不是经理又不是办事员，但其薪金大于或等于2000的所有员工的详细资料.
    SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno
    	FROM emp
    	WHERE (deptno=10 AND job='MANAGER') OR 
    		(deptno=20 AND job='CLERK') OR
    		(job<>'MANAGER' AND job<>'CLERK' AND sal>=2000)
    
    #找出收取佣金的员工的不同工作.
    SELECT DISTINCT job #去重
    	FROM emp
    	WHERE comm IS NOT NULL
    
    #找出不收取佣金或收取的佣金低于100的员工
    SELECT ename
    	FROM emp
    	WHERE comm IS NULL OR IFNULL(comm,0)<100
    	
    #找出各月倒数第3天受雇的所有员工
    -- last_day(日期)，可以返回该日期所在月份的最后一天
    SELECT *
    	FROM emp
    	WHERE LAST_DAY(hiredate)-2=hiredate
    	
    #找出早于12年前受雇的员工，即：入职时间超过12年
    SELECT *
    	FROM emp
    	WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW()
    	
    #以首字母小写的方式显示所有员工的姓名.
    SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2))
    	FROM emp
    	
    #显示正好为5个字符的员工的姓名.
    SELECT *
    	FROM emp
    	WHERE LENGTH(ename)=5

*  显示不带有＂R＂的员工的姓名．
 *  显示所有员工姓名的前三个字符．
 *  显示所有员工的姓名，用a替换所有＂A＂
 *  显示满10年服务年限的员工的姓名和受雇日期．
 *  显示员工的详细资料，按姓名排序．
 *  显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面．
 *  显示所有员工的姓名、工作和薪金，按工作降序排序，若工作相同则按薪金排序．
 *  显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面.
 *  显示在一个月为30天的情况所有员工的日薪金，忽略余数．
 *  找出在（任何年份的）2月受聘的所有员工。
 *  对于每个员工，显示其加入公司的天数．
 *  显示姓名字段的任何位置包含＂A”的所有员工的姓名．
 *  以年月日的方式显示所有员工的服务年限．（大概）

-- 显示不带有＂R＂的员工的姓名．
    #我的答案：
    SELECT ename
    	FROM emp
    	WHERE INSTR(ename,'R')=0
    #老师的答案：
    SELECT *
    	FROM emp
    	WHERE ename NOT LIKE '%R%'
    	
    -- 显示所有员工姓名的前三个字符．
    #我的答案：
    SELECT SUBSTRING(ename,1,3)
    	FROM emp
    #老师的答案：
    SELECT LEFT(ename,3)
    	FROM emp
    
    	
    -- 显示所有员工的姓名，用a替换所有＂A＂
    #我的答案：
    SELECT REPLACE(INSTR(ename,'A'),'a')
    	FROM emp
    #老师的答案：
    SELECT REPLACE(ename,'A','a')
    	FROM emp
    
    -- 显示满10年服务年限的员工的姓名和受雇日期．
    SELECT hiredate,ename
    	FROM emp
    	WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR) < NOW()
    
    -- 显示员工的详细资料，按姓名排序．
    SELECT *
    	FROM emp
    	ORDER BY ename
    	
    -- 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面．
    SELECT ename,hiredate
    	FROM emp
    	ORDER BY hiredate
    	
    -- 显示所有员工的姓名、工作和薪金，按工作降序排序，若工作相同则按薪金排序．
    #我的答案：运行失败
    SELECT ename,job,sal
    	FROM emp
    	ORDER BY job
    UNION
    SELECT ename,job,sal
    	FROM emp
    	GROUP BY job
    	ORDER BY sal
    #老师的答案：
    SELECT ename,job,sal
    	FROM emp
    	ORDER BY job DESC,sal
    		
    
    -- 显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面.
    #我的答案：运行失败
    SELECT ename,hiredate
    	FROM emp
    	ORDER BY MONTH(hiredate)
    
    SELECT ename,hiredate
    	FROM (
    	SELECT ename,hiredate
    		FROM emp
    		ORDER BY MONTH(hiredate))
    	ORDER BY YEAR(hiredate)
    #老师的答案：
    SELECT ename,CONCAT(YEAR(hiredate),'-',MONTH(hiredate))
    	FROM emp
    	ORDER BY MONTH(hiredate),YEAR(hiredate)
    
    
    -- 显示在一个月为30天的情况所有员工的日薪金，忽略余数．
    SELECT ename,FLOOR(sal/30)
    	FROM emp
    
    -- 找出在（任何年份的）2月受聘的所有员工。
    SELECT *
    	FROM emp
    	WHERE MONTH(hiredate)=2
    	
    -- 对于每个员工，显示其加入公司的天数．
    SELECT ename,DATEDIFF(NOW(),hiredate)
    	FROM emp
    	
    -- 显示姓名字段的任何位置包含＂A”的所有员工的姓名．
    SELECT *
    	FROM emp
    	WHERE ename LIKE '%A%'
    
    -- 以年月日的方式显示所有员工的服务年限．（这题有点懵）
    #老师的答案：
    #1. 先求出工作了多少天
    SELECT ename,DATEDIFF(NOW(),hiredate)
    	FROM emp
    #2. 
    SELECT ename,FLOOR(DATEDIFF(NOW(),hiredate)/365) AS '工作年',
    	FLOOR((DATEDIFF(NOW(),hiredate)%365)/31) AS '工作月',
    	DATEDIFF(NOW(),hiredate) % 31 AS '工作天'
    	FROM emp

**7、 根据：emp员工表，dept部门表，工资 ＝ 薪金 ＋ 佣金 写出正确SQL**

*  **列出至少有一个员工的所有部门**
 *  **列出薪金比“SMITH”多的所有员工。**
 *  **列出受雇日期晚于其直接上级的所有员工。**
 *  **列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。**
 *  **列出所有“CLERK”（办事员）的姓名及其部门名称。**
 *  **列出最低薪金大于1500的各种工作。**
 *  **列出在部门“SALES”（销售部）工作的员工的姓名。**
 *  **列出薪金高于公司平均薪金的所有员工。**

-- 列出至少有一个员工的所有部门
    #我的答案：
    SELECT DISTINCT dname
    	FROM emp,dept
    	WHERE emp.deptno=dept.deptno
    #老师的答案：
    -- 先查出各个部门有多少人，后使用having子句过滤
    SELECT COUNT(*) AS c,deptno
    	FROM emp
    	GROUP BY deptno
    	HAVING c>1
    	
    -- 列出薪金比“SMITH”多的所有员工。
    # 先查出smith的sal=>作为子查询，后其它员工sal>smith即可
    SELECT *
    	FROM emp
    	WHERE sal > (
    		SELECT sal
    		FROM emp
    		WHERE ename='SMITH')
    	
    # 列出受雇日期晚于其直接上级的所有员工。
    -- 先把emp表当作两张表worker,leader
    -- 条件：1、worker.hriedate > leader.hiredate
    --       2、worker.mgr = leader.empno
    SELECT worker.ename AS'员工名',worker.hiredate AS '员工入职时间',
    	leader.ename AS '上级名',leader.hiredate AS '上级入职时间'
    	FROM emp worker,emp leader
    	WHERE worker.hiredate > leader.hiredate
    	AND worker.mgr = leader.empno
    	
    -- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
    #因为要显示所有部门，因此考虑使用外连接（左外连接）
    SELECT dname,emp.*
    	FROM dept
    	LEFT JOIN emp ON dept.deptno = emp.deptno
    	
    -- 列出所有“CLERK”（办事员）的姓名及其部门名称。
    SELECT ename,dname,job
    	FROM emp,dept
    	WHERE job='CLERK' AND emp.deptno = dept.deptno
    	
    -- 列出最低薪金大于1500的各种工作。
    SELECT MIN(sal) AS min_sal,job
    	FROM emp
    	GROUP BY job
    	HAVING min_sal > 1500
    	
    -- 列出在部门“SALES”（销售部）工作的员工的姓名。
    SELECT ename,dname
    	FROM emp,dept
    	WHERE dname='SALES' AND emp.deptno = dept.deptno #总是漏掉AND后面的部分
    	
    -- 列出薪金高于公司平均薪金的所有员工。
    SELECT *
    	FROM emp
    	WHERE sal>(
    		SELECT AVG(sal)
    			FROM emp)

--------------------

*  列出与“SCOTT”从事相同工作的所有员工。
 *  列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
 *  列出在每个部门工作的员工数量、平均工资和平均服务期限。
 *  列出所有员工的姓名、部门名称和工资。
 *  列出所有部门的详细信息和部门人数。
 *  列出各种工作的最低工资。
 *  列出MANAGER（经理）的最低薪金。
 *  列出所有员工的年工资，按年薪从低到高排序。

-- 列出与“SCOTT”从事相同工作的所有员工。
    SELECT *
    	FROM emp
    	WHERE job=(
    		SELECT job
    			FROM emp
    			WHERE ename='SCOTT')
    	
    -- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
    SELECT ename,sal
    	FROM emp
    	WHERE sal>ALL(
    		SELECT sal
    			FROM emp
    			WHERE deptno=30)
    
    -- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
    SELECT COUNT(*)AS'部门员工数量',AVG(sal)AS'部门平均工资',
    	FORMAT(AVG(DATEDIFF(NOW(),hiredate) / 365), 2 ) AS '平均服务期限（年）'
    	FROM emp
    	GROUP BY deptno
    
    -- 列出所有员工的姓名、部门名称和工资。
    #就是emp和dept的联合查询，连接条件就是emp.deptno=dept.deptno
    SELECT ename,dname,sal
    	FROM emp,dept
    	WHERE emp.deptno=dept.deptno
    	
    -- 列出所有部门的详细信息和部门人数。
    #1、先得到各个部门人数，把下面的结果看成临时表和dept表联合查询
    SELECT COUNT(*) AS c,deptno
    	FROM emp
    	GROUP BY deptno
    #2、联合
    SELECT dept.*,tmp.c
    	FROM dept,(
    		SELECT COUNT(*) AS c,deptno
    			FROM emp
    			GROUP BY deptno
    	) tmp
    	WHERE tmp.deptno=dept.deptno 
    	
    
    -- 列出各种工作的最低工资。
    SELECT MIN(sal),job
    	FROM emp
    	GROUP BY job
    	
    -- 列出MANAGER（经理）的最低薪金。
    SELECT MIN(sal),job
    	FROM emp
    	WHERE job='MANAGER'
    	
    -- 列出所有员工的年工资，按年薪从低到高排序。
    SELECT ename,(sal+IFNULL(comm,0))*12 AS year_sal
    	FROM emp
    	ORDER BY year_sal

**8、设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。现要建立关于系、学生、班级的数据库，关系模式为：**

*  **班CLASS（班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num）** 
 *  **学生STUDENT（学号studentid，姓名name，年龄age，班号classid）**
 *  **系 DEPARTMENT（系号departmentid，系名deptname）**

**试用SQL语言完成以下功能：**

*  **建表，在定义中要求声明： （1）每个表的主外码。 （2）deptname是唯一约束。 （3）学生姓名不能为空。**
 *  **插入数据 DEPARTMENT (001,数学； 002, 计算机； 003, 化学； 004, 中文； 005， 经济；）**
 *  **完成以下查询功能： （1）找出所有姓李的学生。 （2）列出所有开设超过1个专业的系的名字。 （3）列出人数大于等于30的系的编号和名字。**
 *  **学校又新增加了一个物理系，编号为006**
 *  **学生张三退学，请更新相关的表**

**![1d4561cca4384aa39f67c23a515f709e.png][]**![838abe26cd394b7a99e6df0c973f93f4.png][]

\#我的答案：

#建class表
    CREATE TABLE class(
    	classid INT PRIMARY KEY,
    	`subject` VARCHAR(32),
    	deptname VARCHAR(32),
    	enrolltime INT,
    	num INT)
     DROP TABLE class
    #插入数据
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(101,'软件','计算机',1995,20)
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(102,'微电子','计算机',1996,30)
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(111,'无机化学','化学',1995,29)
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(112,'高分子化学','化学',1996,25)
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(121,'统计数学','数学',1995,20)
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(131,'现代语言','中文',1996,20)
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(141,'国际贸易','经济',1997,30)
    INSERT INTO class(classid,`subject`,deptname,enrolltime,num)
    	VALUES(142,'国际金融','经济',1996,14)
    
    SELECT * FROM class	
    
    #建student表
    CREATE TABLE student(
    	studentid INT PRIMARY KEY,
    	`name` VARCHAR(32) NOT NULL,
    	age INT,
    	classid INT)
    #插入数据
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8101,'张三',18,101)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8102,'钱四',16,121)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8103,'王玲',17,131)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8105,'李飞',19,102)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8109,'赵四',18,141)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8110,'李可',20,142)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8201,'张飞',18,111)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8302,'周瑜',16,112)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8203,'王亮',17,111)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8305,'董庆',19,102)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8409,'赵龙',18,101)
    INSERT INTO student(studentid,`name`,age,classid)
    	VALUES(8510,'李丽',20,142)
    
    SELECT * FROM student
    
    #建表department
    CREATE TABLE department(
    	departmentid INT PRIMARY KEY,
    	deptname VARCHAR(32) UNIQUE)
    	
    INSERT INTO department(departmentid,deptname)
    	VALUES(001,'数学')
    INSERT INTO department(departmentid,deptname)
    	VALUES(002,'计算机')
    INSERT INTO department(departmentid,deptname)
    	VALUES(003,'化学')
    INSERT INTO department(departmentid,deptname)
    	VALUES(004,'中文')
    INSERT INTO department(departmentid,deptname)
    	VALUES(005,'经济')
    	
    SELECT * FROM department
    
    
    -- 找出所有姓李的学生。  
    SELECT *
    	FROM student
    	WHERE `name` LIKE '李%'  
    	
    -- 列出所有开设超过1个专业的系的名字。       
    SELECT deptname
    	FROM class
    	GROUP BY deptname
    	WHERE COUNT(*)>1
    	
    	
    -- 列出人数大于等品0的系的编号和名字。
    SELECT departmentid,deptname
    	FROM department
    	WHERE (
    	SELECT COUNT(`name`)
    		FROM student
    		GROUP BY deptname
    		)>0
    	
    -- 学校又新增加了一个物理系，编号为006	
    INSERT INTO department(departmentid,deptname)
    	VALUES(006,'物理')
    	
    -- 学生张三退学，请更新相关的表
    DELETE FROM student
    	WHERE `name`='张三'

\#老师的答案：

#建class表
    CREATE TABLE class(
    	classid INT PRIMARY KEY,
    	`subject` VARCHAR(32)NOT NULL DEFAULT'',
    	deptname VARCHAR(32),
    	enrolltime INT NOT NULL DEFAULT 2000,
    	num INT NOT NULL DEFAULT 0,
    	FOREIGN KEY(deptname) REFERENCES DEPARTMENT(deptname));
    #插入数据
    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);
    #建student表
    CREATE TABLE student(
    	studentid INT PRIMARY KEY,
    	`name` VARCHAR(32) NOT NULL DEFAULT'',
    	age INT NOT NULL DEFAULT 0,
    	classid INT,
    	FOREIGN KEY(classid) REFERENCES `class`(classid));
    #插入数据
    INSERT INTO student VALUES(8101,'张三',18,101);
    INSERT INTO student VALUES(8102,'钱四',16,121);
    INSERT INTO student VALUES(8103,'王玲',17,131);
    INSERT INTO student VALUES(8105,'李飞',19,102);
    INSERT INTO student VALUES(8109,'赵四',18,141);
    INSERT INTO student VALUES(8110,'李可',20,142);
    INSERT INTO student VALUES(8201,'张飞',18,111);
    INSERT INTO student VALUES(8302,'周瑜',16,112);
    INSERT INTO student VALUES(8203,'王亮',17,111);
    INSERT INTO student VALUES(8305,'董庆',19,102);
    INSERT INTO student VALUES(8409,'赵龙',18,101);
    INSERT INTO student VALUES(8510,'李丽',20,142);
    #建表department
    CREATE TABLE department(
    	departmentid VARCHAR(32) PRIMARY KEY,
    	deptname VARCHAR(32) UNIQUE NOT NULL);
    #插入数据	
    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,'经济');
    #查看表
    SELECT * FROM class		
    SELECT * FROM student
    SELECT * FROM department
    
    -- 找出所有姓李的学生。  
    SELECT *
    	FROM student
    	WHERE `name` LIKE '李%'  
    	
    -- 列出所有开设超过1个专业的系的名字。
    -- 1、先查询各个系有多少个专业
    SELECT COUNT(*) AS nums,deptname
    	FROM class
    	GROUP BY deptname 
    	HAVING nums>1  
    	
    -- 列出人数大于等于30的系的编号和名字。
    -- 1、先查出各个系有多少人
    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;
    
    -- 学校又新增加了一个物理系，编号为006	
    INSERT INTO department VALUES(006,'物理')
    	
    -- 学生张三退学，请更新相关的表
    -- 1、张三所在班级的人数-1，2、将张三从学生表删除，3、需要使用事务控制
    
    -- 开启事务
    START TRANSACTION
    -- 张三所在班级的人数-1
    UPDATE class SET num=num-1
    	WHERE classid=(
    		SELECT classid
    			FROM student
    			WHERE `name`='张三');
    -- 将张三从学生表删除			
    DELETE
    	FROM student
    	WHERE `name`='张三';
    -- 提交事务
    COMMIT;

[e4b93f3df1e9436f9589519c039b1cf6.png]: https://img-blog.csdnimg.cn/e4b93f3df1e9436f9589519c039b1cf6.png
[a7311bb50dac4b978b123a2ff8a92227.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/0ce4f5793c224276b871ec14b154897f.png
[30b53bd422e54c389aaf4bde5746c462.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bf3cf213c01947ba86dde78c8dc49391.png
[338dc66191344a4a8133f57af0d0c7bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/21b7713040b3423bb60a80867aa0226d.png
[f327c014b7654432a474b123ba60ee78.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/e0f8037897f1400aa8ac5fefdbbf388b.png
[4739c23dadf143d8b3b6e4ba7a239ab6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/2b5d7bc4847d4b058c313563324797d0.png
[1d4561cca4384aa39f67c23a515f709e.png]: https://img-blog.csdnimg.cn/1d4561cca4384aa39f67c23a515f709e.png
[838abe26cd394b7a99e6df0c973f93f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/201e176b705848e5a467637e714d108b.png