The examples in SQL Run use the sample database books
, described in “The Sample Database” in Chapter 2. The sample database also includes additional tables that are used in the book’s more-advanced examples.
Note: This sample database also is compatible with the author’s earlier SQL books, including SQL Database Programming (all editions), SQL: Visual QuickStart Guide (all editions), Rapid SQL: A Self-Teaching Guide, SQL Short Course, and SQL Tricks.
To download and create the sample database books
:
Download the zip file sql_run_files.zip and then expand (uncompress) it.
The text file readme.txt, included in the zip file, lists and describes the files in the distribution.
Tip: Microsoft Windows refers to a zip file as a compressed folder.
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 to run SQL scripts.
or
If you’re connecting to a DBMS on a network server, then ask your DBA for connection parameters and the privileges to run SQL scripts that create, query, update, and drop databases, tables, and other database objects.
Follow the instructions below to create the sample database for your DBMS:
CREATE DATABASE
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 Visual C# to create Access databases programmatically.
To open the database books
in Microsoft Access:
In File Explorer (or Windows Explorer), navigate to the drive or folder containing the database file books.mdb and then double-click its icon.
Microsoft Access starts and the database opens.
In Microsoft Access 2007 or later, to inspect the database tables, press F11 to show the Navigation pane. Click the menu at the top of the pane and choose Object Type, and then click the menu again and choose Tables (or All Access Objects).
In Microsoft Access 2000, 2002, or 2003, to inspect the database tables, click Tables (below Objects) in the Database window.
If you’re running Microsoft 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:
On the Windows desktop, choose Start > Microsoft SQL Server Tools > Microsoft SQL Server Management Studio.
Microsoft SQL Server Management Studio opens.
In the Connect to Server dialog box, select the server and authentication mode, and then click Connect.
In Object Explorer (the left pane), navigate to the Databases folder of the server that you’re using.
If Object Explorer isn’t visible, then choose View > Object Explorer (or press F8).
Right-click the Databases folder and then choose New Database.
The New Database dialog box opens.
On the General page, type 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.)
SQL Server creates the database books
and then closes the New Database dialog box.
In Object Explorer, expand the Databases folder and then select the database books
.
Choose File > Open > File (or press Ctrl+O), navigate to the drive or folder containing the file books_sqlserver.sql, select its icon, and then click Open.
The file’s contents appear in a new tab in the right pane.
Choose Query > Execute (or press F5).
SQL Server Management Studio displays the results in the bottom pane. Ignore the messages about nonexistent tables—they’re caused by the script’s DROP
TABLE
statements, which are needed to rerun books_sqlserver.sql to restore the tables to their original states.
To run SQL scripts and interactive statements against the database, see “Microsoft SQL Server” in Chapter 1.
Tip: The script books_sqlserver.sql differs slightly from the standard SQL script books_standard.sql. In the SQL Server script, the data type of the column 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:
Start Database Configuration Assistant.
This procedure varies by platform. In Microsoft Windows, for example, choose Start > Oracle - OraDB18Home1 > Database Configuration Assistant.
Database Configuration Assistant guides you through the steps needed to create a database.
On the Database Operation page, select “Create a database”, and then click Next.
On the Creation Mode page, select “Typical configuration”, type 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.
On the Summary page, review the configuration options (and save them to a response file if you like), and then click Finish.
On the Progress Page, a progress meter and status list appears while Oracle creates the database.
The Finish page appears when Oracle finishes creating the database. Review the database information and then click Close to exit the Database Configuration Assistant.
Start SQL*Plus (sqlplus
) and connect to the books
database.
At an administrator command prompt, type:
sqlplus user/password@dbname
user is your Oracle user name, password is your password, and dbname is the name of the database to connect to (books
, in this case). For security, you can omit the password and instead type:
sqlplus user@dbname
SQL*Plus will prompt you for your password.
If you’re running Oracle locally, then you can use the user name system
and the password that you set in step 3:
sqlplus system@books
If you’re connecting to a remote Oracle database, then ask your database administrator (DBA) for the connection parameters.
Tip: To open an administrator command prompt in Microsoft Windows, tap the Windows Logo Key (or click Start), type command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu.
At the SQL prompt, type:
@books_oracle.sql
and then press Enter. You can include an absolute or relative pathname (see “Paths” in Chapter 1).
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.
To run SQL scripts and interactive statements against the database, see “Oracle Database” in Chapter 1.
Tip: The script books_oracle.sql differs slightly from the standard SQL script books_standard.sql. In the Oracle script, the value in the column 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:
Open Data Studio.
This procedure varies by platform. In Microsoft Windows, for example, choose Start > IBM Data Studio > Data Studio Client.
On the Administration Explorer tab (on the left), expand the All Databases folder of the object tree until you find your instance of Db2, right-click the instance, and then click New Database in the context menu.
If the “New database” dialog box opens, then type your Db2 user name and password. Click Finish.
A “New database” tab opens.
In the Details pane on the “New database” tab, type 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.)
A progress meter appears while Db2 creates the database.
When Db2 finishes creating the database, the new database books
appears below the Db2 instance in the All Databases folder on the Administration Explorer tab.
At an administrator command prompt, type:
db2batch -d books -f books_db2.sql
and then press Enter. The -f
option specifies the name of the SQL file. You can include an absolute or relative pathname (see “Paths” in Chapter 1). 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.
Tip: To open an administrator command prompt in Microsoft Windows, tap the Windows Logo Key (or click Start), type command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu.
Tip: Instead of db2batch
in this step, you can use the db2
command-line processor in script mode (see “IBM Db2 Database” in Chapter 1).
To run SQL scripts and interactive statements against the database, see “IBM Db2 Database” in Chapter 1.
Tip: The script books_db2.sql differs slightly from the standard SQL script books_standard.sql. In the Db2 script, date literals don’t have the 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:
At an administrator command prompt, type:
mysqladmin -h host -u user -p create books
host is the host name, and user is your MySQL user name. MySQL will prompt you for your password (for a passwordless user, either omit the -p
option or press Enter at the password prompt). MySQL creates a new, empty database named books
.
If MySQL is running on a remote network computer, then ask your database administrator (DBA) for the connection parameters. If you’re running MySQL locally (that is, on your own computer), then set host to localhost
, set user to root
, and use the password that you assigned to the user root
when you set up or installed MySQL.
Tip: To open an administrator command prompt in Microsoft Windows, tap the Windows Logo Key (or click Start), type command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu. As an alternative to the command prompt, you can use the MySQL Workbench graphical tool at mysql.com/products/workbench.
Tip: You can set the environment variable MYSQL_HOST to specify the default host name used to connect to the database. See “Environment Variables” in MySQL documentation.
At the administrator command prompt, type:
mysql -h host -u user -p -f books < books_mysql.sql
The -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 Chapter 1).
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.
To run SQL scripts and interactive statements against the database, see “MySQL” in Chapter 1.
Tip: The script books_mysql.sql is the same as the standard SQL script books_standard.sql.
To create the database books
in PostgreSQL:
At an administrator command prompt, type:
createdb -h host -U user -W books
host is the host name, and user is your PostgreSQL user name. PostgreSQL will prompt you for your password (for a passwordless user, either omit the -W
option or press Enter at the password prompt). PostgreSQL creates a new, empty database named books
.
If PostgreSQL is running on a remote network computer, then ask your database administrator (DBA) for the connection parameters. If you’re running PostgreSQL locally (that is, on your own computer), then set host to localhost
, set user to postgres
, and use the password that you assigned to the user postgres
when you set up or installed PostgreSQL.
Tip: To open an administrator command prompt in Microsoft Windows, tap the Windows Logo Key (or click Start), type command
, right-click “Command Prompt” in the results list, and then choose “Run as administrator” in the context menu. As an alternative to the command prompt, you can use the pgAdmin graphical tool. If the PostgreSQL installer didn’t install pgAdmin automatically, then you can download it for free at pgadmin.org.
Tip: You can set the environment variables PGHOST, PGDATABASE, and PGUSER to specify the default host, database, and user names used to connect to the database. See “Environment Variables” in PostgreSQL documentation.
At the administrator command prompt, type:
psql -h host -U user -W -f books_postgresql.sql books
The -f
option specifies the name of the SQL file. You can include an absolute or relative pathname (see “Paths” in Chapter 1).
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.
To run SQL scripts and interactive statements against the database, see “PostgreSQL” in Chapter 1.
Tip: The script books_postgresql.sql is the same as the standard SQL script books_standard.sql.
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.