SQL Database Programming (Fifth Edition)

SQL Database Programming (Fifth Edition)
by Chris Fehily

Download sql_files.zip (sample database).

Create the sample database in your DBMS.

Description

Perfect for end users, analysts, data scientists, students, and developers, this best-selling guide will get you up and running with SQL, the language of databases. You’ll find general concepts, practical answers, and clear explanations of what the various SQL statements can do. Hundreds of examples of varied difficulty encourage you to experiment and explore. Formatted SQL code listings help you see the elements and structure of the language. You can download the sample database to follow along with the author’s examples.

Contents

Introduction
1. Running SQL Programs
2. The Relational Model
3. SQL Basics
4. Retrieving Data from a Table
5. Operators and Functions
6. Summarizing and Grouping Data
7. Joins
8. Subqueries
9. Set Operations
10. Inserting, Updating, and Deleting Rows
11. Creating, Altering, and Dropping Tables
12. Indexes
13. Views
14. Transactions
15. Advanced SQL

Excerpt

Amazon preview

Purchase

Paperback
   Amazon paperback (US, CA, UK, AU, DE)

Ebook
   Amazon Kindle (US, CA, UK, AU, DE)
   Apple Books (US, CA, UK, AU, DE)
   Barnes & Noble Nook
   Google Play

Creating the Sample Database

The examples in SQL Database Programming use the sample database books, described in “The Sample Database” in Chapter 2. To create the database books, download the zip file 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 editions of this book, as well as all editions of SQL: Visual QuickStart Guide.)

In addition to creating the tables described in “The Sample Database” in Chapter 2, 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.)

Opening the Sample Database in Microsoft Access

To open the database books in Microsoft Access:

  1. 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.

  2. 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.

  3. To run SQL statements against the database, see “Microsoft Access” in Chapter 1.

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:

  1. In Access, open or create a database, or press F11 to switch to the Database window for the open database.
  2. Choose File > Get External Data > Import.
  3. In the Import dialog box, in the Files of Type box, select Text Files.
  4. Navigate to the drive or folder containing the CSV file, and then double-click its icon.
  5. Follow the onscreen instructions in the Import Text wizard. Click Advanced to create or use an import/export specification. (To cancel importing, press Ctrl+Break.)

Creating the Sample Database in Microsoft SQL Server

To create the database books in Microsoft SQL Server:

  1. On the Windows desktop, choose Start > Microsoft SQL Server Tools > Microsoft SQL Server Management Studio.

    Microsoft SQL Server Management Studio opens.

  2. In the Connect to Server dialog box, select the server and authentication mode, and then click Connect.
    Microsoft SQL Server - Microsoft SQL Server Management Studio - Connect to Server dialog box
  3. 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).

  4. Right-click the Databases folder and then choose New Database.

    The New Database dialog box opens.
    Microsoft SQL Server - Microsoft SQL Server Management Studio - New Database command
  5. 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.
    Microsoft SQL Server - Microsoft SQL Server Management Studio - New Database dialog box - General page
  6. In Object Explorer, expand the Databases folder and then select the database books.
    Microsoft SQL Server - Microsoft SQL Server Management Studio - Object Explorer - books Database
  7. 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.
    Microsoft SQL Server - Microsoft SQL Server Management Studio - books Database - contents of books_sqlserver.sql
  8. 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.
    Microsoft SQL Server - Microsoft SQL Server Management Studio - books Database - results of executing books_sqlserver.sql
  9. To run SQL scripts and interactive statements against the database, see “Microsoft SQL Server” in Chapter 1.

Tip

Creating the Sample Database in Oracle Database

To create the database books in Oracle Database:

  1. 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.

  2. On the Database Operation page, select “Create a database”, and then click Next.
    Oracle Database - Configuration Assistant Database - Database Operation page
  3. 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.
    Oracle Database - Configuration Assistant Database - Creation Mode page
  4. On the Summary page, review the configuration options (and save them to a response file if you like), and then click Finish.
    Oracle Database - Configuration Assistant Database - Summary page
  5. On the Progress Page, a progress meter and status list appears while Oracle creates the database.

  6. The Finish page appears when Oracle finishes creating the database. Review the database information and then click Close to exit the Database Configuration Assistant.

  7. 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.

  8. 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.

    Oracle Database - SQL*Plus - sqlplus system@books
  9. To run SQL scripts and interactive statements against the database, see “Oracle Database” in Chapter 1.

Tip

Creating the Sample Database in IBM Db2 Database

To create the database books in IBM Db2 Database:

  1. Open Data Studio.

    This procedure varies by platform. In Microsoft Windows, for example, choose Start > IBM Data Studio > Data Studio Client.

  2. 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.

    IBM Db2 - Data Studio - Administration Explorer tab - New Database command
  3. If the “New database” dialog box opens, then type your Db2 user name and password. Click Finish.

    A “New database” tab opens.

    IBM Db2 - Data Studio - New Database dialog box

  4. 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.

    IBM Db2 - Data Studio - New Database tab - Details pane
  5. 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.

    IBM Db2 - Data Studio - Administration Explorer tab - All Databases folder - books database
  6. 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.

    IBM Db2 - db2batch
  7. To run SQL scripts and interactive statements against the database, see “IBM Db2 Database” in Chapter 1.

Tips

Creating the Sample Database in MySQL

To create the database books in MySQL:

  1. 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.

  2. 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.
    MySQL - mysqladmin - mysql
  3. To run SQL scripts and interactive statements against the database, see “MySQL” in Chapter 1.

Tips

Creating the Sample Database in PostgreSQL

To create the database books in PostgreSQL:

  1. 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.

  2. 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.
    PostgreSQL - createdb - psql
  3. To run SQL scripts and interactive statements against the database, see “PostgreSQL” in Chapter 1.

Tips

Creating the Sample Database in Other DBMSs

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.