Deck 4: Single Table Queries

Question 1

Preceding a condition with which SQL operator reverses the truth of the original condition?

A) REVERSE
B) NOT
C) NULL
D) OR

Answer 1

B

Question 2

Which operator is inclusive, meaning that a query selects a value equal to either value in the condition along with those in the range of the values?

A) AND
B) BETWEEN
C) OR
D) NOT

Answer 2

B

Question 3

The GROUP BY clause sorts the data in a particular order.

Answer 3

False

Question 4

Which function calculates a total of the values in a column?

A) TOTAL
B) CALCULATE
C) SUM
D) ADD

Question 5

Which operator uses one or more wildcard characters to test for a pattern match?

A) PATTERN
B) LIKE
C) MATCH
D) SIMILAR

Question 6

Which of the following operators can be used for a column computation?

A) +
B) ^
C) **
D) +, ^, and **

Question 7

All SELECT statements require a WHERE clause.

Question 8

When you need to sort data on two columns, the less important column is called the _____.

A) double sort key
B) primary sort key
C) minor sort key
D) foreign sort key

Question 9

It is possible to place one query inside another.

Question 10

The basic form of the SQL SELECT command is _____.

A) FROM-SELECT-WHERE
B) SELECT-FROM-WHERE
C) FROM-WHERE-SELECT
D) WHERE-SELECT-FROM

Question 11

In MySQL, you can use an asterisk (*) to represent any column when using the _____.

A) SUM function
B) COUNT function
C) MAX function
D) MIN function

Question 12

What clause can you use to list data in a specific order?

A) ORDER
B) SET ORDER
C) ORDER BY
D) LIST BY

Question 13

It is possible to use both a WHERE clause and a HAVING clause in a SELECT statement.

Question 14

The only arithmetic operators that can be used in SELECT statements are + for addition and - for subtraction.

Question 15

Which function determines the number of rows in a table?

A) ROW
B) CALCULATE
C) COUNT
D) NUMBER

Question 16

In the SELECT clause, you can indicate that you want to include all columns using the _____.

A) \ symbol
B) / symbol
C) * symbol
D) ? symbol

Question 17

Which clause lets you group data on a particular column?

A) GROUP
B) GROUP BY
C) SET GROUP
D) GROUPING

Question 18

What SQL command do you use to query a database?

A) WHERE
B) SELECT
C) FROM
D) SET

Question 19

Mallory would like to display the rows from a MySQL table that have "Red," "Pink," or "White" in the COLOR column. A concise way of writing her query would be to use a(n) _____.

A) LIKE clause
B) CONTAINS clause
C) BETWEEN clause
D) IN clause

Question 20

You form compound conditions by connecting two or more simple conditions using _____.

A) multiple SELECT statements
B) WHERE clauses
C) subqueries
D) AND, OR, and NOT operators

Question 21

When you need to find rows with null values in a MySQL table, you can use the _____.

A) SELECT NULL command
B) GROUP BY NULL condition
C) = NULL condition
D) IS NULL operator

Question 22

Bradford would like to write a query that returns the average unit sales over the last 30 days for each of his bakery's product types: cupcakes, muffins, and cookies. There are several flavors of each in the table, but the product types are identified in a CATEGORY column as "Cupcake," "Muffin," or "Cookie." What should the last part of his query be, following the part shown below? ​
SELECT CATEGORY, AVG(UNIT_SALES) FROM PRODUCTS

A) ORDER BY CATEGORY;
B) GROUP BY CATEGORY;
C) BETWEEN ('Cupcake', 'Muffin', 'Cookie');
D) WHERE CATEGORY IN ('Cupcake', 'Muffin', 'Cookie');

Question 23

Adding a ORDER BY UNIT_PRICE clause to a SQL query will _____.

A) display the results in ascending order by UNIT_PRICE
B) set UNIT_PRICE as the primary key for the table being queried
C) display the results in descending order by UNIT_PRICE
D) display UNIT_PRICE as the first column in the results

Question 24

What is the appropriate sequence for the elements in a SQL query clause that displays the rows returned in a particular order?

A) command, operator (if any), major sort key, minor sort key
B) primary sort key followed by operator (if any), secondary sort key followed by operator (if any), command
C) command, major sort key followed by operator (if any), minor sort key followed by operator (if any)
D) command, secondary sort key followed by operator (if any), primary sort key followed by operator (if any)

Question 25

What does the following SQL query do? SELECT INVOICE_NUM FROM INVOICE_LINE WHERE PROD_CODE IN (SELECT PROD_CODE FROM PRODUCTS WHERE (PROD_TYPE = 'Cupcake'));

A) cause an error because it includes more than one SELECT command
B) lists all rows from the invoice line table that contain a "Cupcake" type product
C) lists the invoice numbers for invoices containing line items with a "Cupcake" product type
D) lists the product codes for all "Cupcake" type products that appear on invoices

Question 26

Imagine you are working with a MySQL table called LIBRARY_MEMBERS that includes the following columns: MEMBER_ID, MOBILE_NUMBER, MAX_BOOKS (the maximum number of books that member can check out at one time), and BOOKS_CHECKED_OUT (the number of books currently checked out). Write a SQL query that will display a grid including the MEMBER_ID for all rows plus a column called CAN_CHECK_OUT that indicates how many more books that library member must check out to reach his or her maximum.

Question 27

Which operator finds rows that do not contain a null value in the specified column?

A) IS NULL
B) NOT NULL
C) !NULL
D) IS NOT NULL

Question 28

When a subquery is used in a SQL statement _____.

A) the subquery is evaluated first
B) a temporary table is created and displayed
C) the outer query is evaluated first
D) only the subquery can include a WHERE clause

Question 29

How do you construct the SELECT query?

Question 30

Describe simple and compound conditions.

Question 31

Imagine that you are working with a MySQL table called ADDRESS_BOOK with the following columns: FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIP_CODE, and BIRTHDAY. Write queries to (1) list all rows missing the ZIP code value and (2) list all rows that include a birthday value.

Question 32

When a SQL query is written so as to display the results in a specific order, this order is determined by the _____.

A) operator
B) sort key
C) primary key
D) WHERE clause

Question 33

Which clause is used to restrict the groups that will be included in a query result?

A) LIKE
B) HAVING
C) WHERE
D) HAVE

Question 34

Compare the WHERE and HAVING clauses.

Question 35

Which MySQL clause used with the query SELECT* FROM PACKAGE would be the most likely to yield the following results?

A) WHERE PACKAGE_NAME DESC, PRICE
B) WHERE PRICE DESC, PACKAGE_NAME
C) ORDER BY PACKAGE_NAME, PRICE DESC
D) ORDER BY PRICE DESC, PACKAGE_NAME