DROP DATABASE IF EXISTS `emp`;
CREATE DATABASE `emp`;
USE emp;
CREATE TABLE `dept`(
`deptno` INT(2) NOT NULL,
`dname` VARCHAR(14),
`loc` VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT *FROM dept;
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(4) NOT NULL PRIMARY KEY,
`ename` VARCHAR(10),
`job` VARCHAR(9),
`mgr` INT(4),
`hiredate` DATE,
`sal` FLOAT(7,2),
`comm` FLOAT(7,2),
`deptno` INT(2),
CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
SELECT *FROM emp;
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-07',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);
SELECT *FROM emp;
CREATE TABLE `salgrade` (
`grade` INT,
`losal` INT,
`hisal` INT
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
SELECT *FROM salgrade;
-- SQL練習訓練一sal工資，comm獎金，mgr 經理編號
-- 1、 選擇部門30中的雇員
SELECT *FROM emp WHERE deptno='30';
-- 2、 檢索emp表中的員工姓名、月收入及部門編號
SELECT ename 姓名,sal 月收入,empno 部門編號 FROM emp;
-- 3、 檢索emp表中員工姓名、及雇傭時間（雇傭時間按照yyyy-mm-dd顯示）
SELECT ename 姓名,hiredate 雇傭時間 FROM emp;
-- 4、 檢索emp表中的部門編號及工種，并去掉重復行
SELECT DISTINCT empno 部門編號,job 工種 FROM emp;
-- 5、 檢索emp表中的員工姓名及全年的月收入
SELECT ename 姓名,sal*12 年收入 FROM emp;
-- 6、 用姓名顯示員工姓名，用年收入顯示全年月收入，
SELECT ename 姓名,sal*12 年收入 FROM emp;
-- 7、 檢索月收入大于2000的員工姓名及月收入
SELECT ename 姓名,sal 月收入 FROM emp WHERE sal>2000;
-- 8、 檢索月收入在1000元到2000元的員工姓名、月收入及雇傭時間
SELECT ename 姓名,sal 月收入 FROM emp WHERE sal<=2000 AND sal>=1000;
-- 9、 檢索以S開頭的員工姓名及月收入
SELECT ename 姓名,sal 月收入 FROM emp WHERE ename LIKE 's%';
-- 10、檢索emp表中月收入是800的或是1250的員工姓名及部門編號
SELECT ename 姓名, empno 部門編號 FROM emp WHERE sal='800'OR sal='1250';
-- 11、顯示在部門20中崗位是CLERK的所有雇員資訊
SELECT *FROM (SELECT *FROM emp WHERE job='CLERK') s WHERE s.deptno='20';
-- 12、顯示工資高于2500或崗位為MANAGER的所有雇員資訊
SELECT *FROM emp WHERE sal>2500 OR job='MANAGER';
-- 13、檢索emp表中有獎金的員工姓名、月收入及獎金
SELECT ename 姓名,sal 月收入,comm 獎金 FROM emp WHERE comm>0;
-- 14、檢索emp表中部門編號是30的員工姓名、月收入及提成，并要求其結果按月收入升序、然后按提成降序顯示
SELECT s.ename 姓名,s.sal 月收入,s.mgr 提成 FROM(SELECT *FROM emp WHERE deptno='30' ORDER BY sal ASC) s ORDER BY s.mgr DESC;
-- 15、列出所有辦事員的姓名、編號和部門(姓名對不上)
SELECT a.ename 姓名,a.empno ,b.deptno FROM dept a LEFT JOIN emp b WHERE a.ename=b.dname;
-- 17、找出部門10中所有經理和部門20中的所有辦事員的詳細資料
SELECT *FROM emp WHERE (deptno='10' AND job='MANAGER') OR (deptno ='20' AND job='CLERK');
-- 18、找出部門10中所有經理、部門20中所有辦事員，既不是經理又不是辦事員但其薪金>=2000的所有雇員的詳細資料
SELECT *FROM emp WHERE (deptno='10' AND job='MANAGER') OR (deptno ='20' AND job='CLERK')OR (job NOT IN('MANAGER','CLERK') AND sal>=2000);
-- 19、找出收取獎金的雇員的不同作業
SELECT DISTINCT job 作業 FROM emp WHERE comm>0;
-- 20、找出不收取獎金或收取的獎金低于100的雇員
SELECT ename 姓名 FROM emp WHERE comm<100 OR comm IS NULL;
-- 21、找出各月倒數第三天受雇的所有雇員
SELECT ename 各月倒數三天受雇人姓名 FROM emp WHERE DAYOFMONTH(LAST_DAY(hiredate))-DAYOFMONTH(hiredate)<=3;
SELECT LAST_DAY(hiredate) FROM emp;
SELECT DAYOFMONTH(LAST_DAY(hiredate)) FROM emp;
SELECT DAYOFMONTH(hiredate) FROM emp;
-- select DAYOFMONTH(now())
-- 22、獲取當前日期所在月的最后一天
SELECT CURDATE(); -- 獲取當前日期
SELECT DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY) -- 獲取本月第一天
SELECT LAST_DAY(CURDATE()); -- 獲取當月最后一天
SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH ) -- 獲取下個月的第一天
SELECT DATEDIFF(DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH ),DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY)) FROM DUAL -- 獲取當前月的天數
-- 23、找出早于25年之前受雇的雇員
SELECT *FROM emp WHERE DATE_ADD(NOW(),INTERVAL -25 YEAR)>hiredate;
-- 24、顯示正好為6個字符的雇員姓名
SELECT *FROM emp WHERE ename LIKE '%______';
SELECT *FROM emp WHERE LENGTH(ename)='6';
-- 25、顯示不帶有'R'的雇員姓名
SELECT *FROM emp WHERE ename NOT LIKE '%R%';
-- 26、顯示雇員的詳細資料，按姓名排序
SELECT *FROM emp ORDER BY ename;
-- 27、顯示雇員姓名，根據其服務年限，將最老的雇員排在最前面
SELECT ename 姓名,hiredate FROM emp ORDER BY hiredate;
-- 28、顯示所有雇員的姓名、作業和薪金，按作業的降序順序排序，而作業相同時按薪金升序
SELECT t.ename 姓名,t.job 作業,t.sal 薪金 FROM(SELECT * FROM emp ORDER BY job DESC) t ORDER BY t.sal ASC;
-- 29、顯示所有雇員的姓名和加入公司的年份和月份，按雇員受雇日所在月排序，將最早年份的專案排在最前面
SELECT t.ename 姓名, YEAR(t.hiredate) 年份, MONTH(t.hiredate)月份 FROM(SELECT *FROM emp ORDER BY DAY(hiredate))t ORDER BY YEAR(t.hiredate);
-- 30、顯示在一個月為30天的情況下所有雇員的日薪金
SELECT sal/30 日薪金 FROM emp WHERE DAY(LAST_DAY(hiredate));
SELECT LAST_DAY(hiredate) FROM emp;
-- 31、找出在（任何年份的）2月受聘的所有雇員
SELECT ename 姓名,MONTH(hiredate) 月份 FROM emp WHERE MONTH(hiredate)=2;
-- 32、對于每個雇員，顯示其加入公司的天數
SELECT ename 姓名,TIMESTAMPDIFF(DAY,hiredate,NOW()) 加入公司天數 FROM emp;
-- 33、顯示姓名欄位的任何位置，包含 "A" 的所有雇員的姓名
SELECT SUBSTRING(ename,1,1)第一個字母,SUBSTRING(ename,2,1)第二個字母,SUBSTRING(ename,3,1)第三個字母,SUBSTRING(ename,4,1)第四個字母,SUBSTRING(ename,5,1)第六個字母,SUBSTRING(ename,6,1)第五個字母,ename 姓名 FROM emp WHERE ename LIKE '%A%';
-- 34、以年、月和日顯示所有雇員的服務年限
SELECT ename 姓名, YEAR(hiredate) 年,MONTH(hiredate)月,DAY(hiredate) 日,TIMESTAMPDIFF(YEAR,hiredate,NOW()) 服務年數 FROM emp;
-- 35、選擇公司中有獎金 (COMM不為空,且不為0) 的員工姓名，工資和獎金比例,按工資逆排序,獎金比例逆排序.
SELECT t.ename 有獎金員工姓名,t.sal 工資,t.comm 獎金 FROM ( SELECT *FROM emp WHERE comm>0 ORDER BY sal DESC) t ORDER BY t.comm DESC;
-- 36、選擇公司中沒有管理者的員工姓名及job
SELECT ename 姓名,job 作業 FROM emp WHERE job NOT IN('MANAGER','PRESIDENT');
-- 37、選擇在1987年雇用的員工的姓名和雇用時間
SELECT ename 姓名,hiredate 雇用時間 FROM emp WHERE YEAR(hiredate)=1987;
-- 38、選擇在20或10號部門作業的員工姓名和部門號
SELECT ename 姓名,deptno 部門號 FROM emp WHERE deptno=10 OR deptno=20;
-- 39、選擇雇用時間在1981-02-01到1981-05-01之間的員工姓名，職位(job)和雇用時間,按從早到晚排序.
SELECT ename 姓名,job 職位,hiredate 雇傭時間 FROM emp WHERE hiredate>'1981-02-01' AND hiredate<'1981-05-01';
-- 40、選擇工資不在5000到12000的員工的姓名和工資
SELECT ename 姓名,sal 工資 FROM emp WHERE NOT sal>'5000' AND sal<'12000';
-- 41、查詢員工號為7934的員工的姓名和部門號
SELECT ename 姓名,deptno 部門號 FROM emp WHERE empno IN('7934');
-- 42、查詢工資大于1200的員工姓名和工資
SELECT ename 姓名,sal 工資 FROM emp WHERE sal>1200;
-- 復雜查詢
-- 1. 列出與“SCOTT”從事相同作業的所有員工及部門名稱，部門人數，
SELECT ename 名字,dname 部門名稱,(SELECT COUNT(*) FROM emp WHERE deptno=20) 人數 FROM emp,dept WHERE emp.`job`IN(SELECT emp.`JOB` FROM emp WHERE emp.`ENAME` = 'SCOTT')AND emp.`deptno`=dept.`deptno`;
SELECT COUNT(*) FROM emp WHERE deptno=20;
-- 2. 列出公司各個工資等級雇員的數量、平均工資，
SELECT salgrade.`GRADE` AS '薪資等級', COUNT(tt.`EMPNO`) AS '員工數' ,AVG(tt.sal)平均工資
FROM salgrade,
(SELECT emp.`EMPNO`, emp.`SAL`FROM emp) AS tt WHERE tt.`SAL` >= salgrade.`LOSAL` AND tt.`SAL` <= salgrade.`HISAL`
GROUP BY salgrade.`GRADE`;
-- 3. 列出薪金高于在部門30作業的所有員工的薪金的員工姓名和薪金、部門名稱，
SELECT MAX(sal) FROM emp WHERE deptno=30;
SELECT *FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
SELECT c.ename 姓名,c.sal 薪金,d.`dname` 部門名稱 FROM dept AS d JOIN (SELECT *FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30)) AS c WHERE d.`deptno`=c.deptno;
-- 4. 列出在每個部門作業的員工數量、平均工資和平均服務期限，
SELECT AVG(sal),(SELECT COUNT(*)FROM emp GROUP BY deptno) FROM emp ;
SELECT COUNT(*)FROM emp GROUP BY deptno;
SELECT AVG(sal)FROM emp GROUP BY deptno;
SELECT COUNT(*),AVG(sal),AVG(YEAR(NOW())-YEAR(hiredate))FROM emp GROUP BY deptno;
-- 5. 列出所有員工的姓名、部門名稱和工資，
SELECT e.`ename` 姓名,d.`dname` 部門名稱,e.sal 工資 FROM emp e JOIN dept d ON e.`deptno`=d.`deptno`;
-- 6. 列出所有部門的詳細資訊和部門人數，
SELECT d.*,COUNT(*) 部門人數 FROM dept d JOIN emp e ON e.`deptno`=d.`deptno` GROUP BY deptno;
-- 7. 列出各種作業的最低工資及從事此作業的雇員姓名，
SELECT job, MIN(sal) FROM emp GROUP BY job;
SELECT job, MIN(sal) FROM emp GROUP BY job;
SELECT ename FROM emp e JOIN (SELECT job, MIN(sal) FROM emp GROUP BY job) t WHERE e.`job`=t.job AND e.`sal`=MIN(sal);
-- 8. 列出各個部門的MANAGER(經理)的最低薪金、姓名、部門名稱、部門人數，
SELECT MIN(e.sal),e.`ename` FROM emp e JOIN dept t ON e.`deptno`=t.`deptno` WHERE job IN('MANAGER') GROUP BY t.`deptno`;
SELECT e.`sal`,e.`ename`, d.`dname`,COUNT=(SELECT COUNT(*) FROM emp WHERE job IN('MANAGER')GROUP BY deptno ) FROM emp e JOIN dept d ON e.`deptno`=d.`deptno` WHERE job IN('MANAGER')
-- 9. 列出所有員工的年工資，所在部門名稱，按年薪從低到高排序，
SELECT ename, sal*12 FROM emp ORDER BY sal*12 ASC;
-- 10. 查出某個員工的上級主管及所在部門名稱，并要求出這些主管中的薪水超過3000
SELECT mgr FROM emp WHERE ename='SMITH'
SELECT e.ename ,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE empno=(SELECT mgr FROM emp WHERE ename='SMITH') AND e.sal>3000;
-- 11. 求出部門名稱中，帶‘S’字符的部門員工的工資合計、部門人數，
SELECT deptno FROM dept WHERE dname LIKE '%S%';-- 查詢部門
SELECT SUM(sal),COUNT(*)FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE '%S%') GROUP BY deptno;
-- 12. 給任職日期超過40年或者在87年雇傭的雇員加薪，加薪原則：10部門增長10%，20部門增長20%，30部門增長30%，依次類推，
SELECT *FROM emp WHERE YEAR(NOW())-YEAR(hiredate)>40 OR YEAR(hiredate)=1987;
SELECT YEAR(NOW())-YEAR(hiredate)FROM emp;
SELECT IF(emp.deptno=10,sal+sal*0.1,IF(emp.`sal`=20,sal+sal*0.2,IF(emp.`deptno`=30,sal+sal*0.3,'無'))) FROM emp WHERE YEAR(NOW())-YEAR(hiredate)>40 OR YEAR(hiredate)=1987;
-- 13. 列出至少有一個員工的所有部門的資訊：
SELECT COUNT(*)FROM emp GROUP BY deptno;
SELECT deptno FROM emp ;
SELECT *FROM emp LEFT JOIN dept ON dept.`deptno`=emp.`deptno` GROUP BY dept.`dname` HAVING COUNT(emp.`empno`)>=5;
SELECT dept.`DNAME` AS '部門名', COUNT(emp.`EMPNO`) AS '員工個數'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
GROUP BY dept.`DNAME` HAVING COUNT(emp.`EMPNO`) >= 5;
-- 14. 列出薪水比“SMITH”多的所有員工資訊
SELECT *FROM emp WHERE emp.`SAL` > (SELECT emp.`SAL`FROM emp WHERE emp.`ENAME` = 'SMITH');
-- 15. 列出所有員工的姓名以及其直接上級的姓名：
SELECT DISTINCT e.ename 員工姓名,d.ename FROM emp e JOIN (SELECT ename FROM emp WHERE empno IN(SELECT mgr FROM emp WHERE ename=ename)) d;
SELECT mgr FROM emp WHERE ename=ename;
SELECT ename FROM emp WHERE empno IN(SELECT mgr FROM emp WHERE ename=ename);
SELECT emp.`ENAME` AS '員工名稱', tt.lname AS '領導名稱'
FROM emp LEFT JOIN (
SELECT emp.`EMPNO`, emp.`ENAME` AS 'lname'
FROM emp
WHERE empno IN (SELECT mgr FROM emp)) AS tt ON emp.`MGR` = tt.empno;
-- 16. 列出受雇日期早于其直接上級的所有員工的編號、姓名，部門名稱
SELECT t2.eno AS '編號', t2.ename AS '姓名', dept.`DNAME` AS '部門名稱', t2.ehiredate AS '員工受雇日期', t2.lhiredate AS '領導受雇日期'
FROM dept
INNER JOIN
(
SELECT emp.`EMPNO` AS 'eno', emp.`ENAME`, emp.`HIREDATE` AS 'ehiredate', t1.lhiredate, emp.`DEPTNO`
FROM
emp LEFT JOIN
(
SELECT emp.`EMPNO` AS 'lno', emp.`HIREDATE` AS 'lhiredate'
FROM emp
WHERE empno IN
(
SELECT mgr FROM emp
) -- 找出所有是領導的員工no
) AS t1
ON emp.`MGR` = t1.lno
) AS t2
ON t2.deptno = dept.`DEPTNO`
WHERE t2.ehiredate < t2.lhiredate OR t2.lhiredate IS NULL;
-- 17. 列出部門名稱和這些部門的員工資訊，同時列出那些沒有員工的部門
SELECT dept.`DNAME` AS '部門名稱', emp.`ENAME` AS '員工名稱', emp.`JOB` AS '職位', emp.`HIREDATE` AS '入職時間', emp.`SAL` AS '薪水'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`;
-- 18. 列出所有"CLERK(職員)"的姓名以及部門名稱，部門的人數
SELECT emp.`ENAME` AS '姓名', dept.`DNAME` AS '部門名稱', tt.emp_count AS '部門人數'
FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO`
INNER JOIN
(
SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'emp_count'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
GROUP BY dept.`DEPTNO`
) AS tt
ON emp.`DEPTNO` = tt.deptno
WHERE emp.`JOB` = 'CLERK';
-- 19. 列出最低薪金大于1500的各種作業以及從事此作業的全部雇員人數
SELECT emp.`JOB` AS '作業名稱', COUNT(emp.`EMPNO`) AS '雇員人數'
FROM emp
WHERE emp.`JOB` IN (
SELECT emp.`JOB`
FROM emp
GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500
)
GROUP BY emp.`JOB`;
-- 20. 列出在部門"SALES"作業的員工的姓名，假定不知道銷售部的部門編號
SELECT emp.`ENAME` AS '銷售部人員'
FROM emp
WHERE emp.`DEPTNO` = (
SELECT dept.`DEPTNO`
FROM dept
WHERE dept.`DNAME` = 'sales'
);
-- 21. 列出薪金高于公司平均薪金的所有員工，所在部門，上級領導，公司的工資等級
SELECT t1.ename AS '員工名稱', dept.`DNAME` AS '部門名稱', t2.ename AS '上級名稱', t1.grade AS '工資等級', t1.sal AS '工資'
FROM
(
SELECT emp.`EMPNO`, emp.`DEPTNO`, emp.`ENAME`, emp.`SAL`, emp.`MGR`,salgrade.`GRADE`
FROM emp, salgrade
WHERE emp.`SAL` > (
SELECT AVG(sal) AS 'avg_sal'
FROM emp
) AND emp.`SAL` >= salgrade.`LOSAL` AND emp.`SAL` <= salgrade.`HISAL`
) AS t1
LEFT JOIN
(
SELECT emp.`EMPNO` AS 'mgr', emp.`ENAME` -- 注意這里是查出所有領導的empno，作為mgr編號
FROM emp
WHERE emp.`EMPNO` IN
(
SELECT DISTINCT(emp.`MGR`) -- 必須要distinct 不然查出來的mgr有重復，后面上層再查重復值接近笛卡爾積
FROM emp
WHERE mgr IS NOT NULL
)
) AS t2
ON t1.mgr = t2.mgr
LEFT JOIN dept ON t1.deptno = dept.`DEPTNO`;
-- 22. 列出至少有一個員工的所有部門編號、名稱，并統計出這些部門的平均工資、最低工資、最高工資，
-- 23. 列出薪金比“SMITH”或“ALLEN”多的所有員工的編號、姓名、部門名稱、其領導姓名，
-- 24. 列出所有員工的編號、姓名及其直接上級的編號、姓名，顯示的結果按領導年工資的降序排列，
SELECT emp.`EMPNO` AS '員工編號', emp.`ENAME` AS '員工名稱', (emp.`SAL`*12) AS '年薪'
FROM emp
ORDER BY 年薪 ASC;
-- 25. 列出受雇日期早于其直接上級的所有員工的編號、姓名、部門名稱、部門位置、部門人數，
-- 26. 列出部門名稱和這些部門的員工資訊（數量、平均工資），同時列出那些沒有員工的部門，
SELECT dept.`DNAME` AS '部門名稱', COUNT(emp.`EMPNO`) AS '員工數量', ROUND(AVG(emp.`SAL`), 2) AS '平均工資', t2.avg_year AS '平均服務年限'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
LEFT JOIN
(
SELECT t1.deptno, ROUND(AVG(t1.year), 0) AS 'avg_year' -- 四舍五入，保留0位小數
FROM
(
SELECT emp.`DEPTNO`, ROUND((TO_DAYS(NOW())-TO_DAYS(emp.`HIREDATE`))/366, 0) AS 'year'
FROM emp
) AS t1
GROUP BY t1.deptno
) AS t2
ON dept.`DEPTNO` = t2.deptno
GROUP BY dept.`DEPTNO`;
-- 27. 列出所有“CLERK”（辦事員）的姓名及其部門名稱，部門的人數，工資等級，
SELECT emp.`ENAME` AS '姓名', dept.`DNAME` AS '部門名稱', tt.emp_count AS '部門人數'
FROM emp LEFT JOIN dept ON emp.`DEPTNO` = dept.`DEPTNO`
INNER JOIN
(
SELECT dept.`DEPTNO`, COUNT(emp.`EMPNO`) AS 'emp_count'
FROM dept LEFT JOIN emp ON dept.`DEPTNO` = emp.`DEPTNO`
GROUP BY dept.`DEPTNO`
) AS tt
ON emp.`DEPTNO` = tt.deptno
WHERE emp.`JOB` = 'CLERK';
-- 28. 列出最低薪金大于1500的各種作業及此從事此作業的全部雇員人數及所在部門名稱、位置、平均工資，
SELECT emp.`JOB` AS '作業名稱', COUNT(emp.`EMPNO`) AS '雇員人數'
FROM emp
WHERE emp.`JOB` IN (
SELECT emp.`JOB`
FROM emp
GROUP BY emp.`JOB` HAVING MIN(emp.`SAL`) >1500
)
GROUP BY emp.`JOB`;
29. 列出在部門“SALES”（銷售部）作業的員工的姓名、基本工資、雇傭日期、部門名稱，假定不知道銷售部的部門編號，
30. 列出薪金高于公司平均薪金的所有員工，所在部門，上級領導，公司的工資等級，
-- 31. 列出與“SCOTT”從事相同作業的所有員工及部門名稱，部門人數，
SELECT emp.`EMPNO` AS '員工編號', emp.`ENAME` AS '員工名稱', dept.`DNAME` AS '部門名稱'-- , emp.`DEPTNO`
FROM emp, dept
WHERE emp.`JOB` = (
SELECT emp.`JOB` -- 查詢出SCOTT從事的職業
FROM emp
WHERE emp.`ENAME` = 'SCOTT'
) AND emp.`ENAME` <> 'SCOTT'
AND emp.`DEPTNO` = dept.`DEPTNO`;
-- 32. 查詢dept表的結構
33. 檢索emp表，用is a 這個字串來連接員工姓名和工種兩個欄位
34. 檢索emp表中有提成的員工姓名、月收入及提成，
SELECT *FROM emp WHERE deptno='20'
SELECT *FROM emp;
SELECT *FROM dept;
SELECT *FROM salgrade;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/431452.html
標籤：其它
上一篇：Oracle的常用命令和表空間
下一篇：MySQL 學習筆記 - 日志