©2020-2024 ioDraw All rights reserved
<> Those who can insist on finishing all are experts
<> Go straight to the subject
<> Create database and table insert data
The code can be copied directly in order
-- Build database CREATE DATABASE `emp`; -- Open library USE emp; -- build dept surface CREATE TABLE `dept`( `
deptno` INT(2) NOT NULL, `dname` VARCHAR(14), `loc` VARCHAR(13), CONSTRAINT
pk_deptPRIMARY KEY(deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- key emp surface CREATE
TABLE `emp` ( `empno` int(4) NOT NULL PRIMARY KEY, `ename` VARCHAR(10), `job`
VARCHAR(9), `mgr` int(4), `hiredate` DATE, `sal` float(7,2), `comm` float(7,2),
`deptno` int(2), CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- build salgrade surface CREATE TABLE `salgrade` (
`grade` int, `losal` int, `hisal` int ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --
insert data INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO dept
VALUES (20,'RESEARCH','DALLAS'); INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); INSERT INTO EMP VALUES (7369
,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO EMP VALUES (7499,
'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO EMP VALUES (7521,
'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO EMP VALUES (7566,
'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO EMP VALUES (7654,
'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO EMP VALUES (
7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO EMP VALUES (
7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO EMP VALUES (
7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); INSERT INTO EMP VALUES (
7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); INSERT INTO EMP VALUES(
7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO EMP VALUES (
7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); INSERT INTO EMP VALUES (
7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO EMP VALUES (
7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO EMP VALUES (
7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); INSERT INTO SALGRADE
VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO
SALGRADEVALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT
INTO SALGRADE VALUES (5,3001,9999);
dept surface ：
emp surface ：
salgrade surface ：
<> Test questions
1. List and “SCOTT” Names of all employees and departments engaged in the same work , Number of departments .
2. List the number of employees in each salary level of the company , average wage .
3. List departments with higher salaries 30 Name and salary of all employees working , Department name .
4. List the number of employees working in each department , Average salary and average length of service .
5. List the names of all employees , Department name and salary .
6. List the details and number of people in all departments .
7. List the minimum wage for various jobs and the names of employees engaged in this job .
8. List the of each department MANAGER( manager ) Minimum salary , full name , Department name , Number of departments .
9. List the annual salary of all employees , Department name , Sort by annual salary from low to high .
10. Find out the superior supervisor and department name of an employee , And demand that the salary of these supervisors exceed 3000
11. Find out the information in the Department name , belt ‘S’ Character of department employee , Total wages , Number of departments .
12. The date of appointment exceeds 30 Year or in 87 Salary increase for employees employed in , Salary increase principle ：10 Sector growth 10%,20 Sector growth 20%, 30 Sector growth 30%, And so on .
13. List information for all departments with at least one employee ：
14. List salary ratio SMITH For all employees ：
15. List the names of all employees and the names of their immediate supervisors ：
16. List all employees whose direct employment dates are earlier than those of their superiors , full name , Department name
17. List Department names and employee information for these departments , Also list those departments that do not have employees
18. List all "CLERK( staff member )" Name of and department , Number of people in the Department
19. List minimum salary greater than 1500 All kinds of work and the number of employees engaged in this work
20. List in Department "SALES" Name of employee working , Suppose you don't know the department number of the sales department
21. List all employees whose salary is higher than the average salary of the company , Department , Superior leaders , Salary scale of the company
22. List all department numbers with at least one employee , name , And calculate the average wages of these departments , minimum wage , Highest worker Endowment .
23. List salary ratio “SMITH” or “ALLEN” Number of all employees with multiple , full name , Department name , Name of its leader .
24. List all employee numbers , Name and the number of his direct supervisor , full name , The displayed results are arranged in descending order of the annual salary of leaders .
25. List the numbers of all employees whose employment date is earlier than their direct supervisor , full name , Department name , Department location , Number of departments .
26. List Department names and employee information for these departments （ quantity , average wage ）, Also list those departments that do not have employees .
27. List all “CLERK”（ clerk ） Name of and department , Number of people in the Department , Wage scale .
28. List minimum salary greater than 1500 All kinds of work and the number of employees engaged in this work and the name of their department , position , Average worker Endowment .
29. List in Department “SALES”（ Sales Department ） Name of employee working , base pay , Date of employment , Department name , Suppose you don't know Department number of Sales Department .
30. List all employees whose salary is higher than the average salary of the company , Department , Superior leaders , Salary scale of the company .
31. List and “SCOTT” Names of all employees and departments engaged in the same work , Number of departments .
32. query dept Table structure
33. retrieval emp surface , use is a This string is used to connect the two fields of employee name and type of work
34. retrieval emp Name of employee with Commission in the form , Monthly income and commission .
<> If there are too many questions, the answers won't be written
<> If you have questions or want to know the answer, leave a message in the comment area
Technology