CSCI 3700 Final Fall 2022 Review

Which of the following statements about common table expressions (CTEs) is not true?

A CTE can refer to any other CTE in the same WITH clause.

Which of the following statements about sorting the rows in a result set is not true?

A column that's used for sorting must be included in the SELECT clause.

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 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 subquery tends to be more intuitive when it uses an existing relationship between the two tables.

You can code an expression that results in a date value for all but one of the following aggregate functions. Which one is it?

AVG

Code example 6-2 SELECT vendor_id, invoice_date, invoice_total, SUM(invoice_total) OVER() AS total_invoices SUM(invoice_total) OVER(PARTITION BY vendor_id ORDER BY invoice_total) AS vendor_total FROM invoices (Refer to code example 6-2.) What rows make up the partitions for the first SUM function?

All of the rows are treated as a single partition.

Which of the following best describes when you should consider using a named window?

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?

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?

EXISTS

Which clause of the SELECT statement specifies the table that contains the data to be retrieved?

FROM

Which of the following statements is not true about a frame for an aggregate window function?

If you don't specify the ending row for a frame, the ending row is the last row in the partition.

In which of the following ways can you not use a subquery in a SELECT statement?

In an ORDER BY clause as a column specification

To use a self join, you must do all but one of the following. Which one is it?

Include the DISTINCT keyword

Which of the following statements about a correlated subquery is not true?

It can't use the same table as the main query.

What does the GROUPING function do?

It evaluates an expression and returns 1 if the expression is null because it's in a summary row.

All but one of the following is true about the WITH ROLLUP operator. Which one is it?

It is part of standard SQL.

Code example 6-2 SELECT vendor_id, invoice_date, invoice_total, SUM(invoice_total) OVER() AS total_invoices SUM(invoice_total) OVER(PARTITION BY vendor_id ORDER BY invoice_total) AS vendor_total FROM invoices (Refer to code example 6-2.) What does the ORDER BY clause in the OVER clause for the second SUM function do?

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 would return a maximum of 7 rows, starting with the 5th row?

LIMIT 4, 7

The order of precedence for the logical operators in a WHERE clause is

Not, And, Or

How would you code a SELECT clause so it returns all columns from the base table?

SELECT *

Which of the following SELECT statements would you use to prevent duplicate rows from being returned?

SELECT DISTINCT vendor_id FROM invoices ORDER BY vendor_id

Which of the following SELECT clauses could you use to assign an alias to the invoice_total column? (Choose all applied)

SELECT invoice_number, invoice_date, invoice_total total or SELECT invoice_number, invoice_date, invoice_total AS total

The six clauses of the SELECT statement must be coded in the following order:

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

Code example 6-1 SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state < 'e' GROUP BY vendor_state, vendor_city, vendor_name HAVING SUM(invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name (Please refer to code example 6-1.) Although this query runs as coded, it contains this logical error:

The column name for the fifth column in the result set doesn't match the data.

Which of the following is not true about calculating a moving average for an aggregate window function?

The frame must be defined using the ROWS clause.

Which of the following is not true about coding inner joins?

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_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1 ORDER BY vendor_state

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?

The result sets must be derived from different tables.

Code example 6-2 SELECT vendor_id, invoice_date, invoice_total, SUM(invoice_total) OVER() AS total_invoices SUM(invoice_total) OVER(PARTITION BY vendor_id ORDER BY invoice_total) AS vendor_total FROM invoices (Refer to code example 6-2.) What rows make up the partitions for the second SUM function?

The rows for each vendor are treated as a separate partition.

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

The total unpaid balance due for each vendor_id

Which of the following is a common reason for using the IF function with the GROUPING function?

To replace the nulls that are generated by WITH ROLLUP with literal values.

Which clause do you use to filter the rows returned by a SELECT statement?

WHERE

Which of the following WHERE clauses will return vendor names from A to C?

WHERE vendor_name < 'D'

Which of the following can you not code in a SELECT clause when you omit the FROM clause from a SELECT statement?

a base table column

If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery)

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 list of values

If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)

a single value

If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)

a single value

A subquery can return

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 subquery can't be introduced in this way

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)

a table of values

You can simulate a full outer join by using

a union

When you use the implicit syntax for coding inner joins, the join conditions

are coded on the WHERE clause

Which of the following can you not use in a join condition?

arithmetic operators

When you code a calculated value in a SELECT clause, the expression for the calculated value can include

arithmetic operators and functions only

SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC (Refer to code example 7-1.) When this query is executed, the rows will be sorted by

balance_due in descending sequence

Expressions coded in the HAVING clause

can use either aggregate search conditions or non-aggregate search conditions

Expressions coded in the WHERE clause

can use non-aggregate search conditions but can't use aggregate search conditions

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

When a SELECT statement includes a GROUP BY clause, the SELECT clause can include all but one of the following. Which one is it?

columns that are not functionally dependent on a column used for grouping

Unlike a join, a union

combines the result sets of two or more SELECT statements

To join each row from the first table with each row from the second table, you use a

cross join

SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Refer to code example 7-2.) When this query is executed, the result table will contain one row for

each vendor in the table named ia

Code example 6-1 SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state < 'e' GROUP BY vendor_state, vendor_city, vendor_name HAVING SUM(invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name (Please refer to code example 6-1.) When this summary query is executed, the result set will contain one summary row for

each vendor with invoice totals over $500

SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Refer to code example 7-2.) When this query is executed, there will be one row

for each vendor with an average invoice total that's greater than 100

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?

invoice_total - credit_total - payment_total / 10

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?

invoice_total IN (0, 1000)

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax

is an older syntax that works with legacy code

SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC (Refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number

of invoices for each vendor that has a larger balance due than the average balance due for all invoices

SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total - credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC (Refer to code example 7-1.) When this query is executed, the result set will contain

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?

payment_date IS NOT NULL AND invoice_total >= 500

To join a table in one database to a table in another database, you must

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

result set

In a cross join, all of the rows in the first table are joined with all of the

rows in the second table

When you use the USING keyword for a join,

the join must be based on a column or columns that have the same name in both tables

SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Refer to code example 7-2.) When this query is executed, each row in the result table will show

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

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

the number of rows in the Invoices table

Code example 4-1 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id (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

the number of rows in the Vendors table

Which of the following is not a valid column alias?

total sales

A full outer join returns

unmatched rows from both the left and right tables

If you assign an alias to one table in a join, you have to

use that alias to refer to the table throughout your query

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?

vendor_city REGEXP 'NA$'

In a join, column names need to be qualified only

when the same column names exist in both tables

Which is not true about the USING keyword?

you code a USING clause in addition to the ON clause