Appendix A Answers to Quizzes and Projects
This appendix provides answers to the quizzes and assignments found at the end of each chapter throughout the book.
Quiz 1
1. True or False. Normalization is a three-step technique used to ensure that all tables are logically linked together and that all fields in a table directly relate to the primary key. Answer: TRUE
2. True or False. A relational database is a collection of one or more tables that are related by key values. Answer: TRUE
3. True or False. A table is a two-dimensional column that contains files and fields. Answer: FALSE
4. True or False. A foreign key is a record in a table that links records of one database with those of another database. Answer: FALSE
5. True or False. A primary key is a column in a table that uniquely identifies every record in that table. Answer: TRUE
Project 1
Use the ERD model to diagram a one-to-many relationship showing one student who takes many courses and a many-to-one relationship showing many students in a single course. Compare this to the many-to-many model.
Answer:
There are many courses with many students.
Quiz 2
1. What does SQL stand for? Answer: Structured Query Language
2. What was SQL called before it was called SQL? Answer: SEQUEL
3. Which SQL component is used to create tables and establish relationships among tables? Answer: Data Definition Language (DDL)
4. True or False. SQL is a procedural database programming language used within DBMSs to create, manage, and secure relational databases. Answer: FALSE
5. True or False. Microsoft Access refers to SQL as PLSQL. Answer: FALSE
Project 2
Practice locating SQL view without looking at the instructions for doing so.
Answer:
For Microsoft Access 2007
1. Click Create from the menu at the top of the screen.
2. Next, click the Query Design button near the top-right side of the screen.
3. Click Close on the Show Table dialog box without selecting any tables.
4. Locate the View drop-down button near the top left of the screen.
5. Use the View button to select SQL View. (Click the down arrow and scroll to the SQL View option.)
For Microsoft Access 2003
1. Click Queries on the left, and then click the New button located near the top of the screen.
2. When the New Query dialog box appears, select Design View and click OK.
3. Click Close in the Show Table dialog box (do not select any tables).
4. Locate the View button near the top of the screen.
5. Use the View button to select SQL View. (Click the down arrow located on the View button to locate the SQL View option.)
Quiz 3
1. True or False. NOT NULL means no value. Answer: FALSE
2. True or False. A data type specifies the maximum number of characters that a cell in a column can hold. Answer: FALSE
3. What constraint is used to link the records of one table to the records of another table? Answer: FOREIGN KEY
4. True or False. The WHERE keyword is used to insert a record into a table. Answer: FALSE
5. True or False. The UPDATE statement is used to update table names. Answer: FALSE
Project 3
Use the following values to insert a record into the Manufacturers table created earlier in the chapter:
Column Name | Value |
ManufacturerID | 1 |
ToyID | 1 |
CompanyName | Matel |
Address | 2892 23rd Ave S |
City | St. Petersburg |
State | FL |
PostalCode | 33710 |
AreaCode | 727 |
PhoneNumber | 324-5421 |
Answer:
INSERT INTO Manufacturers
VALUES (1, 1, 'Matel','2892 23rd Ave S', 'St. Petersburg', 'FL',
33710, 727, '324-5421'),
Quiz 4
1. What two keywords must be used in the SELECT statement? Answer: SELECT and FROM
2. Records retrieved from the database are often referred to as what? Answer: The result set
3. True or False. The TOP keyword is used to display records that fall in the middle of a range specified by an ORDER BY clause. Answer: FALSE
4. True or False. The AS keyword is used to create analias. Answer: TRUE
5. True or False. The DISTINCT keyword is used to display the duplicate values in a column. Answer: FALSE
Project 4
Use the Committee2 table in Figure 4-23 to create a query that displays the following output:
Answer:
SELECT Lastname& ', ' &Firstname AS Name, Address& ' ' &Zipcode
AS FullAddress, Areacode& '-' &PhoneNumber AS
TelephoneNumber
FROM Committee2
ORDER BY Lastname;
Quiz 5
1. True or False. An expression is a special character used to match parts of a value. Answer: FALSE
2. True or False. The following queries are equivalent:
Query 1:
SELECT *
FROM Tools
WHERE ToolID > 3 AND ToolID <10;
Query 2:
SELECT *
FROM Tools
WHERE ToolID BETWEEN 3 AND 10;
Answer: FALSE
3. Using the Friends table in Figure 5-16, what will the following query return?
SELECT FriendsID
FROM Friends
WHERE Lastname = 'Jones' AND Email IS NULL;
Answer: 2
4. True or False. The exclamation mark (!) in the following WHERE clause means NOT:
WHERE Location LIKE '[!A-C]';
Answer: TRUE
5. True or False. The OR operator is processed before the AND operator in the order of evaluation. Answer: FALSE
Project 5
Use the Friends table in Figure 5-16 to write a query that returns records for individuals who live in Florida (FL).
Answer:
SELECT *
FROM Friends
WHERE Address LIKE '*FL*';
Quiz 6
1. True or False. The divide (/) operator is used to return the remainder in division. Answer: FALSE
2. True or False. Aggregate functions operate on only one row at a time. Answer: FALSE
3. True or False. The ddd date format displays the full names of days. Answer: FALSE
4. True or False. The CURRENTTIME () function is used to return the current time. Answer: FALSE
5. True or False. The numeric representation of dates is called a Julian (or serial) date. Answer: TRUE
Project 6
Use the Computers table in Figure 6-13 to display today’s date and time, the SerialNum column, and the last five numbers from each serial number in the SerialNum column.
Answer:
SELECT NOW () AS DateAndTime, SerialNum, RIGHT (SerialNum, 5)
AS LastFiveChars
FROM Computers;
Quiz 7
1. True or False. The GROUP BY clause can only be used in queries that contain at least two aggregate functions.
Answer: FALSE
2. Will the following query work?
SELECT DATE () AS TodaysDate
FROM Transactions
GROUP BY CustomerID;
Answer: No. The query does not require a GROUP BY clause since there is no aggregate function in the query.
3. True or False. When using the GROUP BY clause with a WHERE clause, the GROUP BY clause must appear before the WHERE clause. Answer: FALSE
4. True or False. The GROUP BY clause must appear before the ORDER BY clause. Answer: TRUE
5. True or False. The HAVING clause filters rows before any data is grouped. Answer: FALSE
Project 7
Use the Transactions table in Figure 7-2 to display the customer IDs and the total number of products purchased by customers who only purchased one product.
Answer:
SELECT CustomerID, COUNT (ProductID) AS TotalProductsPurchased
FROM Transactions
GROUP BY CustomerID
HAVING COUNT (ProductID) = 1;
Quiz 8
1. True or False. A join enables you to use a single SELECT statement to query two or more tables simultaneously. Answer: TRUE
2. True or False. The following shows the correct syntax to qualify a table and column name: Tablename,Columnname. Answer: FALSE
3. True or False. Table aliases are created just like column aliases. Answer: TRUE
4. True or False. The UNION ALL keyword is used to combine records from two queries while excluding duplicate records. Answer: FALSE
5. True or False. A left outer join is used to select every record from the table specified to the left of the LEFT JOIN keywords. Answer: TRUE
Project 8
Use the Products table in Figure 8-12 and the Transactions table in Figure 8-14 to create an outer join that will display product IDs with customer IDs and purchase dates for customers who purchased a product (product ID). Additionally, display product IDs of products that have not been purchased yet.
Answer:
SELECT P.ProductID, T.CustomerID, T.DateSold
FROM Transactions AS T RIGHT JOIN Products AS P
ON T.ProductID = P.ProductID;
Quiz 9
1. True or False. A correlated subquery executes once for each record a referenced query returns. Answer: TRUE
2. True or False. The NOT operator is used to instruct Microsoft Access to match any condition opposite of the one defined. Answer: TRUE
3. True or False. The IN predicate is often used with the following comparison operators: =, <>, <, >, <=, and >=. Answer: FALSE
4. True or False. A subquery linked by the IN predicate can return two columns. Answer: FALSE
5. True or False. Subqueries nested within other queries are processed first, working outward. Answer: TRUE
Project 9
Use the Products table in Figure 9-21 to create a subquery that retrieves the ProductID and ProductName columns for products that have 30 or more items on order.
Answer:
SELECT ProductID, ProductName
FROM Products
WHERE OnOrder >= ALL
(SELECT OnOrder
FROM Products
WHERE OnOrder = 30);
Quiz 10
1. True or False. Updating data in views does not affect data stored in tables. Answer: FALSE
2. Views are commonly referred to as what? Answer: Virtual tables
3. True or False. Views are deleted using the DELETE keyword. Answer: FALSE
4. True or False. Views are created in SQL-92 using the CREATE VIEW keywords. Answer: TRUE
5. True or False. Deleting a table on which a view is dependent does not affect the view. Answer: FALSE
Project 10
Use the ComputerBrandLoc view in Figure 10-7 to update the Computers table in Figure 10-1. Update the office number for serial number X8276538101 from 311 to 136.
Answer:
UPDATE ComputerBrandLoc
SET OfficeNumber = 136
WHERE OfficeNumber = 311
AND SerialNum = 'X8276538101';
Quiz 11
1. True or False. The DISALLOW NULL option is used in the WITH clause. Answer: TRUE
2. Which option is used in the WITH clause to cause null data in a table to be ignored for an index? Answer: IGNORE NULL
3. True or False. The DELETE TABLE keywords are used to delete or remove an index. Answer: FALSE
4. True or False. The ALTER TABLE keywords are used to modify columns in an existing table. Answer: TRUE
5. What keywords are used in the ALTER TABLE statement to change a column’s data type or field size? Answer: ALTER COLUMN
Project 11
1. Add a column named NewColumn to the Numbers table in Figure 11-1. Additionally, add a CHAR data type with a field size of 3.
Answer:
ALTER TABLE Numbers
ADD NewColumn CHAR (3);
2. Create a unique index named NewColumnIdx for the NewColumn column you created in the Numbers table.
Answer:
CREATE UNIQUE INDEX NewColumnIdx
ON Numbers (NewColumn);
Quiz 12
1. True or False. Updating data in temporary tables does not affect data stored in tables. Answer: TRUE
2. True or False. Temporary tables are automatically dropped when you log off or close Access. Answer: FALSE
3. True or False. Temporary tables are deleted using the DELETE keyword. Answer: FALSE
4. True or False. You must use the INTO keyword to create a temporary table in Access. Answer: TRUE
5. True or False. Temporary tables store the most current, up-to-date data. Answer: FALSE
Project 12
Create a temporary table named Temp2 that selects all the information from a table named Flowers with the following column names: FlowerID, Type, Color, Size.
Answer:
SELECT * INTO Temp2
FROM Flowers;
Quiz 13
1. True or False. A parameter query is a query that enables the user to set the criteria for selecting records at run time by filling in a dialog box. Answer: TRUE
2. True or False. When you use the BETWEEN keyword in a parameter query, it does not include records that match the values entered by the user. Answer: FALSE
3. True or False. Parameter queries can be used within forms. Answer: TRUE
4. True or False. The use of brackets in a parameter query is optional. Answer: FALSE
5. True or False. The asterisk is used with the LIKE keyword to match characters in a parameter query. Answer: TRUE
Project 13
Use the Sales table in Figure 13-13 to create a parameter query that prompts the user for two dates.
Answer:
SQL View:
SELECT *
FROM Sales;
Design view criteria:
BETWEEN [Type the first date:] AND [Type the second date:]
Quiz 16
1. What are the major differences between a project and a database? Answer: Review Chapter 16 for the answers.
2. True or False. The only way to include an SQL table in Access is with an Access project. Answer: FALSE