1. Running SQL Programs

Sections
DBMSs and SQL Tools
Microsoft Access
Microsoft SQL Server
Oracle Database
IBM Db2 Database
MySQL
PostgreSQL

You need a database management system to run SQL programs. You can have your own private copy of a DBMS running on your personal (local) computer, or you can use a shared DBMS over a network. In the latter case, you use your personal computer to connect to a DBMS server running on another machine. The computer where the DBMS is running is called a host.

Older DBMSs and
Backward Compatibility of SQL

At this writing, the current (“stable”) releases of the DBMSs covered in this book are Microsoft Access 2019, Microsoft SQL Server 2019, Oracle Database 19c, IBM Db2 Database 12, MySQL 8, and PostgreSQL 13. New releases bring new features and fixes, but also bring loss of familiarity, workflow disruptions, new bugs, dropped features, incompatibilities, installation nightmares, endless testing, revised license agreements, new prices, and a chance of data loss. Given these risks and headaches, most DBMS users, from individuals to large organizations, typically are very slow to upgrade their DBMS.

This book favors SQL code that’s backward compatible, meaning it runs on older (“legacy”) systems. Each new release of a DBMS brings additions (whether standard or nonstandard) to its implementation of SQL. Given a choice, opt for tried-and-true legacy SQL code, which runs on legacy and current systems. For basic SQL (SELECT, JOIN, INSERT, CREATE, and so on), the DBMS’s optimizer runs legacy code at the same speed and efficiency as new-style code, so there’s no disadvantage in using legacy SQL code in most cases.

DBMSs and SQL Tools

This chapter describes how to run SQL programs on these DBMSs:

Microsoft Access’s graphical interface lets you run only one SQL statement at a time. The other systems, all DBMS servers, let you run SQL programs in interactive mode or script mode. In interactive mode, you type individual SQL statements at a command prompt and view the results of each statement separately, so input and output are interleaved. In script mode (also called batch mode), you save your entire SQL program in a text file (called a script or a batch file), and a command-line tool takes the file, executes the program, and returns the results without your intervention. I use the sample database and the SQL program shown in Listing 1.1 in all the examples in the rest of this chapter. The examples give the minimal syntax of command-line tools; the complete syntax is given in the DBMS documentation.

Listing 1.1This file, named listing0101.sql, contains a simple SQL SELECT statement, which is used to query the sample database in subsequent DBMS examples.

SELECT au_fname, au_lname
  FROM authors
  ORDER BY au_lname;

The Command Line

Most database professionals prefer to submit commands and SQL scripts through a DBMS’s command-line environment rather than mousing around the menus and windows of a graphical front-end. (Database administrators don’t add 1000 users by pointing and clicking.) If you’re new to DBMSs, then you might find the command line to be cryptic and intimidating, but experience will show you its power, simplicity, and speed. Graphical tools do have a few advantages, though:

You can find free full-featured SQL shells that work across DBMSs by searching the web for sql front end or sql client. Unix lovers stuck with Windows can use Windows Subsystem for Linux (docs.microsoft.com/windows/wsl). Windows PowerShell (microsoft.com/powershell) also provides advanced scripting.

Paths

A path (or pathname) specifies the unique location of a directory (folder) or file in a filesystem hierarchy. An absolute path specifies a location completely, starting at the topmost node of the directory tree, called the root. A relative path specifies a location relative to the current (or working) directory. In Windows, an absolute path starts with a backslash (\) or with a drive letter followed by a colon and a backslash (C:\, for example). In Unix or macOS Terminal, an absolute path starts with a slash (/).

C:\Program Files\Microsoft SQL Server (Windows) and /usr/local/bin/mysql (Unix) are absolute paths, for example. scripts\listing0101.sql (Windows) and doc/readme.txt (Unix) are relative paths. Absolute paths for files and folders on a network also can begin with a double backslash and server name (\\servername, for example). If a path contains spaces, then surround the entire path with double quotes. When you specify the name of an SQL file in script mode, you can include an absolute or relative path.

To run a command-line tool from an arbitrary directory, your PATH environment variable must include the directory that actually contains the tool. This environment variable lists the directories (folders) that the OS searches for programs. For some DBMSs, the installer handles the PATH details; for others, you must add the tool’s directory to PATH yourself.

To view the contents of the PATH variable, type path (Windows) or echo $PATH (Unix or macOS Terminal) at a command prompt. To change the PATH, add the absolute path of the directory in which the tool resides to the PATH environment variable. Search Help for environment variable (Windows), or modify the path command in your login initialization file, usually named .bash_login, .bashrc, .cshrc, .login, .profile, or .shrc (Unix or macOS).

Microsoft Access

Microsoft Access is a personal and commercial desktop DBMS that supports small and medium-size databases. Learn about Access at products.office.com/access and download a free trial copy. To determine which version of Access you’re using, choose File tab > Account > About Access.

In Access, you must turn on ANSI-92 SQL syntax to run many of the examples in this book.

To turn on ANSI-92 SQL syntax for a Microsoft Access database:

  1. In Microsoft Access, open the database.
  2. Choose File tab > Options > Object Designers (in the left pane).
  3. Below SQL Server Compatible Syntax (ANSI 92), select “This database”. (Figure 1.1).

    Figure 1.1Select this checkbox to turn on ANSI-92 SQL syntax mode for the open database.

    Screenshot: Selected checkbox for This Database in section SQL Server Compatible Syntax (ANSI 92)

  4. Click OK.

    Access closes, compacts, and then reopens the database before the new setting takes effect. You might see a few warnings, depending on your security settings.

ANSI-89 vs. ANSI-92 SQL

Be careful switching between ANSI-89 and ANSI-92 SQL syntax modes in Microsoft Access. The modes aren’t compatible, so you should pick a mode when you create a database and never change it. The range of data types, reserved words, and wildcard characters differs by mode, so SQL statements created in one mode might not work in the other. The older ANSI-89 standard is limited compared with ANSI-92, so you should choose ANSI-92 syntax for new databases. For more information, see “SQL Standards and Conformance” in Chapter 3.

If you’re using Microsoft Access as a front-end to query a Microsoft SQL Server database, then you must use ANSI-92 syntax, available in Access 2000 or later.

If you’re a casual Access user, then you’ve probably used the query design grid to create a query. When you create a query in Design View, Access builds the equivalent SQL statement behind the scenes for you. You can view, edit, and run the SQL statement in SQL View.

You can run only a single SQL statement through an Access Query object. To run multiple statements, use multiple Query objects or a host language such as Visual Basic or C#.

To run an SQL statement in Microsoft Access:

  1. In Microsoft Access, open a database.
  2. Choose Create tab > Queries group > Query Design (Figure 1.2).

    Figure 1.2Query Design lets you skip the hand-holding wizards.

    Screenshot: Create tab | Queries group | Query Design selected

  3. Without adding tables or queries, click Close in the Show Table dialog box (Figure 1.3).

    Figure 1.3You don’t need to add tables graphically because the SQL statement specifies the tables.

    Screenshot: The Show Table dialog box

  4. To run the SQL statement, choose Design tab > Results group > SQL View (Figure 1.4).

    Figure 1.4SQL View hides the graphical query grid and instead shows a text editor where you can type or paste an SQL statement.

    Screenshot: Design tab | Results group | SQL View selected

  5. Type or paste an SQL statement (Figure 1.5).

    Figure 1.5Enter an SQL statement...

    Screenshot: SQL View text editor with SQL statement SELECT au_fname, au_lname FROM authors ORDER BY au_lname;

  6. Choose Design tab > Results group > Run (Figure 1.6).

    Figure 1.6...and run it.

    Screenshot: Design tab | Results group | Run selected

    Access displays the result of a SELECT statement (Figure 1.7) but blocks or executes other types of SQL statements, with or without warning messages, depending on your settings.

    Figure 1.7Microsoft Access displays the result of a SELECT statement.

    Screenshot: The result of an SQL SELECT statement displayed in a table

Tip: To display a list of existing queries, press F11 to show the Navigation pane (on the left), click the menu at the top of the pane, choose Object Type, click the menu again, and then choose Queries. (The Navigation pane replaced the Database window of early Access versions.)

Microsoft SQL Server

Microsoft SQL Server is a commercial DBMS that supports very large databases and numbers of transactions. It runs on Microsoft Windows and Linux operating systems and is complex enough to require a full-time database administrator (DBA) to run and maintain it.

Learn about SQL Server products at microsoft.com/sql and download a free trial copy of SQL Server or a (permanently) free copy of SQL Server Express. SQL Server Express is a free, limited version of SQL Server. To run SQL programs in SQL Server or SQL Server Express, you can use the SQL Server Management Studio graphical tool or the sqlcmd command-line tool.

To determine which version of Microsoft SQL Server you’re using, run the SQL Server command-line command

sqlcmd -S server\instance_name -E -Q "SELECT @@VERSION;"

server\instance_name is the named instance of SQL Server to which to connect. Alternatively, run the query

SELECT SERVERPROPERTY('ProductVersion');

or

SELECT @@VERSION;

Tip: You can use the SET ANSI_DEFAULTS ON option to make SQL Server conform to standard SQL more closely.

To use SQL Server Management Studio:

  1. On the Windows desktop, choose Start > Microsoft SQL Server Tools > Microsoft SQL Server Management Studio.
  2. In the Connect to Server dialog box, select the server and authentication mode, and then click Connect.
  3. In Object Explorer (the left pane), expand the Databases folder of the server that you’re using, and then select a database (Figure 1.8).

    If Object Explorer isn’t visible, then choose View > Object Explorer (or press F8).

    Figure 1.8SQL Server Management Studio uses the selected database to resolve references in your SQL statements.

    Screenshot: The database books selected in the Databases folder of Object Explorer in SQL Server Management Studio

  4. To run SQL interactively, click New Query (on the toolbar) or right-click the database (in Object Explorer) and choose New Query in the context menu. Type or paste an SQL statement in the empty tab that appears in the right pane.

    or

    To run an SQL script, choose File > Open > File (or press Ctrl+O), navigate to and select the script file, and then click Open. The file’s contents appear in a new tab in the right pane.

  5. Click Execute (on the toolbar) or choose Query > Execute (or press F5).

    SQL Server displays the results in the bottom pane (Figure 1.9).

    Figure 1.9The result of a SELECT statement in SQL Server Management Studio.

    Screenshot: The result of an SQL SELECT statement displayed in a table in SQL Server Management Studio

To use the sqlcmd command-line tool interactively:

  1. At an administrator command prompt, type:

    sqlcmd -S server\instance_name -d dbname

    server\instance_name is the named instance of SQL Server to which to connect, and dbname is the name of the database to use.

  2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter.
  3. Type go and then press Enter to display the result. (Figure 1.10).

    Figure 1.10The result of a SELECT statement in sqlcmd interactive mode.

    Screenshot: The result of running an SQL SELECT statement by using sqlcmd in interactive mode at a command prompt

To use the sqlcmd command-line tool in script mode:

  1. At an administrator command prompt, type:

    sqlcmd -S server\instance_name -d dbname -i sql_script

    server\instance_name is the named instance of SQL Server to which to connect, and dbname is the name of the database to use. sql_script is a text file containing SQL statement(s) and can include an absolute or relative path.

  2. Press Enter to display the results (Figure 1.11).

    Figure 1.11The result of a SELECT statement in sqlcmd script mode.

    Screenshot: The result of running an SQL SELECT statement by using sqlcmd in script mode at a command prompt

To exit the sqlcmd command-line tool:

To show sqlcmd command-line options:

sqlcmd tries to use a trusted connection by default. If instead you have to specify a user name and password, then add the option -U login_id. login_id is your user name. sqlcmd will prompt you for your password.

If SQL Server is running on a remote network computer, then the sqlcmd option -S server\instance_name is required to specify the SQL Server instance to connect to. Ask your database administrator for the connection parameters. The -S option also works for local connections, when SQL Server is running on your own personal computer rather than on a server elsewhere.

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.

Oracle Database

Oracle Database is a commercial DBMS that supports very large databases and numbers of transactions. It runs on many operating systems and hardware platforms and is complex enough to require a full-time database administrator (DBA) to run and maintain it.

Learn about Oracle products at oracle.com and download Oracle Express Edition (XE)—a free, limited version of Oracle Database. Documentation is at docs.oracle.com.

To determine which version of Oracle you’re using, run the query

SELECT banner FROM v$version;

The Oracle version also is displayed in the initial “Connected to” message that appears when you log on to SQL*Plus.

To run SQL programs, use the sqlplus command-line tool.

To use the sqlplus command-line tool interactively:

  1. 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. For security, you can omit the password and instead type:

    sqlplus user@dbname

    sqlplus will prompt you for your password.

  2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.12).

    Figure 1.12The result of a SELECT statement in sqlplus interactive mode.

    Screenshot: The result of running an SQL SELECT statement by using sqlplus in interactive mode at a command prompt

To use the sqlplus command-line tool in script mode:

  1. At an administrator command prompt, type:

    sqlplus user/password@dbname @sql_script

    user is your Oracle user name, password is your password, dbname is the name of the database to connect to, and sql_script is a text file containing SQL statement(s) and can include an absolute or relative path. For security, you can omit the password, and instead type:

    sqlplus user@dbname @sql_script

    sqlplus will prompt you for your password.

  2. Press Enter to display the results (Figure 1.13).

    Figure 1.13The result of a SELECT statement in sqlplus script mode.

    Screenshot: The result of running an SQL SELECT statement by using sqlplus in script mode at a command prompt

To exit the sqlplus command-line tool:

To show sqlplus command-line options:

If you’re running Oracle locally, then you can use the user name system and the password that you specified when you created the database:

sqlplus system@dbname

If you’re connecting to a remote Oracle database, then ask your database administrator for the connection parameters.

An alternative way to start sqlplus in Windows is to choose Start > Oracle > SQL Plus.

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 Database

IBM Db2 Database is a commercial DBMS that supports very large databases and numbers of transactions. It runs on many operating systems and hardware platforms and is complex enough to require a full-time database administrator (DBA) to run and maintain it.

Learn about Db2 products at ibm.com/db2 and download a free trial copy of Db2 or a (permanently) free copy of IBM Db2 Community Edition.

To determine which version of Db2 you’re using, run the Db2 command-line command

db2level

or run the query

SELECT service_level
  FROM SYSIBMADM.ENV_INST_INFO;

To run SQL programs, use the IBM Data Studio graphical tool or the db2 command-line processor (CLP).

To use Data Studio:

  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, and then select a database (below the Db2 instance).

    If the desired database doesn’t appear, then right-click the All Databases folder, choose “New Connection to a Database” in the context menu, and then connect to the target database.

  3. To run SQL interactively, click New SQL Script New SQL Script button on the Administration Explorer toolbar. On the Script tab that opens, type or paste an SQL statement in the box.

    or

    To run an SQL script, choose File > Open File, navigate to and select the script file, and then click Open. On the File tab that opens, click No Connection (if it appears) to connect to the target database.

  4. Choose Script > Run SQL or click Run SQL button.

    Data Studio displays the results in the SQL Results tab (at the bottom). Click the Result tab to see the query results or click the Status tab to see query-processing information.

    The result of a SELECT statement in IBM Data Studio.

    Screenshot: The result of an SQL SELECT statement displayed in a table in IBM Data Studio

For technical reasons involving parent and child processes, (only) Microsoft Windows users must start the db2 command-line processor with a special preliminary step.

To start the db2 command-line processor in Windows:

You must use the Db2 CLP window for all db2 commands (described next). If you try to run db2 at a normal Windows command prompt, then Db2 responds with the error “Command line environment not initialized.”

If you launch a new Db2 CLP window via the db2cmd command, then you can close the original command-prompt window.

In the Db2 CLP window, change (cd) your working directory if necessary before you run db2 commands.

To use the db2 command-line processor interactively:

  1. At a command prompt, type:

    db2 -t

    and then press Enter. The -t option tells db2 that a semicolon (;) terminates statements.

    The db2 => prompt appears.

  2. At the db2 prompt, type:

    connect to dbname;

    and then press Enter. dbname is the name of the database to use.

  3. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.15).

    Figure 1.15The result of a SELECT statement in db2 interactive mode.

    Screenshot: The result of running an SQL SELECT statement by using db2 in interactive mode at a command prompt

Alternatively, you can avoid the db2 => prompt by typing commands and SQL statements right on the command line. For example:

db2 connect to books
db2 SELECT * FROM authors

If you omit the -t option, as here, then don’t terminate commands and SQL statements with a semicolon.

To use the db2 command-line processor in script mode:

  1. At a command prompt, type:

    db2 connect to dbname

    dbname is the name of the database to use.

  2. At a command prompt, type:

    db2 -t -f sql_script

    sql_script is a text file containing SQL statement(s) and can include an absolute or relative path. The -t option tells db2 that a semicolon (;) terminates statements. Add the -v option if you want to echo the contents of sql_script in the output.

  3. Press Enter to display the results (Figure 1.16).

    Figure 1.16The result of a SELECT statement in db2 script mode.

    Screenshot: The result of running an SQL SELECT statement by using db2 in script mode at a command prompt

Tip: An alternative script tool is db2batch.

To exit the db2 command-line tool:

To show db2 command-line options:

MySQL

MySQL (pronounced my-es-kyoo-el) is an open-source DBMS that supports large databases and numbers of transactions. MySQL is known for its speed and ease of use. It’s free for personal use and runs on many operating systems and hardware platforms. You can download it at mysql.com.

To determine which version of MySQL you’re using, run the MySQL command-line command

mysql -V

or run the query

SELECT VERSION();

To run SQL programs, use the mysql command-line tool.

To use the mysql command-line tool interactively:

  1. At an administrator command prompt, type:

    mysql -h host -u user -p dbname

    host is the host name, user is your MySQL user name, and dbname is the name of the database to use. MySQL will prompt you for your password (for a passwordless user, either omit the -p option or press Enter at the password prompt).

  2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.18).

    Figure 1.18The result of a SELECT statement in mysql interactive mode.

    Screenshot: The result of running an SQL SELECT statement by using mysql in interactive mode at a command prompt

To use the mysql command-line tool in script mode:

  1. At an administrator command prompt, type:

    mysql -h host -u user -p -t dbname < sql_script

    host is the host name, user is your MySQL user name, and dbname is the name of the database to use. MySQL will prompt you for your password (for a passwordless user, either omit the -p option or press Enter at the password prompt). The -t option formats the results as a table; omit this option if you want tab-delimited output. The redirection operator < reads from the file sql_script, which is a text file containing SQL statement(s) and can include an absolute or relative path.

  2. Press Enter to display the results (Figure 1.19).

    Figure 1.19The result of a SELECT statement in mysql script mode.

    Screenshot: The result of running an SQL SELECT statement by using mysql in script mode at a command prompt

To exit the mysql command-line tool:

To show mysql command-line options:

If MySQL is running on a remote network computer, then ask your database administrator 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 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.

PostgreSQL

PostgreSQL (pronounced post-gres-kyoo-el) is an open-source DBMS that supports large databases and numbers of transactions. PostgreSQL is known for its rich feature set and its high conformance with standard SQL. It’s free and runs on many operating systems and hardware platforms. You can download it at postgresql.org.

To determine which version of PostgreSQL you’re using, run the PostgreSQL command-line command

psql -V

or run the query

SELECT VERSION();

To run SQL programs, use the psql command-line tool.

To use the psql command-line tool interactively:

  1. At an administrator command prompt, type:

    psql -h host -U user -W dbname

    host is the host name, user is your PostgreSQL user name, and dbname is the name of the database to use. PostgreSQL will prompt you for your password (for a passwordless user, either omit the -W option or press Enter at the password prompt).

  2. Type an SQL statement. The statement can span multiple lines. Terminate it with a semicolon (;) and then press Enter to display the result (Figure 1.21).

    Figure 1.21The result of a SELECT statement in psql interactive mode.

    Screenshot: The result of running an SQL SELECT statement by using psql in interactive mode at a command prompt

To use the psql command-line tool in script mode:

  1. At an administrator command prompt, type:

    psql -h host -U user -W -f sql_script dbname

    host is the host name, user is your PostgreSQL user name, and dbname is the name of the database to use. PostgreSQL will prompt you for your password (for a passwordless user, either omit the -W option or press Enter at the password prompt). The -f option specifies the name of the SQL file sql_script, which is a text file containing SQL statement(s) and can include an absolute or relative path.

  2. Press Enter to display the results (Figure 1.22).

    Figure 1.22The result of a SELECT statement in psql script mode.

    Screenshot: The result of running an SQL SELECT statement by using psql in script mode at a command prompt

To exit the psql command-line tool:

To show psql command-line options:

If PostgreSQL is running on a remote network computer, then ask your database administrator 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 postgres when you set up or installed PostgreSQL.

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 the PostgreSQL documentation.

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