Question 1

The DATE_ADD function has one argument.

Accepted Answer

The DATE_ADD function in SQL requires at least two arguments: the starting date and the interval by which to add to the starting date.

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

Accepted Answer

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.

Accepted Answer

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

Accepted Answer

The "ALL" operator is used to compare a value to all values in another value set or returned by a subquery. A condition is true if it holds true for every value that the subquery returns.

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';

Accepted Answer

The correct SQL command to update a value in a column for a specific row is the UPDATE statement followed by the SET clause to specify the column and new value, and the WHERE clause to specify which row(s) to update.

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

Accepted Answer

The answer of Write a MySQL command to create a...

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

Accepted Answer

The underlined items in the database representation are primary keys, which uniquely identify each record in a table.

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))

Accepted Answer

C) PATIENT (PATIENT_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NO, DOC_ID) is in third normal form because it does not contain transitive dependencies or repeating groups, and every non-key attribute is functionally dependent on the primary key.

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

The DATE_ADD function has one argument.

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

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

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

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)

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