- Issue the same query as the previous one except that this time truncate (TRUNC) to the nearest penny rather than round.
SELECT * FROM (SELECT FLOOR(SUM(SAL) / COUNT(*) / 22) AS 'daily salary' FROM emp2019284073 WHERE DEPTNO = 30) temp1, (SELECT FLOOR(SUM(SAL) / COUNT(*) / 22 / 8) AS 'hourly salary' FROM emp2019284073 WHERE DEPTNO = 30) temp2;
EXERCISES 4 DATES
- Select the name, job, and date of hire of the employees in department 20. (Format the hiredate column using a picture MM/DD/YY)
SELECT ename, job, DATE_FORMAT(hiredate, '%M-%D-%Y') FROM emp2019284073 WHERE deptno = 20;
Use a picture to format hiredate as DAY(day of the week), MONTH (name of the month, ) DD (day of the month) and YYYY(year)
SELECT ename, job, DATE_FORMAT(hiredate, '%a-%M-%D-%Y') FROM emp2019284073 WHERE deptno = 20;
- Which employees were hired in March?
SELECT * FROM emp2019284073 WHERE DATE_FORMAT(hiredate, '%M') LIKE '%March%'
- Which employees were hired on a Tuesday?
SELECT * FROM emp2019284073 WHERE DATE_FORMAT(hiredate, '%W') LIKE '%Tuesday%'
- Are there any employees who have worked more than 16 years for the company?
SELECT * FROM emp2019284073 WHERE TIMESTAMPDIFF(YEAR, hiredate, NOW()) > 16
- Show the weekday of the first day of the month in which each employee was hired. (plus their names)
(SELECT ENAME, WEEKDAY(DATE_ADD(HIREDATE, INTERVAL - DAY(HIREDATE) + 1 DAY)) FROM emp2019284073)
- Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names)
set global log_bin_trust_function_creators=TRUE; DELIMITER $$ DROP FUNCTION IF EXISTS last_friday $$ CREATE FUNCTION last_friday (dt DATE) RETURNS DATE BEGIN DECLARE last DATE; SET last=LAST_DAY(dt); WHILE DATE_FORMAT(last, "%W")!='Friday' DO SET last=DATE_SUB(last, interval 1 day); END WHILE; RETURN last; END $$ SELECT ENAME, HIREDATE, LAST_FRIDAY(HIREDATE) first_payday FROM emp2019284073;
- Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month (cf Martin)
set global log_bin_trust_function_creators=TRUE; DELIMITER $$ DROP FUNCTION IF EXISTS last_friday $$ CREATE FUNCTION last_friday (dt DATE) RETURNS DATE BEGIN DECLARE last DATE; SET last=LAST_DAY(dt); WHILE DATE_FORMAT(last, "%W")!='Friday' DO SET last=DATE_SUB(last, interval 1 day); END WHILE; RETURN last; END $$ SELECT ENAME, HIREDATE, LAST_FRIDAY(HIREDATE) first_payday FROM emp2019284073;
EXERCISES 5 GROUP BY & HAVING
- List the department number and average salary of each department.
SELECT DEPTNO, AVG(SAL) FROM emp2019284073 GROUP BY DEPTNO
Divide all employees into groups by department and by job within department. Count the employees in each group and compute each group’s average annual salary.
SELECT DEPTNO, JOB, COUNT(ENAME), AVG(SAL) FROM emp2019284073 GROUP BY DEPTNO , JOB
- Issue the same query as above except list the department name rather than the department number.
SELECT DNAME, JOB, COUNT(*), AVG(SAL) FROM (SELECT * FROM emp2019284073 e JOIN dept2019284073 d USING (DEPTNO)) a GROUP BY DNAME , JOB
- List the average annual salary for all job groups having more than 2 employees in the group.
SELECT JOB, AVG(SAL) FROM emp2019284073 GROUP BY JOB HAVING COUNT(*) > 2
5. Find all departments with an average commission greater than 25% of average salary.
SELECT DEPTNO FROM emp2019284073 GROUP BY DEPTNO HAVING (AVG(COMM) > 0.25 * AVG(SAL))
- Find each department’s average annual salary for all its employees except the managers and the president.
SELECT DEPTNO, COUNT(*), AVG(SAL) FROM emp2019284073 WHERE JOB != 'MANAGER' AND JOB != 'PRESIDENT' GROUP BY DEPTNO
- List the Department ID and Name where there are at least one Manager and two clerks.
SELECT DEPTNO, DNAME FROM dept2019284073 WHERE DEPTNO = ANY (SELECT DEPTNO FROM emp2019284073 WHERE JOB = 'MANAGER' GROUP BY DEPTNO , JOB HAVING COUNT(*) >= 1) AND DEPTNO = ANY (SELECT DEPTNO FROM emp2019284073 WHERE JOB = 'CLERK' GROUP BY DEPTNO , JOB HAVING COUNT(*) >= 2);
List the Department ID and Name where there are at least one Manager and two clerk and whose average salary is greater that the company’s average salary.
SELECT DEPTNO, DNAME FROM dept2019284073 WHERE DEPTNO = ANY (SELECT DEPTNO FROM emp2019284073 WHERE JOB = 'MANAGER' GROUP BY DEPTNO , JOB HAVING COUNT(*) >= 1) AND DEPTNO = ANY (SELECT DEPTNO FROM emp2019284073 WHERE JOB = 'CLERK' GROUP BY DEPTNO , JOB HAVING COUNT(*) >= 2) AND DEPTNO = ANY (SELECT DEPTNO FROM emp2019284073 GROUP BY DEPTNO , JOB HAVING AVG(SAL) > (SELECT AVG(SAL) FROM emp2019284073))
- List the name of the Manager who manages most employee
SELECT ENAME, NUM FROM (SELECT e1.ENAME, COUNT(*) NUM FROM emp2019284073 e1 JOIN emp2019284073 e2 ON e1.EMPNO = e2.MGR GROUP BY e1.ENAME ORDER BY COUNT(*) DESC) e3 LIMIT 1
