Appendix C. Answers to Quizzes and Exercises
Hour 1, “Welcome to the World of SQL”
Quiz Answers
1. What does the acronym SQL stand for?
A. SQL stands for Structured Query Language.
2. What are the six main categories of SQL commands?
A. Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)
Data administration commands (DAC)
Transactional control commands (TCC)
3. What are the four transactional control commands?
A. COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTIONS
4. What is the main difference between client/server and web technologies as they relate to database access?
A. The connection to the database is the main difference. Using the client to connect means you log on to the server directly to access the database. When using the Web, you log on to the Internet to reach the database.
5. If a field is defined as NULL, does something have to be entered into that field?
A. No. If a column is defined as NULL, nothing has to be in the column. If a column is defined as NOT NULL, something does have to be entered.
Exercise Answers
1. Identify the categories in which the following SQL commands fall:
CREATE TABLE
DELETE
SELECT
INSERT
ALTER TABLE
UPDATE
A. CREATE TABLE—DDL, Data Definition Language
DELETE—DML, Data Manipulation Language
SELECT—DQL, Data Query Language
INSERT—DML, Data Manipulation Language
ALTER TABLE—DDL, Data Definition Language
UPDATE—DML, Data Manipulation Language
2. Study the following tables, and pick out the column that would be a good candidate for the primary key.
A. The primary key for EMPLOYEE_TBL is the employee number. Each employee is assigned a unique employee number. Employees could have the same name, phone, start date, and address.
The primary key for INVENTORY_TBL is the item number. The other columns could be duplicated.
The primary key for EQUIPMENT_TBL is the equipment number. Once again, the other columns could be duplicated.
Hour 2, “Defining Data Structures”
Quiz Answers
1. True or false: An individual’s Social Security number, entered in the format '111111111', can be any of the following data types: constant length character, varying length character, or numeric.
A. True, as long as the precision is the correct length.
2. True or false: The scale of a numeric value is the total length allowed for values.
A. False. The precision is the total length, where the scale represents the number of places reserved to the right of a decimal point.
3. Do all implementations use the same data types?
A. No. Most implementations differ in their use of data types. The data types prescribed by ANSI are adhered to but might differ among implementations according to storage precautions taken by each vendor.
4. What are the precision and scale of the following?
DECIMAL(4,2)
DECIMAL(10,2)
DECIMAL(14,1)
A. DECIMAL(4,2)—Precision = 4, scale = 2
DECIMAL(10,2)—Precision = 10, scale = 2
DECIMAL(14,1)—Precision = 14, scale = 1
5. Which numbers could be inserted into a column whose data type is DECIMAL(4,1)?
a. 16.2
b. 116.2
c. 16.21
d. 1116.2
e. 1116.21
A. The first three fit, although 16.21 is rounded off to 16.2. The numbers 1116.2 and 1116.21 exceed the maximum precision, which was set at 4.
A. Data is a collection of information stored in a database as one of several different data types.
Exercise Answers
1. Take the following column titles, assign them to a data type, decide on the proper length, and give an example of the data you would enter into that column.
A. SSN—Constant-length character; '111111111'
STATE—Varying-length character; 'INDIANA'
CITY—Varying-length character; 'INDIANAPOLIS'
PHONE_NUMBER—Constant-length character; '(555)555-5555'
ZIP—Constant-length character; '46113'
LAST_NAME—Varying-length character; 'JONES'
FIRST_NAME—Varying-length character; 'JACQUELINE'
MIDDLE_NAME—Varying-length character; 'OLIVIA'
SALARY—Numeric data type; 30000
HOURLY_PAY_RATE—Decimal; 35.00
DATE_HIRED—Date; '01/01/2007'
2. Take the same column titles and decide if they should be NULL or NOT NULL, realizing that in some cases where a column would normally be NOT NULL, the column could be NULL or vice versa, depending on the application.
STATE—NOT NULL
CITY—NOT NULL
PHONE_NUMBER—NULL
ZIP—NOT NULL
LAST_NAME—NOT NULL
FIRST_NAME—NOT NULL
MIDDLE_NAME—NULL
SALARY—NULL
HOURLY_PAY_RATE—NULL
DATE_HIRED—NOT NULL
Some individuals might not have a phone (however rare that might be), and not everyone has a middle name, so these columns should allow NULL values. In addition, not all employees are paid an hourly rate.
Hour 3, “Managing Database Objects”
Quiz Answers
1. Does the following CREATE TABLE statement work? If not, what needs to be done to correct the problem(s)? Are there limitations as to what database implementation it works in (MySQL, Oracle, SQL Server)?
CREATE TABLE EMPLOYEE_TABLE AS:
( SSN NUMBER(9) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
FIRST_NAME VARCHAR(20) NOT NULL,
MIDDLE_NAME VARCHAR2(20) NOT NULL,
ST ADDRESS VARCHAR2(20) NOT NULL,
CITY CHAR(20) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMBER(4) NOT NULL,
DATE HIRED DATE);
A. The CREATE TABLE statement does not work because there are several errors in the syntax. The corrected statement follows and is given as an Oracle-specific version. A listing of what was incorrect follows a corrected statement.
CREATE TABLE EMPLOYEE_TABLE
( SSN NUMBER() NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
FIRST_NAME VARCHAR2(20) NOT NULL,
MIDDLE_NAME VARCHAR2(20),
ST_ADDRESS VARCHAR2(30) NOT NULL,
CITY VARCHAR2(20) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMBER(5) NOT NULL,
DATE_HIRED DATE );
The following needs to be done:
- The
AS:should not be in thisCREATE TABLEstatement. - A comma is missing after the
NOT NULLfor theLAST_NAMEcolumn. - The
MIDDLE_NAMEcolumn should beNULLbecause not everyone has a middle name. - The column
ST ADDRESSshould beST_ADDRESS. With two words, the database looked atSTas being the column name, which would make the database look for a valid data type, where it would find the wordADDRESS. - The
CITYcolumn works, although it would be better to use theVARCHAR2data type. If all city names were a constant length,CHARwould be okay. - The
STATEcolumn is missing a left parenthesis. - The
ZIPcolumn length should be(5), not(4). - The
DATE HIREDcolumn should beDATE_HIREDwith an underscore to make the column name one continuous string.
2. Can you drop a column from a table?
A. Yes. However, even though it is an ANSI standard, you must check your particular implementation to see if it has been accepted.
3. What statement would you issue to create a primary key constraint on the preceding EMPLOYEE_TABLE?
A. ALTER TABLE EMPLOYEE_TBL
ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(SSN);
4. What statement would you issue on the preceding EMPLOYEE_TABLE to allow the MIDDLE_NAME column to accept NULL values?
A. ALTER TABLE EMPOYEE_TBL
MODIFY MIDDLE_NAME VARCHAR(20), NOT NULL;
5. What statement would you use to restrict the people added into the preceding EMPLOYEE_TABLE to only reside in the state of New York ('NY')?
A. ALTER TABLE EMPLOYEE_TBL
ADD CONSTRAINT CHK_STATE CHECK(STATE='NY'),
6. What statement would you use to add an auto-incrementing column called 'EMPID' to the preceding EMPLOYEE_TABLE using both the MySQL and SQL Server syntax?
A. ALTER TABLE EMPLOYEE_TBL
ADD COLUMN EMPID INT AUTO_INCREMENT;
Exercise Answers
N. answer required.
Hour 4, “The Normalization Process”
Quiz Answers
1. True or false: Normalization is the process of grouping data into logical related groups.
A. True.
2. True or false: Having no duplicate or redundant data in a database, and having everything in the database normalized, is always the best way to go.
A. False. Not always; normalization can and does slow performance because more tables must be joined, which results in more I/O and CPU time.
3. True or false: If data is in the third normal form, it is automatically in the first and second normal forms.
A. True.
4. What is a major advantage of a denormalized database versus a normalized database?
A. The major advantage is improved performance.
5. What are some major disadvantages of denormalization?
A. Having redundant and duplicate data takes up valuable space; it is harder to code, and much more data maintenance is required.
6. How do you determine if data needs to be moved to a separate table when normalizing your database?
A. If the table has redundant groups of data, this data would be a candidate to remove into a separate table.
7. What are the disadvantages of overnormalizing your database design?
A. Overnormalization can lead to excess CPU and memory utilization, which can put excess strain on the server.
Exercise Answers
1. You are developing a new database for a small company. Take the following data and normalize it. Keep in mind that there would be many more items for a small company than you are given here.
Employees:
Angela Smith, secretary, 317-545-6789, RR 1 Box 73, Greensburg, Indiana, 47890, $9.50 hour, date started January 22, 1996, SSN is 323149669.
Jack Lee Nelson, salesman, 3334 N. Main St., Brownsburg, IN, 45687, 317-852-9901, salary of $35,000.00 year, SSN is 312567342, date started 10/28/95.
Customers:
Robert’s Games and Things, 5612 Lafayette Rd., Indianapolis, IN, 46224, 317-291-7888, customer ID is 432A.
Reed’s Dairy Bar, 4556 W 10th St., Indianapolis, IN, 46245, 317-271-9823, customer ID is 117A.
Customer Orders:
Customer ID is 117A, date of last order is February 20, 1999, the product ordered was napkins, and the product ID is 661.
A. Employees Customers Orders
SSN CUSTOMER ID CUSTOMER ID
NAME NAME PRODUCT ID
STREET ADDRESS STREET ADDRESS PRODUCT
CITY CITY DATE ORDERED
STATE STATE
ZIP ZIP
PHONE NUMBER PHONE NUMBER
SALARY
HOURLY PAY
START DATE
POSITION
Hour 5, “Manipulating Data”
Quiz Answers
1. Use the EMPLOYEE_TBL with the following structure:
Column data type (not)null
last_name varchar2(20) not null
first_name varchar2(20) not null
ssn char(9) not null
phone number(10) null
LAST_NAME FIRST_NAME SSN PHONE
SMITH JOHN 312456788 3174549923
ROBERTS LISA 232118857 3175452321
SMITH SUE 443221989 3178398712
PIERCE BILLY 310239856 3176763990
What would happen if the following statements were run?
a. INSERT INTO EMPLOYEE_TBL
(''JACKSON', 'STEVE', '313546078', '3178523443'),
A. The INSERT statement does not run because the keyword VALUES is missing in the syntax.
b. INSERT INTO EMPLOYEE_TBL VALUES
('JACKSON', 'STEVE', '313546078', '3178523443'),
A. One row would be inserted into the EMPLOYEE_TBL.
c. INSERT INTO EMPLOYEE_TBL VALUES
('MILLER', 'DANIEL', '230980012', NULL);
A. One row would be inserted into the EMPLOYEE_TBL, with a NULL value in the PHONE column.
d. INSERT INTO EMPLOYEE_TBL VALUES
('TAYLOR', NULL, '445761212', '3179221331'),
A. The INSERT statement would not process because the FIRST_NAME column is NOT NULL.
e. DELETE FROM RMPLOYEE_TBL;
A. All rows in EMPLOYEE_TBL would be deleted.
f. DELETE FROM EMPLOYEE_TBL
WHERE LAST_NAME = 'SMITH';
A. All employees with the last name of SMITH would be deleted from EMPLOYEE_TBL.
g. DELETE FROM EMPLOYEE_TBL
WHERE LAST_NAME = 'SMITH'
AND FIRST_NAME = 'JOHN';
A. Only JOHN SMITH would be deleted from the EMPLOYEE_TBL.
h. UPDATE EMPLOYEE_TBL
SET LAST_NAME – 'CONRAD';
A. All last names would be changed to CONRAD.
i. UPDATE EMPLOYEE_TBL
SET LAST_NAME = 'CONRAD'
WHERE LAST_NAME = 'SMITH';
A. Both JOHN and SUE SMITH would now be JOHN and SUE CONRAD.
j. UPDATE EMPLOYEE_TBL
SET LAST_NAME = 'CONRAD',
FIRST_NAME = 'LARRY';
A. All employees are now LARRY CONRAD.
k. UPDATE EMPLOYEE_TBL
SET LAST_NAME = 'CONRAD',
FIRST_NAME = 'LARRY'
WHERE SSN = '312456788';
Exercise Answers
A. JOHN SMITH is now LARRY CONRAD.
2. Use PRODUCTS_TBL for the next exercise.
a. Add the following products to the product table:
PROD_ID PROD_DESC COST
301 FIREMAN COSTUME 24.99
302 POLICEMAN COSTUME 24.99
303 KIDDIE GRAB BAG 4.99
A. INSERT INTO PRODUCTS_TBL VALUES
('301','FIREMAN COSTUME',24.99);
INSERT INTO PRODUCTS_TBL VALUES
('302','POLICEMAN COSTUME',24.99);
INSERT INTO PRODUCTS_TBL VALUES
('303','KIDDIE GRAB BAG',4.99);
b. Write DML to correct the cost of the two costumes added. The cost should be the same as the witch costume.’
A. UPDATE PRODUCTS_TBL
SET COST = 29.99
WHERE PROD_ID = '301';
UPDATE PRODUCTS_TBL
SET COST = 29.99
WHERE PROD_ID = '302';
c. Now we have decided to cut our product line, starting with the new products. Remove the three products you just added.
A. DELETE FROM PRODUCTS_TBL WHERE PROD_ID = '301';
DELETE FROM PRODUCTS_TBL WHERE PROD_ID = '302';
DELETE FROM PRODUCTS_TBL WHERE PROD_ID = '303';
d. Before you executed the statements to remove the products you added, what should you have done to ensure that you only delete the desired rows?
A. To ensure that you are deleting exactly what you want to delete, you need to perform a SELECT statement using the same FROM and WHERE clause.
Hour 6, “Managing Database Transactions”
Quiz Answers
1. True or false: If you have committed several transactions, have several more transactions that have not been committed, and issue a ROLLBACK command, all your transactions for the same session are undone.
A. False. When a transaction is committed, the transaction cannot be rolled back.
2. True or false: A SAVEPOINT command actually saves transactions after a specified number of transactions have executed.
A. False. A SAVEPOINT is used only as a point for a ROLLBACK to return to.
3. Briefly describe the purpose of each one of the following commands: COMMIT, ROLLBACK, and SAVEPOINT.
A. COMMIT saves changes made by a transaction. ROLLBACK undoes changes made by a transaction. SAVEPOINT creates logical points in the transaction to which to roll back.
4. What are some differences in the implementation of transactions in Microsoft SQL Server?
A. SQL Server auto-commits statements unless specifically placed in a transaction and has a different syntax for SAVEPOINT. Also, it does not support the RELEASE SAVEPOINT command.
5. What are some performance implications when using transactions?
A. Transactions have implications on temporary storage space because the database server has to keep track of all the changes until they are committed in case of a ROLLBACK.
Exercise Answers
1. Take the following transactions and create a SAVEPOINT or a SAVE TRANSACTION command after the first three transactions. Then create a ROLLBACK statement for your SAVEPOINT at the end. Try to determine what CUSTOMER_TBL will look like after you are done.
A. INSERT INTO CUSTOMER_TBL VALUES(615,'FRED WOLF','109 MEMORY
LANE','PLAINFIELD','IN',46113,'3175555555',NULL);
INSERT INTO CUSTOMER_TBL VALUES(559,'RITA THOMPSON',
'125PEACHTREE','INDIANAPOLIS','IN',46248,'3171111111',NULL);
INSERT INTO CUSTOMER_TBL VALUES(715,'BOB DIGGLER',
'1102 HUNTINGTON ST','SHELBY','IN',41234,'3172222222',NULL);
SAVEPOINT SAVEPOINT1;
UPDATE CUSTOMER_TBL SET CUST_NAME='FRED WOLF' WHERE CUST_ID='559';
UPDATE CUSTOMER_TBL SET CUST_ADDRESS='APT C 4556 WATERWAY'
WHERE CUST_ID='615';
UPDATE CUSTOMER_TBL SET CUST_CITY='CHICAGO' WHERE CUST_ID='715';
ROLLBACK;
2. Take the following group of transactions and create a SAVEPOINT after the first three transactions.
Then place a COMMIT statement at the end followed by a ROLLBACK statement to your SAVEPOINT. What do you think should happen?
A. UPDATE CUSTOMER_TBL SET CUST_NAME='FRED WOLF' WHERE CUST_ID='559';
UPDATE CUSTOMER_TBL SET CUST_ADDRESS='APT C 4556 WATERWAY'
WHERE CUST_ID='615';
UPDATE CUSTOMER_TBL SET CUST_CITY='CHICAGO' WHERE CUST_ID='715';
SAVEPOINT SAVEPOINT1;
DELETE FROM CUSTOMER_TBL WHERE CUST_ID='615';
DELETE FROM CUSTOMER_TBL WHERE CUST_ID='559';
DELETE FROM CUSTOMER_TBL WHERE CUST_ID='615';
COMMIT;
ROLLBACK;
Because the statement is committed, the ROLLBACK statement doesn’t have an effect.
Hour 7, “Introduction to the Database Query”
Quiz Answers
1. Name the required parts for any SELECT statement.
A. The SELECT and FROM keywords, also called clauses, are required for all SELECT statements.
2. In the WHERE clause, are single quotation marks required for all the data?
A. No. Single quotation marks are required when selecting alphanumeric data types. Number data types do not require single quotation marks.
3. Under what part of the SQL language does the SELECT statement (database query) fall?
A. The SELECT statement is considered Data Query Language.
4. Can multiple conditions be used in the WHERE clause?
A. Yes. Multiple conditions can be specified in the WHERE clause of SELECT, INSERT, UPDATE, and DELETE statements. Multiple conditions are used with the operators AND and OR, which are thoroughly discussed in Hour 8, “Using Operators to Categorize Data.”
5. What is the purpose of the DISTINCT option?
A. The DISTINCT option suppresses the display of duplicates.
6. Is the ALL option required?
A. No. Even though the ALL option can be used, it is not really required.
7. How are numeric characters treated when ordering based upon a character field?
A. They are sorted as ASCII characters. This means that numbers would be ordered like this: 1, 12, 2, 222, 22222, 3, 33.
8. How does Oracle handle its default case sensitivity differently from MySQL and Microsoft SQL Server?
A. Oracle by default performs matches as case sensitive.
Exercise Answers
1. Invoke your RDBMS query editor on your computer. Using your learnsql database, enter the following SELECT statements. Determine whether the syntax is correct. If the syntax is incorrect, make corrections to the code as necessary. We are using the EMPLOYEE_TBL here.
a. SELECT EMP_ID, LAST_NAME, FIRST_NAME,
FROM EMPLOYEE_TBL;
A. This SELECT statement does not work because there is a comma after the FIRST_NAME column that does not belong there. The correct syntax follows:
a. SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL;
b. SELECT EMP_ID, LAST_NAME
ORDER BY EMP_ID
FROM EMPLOYEE_TBL;
A. This SELECT statement does not work because the FROM and ORDER BY clauses are in the incorrect order. The correct syntax follows:
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
ORDER BY EMP_ID;
c. SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '213764555'
ORDER BY EMP_ID;
A. The syntax for this SELECT statement is correct.
d. SELECT EMP_ID SSN, LAST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '213764555'
ORDER BY 1;
A. The syntax for this SELECT statement is correct. Notice that the EMP_ID column is renamed SSN.
e. SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '213764555'
ORDER BY 3, 1, 2;
A. The syntax for this SELECT statement is correct. Notice the order of the columns in the ORDER BY. This SELECT statement returns records from the database that are sorted by FIRST_NAME, and then by EMP_ID, and finally by LAST_NAME.
2. Does the following SELECT statement work?
SELECT LAST_NAME, FIRST_NAME, PHONE
FROM EMPLOYEE_TBL
WHERE EMP_ID = '333333333';
A. The syntax is correct and the statement worked, even though no data was returned. No data was returned because there was no row with an EMP_ID of 333333333.
3. Write a SELECT statement that returns the name and cost of each product from the PRODUCTS_TBL. Which product is the most expensive?
A. SELECT PROD_DESC,COST FROM PRODUCTS_TBL;
The witch costume is the most expensive.
4. Write a query that generates a list of all customers and their telephone numbers.
A. SELECT CUST_NAME,CUST_PHONE FROM CUSTOMER_TBL;
Hour 8, “Using Operators to Categorize Data”
Quiz Answers
1. True or false: Both conditions when using the OR operator must be TRUE.
A. False. Only one of the conditions must be TRUE.
2. True or false: All specified values must match when using the IN operator.
A. False. Only one of the values must match.
3. True or false: The AND operator can be used in the SELECT and the WHERE clauses.
A. False. The AND operator can only be used in the WHERE clause.
4. True or false: The ANY operator can accept an expression list.
A. False. The ANY operator cannot take an expression list.
5. What is the logical negation of the IN operator?
A. NOT IN.
6. What is the logical negation of the ANY and ALL operators?
A. <>ANY and <>ALL.
7. What, if anything, is wrong with the following SELECT statements?
a. SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN 20000, 30000;
A. The AND is missing between 20000, 30000. The correct syntax is
SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN 20000 AND 30000;
b. SELECT SALARY + DATE_HIRE
FROM EMPLOYEE_PAY_TBL;
A. The DATE_HIRE column is a DATE data type and is in the incorrect format for arithmetic functions.
c. SELECT SALARY, BONUS
FROM EMPLOYEE_PAY_TBL
WHERE DATE_HIRE BETWEEN 1999-09-22
AND 1999-11-23
AND POSITION = 'SALES'
OR POSITION = 'MARKETING'
AND EMP_ID LIKE '%55%';
A. The syntax is correct.
Exercise Answers
1. Using the following CUSTOMER_TBL, write a SELECT statement that returns customer IDs and customer names (alpha order) for customers who live in Indiana, Ohio, Michigan, and Illinois, and whose names begin with the letters A or B:
DESCRIBE CUSTOMER_TBL
Name Null? Type
------------------------------ --------- ------------
CUST_ID NOT NULL VARCHAR (10)
CUST_NAME NOT NULL VARCHAR (30)
CUST_ADDRESS NOT NULL VARCHAR (20)
CUST_CITY NOT NULL VARCHAR (12)
CUST_STATE NOT NULL VARCHAR (2)
CUST_ZIP NOT NULL VARCHAR (5)
CUST_PHONE VARCHAR (10)
CUST_FAX VARCHAR (10)
A. SELECT CUST_ID, CUST_NAME, CUST_STATE
FROM CUSTOMER_TBL
WHERE CUST_STATE IN ('IN', 'OH', 'MI', 'IL')
AND CUST_NAME LIKE 'A%'
OR CUST_NAME LIKE 'B%'
ORDER BY CUST_NAME;
2. Using the following PRODUCTS_TBL, write a SELECT statement that returns the product ID, product description, and product cost. Limit the product cost to between $1.00 and $12.50:
DESCRIBE PRODUCTS_TBL
Name Null? Type
------------------------------- -------- ------------
PROD_ID NOT NULL VARCHAR (10)
PROD_DESC NOT NULL VARCHAR (25)
COST NOT NULL DECIMAL(6,2)
A. SELECT *
FROM PRODUCTS_TBL
WHERE COST BETWEEN 1.00 AND 12.50;
3. Assuming that you used the BETWEEN operator in Exercise 2, rewrite your SQL statement to achieve the same results using different operators. If you did not use the BETWEEN operator, do so now.
A. SELECT *
FROM PRODUCTS_TBL
WHERE COST >= 1.00 AND COST <= 12.50;
SELECT *
FROM PRODUCTS_TBL
WHERE COST BETWEEN 1.00 AND 12.50;
4. Write a SELECT statement that returns products that are either less than 1.00 or greater than 12.50. There are two ways to achieve the same results.
A. SELECT *
FROM PRODUCTS_TBL
WHERE COST < 1.00 OR COST > 12.50;
SELECT *
FROM PRODUCTS_TBL
WHERE COST NOT BETWEEN 1.00 AND 12.50;
Also keep in mind that BETWEEN is inclusive of the upper and lower values, whereas NOT BETWEEN is not inclusive.
5. Write a SELECT statement that returns the following information from PRODUCTS_TBL: product description, product cost, and 5% sales tax for each product. List the products in order from most to least expensive.
A. SELECT PROD_DESC, COST, COST * .05
FROM PRODUCTS_TBL
ORDER BY COST DESC;
6. Write a SELECT statement that returns the following information from PRODUCTS_TBL: product description, product cost, 5% sales tax for each product, and total cost with sales tax. List the products in order from most to least expensive. There are two ways to achieve the same results. Try both.
A. SELECT PROD_DESC, COST, COST * .05, COST + (COST * .05)
FROM PRODUCTS_TBL
ORDER BY COST DESC;
SELECT PROD_DESC, COST, COST * .05, COST * 1.05
FROM PRODUCTS_TBL
ORDER BY COST DESC;
7. Pick three items from the PRODUCTS table. Now write a query to return the rows of data from the table associated with those three items. Now rewrite the query to return everything but those three items. For your query, use combinations of equality operators and conjunctive operators.
A. SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID=11235
OR PROD_ID=119
OR PROD_ID=13;
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_ID<>11235
AND PROD_ID<>119
AND PROD_ID<>13;
8. Rewrite the queries you wrote in Exercise 7 using the IN operator. Which statement is more efficient? Which one is more readable?
A. SELECT * FROM PRODUCT_TBL
WHERE PROD_ID IN (11235,119,13);
SELECT *
FROM PRODUCT_TBL
WHERE PROD_ID IN (11235,119,13);
9. Write a query to return all the products that start with the letter P. Now write a query to return all products that do not start with the letter P.
A. SELECT *
FROM PRODUCTS_TBL
WHERE PROD_DESC LIKE ('P%'),
SELECT *
FROM PRODUCTS_TBL
WHERE PROD_DESC NOT LIKE ('P%'),
Hour 9, “Summarizing Data Results from a Query”
Quiz Answers
1. True or False: The AVG function returns an average of all rows from a SELECT column, including any NULL values.
A. False. The NULL values are not considered.
2. True or False: The SUM function adds column totals.
A. False. The SUM function returns a total for a group of rows.
3. True or False: The COUNT(*) function counts all rows in a table.
A. True.
4. Do the following SELECT statements work? If not, what fixes the statements?
a. SELECT COUNT *
FROM EMPLOYEE_PAY_TBL;
A. This statement does not work because the left and right parentheses are missing around the asterisk. The correct syntax is
SELECT COUNT(*)
FROM EMPLOYEE_PAY_TBL;
b. SELECT COUNT(EMP_ID), SALARY
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY;
A. Yes, this statement works.
c. SELECT MIN(BONUS), MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000;
A. Yes, this statement works.
d. SELECT COUNT(DISTINCT PROD_ID) FROM PRODUCTS_TBL;
A. Yes, this statement works.
e. SELECT AVG(LAST_NAME) FROM EMPLOYEE_TBL;
A. No, this statement does not work because LAST_NAME needs to be a numeric value.
f. SELECT AVG(PAGER) FROM EMPLOYEE_TBL;
A. Yes, this statement works with the current set of data in the database.
Exercise Answers
1. Use EMPLOYEE_PAY_TBL to construct SQL statements to solve the following exercises:
a. What is the average salary?
A. The average salary is $30,000.00. The SQL statement to return the data is
SELECT AVG(SALARY)
FROM EMPLOYEE_PAY_TBL;
b. What is the maximum bonus?
A. The maximum bonus is $2000.00. The SQL statement to return the data is
SELECT MAX(BONUS)
FROM EMPLOYEE_PAY_TBL;
c. What are the total salaries?
A. The sum of all the salaries is $90,000.00. The SQL statement to return the data is
SELECT SUM(SALARY)
FROM EMPLOYEE_PAY_TBL;
d. What is the minimum pay rate?
A. The minimum pay rate is $11.00 an hour. The SQL statement to return the data is
SELECT MIN(PAY_RATE)
FROM EMPLOYEE_PAY_TBL;
e. How many rows are in the table?
A. The total row count of the table is six. The SQL statement to return the data is
SELECT COUNT(*)
FROM EMPLOYEE_PAY_TBL;
2. Write a query to determine how many employees are in the company whose last names begin with a G.
A. We should get two employees using the following syntax:
SELECT COUNT(*)
FROM EMPLOYEE_TBL
WHERE LAST_NAME LIKE 'G%';
3. Write a query to determine the total dollar amount for all the orders in the system. Rewrite the query to determine the total dollar amount if we set the price of each item as $10.00.
A. SELECT SUM(COST*QTY)
FROM ORDERS_TBL,PRODUCTS_TBL
WHERE ORDERS_TBL.PROD_ID=PRODUCTS_TBL.PROD_ID;
SELECT SUM(QTY) * 10
FROM ORDERS_TBL;
4. Write two sets of queries to find the first employee name and last employee name when they are listed in alphabetical order.
A. SELECT MIN(LAST_NAME) AS LAST_NAME FROM EMPLOYEE_TBL;
SELECT MAX(LAST_NAME) AS LAST_NAME
FROM EMPLOYEE_TBL;
5. Write a query to perform an AVG function on the employee names. Does the statement work? Determine why it is that you got that result.
A. SELECT AVG(LAST_NAME) AS LAST_NAME FROM EMPLOYEE_TBL;
It errors out because it is not a numeric value.
Hour 10, “Sorting and Grouping Data”
Quiz Answers
1. Will the following SQL statements work?
a. SELECT SUM(SALARY), EMP_ID
FROM EMPLOYEE_PAY_TBL
GROUP BY 1 AND 2;
A. No, this statement will not work. The and in the GROUP BY clause does not belong there, and you cannot use an integer in the GROUP BY clause. The correct syntax is
SELECT SUM(SALARY), EMP_ID
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY, EMP_ID;
b. SELECT EMP_ID, MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY, EMP_ID;
A. Yes, this statement will work.
c. SELECT EMP_ID, COUNT(SALARY)
FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID
GROUP BY SALARY;
A. No, this statement will not work. The ORDER BY clause and the GROUP BY clause are not in the correct sequence. Also, the EMP_ID column is required in the GROUP BY clause. The correct syntax is
SELECT EMP_ID, COUNT(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY EMP_ID
ORDER BY EMP_ID;
d. SELECT YEAR(DATE_HIRE) AS YEAR_HIRED,SUM(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY 1
HAVING SUM(SALARY)>20000;
A. Yes, this statement will work.
2. True or false: You must also use the GROUP BY clause when using the HAVING clause.
A. False. The HAVING clause can be used without a GROUP BY clause.
3. True or false: The following SQL statement returns a total of the salaries by groups:
SELECT SUM(SALARY)
FROM EMPLOYEE_PAY_TBL;
A. False. The statement cannot return a total of the salaries by groups because there is no GROUP BY clause.
4. True or false: The columns selected must appear in the GROUP BY clause in the same order.
A. False. The order of the columns in the SELECT clause can be in a different order in the GROUP BY clause.
5. True or false: The HAVING clause tells the GROUP BY which groups to include.
A. True.
Exercise Answers
4. Modify the query in Exercise 3 by ordering the results in descending order, from highest count to lowest.
A. SELECT CITY, COUNT(*)FROM EMPLOYEE_TBL
GROUP BY CITY
ORDER BY 2 DESC;
5. Write a query to list the average pay rate by position from the EMPLOYEE_PAY_TBL table.
A. SELECT POSITION, AVG(PAY_RATE)
FROM EMPLOYEE_PAY_TBL
GROUP BY POSITION;
6. Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL table where the average salary is greater than 20000.
A. SELECT POSITION, AVG(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY POSITION
HAVING AVG(SALARY)>20000;
Hour 11, “Restructuring the Appearance of Data”
Quiz Answers
1. Match the descriptions with the possible functions.
2. True or false: Using functions in a SELECT statement to restructure the appearance of data in output also affects the way the data is stored in the database.
A. False.
3. True or false: The outermost function is always resolved first when functions are embedded within other functions in a query.
A. False. The innermost function is always resolved first when embedding functions within one another.
Exercise Answers
3. Write an SQL statement that lists employee email addresses. Email is not a stored column. The email address for each employee should be as follows:
FIRST.LAST @PERPTECH.COM
For example, John Smith’s email address is [email protected].
A. SELECT CONCAT(FIRST_NAME, '.', LAST_NAME, '@PERPTECH.COM')
FROM EMPLOYEE_TBL;
4. Write an SQL statement that lists each employee’s name and phone number in the following formats:
a. The name should be displayed as SMITH, JOHN.
b. The employee ID should be displayed as 999-99-9999.
c. The phone number should be displayed as (999)999-9999.
A. SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME),EMP_ID,
CONCAT('(',SUBSTRING(PHONE,1,3),')',SUBSTRING(PHONE,4,3),'-',
SUBSTRING(PHONE,7,4))
FROM EMPLOYEE_TBL;
Hour 12, “Understanding Dates and Times”
Quiz Answers
1. From where is the system date and time normally derived?
A. The system date and time are derived from the current date and time of the operating system on the host machine.
2. What are the standard internal elements of a DATETIME value?
A. YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
3. What could be a major factor concerning the representation and comparison of date and time values if your company is an international organization?
A. The awareness of time zones might be a concern.
4. Can a character string date value be compared to a date value defined as a valid DATETIME data type?
A. A DATETIME data type cannot be accurately compared to a date value defined as a character string. The character string must first be converted to the DATETIME data type.
5. What would you use in SQL Server, MySQL, and Oracle to get the current date and time?
A. NOW()
Exercise Answers
6. On what day of the week was each employee hired?
SELECT EMP_ID, DAYNAME(DATE_HIRE)
FROM EMPLOYEE_PAY_TBL;
A. Use the following statement to find the answer:
7. What is today’s Julian date (day of year)?
A. Use the following statement to find the answer:
SELECT DAYOFYEAR(CURRENT_DATE);
Hour 13, “Joining Tables in Queries”
Quiz Answers
1. What type of join would you use to return records from one table, regardless of the existence of associated records in the related table?
A. You would use an outer join.
2. The JOIN conditions are located in what part of the SQL statement?
A. The JOIN conditions are located in the WHERE clause.
3. What type of JOIN do you use to evaluate equality among rows of related tables?
A. You would use an equijoin.
4. What happens if you select from two different tables but fail to join the tables?
A. You receive a Cartesian product by not joining the tables (this is also called a cross join).
ORDERS_TBL
ORD_NUM VARCHAR2(10) NOT NULL primary key
CUST_ID VARCHAR2(10) NOT NULL
PROD_ID VARCHAR2(10) NOT NULL
QTY INTEGER NOT NULL
ORD_DATE DATE
PRODUCTS_TBL
PROD_ID VARCHAR2(10) NOT NULL primary key
PROD_DESC VARCHAR2(40) NOT NULL
COST DECIMAL(,2) NOT NULL
Is the following syntax correct for using an outer join?
SELECT C.CUST_ID, C.CUST_NAME, O.ORD_NUM
FROM CUSTOMER_TBL C, ORDERS_TBL O
WHERE C.CUST_ID(+) = O.CUST_ID(+)
A. No, the syntax is not correct. The (+) operator should only follow the O.CUST_ID column in the WHERE clause. The correct syntax is
SELECT C.CUST_ID, C.CUST_NAME, O.ORD_NUM
FROM CUSTOMER_TBL C, ORDERS_TBL O
WHERE C.CUST_ID = O.CUST_ID(+)
What would the query look like if you used the verbose JOIN syntax?
SELECT C.CUST_ID, C.CUST_NAME, O.ORD_NUM
FROM CUSTOMER_TBL C LEFT OUTER JOIN ORDERS_TBL O
ON C.CUST_ID = O.CUST_ID
Exercise Answers
3. Rewrite the SQL query from Exercise 2 using the INNER JOIN syntax.
A. SELECT E.LAST_NAME, E.FIRST_NAME, EP.DATE_HIRE
FROM EMPLOYEE_TBL E INNER JOIN
EMPLOYEE_PAY_TBL EP ON
E.EMP_ID = EP.EMP_ID;
4. Write an SQL statement to return the EMP_ID, LAST_NAME, and FIRST_NAME columns from EMPLOYEE_TBL and SALARY and BONUS columns from EMPLOYEE_PAY_TBL. Use both types of join techniques. Once that’s completed, use the queries to determine what the average employee salary per city is.
A. SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY, EP.BONUS
FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL EP
WHERE E.EMP_ID = EP.EMP_ID;
SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY, EP.BONUS
FROM EMPLOYEE_TBL E INNER JOIN
EMPLOYEE_PAY_TBL EP
ON E.EMP_ID = EP.EMP_ID;
SELECT E.CITY, AVG(EP.SALARY) AVG_SALARY
FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL EP
WHERE E.EMP_ID = EP.EMP_ID
GROUP BY E.CITY;
SELECT E.CITY, AVG(EP.SALARY) AVG_SALARY
FROM EMPLOYEE_TBL E INNER JOIN
EMPLOYEE_PAY_TBL EP
ON E.EMP_ID = EP.EMP_ID
GROUP BY E.CITY;
Hour 14, “Using Subqueries to Define Unknown Data”
Quiz Answers
1. What is the function of a subquery when used with a SELECT statement?
A. The main function of a subquery when used with a SELECT statement is to return data that the main query can use to resolve the query.
2. Can you update more than one column when using the UPDATE statement in conjunction with a subquery?
A. Yes, you can update more than one column using the same UPDATE and subquery statement.
3. Do the following have the correct syntax? If not, what is the correct syntax?
a. SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = '16C17'),
A. Yes, this syntax is correct.
b. SELECT EMP_ID, SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY BETWEEN '20000'
AND (SELECT SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY = '40000'),
A. No. You cannot use the BETWEEN operator in this format.
c. UPDATE PRODUCTS_TBL
SET COST = 1.15
WHERE PROD_ID =
(SELECT PROD_ID
FROM ORDERS_TBL
WHERE ORD_NUM = '32A132'),
A. Yes, this syntax is correct.
4. What would happen if you ran the following statement?
DELETE FROM EMPLOYEE_TBL
WHERE EMP_ID IN
(SELECT EMP_ID
FROM EMPLOYEE_PAY_TBL);
A. All rows that you retrieved from the EMPLOYEE_PAY_TBL would be deleted from the EMPLOYEE_TBL. A WHERE clause in the subquery is highly advised.
Exercise Answers
2. Using a subquery, write an SQL statement to update CUSTOMER_TBL. Find the customer with the order number 23E934, contained in the field ORD_NUM, and change the customer name to DAVIDS MARKET.
A. UPDATE CUSTOMER_TBL
SET CUST_NAME = 'DAVIDS MARKET'
WHERE CUST_ID =
(SELECT CUST_ID
FROM ORDERS_TBL
WHERE ORD_NUM = '23E934'),
3. Using a subquery, write a query that returns the names of all employees who have a pay rate greater than JOHN DOE, whose employee identification number is 343559876.
A. SELECT E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME
FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL P
WHERE P.PAY_RATE > (SELECT PAY_RATE
FROM EMPLOYEE_PAY_TBL
WHERE EMP_ID = '343559876'),
4. Using a subquery, write a query that lists all products that cost more than the average cost of all products.
A. SELECT PROD_DESC
FROM PRODUCTS_TBL
WHERE COST > (SELECT AVG(COST)
FROM PRODUCTS_TBL);
Hour 15, “Combining Multiple Queries into One”
Quiz Answers
Refer to the syntax covered in this hour for the following quiz questions when referring to the INTERSECT and EXCEPT operators. Remember that MySQL does not currently support these two operators.
1. Is the syntax correct for the following compound queries? If not, what would correct the syntax? Use EMPLOYEE_TBL and EMPLOYEE_PAY_TBL as follows:
EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE VARCHAR(10),
PAGER VARCHAR(10),
EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL, primary key
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LASTRAISE DATE,
SALARY DECIMAL(8,2),
BONUS DECIMAL(6,2),
a. SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
UNION
SELECT EMP_ID, POSITION, DATE_HIRE
FROM EMPLOYEE_PAY_TBL;
A. This compound query does not work because the data types do not match. The EMP_ID columns match, but the LAST_NAME and FIRST_NAME data types do not match the POSITION and DATE_HIRE data types.
b. SELECT EMP_ID FROM EMPLOYEE_TBL
UNION ALL
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID;
A. Yes, the statement is correct.
c. SELECT EMP_ID FROM EMPLOYEE_PAY_TBL
INTERSECT
SELECT EMP_ID FROM EMPLOYEE_TBL
ORDER BY 1;
A. Yes, this compound query works.
2. Match the correct operator to the following statements.
Exercise Answers
Refer to the syntax covered in this hour for the following exercises. You might have to write your queries by hand because MySQL does not support some of the operators covered in this hour. When you are finished, compare your results to ours.
Use CUSTOMER_TBL and ORDERS_TBL as listed:
CUSTOMER_TBL
CUST_IN VARCHAR(10) NOT NULL primary key
CUST_NAME VARCHAR(30) NOT NULL,
CUST_ADDRESS VARCHAR(20) NOT NULL,
CUST_CITY VARCHAR(15) NOT NULL,
CUST_STATE VARCHAR(2) NOT NULL,
CUST_ZIP INTEGER(5) NOT NULL,
CUST_PHONE INTEGER(10),
CUST_FAX INTEGER(10)
ORDERS_TBL
ORD_NUM VARCHAR(10) NOT NULL primary key
CUST_ID VARCHAR(10) NOT NULL,
PROD_ID VARCHAR(10) NOT NULL,
QTY INTEGER(6) NOT NULL,
ORD_DATE DATETIME
1. Write a compound query to find the customers who have placed an order.
A. SELECT CUST_ID FROM CUSTOMER_TBL
INTERSECT
SELECT CUST_ID FROM ORDERS_TBL;
2. Write a compound query to find the customers who have not placed an order.
A. SELECT CUST_ID FROM CUSTOMER_TBL
EXCEPT
SELECT CUST_ID FROM ORDERS_TBL;
Hour 16, “Using Indexes to Improve Performance”
Quiz Answers
1. What are some major disadvantages of using indexes?
A. Major disadvantages of an index include slowing batch jobs, storage space on the disk, and maintenance upkeep on the index.
2. Why is the order of columns in a composite important?
A. Because query performance is improved by putting the column with the most restrictive values first.
3. Should a column with a large percentage of NULL values be indexed?
A. No. A column with a large percentage of NULL values should not be indexed because the speed of accessing these rows degrades when the value of a large percentage of rows is the same.
4. Is the main purpose of an index to stop duplicate values in a table?
A. No. The main purpose of an index is to enhance data retrieval speed, although a unique index stops duplicate values in a table.
5. True or false: The main reason for a composite index is for aggregate function usage in an index.
A. False. The main reason for composite indexes is for two or more columns in the same table to be indexed.
6. What does cardinality refer to? What is considered a column of high-cardinality?
A. Cardinality refers to the uniqueness of the data within a column. The SSN column is an example of such a column.
Exercise Answers
1. For the following situations, decide whether an index should be used and, if so, what type of index should be used.
a. Several columns, but a rather small table.
A. Being a very small table, no index is needed.
b. Medium-sized table; no duplicates should be allowed.
A. A unique index could be used.
c. Several columns, very large table, several columns used as filters in the WHERE clause.
A. A composite index on the columns used as filters in the WHERE clause should be the choice.
d. Large table, many columns, a lot of data manipulation.
A. A choice of a single-column or composite index should be considered, depending on filtering, ordering, and grouping. For the large amount of data manipulation, the index could be dropped and re-created after the INSERT, UPDATE, or DELETE jobs were done.
3. Create a statement to alter the index you just created to make it unique. What do you need to do to create a unique index on the SALARY column? Write the SQL statements that you need to run them in the sequence.
A. DROP INDEX EP_POSITON ON EMPLOYEE_PAY_TBL;
CREATE UNIQUE INDEX EP_POSITION
ON EMPLOYEE_TBL(POSITION);
4. Study the tables used in this book. What are some good candidates for indexed columns based on how a user might search for data?
A. EMPLOYEE_TBL.LAST_NAME
EMPLOYEE_TBL.FIRST_NAME
EMPLOYEE_TBL.EMP_ID
EMPLOYEE_PAY_TBL.EMP_ID
EMPLOYEE_PAY_TBL.POSITION
CUSTOMER_TBL.CUST_ID
CUSTOMER_TBL.CUST_NAME
ORDERS_TBL.ORD_NUM
ORDERS_TBL.CUST_ID
ORDERS_TBL.PROD_ID
ORDERS_TBL.ORD_DATE
PRODUCTS_TBL.PROD_ID
PRODUCTS_TBL.PROD_DESC
5. Create a multicolumn index on ORDERS_TBL. Include the following columns: CUST_ID, PROD_ID, and ORD_DATE.
A. CREATE INDEX ORD_IDX ON ORDERS_TBL (CUST_ID, PROD_ID, ORD_DATE);
Hour 17, “Improving Database Performance”
Quiz Answers
1. Would the use of a unique index on a small table be of any benefit?
A. The index might not be of any use for performance issues, but the unique index would keep referential integrity intact. Referential integrity is discussed in Hour 3, “Managing Database Objects.”
2. What happens when the optimizer chooses not to use an index on a table when a query has been executed?
A. A full table scan occurs.
3. Should the most restrictive clause(s) be placed before the join condition(s) or after the join conditions in the WHERE clause?
A. The most restrictive clause(s) should be evaluated before the join condition(s) because join conditions normally return a large number of rows.
Exercise Answers
1. Rewrite the following SQL statements to improve their performance. Use EMPLOYEE_TBL and EMPLOYEE_PAY_TBL as described here:
EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL Primary key
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP INTEGER(5) NOT NULL,
PHONE VARCHAR(10),
PAGER VARCHAR(10),
EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL primary key
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
DATE_LAST_RAISE DATETIME,
SALARY DECIMAL(8,2),
BONUS DECIMAL(8,2),
a. SELECT EMP_ID, LAST_NAME, FIRST_NAME,
PHONE
FROM EMPLOYEE_TBL
WHERE SUBSTRING(PHONE, 1, 3) = '317' OR
SUBSTRING(PHONE, 1, 3) = '812' OR
SUBSTRING(PHONE, 1, 3) = '765';
A. SELECT EMP_ID, LAST_NAME, FIRST_NAME,
PHONE
FROM EMPLOYEE_TBL
WHERE SUBSTRING(PHONE, 1, 3) IN ('317', '812', '765'),
From our experience, it is better to convert multiple OR conditions to an IN list.
b. SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE LAST_NAME LIKE '%ALL%';
A. SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE LAST_NAME LIKE 'WAL%';
You cannot take advantage of an index if you do not include the first character in a condition’s value.
c. SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME,
EP.SALARY
FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL EP
WHERE LAST_NAME LIKE 'S%'
AND E.EMP_ID = EP.EMP_ID;
A. SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME,
EP.SALARY
FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL EP
WHERE E.EMP_ID = EP.EMP_ID
AND LAST_NAME LIKE 'S%';
2. Add another table called EMPLOYEE_PAYHIST_TBL that contains a large amount of pay history data. Use the table that follows to write the series of SQL statements to address the following problems.
EMPLOYEE_PAYHIST_TBL
PAYHIST_ID VARCHAR(9) NOT NULL primary key,
EMP_ID VARCHAR(9) NOT NULL,
START_DATE DATETIME NOT NULL,
END_DATE DATETIME,
PAY_RATE DECIMAL(4,2) NOT NULL,
SALARY DECIMAL(8,2) NOT NULL,
BONUS DECIMAL(8,2) NOT NULL,
CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID)
REFERENCES EMPLOYEE_TBL (EMP_ID)
What steps did you take to ensure that the queries you wrote perform well?
a. Find the SUM of the salaried versus nonsalaried employees by the year in which their pay started.
A. SELECT START_YEAR,SUM(SALARIED) AS SALARIED,SUM(HOURLY) AS
HOURLY
FROM
(SELECT YEAR(E.START_DATE) AS START_YEAR,COUNT(E.EMP_ID) AS SALARIED,0 AS HOURLY
FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN
( SELECT MIN(START_DATE) START_DATE,EMP_ID
FROM EMPLOYEE_PAYHIST_TBL
GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND
E.START_DATE=F.START_DATE
WHERE E.SALARY > 0.00
GROUP BY YEAR(E.START_DATE)
UNION
SELECT YEAR(E.START_DATE) AS START_YEAR,0 AS SALARIED,
COUNT(E.EMP_ID) AS HOURLY
FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN
( SELECT MIN(START_DATE) START_DATE,EMP_ID
FROM EMPLOYEE_PAYHIST_TBL
GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND
E.START_DATE=F.START_DATE
WHERE E.PAY_RATE > 0.00
GROUP BY YEAR(E.START_DATE)
) A
GROUP BY START_YEAR
ORDER BY START_YEAR
b. Find the difference in the yearly pay of salaried employees versus nonsalaried employees by the year in which their pay started. Consider the nonsalaried employees to be working full time during the year (PAY_RATE * 52 * 40).
A. SELECT START_YEAR,SALARIED AS SALARIED,HOURLY AS HOURLY,
(SALARIED - HOURLY) AS PAY_DIFFERENCE
FROM
(SELECT YEAR(E.START_DATE) AS START_YEAR,AVG(E.SALARY) AS
SALARIED,
0 AS HOURLY
FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN
( SELECT MIN(START_DATE) START_DATE,EMP_ID
FROM EMPLOYEE_PAYHIST_TBL
GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND
E.START_DATE=F.START_DATE
WHERE E.SALARY > 0.00
GROUP BY YEAR(E.START_DATE)
UNION
SELECT YEAR(E.START_DATE) AS START_YEAR,0 AS SALARIED,
AVG(E.PAY_RATE * 52 * 40 ) AS HOURLY
FROM EMPLOYEE_PAYHIST_TBL E INNER JOIN
( SELECT MIN(START_DATE) START_DATE,EMP_ID
FROM EMPLOYEE_PAYHIST_TBL
GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND
E.START_DATE=F.START_DATE
WHERE E.PAY_RATE > 0.00
GROUP BY YEAR(E.START_DATE)
) A
GROUP BY START_YEAR
ORDER BY START_YEAR
c. Find the difference in what employees make now versus what they made when they started with the company. Again, consider the nonsalaried employees to be full-time. Also consider that the employees’ current pay is reflected in the EMPLOYEE_PAY_TBL as well as the EMPLOYEE_PAYHIST_TBL. In the pay history table, the current pay is reflected as a row with the END_DATE for pay equal to NULL.
A. SELECT CURRENTPAY.EMP_ID,STARTING_ANNUAL_PAY,CURRENT_
ANNUAL_PAY,
CURRENT_ANNUAL_PAY - STARTING_ANNUAL_PAY AS PAY_DIFFERENCE
FROM
(SELECT EMP_ID,(SALARY + (PAY_RATE * 52 * 40)) AS
CURRENT_ANNUAL_PAY
FROM EMPLOYEE_PAYHIST_TBL
WHERE END_DATE IS NULL) CURRENTPAY
INNER JOIN
(SELECT E.EMP_ID,(SALARY + (PAY_RATE * 52 * 40)) AS
STARTING_ANNUAL_PAY
FROM EMPLOYEE_PAYHIST_TBL E
( SELECT MIN(START_DATE) START_DATE,EMP_ID
FROM EMPLOYEE_PAYHIST_TBL
GROUP BY EMP_ID) F ON E.EMP_ID=F.EMP_ID AND
E.START_DATE=F.START_DATE
) STARTINGPAY ON
CURRENTPAY.EMP_ID = STARTINGPAY.EMP_ID
Hour 18, “Managing Database Users”
Quiz Answers
1. What command establishes a session?
A. The CONNECT TO statement establishes this.
2. Which option drops a schema that still contains database objects?
A. The CASCADE option allows the schema to be dropped if there are still objects under that schema.
3. Which command in MySQL creates a schema?
A. The CREATE SCHEMA command creates a schema.
4. Which statement removes a database privilege?
A. The REVOKE statement removes database privileges.
5. What command creates a grouping or collection of tables, views, and privileges?
A. The CREATE SCHEMA statement.
6. What is the difference in SQL Server between a login account and a database user account?
A. The login account grants access to the SQL Server instance to log in and access resources. The database user account is what gains access to the database and is assigned rights.
Exercise Answers
1. Describe how you would create a new user 'John' in your learnsql database.
A. USE LEARNSQL:
CREATE USER JOHN
2. How would you grant access to the EMPLOYEE_TBL to your new user 'John'?
A. GRANT SELECT ON TABLE EMPLOYEE_TBL TO JOHN;
3. Describe how you would assign permissions to all objects within the learnsql database to 'John'.
A. GRANT SELECT ON TABLE * TO JOHN;
4. Describe how you would revoke the previous privileges from 'John' and then remove his account.
A. DROP USER JOHN CASCADE;
Hour 19, “Managing Database Security”
Quiz Answers
1. What option must a user have to grant another user privileges to an object not owned by the user?
A. GRANT OPTION
2. When privileges are granted to PUBLIC, do all database users acquire the privileges, or only specified users?
A. All users of the database are granted the privileges.
3. What privilege is required to look at data in a specific table?
A. The SELECT privilege.
4. What type of privilege is SELECT?
A. An object-level privilege.
5. What option revokes a user’s privilege to an object as well as the other users that they might have granted privileges to by use of the GRANT option?
A. The CASCADE option is used with the REVOKE statement to remove other users’ access that was granted by the affected user.
Exercise Answers
Hour 20, “Creating and Using Views and Synonyms”
Quiz Answers
1. Can you delete a row of data from a view that you created from multiple tables?
A. No. You can only use the DELETE, INSERT, and UPDATE commands on views you create from a single table.
2. When creating a table, the owner is automatically granted the appropriate privileges on that table. Is this true when creating a view?
A. Yes. The owner of a view is automatically granted the appropriate privileges on the view.
3. Which clause orders data when creating a view?
A. The GROUP BY clause functions in a view much as the ORDER BY clause (or GROUP BY clause) does in a regular query.
4. Which option can you use when creating a view from a view to check integrity constraints?
A. You can use the WITH CHECK OPTION.
5. You try to drop a view and receive an error because of one or more underlying views. What must you do to drop the view?
A. Re-execute your DROP statement with the CASCADE option. This allows the DROP statement to succeed by also dropping all underlying views.
Exercise Answers
1. Write a statement to create a view based on the total contents of EMPLOYEE_TBL.
A. CREATE VIEW EMP_VIEW AS
SELECT * FROM EMPLOYEE_TBL;
2. Write a statement that creates a summarized view containing the average pay rate and average salary for each city in EMPLOYEE_TBL.
A. CREATE VIEW AVG_PAY_VIEW AS
SELECT E.CITY, AVG(P.PAY_RATE), AVG(P.SALARY)
FROM EMPLOYEE_PAY_TBL P,
EMPLOYEE_TBL E
WHERE P.EMP_ID = E.EMP_ID
GROUP BY E.CITY;
3. Create another view for the same summarized data except use the view you created in Exercise 1 instead of the base EMPLOYEE_TBL. Compare the two results.
A. CREATE VIEW AVG_PAY_ALT_VIEW AS
SELECT E.CITY, AVG(P.PAY_RATE), AVG(P.SALARY)
FROM EMPLOYEE_PAY_TBL P,
EMP_VIEW E
WHERE P.EMP_ID = E.EMP_ID
GROUP BY E.CITY;
4. Use the view in Exercise 2 to create a table called EMPLOYEE_PAY_SUMMARIZED. Verify that the view and the table contain the same data.
A. SELECT * INTO EMPLOYEE_PAY_SUMMARIZED FROM AVG_PAY_VIEW;
5. Write statements that drop the three views that you created in Exercises 1, 2, and 3.
A. DROP VIEW EMP_VIEW;
DROP VIEW AVG_PAY_VIEW;
DROP VIEW AVG_PAY_ALT_VIEW;
Hour 21, “Working with the System Catalog”
Quiz Answers
1. In some implementations, the system catalog is also known as what?
A. The system catalog is also known as the data dictionary.
2. Can a regular user update the system catalog?
A. Not directly; however, when a user creates an object such as a table, the system catalog is automatically updated.
3. Which Microsoft SQL Server system table retrieves information about views that exist in the database?
A. SYSVIEWS is used.
4. Who owns the system catalog?
A. The owner of the system catalog is often a privileged database user account called SYS or SYSTEM. The owner of the database can also own the system catalog, but a particular schema in the database does not ordinarily own it.
5. What is the difference between the Oracle system objects ALL_TABLES and DBA_TABLES?
A. ALL_TABLES shows all tables that are accessible by a particular user, whereas DBA_TABLES shows all tables that exist in the database.
6. Who makes modifications to the system tables?
A. The database server makes these modifications.
Exercise Answers
Hour 22, “Advanced SQL Topics”
Quiz Answers
A. No, the trigger must be replaced or re-created.
2. When a cursor is closed, can you reuse the name?
A. This is implementation specific. In some implementations, the closing of the cursor enables you to reuse the name and even free the memory, whereas for other implementations you must use the DEALLOCATE statement before you can reuse the name.
3. Which command retrieves the results after a cursor has been opened?
A. The FETCH command does this.
4. Are triggers executed before or after an INSERT, DELETE, or UPDATE statement?
A. Triggers can be executed before or after an INSERT, DELETE, or UPDATE statement. Many different types of triggers can be created.
5. Which MySQL function retrieves information from an XML fragment?
A. EXTRACTVALUE is used.
6. Why do Oracle and MySQL not support the DEALLOCATE syntax for cursors?
A. They do not support the statement because they automatically deallocate the cursor resources when the cursor is closed.
7. Why is a cursor not considered a set-based operation?
A. Cursors are not considered set-based operations because they operate on only one row at a time by fetching a row from memory and performing some action with it.
Exercise Answers
2. Write a SELECT statement that generates the SQL code to count all rows in each of your tables. (Hint: It is similar to Exercise 1.)
A. SELECT CONCAT('SELECT COUNT(*) FROM ',TABLE_NAME,';') FROM TABLES;
3. Write a series of SQL commands to create a cursor that prints each customer name and the customer’s total sales. Ensure that the cursor is properly closed and deallocated based on which implementation you are using.
A. An example using SQL Server might look similar to this:
BEGIN
DECLARE @custname VARCHAR(30);
DECLARE @purchases decimal(6,2);
DECLARE customercursor CURSOR FOR SELECT
C.CUST_NAME,SUM(P.COST*O.QTY) as SALES
FROM CUSTOMER_TBL C
INNER JOIN ORDERS_TBL O ON C.CUST_ID=O.CUST_ID
INNER JOIN PRODUCTS_TBL P ON O.PROD_ID=P.PROD_ID
GROUP BY C.CUST_NAME;
OPEN customercursor;
FETCH NEXT FROM customercursor INTO @custname,@purchases
WHILE (@@FETCH_STATUS<>-1)
BEGIN
IF (@@FETCH_STATUS<>-2)
BEGIN
PRINT @custname + ': $' + CAST(@purchases AS
VARCHAR(20))
END
FETCH NEXT FROM customercursor INTO @custname,@purchases
END
CLOSE customercursor
DEALLOCATE customercursor
END;
Hour 23, “Extending SQL to the Enterprise, the Internet, and the Intranet”
Quiz Answers
1. Can a database on a server be accessed from another server?
A. Yes, by using a middleware product. This is called accessing a remote database.
2. What can a company use to disseminate information to its own employees?
A. An intranet.
3. Products that allow connections to databases are called what?
A. Middleware.
4. Can SQL be embedded into Internet programming languages?
A. Yes. SQL can be embedded in Internet programming languages, such as Java.
5. How is a remote database accessed through a web application?
A. Via a web server.
Exercise Answers
Hour 24, “Extensions to Standard SQL”
Quiz Answers
1. Is SQL a procedural or nonprocedural language?
A. SQL is nonprocedural, meaning that the database decides how to execute the SQL statement. The extensions discussed in this hour were procedural.
2. What are the three basic operations of a cursor, outside of declaring the cursor?
A. OPEN, FETCH, and CLOSE.
3. Procedural or nonprocedural: With which does the database engine decide how to evaluate and execute SQL statements?
A. Nonprocedural.