SQL by Example

John Russo

• Publisher: Momentum Press
• Year: 2018
• ISBN:
  9781945612633
• Topic: Computer Science
• Subtopic: Programming in SQL
• Index: Computer Science

SQL by Example uses one case study to teach the reader basic structured query language (SQL) skills.

The author has tested the case study in the classroom with thousands of students. While other SQL texts tend to use examples from many different data sets, the author has found that once students get used to one case study, they learn the material at a much faster rate.

The text begins with an introduction to the case study and trains the reader to think like the query processing engine for a relational database management system. Once the reader has a grasp of the case study then SQL programming constructs are introduced with examples from the case study. In order to reinforce concepts, each chapter has several exercises with solutions provided on the book's website.

SQL by Example is designed both for those who have never worked with SQL as well as those with some experience. It is modular in that each chapter can be approached individually or as part of a sequence, giving the reader flexibility in the way that they learn or refresh concepts. This also makes the book a great reference to refer back to once the reader is honing his or her SQL skills on the job.

CHAPTER 1

THE SHORE TO SHORE SHIPPING CASE STUDY

1.1 OVERVIEW

In this chapter, I will first introduce a case study that we will use throughout our discussion of SQL. I have designed this case study after numerous iterations of teaching SQL and have found that students learn best by using one set of tables with which they become very familiar. Once the case study has been presented, we will then go through several sample queries by hand. The objective of this is to learn to think through queries before writing the actual SQL. We will then move on to a discussion of SQL syntax and present some examples from the case study.

1.2 OBJECTIVES

• Introduce the Shore to Shore Shipping Company case study

• Discuss thinking through queries

• Discuss problem solving using relational databases

1.3 THE SHORE TO SHORE SHIPPING COMPANY CASE STUDY

The Shore to Shore Shipping Company is a small merchant marine operation that wishes to keep track of ships, ship manufacturers, shipments, and ship captains. The company operates all over the world and currently has several hundred ships. Because the company is growing, management has decided to develop a database management system to produce shipment manifests as well as management reports. Management has determined that there are four types of data that must be maintained and reported on:

1. Ship

(a) Each ship has a ship number, class, capacity, date of purchase and manufacturer ID.

(b) Each ship manufacturer has a manufacturer ID, name, city, state, representative ID and a bidding preference.

2. Captain

• Each captain has an ID, a first name, last name, license grade and a date of birth.

3. Item

• Every item that is shipped has an item number, a type, a description and a weight.

4. Shipment

When a shipment is sent out, a shipment manifest is generated, as shown in Figure 1.1.

The shipment manifest is composed of three parts, each of which contains unique information.

(a) The heading contains the shipment id, order date, origin, and destination, expected date of arrival, ship number and captain.

(b) The body of the manifest contains line items, which represent a component of the shipment. Each line contains an item number, type, description, weight and quantity. The total weight is calculated by multiplying the weight by the quantity.

(c) The footing of the manifest contains the total weight for the entire shipment. This is compared against the capacity of the ship to ensure that a ship is not overloaded.

Figure 1.1. Shipment manifest.

Based upon the shipment manifest, the following additional information must be stored in the database:

(a) For every shipment, the shipment id, date that the shipment left port, the origin, destination, ship number and captain. Additional information about the captain is stored with the captain information. The expected arrival date is not stored but is calculated based upon the origin and destination. Although it is not shown on the shipment manifest, the arrival date of the shipment is recorded and stored in the database when it arrives at its destination.

(b) For each line item, the shipment id, item number and quantity. The type, description and weight are stored with the item information. The total weight is not stored, since it can be calculated from the weight and the quantity.

(c) The shipment total weight is not stored in the database. It can be calculated whenever it is needed for a report or query.

Figures 1.2 to 1.8 show sample data for Shore to Shore. Let’s take a look around and explore the data a little bit. Once you have a thorough understanding of the data, we will begin to look at how the tables are related and how information can be obtained by combining tables. But first, let’s begin by examining each table, its columns and our sample data.

Figure 1.2. The captain table.

MANUFACTURER

Figure 1.3. The manufacturer table.

SHIP

Figure 1.4. The ship table.

1.4 CAPTAIN

We have included 12 rows in our table. Each captain has an ID that uniquely identifies him or her. Notice that this ID column is named Capt_ID and is the left most column in the table. The first captain has an ID of 001-01 and the last captain in the table has an ID of 011-11. Each captain also has a license grade that is used to determine the class of ship that he or she can pilot. This column is called License_Grade. We can look across the table from Capt_ID to License_Grade and determine that the captain with an ID of 002-14 has a license grade of 2. Since we also want to keep track of a captain’s name, we have two columns to store the captain’s first name (Fname) and last name (Lname). Looking again to our data, you can see that captain 004-02 is named Marcia Nesmith. Finally, we wish to store the date of birth for each captain in a column named DOB. Marcia Nesmith’s date of birth is May 1, 1957.

ITEM

Figure 1.5. The item table.

SHIPMENT

Figure 1.6. The shipment table.

DISTANCE

Figure 1.7. The distance table.

1.4.1 ON YOUR OWN EXERCISES

Now, just to make sure that you understand the data in the table, answer the following review questions. We are not asking you to write SQL queries, but rather to just answer the questions by looking at the captain table.

1.1 What is the license grade of OthenoVollage?

1.2 Which captain is the oldest and what is his or her license grade and captain ID?

1.3 How many captains have a license grade of 1?

1.4 How many captains were born after 1970?

SHIPMENT_LINE

Figure 1.8a. The shipment_line table (part ...