Chapter 10 : Review

This chapter combines the concepts discussed in the previous chapters.

Figure 10.1 : Database schema

1. Combining aliases and ORDER BY together

Let’s suppose we’d like to get data about employees and the monthly salary of each. We’d like to get data in ascending order by monthly salary.

SELECT Last_name AS LastName, First_name AS FirstName, Salary/12 AS MonthlySalary
FROM Employee
ORDER BY MonthlySalary ASC

We get the following table.

2. Complement set

Let’s suppose we’d like to get data about all projects in which the employee Ryu Tanaka is not involved. Since this kind of query seems to make some people confuse, let’s tell in more detail how to deal with it. Some may wrongly suggest the following answer.

SELECT p.name AS Project
FROM Involvement AS inv, Project AS p, Employee AS emp
WHERE inv.Project_id = p.ID AND inv.Employee_id = emp.ID
AND emp.First_name <> 'Ryu' AND emp.Last_name <> 'Tanaka'

We get the following table.

What’s wrong here ? The point is that the Involvement table is a special table, a so-called linking table (or junction table). This table plays the role of a connector between Employee and Project tables. That table may then contain many records related to one single project. Figure 10.2 illustrates how the query is constructed.

Figure 10.2 : SQL multiple join construction

Let’s have another approach. Let’s first create the set of all projects in which the concerned employee is involved by using a subquery. Let’s remove that set from the set of all projects and thus get the complement set.

SELECT p.Name
FROM Project AS p
WHERE p.ID NOT IN (
	SELECT inv.project_id
	FROM Involvement AS inv, Employee AS emp
	WHERE inv.Employee_id = emp.ID
	AND emp.First_name = 'Ryu' AND emp.Last_name = 'Tanaka'
)

We get the following table.