Querying a Database: Consolidation (Solutions)
NOTE
There are sometimes multiple ways to write a query that obtains the correct result.
1.
The number of employees with first name starting with ‘A’ and last name starting with ‘A’.
2.
The number of female employees paid more than $100,000.
3.
The name and salary of the lowest paid employee ever employed by the company.
4.
The name and salary of the lowest paid employee currently employed by the company.
NOTE
In this dataset, the answer to this question happens to be the same as the previous question – but that may not always be the case for any given dataset.
5.
Find all of the employees whose first names end with ‘ase’.
NOTE
There are no employees who match this criteria.
For example, this means no employees have a first name like ‘Chase’.
6.
Find a list of all the employees who were born in 1952 and were hired in 1990.
7.
Find how many employees were born in 1952 and were hired in 1990.
8.
Find all the employees that make more than 100K. (no duplicates)
9.
Now find a count of employees that make more than 100K. (no duplicates)
10.
Find the most recent person to be hired and their first name.
11.
Find how many years the employee with the number 10022 worked for.
12.
Find only the maximum and minimum salary paid to every employee with the employee number above 39,000 and below 40,000.
13.
Find a list of all the names of the employees that have a salary of exactly $60,000.
14.
Find how many employees have a salary of $60,000.
15.
How many Senior Engineers are currently employed by the company?
16.
What is the average pay for a Senior Engineer currently employed by the company?
17.
Find a list of all Senior Engineers that make a pay higher than the average pay of all staff.
18.
Find how many employees have the first name “Brendon” and a last name starting with “L”.
19.
Find the first and last name of the highest paid employee ever employed by the company.
20.
Find the first and last name of the highest paid employee currently employed by the company.
NOTE
In this dataset, the answer to this question happens to be the same as the previous question – but that may not always be the case for any given dataset.
21.
Find the first and last name of male employees currently paid between 90,000.
22.
What are the names of the 3 oldest employees?
23.
Who are the ten most recent hires at the company?
24.
Among the 10 most recent hires, which employee has the highest salary?
25.
Who is the youngest employee at the company, and when were they hired?
26.
How many employees have birthdays in January and were born in 1999?
27.
Who has had the most job titles?
28.
Who has had the largest range in pay at the company?
