THE RELATIONAL MODEL 2: SQL - Concepts of Database Management - page 70

You will begin studying SQL by examining how to use it to create a table. You will examine simple retrieval methods and

compound conditions. You will use computed fields in SQL and learn how to sort data. Then you will learn how to use built-in

70

functions, subqueries, and grouping. You will learn how to join tables and use the UNION operator. Finally, you will use SQL

to update data in a database. The end of this chapter includes generic versions of all the SQL commands presented in the

chapter.

GETTING STARTED WITH SQL

In this chapter, you will be reading the material and examining the figures to understand how to use SQL to
manipulate a relational database. You might also be using a DBMS to practice database manipulation at the
same time. If you are completing the work in this chapter using Microsoft Office Access 2003, Microsoft Office
Access 2007, or MySQL version 4.1 or higher, you should read the following information about your DBMS to
learn more about how to start SQL and to learn specific details about differences you might encounter as
you complete your work.

Getting Started with Microsoft Office Access 2003 and 2007
If you are using the Access 2003 or 2007 version of the Premiere Products database provided with the Data
Files for this text, the tables in the database have already been created. You will not need to execute the
CREATE TABLE commands to create the tables or the INSERT commands to add records to the tables.
To execute the SQL commands shown in the figures in Access 2003, open the Premiere Products data-
base, click Queries on the Objects bar in the Database window, and then double-click the “Create query in
Design view” option. Click the Close button in the Show Table dialog box, click the View button list arrow on
the Query Design toolbar, and then click SQL View. The query opens in SQL view, ready for you to type your
SQL commands. To run the SQL command, click the View button on the Query Design toolbar. To retun to
SQL view for the query, click the View button list arrow, and then click SQL View.
To execute SQL commands shown in the figures in Access 2007, open the Premiere Products database, click
the Create tab on the Ribbon, click the Query Design button in the Other group, click the Close button in the Show
Table dialog box, click the View button arrow in the Results group on the Query Design Tools tab, and then click SQL
View. The Query1 tab displays the query in SQL view, ready for you to type your SQL commands. To run the SQL
command, click the Run button in the Results group on the Query Tools Design tab. To return to SQL view, click
the View button arrow in the Views group on the Home tab, and then click SQL View.
Unlike other SQL implementations, Access doesn't have a DECIMAL data type. To create numbers with
decimals, you must use either the CURRENCY or NUMBER data type. Use the CURRENCY data type for fields
that will contain currency values; use the NUMBER data type for all other numeric fields.
In Access, you can correct typing errors in a command just as you would correct errors in a document
by using the keyboard arrow keys to move the insertion point and using the Backspace or Delete keys to delete
text. After making your corrections, you can run the query again.
Some of the examples in this text change the data in the database. If you plan to work through the examples
using Access, you should use a copy of the original Premiere Products database because the version of the data-
base that is used in subsequent chapters does not reflect these changes.

Getting Started with MySQL
If you use the MySQL-Premiere script provided with the Data Files for this text to create and activate the Premiere
(Products) database, the script will activate the database, create the tables, and insert the records for you. You will
not need to execute the CREATE TABLE commands to create the tables or the INSERT commands to add
records to the tables. ( Note: This script file assumes you have not previously created the database or any of the tables
in the database. If you have created any of the tables, you should run the MySQL-DropPremiere script provided
with the Data Files for this topic prior to running the MySQL-Premiere script.)