SQL Drills

Question 1

Use a SELECT statement to get the first_name and
last_name of all employees in the hcm.employees table.

Answer 1

SELECT first_name, last_name
FROM hcm.employees;

Question 2

Use a SELECT statement to get the last_name and city of
all customers from the oes.customers table. Alias the last_name to customer_last_name in
the query.

Answer 2

SELECT last_name AS customer_last_name, city
FROM oes.customers;

Question 3

Use a SELECT statement to select all columns from the
oes.order_items table.

Answer 3

SELECT *
FROM oes.order_items;

Question 4

Use a SELECT DISTINCT statement to get the distinct (i.e.
unique) values that occur in the locality column from the
bird.antarctic_populations table.

Answer 4

SELECT DISTINCT locality
FROM bird.antarctic_populations;

Question 5

Use a SELECT DISTINCT statement to get the distinct
combinations of values for both the locality and
species_id columns from the bird.antarctic_populations
table.

Answer 5

SELECT DISTINCT locality, species_id
FROM bird.antarctic_populations;

Question 6

Use the hcm.employees table

Write a query that returns all employees ordered
alphabetically by their last name from A to Z.

Answer 6

SELECT *
FROM hcm.employees
ORDER BY last_name;

Question 7

Use the hcm.employees table

Write a query that returns all employees ordered by
salary from highest to lowest.

Answer 7

SELECT *
FROM hcm.employees
ORDER BY salary DESC;

Question 8

Use the hcm.employees table

Write a query to return all employees ordered by most
recently hired to longest serving.

Answer 8

SELECT *
FROM hcm.employees
ORDER BY hire_date DESC;

Question 9

Use the hcm.employees table

Write a query to return all employees ordered by
department_id in ascending order and within each
department_id, order by salary from highest to lowest.

Answer 9

SELECT *
FROM hcm.employees
ORDER BY department_id, salary DESC;

Question 10

Use the hcm.employees table

Write a query to return the employee_id, first_name,
last_name and salary for the top 10 employees who get
paid the most.

Answer 10

SELECT TOP (10) employee_id, first_name, last_name, salary
FROM hcm.employees
ORDER BY salary DESC;

Question 11

Use the hcm.employees table

Write a query to return the employee_id, first_name,
last_name and salary for the employee or employees
who get paid the least

Answer 11

SELECT TOP (1) WITH TIES employee_id, first_name, last_name, salary
FROM hcm.employees
ORDER BY salary ASC;

Question 12

Write query to give the total number of employees in
each department as given by the department_id column
in the hcm.employees table.

Answer 12

select department_id, count(*) as employee_count
from hcm.employees
group by department_id

Question 13

Write a query to give the average salary in each
department as given by the department_id column in the
hcm.employees table. Order the query result by average
salary from highest to lowest.

Answer 13

select department_id, avg(salary) as average_salary
from hcm.employees
group by department_id
order by average_salary desc

Question 14

Write a query to give the total number of products on
hand at each warehouse as given by the warehouse_id
column in the oes.inventories table. Also, limit the result
to only warehouses which have greater than 5,000
product items on hand.

Answer 14

select warehouse_id, sum(quantity_on_hand) as total_products_on_hand
from oes.inventories
group by warehouse_id
having sum(quantity_on_hand) > 5000

Question 15

What is the date of the most recent population count at
each locality in the bird.antarctic_populations table?

Answer 15

select locality, max(date_of_count) as most_recent_count_date
from bird.antarctic_populations
group by locality

Question 16

What is the date of the most recent population count for each
species at each locality in the bird.antarctic_populations
table?

Answer 16

select species_id, locality, max(date_of_count) as most_recent_count_date
from bird.antarctic_populations
group by species_id, locality

Question 17

Use the hcm.employees table

Select employees from the hcm.employees table who live
in either Seattle or Sydney.

Answer 17

select *
from hcm.employees
where city = ‘Seattle’ OR city = ‘Sydney’

Question 18

Use the hcm.employees table

Select employees who live in any of the following cities:
- Seattle
- Sydney
- Ascot
- Hillston

Answer 18

select *
from hcm.employees
where city IN (‘Seattle’, ‘Sydney’, ‘Ascot’, ‘Hillston’)

Question 19

Use the hcm.employees table

Select employees from Sydney who have a salary greater
than $200,000.

Answer 19

select *
from hcm.employees
where city = ‘Sydney’ AND salary > 200000

Question 20

Use the hcm.employees table

Select employees who live in either Seattle or Sydney
and were also hired on or after 1st January 2019.

Answer 20

select *
from hcm.employees
where (city = ‘Seattle’ OR city = ‘Sydney’) AND hire_date > ‘2019-01-01’

Question 21

Use the oes.products table

Select products from the oes.products table which do
not have a product category_id of either 1, 2, or 5.

Answer 21

select *
from oes.products
where category_id NOT IN (1, 2, 5)

Question 22

Use the hcm.employees and hcm.departments tables

Write a query to return the following attributes for
employees who belong to a department:
- employee_id
- first_name
- last_name
- salary
- department_name

Answer 22

select e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
from hcm.employees e INNER JOIN hcm.departments d
on e.department_id = d.department_id

Question 23

Use the hcm.employees and hcm.departments tables

Write a query to return the following attributes for all
employees, including employees who do not belong to a
department:
- employee_id
- first_name
- last_name
- salary
- department_name

Answer 23

select e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
from hcm.employees e LEFT OUTER JOIN hcm.departments d
on e.department_id = d.department_id

Question 24

Use the hcm.employees and hcm.departments tables

Write a query to return the total number of employees in
each department. Include the department_name in the
query result. Also, include employees who have not been
assigned to a department.

Answer 24

select count(*) AS number_of_employees, d.department_name
from hcm.employees e LEFT OUTER JOIN hcm.departments d
on e.department_id = d.department_id
group by department_name

Question 25

Return all rows from both the bird.california_sightings
table and the bird.arizona_sightings table. Use column
names from the bird.california_sightings table.

Answer 25

SELECT sighting_id, common_name, scientific_name, location_of_sighting, sighting_date
FROM bird.california_sightings
UNION ALL
SELECT sighting_id, common_name, scientific_name, sighting_location, sighting_date
FROM bird.arizona_sightings;

Question 26

Return all unique species - as identified by the
scientific_name column – for species which have been
sighted in either California or Arizona. Use column names
from the bird.california_sightings table.

Answer 26

SELECT scientific_name
FROM bird.california_sightings
UNION
SELECT scientific_name
FROM bird.arizona_sightings;

Question 27

Return all unique combinations of species
(scientific_name) and state name. The state_name will
need to be added on as a new expression which gives
the applicable state name. Use column names from the
bird.california_sightings table. Order by state_name
and then by scientific_name in ascending order.

Answer 27

SELECT scientific_name, ‘California’ AS state_name
FROM bird.california_sightings
UNION
SELECT scientific_name, ‘Arizona’
FROM bird.arizona_sightings
ORDER BY state_name, scientific_name;

Question 28

Return all rows from all the bird sightings tables i.e.
Arizona, California and Florida. Use column names from
the bird.california_sightings table.

Answer 28

SELECT sighting_id, common_name, scientific_name, location_of_sighting, sighting_date, ‘California’ AS state_name
FROM bird.california_sightings
UNION ALL
SELECT sighting_id, common_name, scientific_name, sighting_location, sighting_date, ‘Arizona’
FROM bird.arizona_sightings
UNION ALL
SELECT observation_id, NULL AS common_name, scientific_name, locality, sighting_datetime, ‘Florida’
FROM bird.florida_sightings;

Question 29

Return all unique customer ids for customers who have
placed orders.

Answer 29

SELECT customer_id
FROM oes.customers
INTERSECT
SELECT customer_id
FROM oes.orders;

Question 30

Return all unique product ids for products that are
currently not in stock.

Answer 30

SELECT product_id
FROM oes.products
EXCEPT
SELECT product_id
FROM oes.inventories;