In this chapter
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 2021, Microsoft SQL Server 2019, Oracle Database 19c, IBM Db2 Database 12, MySQL 8, and PostgreSQL 14. 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.
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 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:
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.
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:
Choose Create tab > Queries group > Query Design (Figure 1.2).
Figure 1.2Query Design lets you skip the hand-holding wizards.
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.
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.
Type or paste an SQL statement (Figure 1.5).
Figure 1.5Enter an SQL statement...
Choose Design tab > Results group > Run (Figure 1.6).
Figure 1.6...and run it.
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.
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 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:
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.
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.
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.
To use the sqlcmd command-line tool interactively:
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.
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.
To use the sqlcmd command-line tool in script mode:
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.
Press Enter to display the results (Figure 1.11).
Figure 1.11The result of a SELECT statement in sqlcmd script mode.
To exit the sqlcmd command-line tool:
exit or quit and then press Enter.To show sqlcmd command-line options:
sqlcmd -? and then press Enter.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 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:
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.
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.
To use the sqlplus command-line tool in script mode:
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.
Press Enter to display the results (Figure 1.13).
Figure 1.13The result of a SELECT statement in sqlplus script mode.
To exit the sqlplus command-line tool:
exit or quit and then press Enter.To show sqlplus command-line options:
At a command prompt, type sqlplus -H and then press Enter.
This command displays pages that speed by. To view one page at a time, type
sqlplus -H | more
and then press Enter. Tap the spacebar to advance pages (Figure 1.14).
Figure 1.14The sqlplus help screen.
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 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:
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, 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.
To run SQL interactively, click New SQL Script 
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.
Choose Script > Run SQL or click 
.
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.
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:
At an administrator command prompt, type db2cmd and then press Enter. Alternatively, choose Start > IBM DB2 DB2COPY1 (default) > Db2 Command Line Processor.
A new DB2 CLP command-prompt window appears.
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:
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.
At the db2 prompt, type:
connect to dbname;
and then press Enter. dbname is the name of the database to use.
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.
Alternatively, you can avoid the db2 => prompt by typing commands and SQL statements right on the command line. For example:
db2 connect to booksdb2 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:
At a command prompt, type:
db2 connect to dbname
dbname is the name of the database to use.
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.
Press Enter to display the results (Figure 1.16).
Figure 1.16The result of a SELECT statement in db2 script mode.
Tip: An alternative script tool is db2batch.
To exit the db2 command-line tool:
quit; and then press Enter. (Omit the semicolon if you didn’t use the -t option when you started db2.)To show db2 command-line options:
At a command prompt, type db2 ? and then press Enter.
This command displays pages that speed by. To view one page at a time, type
db2 ? | more
and then press Enter. Tap the spacebar to advance pages (Figure 1.17).
To get help while you’re at a db2 => prompt, type ?; and then press Enter. (Omit the semicolon if you didn’t use the -t option when you started db2.)
Figure 1.17The db2 help screen.
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:
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).
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.
To use the mysql command-line tool in script mode:
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.
Press Enter to display the results (Figure 1.19).
Figure 1.19The result of a SELECT statement in mysql script mode.
To exit the mysql command-line tool:
quit or \q and then press Enter.To show mysql command-line options:
At a command prompt, type mysql -? and then press Enter.
This command displays pages that speed by. To view one page at a time, type
mysql -? | more
and then press Enter. Tap the spacebar to advance pages (Figure 1.20).
Figure 1.20The mysql help screen.
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 (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:
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).
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.
To use the psql command-line tool in script mode:
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.
Press Enter to display the results (Figure 1.22).
Figure 1.22The result of a SELECT statement in psql script mode.
To exit the psql command-line tool:
\q and then press Enter.To show psql command-line options:
At a command prompt, type psql -? and then press Enter.
This command displays pages that speed by. To view one page at a time, type
psql -? | more
and then press Enter. Tap the spacebar to advance pages (Figure 1.23).
Figure 1.23The psql help screen.
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.