作业一

 /*--1.查询两表的数据--*/
SELECT * FROM stuinfo;
SELECT * FROM stumarks;
 /*--2.查询男学员名单-- */
SELECT * FROM stuinfo WHERE STUSEX = '男';
 /*--3.查询笔试成绩优秀的学员情况（成绩在75~100之间)-- */
SELECT * FROM stumarks WHERE writtenExam > 75 AND writtenExam < 100;
 /*--4.显示学员信息，地址没有填写的显示“地址不详”--*/
UPDATE stuinfo SET STUADDRESS= '地址不详' WHERE STUADDRESS IS NULL;
SELECT * FROM stuinfo;
 /*--5.统计笔试考试平均分和机试考试平均分--     ？空值问题*/
SELECT AVG(writtenExam)FROM stumarks;
SELECT AVG(LabExam)FROM stumarks;
 /*--6.统计参加本次考试的学员人数 */
SELECT COUNT(*)FROM stuinfo;
 /*--7.查询没有通过考试的人数（笔试或机试小于60分）-- ？空值问题*/
SELECT COUNT(*)FROM stumarks WHERE writtenExam < 60 OR LabExam < 60 OR writtenExam IS NULL OR LabExam IS NULL;
 /*--8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分-- */
UPDATE stumarks SET LabExam=0 WHERE LabExam IS NULL;
SELECT stuNo,writtenExam,LabExam,(writtenExam + LabExam)/2 avgExam FROM stumarks;
 /*--9.排名次（按平均分从高到低排序），显示名次、学号、平均分--  ?名次*/
SELECT ,examNo,writtenExam,LabExam,(writtenExam + LabExam)/2 avgExam FROM stumarks ORDER BY avgExam DESC;
 /*--10.机试成绩都提5分--*/
UPDATE stumarks SET LabExam=LabExam+5;
 /*--11.机试成绩100分封顶（加分后超过100分的，按100分计算）--*/
UPDATE stumarks SET LabExam=100 WHERE LabExam > 100;
 /*--12.笔试不及格的提到及格，60-90分的加5分，90分以上加3分（但不能超过100）--*/
UPDATE stumarks SET writtenExam=writtenExam+3 WHERE writtenExam > 90;
UPDATE stumarks SET writtenExam=writtenExam+5 WHERE writtenExam BETWEEN 60 AND 90;
UPDATE stumarks SET writtenExam=60 WHERE writtenExam < 60;
UPDATE stumarks SET writtenExam=100 WHERE writtenExam > 100;
copy

作业二

/*--1、选择部门30中的雇员*/
SELECT * FROM emp WHERE deptno = 30;
/*--2、列出所有办事员的姓名、编号和部门*/
SELECT ename,empno,dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno WHERE job='CLERK';
/*--3、找出佣金高于薪金的雇员*/
SELECT * FROM emp WHERE comm>sal;
/*--4、找出佣金高于薪金60%的雇员*/
SELECT * FROM emp WHERE comm>sal*0.6;
/*--5、找出部门10中所有经理manager和部门20中的所有办事员clerk的详细资料*/
SELECT * FROM emp WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK');
/*--6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料*/
SELECT * FROM emp WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK') OR (job<>'MANAGER' AND job<>'CLERK' AND sal>=2000);
/*--7、找出收取佣金的雇员的不同工作*/
SELECT DISTINCT job FROM emp WHERE comm>0;
/*--8、找出不收取佣金或收取的佣金低于100的雇员*/
SELECT * FROM emp WHERE comm<100 OR comm IS NULL;
/*--9、找出各月最后一天受雇的所有雇员*/
SELECT * FROM emp WHERE hiredate= LAST_DAY(hiredate);
/*--10、找出早于25年之前受雇的雇员*/
SELECT * FROM emp WHERE ADDDATE(NOW(), INTERVAL -35 YEAR) > hiredate;
/*--11、显示只有首字母大写的所有雇员的姓名*/
SELECT ename  FROM emp WHERE ASCII(SUBSTRING(ename,1,1)) >= 65 AND ASCII(SUBSTRING(ename,1,1))<=90;
 /*--12、显示正好为6个字符的雇员姓名*/
SELECT ename FROM emp WHERE LENGTH(ename) = 6;
/*--13、显示不带有'R'的雇员姓名*/
SELECT ename FROM emp WHERE ename NOT LIKE '%R%';
/*--14、显示所有雇员的姓名的前三个字符*/
SELECT SUBSTR(ename,1,3) FROM emp;
/*--15、显示所有雇员的姓名，用a替换所有'A'*/
SELECT REPLACE(ename,'A','a') FROM emp;
/*--16、显示所有雇员的姓名以及满10年服务年限的日期*/
SELECT ename,hiredate,DATE_ADD(hiredate,INTERVAL 10 YEAR) AS '十年' FROM emp;
 /*--17、显示雇员的详细资料，按姓名排序*/
SELECT * FROM emp ORDER BY ename;
/*--18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面*/
SELECT ename FROM emp ORDER BY hiredate;
/*--19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序*/
SELECT ename,job,sal FROM emp ORDER BY job DESC ,sal ASC;
/*--20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面*/
SELECT ename , YEAR(hiredate) 年份,MONTH(hiredate) 月份 FROM emp ORDER BY 月份,年份;
/*--21、显示在一个月为30天的情况下所有雇员的日薪金*/
SELECT ename,sal/30 FROM emp;
/*--22、找出在（任何年份的）2月受聘的所有雇员*/
SELECT * FROM emp WHERE MONTH(hiredate) = 2;
 /*--23、对于每个雇员，显示其加入公司的天数*/
SELECT ename, DATEDIFF(NOW(),hiredate) FROM emp;
/*--24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名*/
SELECT ename FROM emp WHERE ename LIKE '%A%';
/*--25、以年、月和日显示所有雇员的服务年限 */
SELECT ename, TIMESTAMPDIFF(YEAR, hiredate,NOW()) 年,TIMESTAMPDIFF(MONTH, hiredate,NOW()) 月,TIMESTAMPDIFF(DAY, hiredate,NOW()) 日 FROM emp 
copy

作业三

/*--1．列出至少有一个员工的所有部门*/
SELECT DISTINCT dname FROM dept INNER JOIN emp ON dept.deptno = emp.deptno;
/*--2．列出薪金比“SMITH”多的所有员工。*/
SELECT ename,sal FROM emp WHERE emp.sal >(SELECT sal FROM emp WHERE ename='SMITH');
/*--3．列出所有员工的姓名及其直接上级的姓名。(自连接)*/
SELECT b.empno,b.ename,b.mgr,(SELECT a.ename FROM emp a WHERE b.mgr = a.empno ) AS mgrname FROM emp b;
/*--4．列出受雇日期晚于其直接上级的所有员工。(自连接)*/
SELECT a.ename FROM emp a WHERE a.hiredate < (SELECT b.hiredate FROM emp b WHERE a.mgr = b.empno);
/*--5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)*/
SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b ON a.deptno = b.deptno;
/*--6．列出所有“CLERK”（办事员）的姓名及其部门名称。*/
SELECT a.ename , b.dname FROM emp a LEFT JOIN dept b ON a.deptno = b.deptno WHERE a.job = 'CLERK';
/*--7．列出最低薪金大于1500的各种工作。 */
SELECT job, MIN(sal) AS ‘最低薪金’ FROM emp GROUP BY job HAVING MIN(sal) >1500;
/*--8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。*/
SELECT a.ename FROM emp a WHERE a.deptno = (SELECT b.deptno FROM dept b WHERE b.dname = 'SALES' );
/*--9．列出薪金高于公司平均薪金的所有员工。*/
SELECT ename FROM emp WHERE sal > (SELECT AVG(sal) FROM emp );
/*--10．列出与“SCOTT”从事相同工作的所有员工。*/
SELECT ename FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT');
/*--11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。*/
SELECT ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30);
/*--12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。*/
SELECT ename,sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30 );
/*--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。*/
SELECT ename,hiredate, TIMESTAMPDIFF(YEAR,hiredate,NOW()) AS '加入年限' ,TIMESTAMPDIFF(MONTH,hiredate,NOW()) AS '加入月',TIMESTAMPDIFF(DAY,hiredate,NOW()) AS '加入天数' FROM emp;
/*--14．列出所有员工的姓名、部门名称和工资。 内连接*/
SELECT a.ename,a.sal,c.dptname FROM emp a LEFT JOIN (SELECT deptno AS dep,dname AS dptname FROM dept) c ON a.deptno = c.dep;
/*--15.列出所有部门的详细信息和部门人数。*/
SELECT a.* , b.cnumb FROM dept a LEFT JOIN (SELECT deptno,COUNT(deptno) AS cnumb FROM emp GROUP BY deptno) b ON a.deptno = b.deptno;
/*--16．列出各种工作的最低工资。*/
SELECT job,MIN(sal) FROM emp GROUP BY job
/*--17．列出MANAGER（经理）的最低薪金。*/
SELECT t1.* FROM emp t1, (SELECT deptno, MIN(sal) AS sal FROM emp WHERE job='MANAGER' GROUP BY deptno) t2 WHERE t1.deptno=t2.deptno AND t1.sal = t2.sal AND job='MANAGER';
/*--19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。 */
SELECT a.ename,a.sal,c.dptname FROM emp a LEFT JOIN (SELECT deptno AS dep,dname AS dptname FROM dept) c ON a.deptno = c.dep;
/*--20.多列分组计算每个部门每个职位的平均薪水和最高薪水*/
SELECT job,MIN(sal) FROM emp GROUP BY job;
copy