SQL Run
Database Programming
| Previous |  Home |
 Contents |
Next |
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.
Figure 1Microsoft SQL Server > Microsoft SQL Server Management Studio > Connect to Server dialog box.
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.
Figure 2Microsoft SQL Server > Microsoft SQL Server Management Studio > New Database command.
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.
Figure 3Microsoft SQL Server > Microsoft SQL Server Management Studio > New Database > General page.
In Object Explorer, expand the Databases folder and then select the database books.
Figure 4Microsoft SQL Server > Microsoft SQL Server Management Studio > Object Explorer > books database.
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.
Figure 5Microsoft SQL Server > Microsoft SQL Server Management Studio > books database > contents of books_sqlserver.sql.
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.
Figure 6Microsoft SQL Server > Microsoft SQL Server Management Studio > books database > results of executing books_sqlserver.sql.
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.
Figure 7Oracle Database > Database Configuration Assistant > Database Operation page.
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.
Figure 8Oracle Database > Database Configuration Assistant > Creation Mode page.
On the Summary page, review the configuration options (and save them to a response file if you like), and then click Finish.
Figure 9Oracle Database > Database Configuration Assistant > Summary page.
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.
Figure 10Oracle Database > SQL*Plus > sqlplus system@books.
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.
Figure 11IBM Db2 > Data Studio > Administration Explorer tab > New Database command.
If the “New database” dialog box opens, then type your Db2 user name and password. Click Finish.
A “New database” tab opens.
Figure 12IBM Db2 > Data Studio > Administration Explorer tab > New Database command > New Database dialog box.
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.
Figure 13IBM Db2 > Data Studio > New Database tab > Details pane.
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.
Figure 14IBM Db2 > Data Studio > Administration Explorer tab > All Databases folder > books database.
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).
Figure 15IBM Db2 > db2batch command.
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.
Figure 16MySQL > mysqladmin command > mysql command.
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.
Figure 17PostgreSQL > createdb command > psql command.
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.