浙江龙网

  1. 首页 > 生活百科 > >

day10-习题( 三 )

生活百科day10-习题

6.Homework06根据emp员工表，dept部门表，工资=薪金+佣金 ， 写出正确的sql

  1. 列出与SCOTT从事相同工作的所有员工
  2. 列出薪金高于 ， 在部门30工作的所有员工的薪金 ， 的员工姓名和薪金
  3. 列出在每个部门工作的员工数量，平均工资和平均服务期限
  4. 列出所有员工的姓名，部门名称和工资
  5. 列出所有部门的详细信息和部门人数
  6. 列出各种工作的最低工资
  7. 列出MANAGER(经理)的最低薪金
  8. 列出所有员工的年工资，按年薪从低到高排序
SELECT * FROM emp;SELECT * FROM dept;SELECT * FROM salgrade;-- 1. 列出与SCOTT从事相同工作的所有员工SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SCOTT');-- 2. 列出薪金高于，在部门30工作的所有员工的薪金，的员工姓名和薪金-- 先查询出30号部门的最高工资SELECT ename ,sal FROM emp WHERE sal>(SELECT MAX(sal) FROM empWHERE deptno = 30)-- 3. 列出在每个部门工作的员工数量，平均工资和平均服务期限SELECT COUNT(*) AS '员工数量', deptno, AVG(sal+IFNULL(comm,0)) AS '部门平均工资', AVG(DATEDIFF(NOW(),hiredate)/365) AS '平均服务期限年' FROM emp GROUP BY deptno-- 4. 列出所有员工的姓名，部门名称和工资SELECT ename,dname,sal+IFNULL(comm,0) FROM emp,dept WHERE emp.deptno= dept.deptno-- 5. 列出所有部门的详细信息和部门人数-- 先得到各个部门的平均人数SELECT dept.*, `all` FROM dept LEFT JOIN(SELECT COUNT(*) AS `all`,deptnoFROM empGROUP BY emp.deptno) AS temp ON dept.deptno=temp.deptno-- 6. 列出各种工作的最低工资SELECT job ,MIN(sal+IFNULL(comm,0))AS '最低工资' FROM emp GROUP BY job;-- 7. 列出MANAGER(经理)的最低薪金SELECT MIN(sal+IFNULL(comm,0))AS '最低工资' FROM emp WHERE job= 'MANAGER';-- 8. 列出所有员工的年工资，按年薪从低到高排序SELECT ename,(sal+IFNULL(comm,0))*12 AS '年工资' FROM emp ORDER BY (sal+IFNULL(comm,0));7.Homework07设学校环境如下：一个系有若干个专业，每一个专业一年只招一个班，每个班有若干个学生 。先要建立关于系、学生和班级的数据库，关系模式为：
班CLASS(班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num)
学生STUDENT（学号studentid，姓名name，年龄age，班号classid）
系DEPARTMENT（系号departmentid，系名deptname）
试用sql语言完成下面功能：
  1. 建表 ， 在定义中要求声明：
    • 每个表的主外键
    • deptname是唯一约束
    • 学生姓名不能为空
  2. 插入如下数据
    DEPARTMENT(001,数学;002,计算机;003,计算机;004,计算机;005,计算机; )CLASS(101,软件,计算机,1995,20;102,微电子,计算机,1996,30;111,无机化学,化学,1995,29;112,高分子化学,化学,1996,25;121,统计数学,数学,1995,20;131,现代语言,中文,1996,20;141,国际贸易,经济,1997,30;142,国际金融,经济,1996,14;)STUDENT(8101,张三,18,101;8102,钱四,16,121;8103,王玲,17,131;8105,李飞,19,102;8109,赵四,18,141;8110,李可,20,142;8201,张飞,18,111;8302,周瑜,16,112;8203,王亮,17,111;8305,董庆,19,102;8409,赵龙,18,101;8510,李丽,20,142;)
  3. 完成以下查询功能
    1. 找出所有姓李的学生
    2. 列出所有开设超过1个专业的系的名字
    3. 列出人数大于等于30的系的编号和名字
  4. 学校又新增了一个物理系 ， 编号为006
  5. 学生张三退学 ， 请更新相关的表
-- 试用sql语言完成下面功能：-- 1. 建表，在定义中要求声明：--- 每个表的主外键--- deptname是唯一约束--- 学生姓名不能为空-- 系DEPARTMENT（系号departmentid ， 系名deptname）CREATE TABLE DEPARTMENT( departmentid VARCHAR(32) PRIMARY KEY, deptname VARCHAR(32) UNIQUE NOT NULL);-- 班CLASS(班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num)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));-- 学生STUDENT（学号studentid，姓名name，年龄age，班号classid）CREATE TABLE hsp_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));-- 2. 插入如下数据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);INSERT INTO hsp_STUDENT VALUES(8510,'李丽',20,142);SELECT * FROM DEPARTMENT;SELECT * FROM CLASS;SELECT * FROM hsp_STUDENT;-- 3. 完成以下查询功能--3.1 找出所有姓李的学生SELECT * FROM hsp_STUDENT WHERE `name` LIKE '李%';--3.2 列出所有开设超过1个专业的系的名字SELECT deptname,COUNT(*) AS nums FROM CLASS GROUP BY deptname HAVING nums > 1;--3.3 列出人数大于等于30的系的编号和名字-- 先查出每个系有多少人-- 将上面的结果看成是一个临时表，与DEPARTMENT表进行联合查询即可SELECT temp.*,departmentidFROM DEPARTMENT,(SELECT SUM(num) AS nums,deptnameFROM CLASSGROUP BY deptnameHAVING nums>=30) AS temp WHERE DEPARTMENT.deptname=temp.deptname;-- 4. 学校又新增了一个物理系 ， 编号为006INSERT INTO department VALUES('006','物理系');-- 5. 学生张三退学，请更新相关的表-- 使用事务处理START TRANSACTION ;-- 5.1张三所在的班级人数要减1UPDATE class SET num=num-1 WHERE classid =( SELECT classidFROM hsp_STUDENTWHERE `name` = '张三' );-- 5.2将张三从学生表中删除DELETE FROM hsp_STUDENT WHERE `name` = '张三';-- 提交事务COMMIT;

推荐阅读


上一篇：KMP模式匹配 学习笔记

下一篇：springboot 多线程的使用