SQL Final
Expressions coded in the HAVING clause A) can use either aggregate search conditions or non-aggregate search conditions B) can use aggregate search conditions but can't use non-aggregate search conditions C) can use non-aggregate search conditions but can't use aggregate search conditions D) can refer to any column in the base tables
A) can use either aggregate search conditions or non-aggregate search conditions
To join each row from the first table with each row from the second table, you use a A) cross join B) natural join C) left join D) right join
A) cross join
When coded in a WHERE clause, which of the following would not return rows for vendors in the cities of San Diego and Santa Ana? A) vendor_city LIKE 'SAN%' B) vendor_city REGEXP 'NA$' C) vendor_city REGEXP '^SA'
B) vendor_city REGEXP 'NA$'
Which clause do you use to filter the rows returned by a SELECT statement? A) SELECT B) FROM C) WHERE D) ORDER BY
C) WHERE
Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1 ORDER BY vendor_state A) The names of the vendors in each state B) The duplicate vendors from each state C) The number of vendors in each state D) The number of vendors in each state that has more than one vendor
D) The number of vendors in each state that has more than one vendor
When you code a union that combines two result sets, which of the following is not true? A) Each result set must have the same number of columns. B) The result sets may or may not have any duplicate rows. C) The corresponding columns in the result sets must have compatible data types. D) The result sets must be derived from different tables.
D) The result sets must be derived from different tables.
Which of the following can you not code in a SELECT clause when you omit the FROM clause from a SELECT statement? A) a literal value B) an arithmetic expression C) a function D) a base table column
D) a base table column
Which of the following SELECT clauses could you use to assign an alias to the invoice_total column? (Choose all applied) A) SELECT invoice_number, invoice_date, invoice_total total B) SELECT invoice_number, invoice_date, invoice_total AS total C) SELECT invoice_number, invoice_date, invoice_total : total
A) SELECT invoice_number, invoice_date, invoice_total total and B) SELECT invoice_number, invoice_date, invoice_total AS total
Which of the following is not true about calculating a moving average for an aggregate window function? A) The frame must be defined using the ROWS clause. B) The moving average always includes the current row. C) The moving average for the first row in a partition can only include the value of the current row plus the values of any following rows. D) To calculate the average, you must use both the SUM and AVG functions.
A) The frame must be defined using the ROWS clause.
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery) A) a single value B) a list of values C) a table of values D) a subquery can't be introduced in this way
A) a single value
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) A) a single value B) a list of values C) a table of values D) a subquery can't be introduced in this way
A) a single value
You can simulate a full outer join by using A) a union B) the ON clause C) a left outer join D) a self join
A) a union
Which of the following expressions does not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total? A) invoice_total - credit_total - payment_total / 10 B) (invoice_total - payment_total - credit_total) / 10 C) (invoice_total - (payment_total + credit_total)) * 0.10 D) ((invoice_total - payment_total) - credit_total) / 10
A) invoice_total - credit_total - payment_total / 10
To join a table in one database to a table in another database, you must A) prefix the name of the table in the other database with the name of that database B) prefix the name of each table with the name of the database that contains it C) assign an alias to the table in the other database D) assign an alias to the table in each database
A) prefix the name of the table in the other database with the name of that database
You can use a common table expression (CTE) to create a temporary A) result set B) expression C) view D) none of the given options
A) result set
(Refer to code example 7-2.) When this query is executed, each row in the result table will show A) the largest invoice total related to that row B) the average invoice total related to that row C) the largest invoice total related to that row, but only if it's larger than the average for all invoices D) the average invoice total related to that row, but only if it's greater than 100
A) the largest invoice total related to that row
When the following query is executed, the result set will include one row for each invoice with an invoice total that's greater than SELECT vendor_name, invoice_number, invoice_total FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id WHERE invoice_total > ANY (SELECT invoice_total FROM invoices WHERE vendor_state = 'CA') A) the minimum invoice total for vendors in California B) the maximum invoice total for vendors in California C) the minimum invoice total for vendors in states other than California D) the maximum invoice total for vendors in states other than California
A) the minimum invoice total for vendors in California
SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id The total number of rows returned by this query must equal A) the number of rows in the Invoices table B) the number of rows in the Vendors table C) the number of rows in the Invoices table plus the number of rows in the Vendors table D) none of the given options
A) the number of rows in the Invoices table
Which of the following is a common reason for using the IF function with the GROUPING function? A) To replace the nulls that are generated by WITH ROLLUP with literal values. B) To replace the nulls in grouped columns with literal values. C) To add a summary row for each group only if the group has more than one row. D) To add a summary row for the result set only if the result set has more than one row.
A) to replace the nulls that are generated by WITH ROLLUP with literal values.
How would you code a SELECT clause so it returns all columns from the base table? A) SELECT ALL B) SELECT COLUMNS C) SELECT *.* D) SELECT *
D) SELECT *
Which of the statements below best describes the result set returned by this SELECT statement? SELECT COUNT(DISTINCT invoice_id) AS 'count', ROUND(AVG(line_item_amount), 2) AS average_amount FROM invoice_line_items A) A single row with a count of the rows in the invoice_line_items table and the average amount of all the line items B) A single row with a count of the invoices in the invoice_line_items table and the average amount of all the line items C) A row for each of the invoices in the invoice_line_items table with a count of the line items and the average amount of the line items for that invoice
B) A single row with a count of the invoices in the invoice_line_items table and the average amount of all the line items
Which of the following is not an advantage of using subqueries over using joins? A) You can use a subquery to pass an aggregate value to the main query. B) A subquery tends to be more intuitive when it uses an existing relationship between the two tables. C) Long, complex queries can sometimes be easier to code using subqueries
B) A subquery tends to be more intuitive when it uses an existing relationship between the two tables.
Which clause of the SELECT statement specifies the table that contains the data to be retrieved? A) SELECT B) FROM C) WHERE D) ORDER BY
B) FROM
Which of the following statements about a correlated subquery is not true? A) It is executed once for each row in the main query. B) It can't use the same table as the main query. C) It must refer to a value that's provided by a column in the main query. D) It can use a table name or alias to refer to a table in the main query.
B) It can't use the same table as the main query.
What does the GROUPING function do? A) It evaluates an expression and returns 0 if the expression is null because it's in a summary row. B) It evaluates an expression and returns 1 if the expression is null because it's in a summary row. C) It evaluates an expression and returns 0 if the expression is null because it's the result of grouping a column that can contain null values. D) It evaluates an expression and returns 1 if the expression is null because it's the result of grouping a column that can contain null values
B) It evaluates an expression and returns 1 if the expression is null because it's in a summary row.
Which of the following would return a maximum of 7 rows, starting with the 5th row? A) LIMIT 7, 4 B) LIMIT 4, 7 C) LIMIT 4 D) LIMIT 4, 6
B) LIMIT 4, 7
Which of the following SELECT statements would you use to prevent duplicate rows from being returned? A) SELECT vendor_id DISTINCT FROM invoices ORDER BY vendor_id B) SELECT DISTINCT vendor_id FROM invoices ORDER BY vendor_id C) SELECT vendor_id FROM DISTINCT invoices ORDER BY vendor_id D) SELECT vendor_id FROM invoices WHERE DISTINCT ORDER BY vendor_id
B) SELECT DISTINCT vendor_id FROM invoices ORDER BY vendor_id
(Please refer to code example 6-1.) Although this query runs as coded, it contains this logical error: A) The condition in the WHERE clause should be coded in the HAVING clause. B) The column name for the fifth column in the result set doesn't match the data. C) The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence. D) The condition in the HAVING clause should be coded in the WHERE clause.
B) The column name for the fifth column in the result set doesn't match the data.
Which of the following is not true about coding inner joins? A) The join condition typically compares the primary key of one table to a foreign key of another table. B) The join condition must consist of a single comparison. C) The join can be done between tables in different databases. D) The join can be done between the same table
B) The join condition must consist of a single comparison.
Which of the statements below best describes the result set returned by this SELECT statement? SELECT vendor_id, SUM(invoice_total - payment_total - credit_total) AS column_2 FROM invoices WHERE invoice_total - payment_total - credit_total > 0 GROUP BY vendor_id ORDER BY vendor_id A) The unpaid balance for each invoice B) The total unpaid balance due for each vendor_id C) The total amount invoiced by each vendor_id D) The total of paid invoices for each vendor_id
B) The total unpaid balance due for each vendor_id
Which of the following WHERE clauses will return vendor names from A to C? A) WHERE vendor_name = D B) WHERE vendor_name < 'D' C) WHERE vendor_name = 'D' D) WHERE vendor_name < 'C'
B) WHERE vendor_name < 'D'
If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery) A) a single value B) a list of values C) a table of values D) a subquery can't be introduced in this way
B) a list of values
If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery) A) a single value B) a list of values C) a table of values D) a subquery can't be introduced in this way
B) a list of values
When you code a calculated value in a SELECT clause, the expression for the calculated value can include A) arithmetic operators only B) arithmetic operators and functions only C) arithmetic operators, comparison operators, and functions only D) arithmetic operators, comparison operators, logical operators, and functions
B) arithmetic operators and functions only
(Please refer to code example 6-1.) When this summary query is executed, the result set will contain one summary row for A) each city with invoice totals over $500 B) each vendor with invoice totals over $500 C) each city with invoice average over $500 D) each vendor with invoice average over $500
B) each vendor with invoice totals over $500
(Refer to code example 7-2.) When this query is executed, there will be one row A) for each vendor B) for each vendor with an average invoice total that's greater than 100 C) for each vendor with a maximum invoice total that's greater than 100 D) for each invoice with an invoice total that's greater than the average invoice total for the vendor and also greater than 100
B) for each vendor with an average invoice total that's greater than 100
Which of the following statements is not true about a frame for an aggregate window function? A) It defines a subset of the current partition. B) If you don't specify the ending row for a frame, the ending row is the last row in the partition. C) It can be defined as the number of rows before and after the current row. D) It can be defined as a range of values based on the value of the current row.
B) if you don't specify the ending row for a frame, the ending row is the last row in the partition.
(Refer to code example 4-1.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal A) the number of rows in the Invoices table B) the number of rows in the Vendors table C) the number of rows in the Invoices table plus the number of rows in the Vendors table D) None of the given options
B) the number of rows in the Vendors table
Which of the following is not a valid column alias? A) total B) total sales C) total_sales D) "Total Sales"
B) total sales
A full outer join returns A) rows in the left table that don't satisfy the join condition B) unmatched rows from both the left and right tables C) rows in the right table that don't satisfy the join condition D) the Cartesian product of the two tables
B) unmatched rows from both the left and right tables
If you assign an alias to one table in a join, you have to A) assign them to all of the tables B) use that alias to refer to the table throughout your query C) qualify every column name in the query D) qualify all of the column names for that table
B) use that alias to refer to the table throughout your query
Which is not true about the USING keyword? A) you use it to simplify the syntax for joining tables B) you code a USING clause in addition to the ON clause C) it can be used with inner or outer joins D) the join must be an equijoin, meaning the equals operator is used to compare the two columns
B) you code a USING clause in addition to the ON clause
Which of the following statements about sorting the rows in a result set is not true? A) Null values always appear first in the sort sequence. B) The sort order is determined by the character set you're using. C) A column that's used for sorting must be included in the SELECT clause. D) The rows can be sorted by any number of columns in either ascending or descending sequence
C) A column that's used for sorting must be included in the SELECT clause.
You can code an expression that results in a date value for all but one of the following aggregate functions. Which one is it? A) MIN B) MAX C) AVG D) COUNT
C) AVG
Which of the following best describes when you should consider using a named window? A) Any time a SELECT statement includes two or more aggregate window functions. B) Any time a SELECT statement includes two or more aggregate window functions that use partitions. C) Any time a SELECT statement includes two or more aggregate window functions that use the same partitions. d) Any time you want to be able to add to the definitions of two or more aggregate window functions.
C) Any time a SELECT statement includes two or more aggregate window functions that use the same partitions.
To define peer rows for an aggregate window function, you must include all but one of the following clauses. Which one is it? A) PARTITION BY B) RANGE C) BETWEEN D) ORDER BY
C) BETWEEN
Which of the following keywords would you use to introduce a subquery if you want to determine if the subquery returns one or more rows? A) ANY B) SOME C) EXISTS D) ALL
C) EXISTS
To use a self join, you must do all but one of the following. Which one is it? A) assign aliases to the tables B) qualify each column name with an alias C) Include the DISTINCT keyword
C) Include the DISTINCT keyword
All but one of the following is true about the WITH ROLLUP operator. Which one is it? A) It adds a summary row for each group specified in the GROUP BY clause. B) It adds a summary row to the end of the result set. C) It is part of standard SQL. D) It is coded on the GROUP BY clause.
C) It is part of standard sql
The six clauses of the SELECT statement must be coded in the following order: A) SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY B) SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING C) SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY D) SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING
C) SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
(Refer to code example 6-2.) What rows make up the partitions for the second SUM function? A) Each row is treated as a separate partition. B) The rows for each invoice date are treated as a separate partition. C) The rows for each vendor are treated as a separate partition. D) All of the rows are treated as a single partition.
C) The rows for each vendor are treated as a separate partition.
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) A) a single value B) a list of values C) a table of values D) a subquery can't be introduced in this way
C) a table of values
When you use the implicit syntax for coding inner joins, the join conditions A) are coded on the ON clause B) are coded on the USING clause C) are coded on the WHERE clause D) aren't coded
C) are coded on the WHERE clause
Which of the following can you not use in a join condition? A) logical operators B) comparison operators C) arithmetic operators
C) arithmetic operators
(Refer to code example 7-1.) When this query is executed, the rows will be sorted by A) invoice_id B) vendor_id C) balance_due in descending sequence D) vendor_id and then by balance_due in descending sequence
C) balance_due in descending sequence
Expressions coded in the WHERE clause A) can use either aggregate search conditions or non-aggregate search conditions B) can use aggregate search conditions but can't use non-aggregate search conditions C) can use non-aggregate search conditions but can't use aggregate search conditions D) must refer to columns in the SELECT clause
C) can use non-aggregate search conditions but can't use aggregate search conditions
When a SELECT statement includes a GROUP BY clause, the SELECT clause can include all but one of the following. Which one is it? A) columns used for grouping B) columns that are functionally dependent on a column used for grouping C) columns that are not functionally dependent on a column used for grouping D) aggregate functions
C) columns that are not functionally dependent on a column used for grouping
When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an invoice_total value of $1000 or less? A) invoice_total <= 1000 B) NOT (invoice_total > 1000) C) invoice_total IN (0, 1000) D) invoice_total BETWEEN 0 AND 1000
C) invoice_total IN (0, 1000)
When you use the USING keyword for a join A) the join can't be an outer join B) the join can't be done on more than one column C) the join must be based on a column or columns that have the same name in both tables D) the join must be an inner join
C) the join must be based on a column or columns that have the same name in both tables
Which of the following statements about common table expressions (CTEs) is not true? A) A CTE consists of a SELECT statement. B) A CTE can be used with SELECT, INSERT, UPDATE, and DELETE statements. C) A CTE can refer to itself. D) A CTE can refer to any other CTE in the same WITH clause.
D) A CTE can refer to any other CTE in the same WITH clause.
(Refer to code example 6-2.) What rows make up the partitions for the first SUM function? A) Each row is treated as a separate partition. B) The rows for each invoice date are treated as a separate partition. C) The rows for each vendor are treated as a separate partition. D) All of the rows are treated as a single partition.
D) All of the rows are treated as a single partition.
In which of the following ways can you not use a subquery in a SELECT statement? A) In a SELECT clause as a column specification B) In a FROM clause as a table specification C) In a WHERE clause as part of a search condition D) In an ORDER BY clause as a column specification
D) In an ORDER BY clause as a column specification
(Refer to code example 6-2.) What rows make up the partitions for the second SUM function? A) It sorts all rows in the result set by the invoice_total column. B) It sorts all rows in the result set by the invoice_total column and causes the values in the invoice_total column to be accumulated from one row to the next. C) It sorts the rows for each vendor by the invoice_total column and causes the values in the vendor_id column for each vendor to be accumulated from one row to the next. D) It sorts the rows for each vendor by the invoice_total column and causes the values in the vendor_total column for each vendor to be accumulated from one row to the next.
D) It sorts the rows for each vendor by the invoice_total column and causes the values in the vendor_total column for each vendor to be accumulated from one row to the next.
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax A) is easier to read and understand B) lets you separate the join and search conditions C) is an older syntax that works with legacy code D) lets you combine inner and outer joins
D) Lets you combine the join and search conditions
When you code a SELECT statement, you must code the four main clauses in the following order A) SELECT, FROM, ORDER BY, WHERE B) SELECT, ORDER BY, FROM, WHERE C) SELECT, WHERE, ORDER BY, FROM D) SELECT, FROM, WHERE, ORDER BY
D) SELECT, FROM, WHERE, ORDER BY
A subquery can return A) a single value only B) a list of values only C) a single value or a list of values only D) a single value, a list of values, or a table of values
D) a single value, a list of values, or a table of values
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) A) a single value B) a list of values C) a table of values D) a subquery can't be introduced in this way
D) a subquery can't be introduced in this way
When you code an ORDER BY clause, you can specify a A) column name or alias only B) column name or expression only C) column name, alias, or expression only D) column name, alias, expression, or column number
D) column name, alias, expression, or column number
Unlike a join, a union A) is coded in the ORDER BY clause B) by default, does not eliminate duplicate rows C) combines columns from the same table D) combines the result sets of two or more SELECT statements
D) combines the result sets of two or more SELECT statements
(Refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number A) 1 B) of invoices in the Invoices table C) of invoices for each vendor D) of invoices for each vendor that has a larger balance due than the average balance due for all invoices
D) of invoices for each vendor that has a larger balance due than the average balance due for all invoices
(Refer to code example 7-1.) When this query is executed, the result set will contain A) one row for each invoice that has a larger balance due than the average balance due for all invoices B) one row for the invoice with the largest balance due for each vendor C) one row for each invoice for each vendor that has a larger balance due than the average balance due for all invoices D) one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if the balance due is larger than the average balance due for all invoices
D) one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if the balance due is larger than the average balance due for all invoices
When coded in a WHERE clause, which search condition will return invoices when payment_date isn't null and invoice_total is greater than or equal to $500? A) payment_date IS NULL AND invoice_total > 500 B) payment_date IS NOT NULL OR invoice_total >= 500 C) NOT (payment_date IS NULL AND invoice_total <= 500) D) payment_date IS NOT NULL AND invoice_total >= 500
D) payment_date IS NOT NULL AND invoice_total >= 500
In a cross join, all of the rows in the first table are joined with all of the A) distinct rows in the second table B) matched rows in the second table C) unmatched columns in the second table D) rows in the second table
D) rows in the second table
In a join, column names need to be qualified only A) in inner joins B) in outer joins C) when the code is confusing D) when the same column names exist in both tables
D) when the same column names exist in both tables
(Refer to code example 7-2.) When this query is executed, the result table will contain one row for A) each vendor B) each vendor in the table named ia C) each invoice D) each invoice in the table named ia
each vendor in the table named ia
