Level 20

Greg hands Reggie a long list of options. A few weeks later, Reggie calls Greg to say the list is no use at all: none of the candidates has anything in common with him.

Let's step away for a minute, young Padawan, and try to solve this task to please the Master and gain new knowledge. Write your own answer to the question, and then check it against the correct one.

At last! Greg has found a match for Reggie:

Reggie set up a date with Alexis, and Greg was eagerly waiting to hear how it went. He was already picturing a new my_contacts table that would become the start of a new social network.

The next day Reggie is standing at Greg's door, and he is very angry.

Reggie shouts: "Sure, she's into animals. But you didn't tell me she stuffs them! There are dead animals everywhere!"

What will Greg's next query look like once he has created several interest columns?

Let's dazzle with more than just our metal-and-ceramic shine: let's show off our intellect by writing down our own solution to this problem on paper. Then we'll check it against the Master's version.

contact_id
last_name
first_name
phone
email
gender
birthday
profession
city
state
status
interest 1
interest 2
interest 3
interest 4
seeking

Let's think it over and write down the answer for this query, and then check it against the Master's correct version.

Adding new columns did nothing to solve the underlying problem: the table's structure makes it hard to write queries against it. Note that every version of the table violates the rule of data atomicity.

So, if we limit ourselves to working with the current table, there is no good solution. We tried to work around the flaws in the data structure in various ways, even changing the structure of the entire table. None of them worked.

The confines of a single table turned out to be too narrow. What we really need are additional tables that work in combination with the current one, letting us link one person to several interests. At the same time, the existing data will be fully preserved.

The non-atomic columns from the existing table should be moved into new tables.

Remember our clown information table from the previous level? The clown problem keeps growing, so we converted the single table into a handier set of several tables.

Over the next few pages we'll show why the table was split this way and not another, and what all these arrows and keys mean. After that, you'll be able to split Greg's table using the same principles.

What do you think the lines with arrows mean? And the key icons?

A representation of all the structures in a database (tables, columns, etc.) and the logical relationships between them is called a schema.

A visual representation of a database helps you picture how its components are related, but a schema can also be written out as text.

The description of the data (columns and tables) in your database, including all related objects and the connections between them, is called a SCHEMA.

You've seen how the clown information table was transformed. Now let's try to do the same with the my_contacts table.

Both approaches work well for individual tables, but when you need to draw a diagram of several tables, you have to look for something else.

Below is a simplified representation of the my_contacts table.

A diagram helps separate a table's structure from the data stored in it.

We remove the interests column and put it in a separate table.

The interests column moves to a new table.

The new interests table will store all the interests from the my_contacts table (a separate record for each interest).

We add columns that will show which interests belong to which person in the my_contacts table.

We've moved the interests out of the my_contacts table, but how do we tell who has which interests? We need to take information from the my_contacts table and put it in the interests table so that the two tables are linked to each other.

For example, we could include the first_name and last_name columns in the interests table.

We're heading in the right direction, but first_name and last_name are not the best way to link tables.

Why not?

Two tables must be linked through a unique column. Fortunately, since we've already started normalizing, my_contacts already has such a column: the primary key.

We can store the value of the primary key from the my_contacts table in the interests table. Even better, this column will let us tell which interests belong to which person in the my_contacts table. This way of linking is called a foreign key.

On insert, the foreign key will accept only values that exist in the parent table's primary key. This requirement is called data integrity.

Recall that a PRIMARY KEY is one example of a unique index and is used to uniquely identify the records in a table. No two records in a table can have the same primary key value. The primary key is usually abbreviated as PK (primary key).

A foreign key must be linked to a unique value in the parent table.

That value doesn't have to be a primary key value, but it must be unique.

The SUBSTR function takes the text of the interests column and returns a specified part of it. We pick out the characters that were copied into interest1, plus two more characters (the comma and the space).

After the UPDATE command runs, the table will look like the one below.

But the work isn't finished yet. Now we need to do the same for the interest2, interest3 and interest4 columns.

Young Padawan, let's reinforce our new knowledge by writing a query on paper and checking it against the Master's correct version.

At last the interests are split into separate columns. To display them we can use a simple SELECT command, but not for all of them at once. And the command won't let us easily pull them into a single result set, because the interests are stored in four columns. The result will look something like this.

Of course, we can write four separate SELECT commands to display all the values:

SELECT interest1 FROM my_contacts; SELECT interest3 FROM my_contacts;

SELECT interest2 FROM my_contacts; SELECT interest4 FROM my_contacts;

All that's left is to figure out how to insert the results of these commands into the new table. Fortunately, it can be done, and in more than one way: at least three of them!

Let's test our knowledge and think about the question below. And please the Master with the right answer!

...this is where result tables really come from.

A cross join creates a pair from each value in the first table and each value in the second table.

A cross join (CROSS JOIN) returns combinations of every record in the first table with every record in the second table.

The join result consists of 20 records (5 toys * 4 boys), that is, all possible combinations.

And why would I need this?

An inner join combines records from two tables according to a given condition.

Consider the following tables. Each boy has only one toy. The relationship is one-to-one, and toy_id is a foreign key.

All we need is to find out which toy belongs to each of the boys. We can use an inner join with the = operator to find the matches between the boys foreign key and the toys primary key.

An equijoin is an inner join that tests for equality.

A non-equijoin returns records in which the specified column values are not equal. As an example, consider the same two tables, boys and toys. Using a non-equijoin, we can find out exactly which toys each boy does not have (this result is handier when you're looking for a birthday present).

A non-equijoin tests for values that do not match.

Only one kind of inner join is left: the so-called natural joins. Natural joins are possible only if the column used for the join has the same name in both tables. Let's take another look at these two tables.

As before, we want to know which toy each boy has. The natural join recognizes matching column names in the two tables and returns the corresponding combinations.

A natural join links records by the values of columns that have the same name.

Greg is gradually starting to grasp what joins can do. He sees that splitting a database into tables makes sense, and that working with well-designed tables isn't all that hard. Greg is even planning to expand the gregs_list database.

But I still often have to enter one query and then use its results as the input of another query, when it would be handier to put one query inside the other. But that's only a dream...

A query inside another query?

Is that even possible?

Interview of the Week:

SENSATION! INVESTIGATION! SQL Aliases: What Are They Hiding?

After Chapter 8 you can build joins like a real SQL pro. Below are the key concepts of this chapter. The full list of tools is in Appendix 3.

With these new capabilities, I can create my own recruiting agency.

Greg knows that for acquaintances who become interested in his offer, he'll have to add new tables to the database. Instead of putting the information in my_contacts, he decides to create separate tables with one-to-one relationships, for two reasons.

First, not everyone on the my_contacts list is interested in his services. A separate table lets him get rid of NULL values in my_contacts.

Second, if Greg ever hires people to help him run the business, information about income may turn out to be confidential. In that case Greg will grant access to such tables only to those who really need it.

Greg has added new tables to his database to store information about the desired position and salary range, as well as the current position and salary. Greg is also creating a simple table to store information about the available job openings.

Greg has received information about an excellent job opening and is now trying to find candidates for it in his database. He wants to find the best match, because if his candidate gets hired, he'll receive a bonus.

Padawan, let's check how well you understood the new topic and reinforce your knowledge. Try writing this query

In effect, we're simply combining two queries into one. The first query is called the outer query, and the second is the inner query.

A subquery is nothing more than a query inside another query. The "enclosing" query is called the outer query, and the "nested" one is called the inner query, or subquery.

Since the subquery uses the = operator, it returns a single value: one record from one column (sometimes called a "cell", but SQL uses the term scalar value). This value is compared with the columns in the WHERE clause.

Let's see how a similar query against the my_contacts table works. The RDBMS reads the scalar value from the zip_code table and compares it with the columns in the WHERE clause.

Why can't the same thing be done with joins?

Below are some rules that subqueries must satisfy. Fill in the blanks with words from the following set (some words may be used more than once).

A very common use case for a correlated subquery is finding all records in the outer query that have no matching records in a related table.

Suppose Greg wants to expand the client base of his job search service. To do that, he plans to send messages to everyone in my_contacts whose data is not yet in the job_current table. To find those records he uses the NOT EXISTS condition.

Write queries that answer the following questions (use joins and non-correlated subqueries where appropriate). Use the gregs_list database schema.

So, can a subquery be nested inside another subquery?

What's the best way to build a subquery inside a subquery?

Try writing small subqueries for the different parts of the question. Look them over and try combining them. If you're trying to find people with the same salary as the highest-paid web designer, the breakdown of the query might look like this:

Find the highest-paid web designer

Find the people who earn x

and then substitute the first query for x.

In most cases, yes, you can, but first you need to learn a few things about joins.

In a left outer join, a match among the records of the right table is looked up for EVERY RECORD of the LEFT table.

With a left outer join we can find out which toy belongs to which girl.

Below is the syntax of a left outer join, using the tables we've already worked with. The girls table is listed first after FROM, so it is considered the left table; next come the keywords LEFT OUTER JOIN; and finally, the toys table is considered the right table.

Is that it? What exactly have we achieved? It seems an outer join is no different from an inner join.

It is different: an outer join returns a record whether or not it has a match in the other table.

The absence of a match is indicated by a NULL value. In our example with girls and toys, a NULL in the results means that the toy doesn't belong to any of the girls. Very valuable information!

Write queries that answer the following questions (use joins and non-correlated subqueries where appropriate). Use the gregs_list database schema.

A right outer join is almost exactly like a left outer join, except that it compares the right table against the left one. The following two queries return exactly the same results.

We need to add a new column to the clown_info table with information about who is the boss of each clown. The new column will store the boss's ID. We'll call it boss_id, as in the clown_boss table.

In the clown_boss table, boss_id was a foreign key. When added to clown_info, this column is still a foreign key, even though it sits in a different table. A foreign key like this, which refers to another field of the same table, is called self-referencing.

We consider Mr. Sniffles to be his own boss, so his boss_id is the same as his id.

A self-referencing foreign key is a table's primary key used in that same table for another purpose.

A SELF-REFERENCING foreign key is a table's primary key used in that same table for other purposes.

Suppose we want to list all the clowns and their bosses. A list of all the clowns with their boss IDs is easy to get with a SELECT query:

SELECT name, boss_id FROM clown_info;

But what we need are pairs: the clown's name and his boss's name.

Write queries that answer the following questions (use joins and non-correlated subqueries where appropriate). Use the gregs_list database schema.

There is one more way to get combined results from tables: unions (the keyword UNION).

A union combines the results of two or more queries into one table, based on what is specified in the SELECT query. Unions can be thought of as the "overlapping" values of all the queries.

Greg notices that the result has no duplicates, but the job titles aren't listed in order, so he tries the query again with an ORDER BY clause added to each SELECT command.

What do you think happened when the new query ran?

The number of columns in the SELECT commands must be the same. You can't select two columns with one command and one column with another.

What do you think will happen if the columns being combined have different data types?

UNION ALL works exactly like UNION, except that it returns all the values from the columns instead of a single instance from each group of duplicates.

So far, the unions we've built have used columns with matching data types. In some situations, however, you may need to build a union from columns of different types.

When we say that data types must be compatible with each other, it means that they can be converted to a common type if necessary; if that can't be done, running the query will result in an error.

Say a union combines an INTEGER type with a VARCHAR type. Since VARCHAR data can't be converted to an integer, the INTEGER will be converted to VARCHAR in the results.

Now You Are a True SQL Jedi

Wow, you did it! You've made it all the way to the end of the course, and this is your last practical assignment, so complete it with the honor of an SQL Jedi. But I think that after so many exercises together, you'll write all the practice queries correctly on the first try.