Table of Contents
To Nancy, of course.
Introduction
Welcome to The Complete Idiots Guide to SQL. The goal of this book is to give you a real, working knowledge of SQL, the language you use to communicate with databases.
Here, youre going to get a guided tour of SQL. But thats not all. Youre also going to get a guided tour of SQL as its used by your Database Management System (DBMS).
SQL is defined according to a strict standard. However, all modern DBMSes stray from that standard considerably. Most books on SQL ignore that fact and just give you the standard SQL story. But that story doesnt work a lot of the time in todays DBMSes.
Thats why we give you both the standard story and the story for the DBMS youre using. If any of the four major DBMSesMySQL, Oracle, Microsoft SQL Server, or PostgreSQLuse different syntax for a particular task, well let you know what that syntax is.
This book also differs from other books in that every point is illustrated with at least one example. You wont see just theoretical discussions of SQL tasks hereyoull see the actual tasks run in real DBMSes so you can get up to speed simply by taking a look at the examples.
Whats in This Book
This book consists of four parts, all of which give you the full SQL story with syntax relating to specific DBMSes. The following paragraphs discuss whats in those parts.
In Part 1, Mastering the SQL Basics, youll lay the foundation of your SQL journey. Here, well start with the SQL basics like understanding databases, tables, rows, and columns.
Then well see how to create database tables, store data in them, and retrieve that data. When youre done with this part, youll be able to take your data, put it into a database, and retrieve it from the database, all with the help of SQL.
In Part 2, Crunching Your Data, you get deeper into SQL, seeing what it can do for you and your data. Here, you investigate different options for sorting your data, such as simple sorts, group sorts, reverse sorts, custom sorts, and more.
Then you learn about filtering your data to extract just the information you want. This is where you see how to get all the data that matches the criteria you want.
Finally, in this part, you take a look at how to make changes to your stored data by updating it. You see how to update individual data items in a table, an entire row in a table, or even in a whole table.
In Part 3, In-Depth Data Handling, youll plunge a little deeper into manipulating data. You start by connecting tables so that the data items you read can come from one table or from a connected table. (Connecting tables like this means you dont have to store all your data in the same table.)
Next, you examine how SQL lets you group data inside the same table. Say, for example, that you have a table that keeps track of a set of employees, some of whom are in Sales and some of whom are in Accounting. By grouping the records in the table, you can collect those in Sales together and those in Accounting together, effectively forming two subtables in the same table. Then you can sort or examine each group of employees independently.
You also look at SQL views in this part. Views are temporary tables that you can use when you dont want to interfere with the data in a real table. You can assemble a view from some or all of the data in a real table, or combine data from two or more tables.
In Part 4, Power Techniques, you see the muscle of SQL. Here, you see how to store SQL code in procedures that you can call at any time. Thats great if you have long scripts of SQL that you need to execute over and over.
You also learn how to make your SQL operations secure with transactions. When you execute SQL as part of a transaction, you can undo or roll it back if you want toif something goes wrong at some point in a transaction, you can roll it back, leaving things as if the transaction never happened.
Finally, you look at SQL constraints and triggers. Constraints are rules you can set up for tables that limit the values you can enter into the table. For example, if you have a set of employees, you can constrain their ID values in the table to be in a certain range that you specify. Triggers are like more complex, more powerful constraints. They let you check whats going on every time someone inserts, updates, or deletes data in a table, and cause the action to fail if you dont approve. Using triggers, you can guard your data down to the last detail.
At the back of the book, there are two appendixes. Appendix A is a glossary of SQL terms. If you need fast access to a definition, this is the place to look. Appendix B is all about how to type in and run the examples in this book. Just how you do that depends on your DBMS, so take a look at how to enter SQL and execute it in Appendix B in order to follow along with the examples in this book.
Extras
You will also see sidebars throughout the book. These have the following meanings:
DEFINITION
These sidebars define unfamiliar terms scattered throughout the text.
SQL CAUTION
These sidebars warn you about potential problems you might encounter while using SQL.
SQL TIP
These sidebars give you inside information on SQL and what you will encounter as you work through the examples in the book.
Special Thanks to the Technical Reviewer
The Complete Idiots Guide to SQL was reviewed by an expert who double-checked the accuracy of what youll learn here, to help us ensure that this book gives you everything you need to know about SQL. Special thanks to Ken Bluttman.
Trademarks
All terms mentioned in this book that are known to be or are suspected of being trademarks or service marks have been appropriately capitalized. Alpha Books and Penguin Group (USA) Inc. cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.
Part 1
Mastering the SQL Basics
Were going to get our feet wet with SQL, first learning about crucial database concepts like tables, rows, columns, and even databases themselves.
Then well jump right in to see what SQL can do. Well create a table of data and see how to extract data from that table, and how to sort and filter that data to get just what you want.
When youve finished this part, youll be able to create database tables, insert data into them, and get that data back out again when you want it.
Chapter 1
Getting into SQL
In This ChapterAn introduction to SQL
An overview of databases
Understanding tables
Working with table columns
Storing records in rows
Putting some SQL to work
