Of course, I tidy up just a few common face questions, the specific database will not give, I believe you can read!!!
--2 The department number of each department in the Employees table is listed, the maximum wage, the minimum wageSelectMax (Salary) as 'Maximum wage', Min (Salary) as 'Minimum wage', department_id as 'Department Number' fromEmployees GROUP BY department_id--3 lists the departments employee_job in the Employees table as'Staff'the minimum wage of the employee, the maximum wageSelectMax (Salary) as 'Maximum wage', Min (Salary) as 'Minimum wage', department_id as 'Department Number' fromEmployeeswhereemployee_job='Staff'GROUP BY department_id--4 for departments with a minimum wage of less than 5000 in employees, list Employee_job as'Staff'the employee's department number, minimum wage, maximum wageSelecta.department_id as 'Department Number', Max (a.salary) as 'Maximum wage', Min (a.salary) as 'Minimum wage' fromEmployees asAwhere the> (SelectMin (Salary) fromEmployees asBwhereA.DEPARTMENT_ID=B.DEPARTMENT_ID) and a.employee_job='Staff'GROUP BY a.department_idSelecta.department_id as 'Department Number', Max (a.salary) as 'Maximum wage', Min (a.salary) as 'Minimum wage' fromEmployees asa GROUP by a.department_id--According to the department number from high and low, wages are low and high list each employee's name, department number, salarySelectA.employee_name as 'name', a.department_id as 'Department Number', A.salary as 'Wages' fromEmployees asa ORDER by a.department_id desc,a.salary ASC--6 List'Liu Bei'name and department number of each employee in your departmentSelectA.employee_name as 'name', a.department_id as 'Department Number' fromEmployees asawhereA.department_id= (Selectdepartment_id fromEmployees asBwhereB.employee_name='Liu Bei')--7 list Each employee's name, work, department number, department nameSelectEmployee_Name, Employee_job, Employees.department_id,department_name fromemployees inner joindepartments on employees. department_id=departments.department_id--8 list employees in work as ' staff'employee's name, job, department number, department nameSelectEmployee_Name, Employee_job, Employees.department_id,department_name fromemployees inner joindepartments on employees. department_id=departments.department_idwhereemployees.employee_job='Staff'--8 for the Departments table, list all department names, department numbers, and also list the departments working as'Staff''s employee name and jobSelectDepartments.department_name as 'Department Name', departments.department_id as 'Department Number', Employees.employee_name as 'Employee Name', Employees.employee_job as 'work' fromdepartmentsleft join Employees on departments.department_id=employees.department_idwhereemployees.employee_job='Staff'--9 for employees whose wages are higher than the average level of the department, list the department number, name, salary, sorted by department numberSelecta.department_id as 'Department Number', A.employee_name as 'name', A.salary as 'Wages' fromEmployees asAwhereA.salary> (SelectAVG (Salary) fromEmployees asBwhereA.department_id=b.department_id) Order by A.department_idselect employees.department_id, Employee_Name, Salaryfrom Employe Es, (SELECT AVG (Salary) as 'avg', department_id from Employees GROUP by department_id) asT WHERE salary>T.avg and employees.department_id=t.department_idSelecta.department_id as 'Department Number', A.employee_name as 'name', A.salary as 'Wages' fromEmployees asaSelectAVG (Salary) fromEmployeeswhereDepartment_id=1SelectAVG (Salary) fromEmployeeswhereDepartment_id=2SelectAVG (Salary) fromEmployeeswhereDepartment_id=3Select* fromEmployees--10 for employees, list the number of employees and department numbers in each department with a salary higher than the average level of the department, sorted by department numberSelectCOUNT (*) as 'Number of employees', a.department_id as 'Department Number', AVG (Salary) as 'Average wage' fromEmployees asawhereA.salary> (SelectAVG (Salary) fromEmployees asBwhereA.department_id=b.department_id) Group by A.DEPARTMENT_ID have Count (a.department_id)>1ORDER BY a.department_idSelectSalary fromEmployeeswhereDepartment_id=3--11 for employees wages higher than the average level of the department, more than 1 people, list the department number, the number of persons, sorted by department numberSelectCOUNT (*) asNumber of employees, a.department_id asDepartment number, AVG (SALARY) asAverage wage fromEMPLOYEES asawhere(SelectCount (c.employee_id) fromEMPLOYEES asCwherec.department_id =a.department_id and C.salary> (SelectAVG (SALARY) fromEMPLOYEES asBwherec.department_id = b.department_id)) >1GROUP BY a.department_id ORDER by a.department_idSelectCOUNT (*) asNumber of employees, a.department_id asDepartment number, AVG (SALARY) asAverage wage fromEMPLOYEES asAgroup by a.department_id order by a.department_idSelect* fromEmployees--12 for employees in employees who are at least 5 of their wages, list their department number, name, salary, and salary less than their own numberSelectA.department_id,a.employee_name,a.salary, (SelectCOUNT (*) fromEMPLOYEES asbwhereB.salary < A.salary) asNumber fromEMPLOYEES asawhere(SelectCOUNT (*) fromEMPLOYEES asBwhereB.salary<a.salary) >5
Organized SQL Sever some database query surface questions