作业一

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

--1.查询两表的数据--

--2.查询男学员名单--

--3.查询笔试成绩优秀的学员情况（成绩在75~100之间)--

--4.显示学员信息，地址没有填写的显示“地址不详”--

--5.统计笔试考试平均分和机试考试平均分-- ？空值问题

--6.统计参加本次考试的学员人数

--7.查询没有通过考试的人数（笔试或机试小于60分）-- ？空值问题

--8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分--

--9.排名次（按平均分从高到低排序），显示名次、学号、平均分-- ?名次

--10.机试成绩都提5分--

--11.机试成绩100分封顶（加分后超过100分的，按100分计算）--

--12.笔试不及格的提到及格，60-90分的加5分，90分以上加3分（但不能超过100）--

作业二

-- 1、选择部门30中的雇员
SELECT * FROM emp WHERE deptno = 30;
-- 2、列出所有办事员的姓名、编号和部门
SELECT ename,empno,deptno FROM emp 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!='CLERK' AND job != 'MANAGER' OR sal >= 2000;
-- 7、找出收取佣金的雇员的不同工作
SELECT DISTINCT job FROM emp WHERE comm is not null;
-- 8、找出不收取佣金或收取的佣金低于100的雇员
select * from emp where comm is null or comm <100;
-- 9、找出各月最后一天受雇的所有雇员
select *from emp where hiredate = LAST_DAY(hiredate);
-- 10、找出早于25年之前受雇的雇员
select * from emp where ADDDATE(now(),interval - 25 year) > hiredate;
-- 11、显示只有首字母大写的所有雇员的姓名
select * from emp where upper(SUBSTR(ENAME,1,1))=SUBSTR(ENAME,1,1) and LOWER(SUBSTR(ENAME,2,LENGTH(ENAME))) = SUBSTR(ENAME,2,LENGTH(ENAME));
-- 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,ADDDATE(hiredate,INTERVAL + 10 year) 服务满十年 from emp;
-- 17、显示雇员的详细资料，按姓名排序
select * from emp order by ename;
-- 18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename,hiredate from emp order by hiredate;
-- 19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
select ename,job,sal from emp order by job desc ,sal;
-- 20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
select ename,YEAR(hiredate) 年份,MONTH(hiredate) 月份 from emp order by 月份,年份;
-- 21、显示在一个月为30天的情况下所有雇员的日薪金
select ename,ROUND(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,curdate(),hiredate,
       if(day(curdate()) >= day(hiredate) ,
          day(curdate())-day(hiredate) ,
          day(date_sub(curdate(),interval day(curdate()) day))-day(hiredate)+ day(curdate())) 天数,

       if( day(curdate()) >= day(hiredate),
           if(month(curdate()) >= month(hiredate),
              month(curdate()) - month(hiredate),
              month(curdate()) - month(hiredate)+12 ),
           if(month(curdate())-1 >= month(hiredate),
              month(curdate())-1 - month(hiredate),
              month(curdate())-1 - month(hiredate)+12 )) 月份,
       if(day(curdate()) >= day(hiredate),
          if(month(curdate())>=month(hiredate),
             year(curdate())-year(hiredate),
              year(curdate())-1-year(hiredate)),
          if(month(curdate())-1>=month(hiredate),
             year(curdate())-year(hiredate),
              year(curdate())-1-year(hiredate))) 年份

from emp;
copy

--1、选择部门30中的雇员

--2、列出所有办事员的姓名、编号和部门

--3、找出佣金高于薪金的雇员

--4、找出佣金高于薪金60%的雇员

--5、找出部门10中所有经理manager和部门20中的所有办事员clerk的详细资料

--6、找出部门10中所有经理、部门中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料

--7、找出收取佣金的雇员的不同工作

--8、找出不收取佣金或收取的佣金低于100的雇员

--9、找出各月最后一天受雇的所有雇员

--10、找出早于25年之前受雇的雇员

--11、显示只有首字母大写的所有雇员的姓名

--12、显示正好为6个字符的雇员姓名

--13、显示不带有'R'的雇员姓名

--14、显示所有雇员的姓名的前三个字符

--15、显示所有雇员的姓名，用a替换所有'A'

--16、显示所有雇员的姓名以及满10年服务年限的日期

--17、显示雇员的详细资料，按姓名排序

--18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

--19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

--20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面

--21、显示在一个月为30天的情况下所有雇员的日薪金

--22、找出在（任何年份的）2月受聘的所有雇员

--23、对于每个雇员，显示其加入公司的天数

--24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名

--25、以年、月和日显示所有雇员的服务年限

作业三

-- 1．列出至少有一个员工的所有部门
select distinct d.dname from emp e,dept d where d.deptno=e.deptno and e.ename is not null ;
-- 2．列出薪金比“SMITH”多的所有员工。
select ename from emp where sal >(select sal from emp where ename = 'SMITH');
-- 3．列出所有员工的姓名及其直接上级的姓名。(自连接)
select e.ename employee,l.ename leader from emp e,emp l where e.mgr=l.empno;
-- 4．列出受雇日期晚于其直接上级的所有员工。(自连接)
select e.ename from emp e,emp l where e.mgr=l.empno and e.hiredate > l.hiredate;
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)
select dname,ename from emp e right join dept d on e.deptno=d.deptno;
-- 6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and job = 'CLERK';
-- 7．列出最低薪金大于1500的各种工作。 
select distinct job,MIN(sal) minsal from emp group by job having minsal > 1500; 
-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select e.ename from emp e,dept d where e.deptno = d.deptno and job = 'SALESMAN';
-- 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 d.deptno,d.dname,COUNT(e.ename) 员工数量,AVG(sal) 平均工资,AVG(timestampdiff(day, hiredate,now())) 平均服务期限（天） from emp e right join dept d on e.deptno=d.deptno GROUP BY d.deptno;
-- 14．列出所有员工的姓名、部门名称和工资。 内连接
select ename,dname,sal from emp e,dept d where e.deptno=d.deptno;
-- 15.列出所有部门的详细信息和部门人数。
select d.deptno,dname,loc,COUNT(ename) from emp e right join dept d on e.deptno=d.deptno group by deptno;
-- 16．列出各种工作的最低工资。
select job,min(sal) from emp GROUP BY job; 
-- 17．列出MANAGER（经理）的最低薪金。
select min(sal) from emp where job = 'MANAGER';
-- 18．列出所有员工的年工资，按年薪从低到高排序。
select ename,sal*12 年薪 from emp order by 年薪;
-- 19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。 
select ename,sal,IFNULL(comm,0) from emp;
-- 20.查询部门表和职员表，列出所有的部门编码和部门名字，以及所有的职员编码和职员名字，并以部门编码和职员编码作为升序排列的标准
select d.deptno,dname,empno,ename from emp e right join dept d on e.deptno=d.deptno order by d.deptno,empno;
-- 21.多列分组计算每个部门每个职位的平均薪水和最高薪水
select deptno,job, avg(sal),max(sal) from emp group by deptno,job order by deptno;
-- 22.查询出薪水比本部门平均薪水高的员工信息
select * from emp e natural join (select avg(sal)as"平均薪水",deptno from emp group by deptno)pp;
select e.* from emp e where e.sal>(select avg(p.sal)from emp p group by deptno having p.deptno=e.deptno);
copy

--1．列出至少有一个员工的所有部门

--2．列出薪金比“SMITH”多的所有员工。

--3．列出所有员工的姓名及其直接上级的姓名。(自连接)

--4．列出受雇日期晚于其直接上级的所有员工。(自连接)

--5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)

--6．列出所有“CLERK”（办事员）的姓名及其部门名称。

--7．列出最低薪金大于1500的各种工作。

--8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

--9．列出薪金高于公司平均薪金的所有员工。

--10．列出与“SCOTT”从事相同工作的所有员工。

--11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

--12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

--14．列出所有员工的姓名、部门名称和工资。 内连接

--15.列出所有部门的详细信息和部门人数。

--16．列出各种工作的最低工资。

--17．列出MANAGER（经理）的最低薪金。

--18．列出所有员工的年工资，按年薪从低到高排序。

--19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。

--20.查询部门表和职员表，列出所有的部门编码和部门名字，以及所有的职员编码和职员名字，并以部门编码和职员编码作为升序排列的标准

--21.多列分组计算每个部门每个职位的平均薪水和最高薪水

--22.查询出薪水比本部门平均薪水高的员工信息

作业四

-- 1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；
select a.sno,a.score,b.score from 
(select * from sc where cno =(select cno from course where cname = '语文')) as a,
(select * from sc where cno =(select cno from course where cname = '数学')) as b
where a.sno=b.sno and a.score>b.score;
-- 2.查询平均分大于60的学生的学号和平均分
select sno,avg(score) avg from sc group by sno having avg>60;
-- 3.查询所有学生的学号，姓名，选课数，总分
select a.sno,sname,count(cno),sum(score) from sc a,student b where a.sno=b.sno group by sno;
-- 4.查询姓叶的老师的个数
select count(tname) from teacher where tname like '叶%';
-- 5.查询没学过“叶平”老师课的同学的学号、姓名；
select sno,sname from student where sno not in
(select sno from sc,course c,teacher t where sc.cno = c.cno and c.tno=t.tno and t.tname='叶平');
-- 6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名； 
select a.sno,sname from 
(select * from sc where cno =(select cno from course where cname = '语文')) as a,
(select * from sc where cno =(select cno from course where cname = '数学')) as b,
student c
where a.sno=b.sno and a.sno=c.sno;
-- 7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select a.sno,sname from sc a,student b where a.sno=b.sno and cno =(select cno from course where tno =(select tno from teacher where tname = '叶平')) group by sno;
-- 8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select a.sno,a.sname from 
(select s.sno,sc.score,s.sname from student s,sc where s.sno = sc.sno and sc.cno=1) a,
(select s.sno,sc.score from student s,sc where s.sno = sc.sno and sc.cno=2) b 
where a.score >b.score and a.sno = b.sno;
-- 9.查询所有课程成绩小于60分的同学的学号、姓名； 
select distinct a.sno,sname from sc a,student b where a.sno=b.sno and a.score <60;
-- 10.查询没有学全所有课的同学的学号、姓名； where sc.c# in(select c# from course) 
select a.sno,sname from sc a,student b where a.sno=b.sno group by a.sno having count(cno)<4;
-- 11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select distinct s.sno,s.sname from student s,sc where s.sno = sc.sno and sc.cno in (select cno from sc where sno =1);
-- 12.查询和学号为“6”同学所有课数量相等的其他同学的学号和姓名；
select a.sno,sname from sc a,student b where a.sno=b.sno group by a.sno having count(cno)=(select count(cno)from sc a,student b where a.sno=b.sno group by a.sno having a.sno=6);
-- 13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名； 
select sno,sname from student s where sno !=1 and
not exists(select * from sc where sc.sno = s.sno and cno not in(select cno from sc where sno =1))
and not exists(select * from sc where sno =1 and cno not in(select cno from sc where sc.sno=s.sno));
-- 14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
select cno as 课程ID,max(score) as 最高分,min(score) as 最低分 from sc group by cno;
-- 15.按各科平均成绩从低到高和及格率的百分数从高到低顺序 
select c.cno,c.cname,round(avg(sc.score),2) avg ,round((select count(*) from sc where cno = c.cno and score>=60)/(select count(*) from sc where cno = c.cno)*100,2)||'%' as path_rate from course c,sc where c.cno = sc.cno group by c.cname,c.cno order by avg, path_rate desc;
copy

--1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；

--2.查询平均分大于60的学生的学号和平均分

--3.查询所有学生的学号，姓名，选课数，总分

--4.查询姓叶的老师的个数

--5.查询没学过“叶平”老师课的同学的学号、姓名；

--6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；

--7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；

--8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

--9.查询所有课程成绩小于60分的同学的学号、姓名；

--10.查询没有学全所有课的同学的学号、姓名； where sc.c# in(select c# from course)

--11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

--12.查询和学号为“6”同学所有课数量相等的其他同学的学号和姓名；

--13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；

--14.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

--15.按各科平均成绩从低到高和及格率的百分数从高到低顺序