Chapter Six Introduction to Structured Query Language (SQL)

Chapter Six Introduction to Structured Query Language (SQL)

ANSWERS TO GROUP I QUESTIONS

6.1  What does SQL stand for?

Structured Query Language

6.2  What is a data sublanguage?

A data sublanguage consists only of language statements for defining and processing a database. To obtain a full programming language, SQL statements must be embedded in scripting languages such as VBScript or in programming languages such as Java or C#.

6.3  Explain the importance of SQL-92.

This is the version of SQL supported by most commonly used database management systems. The SQL-92 version includes some object-oriented concepts but is not commonly implemented.

6.4  Why is it important to learn SQL?

Most, but not all, DBMS provide graphical tools to provide access to the database. You cannot do everything with graphical tools that you can do with SQL; furthermore, you must use SQL to generate SQL statements dynamically in program code.

6.5  In your own words, describe the meanings of the phrases ON UPDATE CASCADE and ON DELETE CASCADE.

Both are used to support referential integrity actions. On Update Cascade says that if the primary key of a parent is changed, also change the foreign key in all of that parent’s children. On Delete Cascade says if a parent is deleted, also delete all of that parent’s children.

Use the following tables for your answers to questions 6.6-6.9:

EMPLOYEE (EmployeeID, Name, Phone, Email)

COMPUTER (SerialNumber, Make, Model, EmployeeID)

(See Figure 6-8 for sample data.)

6.6  Code a CREATE TABLE command to create the EMPLOYEE table. Justify your choices of column data types and constraints.

Create Table EMPLOYEE

(EmployeeID Integer,

Name Char(20) Not Null,

Phone Char(12) Unique Not Null,

Email Char(20) Unique Not Null,

CONSTRAINT EmployeePK PRIMARY KEY (EmployeeID));

EmployeeID is defined as an Integer because all Employee IDs are numbers and have a value of 400 or less. EmployeeID is also defined as the primary key as shown in the table description. All other attributes (including the Phone) contain characters so they are defined as character attributes. All attributes are Not Null because, based upon the data shown, each employee has a value for the attribute. Phone and Email are defined as unique because they should be alternate keys for an employee.

6.7  Code a CREATE TABLE command to create the COMPUTER table. Justify your choices of column constraints. Should EmployeeID be null or not null?

Create Table COMPUTER

(SerialNumber Integer,

Make Char(20) Not Null,

Model Integer Not Null,

EmployeeID Integer Null,

CONSTRAINT ComputerPK PRIMARY KEY (SerialNumber),

CONSTRAINT EmployeeFK FOREIGN KEY (EmployeeID) References EMPLOYEE(EmployeeID));

SerialNumber is defined as an integer because all serial numbers are numbers and have a value of 8000 or less. SerialNumber is also defined as the primary key as shown in the table description. Make contains characters so it is defined as a character attribute. Model contains numbers so it is an integer. EmployeeId is defined as a foreign key to Employee. It is an integer to make it domain compatible with the Employee table’s primary key. It is allowed to be Null because some Computers may not be assigned to employees.

6.8  Create a referential integrity constraint on COMPUTER.EmployeeID. Assume that deletions should not cascade, but that updates should cascade.

Change the EmployeeFK constraint to:

CONSTRAINT EmployeeFK FOREIGN KEY (EmployeeID) References EMPLOYEE(EmployeeID)

ON UPDATE CASCADE);

6.9  Code the required SQL statements for the following alternative version of the COMPUTER table:

COMPUTER1 (SerialNumber, Make, Model, EmployeeID)

Create Table COMPUTER1

(SerialNumber Integer,

Make Char(20) Not Null,

Model Integer Not Null,

EmployeeID Integer,

CONSTRAINT ComputerPK PRIMARY KEY (SerialNumber, EmployeeID)

CONSTRAINT EmployeeFK FOREIGN KEY (EmployeeID) References EMPLOYEE(EmployeeID));

6.10  Is COMPUTER or COMPUTER1 a better design? Explain your rationale.

Depends upon the requirements. If there are computers that are not assigned to an Employee, the COMPUTER definition is better. If all computers must be assigned to an employee before their information is stored in the database, COMPUTER1 is acceptable, but not positive that it is better.

6.11  Code SQL to remove the EMPLOYEE table from the database. Assume that the referential integrity constraint is to be removed.

ALTER TABLE ASSIGNMENT DROP CONSTRAINT ComputerFK;

DROP TABLE EMPLOYEE.

6.12  Code SQL to remove the EMPLOYEE table from the database. Assume that the COMPUTER table is to be removed, also.

DROP TABLE COMPUTER.

DROP TABLE EMPLOYEE.

For questions 6.13–6.42, use the EMPLOYEE and COMPUTER tables. Assume that EmployeeID and Computer ID are surrogate keys.

6.13  Write a SQL statement to display all columns of all rows of COMPUTER. Do not use the * notation.

SELECT SerialNumber, Make, Model, EmployeeID

FROM COMPUTER;

6.14  Write a SQL statement to display all columns of all rows of COMPUTER. Use the * notation.

SELECT *

FROM COMPUTER;

6.15  Write SQL to display the Make and Model of all COMPUTERs.

SELECT Make, Model

FROM COMPUTER;

6.16  Write SQL to display the SerialNumber, Make, and Model of all COMPUTERs having the Make ‘Dell’.

SELECT SerialNumber, Make, Model

FROM COMPUTER

WHERE Make = ‘Dell’;

6.17  Write SQL to display the Make column of COMPUTER.

SELECT Make

FROM COMPUTER;

6.18  Write SQL to display the Make column of COMPUTER (do not show duplicates).

SELECT Distinct Make

FROM COMPUTER;

6.19  Write SQL to display the Make and Model of all computers having the type ‘Dell’ and the Model is ’Laptop.’

SELECT Make, Model

FROM COMPUTER

WHERE Make = ‘Dell’ AND

Model = 4100;

6.20  Write SQL to display the ComputerID, Make, and Model of all computers that are not of Type Dell or IBM.

SELECT SerialNumber, Make, Model

FROM COMPUTER

WHERE Make = ‘Dell’;

6.21  Write SQL to display the ComputerID, Make, and Model for all computers having a four-character Type starting with D.

SELECT SerialNumber, Make, Model

FROM COMPUTER

WHERE Make LIKE ‘D_ _ _’;

6.22  Write SQL to display the Name and Email address of all employees who have an email address ending with ‘somewhere.com’. Assume that email account names can be any number of characters.

SELECT Name, Email

FROM EMPLOYEE

WHERE Email LIKE ‘%@somewhere.com’;

6.23  Write SQL to display the ComputerID of any computer that has a null value for Model.

SELECT SerialNumber

FROM COMPUTER

WHERE Model IS NULL’;

6.24  Write SQL to display the Make and Model of all computers sorted by Name.

SELECT Make, Model

FROM COMPUTER

ORDER BY Make;

6.25  Write SQL to display the Make and Model of all computers sorted by Make in ascending order and by Model in descending order within Make.

SELECT Make, Model

FROM COMPUTER

ORDER BY Make ASC Model DESC;

6.26  Write SQL to count the number of computers.

SELECT COUNT(*)

FROM COMPUTER;

6.27  Write SQL to count the number of distinct Makes.

SELECT COUNT(DISTINCT MAKE)

FROM COMPUTER;

6.28  For the following table, write SQL to display the minimum, maximum, and average Weight of computers.

COMPUTER2 (SerialNumber, Make, Model, Weight, OwnerID)

SELECT MIN(Weight), MAX(Weight), AVG(Weight)

FROM COMPUTER;

6.29  For the COMPUTER2 table, write SQL to group the data by Make and display the average Weight per make.

SELECT Make, AVG(Weight)

FROM COMPUTER

GROUP BY Make;

6.30  Answer question 6.29, but consider only Makes for which there are five or more computers in the database.

SELECT Make, AVG(Weight)

FROM COMPUTER

GROUP BY Make

HAVING Count(Make) > 5;

6.31  Answer question 6.30, but do not consider any computer having the Make ‘Dell’.

SELECT Make, AVG(Weight)

FROM COMPUTER

WHERE NOT Make = ‘Dell’

GROUP BY Make

HAVING Count(Make) > 5;

6.32  Using the tables in Figure 6-8, write SQL to display Name and Email of any employees with Dell computers. Use a subquery.

SELECT Name, Email

FROM EMPLOYEE

WHERE EmployeeID IN

(SELECT EmployeeID

FROM COMPUTER

WHERE Make = ‘Dell’);

6.33  Using the tables in Figure 6-8, write SQL to display Name and Email of any employees with IBM computers with the Model ‘Laptop’. Use a join.

SELECT Name, Email

FROM EMPLOYEE, COMPUTER

WHERE EMPLOYEE.EmployeeID = COMPUTER.EmployeeID

AND Make = ‘IBM’

AND Model = ‘Laptop’;

6.34  Suppose that the following new table is added to the database:

MANUFACTURER (Name, City, State)

Assume that Make of COMPUTER is a foreign key into Name of MANUFACTURER. Code SQL to display the Name and Email of any employee with a computer that was made by a manufacturer in Atlanta. Use subquery format.

SELECT Name, Email

FROM EMPLOYEE

WHERE EmployeeID IN

(SELECT EmployeeID

FROM COMPUTER

WHERE Make IN

(SELECT Name

FROM MANUFACTURER

WHERE City = ‘Atlanta’)));

6.35  Answer question 6.34, but use joins in the standard SELECT FROM WHERE format.

SELECT Name, Email

FROM EMPLOYEE, COMPUTER, MANUFACTURER

WHERE EMPLOYEE.EmployeeID = COMPUTER.EmployeeID

AND COMPUTER.Make = MANUFACTURER.Name;

6.36  Answer question 6.34, but use joins in the JOIN ON format.

SELECT Name, Email

FROM ((EMPLOYEE JOIN COMPUTER

ON EMPLOYEE.EmployeeID = COMPUTER.EmployeeID)

JOIN MANUFACTURER

ON COMPUTER.Make = MANUFACTURER.Name);

6.37  Answer question 6.34, but include employees, regardless of whether they have a computer.

SELECT Name, Email

FROM ((EMPLOYEE LEFT JOIN COMPUTER

ON EMPLOYEE.EmployeeID = COMPUTER.EmployeeID)

LEFT JOIN MANUFACTURER

ON COMPUTER.Make = MANUFACTURER.Name);

6.38  Code SQL to add three new rows to the COMPUTER table. Assume that ComputerID is a surrogate key and the DBMS will provide a value for it. Otherwise, assume you have all of the data.

INSERT INTO COMPUTER VALUES (‘IBM’, 2100, 100);

INSERT INTO COMPUTER VALUES (‘IBM’, 330, 100);

INSERT INTO COMPUTER VALUES (‘Gateway’, 6690, 300);

6.39  Code SQL to add three new rows to the EMPLOYEE table. Assume that EmployeeID is a surrogate key and the DBMS will provide a value for it. Otherwise, you have only values for Name and Phone; and assume that Email is Not Null.

INSERT INTO EMPLOYEE (Name, Phone, Email) VALUES

(‘Jack Jones’, ‘540-433-0099’, NULL);

INSERT INTO EMPLOYEE (Name, Phone, Email) VALUES

(‘Jill Jones’, ‘540-433-2233’, NULL);

INSERT INTO EMPLOYEE (Name, Phone, Email) VALUES

(‘Jacoline Jenkins, ‘777-888-9999’, NULL);

6.40  Code SQL to change the values of ‘Compaq’ in Make of COMPUTER to ‘HP’.

UPDATE COMPUTER

SET Make = ‘HP’

WHERE Make = ‘Compaq’;

6.41  Explain what will happen if you leave the WHERE clause off your answer to question 6.40.

The Make of ALL computers would be changed to ‘HP’.

6.42  Code SQL to delete all rows of COMPUTERs of Model ‘Desktop’. What will happen if you forget to code the WHERE clause in this statement?

DELETE FROM COMPUTER

WHERE Model = ‘Desktop’;

If you leave off the where clause, all rows will be deleted. The structure of the table will remain, but the table will be empty.

u ANSWERS TO GROUP II QUESTIONS

6.43 Write SQL statements to create the tables for a relational design of the data model shown in Figure 5-17(c). Use the format of statements shown in Figure 6-7. Define foreign keys and set cascading behavior in accordance with the referential integrity actions shown there. Which of the referential integrity actions cannot be implemented using foreign key constraints?

CREATE TABLE SALESPERSON

(EmployeeID Integer Not Null,

Name Char(20) Not Null,,

Phone Char(12) Not Null,,

Email Char(20) Not Null,,

CONSTRAINT EmployeePK PRIMARY KEY (EmployeeID));

CREATE TABLE SALESORDER

(SalesOrderNumber Integer Not Null,

OrderDate DATE Not Null,

OrderDescription VarChar(50) Not Null,,

OrderTotal Number(8,2) Not Null,,

TotalCommission Number(6,2) Not Null,,

CONSTRAINT EmployeePK PRIMARY KEY (EmployeeID));

CREATE TABLE SALESPERSON_SHARE

(EmployeeID Integer Not Null,

SalesOrderNumber Integer Not Null,

CommissionPercent Number(3,2) Not Null,,

CONSTRAINT SharePK PRIMARY KEY (EmployeeID, SalesOrderNumber));

ALTER TABLE SALESPERSON_SHARE

ADD CONSTRAINT EmployeeFK

FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE (EmployeeID)

ON UPDATE CASCADE

ON DELETE NO ACTION;

ALTER TABLE SALESPERSON_SHARE

ADD CONSTRAINT BookFK

FOREIGN KEY (SalesOrderNumber) REFERENCES SALESORDER (SalesOrderNumber)

ON UPDATE CASCADE

ON DELETE CASCADE;

6.44 Use SQL INSERT statements to add data to the tables you created in Question 6.43. Ensure that you have at least three salespeople, three sales orders, and seven records of salesperson share.

Insert Into SALESPERSON Values (10, ‘Jack Jones’, ‘540.111.2222’, ‘’);

Insert Into SALESPERSON Values (20, ‘Jill Jones’, ‘540.333.4444’, ‘’);

Insert Into SALESPERSON Values (30, ‘Bill Baker’, ‘540.555.6666’, ‘’);

Insert Into SALESORDER Values

(101, ’5-10-2003’, ‘Discount Order for PeakView’, 2500.00, 250.00);

Insert Into SALESORDER Values

(102, ’5-22-2003’, ‘Replace Playground Equipment’, 55000.00, 5500.00);

Insert Into SALESORDER Values

(103, ’6-1-2003’, ‘Refurnish Hotel Lobby’, 100000, 10000.00);

Insert Into SALESPERSON_SHARE Values (10, 101, 1.00);

Insert Into SALESPERSON_SHARE Values (10, 102, .25);

Insert Into SALESPERSON_SHARE Values (20, 102, .25);

Insert Into SALESPERSON_SHARE Values (30, 102, .50);

Insert Into SALESPERSON_SHARE Values (20, 103, .60);

Insert Into SALESPERSON_SHARE Values (30, 103, .40);

6.45 Using the tables from your answers to questions 6.43 and 6.44, write SQL statements for the following questions. Where joins are required, use the JOIN ON format of join; use aliases for table names in join statements.

A. Show the Names and Phone numbers of SALESPEOPLE who have a null Email address. Explain why the query should find no such rows.

SELECT Name, Phone

FROM SALESPERSON

WHERE Email Is Null;

No rows should be found because all Salespeople have an Email Address.

B. Show the Names of SALESPEOPLE and the total and average of OrderTotal of all orders in which they have participated.

SELECT SP.Name, Sum(SO.OrderTotal), Avg(SO.OrderTotal)

FROM SALESPERSON AS SP INNER JOIN (SalesOrder AS SO INNER JOIN SalesPerson_Share AS SS ON SO.SalesOrderNumber = SS.SalesOrderNumber) ON SP.EmployeeID = SS.EmployeeID

GROUP BY SP.Name;

C. For each order, show the SalesOrderNumber and sum of CommissionPercent.

SELECT SalesOrderNumber, SUM(CommissionPercent)

FROM SALESPERSON_SHARE

GROUP BY SalesOrderShare;

D. Show the SalesOrderNumber and Name of SALESPERSON for orders in which more than two salespeople have participated.

SELECT SP.Name,SS.SalesOrderNumber

FROM SALESPERSON AS SP INNER JOIN (SALESORDER AS SO

INNER JOIN salesperson_share AS SS ON SO.SalesOrderNumber = SS.SalesOrderNumber) ON SP.EmployeeID = SS.EmployeeID

WHERE SS.SalesOrderNumber IN

(SELECT SALESPERSON_SHARE.SalesOrderNumber

FROM SALESPERSON_SHARE

GROUP BY SALESPERSON_SHARE.SalesOrderNumber