Additional Practice with SQL

Exercises

In fact, there are many ways that queries can be constructed.

The best way to learn is by doing, and taking notes as you go.

Work with a partner, and refer to this summary sheet:

… to complete the questions listed a bit further below.

TIP

DB Browser is a macOS desktop application. Your work to answer these questions will exist in a document which you must create and save changes to manually (as opposed to environments like Google Docs where your work is automatically saved).

You are strongly recommended to take five minutes and watch this video that demonstrates how to work with multiple queries in DB Browser – this also shows how to save your work:

For reference, here is the diagram showing the database schema once again:

Now, with your partner, try to write queries to answer these questions.

Try each question on your own first. Then expand the example solution provided to check your work.

TIP

From a prior year’s class, here is a recorded discussion of the solutions to these exercises:

Question 1

Find a list of all the departments in the company.

SOLUTION

Alternatively, we could select just the dept_name column from the departments table.

Question 2

Write a query to get the details for the first 25 employees in the employee table ordered by first name, descending.

HINT

Append LIMIT x to your query, where x is an integer.

SOLUTION

The results for this query would be more useful if sorted by last name after the first name, like this:

ORDER BY first_name DESC, last_name DESC

Try making this change to the query and compare the new results to the original results.

Question 3

Write a query to display the first 10 employee names (first_name, last_name) using the alias name “First Name”, “Last Name”.

SOLUTION

Question 4

How many male employees are there? How many female employees?

SOLUTION

We can obtain these totals using separate queries. However, it can be done in a single query – using the GROUP BY clause together with the COUNT function allows us to obtain a subtotal for each unique value found in the gender column.

Question 5

Write a query that lists all the salaries for employees in the company, in ascending order.

NOTE

Depending on the speed of your computer, this query might take a few extra seconds to run. Be patient. 🙂

SOLUTION

This query may take a long time to execute on your computer. It took almost four seconds with an M1 Pro processor, and much longer on Macs with older processors.

Question 6

Write a query that lists all the salaries for employees in the company, in descending order.

NOTE

Depending on the speed of your computer, this query might take a few extra seconds to run. Be patient. 🙂

SOLUTION

Question 7

Write a query to get the total salaries payable to employees.

SOLUTION

Use of the alias:

SUM(salary) AS "Total salaries payable"

…is not strictly required, but helps to make the results more readable, since the column title will show as "Total salaries payable" rather than SUM(salary).

Question 8

What is the average salary for all employees?

SOLUTION

Question 9

How many employees work for the company?

SOLUTION

Question 10

Find a list of employee IDs where the salary paid is greater than $60000.

SOLUTION

The WHERE clause is useful for filtering the list of rows that are returned based on some provided criteria – in this case – only rows where the salary value is more than $60000 are returned.

An alternate solution to this question provides a list of employee ID’s without duplicates, by adding the GROUP BY clause:

SELECT emp_id
FROM salaries
WHERE salary > 60000
GROUP BY emp_id

Question 11

How many employee IDs have been tied to a salary that is greater than $60000?

SOLUTION

Here we begin making use of sub-queries.

Think of this as a two-step process.

First – with the innermost query – the sub-query – we find the list of unique employee ID’s tied to at least one salary that was greater than $60000.

Then – with the outer query – we count the number of rows returned by the sub-query.

Question 12

Find a list of employee IDs where the salary paid is in the range $60000 to $70000.

SOLUTION

Here is an alternate solution to this question:

Consider – which syntax do you prefer? Is one version of this query clearer than the other with regard to what range of values it is looking for?

Question 13

How many employee IDs have been tied to a salary in the range $60000 to $70000?

SOLUTION

Here a sub-query is used again.

The innermost query is the same as the solution to question 12. It returns all the rows where an employee was at least once tied to a salary in the desired range.

The outermost query counts how many rows were returned in the sub-query.

Question 14

Find a list of employee IDs whose salary is not in the range $60000 to $70000.

SOLUTION

The NOT keyword provides the logical inverse of the set of rows returned by the query from question 12.

At first glance, the results above may seem incorrect.

For instance: how can the employee with ID 10001 have a salary that is both between $60000and$70000 and outside of that range?

That is possible because the employee had different salaries at different points in time.

This becomes apparent if we adjust the results of the query by removing the GROUP BY clause and taking a look at the data in all four columns:

We can see that as time went on, the employee with ID 10001 received several raises. We can infer that prior to June 25, 1991 the same employee had a salary lower than $70000, which is why that employee ID also shows up in the rows returned by the query from question 12.

Question 15

Find only the maximum and minimum salary paid to employee number 10012.

SOLUTION

Try running just the following query:

SELECT
    salary
FROM salaries
WHERE emp_id = 10012

You will see the set of rows showing all salaries that have been tied to employee ID 10012.

By adding the MAX and MIN functions to the query, we are picking out only the highest and lowest values in the list of rows returned by the simpler query.

Question 16

Find the maximum and minimum salary paid to all employees.

SOLUTION

Without the GROUP BY clause we would get the maximum and minimum salary paid to the entire set of employees at the company. That is, the highest single salary paid to anyone, and the lowest single salary paid to anyone.

By using the GROUP BY clause on the emp_id column, we are telling the database to give us the maximum and minimum salary for each individual employee at the company.

Question 17

Write a query to display the first name and last name of all employees who have both “b” and “c” in their first name. Order the results by first name, then by last name.

SOLUTION

Sorting by first name and then last name as well was not required, but makes the results easier for a human being to review.

Question 18

Find a list of all the job titles held by employee number 499998. List the job titles in alphabetical order.

SOLUTION