SQL by Example
eBook - ePub

SQL by Example

John Russo

Share book
  1. 126 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

SQL by Example

John Russo

Book details
Book preview
Table of contents
Citations

About This Book

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.

Frequently asked questions

How do I cancel my subscription?
Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.
Can/how do I download books?
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
What is the difference between the pricing plans?
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
What is Perlego?
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Do you support text-to-speech?
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Is SQL by Example an online PDF/ePUB?
Yes, you can access SQL by Example by John Russo in PDF and/or ePUB format, as well as other popular books in Computer Science & Programming in SQL. We have over one million books available in our catalogue for you to explore.

Information

Year
2018
ISBN
9781945612633
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.
image
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.
image
Figure 1.2. The captain table.
MANUFACTURER
image
Figure 1.3. The manufacturer table.
SHIP
image
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
image
Figure 1.5. The item table.
SHIPMENT
image
Figure 1.6. The shipment table.
DISTANCE
image
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
image
Figure 1.8a. The shipment_line table (part ...

Table of contents