Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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
© 2018 Pearson Education, Inc. Page 31 of 154
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
© 2018 Pearson Education, Inc. Page 33 of 154
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
© 2018 Pearson Education, Inc. Page 36 of 154
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
© 2018 Pearson Education, Inc. Page 37 of 154
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(
