SQL pen questions: Company staff and department Query

Table creation: DEPARTMENTS: DEPARTMENT_ID (primarykey), DEPARTMENT_NAME, Region: EMPLOYEE_ID (primarykey), EMPLOYEE_NAME, EMPLOYEE_JOB, MANAGER, SALARY, DEPARTMENT_ID list Department numbers in the EMPLOYEES table, the highest SALARY, minimum wage selectmax (SALARY) as highest

Table creation: DEPARTMENTS: DEPARTMENT_ID (primary key), DEPARTMENT_NAME, LOCATIONEMPLOYEES: EMPLOYEE_ID (primary key), EMPLOYEE_NAME, EMPLOYEE_JOB, MANAGER, SALARY, DEPARTMENT_ID list Department numbers in the EMPLOYEES table, highest SALARY, highest minimum wage select max (SALARY) as highest

Table creation:

DEPARTMENTS:DEPARTMENT_ID(primary key),DEPARTMENT_NAME,LOCATIONEMPLOYEES:EMPLOYEE_ID(primary key),EMPLOYEE_NAME,EMPLOYEE_JOB,MANAGER,SALARY,DEPARTMENT_ID

1. List the Department numbers of each department in the EMPLOYEES table, including the highest salary and minimum wage.

Select max (SALARY) as highest SALARY, min (SALARY) as minimum wage, DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID;

2. List the minimum wage of EMPLOYEES whose employee _ job in each department is 'cler' in the "EMPLOYEES" table, with the highest salary

Select max (SALARY) as highest SALARY, min (SALARY) as minimum wage, DEPARTMENT_ID as department number from EMPLOYEES where EMPLOYEE_JOB = 'cler' group by DEPARTMENT_ID;

3. For departments with a minimum wage of less than 1000 in EMPLOYEES, the Department numbers of EMPLOYEES whose EMPLOYEE_JOB is 'cler' are listed. The minimum wage and the highest wage are listed.

Select max (SALARY) as highest SALARY, min (SALARY) as minimum wage, DEPARTMENT_ID as department number from EMPLOYEES as bwhere EMPLOYEE_JOB = 'cler' and 1000> (select min (SALARY) from EMPLOYEES as a where. DEPARTMENT_ID = B. DEPARTMENT_ID) group by B. DEPARTMENT_ID

4. The name, Department number, and salary of each employee are listed based on the department number.

Select DEPARTMENT_ID as department number, EMPLOYEE_NAME as name, SALARY as SALARY from EMPLOYEES order by DEPARTMENT_ID desc, SALARY asc

5. Write another solution to the above question

If you have another answer, please contact www.nowamagic.net. My contact information is on the homepage of the website.

6. Lists the names and department numbers of each employee in the department where 'zhangsan' is located.

Select EMPLOYEE_NAME, DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = (select DEPARTMENT_ID from EMPLOYEES where EMPLOYEE_NAME = 'zhang san ')

7. List the names, jobs, Department numbers, and department names of each employee.

select EMPLOYEE_NAME,EMPLOYEE_JOB,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_NAME from EMPLOYEES,DEPARTMENTS where EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

8. Lists the names, jobs, Department numbers, and department names of EMPLOYEES who use the EMPLOYEES as 'cler' in EMPLOYEES.

select EMPLOYEE_NAME,EMPLOYEE_JOB,DEPARTMENTS.DEPARTMENT_ID,DEPARTMENT_NAME from EMPLOYEES,DEPARTMENTS where DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID and DEPARTMENT_JOB = 'CLERK'

9. For EMPLOYEES with managers in EMPLOYEES, the names and names of managers are listed (the foreign key of managers is MANAGER)

Select a. EMPLOYEE_NAME as name, B. EMPLOYEE_NAME as MANAGER from EMPLOYEES as a, EMPLOYEES as B where a. MANAGER is not null and a. MANAGER = B. EMPLOYEE_ID

10. For the DEPARTMENTS table, the names and numbers of all DEPARTMENTS are listed, and the names and work of employees whose jobs are 'cler' are also listed.

Select DEPARTMENT_NAME as department name, DEPARTMENTS. DEPARTMENT_ID as department number, EMPLOYEE_NAME as employee name, EMPLOYEE_JOB as job from DEPARTMENTS, EMPLOYEES where DEPARTMENTS. Jobs * = EMPLOYEES. DEPARTMENT_ID and EMPLOYEE_JOB = 'cler'

11. For employees whose salaries are higher than the average level of the department, list the Department numbers, names, and salaries, sorted by Department numbers.

Select. DEPARTMENT_ID as department number,. EMPLOYEE_NAME as name,. SALARY as SALARY from EMPLOYEES as awhere. SALARY> (select avg (SALARY) from EMPLOYEES as B where. DEPARTMENT_ID = B. DEPARTMENT_ID) order by. DEPARTMENT_ID

12. For EMPLOYEES, list the number of EMPLOYEES and department numbers whose average salary is higher than the average salary of the Department.

Select count (. SALARY) Number of as employees,. DEPARTMENT_ID as department no. from EMPLOYEES as awhere. SALARY> (select avg (SALARY) from EMPLOYEES as B where. DEPARTMENT_ID = B. DEPARTMENT_ID) group by. DEPARTMENT_ID order by. DEPARTMENT_ID

13. For EMPLOYEES instances with higher salaries than the average of the Department, if the number of EMPLOYEES is large and the number of EMPLOYEES is one, the Department numbers are listed and sorted by Department numbers.

Select count (. EMPLOYEE_ID) Number of as employees,. DEPARTMENT_ID as department number, avg (SALARY) as average SALARY from EMPLOYEES as awhere (select count (c. EMPLOYEE_ID) from EMPLOYEES as c where c. DEPARTMENT_ID =. DEPARTMENT_ID and c. SALARY> (select avg (SALARY) from EMPLOYEES as B where c. DEPARTMENT_ID = B. DEPARTMENT_ID)> 1 group by. DEPARTMENT_ID order by. DEPARTMENT_ID

14. For EMPLOYEES with at least five EMPLOYEES lower than their own salaries in EMPLOYEES, list their department numbers, names, salaries, and number of EMPLOYEES with less than their own salaries

Select. DEPARTMENT_ID,. EMPLOYEE_NAME,. SALARY, (select count (B. EMPLOYEE_NAME) from EMPLOYEES as B where B. SALARY <. SALARY) as count from EMPLOYEES as awhere (select count (B. EMPLOYEE_NAME) from EMPLOYEES as B where B. SALARY5