John Russo - SQL by example

SQL BY EXAMPLE

SQL BY EXAMPLE

JOHN RUSSO

SQL By Example

Copyright Momentum Press, LLC, 2019.

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any meanselectronic, mechanical, photocopy, recording, or any otherexcept for brief quotations, not to exceed 400 words, without the prior permission of the publisher.

First published by Momentum Press, LLC

222 East 46th Street, New York, NY 10017

www.momentumpress.net

ISBN-13: 978-1-94561-262-6 (print)

ISBN-13: 978-1-94561-263-3 (e-book)

Momentum Press Computer Science Collection

Cover and interior design by Exeter Premedia Services Private Ltd., Chennai, India

10 9 8 7 6 5 4 3 2 1

Printed in the United States of America

This book is dedicated to my wife Kim and children Sara, Sam and Sophie. Without their support and understanding, it would not have been written. Id also like to thank my parents for giving me a good education to get started on this career. Finally, Id like to also dedicate this to my good friend and neurosurgeon, the late Dr. John Shillito. Without his skilled work so many years ago I would not have had all that I have today.

ABSTRACT

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 books 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.

KEYWORDS

query language; query; RDBMS; relation database management systems; SQL; Structured query language; table

CONTENTS

I would first like to thank the thousands of students who have successfully used the Shore to Shore Shipping case study over the years. Through their feedback, the case study has been refined and made even more useful.

I would also like to thank my former institution, Wentworth Institute of Technology, as well as my current college, Landmark College, for the support during the writing of this book. I would also like to thank my mentor and friend, Meggin McIntosh who gave me invaluable guidance to bring this project to completion. In addition, I would like to thank my mentor and friend, Monte Unger, who helped me to realize my desire to write.

The study of relational database management systems is a core part of computer science education. In the workplace, you will find that having knowledge of database management systems and how to extract data from such systems is beneficial for many different job functions. This book does not cover the design of databases, but rather covers basic syntax to retrieve data from a database management system. If you are a student in computer science, this text will be an excellent companion to a text on database design. If you are a professional looking to learn SQL or expand your skills, this text will provide you with the groundwork necessary to expand your skills and begin learning SQL.

KEY FEATURES

Unified Case Study

While there are many SQL books on the market today, most use multiple datasets to teach concepts. Through 20 years of teaching database management systems and SQL, the author has found that students learn best by being taught how to think like the database management systems query processor. As such, the text starts off with an overview of the case study as well as many queries written out in long form, not using the language at this point. Once students have gained familiarity with the case study they are then introduced to the language in small bites, focusing on one particular aspect or clause of the select statement. All examples in the book can be run using MySQL or another relational database management system with the scripts provided on the books website.

On Your Own Exercises

Each chapter has a series of on your exercises with solutions provided on the books website. The exercises are not at the back of the chapter but occur whenever the coverage of one topic is finished before moving on to the next topic. It is important for students to use these as way to check on their understanding of the concepts just introduced.

Modular

SQL by Example has been designed to be modular in nature. Readers who have had some introduction to SQL can quickly read through the first chapter and then jump to any chapter where they feel a need to refresh their skills.

RDBMS Agnostic

As much as possible, the text has been designed to be used with any relational database management system.

PATHWAYS OF LEARNING

As mentioned earlier, all readers should start with in order to gain a firm understanding of the case study. There are then multiple pathways of learning. Readers without any SQL experience should read the text sequentially. More experienced readers can skip to sections of interest based on what skills they need to expand. The following table is a logical grouping of chapters.

SCRIPTS AND SOLUTION FILES

The scripts for the case study as well as solutions to the on your own exercises and scripts to run example queries can be found at www.profrusso.com/SQL_BY_EXAMPLE. Also, instructions for loading the scripts can be found here.

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: