CHAPTER 5

Structured Query Language (SQL)

Primary Chapter Objectives

1. To learn the SQL data definition: CREATE.

2. To learn the basic SQL data management language: INSERT, UPDATE, and DELETE.

3. To learn the basic SQL data querying language: SELECT.

Discussion Focus

SQL is considered to be a 4GL. Why is that important?

A Fourth Generation Language (4GL) is a nonprocedural language. That is, its user merely commands what is to be done without having to worry about how it's to be done. Contrast this approach to that taken by such 3GL languages as COBOL, BASIC, or Pascal. Given this characteristic, the 4GL languages are much easier to use than their 3GL predecessors.

Why is SQL often considered to be an ideal database language?

An ideal database language must be able to:

1. Create database and table structures. SQL has a data definition component that gives us the ability to meet this requirement.

2. Manage the data component of the database. SQL gives us a set of commands to enter, correct, delete, and update data within the database tables.

3. Provide detailed data query capability. "Standard" SQL uses a set of approximately thirty commands that allow us to retrieve data and to convert the raw data into useful information.

Since SQL is a 4GL, its English-like query structure is relatively easy to use. Also, ANSI does prescribe an SQL standard, thus making it easier to move between RDBMSes that share this standard.

It has been said that "if it doesn't use SQL, it's not relational." Since the relational database is the current standard, it's easy to argue that SQL plays an important role in database management and use.

What three basic data functions are provided by SQL, and what are their basic SQL commands?

1. Data definition through the use of CREATE

2. Data management through INSERT, UPDATE, and DELETE

3. Data querying through the use of SELECT ... which is the basis for all SQL queries.

ANSWERS TO THE REVIEW QUESTIONS

All Review Questions are based on the data shown in Figure R5.1. This database table is named EMP_1. The table structure is summarized in Table R5.1.

FIGURE R5.1 The Contents of the EMP_1 Table

Table R5.1 The EMP_1 Table Structure Summary

Attribute (Field) Name	Data Declaration
EMP_NUM	CHAR(3)
EMP_LNAME	VARCHAR(15)
EMP_FNAME	VARCHAR(15)
EMP_INITIAL	CHAR(1)
EMP_HIREDATE	DATE
JOB_CODE	CHAR(3)

Given this information, answer the following questions:

1. Write the SQL code that will create the table structure.

Since the EMP_NUM is a primary key, it is best to declare it NOT NULL. In fact, attributes are usually declared NOT NULL for efficiency reasons, unless there is reason to believe that the attribute is expected to have nulls occasionally. (For example, not everyone has a middle initial, nor does everyone necessarily have a job code assignment.) Also, to enforce referential and entity integrity, you should make use of the primary and foreign key declarations shown in the following sequence. (We're assuming that the JOB_CODE is the foreign key to a JOB table.)

CREATE TABLE EMP_1 (

EMP_NUM CHAR(3) NOT NULL,

EMP_LNAME CHAR(15 NOT NULL,

EMP_FNAME CHAR(15) NOT NULL,

EMP_INITIAL CHAR(1),

EMP_HIREDATE DATE NOT NULL,

JOB_CODE CHAR(3),

PRIMARY KEY (EMP_NUM),

FOREIGN KEY (JOB_CODE) REFERENCES JOB);

Naturally, if your software's SQL does not allow such enhancements, delete the NOT NULL and primary key and foreign key references.

2. Having created the table structure in question 1, write the SQL code that will enter the first two data rows into the EMP_l table.

INSERT INTO EMP_1

VALUES ('101', 'News', 'John', 'G', '11/8/92', '502');

INSERT INTO EMP_1

VALUES ('102', 'Senior', 'David', 'H', '7/12/87', '501');

Note: If the DBMS supports the DATE format, it may allow you to enter dates without the use of the apostrophes and it may allow you to use date entries such as 11Nov96. Check your DBMS' SQL manual to see what formats are allowed. Also, modern DBMSes use a GUI to make data entry considerably less cumbersome than the entry shown here. The purpose of using the SQL data entry format shown here is to acquaint students with SQL procedures.

3. Assuming that the remaining data have been entered, write the SQL code that will list all attributes for a job code of 502.

SELECT *

FROM EMP_1

WHERE JOB_CODE = '502';

4. Write the SQL code that will save the EMP_l table.

COMMIT EMP_1;

5. Write the SQL code to change the job code to 501 for the person whose personnel number is 106. After you have completed the task, examine the results, and then reset the job code to its original value.

First make the change:

UPDATE EMP_1

SET JOB_CODE = '501'

WHERE EMP_NUM = '106';

Next, examine the results with the command

SELECT *

FROM EMP_1

WHERE EMP_NUM = '106';

Finally, reset the job code to its original value:

UPDATE EMP_1

SET JOB_CODE = '500'

WHERE EMP_NUM = '106';

You may also reset the attribute values to their original state with the ROLLBACK command, assuming that the original database table values had been saved to disk with COMMIT.

6. Write the SQL code to delete the row for the person named William Smithfield, who was hired on 06/23/90 and whose job code classification is 500.(Hint: Use logical operators to include all the information given in this problem.)

If William Smithfield's personnel number (106) is known, it is safest to use this primary key. After all, the use of the primary key guarantees a unique match.

DELETE FROM EMP_1

WHERE EMP_NUM = '106';

If the personnel number is not known, first write a query to find it, or use some combination of attributes that you think will make a unique match. (You will discover that it may take quite a few attributes to make such a match... that's why primary keys are so useful!) The following example illustrates one method. The assumption is that it is not likely that more than one employee has the same last name, first name, initial, hire date, and job code. Naturally, there is no guarantee that in a large personnel database there will not be more than one match and, therefore, it would be possible to delete more than the one targeted record... again illustrating the value of having a primary key.

DELETE FROM EMP_1

WHERE EMP_LNAME = 'Smithfield' AND

EMP_FNAME = 'William' AND

EMP_INITIAL = '' AND

EMP_HIREDATE = '6/23/90' AND

JOB_CODE = '500';

Note: Because Mr. Smithfield does not have a middle initial, the EMP_INITIAL is null. Because a null is not the same thing as a blank, the condition EMP_INITIAL = '' uses two apostrophes without a blank space between them. In other words, the '' represents two apostrophes, rather than a single quotation symbol!

7. Write the SQL code that will restore the data to its original status; that is, the table should contain the data that existed before you made the changes in questions 5 and 6.

ROLLBACK;

8. Create a copy of EMP_1, name it EMP_2, then add the attributes EMP_PCT and PROJ_NUM to its structure. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are shown next:

EMP_PCT NUMBER(4,2)

PROJ_NUM CHAR(3)

(Note: If your SQL implementation allows it, you may use DECIMAL(4,2), rather than NUMBER(4,2).

First create a copy of EMP_1 and name the copy EMP_2.

Next, insert the two new attributes into the new EMP_2 table:

ALTER TABLE EMP_2

ADD (EMP_PCT DECIMAL(4,2),

PROJ_NUM CHAR(3));

9. Write the SQL code to enter an EMP_PCT value of 3.85 for a person whose personnel number (EMP_NUM) is 103. Assume that all the EMP_PCT entries have been made at this point and that the EMP_2 table now contains the data shown in Figure R5.9:

FIGURE R5.9 The Contents of the EMP_2 Table

The update for employee 103, June E. Arbough, was created by:

UPDATE EMP_2

SET EMP_PCT = 3.85

WHERE EMP_NUM = 103;

10. Using a single command sequence, write the SQL code that will enter the project number (PROJ_NUM) = 18 for all employees whose job classification (JOB_CODE) is 500.

UPDATE EMP_1

SET PROJ_NUM = '18'

WHERE JOB_CODE = '500';

11. Using a single command sequence, write the SQL code that will enter the project number (PROJ_NUM) = 25 for all employees whose job classification (JOB_CODE) is 502 or higher. When you are done with questions 10 and 11, the EMP_2 table will contain the data shown in Figure R5.11:

FIGURE R5.11 The EMP_2 Table Contents After the Modifications

(You may assume that the table has been saved again at this point!)

UPDATE EMP_2

SET PROJ_NUM = '25'

WHERE JOB_CODE >= '502';

12. Write the SQL code that will enter a PROJ_NUM of 14 for those employees who were hired before January 1, 1992 and whose job code is at least 501. (You may assume that the table will be restored to the condition shown following question 11.)

UPDATE EMP_2

SET PROJ_NUM = '14'

WHERE EMP_HIREDATE < '1/1/92' AND JOB_CODE >= '501';

Depending on your date format selection, the SQL command sequence might have used the date entry ‘01-JAN-1992’ or some other format.

13. Write the two SQL command sequences required to:

a. Create a temporary table named TEMP_1, whose structure is composed of the EMP_2 attributes EMP_NUM and EMP_PCT.

CREATE TABLE TEMP_1

(EMP_NUM CHAR(2),

EMP_PCT DECIMAL(5,2));

b. Copy the matching EMP_2 values into the TEMP_1 table.

INSERT INTO TEMP_1 (EMP_NUM, BONUS_PCT)

(SELECT EMP_NUM, EMP_PCT

FROM EMP_2));

14. Write the SQL command that will delete the newly created TEMP_1 table from the database.

DROP TABLE TEMP_1;

15. Write the SQL code required to list all employees whose last names start with ‘Smith’. In other words, the rows for both Smith and Smithfield should be included in the listing.

SELECT FROM EMP_1

WHERE EMP_LNAME LIKE 'Smith%';

16. Suppose that your database also contains the PROJECT table shown in Database Table 5.16A. The EMP_NUM in the PROJECT table references the employee in the EMPLOYEE_2 table who manages the PROJECT.

FIGURE R5.16A The PROJECT Table Contents

Given this information, write the SQL code that will produce the results shown in Database Table R5.16B.

FIGURE R5.16B The Query results for Question 16

(Hint: You must join the EMP_1 and PROJECT tables.)

SELECT PROJ_NAME, EMP_LNAME, EMP_FNAME, EMP_INITIAL, JOB_CODE

FROM EMP_2, PROJECT

WHERE EMP_2.PROJ_NUM = PROJECT.PROJ_NUM;

17. Write the SQL code that will produce a virtual table named REP_1, containing the same information that was shown in question 16.

CREATE VIEW REP_1 AS

(SELECT PROJ_NAME, EMP_LNAME, EMP_FNAME, EMP_INITIAL,

JOB_CODE, PROJ_MGR

FROM EMP_2, PROJECT

WHERE EMP_2.PROJ_NUM = PROJECT.PROJ_NUM;

18. Write the SQL code to find the average bonus percentage in the EMP_2 table.

SELECT AVG(BONUS_PCT)

FROM EMP_2;

19. Write the SQL code that will produce a listing for the data in the EMP_2 table in ascending order by the bonus percentage.

SELECT *

FROM EMP_2

ORDER BY BONUS_PCT;

20. Write the SQL code that will list only the different project numbers found in the EMP_2 table.

SELECT DISTINCT PROJ_NUM

FROM EMP_2;

Answers to Problems

1. Using the CH2-AVIA database first analyzed in Chapter 2, “The Relational Database Model”, use the data dictionary as the basis for the implementation of the database. (The database must contain the tables AIRCRAFT, CHARTER, CUSTOMER, EMPLOYEE, MODEL, and PILOT.) Rename this database CH5_AVIA. Using your DBMS’s import function, import the data from the CH2_AVIA database. (The CH5_AVIA database will be the basis for the first fifteen problems.)

Refer to the answers to questions 1 and 2, then note the appropriate procedures:

1. create the table structure (question 1)

2. enter the data (question 2)

Teacher's Note: The data are available in the CH5_VIA database:

Directory Database Table(s)

CH5 CH5_AVIA AIRCRAFT

CHARTER

CUSTOMER

EMPLOYEE

MODEL

PILOT

You may export the Access data in any number of formats: ASCII, SQL, etc., in order to meet your database software requirements. It may be useful to show how modern DBMS software includes many data entry screen generators that make data entry much simpler for end users. Yet explain that knowledge of SQL procedures is useful to the database managers who must understand how the query "engine" works in order to optimize the system's efficiency.

2. Use the contents of the CHARTER table, write the SQL query that will produce the output shown in Figure P5.2. Note that the output is limited to selected attributes for aircraft number 2778V.

FIGURE P5.2 Selected Attributes from the CHARTER Table

For Aircraft 2778V

SELECT CHAR_DATE, AC_NUMBER, CHAR_DESTINATION, CHAR_DISTANCE,

CHAR_HOURS_FLOWN

FROM CHARTER

WHERE AC_NUMBER = '2778V';

3. Create a virtual table (named AC2778V) containing the output presented in problem 2.

CREATE VIEW AC2778V AS

(SELECT CHAR_DATE, AC_NUMBER, CHAR_DESTINATION, CHAR_DISTANCE,

CHAR_HOURS_FLOWN

FROM CHARTER

WHERE AC_NUMBER = '2778V');

4. Produce the output shown in Figure P5.4 for aircraft 2778V. Note that this output includes data from the CHARTER and CUSTOMER tables. (Hint: Use a join in this query.)

FIGURE P5.4 Selected Output from the CHARTER

and CUSTOMER Tables

33. We have demonstrated ORACLE's use of the ASCII files in the Basic ORACLE SQL video.

Teacher's note: To see how the tables are to be joined, the Entity relationship diagram and the relational schema are very handy. Use problem 2.31's E-R diagram and problem 2.32's Relational Schema (see Chapter 2's solutions) as the basis for discussing the appropriate joins in the remaining problems based on the CH5_AVIA database.

SELECT CHAR_DATE,AC_NUMBER,CHAR_DESTINATION,CUS_LNAME,

CUS_AREACODE,CUS_PHONE

FROM CHARTER,CUSTOMER

WHERE CHARTER.AC_NUMBER = '2778V' AND

CHARTER.CUS_CODE = CUSTOMER.CUS_CODE;

Some DBMSes generate SQL code from QBE screens that are filled out by the end-user. For example, Microsoft Access generates the following SQL code to produce the output with the help of an inner join:

SELECT CHARTER.CHAR_DATE, CHARTER.AC_NUMBER,

CHARTER.CHAR_DESTINATION, CUSTOMER.CUS_LNAME,

CUSTOMER.CUS_AREACODE, CUSTOMER.CUS_PHONE

FROM CUSTOMER INNER JOIN CHARTER ON

CUSTOMER.CUS_CODE = CHARTER.CUS_CODE

WHERE ((CHARTER.AC_NUMBER="2778V"));

However, this SQL code does not conform to the commonly accepted SQL standard. Nevertheless, it may be useful to discuss various SQL dialects.

5. Produce the output shown in Figure P5.5 for aircraft 2778V. Note that this output includes data from the CHARTER, EMPLOYEE, and MODEL tables.

Hint: Two of the joins pass through other tables. For example, the “connection” between CHARTER and MODEL requires the existence of AIRCRAFT, because the CHARTER table does not contain a foreign key to MODEL. However, CHARTER does contain a foreign key to AIRCRAFT, which contains a foreign key to MODEL.

Figure P5.5 Selected Output from the CHARTER, EMPLOYEE,

and MODEL Tables

SELECT CHAR_DATE,CHARTER.AC_NUMBER,MOD_NAME,EMP_LNAME

FROM CHARTER,AIRCRAFT,MODEL,PILOT,EMPLOYEE

WHERE CHARTER.AC_NUMBER = AIRCRAFT.AC_NUMBER AND

AIRCRAFT.MOD_CODE = MODEL.MOD_CODE AND

PILOT.EMP_NUM = EMPLOYEE.EMP_NUM AND

CHARTER.CHAR_PILOT = PILOT.EMP_NUM AND

CHARTER.AC_NUMBER = '2778V';

6. Modify the query in problem 5 to include data from the CUSTOMER table. This time, the output is limited by the date selection, as shown in Figure P5.6. Use the clause WHERE CHAR_DATE >= ‘5/15/1999’; to define the query’s date restriction.

FIGURE P5.6 Selected Output from the CHARTER, EMPLOYEE, MODEL,

and CUSTOMER Tables

SELECT CHAR_DATE, CHARTER.AC_NUMBER, MOD_NAME, EMP_LNAME, CUS_LNAME

FROM CHARTER, AIRCRAFT,MODEL, PILOT, EMPLOYEE, CUSTOMER

WHERE CHARTER.AC_NUMBER = AIRCRAFT.AC_NUMBER AND

AIRCRAFT.MOD_CODE = MODEL.MOD_CODE AND

PILOT.EMP_NUM = EMPLOYEE.EMP_NUM AND

CHARTER.CHAR_PILOT = PILOT.EMP_NUM AND

CHARTER.CUS_CODE = CUSTOMER.CUS_CODE AND

CHAR_DATE >= '15‑MAY‑1999';

(Keep in mind again that you may use a different date format such as ‘15-MAY-99’ or ‘05/15/99’.)

7. Modify the query in problem 6 to produce the output shown in Figure P5.7. The date limitation in Problem 6 applies to this problem, too.

FIGURE P5.7 Selected Output from the CHARTER, MODEL,

and CUSTOMER Tables

SELECTCHAR_DATE, CHAR_DESTINATION, CHARTER.AC_NUMBER,

MOD_CHG_MILE, CHAR_DISTANCE,CUS_LNAME

FROM CHARTER, AIRCRAFT, MODEL, PILOT, EMPLOYEE, CUSTOMER

WHERE CHARTER.AC_NUMBER = AIRCRAFT.AC_NUMBER AND

AIRCRAFT.MOD_CODE = MODEL.MOD_CODE AND

PILOT.EMP_NUM = EMPLOYEE.EMP_NUM AND

CHARTER.CHAR_PILOT = PILOT.EMP_NUM AND

CHARTER.CUS_CODE = CUSTOMER.CUS_CODE AND

CHAR_DATE >= '15‑MAY‑99'

8. Modify the query in problem 7 to produce the computed (derived) attributes “fuel per hour” and “total mileage charge.” (Hint: It is possible to use SQL to produce computed “attributes” that are not stored in any table. For example, the following SQL query is perfectly acceptable:

SELECT CHAR_DISTANCE, CHAR_GALLONS,

CHAR_DISTANCE/CHAR_GALLONS

FROM CHARTER

WHERE CHAR_DATE >= ‘1/20/02’;

Use a similar technique on joined tables to produce the output shown in Figure P5.8.)

FIGURE P5.8 Using a SQL Query to Produce Computed

(Derived) Attribute Values

NOTE

The output format is determined by the RDBMS you use. In this example, the Access software defaulted to an output heading label “Expr1” to indicate the expression resulting from the division

[CHARTER]![CHAR_FUEL_GALLONS]/[CHARTER]![CHAR_HOURS]

created by its expression builder. ORACLE defaults to the full division label. You should learn to control the output format with the help of your RDBMS’s utility software. For instance, Access allows you to use its properties box to redefine the output format to produce the output shown in Database Table P5.8A.

FIGURE P5.8A Using the System Software to Format the O