SQL statement exercises-Basic article

The content of this article originates from the adaptation http://blog.csdn.net/ochangwen/article/details/51297893

• Syntax changes for MySQL database
• I think the original has some unreasonable, changed the structure of some tables

Start, initialize table structure and data

• Table 1, Department table

create table dept(       deptno int(10) primary key,#主键       dname varchar(30),#部门名称       loc varchar(30)#所在地);

-Table 2, Employee table

create table emp(       empno int(10) primary key,#empno 员工号       ename varchar(30),#ename 员工姓名       job varchar(30),#job 工作       mgr int(10),#mgr 上级编号       hiredate int(10),#hiredate 受雇日期       sal int(10),#sal 薪金       comm int(10),#comm 佣金       deptno int(10),#deptno 部门编号       foreign key(deptno) references dept(deptno));

• Insert and other statements

INSERT INTO Dept values (1, ' Technical department ', ' Nanniwan '), insert INTO dept values (2, ' SALES ', ' Shenzhen '), insert into Dept values (3, ' Business unit ', ' Beijing INSERT INTO Dept values (4, ' service ', ' Yanan '), insert INTO dept values (5, ' production ', ' Nanjing '), insert INTO Dept values (6, ' propaganda department ', ' Shanghai city INSERT INTO Dept VALUES (7, ' chores ', ' Guangzhou '), insert INTO Dept values (8, ' command ', ' Chongqing '), insert INTO Dept values (9, ' health ', ' Changsha INSERT INTO Dept values (10, ' Culture ', ' Wugang '), insert INTO Dept values (11, ' Entertainment Department ', ' New York '), insert INTO dept values (12, ' Department of Management ', ' London '); INSERT into dept values (13, ' Administrative department ', ' Tianjin '); INSERT into EMP values (1, ' Guan Yu ', ' Clerk ', 8, 20011109, +, 3); Inse RT into EMP values (2, ' SMITH ', ' Clerk ', 1, 20120101, N, 6); INSERT into EMP values (3, ' Liu Bei prepare ', ' MANAGER ', 0, 20080808 , 9000, 4000, 3); INSERT into EMP values (4, ' TOM ', ' ENGINEER ', 5, 20050612, 5,, 1); INSERT into EMP values (, ' Steve ', ' MANAGER ', 8, 20110323, 80000, 9000, 1); INSERT into EMP values (6, ' Zhang Feifei ', ' Clerk ', 1, 20101010, +, 3, +); Insert in To EMP values (7, ' SCOTT ', ' clerk ', 1, 20071204, 8, 3); INSERT into EMP values (8, ' Boss ', ' Boss ', 0, 20060603,,); INSERT INTO EMP VALUES (9, ' Cao Ren People ', ' salesman ', ten, 20120130, 10, 2); INSERT into EMP values (, ' Caocao ', ' MANAGER ', 8,20090815, 2000, 2) INSERT into EMP values (11, ' soy sauce ', ' HAPI ', 0,20090215, 3, 1, 2);d ROP table Emp;drop Table dept;

Practice statements and Answers 1, list all departments that have at least one employee.

SELECT *, (SELECT COUNT (*) from emp e where e.deptno = D.deptno) c from Dept D where c > 0

--Method 1: There is a representative employee about the associated field data
Select d.* from Dept d where exists (select 1 from emp e where e.deptno = D.deptno)

--Method 2: Get the number of employees in each department, Count > 0

Select d.* from Dept D where EXISTS (select 1 from emp e where E.deptno = D.deptno have count (E.deptno) > 1);--SQL02
Select d.* from Dept D where D.deptno in (select E.deptno from emp e GROUP by E.deptno have count (E.deptno) > 1);--S Ql03

Note: SQL01 is to query at least one employee, SQL02,SQL03 is a query more than 1 of 2 kinds of writing! 2. List all employees who pay more than "SMITH". (Greater than the maximum salary of Smith staff)

SELECT * from emp e where e.sal > (select Sal from emp where ename = "SMITH")

3. List the names of all employees and their immediate superiors.

Select E2.ename,e1.ename from emp e1,emp e2 where E1.empno = E2.mgr

4. List all employees whose employment date is earlier than their immediate superiors.

Select E2.empno, e2.ename from EMP e1,emp E2
where E1.hiredate < e2.hiredate and e1.empno = E2.mgr

5. List department names and employee information for these departments, including those with no employees.

SELECT * FROM Dept D left JOIN emp p on d.deptno = P.deptno

6. List the names of all job "clerk" (clerks) and their department names.

Select E.ename,d.dname from emp e,dept d where e.job = "Clerk" and E.deptno = D.deptno

7. List all kinds of jobs with salary greater than 1500.

SELECT DISTINCT (e.job) from emp e where e.sal > 1500
Select E.job from emp e where e.sal > E.job
Select E.job from emp e where e.sal > GROUP by E.job have COUNT (e.job) = 1

8. List the names of employees who work in the department "Sales" (technical department), assuming they do not know the department number of the sales department.

Select E.ename,d.dname from emp e,dept d where e.job = "Clerk" and E.deptno = D.deptno
Select E.ename from emp e where exists (select 1 from dept d where d.dname = "Technical Department" and E.deptno = D.deptno)

9. List all employees who pay more than the company's average salary.

Select E.ename from emp e where E.sal > (select AVG (es.sal) from emp es)

10. List all employees who work in the same way as "SCOTT".

Select E.ename from emp e where e.job = (select Job from emp where ename = "Scott") and e.ename! = "Scott"

11. The names and salaries of all employees whose salaries are equal to the salaries of employees in the "technical department" are listed.

Select E.ename and e.sal from EMP e where e.sal in (select E.sal from Dept D, emp e where d.dname = "Technical Department" and E.deptno = d . Deptno)
Select E1.ename and e1.sal from EMP E1 where exists (select 1 from Dept D, emp e where d.dname = "Technical Department" and E.deptno = d.de Ptno and e1.empno = e.empno)

12. Name and salary of employees who pay more than the salaries of all employees working in the "technical department".

Select E.ename and e.sal from EMP e where e.sal in (select E.ename,max (e.sal) from Dept D, emp e where d.dname = "Technical Department" and E.deptno = D.deptno)

13. List the number of employees working in each department and the average salary.

Select D.dname, c,a from Dept D left joins (select E.deptno deptno,count (e.empno) c,avg (Sal+comm) A from EMP e GROUP by E.D Eptno) T on d.deptno = T.deptno

14. List all employees ' names, department names and wages.

Select E.ename,d.dname,e.sal+e.comm sum from emp e left joins dept D on d.deptno = E.deptno GROUP by e.empno

15. List A combination of employees who work in the same job but belong to different departments.

Select E1.ename,e1.job,e1.deptno, E2.ename,e2.job,e2.deptno from EMP E1, emp e2 where E1.job = E2.job and E1.deptno! = E2. Deptno Limit 0,1

16. List details of all departments and number of departments.

Select D.*, COUNT (E.deptno) from Dept D left JOIN emp e in D.deptno = E.deptno GROUP BY E.deptno
Select D.*, (select COUNT (*) from emp e where d.deptno = E.deptno) p from Dept D

17. List the minimum wage for each job.

Select E.job,min (e.sal+e.comm) from EMP e GROUP by E.job

18, list the manager (manager) minimum salary for each department (job is manager).

Select E.deptno,e.job,min (E.sal+e.comm) from the emp e where e.job = "MANAGER" GROUP by E.deptno;

19, List all employees annual salary, according to the annual salary from low to high ranking.

Select E.ename, (e.sal+e.comm) y from emp e order by Y

SQL statement exercises-Basic article