Download rapid_sql_files.zip (sample database).
Create the sample database in your DBMS.
Get up to speed fast with SQL, the language of databases. Bestselling author Chris Fehily teaches you just the parts of SQL that you need to know. Quick, learn-by-example lessons start with simple data retrieval and sorting, move on to filtering and grouping, and then build to more-advanced topics, including joins, subqueries, views, and transactions. Whether you’re an analyst, programmer, webmaster, or personal user, you’ll find straightforward, practical answers. You can download the sample database to follow along with the examples.
1. Introduction
2. Database Basics
3. SQL Basics
4. Retrieving Data from a Table
5. Sorting Rows
6. Filtering Rows
7. Combining and Negating Conditions
8. Pattern Matching
9. More Ways to Filter Rows
10. Operators and Functions
11. Working with Functions
12. Evaluating Conditional Values
13. Summarizing Data
14. Grouping Data
15. Joining Tables
16. Working with Joins
17. Subqueries
18. Combining Queries
19. Inserting Rows
20. Updating and Deleting Rows
21. Creating and Changing Tables
22. Indexes
23. Views
24. Transactions
A. The Sample Database
B. Running SQL Programs
C. Data Types
The examples in Rapid SQL: A Self-Teaching Guide use the sample database books
, described in Appendix A, “The Sample Database.” To create the database books
, download the zip file rapid_sql_files.zip, expand it, and then follow the instructions for your DBMS, listed below. The file readme.txt describes the distribution. (This distribution also is compatible with the earlier edition of this book, SQL Short Course.)
In addition to creating the tables described in Appendix A, the SQL scripts create additional tables used in other examples. If you’re running a DBMS locally (that is, on your own computer), then you’re the database administrator (DBA) and have all the privileges that you need. If you’re connecting to a DBMS on a network server, then ask your DBA for connection parameters and the privileges to create, query, update, and drop databases and tables.
The following instructions for creating the sample database explain how to use simple tools and settings. As you gain experience, you might want to switch to using the statement CREATE
DATABASE
to create new databases. CREATE
DATABASE
is a powerful but nonstandard SQL command, so its syntax and capabilities vary by DBMS; see your DBMS’s documentation. (Microsoft Access doesn’t support CREATE
DATABASE
, but you can use Visual Basic for Applications or C# to create Access databases programmatically.)
To open the database books
in Microsoft Access:
If you’re running Access 97 or earlier, then you won’t be able to open books_rapid.mdb because it’s an Access 2000-format (.mdb) file. To create the sample-database tables, use the Import Text wizard to import the CSV files included in the distribution. A CSV (comma-separated values) file is a text file in which each column value is separated by a comma from the next column’s value and each row starts a new line. The first row contains column names. The CSV files for the various tables are named csv_authors.txt, csv_publishers.txt, and so on.
To import a CSV file as a table in Microsoft Access 97 or earlier:
To create the database books
in Microsoft SQL Server:
books
in the Database Name field, and then click OK. (The default values for the settings in the General, Options, and Filegroups pages are suitable for the sample database.)books
and then closes the New Database dialog box.books
.DROP
TABLE
statements, which are needed to rerun books_sqlserver.sql to restore the tables to their original states.Tip
pubdate
in the table titles
is DATETIME
(rather than DATE
). Also, date literals don’t have the DATE
keyword. (The standard SQL date value DATE
'2000-08-01'
, for example, is equivalent to the SQL Server date value '2000-08-01'
.)To create the database books
in Oracle Database:
books
in the “Global database name” box, select “File System” for the storage type, type and confirm an administrative password, clear “Create as Container database”, and then click Next.sqlplus
) and connect to the books
database.sqlplus user/password@dbname
books
, in this case). For security, you can omit the password and instead type:sqlplus user@dbname
system
and the password that you set in step 3:sqlplus system@books
command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu.@books_oracle.sql
sqlplus
displays the results. Ignore the messages about nonexistent tables—they’re caused by the script’s DROP
TABLE
statements, which are needed to rerun books_oracle.sql to restore the tables to their original states.Tip
au_fname
in the table authors
for author A06 is a space character (' '), rather than an empty string (''). This change prevents Oracle from interpreting the first name of author A06 as null; for details, see the DBMS tip in “Nulls” in Chapter 3.To create the database books
in IBM Db2 Database:
books
in the “Database name” box, specify a path in the “Database location” box, and then click Run. (The default values for the settings in the Storage and Locale panes are suitable for the sample database.)books
appears below the Db2 instance in the All Databases folder on the Administration Explorer tab.db2batch -d books -f books_db2.sql
-f
option specifies the name of the SQL file. You can include an absolute or relative pathname (see “Paths” in Appendix B). You can add the option -a
user[/password]
to connect to the database as a specific user.db2batch
displays the results. Ignore the messages about undefined names (nonexistent tables)—they’re caused by the script’s DROP
TABLE
statements, which are needed to rerun books_db2.sql to restore the tables to their original states.command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu.Tips
db2batch
in step 6, you can use the db2
command-line processor in script mode (see “IBM Db2 Database” in Appendix B).DATE
keyword. (The standard SQL date value DATE
'2000-08-01'
, for example, is equivalent to the Db2 date value '2000-08-01'
.)To create the database books
in MySQL:
mysqladmin -h host -u user -p create books
-p
option or press Enter at the password prompt). MySQL creates a new, empty database named books
.localhost
, set user to root
, and use the password that you assigned to the user root
when you set up or installed MySQL.command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu.mysql -h host -u user -p -f books < books_mysql.sql
-f
option forces mysql
to keep running even if an SQL error occurs. The redirection operator <
reads from the specified SQL file. You can include an absolute or relative pathname (see “Paths” in Appendix B).mysql
displays the results. Ignore the messages about unknown (nonexistent) tables—they’re caused by the script’s DROP
TABLE
statements, which are needed to rerun books_mysql.sql to restore the tables to their original states.Tips
To create the database books
in PostgreSQL:
createdb -h host -U user -W books
-W
option or press Enter at the password prompt). PostgreSQL creates a new, empty database named books
.localhost
, set user to postgres
, and use the password that you assigned to the user postgres
when you set up or installed PostgreSQL.command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu.psql -h host -U user -W -f books_postgresql.sql books
-f
option specifies the name of the SQL file. You can include an absolute or relative pathname (see “Paths” in Appendix B).psql
displays the results. Ignore the messages about nonexistent tables—they’re caused by the script’s DROP
TABLE
statements, which are needed to rerun books_postgresql.sql to restore the tables to their original states.Tips
To create the sample database in a DBMS that’s not covered in the book, edit and run one of the books_*.sql scripts included in the distribution. If your DBMS complies (or almost complies) with standard SQL, then you can run books_standard.sql with few or no changes.
If you can’t create the sample database by running an SQL script, then you can create the tables individually by importing the CSV files included in the distribution. A CSV (comma-separated values) file is a text file in which each column value is separated by a comma from the next column’s value and each row starts a new line. The first row contains column names. The CSV files for the various tables are named csv_authors.txt, csv_publishers.txt, and so on. All DBMSs (even non-SQL DBMSs) can import CSV files as tables—look for an Import or Load command.