Database Storage & Design Chapter 3 Three Structured Query Language Note That There Are Results That The

Chapter Three – Structured Query Language

Note that there are no results; that is the correct response.

3.23 Write an SQL statement to display the Pet ID, breed, and type for all pets having a

four-character name starting with K. Note that the RTRIM function will be needed in

the solution that uses a CHAR column, but not for one that uses a VARCHAR column.

For Microsoft Access:

For SQL Server, Oracle Database, and MySQL:

(Note: There are three underlines after the letter K in the WHERE clause.)

3.24 Write an SQL statement to display the last name, first name, and email of all owners

who have an email address ending with somewhere.com. Assume that email account

names can be any number of characters.

For Microsoft Access:

For SQL Server, Oracle Database and MySQL:

3.25 Write an SQL statement to display the last name, first name and email of any owner

who has a NULL value for OwnerPhone.

Chapter Three – Structured Query Language

SELECT OwnerLastName, OwnerFirstName, OwnerEmail

3.26 Write an SQL statement to display the name and breed of all pets, sorted by

PetName.

SELECT PetName, PetBreed

FROM PET

ORDER BY PetName;

3.27 Write an SQL statement to display the name and breed of all pets, sorted by

PetBreed in ascending order and by PetName in descending order within PetBreed.

SELECT PetName, PetBreed

3.28 Write an SQL statement to count the number of pets.

SELECT COUNT (*) AS NumberOfPets

FROM PET;

Chapter Three – Structured Query Language

3.29 Write an SQL statement to count the number of distinct breeds.

For SQL Server, Oracle Database and MySQL:

For Microsoft Access:

The SQL solution requires the use of DISTINCT as part of the Count expression, but Microsoft

Access SQL does not support this. (See “Does Not Work with Microsoft Access ANSI-89 SQL” on

p. 178.) However, there is a work around; we can use a subquery to determine the distinct

PetBreeds, and then count that result:

The following table schema for the PET_3 table is another alternate version of the PET

table:

Chapter Three – Structured Query Language

3.30 Write the required SQL statements to create the PET_3 table. Assume that PetWeight

is Numeric(4,1).

We will create the required foreign key, with the ON DELETE CASCADE option (see the

solutions for exercise 3.9 if you wish to create it without the ON DELETE CASCADE option).

For Microsoft Access (use the GUI after this to achieve ON DELETE CASCADE and the

autonumber):

CREATE TABLE PET_3(

PetID Int NOT NULL,

PetName Char (50) NOT NULL,

For SQL Server:

CREATE TABLE PET_3(

PetID Int NOT NULL IDENTITY(1,1),

PetName Char (50) NOT NULL,

);

For Oracle Database:

The SQL CREATE TABLE commands shown for SQL Server 2016 will also work for Oracle

Database with only one modification. Oracle Database uses SEQUENCES to set surrogate

keys and set starting values and increment values. Therefore, the definitions of the table

and sequence surrogate values should be written as:

Chapter Three – Structured Query Language

CREATE TABLE PET_3(

PetID Int NOT NULL,

PetName Char(50) NOT NULL,

);

For MySQL:

MySQL uses the AUTO_INCREMENT keyword to implement surrogate keys. By default,

AUTO_INCREMENT starts at 1 and increments by 1. Although the increment cannot be

changed, the starting value can be reset using an ALTER command as shown below.

CREATE TABLE PET_3(

PetID Int NOT NULL AUTO_INCREMENT,

PetName Char(50) NOT NULL,

);

ALTER TABLE PET AUTO_INCREMENT=1;

See Figure 3-29 for data for this table.

3.31 Write an SQL statement to display the minimum, maximum, and average weight of

dogs.

Chapter Three – Structured Query Language

SELECT MIN(PetWeight) AS MinPetWeight,

MAX(PetWeight) AS MaxPetWeight,

3.32 Write an SQL statement to group the data by PetBreed and display the average

weight per breed.

SELECT PetBreed, AVG(PetWeight) AS AvgBreedWeight

FROM PET_3

3.33 Answer question 3.32, but consider only breeds for which two or more pets are

included in the database.

SELECT PetBreed, AVG(PetWeight) AS AvgBreedWeight

FROM PET_3

3.34 Answer question 3.33, but do not consider any pet having the breed of Unknown.

SELECT PetBreed, AVG(PetWeight) AS AvgBreedWeight

FROM PET_3

3.35 Write an SQL statement to display the last name, first name, and email of any owners

of cats. Use a subquery.

Chapter Three – Structured Query Language

SELECT OwnerLastName, OwnerFirstName, OwnerEmail

3.36 Write an SQL statement to display the last name, first name, and email of any owners

of cats with at cat named Teddy. Use a subquery.

SELECT OwnerLastName, OwnerFirstName, OwnerEmail

The following table schema for the BREED Table shows a new table to be added to the PET

database:

Assume that PetBreed in PET_3 is a foreign key that matches the primary key BreedName in

BREED, and that BreedName in Breed is now a foreign key linking the two tables with the

referential integrity constraint:

If needed, you may also assume that a similar referential integrity constraint exists between

PET and BREED and between PET_2 and BREED. The BREED table data are shown in Figure

3-30.

Chapter Three – Structured Query Language

3.37 Write SQL Statements to (1) create the BREED table, (2) insert the data in Figure 3-

30 into the BREED table, (3) alter the PET_3 table so that PetBreed is a foreign key

referencing BreedName in BREED with cascading updates enabled, and (4) with the

BREED table added to the pet database, write an SQL statement to display the last

name, first name, and email of any owner of a pet that has an AverageLifeExpectancy

value greater than 15. Use a subquery.

);

For SQL Server, Oracle Database, and MySQL:

CREATE TABLE BREED(

The Breed columns in PET and PET_3 will now become foreign keys, and if we haven’t already

included them in our CREATE TABLE statements, we will need to add an additional foreign key

constraint to both tables. We will use the ALTER TABLE command as follows:

For Microsoft Access:

Chapter Three – Structured Query Language

Microsoft Access does not support the ON DELETE and ON UPDATE constraint clauses, so use

the GUI to set the proper ON UPDATE behavior after the following statements:

ALTER TABLE PET

For Oracle:

Oracle does not support ON UPDATE syntax. The proper behavior can be achieved in Oracle

using triggers, but the details are beyond the scope of this book.

ALTER TABLE PET

ADD CONSTRAINT PET_BREED_FK FOREIGN KEY(Breed)

For SQL Server and MySQL:

ALTER TABLE PET

ADD CONSTRAINT PET_BREED_FK FOREIGN KEY(PetBreed)

See Figure 3-30 for data for this table. The SQL INSERT statements for all systems are:

INSERT INTO BREED VALUES(‘Border Collie’, 15.0, 22.5, 20);

The data appear as follows in SQL Server:

The query itself is:

Chapter Three – Structured Query Language

SELECT OwnerLastName, OwnerFirstName, OwnerEmail

FROM PET_OWNER

3.38 Answer question 3.35, but use a join using JOIN ON syntax.

For Microsoft SQL Server and MySQL:

SELECT OwnerLastName, OwnerFirstName, OwnerEmail

For Microsoft Access:

SELECT OwnerLastName, OwnerFirstName, OwnerEmail

We can also use DISTINCT to remove duplicate lines:

SELECT DISTINCT OwnerLastName, OwnerFirstName, OwnerEmail

FROM PET_OWNER as PO JOIN PET_3 as P

Note that Oracle (see page 191) does not allow the use of the “AS” keyword in the JOIN

syntax. In Oracle, therefore, the solutions become (with the same results as above):

Chapter Three – Structured Query Language

SELECT OwnerLastName, OwnerFirstName, OwnerEmail

FROM PET_OWNER PO JOIN PET_3 P

ON PO.OwnerID = P.OwnerID

WHERE PetType = ‘Cat’;

We can also use DISTINCT to remove duplicate lines:

SELECT DISTINCT OwnerLastName, OwnerFirstName, OwnerEmail

3.39 Answer question 3.36, but use a join using JOIN ON syntax.

Note that in this solution we used the table names instead of aliases so the solution is identical

for all DBMSs except Access (contrast with the solutions to the previous question). Note also

that this query happens to return no duplicates, but in theory it could, in which case DISTINCT

would be required to make it precisely equivalent to question 3.36 (see solutions to the

previous question).

For SQL Server, Oracle, and MySQL:

For Access:

3.40 Answer part (4) of question 3.37, but use joins using JOIN ON syntax.

For Microsoft SQL Server and MySQL:

SELECT DISTINCT OwnerLastName, OwnerFirstName, OwnerEmail

FROM (PET_OWNER as PO JOIN PET_3 as P

Chapter Three – Structured Query Language

SELECT DISTINCT OwnerLastName, OwnerFirstName, OwnerEmail

FROM (PET_OWNER as PO INNER JOIN PET_3 as P

Note that Oracle (see page 191) does not allow the use of the “AS” keyword in the JOIN

syntax. In Oracle, therefore, the solutions become (with the same results as above):

SELECT DISTINCT OwnerLastName, OwnerFirstName, OwnerEmail

FROM (PET_OWNER PO JOIN PET_3 P

3.41 Write an SQL statement to display the OwnerLastName, OwnerFirstName, PetName,

PetType, PetBreed, and AverageLifeExpectancy for pets with a known PetBreed.

For SQL Server, Oracle, and MySQL:

SELECT OwnerLastName, OwnerFirstName,

PetName, PetType, PetBreed,

AverageLifeExpectancy

For Access:

SELECT OwnerLastName, OwnerFirstName,

Chapter Three – Structured Query Language

3.42 Write SQL statements to add three new rows to the PET_OWNER table. Assume that

OwnerID is a surrogate key, and that the DBMS will provide a value for it. Use the first

three lines of data provided in Figure 3-31.

See combined answer with Review Question 3.43.

3.43 Write SQL statements to add three new rows to the PET_OWNER table. Assume that

OwnerID is a surrogate key and that the DBMS will provide a value for it. Assume,

however, that you have only OwnerLastName, OwnerFirstName, and OwnerPhone

and that therefore OwnerEmail is NULL. Use the last three lines of the data provided

in Figure 3-31.

See the solution to review question 3.7 for more about surrogate key data entry.

For SQL Server:

INSERT INTO PET_OWNER VALUES (‘Rogers’, ‘Jim’,’555-232-3456′,

‘Jim.Rogers@somewhere.com’);

INSERT INTO PET_OWNER VALUES (‘Keenan’, ‘Mary’, ‘555-232-4567’,

‘Mary.Keenan@somewhere.com’);

Chapter Three – Structured Query Language

For Access and MySQL:

INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerPhone,

OwnerEmail) VALUES(

‘Rogers’, ‘Jim’, ‘555-232-3456’, ‘Jim.Rogers@somewhere.com’);

INSERT INTO PET_OWNER (OwnerLastName, OwnerFirstName, OwnerPhone,

For Oracle:

INSERT INTO PET_OWNER VALUES(seqPOID.NextVal,

‘Rogers’, ‘Jim’, ‘555-232-3456’, ‘Jim.Rogers@somewhere.com’);

INSERT INTO PET_OWNER VALUES(seqPOID.NextVal,

‘Keenan’, ‘Mary’, ‘555-232-4567’, ‘Mary.Keenan@somewhere.com’);

Chapter Three – Structured Query Language

3.44 Write an SQL statement to change the value of Std. Poodle in BreedName of BREED

to Poodle, Std. When you ran this statement, what happened to the data values of

PetBreed in the PET_3 table? Why did this occur?

/* Make change to BREED, which cascades to PET */

UPDATE BREED

Note the updated values of PetBreed in PET_3 (see data below)! This occurred

automatically due to the ON UPDATE CASCADE property of the foreign key linking PET_3 to

BREED. Note also that in Oracle the above update will result in an error.

3.45 Explain what will happen if you leave the WHERE clause off your answer to question

3.44.

All pets would have the Breed ‘Poodle, Std.’

3.46 Write an SQL statement to delete all rows of pets of type Anteater. What will happen

if you forget to code the WHERE clause in this statement?

Chapter Three – Structured Query Language

As there are NO anteaters in the PET_3 table, in PET, no rows are affected.

If the WHERE clause is omitted, we’d delete all the rows in PET—DO NOT run that command!

3.47 Write an SQL statement to add a PetWeight column like the one in PET_3 to the PET

table, given that this column is NULL. Again, assume that PetWeight is Numeric(4,1).

For Microsoft Access:

Access SQL does not support the (m, n) extension of the Numeric data type. Alter the table

with the following command, and then set the column properties in the GUI.

For SQL Server, Oracle Database, and MySQL (note the results in the PET table assume that

question 3.44 has either not been done or has been undone; Std. Poodle is still a breed):

SELECT * FROM PET;

3.48 Write SQL statements to insert data into the PetWeight column you created in

question 3.47. Use the PetWeight from the PET_3 table as shown in Figure 3-29.

SELECT PetID, PetWeight

FROM PET_3;

Chapter Three – Structured Query Language

UPDATE PET

SET PetWeight = 25.5

WHERE PetID = 1;

UPDATE PET

SET PetWeight = 10.5

WHERE PetID = 2;

UPDATE PET

SET PetWeight = 28.5

WHERE PetID = 3;

SELECT * FROM PET;

Note the results in the PET table assume that question 3.44 has either not been done or has

been undone (the breed is still Std. Poodle).

Chapter Three – Structured Query Language

 ANSWERS TO EXERCISES

The following is a set of tables for the Art Course database shown in Figure 1-12. For

the data for these tables, use the data shown in Figure 1-12.

CUSTOMER (CustomerNumber, CustomerLastName, CustomerFirstName, Phone)

COURSE (CourseNumber, Course, CourseDate, Fee)

ENROLLMENT (CustomerNumber, CourseNumber, AmountPaid)

where:

CustomerNumber and CourseNumber are surrogate keys. Therefore, these numbers will

never be modified, and there is no need for cascading updates. No customer data are

ever deleted so there is no need to cascade deletions. Courses can be deleted. If there

are enrollment entries for a deleted class, they should also be deleted.

3.52 Write and run the SQL statements necessary to create the tables and their referential

integrity constraints.

For the database solutions for the Review Questions about the Art-Course-Database, see the IRC

files supplied and use:

• Microsoft Access:

• DBC-e08-Art-Course-Database-CH03-AppE.accdb

• SQL Server 2016:

• Oracle Database XE:

• DBC-e08-ODB-Art-Course-Database-Create-Tables.sql

• DBC-e08-ODB-Art-Course-Database-Insert-Data.sql

• DBC-e08-ODB-Art-Course-Database-SQL-Queries-CH03.sql

Chapter Three – Structured Query Language

• MySQL 5.7:

• DBC-e08-MySQL-Art-Course-Database-Create-Tables.sql

• DBC-e08-MySQL-Art-Course-Database-Insert-Data.sql

• DBC-e08-MySQL-Art-Course-Database-SQL-Queries-CH03.sql

For Microsoft Access (add cascading delete constraint and the autonumber constraint in the GUI

after creating the tables below):

CREATE TABLE CUSTOMER(

CustomerNumber Int NOT NULL,

CustomerLastName Char(25) NOT NULL,

CREATE TABLE COURSE(

CourseNumber Int NOT NULL,

CREATE TABLE ENROLLMENT(

CustomerNumber Int NOT NULL,

CourseNumber Int NOT NULL,

AmountPaid Numeric NULL,

CONSTRAINT ENROLLMENT_PK

For SQL Server:

CREATE TABLE CUSTOMER(

CustomerNumber Int NOT NULL IDENTITY (1, 1),

CustomerLastName Char(25) NOT NULL,

Chapter Three – Structured Query Language

CREATE TABLE COURSE(

CourseNumber Int NOT NULL IDENTITY (1, 1),

);

CREATE TABLE ENROLLMENT(

CustomerNumber Int NOT NULL,

CourseNumber Int NOT NULL,

AmountPaid Numeric(8,2)NULL,

CONSTRAINT ENROLLMENT_PK

For Oracle Database:

The SQL CREATE TABLE commands shown for SQL Server 2016 will also work for Oracle

Dataabase with only two modifications.

First, Oracle Database does not support ON UPDATE referential integrity actions. Second, Oracle

Database uses SEQUENCES to set surrogate keys and set starting values and increment values.

Therefore, the definitions of the CustomerID and CourseID surrogate values should be written

as:

CREATE SEQUENCE seqCustomerID INCREMENT BY 1 START WITH 1;

CREATE TABLE COURSE(