The
Free SQL Book
Website Layout
This one-page website is organized into Sections A-D. Most visitors only need to read Section A and Section B.
A. Introduction to The Free SQL BooK
Abstract: First and foremost, this (950+ page) Free SQL Book is a tutorial introduction to SQL. The learning method is learn-by-example, presenting over 290 sample queries, plus exercises (including an answer book).
This book’s SQL statements have been tested on SQL Server, DB2, and ORACLE. Commentary identifies differences in syntax and behavior across these systems.
Like most other SQL books, this book presents the basic syntax and logic of SQL statements. Unlike many other SQL books, this book highlights important “know-your-data” and “know-your-logic” considerations. Hence this book is especially relevant for use in SQL professional training courses.
Also, unlike many other SQL books, this book includes optional appendices that address other database topics usually presented in an academic course. These appendices relate SQL to Codd’s Relational Model, database analysis & design, index design, and query optimization.
Absolutely Free or “Almost” Free: This book is absolutely free for practically all readers. No registration, no request for an email address, no cookies, etc. Just download the book and read it. Thereafter, please consider the author’s thoughts about “almost” free. Each satisfied reader is encouraged to make an optional $5.00 donation to some nonprofit environmental organization identified on Page 2 of the book. A restriction on the “freeness” of this book applies to for-profit educational organizations. (Page 2 presents details.)
Target Audience: This book is written for the following categories of readers.
SQL Rookie: Anyone who wants a “getting started” tutorial on SQL.
SQL Super-User: This person already knows some SQL and desires to become proficient in the language. Frequently, a super-user does not work in an IT Department. Instead, a super-user could be an accountant, engineer, actuary, bookie, or scientist who spends a considerable amount of time retrieving and manipulating data that is stored in a relational database.
Application Developer: This person has a working knowledge of some procedural programming language (e.g., Java, C++, COBOL, PHP, Python). Application developers usually write “embedded SQL” where SQL statements are embedded within an application program. An application developer must first learn SQL (as presented in this book) before she can embed SQL statements within a program. This book does not discuss the embedding process per se because the details, while not difficult, differ among various database systems and programming languages.
College Student: This person may be attending an academic course that covers a wide spectrum of database topics, including SQL. Typically, classroom time constraints imply that the instructor can offer no more than a few lectures on SQL. The tutorial orientation of this book allows such students to learn SQL via independent reading and homework exercises.
Table of Contents
Part I The SELECT Statement
Chapter 0 Read this Chapter!
Chapter 1 SELECT Statement
Chapter 2 ORDER BY Clause
Chapter 3 DISTINCT Keyword
Chapter 4 Logic: AND-OR-NOT
Chapter 5 Logic: IN and BETWEEN
Chapter 6 Pattern Matching: LIKE
Chapter 7 Arithmetic Expressions
Part II Built-in Functions & Null Values
Chapter 8 Aggregate Functions
Chapter 9 GROUP BY and HAVING Clauses
Chapter 9.5 Grouping by Multiple Columns
Chapter 10 Individual Functions
Chapter 10.5 Processing DATE Values
Chapter 11 Null Values
Part III Data Definition & Data Manipulation
Chapter 12 Preview Sample Sessions
Chapter 13 CREATE TABLE Statement
Chapter 14 CREATE INDEX Statement
Chapter 15 INSERT, UPDATE, & DELETE
Part IV Join Operations
Chapter 16 Inner-Join: Getting Started
Chapter 17 More about Inner-Join
Chapter 18 Multi-Table Inner-Join
Chapter 19 Outer-Join: Getting Started
Chapter 20 Multi-Table Outer-Joins
Chapter 20.5 Mixing Inner-Join & Outer-Join
Part V Set Operations & CASE Expressions
Chapter 21 UNION, INTERSECT, and EXCEPT
Chapter 22 CASE Expressions
Part VI Sub-SELECTs
Chapter 23 “Regular” Sub-SELECTs
Chapter 24 Sub-SELECTs in DML
Chapter 25 Correlated Sub-SELECTs
Chapter 26 Inline Views
Chapter 27 Common Table Expressions
Chapter 28 CREATE VIEW Statement
Part VII Special Topics
Chapter 29 Transaction Processing
Chapter 30 Recursive Queries
Book Appendices
I. Create Sample Tables for The Free SQL Book
II. Obtain Access to a Relational Database
III. Summary of Chapter Appendices
IV. Post-Relational Database Systems
V. Abbreviated Bibliography
B. Download Free SQL Book
&
Related Documents
Download The Free SQL Book
The following link downloads The Free SQL Book as a PDF file.
Download Answer Book
The following link downloads the Exercise Answer Book as a PDF file.
Download SQL Scripts(s) to Create Sample Tables
Before downloading any SQL script, rookie users should read Book-Appendix-I (located at the end of The Free SQL Book) for a short tutorial about SQL scripts. There are two options for downloading and executing scripts that create sample tables. The first option creates two tables that are referenced in the first five chapters. The second option creates all sample tables referenced in the book.
Option-1: The Chpt-1-5 Script creates and populates the only two tables (PRESERVE and EMPLOYEE) referenced in Chapters 1-5. This script should execute on DB2, ORACLE, SQL Server and all other relational database systems.
Option-2: One of three scripts can be executed by users of DB2, ORACLE, or SQL Server. (If you are using some other relational database system you should be able to use the DB2 script with very few modifications.) Each script creates all sample tables referenced in The Free SQL Book. These scripts, while very similar to each other, account for some differences between DB2, ORACLE, and SQL Server.
C. Notes to SQL Instructors
Author Observations re. Academic Courses: I have taught undergraduate and graduate level database concept courses that cover a broad spectrum of database topics, including a short introduction to SQL. Students were expected to gain a basic (not proficient) knowledge of SQL. It these courses I devoted no more than three lectures to SQL and assigned selected chapters as homework.
Author Observations re. Professional Training Classes: I have taught many 2-4-day professional training SQL courses for students who already knew some SQL. Here I could assume that students already knew elementary logic and basic SQL data types. For review purposes, I would present a few sample queries from Chapters 1-11 and solicit student questions. Then I would proceed present many (but not all) sample queries from Chapters 12-30. Contingent upon customer objectives, I would usually skip the optional chapters (Chapters 9.5, 10.5, 13, 14, 28-30).
Book Layout for Sample Queries: This book frequently presents a sample query on two pages as illustrated by Sample Query 1.1 on Pages 24-25. Here:
The “Left-Page” (even-numbered page) presents:
-
the query objective,
-
the SQL statement that satisfies this objective. and
-
the result table
The “Right-Page” (odd-numbered page) presents commentary on syntax and logic.
This layout facilitates reading a physical book. However, hopefully very few readers will print this large book. (Save a tree!) Within a traditional classroom setting, some projection devices can simultaneously display both pages with good readability. If both pages cannot be displayed, the instructor can display the even-numbered page showing the SQL statement and result table and speak to the syntax and logic issues that are presented on the following odd-numbered page.
Classroom Time Constraints: Time constraints never allow an instructor to discuss all sample queries. In an academic course, the instructor can present a few sample queries from selected chapters and assign independent reading and exercises. (The tutorial flavor of this book facilitates independent reading.) In a professional training class, where the assignment of independent reading is rarely practical, the instructor can present selected sample queries according to the customer’s objectives and students’ current knowledge of SQL.
Chapter Appendices: Optional appendices are included at the end of some chapters. These appendices offer by-the-way commentary on database topics that are related to the SQL statements presented in the corresponding chapter.
Some appendices introduce topics that are discussed within an academic course on database concepts. (For example, an academic course may devote multiple lectures to database analysis and design.) Within an SQL professional training course, the instructor rarely has enough time to digress into these appendix topics. However, these topics may be briefly discussed off-line with interested students. Business-oriented systems may be interested the appendices that discuss database analysis and design. Computer science students may be interested in appendices that discuss query efficiency and query optimization. Mathematicians may be interested in appendices that discuss relational database theory.
These chapter appendices are included in this book because I want readers to know that SQL is a small but important component of the large and very interesting database world. Hopefully some readers will be motivated to progress beyond SQL and explore this world.
D. Feedback to Author
Reader feedback can help the author detect and correct the (hopefully very few) errors that appear in this book. After reading the following commentary, readers are invited to email feedback to the author at freesqlbook@gmail.com. Please preview potential errors in the subject line. Examples: [abbreviating “Sample Query” as SQ]
-
Incorrect SQL for SQ x.x
-
Incorrect result table for SQ x.x
-
Commentary on SQ x.x is wrong/confusing
Author Commentary: When writing earlier SQL books on DB2 and ORACLE, I was fortunate to have two editors. First and foremost was my coauthor, Tim Hartley, whose critical feedback was invaluable. The second editor was hired by the publisher (McGraw Hill). Hence most errors were detected and corrected before publication. Here, I am my own editor, and I am tired of reading my own writing. I know there must be some errors that I just don’t see. Therefore, corrective feedback from readers will be greatly appreciated.
I want to emphasize that I truly appreciate any reader who takes the time to offer feedback. I emphasize my appreciation because my response will probably be no more than a brief “Thanks.” Sadly, such a minimal response reflects a personal character flaw, my painfully slow typing skills, and life’s obligations. (Mea Cupla.}
Reader feedback should help me discover:
-
SQL Errors - Incorrect SQL is a mortal sin. This is my highest priority because such errors are very embarrassing. (With an apology to all English majors, I see imperfect writing as a venial sin.)
-
Confusing Commentary - I want to know where this book fails to offer a coherent explanation of SQL syntax and logic. (Please note that I am too lazy to attempt to transform a correct and understandable sentence into a “perfect” sentence.)
-
Spelling & Unforgivable Grammar Errors - My spell-checker should have prevented spelling errors. Because I have taken some liberties with grammar, I only want to know about “unforgivable” grammar errors. (Sometime in the future, after all SQL issues have been resolved, I may invite the English majors to take out their red pencils.)
I conclude with a simple but very sincere “thank you.”
Tim M.