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 | +-------+--------+-----------+------+------------+---------+---------+--------+