Deck 1: Efficiency Improvements for Creating Reports and Removing Rows in MySQL

Question 1

The DATE_ADD function has one argument.

Answer 1

False

Question 2

Henry uses a large database to generate reports and would like to make report delivery more efficient (faster). The reports derive from the following tables.
?
CANDY table, with these columns: PROD_ID, NAME, UNITS_SOLD_LAST_30DAYS
EMPLOYEE table, with these columns: EMP_ID, FIRST_NAME, LAST_NAME, OFFICE_PHONE
?
Write SQL commands to improve the efficiency of creating these two reports:
.Employee records alphabetized by last name, then first name
.Names of candy products listed by units sold in the last 30 days, in descending order

Answer 2

CREATE INDEX EMPLOYEE_NAME_IND ON EMPLOYEE (LAST_NAME, FIRST_NAME);
?
CREATE INDEX CANDY_SALES_IND ON CANDY(UNITS_SOLD_LAST_30DAYS DESC)

Question 3

Imagine you have created a table called BOXES in MySQL to store data about various sizes of cardboard boxes that your organization sells. Its columns include PRODUCT_ID, NAME, DESCRIPTION, HEIGHT_IN, WIDTH_IN, and DEPTH_IN. Write a SQL command to remove any rows describing boxes with a height of 24 inches from the table.

Answer 3

DELETE FROM BOXES
WHERE (HEIGHT_IN = 24);

Question 4

A condition is true only if it satisfies all values produced by a subquery when that subquery is preceded by which operator?

A) TRUE
B) ALL
C) IS ALL
D) ALWAYS

Question 5

Which of the following SQL commands changes the value in the column LAST_NAME within the row containing a REP_ID of 007?

A) UPDATE SALES_REP SET LAST_NAME = 'Bond' WHERE REP_ID = '007';
B) CHANGE SALES_REP LAST_NAME = 'Bond' WHERE REP_ID = '007';
C) REPLACE SALES_REP LAST_NAME = 'Bond' WHERE REP_ID = '007';
D) INSERT SALES_REP SET LAST_NAME = 'Bond' WHERE REP_ID = '007';

Question 6

Write a MySQL command to create a table called CANDY that contains the following columns:
.Candy ID, five characters; will function as primary key
.Candy name, 25 characters
.Description, 50 characters
.Quantity per unit, whole number

Question 7

In the following shorthand representation of a database, the underlined items are _____.
INVOICES (INVOICE_NUM, INVOICE_DATE, CUST_ID)
INVOICE_LINE (INVOICE_NUM, ITEM_ID, QUANTITY, QUOTED_PRICE)
ITEM (ITEM_ID, DESCRIPTION, ON_HAND, CATEGORY, LOCATION, PRICE)

A) relations
B) entities
C) nonkey columns
D) primary keys

Question 8

Which of the following tables is in third normal form, given that DOC_ID determines DOC_FIRST_NAME and DOC_LAST_NAME?

A) PATIENT (PATIENT_ID, FIRST_NAME, LAST_NAME, DOC_ID, DOC_FIRST_NAME, DOC_LAST_NAME)
B) PATIENT (PATIENT_ID, FIRST_NAME, LAST_NAME, VISIT_DATE, (SERVICE, BILLING_CODE))
C) PATIENT (PATIENT_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NO, DOC_ID)
D) PATIENT (PATIENT_ID, DOC_ID, FIRST_NAME, LAST_NAME, (ADMIT_DATE, RELEASE_DATE))