Lao Du MySQL 34 exercises

MYSQ Exercise Notes

Data Course Structure

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;
 

1. Name of the person who gets the highest salary in each department

SELECT
T.* 
FROM 
EMP E 
JOIN 
(SELECT ENAME,DEPTNO,MAX(SAL) AS MAXSAL FROM EMP GROUP BY DEPTNO) T ON E.DEPTNO=T.DEPTNO AND E.SAL=T.MAXSAL;
+-------+--------+---------+
| ENAME | DEPTNO | MAXSAL  |
+-------+--------+---------+
| ALLEN |     30 | 2850.00 |
| SMITH |     20 | 3000.00 |
| CLARK |     10 | 5000.00 |
| SMITH |     20 | 3000.00 |
+-------+--------+---------+

2. Who earns more than the average salary in the Department

SELECT 
E.ENAME,E.SAL,T.AVGSAL 
FROM 
EMP E 
JOIN 
(SELECT ENAME,DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO) T ON E.DEPTNO=T.DEPTNO AND E.SAL>T.AVGSAL;
+-------+---------+-------------+
| ENAME | SAL     | AVGSAL      |
+-------+---------+-------------+
| ALLEN | 1600.00 | 1566.666667 |
| JONES | 2975.00 | 2175.000000 |
| BLAKE | 2850.00 | 1566.666667 |
| SCOTT | 3000.00 | 2175.000000 |
| KING  | 5000.00 | 2916.666667 |
| FORD  | 3000.00 | 2175.000000 |
+-------+---------+-------------+

3. Get the average salary level in the Department (for everyone)

 SELECT
 A.DEPTNO,AVG(A.GRADE) 
 FROM 
 (SELECT E.DEPTNO,E.SAL,S.GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL) A 
 GROUP BY DEPTNO;
+--------+--------------+
| DEPTNO | AVG(A.GRADE) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

4. Disallow group functions (Max) and get the highest salary

 SELECT 
 SAL 
 FROM 
 EMP 
 ORDER BY
 SAL 
 DESC 
 LIMIT 1;
+---------+
| SAL     |
+---------+
| 5000.00 |
+---------+

5. Department number of the Department with the highest average salary

SELECT 
S.DEPTNO 
FROM 
(SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO) S 
ORDER BY 
S.AVGSAL 
DESC 
LIMIT 1;
+--------+
| DEPTNO |
+--------+
|     10 |
+--------+

6. Department name of the Department with the highest average salary

SELECT 
D.DNAME 
FROM 
DEPT D 
JOIN (SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO)  S 
ON 
D.DEPTNO=S.DEPTNO 
ORDER BY
S.AVGSAL 
DESC 
LIMIT 1;
+------------+
| DNAME      |
+------------+
| ACCOUNTING |
+------------+

7. Name of department with lowest rank for average salary

SELECT
T.DNAME
FROM
(SELECT D.DNAME,AVG(SAL) AS AVGSAL FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO GROUP BY D.DNAME) T
JOIN
SALGRADE S
ON
T.AVGSAL BETWEEN S.LOSAL AND S.HISAL
WHERE
S.GRADE = (SELECT GRADE FROM SALGRADE WHERE (SELECT AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO ORDER BY AVGSAL ASC LIMIT 1) BETWEEN LOSAL AND HISAL);
+-------+
| DNAME |
+-------+
| SALES |
+-------+
 select d.DNAME 
 from 
 dept d 
 join
 (select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) t 
 on d.deptno = t. deptno;
+-------+
| DNAME |
+-------+
| SALES |
+-------+

8. Get the name of a leader who earns a higher salary than the average employee (the employee code does not appear in the mgr field)

 SELECT 
 ENAME 
 FROM 
 EMP 
 WHERE 
 SAL > (SELECT MAX(SAL) FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL));
+-------+
| ENAME |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

9. Top five highest paid employees

SELECT
ENAME,SAL
FROM EMP
ORDER BY
SAL
DESC
LIMIT 5;
+-------+---------+
| ENAME | SAL     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

10. Sixth to tenth highest paid employees

SELECT
ENAME,SAL
FROM EMP
ORDER BY
SAL
DESC
LIMIT 5,5;
+--------+---------+
| ENAME  | SAL     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
+--------+---------+

11. Five last employees

SELECT
ENAME,HIREDATE
FROM 
EMP
ORDER BY
HIREDATE
DESC
LIMIT 5;
+--------+------------+
| ENAME  | HIREDATE   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+

12. How many employees are earning each salary level

SELECT 
S.GRADE,COUNT(*)
FROM
EMP E 
JOIN 
SALGRADE S
ON 
E.SAL BETWEEN S.LOSAL AND S.HISAL
GROUP BY 
S.GRADE;
+-------+----------+
| GRADE | COUNT(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

13. Interview Questions

There are three tables S (Student Form), C (Course Form), SC (Student Selection Form)

S (SNO, SNAME) representative (school number, name)

C (CNO, CNAME, CTEACHER) on behalf of (class number, class name, teacher)

SC (SNO, CNO, SCGRADE) on behalf of (number, course number, grade)

CREATE TABLE SC
(
  SNO      VARCHAR(200),
  CNO      VARCHAR(200),
  SCGRADE  VARCHAR(200)
);

CREATE TABLE S
(
  SNO    VARCHAR(200 ),
  SNAME  VARCHAR(200)
);

CREATE TABLE C
(
  CNO       VARCHAR(200),
  CNAME     VARCHAR(200),
  CTEACHER  VARCHAR(200)
);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', 'Chinese', 'Zhang'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', 'Politics', 'king'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', 'English', 'plum'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', 'Mathematics', 'Zhao'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', 'Physics', 'Dawn'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', 'Student 1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', 'Student 2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', 'Student 3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', 'Student 4'); 
commit;
 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
commit;

Question:

1. Find out the names of all the students who have not selected a Dawn teacher.

SELECT
S.SNO,S.SNAME 
FROM
S
WHERE
S.SNO NOT IN (SELECT S.SNO FROM SC JOIN C ON C.CNO=SC.CNO JOIN S ON S.SNO=SC.SNO WHERE C.CTEACHER='Dawn');
+------+---------+
| SNO  | SNAME   |
+------+---------+
| 3    | Student 3   |
| 4    | Student 4   |
+------+---------+

2. List the names and average scores of the failing students of more than 2 (including 2).

SELECT
T.SNAME,AVG(SCGRADE)
FROM SC 
JOIN
(SELECT S.SNO,S.SNAME FROM
(SELECT SNO,COUNT(SCGRADE) AS SUM FROM SC WHERE SCGRADE<60 GROUP BY SNO ) T 
JOIN S ON S.SNO=T.SNO WHERE T.SUM>=2) T
ON T.SNO=SC.SNO;

+---------+--------------+
| SNAME   | AVG(SCGRADE) |
+---------+--------------+
| Student 1   |           46 |
+---------+--------------+

3. Names of all students who have studied Lesson 1 and Lesson 2.

SELECT S.SNAME FROM S JOIN 
(SELECT A.SNO FROM SC A,SC B WHERE A.CNO="1" AND B.CNO="2" AND A.SNO=B.SNO) T 
ON S.SNO = T.SNO;
+---------+
| SNAME   |
+---------+
| Student 1   |
| Student 2   |
+---------+

14. List the names of all employees and leaders

SELECT
A.ENAME AS SUBORDINATE,  IFNULL(B.ENAME,"No superior") AS SUPERIOR
FROM
EMP A
LEFT JOIN
EMP B
ON
A.MGR=B.EMPNO;
+-------------+--------------+
| subordinate | superior     |
+-------------+--------------+
| SMITH       | FORD         |
| ALLEN       | BLAKE        |
| WARD        | BLAKE        |
| JONES       | KING         |
| MARTIN      | BLAKE        |
| BLAKE       | KING         |
| CLARK       | KING         |
| SCOTT       | JONES        |
| KING        | No superior     |
| TURNER      | BLAKE        |
| ADAMS       | SCOTT        |
| JAMES       | BLAKE        |
| FORD        | JONES        |
| MILLER      | CLARK        |
+-------------+--------------+

15. List the number, name, department name of all employees who are hired earlier than their immediate superiors

SELECT T.EMPNO,T.ENAME,D.DNAME
FROM
DEPT D
JOIN
(SELECT A.EMPNO,A.DEPTNO,A.ENAME,  B.ENAME AS SUPERIOR  FROM EMP A LEFT JOIN EMP B ON A.MGR=B.EMPNO WHERE A.HIREDATE<B.HIREDATE) T
ON D.DEPTNO=T.DEPTNO;
+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7782 | CLARK | ACCOUNTING |
|  7369 | SMITH | RESEARCH   |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
+-------+-------+------------+

16. List Department names and employee information for those departments, as well as those departments that do not have employees.

SELECT D.DNAME,E.*
FROM DEPT D 
LEFT JOIN
EMP E
ON
E.DEPTNO=D.DEPTNO;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| DNAME      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+

17. List all departments with at least five employees

SELECT D.DNAME,COUNT(*) AS SUMEMP
FROM
DEPT D JOIN EMP E
ON D.DEPTNO=E.DEPTNO
GROUP BY E.DEPTNO
HAVING SUMEMP>=5;
+----------+--------+
| DNAME    | SUMEMP |
+----------+--------+
| RESEARCH |      5 |
| SALES    |      6 |
+----------+--------+

18. List all employees with more salaries than SMITH.

SELECT * 
FROM 
EMP 
WHERE
SAL>(SELECT SAL FROM EMP WHERE ENAME="SMITH");
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

19. List the names of all CLERK s, their department names, and the number of departments

SELECT
T.DNAME,S.ENAME,T.CC
FROM
EMP S
JOIN
( SELECT D.DEPTNO, D.DNAME,COUNT(E.DEPTNO) AS CC FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO  GROUP BY(E.DEPTNO) ) T ON
S.DEPTNO=T.DEPTNO
WHERE S.JOB="CLERK";
+------------+--------+----+
| DNAME      | ENAME  | CC |
+------------+--------+----+
| RESEARCH   | SMITH  |  5 |
| RESEARCH   | ADAMS  |  5 |
| SALES      | JAMES  |  6 |
| ACCOUNTING | MILLER |  3 |
+------------+--------+----+

20. List the various jobs with a minimum salary of more than 1500 and the total number of employees working on them.

SELECT JOB,COUNT(*) AS SUMEMP
FROM
EMP
GROUP BY
JOB
HAVING
MIN(SAL)>1500;
+-----------+--------+
| JOB       | SUMEMP |
+-----------+--------+
| ANALYST   |      2 |
| MANAGER   |      3 |
| PRESIDENT |      1 |
+-----------+--------+

21. List the names of employees working in the Department SALES <Sales Department>assuming they do not know the department number of the Sales Department.

SELECT ENAME FROM EMP 
WHERE
DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME="SALES" );
+--------+
| ENAME  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

22. List all employees whose salaries are higher than the average salary of the company, their departments, their supervisors, and their salary levels.

SELECT E.ENAME,D.DNAME,S.GRADE,IFNULL(P.ENAME,"No superior") AS SUPERIOR
FROM
EMP E LEFT JOIN EMP P 
ON E.MGR=P.EMPNO
LEFT JOIN DEPT D
ON D.DEPTNO=P.DEPTNO
LEFT JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE
E.SAL>(SELECT AVG(SAL) FROM EMP);
+-------+------------+-------+--------------+
| ENAME | DNAME      | GRADE | SUPERIOR     |
+-------+------------+-------+--------------+
| JONES | ACCOUNTING |     4 | KING         |
| BLAKE | ACCOUNTING |     4 | KING         |
| CLARK | ACCOUNTING |     4 | KING         |
| SCOTT | RESEARCH   |     4 | JONES        |
| FORD  | RESEARCH   |     4 | JONES        |
| KING  | NULL       |     5 | No superior     |
+-------+------------+-------+--------------+

23 List all employees and departments with the same job as SCOTT.

SELECT E.ENAME,D.DNAME 
FROM
EMP E
JOIN 
DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE
JOB=(SELECT JOB FROM EMP WHERE ENAME="SCOTT");
+-------+----------+
| ENAME | DNAME    |
+-------+----------+
| SCOTT | RESEARCH |
| FORD  | RESEARCH |
+-------+----------+

24. List the names and salaries of other employees whose salaries are equal to those of employees in Department 30.

SELECT ENAME,SAL,DEPTNO 
FROM
EMP
WHERE
SAL IN (SELECT DISTINCT SAL FROM EMP WHERE DEPTNO=30) AND DEPTNO<>30;

25. List the names and salaries of employees whose salaries are higher than those of all employees working in Department 30. Department name

SELECT E.ENAME,E.SAL,D.DNAME 
FROM
EMP E
JOIN
DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE
SAL > (SELECT DISTINCT MAX(SAL) FROM EMP WHERE DEPTNO=30) ;
+-------+---------+------------+
| ENAME | SAL     | DNAME      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

26. List the number of employees working in each department, average salary and service life

SELECT
D.DEPTNO, COUNT(E.ENAME) ECOUNT,IFNULL(AVG(E.SAL),0) AS AVGSAL, IFNULL(AVG(TIMESTAMPDIFF(YEAR, HIREDATE, NOW())), 0) AS AVGSERVICETIME
FROM
EMP E
RIGHT JOIN
DEPT D
ON
E.DEPTNO = D.DEPTNO
GROUP BY
D.DEPTNO;
+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | avgservicetime |
+--------+--------+-------------+----------------+
|     10 |      3 | 2916.666667 |        39.6667 |
|     20 |      5 | 2175.000000 |        37.4000 |
|     30 |      6 | 1566.666667 |        39.8333 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+

27. List the names, Department names, and salaries of all employees.

SELECT E.ENAME,D.DNAME,E.SAL
FROM
EMP E
JOIN 
DEPT D
ON E.DEPTNO=D.DEPTNO;
+--------+------------+---------+
| ENAME  | DNAME      | SAL     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
+--------+------------+---------+

28. List details and number of people in all departments

SELECT D.*,T.SUMEMP
FROM DEPT D
LEFT JOIN 
(SELECT DEPTNO,COUNT(*) AS SUMEMP FROM EMP GROUP BY DEPTNO) T
ON D.DEPTNO=T.DEPTNO;
+--------+------------+----------+--------+
| DEPTNO | DNAME      | LOC      | SUMEMP |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |      3 |
|     20 | RESEARCH   | DALLAS   |      5 |
|     30 | SALES      | CHICAGO  |      6 |
|     40 | OPERATIONS | BOSTON   |   NULL |
+--------+------------+----------+--------+

29. List the minimum wages for various jobs and the names of employees who do this work

SELECT
E.ENAME,T.MINSAL
FROM EMP E
JOIN 
(SELECT MIN(SAL) MINSAL,JOB FROM EMP GROUP BY JOB) T
ON E.SAL=T.MINSAL AND E.JOB=T.JOB; 
+--------+---------+
| ENAME  | MINSAL  |
+--------+---------+
| SMITH  |  800.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| FORD   | 3000.00 |
+--------+---------+

30. List MANAGER (Leader) minimum salaries for each department

SELECT DEPTNO,MIN(SAL)
FROM EMP
WHERE
JOB="MANAGER"
GROUP BY
DEPTNO;
+--------+----------+
| DEPTNO | MIN(SAL) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+

31 Lists the annual salaries of all employees, sorted from low to high

SELECT ENAME,SAL*12 YEARSAL
FROM
EMP
ORDER BY SAL ASC;
+--------+----------+
| ENAME  | YEARSAL  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| WARD   | 15000.00 |
| MARTIN | 15000.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN  | 19200.00 |
| CLARK  | 29400.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

32. Find out the names of employees and leaders whose salary exceeds 3000.

SELECT A.ENAME,B.ENAME,B.SAL
FROM
EMP A
JOIN 
EMP B
ON A.MGR=B.EMPNO
WHERE
B.SAL>3000;
+-------+-------+---------+
| ENAME | ENAME | SAL     |
+-------+-------+---------+
| JONES | KING  | 5000.00 |
| BLAKE | KING  | 5000.00 |
| CLARK | KING  | 5000.00 |
+-------+-------+---------+

33. Find out the total wages and the number of department employees with the character'S'in the Department name.

SELECT T.DNAME,SUM(E.SAL) SUMSAL,COUNT(*) SUMEMP
FROM EMP E
JOIN 
(SELECT DNAME,DEPTNO FROM DEPT WHERE DNAME LIKE "%S%") T
ON T.DEPTNO=E.DEPTNO
GROUP BY E.DEPTNO;
+----------+----------+--------+
| DNAME    | SUMSAL   | SUMEMP |
+----------+----------+--------+
| RESEARCH | 10875.00 |      5 |
| SALES    |  9400.00 |      6 |
+----------+----------+--------+

34. Increase salaries by 10% for employees who have served for more than 30 years.

update emp set sal = sal*1.1 where timestampdiff(YEAR,hiredate,now())> 30;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  880.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1760.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1375.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 3272.50 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1375.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 3135.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2695.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3300.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5500.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1650.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1210.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 1045.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3300.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1430.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

Tags: Java MySQL

Posted on Sun, 28 Nov 2021 14:53:34 -0500 by EddieFoyJr