day10-习题

习题

1.Homework01

1. (1)        D -- 没有在别名上加引号（ps：别名的as可以省略）
2. (2)        B -- 判断null或非空不能用不等于号
3. (3)        C

复制代码

2.Homework02

• 写出查看dept表和emp表的结构的sql语句
  
• 使用简单查询语句完成
  
  
  
  • 显示所有部门名称
    
  • 显示所有雇员名及其全年收入 13月（工资+补助），并指定列别名“年收入”
    
  
  
• 限制查询数据
  
  
  
  • 显示工资超过2850的雇员的姓名和工资
    
  • 显示工资不在1500到2850之间的员工名和工资
    
  • 显示编号为7566的雇员姓名及所在的部门编号
    
  • 显示部门10和30中工资超过1500的员工名及工资
    
  • 显示无管理者的雇员名及岗位
    
  
  
• 排序数据
  
  
  
  • 显示在1991-2-1到1991-5-1之间雇用的员工名，岗位以及雇用日期，并以雇用日期进行排序
    
  • 显示获得补助的所有员工名，工资以及补助，并以工资降序进行排序
    
  
  

练习

1. -- 2.写出查看dept表和emp表的结构的sql语句
2. DESC dept;
3. DESC emp;
5. -- 3.使用简单查询语句完成
6. -- 3.1 显示所有部门名称
7. SELECT dname FROM dept;
8. -- 3.2 显示所有雇员名及其全年收入 13月（工资+补助），并指定列别名“年收入”
9. SELECT ename,(sal+IFNULL(comm ,0.0))*13 AS '年收入' FROM emp;
11. -- 4.限制查询数据
12. -- 4.1显示工资超过2850的雇员的姓名和工资
13. SELECT ename,sal FROM emp
14.         WHERE sal>2850;
15. -- 4.2显示工资不在1500到2850之间的员工名和工资
16. SELECT ename,sal FROM emp
17.         WHERE sal>2850 OR sal<1500;
18. -- 4.3显示编号为7566的雇员姓名及所在的部门编号
19. SELECT ename,deptno FROM emp
20.         WHERE empno=7566;
21. -- 4.4显示部门10和30中工资超过1500的员工名及工资
22. SELECT ename,sal FROM emp
23.         WHERE deptno IN (10,30) AND sal >1500;
24. -- 4.5显示无管理者的雇员名及岗位
25. SELECT ename,job FROM emp
26.         WHERE mgr IS NULL;
28. -- 5.排序数据
29. -- 5.1显示在1991-2-1到1991-5-1之间雇用的员工名，岗位以及雇用日期，
30. -- 并以雇用日期进行排序
31. SELECT ename,job,hiredate FROM emp
32.         WHERE hiredate>='1991-02-01' AND hiredate<='1991-05-01'
33.         ORDER BY hiredate;
34. -- 显示获得补助的所有员工名，工资以及补助，并以工资降序进行排序
35. SELECT ename,sal,comm FROM emp
36.         WHERE comm IS NOT NULL
37.         ORDER BY sal DESC;

复制代码

5.Homework05

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

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

1. SELECT * FROM emp;
2. -- 1. 选择部门30中的所有员工
3. SELECT * FROM emp
4.         WHERE deptno = 30;
6. -- 2. 列出所有办事员（CLERK）的姓名，编号和部门编号
7. SELECT ename,empno,deptno FROM emp
8.         WHERE job = 'CLERK';
9.        
10. -- 3. 找出佣金高于薪金的员工
11. SELECT ename FROM emp
12.         WHERE IFNULL(comm,0)>sal;
13.        
14. -- 4. 找出佣金高于薪金60%的员工
15. SELECT ename FROM emp
16.         WHERE IFNULL(comm,0)>(sal*0.6);
17.        
18. -- 5. 找出部门10中所有经理（MANAGER）和部门20中的所有办事员（CLERK）的详细资料
19. SELECT * FROM emp
20.         WHERE (deptno = 10 AND job= 'MANAGER')OR
21.                 (deptno = 20 AND job= 'CLERK');
22.        
23. -- 6. 找出部门10中所有经理（MANAGER），部门20中的所有办事员（CLERK），
24. -- 还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料
25. SELECT * FROM emp
26.         WHERE (deptno = 10 AND job= 'MANAGER')OR
27.                 (deptno = 20 AND job= 'CLERK')OR
28.                 (job NOT IN ('MANAGER','CLERK') AND sal>=2000);
29.                
30. -- 7. 找出收取佣金的员工的不同工作
31. SELECT DISTINCT job FROM emp
32.         WHERE comm IS NOT NULL;
33.        
34. -- 8. 找出不收取佣金或收取佣金低于100的员工
35. SELECT ename FROM emp
36.         WHERE comm IS NULL OR IFNULL(comm,0) <100;
37.        
38. -- 9. 找出各月倒数第三天受雇的所有员工
39. -- 关于最后一天 last_day(日期)可以返回该日期所在月份的最后一天
40. SELECT * FROM emp
41.         WHERE LAST_DAY(hiredate)-2 = hiredate;
43. -- 10. 找出早于12年前受雇的员工
44. SELECT * FROM emp
45.         WHERE hiredate < DATE_SUB(NOW(),INTERVAL 12 YEAR);
47. -- 11. 以首字母小写的方式显示所有员工的姓名
48. -- 先截取首字母,将其小写 LCASE (LEFT(ename,1))
49. -- 再截取剩下的字母 RIGHT(ename, (LENGTH(ename)-1))
50. -- 将两者拼起来 CONCAT
51. SELECT CONCAT(LCASE(LEFT(ename,1)),RIGHT(ename, (LENGTH(ename)-1))) FROM emp       
52. -- 或者
53. SELECT CONCAT( LCASE(LEFT(ename,1)) ,SUBSTRING(ename,2)) FROM emp;
55. -- 12. 显示正好为5个字符的员工的姓名
56. SELECT ename FROM emp
57.         WHERE ename LIKE '_____';
58. -- 或者
59. SELECT ename FROM emp
60.         WHERE LENGTH(ename)=5;

复制代码

6.Homework06

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

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

1. -- 根据emp员工表，写出正确的sql语句
2. SELECT * FROM emp;
3. -- 13. 显示不带有R的员工的姓名
4. SELECT ename FROM emp
5.         WHERE ename NOT LIKE '%R%';
6.        
7. -- 14. 显示所有员工姓名的前三个字符
8. SELECT LEFT(ename,3) FROM emp;
10. -- 15. 显示所有员工的姓名，用a替换所有的A
11. SELECT REPLACE(ename,'A','a') FROM emp;
13. -- 16. 显示满10年服务年限的员工的姓名和受雇日期
14. SELECT ename,hiredate FROM emp
15.         WHERE (DATEDIFF(NOW(),hiredate)/365)>10;
16.        
17. -- 17. 显示员工的详细资料，按姓名排序
18. SELECT * FROM emp
19.         ORDER BY ename;
21. -- 18. 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面
22. SELECT ename,hiredate FROM emp
23.         ORDER BY hiredate;
25. -- 19. 显示所有的员工的姓名，工作和薪金，按工作降序排序，若工作相同则按薪金排序
26. SELECT ename,job,sal FROM emp
27.         ORDER BY job DESC,sal;
29. -- 20. 显示所有员工的姓名，加入公司的年份和月份，
30. -- 按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面
31. SELECT ename,CONCAT(YEAR(hiredate),'-',MONTH(hiredate))FROM emp
32.         ORDER BY MONTH(hiredate),YEAR(hiredate);
34. -- 21. 显示在一个月为30天的情况所有员工的日薪金，忽略余数
35. SELECT ename,FLOOR(sal/30) FROM emp
36.         WHERE LAST_DAY(hiredate) LIKE '____-__-30';
37.        
38. -- 22. 找出在（任何年份的）2月受聘的所有员工
39. SELECT * FROM emp
40.         WHERE hiredate LIKE '____-02-__';
41.        
42. -- 23. 对于每个员工，显示其加入公司的天数
43. SELECT ename,DATEDIFF(NOW(),hiredate) FROM emp;
45. -- 24. 显示姓名字段的任何位置包含A的所有员工的姓名
46. SELECT ename FROM emp
47.         WHERE ename LIKE '%A%';
49. -- 25. 以年月日的方式显示所有员工的服务年限（大概）
50. SELECT ename,
51.         (DATEDIFF(NOW(),hiredate)-DATEDIFF(NOW(),hiredate)%365)/365 AS '年',
52.         (DATEDIFF(NOW(),hiredate)%365-DATEDIFF(NOW(),hiredate)%365%31)/31 AS '月',
53.         DATEDIFF(NOW(),hiredate)%31 AS '天'
54.         FROM emp
55.        
56. -- 或者
57. SELECT ename,
58.         FLOOR((DATEDIFF(NOW(),hiredate)/365)) AS '年',
59.         FLOOR(DATEDIFF(NOW(),hiredate)%365/31) AS '月',
60.         DATEDIFF(NOW(),hiredate)%31 AS '天'
61.         FROM emp       

复制代码

7.Homework07

设学校环境如下：一个系有若干个专业，每一个专业一年只招一个班，每个班有若干个学生。先要建立关于系、学生和班级的数据库，关系模式为：
班CLASS  (班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num)
学生STUDENT（学号studentid，姓名name，年龄age，班号classid）
系DEPARTMENT（系号departmentid，系名deptname）
试用sql语言完成下面功能：

• 建表，在定义中要求声明：
  
  
  
  • 每个表的主外键
    
  • deptname是唯一约束
    
  • 学生姓名不能为空
    
  
  
• 插入如下数据
  1. -- 根据emp员工表，dept部门表，工资=薪金+佣金，写出正确的sql
  2. SELECT * FROM emp;
  3. SELECT * FROM dept;
  4. SELECT * FROM salgrade;
  6. -- 1. 列出至少有一个员工的所有部门
  7. SELECT DISTINCT dname FROM emp,dept
  8.         WHERE emp.deptno=dept.deptno;
  9. -- 或者
  10. SELECT COUNT(*) AS c ,deptno
  11.         FROM emp
  12.         GROUP BY deptno
  13.         HAVING c > 1;
  15.        
  16. -- 2. 列出薪金比Smith多的所有员工
  17. -- 先查出Smith的sal
  18. -- 然后其他的员工的sal>smith.sal
  19. SELECT ename FROM emp
  20.         WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');
  21.        
  22. -- 3. 列出受雇日期晚于其直接上级的所有员工
  23. -- 先把emp表当做是两张表，worker ， leader  （自连接）
  24. -- 条件1. emp.hiredate>d.hiredate 条件2.emp.mgr=d.empno
  25. SELECT worker.ename AS '员工名',worker.hiredate AS '员工入职时间',
  26.         leader.ename AS '上级名',leader.hiredate AS '上级入职时间'
  27.         FROM emp AS worker, emp AS leader
  28.         WHERE worker.mgr=leader.empno AND worker.hiredate>leader.hiredate;
  31. -- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
  32. -- 这里因为需要显示所有部门，需要使用外连接
  33. SELECT dname,emp.* FROM emp RIGHT JOIN dept
  34.         ON emp.deptno=dept.deptno ;
  36. -- 5. 列出所有CLERK(办事员)的姓名及其部门名称
  37. SELECT ename,dname FROM emp,dept
  38.         WHERE job='CLERK' AND emp.deptno=dept.deptno;
  41. -- 6. 列出最低薪金大于1500的各种工作
  42. SELECT DISTINCT job FROM emp
  43.     WHERE job NOT IN
  44.         (SELECT job FROM emp
  45.         WHERE sal <1500);
  46. -- 或者
  47. -- 查询各个部门的最低工资
  48. -- 使用having子句进行过滤
  49. SELECT MIN(sal) AS min_sal ,job
  50.         FROM emp
  51.         GROUP BY job
  52.         HAVING min_sal > 1500;
  54.        
  55. -- 7. 列出在部门SALES（销售部）工作的员工的姓名
  56. SELECT ename FROM emp,dept  
  57.     WHERE emp.deptno = dept.deptno AND dname = 'SALES';
  59. -- 8. 列出薪金高于公司平均薪金的所有员工
  60. SELECT ename FROM emp
  61.         WHERE sal>(SELECT AVG(sal) FROM emp)

  复制代码
  1. SELECT * FROM emp;
  2. SELECT * FROM dept;
  3. SELECT * FROM salgrade;
  5. -- 1. 列出与SCOTT从事相同工作的所有员工
  6. SELECT * FROM emp
  7.         WHERE job=(SELECT job FROM emp WHERE ename='SCOTT');
  9. -- 2. 列出薪金高于，在部门30工作的所有员工的薪金，的员工姓名和薪金
  10. -- 先查询出30号部门的最高工资
  11. SELECT ename ,sal FROM emp
  12.         WHERE sal>(SELECT MAX(sal) FROM emp
  13.                         WHERE deptno = 30)
  14.                        
  15. -- 3. 列出在每个部门工作的员工数量，平均工资和平均服务期限
  16. SELECT COUNT(*) AS '员工数量',
  17.         deptno,
  18.         AVG(sal+IFNULL(comm,0)) AS '部门平均工资',
  19.         AVG(DATEDIFF(NOW(),hiredate)/365) AS '平均服务期限年'
  20.         FROM emp
  21.         GROUP BY deptno
  23. -- 4. 列出所有员工的姓名，部门名称和工资
  24. SELECT ename,dname,sal+IFNULL(comm,0) FROM emp,dept
  25.         WHERE emp.deptno= dept.deptno
  27. -- 5. 列出所有部门的详细信息和部门人数
  28. -- 先得到各个部门的平均人数
  29. SELECT dept.*, `all`
  30.         FROM dept
  31.         LEFT JOIN
  32.                 (SELECT COUNT(*) AS `all`,deptno
  33.                         FROM emp
  34.                         GROUP BY emp.deptno) AS temp
  35.         ON dept.deptno=temp.deptno
  36.        
  37. -- 6. 列出各种工作的最低工资
  38. SELECT job ,MIN(sal+IFNULL(comm,0))AS '最低工资' FROM emp
  39.         GROUP BY job;
  41. -- 7. 列出MANAGER(经理)的最低薪金
  42. SELECT MIN(sal+IFNULL(comm,0))AS '最低工资' FROM emp
  43.         WHERE job= 'MANAGER';
  45. -- 8. 列出所有员工的年工资，按年薪从低到高排序
  46. SELECT ename,(sal+IFNULL(comm,0))*12 AS '年工资' FROM emp
  47.         ORDER BY (sal+IFNULL(comm,0));

  复制代码
  1. DEPARTMENT(
  2.     001,数学;
  3.     002,计算机;
  4.     003,计算机;
  5.     004,计算机;
  6.     005,计算机; )

  复制代码
• 完成以下查询功能
  
  
  
  • 找出所有姓李的学生
    
  • 列出所有开设超过1个专业的系的名字
    
  • 列出人数大于等于30的系的编号和名字
    
  
  
• 学校又新增了一个物理系，编号为006
  
• 学生张三退学，请更新相关的表
  

1. CLASS(
2.     101,软件,计算机,1995,20;
3.     102,微电子,计算机,1996,30;
4.     111,无机化学,化学,1995,29;
5.     112,高分子化学,化学,1996,25;
6.     121,统计数学,数学,1995,20;
7.     131,现代语言,中文,1996,20;
8.     141,国际贸易,经济,1997,30;
9.     142,国际金融,经济,1996,14;      
10.      )

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！