20180522 SQL Exercises : Employee management

소개

- 이전시간에 배운것들

- Group By

- Having

- Inner Join

- set Autocommit = 0

- set Autocommit = 1

- LIMIT

- 깃헙링크

Relational Schema

Exercises

1. Select the last name of all employees.

2. Select the last name of all employees, without duplicates.

3. Select all the data of employees whose last name is "Smith".

4. Select all the data of employees whose last name is "Smith" or "Doe".

5. Select all the data of employees that work in department 14.

6. Select all the data of employees that work in department 37 or department 77.

7. Select all the data of employees whose last name begins with an "S".

8. Select the sum of all the departments' budgets.

9. Select the number of employees in each department (you only need to show the department code and the number of employees).

10. Select all the data of employees, including each employee's department's data.

11. Select the name and last name of each employee, along with the name and budget of the employee's department.

12. Select the name and last name of employees working for departments with a budget greater than $60,000.

13. Select the departments with a budget larger than the average budget of all the departments.

14. Select the names of departments with more than two employees.

15. Select the name and last name of employees working for departments with second lowest budget.

16. Add a new department called "Quality Assurance", with a budget of $40,000 and departmental code 11. Add an employee called "Mary Moore" in that department, with SSN 847-21-9811.

17. Reduce the budget of all departments by 10%.

18. Reassign all employees from the Research department (code 77) to the IT department (code 14).

19. Delete from the table all employees in the IT department (code 14).

20. Delete from the table all employees who work in departments with a budget greater than or equal to $60,000.

21. Delete from the table all employees.

[ 참고링크 ]

[ SQL Exercises/Employee management ]