Employee DB schema

Construct MYSQL Queries

Now that we have our database loaded we can develop queries to access and manipulate the data in the database.

Note: In the employee database you can identify a current employee where the value of the to_date column is 9999-01-01

List all of the current employees first name, last name, salary and department name. (265332 rows returned)

List the first name, last name, and salary of the current employees with the ten (10) lowest salaries.

+------------+--------------+--------+ | first_name | last_name | salary | +------------+--------------+--------+ | Olivera | Baek | 38623 | | Pascal | Lueh | 38936 | | Sachar | Nicolson | 38942 | | Yurij | Narwekar | 39012 | | Oldrich | Schmiedel | 39036 | | Gianluca | Talmor | 39117 | | Marie | Marrakchi | 39186 | | Xiaopeng | Piancastelli | 39227 | | Angus | Schlumberger | 39264 | | Vidar | Turnbull | 39285 | +------------+--------------+--------+

What is the total payroll (sum of all current salaries) for each department in the company. Name the column with the total salary "Total Salary" and sort from largest salary to smallest.

+--------------------+-------------------+ | dept_name | Total Salary | +--------------------+---------------------+ | Development | 4627787365 | | Production | 3982729442 | | Sales | 3731370677 | | Marketing | 1300398678 | | Customer Service | 1270982348 | | Research | 1148334609 | | Finance | 1089231961 | | Quality Management | 1045459198 | | Human Resources | 909717009 | +--------------------+--------------+

Write a query to show the titles in the company and the number of current employees with those titles. Place the number of employees in a column called "Number of Employees". Sort from largest to smallest Number of employees.

+--------------------+---------------------+ | title | Number of Employees | +--------------------+---------------------+ | Senior Engineer | 85939 | | Senior Staff | 82024 | | Engineer | 30983 | | Staff | 25526 | | Technique Leader | 12055 | | Assistant Engineer | 3588 | | Manager | 9 | +--------------------+---------------------+

List the first name, last name, department name, and salary for all current department managers. Remember that a current manager has a to_date value of 9999-01-01 and a current salary also has a to_date value of 9999-01-01. Sort by salary, largest to smallest.

+------------+------------+--------------------+----------------+ | first_name | last_name | dept_name | salary | +------------+------------+-----------------------------+--------+ | Vishwani | Minakawa | Marketing | 106491 | | Hauke | Zhang | Sales | 101987 | | Isamu | Legleitner | Finance | 83457 | | Hilary | Kambil | Research | 79393 | | Leon | DasSarma | Development | 74510 | | Dung | Pesch | Quality Management | 72876 | | Karsten | Sigstam | Human Resources | 65400 | | Yuchang | Weedman | Customer Service | 58745 | | Oscar | Ghazalie | Production | 56654 | +------------+------------+--------------------+-----------------+

Show the three departments with the least number of employees? List the department name and number of employees in a column named Number of Employees. Sort the results from least to greatest by number of employees.

+--------------------+---------------------+ | dept_name | Number Of Employees | +--------------------+---------------------+ | Finance | 12437 | | Human Resources | 12898 | | Quality Management | 14546 | +--------------------+---------------------+

What is the number of male and female employees currently working in the company? Sort from least greatest to least.

+--------+---------------------+ | gender | Number of Employees | +--------+---------------------+ | M | 144114 | | F | 96010 | +--------+---------------------+

How many employees were hired each year? Display the year and the number of employees hired in order of greatest to least.

+------+---------------------------+ | Year | Number of Employees Hired | +------+---------------------------+ | 1986 | 36150 | | 1985 | 35316 | | 1987 | 33501 | | 1988 | 31436 | | 1989 | 28394 | | 1990 | 25610 | | 1991 | 22568 | | 1992 | 20402 | | 1993 | 17772 | | 1994 | 14835 | | 1995 | 12115 | | 1996 | 9574 | | 1997 | 6669 | | 1998 | 4155 | | 1999 | 1514 | | 2000 | 13 | +------+---------------------------+