Organized SQL Sever some database query surface questions

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.