Basic operations and advanced exercises for SQL
Reference resources: https://blog.csdn.net/Fenggms/article/details/83034175
There are now two tables, one for departments and the other for employees, whose table structure is as follows:
Step 1: Create a test database, command: create database test;
The second step is to build the table:
emp (employee table)
create table emp( empno int(11) primary key comment 'Employee number', ename varchar(32) comment 'Employee Name', job varchar(32) comment 'Type of work', mgr varchar(32) comment 'manager', hiredate number(10) comment 'Date of entry', sal int(11) comment 'salary', comm int(11) comment 'bonus', deptno int(11) comment 'Department number', foreign key(deptno) references dept(deptno) )
dept (department table)
create table dept( deptno int(11) primary key comment 'Department number', dname varchar(32) comment 'Department Name', loc varchar(32) comment 'place' )
Step three, insert data:
insert into dept values(1, 'Technology Department' ,'Nanni Bay'); insert into dept values(2, 'SALES' ,'Shenzhen City'); insert into dept values(3, 'Division' ,'Beijing'); insert into dept values(4, 'Service Department' ,'Yan'an'); insert into dept values(5, 'Production Department' ,'Nanjing City'); insert into dept values(6, 'Propaganda Department' ,'Shanghai'); insert into dept values(7, 'Miscellaneous Department' ,'Guangzhou City'); insert into dept values(8, 'Command' ,'Chongqing City'); insert into dept values(9, 'Ministry of Health' ,'Changsha City'); insert into dept values(10, 'Ministry of Culture' ,'wugang city'); insert into dept values(11, 'recreation center' ,'New York'); insert into dept values(12, 'Management' ,'London'); insert into dept values(13, 'Administrative Department' ,'Tianjin'); select * from dept insert into emp values(1, 'Guan Yu', 'CLERK' ,'Liu Beibei', 20011109, 2000, 1000, 3); insert into emp values(2, 'SMITH', 'CLERK' ,'Liu Beibei', 20120101, 2000, 800, 6); insert into emp values(3, 'Liu Beibei', 'MANAGER' ,'Song Zuying', 20080808, 9000, 4000, 3); insert into emp values(4, 'TOM', 'ENGINEER' ,'Steve', 20050612, 3000, 1000, 1); insert into emp values(5, 'Steve', 'MANAGER' ,'Song Zuying', 20110323, 80000, 9000, 1); insert into emp values(6, 'Zhang Feifei', 'CLERK' ,'Liu Beibei', 20101010, 2000, 1000, 3); insert into emp values(7, 'SCOTT', 'CLERK' ,'Liu Beibei', 20071204, 2000, 1000, 3); insert into emp values(8, 'Song Zuying', 'Boss' ,'nothing', 20060603, 2000, 1000, 8); insert into emp values(9, 'Cao Ren People', 'SALESMAN' ,'Cao Cao Cao', 20120130, 2000, 1000, 2); insert into emp values(10, 'Cao Cao Cao', 'MANAGER' ,'Song Zuying',20090815, 2000, 1000, 2); insert into emp values(11, 'Brother Soy Sauce', 'HAPI' ,'XXX',20090215, 3, 1, 2);
1. List all departments with at least one employee.
select deptno,dname loc from dept where deptno in (select deptno from emp);
2. List all employees with more salaries than SMITH.(SMITH employee above maximum salary)
select empno,ename,sal from emp where emp.sal>(select sal from emp emp1 where emp1.ename = 'SMITH')
3. List the names of all employees and their immediate superiors.
select a.ename,b.ename as mgr from emp a,emp b where a.mgr=b.ename;
Resolution: Table self-join is used here to connect the manager's number in table a to the employee's number in table b in order to match the employee with the manager.Finally, detailed information about the manager is displayed.
4. List all employees who have been hired earlier than their immediate superiors.
select a.empno, a.ename from emp a,emp b where a.mgr=b.ename and a.hiredate<b.hiredate;
5. List Department names and employee information for those departments, including those that do not have employees.
Correct: select dname,e.* from dept d LEFT JOIN emp e on d.deptno = e.deptno (use left join) Error: select dname,e.* FROM dept d,emp e WHERE d.deptno = e.deptno; (Only departments with employees select e d)
6. List the names of all job s that are CLERK (clerk) and their department names.
select ename,dname,job from emp e join dept d on e.deptno=d.deptno where e.job='CLERK';
7. List jobs with a minimum salary of more than 1500.*
Correct: select job from EMP GROUP BY job having min(sal)>1500; Error: select job FROM EMP WHERE sal>1500
8. 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, dept where emp.deptno = dept.deptno and dept.dname = "SALES";
9. List all employees whose salaries are higher than the average salary of the company.
select * from emp where sal >(select avg(sal) from emp);
10. List all employees who do the same job as SCOTT.
11. List the names and salaries of all employees whose salaries are equal to those of employees in Department 30.
12. List the names and salaries of employees whose salaries are higher than those of all employees working in Department 30.
13. List the number of employees working in each department, average salary and average service life.
14. List the names, Department names and salaries of all employees.
15. List a combination of employees working in the same job but belonging to different departments.
16. List details of all departments and number of departments.
17. List the minimum wages for various jobs.
18. List the minimum salary (job is MANAGER) for each department.
19. List the annual salaries of all employees, sorted from low to high.