MySQL 20200327 连接子查询作业

keke · 2020年03月26日 · 最后由 liuyue824 回复于 2020年03月30日 · 327 次阅读

根据emp和dept表完成下列作业:

1.列出至少有三个员工的所有部门和部门信息。

2.列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称

3.列出职位为“CLERK”的姓名和部门名称,部门人数:

-- 部门表

CREATE TABLE DEPT( DEPTNO INT PRIMARY KEY, -- 部门编号 DNAME VARCHAR(14) , -- 部门名称 LOC VARCHAR(13) -- 部门地址 ) ;

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

-- 员工表

CREATE TABLE EMP ( EMPNO INT PRIMARY KEY, -- 员工编号 ENAME VARCHAR(10), -- 员工名称 JOB VARCHAR(9), -- 工作 MGR DOUBLE, -- 直属领导编号 HIREDATE DATE, -- 入职时间 SAL DOUBLE, -- 工资 COMM DOUBLE, -- 奖金 DEPTNO INT, -- 部门号 FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

回复形式:

1、问题+答案 或者 问题求助

2、本次系列课的心得体会,收获、建议以及评价都可以哦~~

注意:老师会评选出优秀回复+作业全勤+到课全勤,将会有大奖等着优秀的你

共收到 15 条回复

1: 2:

3:第三题觉得题意有点模糊,我理解的是求职位为CLERK的员工姓名、以及员工所在的部门名称及部门名称中有CLERK员工的人数,不知道理解得对不对,还是这个部门人数只是单纯的部门有的员工数量

根据emp和dept表完成下列作业:

1.列出至少有三个员工的所有部门和部门信息。

select * from dept1 d INNER JOIN (select DEPTNO, count() 部门人数 from emp1 GROUP BY DEPTNO HAVING count() >= 3) e on d.DEPTNO=e.DEPTNO;

2.列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称

select t.ename,t.empno,d.dname from dept1 d, (select a.ename,a.empno,a.deptno from emp1 a where a.hiredate<(select hiredate from emp1 b where b.empno=a.mgr))t where t.deptno=d.deptno;

3.列出职位为“CLERK”的员工姓名和部门名称,部门人数:

select e1.ename,e1.job,t.dname,t.部门总人数 from emp1 e1 left join (select d.deptno,d.dname,count(*) 部门总人数 from dept1 d INNER JOIN emp1 e on d.deptno=e.deptno GROUP BY d.dname) t on e1.deptno= t.deptno where e1.job='CLERK'

#1.列出至少有三个员工的所有部门和部门信息。
SELECT dept.* from dept WHERE dept.DEPTNO IN 
        (SELECT DEPTNO from emp GROUP BY emp.DEPTNO HAVING COUNT(emp.DEPTNO)>=3);

#2.列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称
SELECT e1.EMPNO, e1.ENAME,d.DNAME FROM emp e1,dept d
    WHERE e1.HIREDATE < (SELECT e2.HIREDATE FROM emp e2 WHERE e2.empno=e1.MGR)
    AND d.DEPTNO=e1.DEPTNO;

#3.列出职位为“CLERK”的姓名和部门名称,部门人数
SELECT emp_name,dept_name,emp_num FROM (
    SELECT emp.ENAME emp_name,
            dept.DNAME dept_name,
            (SELECT COUNT(emp.EMPNO) FROM emp WHERE emp.DEPTNO=dept.DEPTNO) emp_num 
    FROM emp,dept 
    WHERE emp.JOB='CLERK' AND emp.DEPTNO=dept.DEPTNO
) as t


一直研究到现在😂 ,终于彻底解决了,开森 😊

不做不知道,一做就更迷茫,更怀疑自己的智商,我真的只有看了别人的答案才逐渐有些思路,或者才慢慢理清思路,真不知如果没有答案,我到底会怎样,字段 很多,有时真记不住这是啥,那是啥的,甚至都搞混了 首先这题,我个人的查询字面表达是这样,首先从部门表里取出所有的信息,并定部门编号为条件?然后in括号里的两表之间的关联再以部门编号为大于等于3为分组条件??

第二题是否这么理解,先从两表里查询出要显示的信息内容,再定条件为入职时间,入职时间再以小于号另一个括号里的条件,而括号里另做查询的也是同一个员工表,所以要另作别名代替emp1 作为两个不同的表不同的字段的判断,而括号里的e1.empno=e.mgr条件就是为了让直属领导编号等于普通员编号;and就是为了得到两表关联查询

第三题,我理解答题字面上的意思就是查询职位为‘clerk‘’的员工姓名和员工的部门名称,再查询职位为clerk的部门名称的人数,这题我是完全借鉴人家的,首先我不知道这个a是什么意思,答案确实是自己想要查询得到的 另外,因为之前做作业已经存在了emp和dept表了,所以我只能另做emp1和dept1 不知老师是否能稍微解答下答案的,虽然课程已经结束了,我是老学员全程班了,给柠檬班丢脸了

hljmingxi 回复

666 ,第三题确实有2种理解

bottle_7 回复

谦虚了,能坚持保持学习状态,棒棒的

1.列出至少有三个员工的所有部门和部门信息 select * from DEPT WHERE DEPT.DEPTNO IN(select DEPTNO from EMP group by DEPTNO HAVING COUNT(EMP.DEPTNO)>=3)

2.列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称 SELECT S.EMPNO,S.ENAME,DEPT.DNAME FROM EMP S LEFT JOIN EMP Y ON Y.EMPNO=S.MGR INNER JOIN DEPT ON DEPT.DEPTNO=S.DEPTNO WHERE Y.HIREDATE>S.HIREDATE 3.列出职位为“CLERK”的姓名和部门名称,部门人数: SELECT DEPT.DNAME,B.ENAME,B.NUM FROM (SELECT * FROM EMP WHERE JOB='CLERK') A JOIN (SELECT COUNT(EMPNO) NUM,ENAME,DEPTNO FROM EMP GROUP BY DEPTNO) B ON A.DEPTNO=B.DEPTNO JOIN DEPT ON B.DEPTNO=DEPT.DEPTNO

keke 回复

能否麻烦另外发帖解答下答案呢

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册