CSCI320 - Midterm1 Questions
Which keyword lets you control the number of rows that are returned by a query? (a) TOP (b) DISTINCT (c) ALL (d) all of the above
Answer: (d) all of the above
Which functions perform a calculation on the values of a column from selected rows? (a) Aggregate (b) Summary (c) Arithmetic (d) String
Answer: (a) Aggregate
The search condition of a WHERE clause consists of one or more . . . (a) numeric expressions (b) string expressions (c) Boolean expressions (d) none of the above
Answer: (c) Boolean expressions
Subqueries can be ________________ within other subqueries. (a) joined (b) grandfathered (c) restated (d) nested
Answer: (d) nested
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; If the LEFT keyword is replaced with the FULL 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 above
Answer: (d) none of the above
When you code a column list in an INSERT statement, you can omit identify columns, columns that have default values, and columns that allow __________ values. (a) time (b) date (c) string (d) null
Answer: (d) null
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; The column name for the second column in the result set will be? (a) Vendor (b) VendorName (c) VendorID (d) Date
Answer: (d) Date
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) (a) a column of one or more rows (b) a single value (c) a subquery can't be introduced in this way (d) a table
Answer: (d) a table
Insert, Update, and Delete statements can be referred to as ________________ queries. (a) database (b) result (c) SELECT (d) action
Answer: (d) action
If you code a column list in an INSERT statement that includes a column that has a default value, which keyword can you code in the VALUES clause to use the default value? (a) DEFAULT (b) VALUE (c) NULL (d) none of the above
Answer: (a) DEFAULT
Which SQL statement deletes every row in the Vendors table? (a) DELETE Vendors; (b) DELETE Vendors WHERE ALL; (c) DELETE * Vendors; (d) none of the above
Answer: (a) DELETE Vendors;
To test whether one or more rows are returned by a subquery, you can use which operator? (a) EXISTS (b) NOT EXISTS (c) MIN (d) MAX
Answer: (a) EXISTS
In an UPDATE statement, the WHERE clause will . . . (a) name the columns to be updated (b) specify the condition a row must meet to be updated (c) specify the values to be assigned to columns (d) name the table to be updated
Answer: (b) specify the condition a row must meet to be updated
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows. (a) expression (b) table (c) aggregate (d) none of the above
Answer: (b) table
Correlation names are temporary table names assigned in which clause? (a) WHERE (b) ON (c) FROM (d) ORDER BY
Answer: (c) FROM
Which clause of the SELECT statement names the table that contains the data to be retrieved? (a) SELECT (b) ORDER BY (c) FROM (d) WHERE
Answer: (c) FROM
To insert several rows selected from another table into a table, you can code an INSERT statement with a/an ______________ in place of the VALUES clause. (a) WHERE clause (b) summary query (c) subquery (d) HAVING clause
Answer: (c) subquery
A SELECT statement that includes aggregate functions is often called a/an ________________ query. (a) action (b) compound (c) summary (d) none of the above
Answer: (c) summary
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause. (a) HAVING (b) GROUP BY (c) FROM (d) ORDER BY
(a) HAVING
When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding which operator? (a) ROLLUP (b) HAVING (c) OVER (d) none of the above
Answer: (a) ROLLUP
A subquery is a/an ______________ statement that's coded within another SQL statement. (a) SELECT (b) WHERE (c) FROM (d) ORDER BY
Answer: (a) SELECT
In a cross join, all of the rows in the first table are joined with all of the . . . (a) unmatched rows in the second table (b) rows in the second table (c) distinct rows in the second table (d) matched rows in the second table
Answer: (b) rows in the second table
In the INSERT statement that follows, assume that all of the table and column names are spelled correctly, that none of the columns are identity columns, and that none of them have default values or accept null values. What's wrong with the statement? INSERT INTO InvoiceCopy (VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDate, InvoiceDueDate) VALUES (97, '456789', 8344.50, 0, 0, 1, '2016-08-01'); (a) The number of items in the column list doesn't match the number in the VALUES list. (b) The values are in the wrong sequence. (c) There are zeroes in the VALUES list. (d) The column names in the column list are in the wrong sequence.
Answer: (a) The number of items in the column list doesn't match the number in the VALUES list.
When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the _________________ clause specifies which row or rows are to be updated. (a) WHERE (b) HAVING (c) MERGE (d) ORDER BY
Answer: (a) WHERE
When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause? (a) WHERE (b) FROM (c) ORDER BY (d) SELECT
Answer: (a) WHERE
If you use ________________ in the select list, you must name the column since that name is used in the definition of the new table. (a) calculated values (b) indexes (c) aggregate functions (d) foreign keys
Answer: (a) calculated values
In many cases, a subquery can be restated as a/an ______________. (a) join (b) average (c) aggregate function (d) object
Answer: (a) join
You can use a ________________ in the ________________ clause if you need to specify column values or search conditions other than the one named in the UPDATE clause. (a) join, FROM (b) join, WHERE (c) table, FROM (d) column, WHERE
Answer: (a) join, FROM
Since the MERGE operation often involves updating existing rows and inserting new rows, the MERGE statement is sometimes referred to as what? (a) the upsert statement (b) the dessert statement (c) the insate statement (d) the magic statement
Answer: (a) the upsert statement
When coding search conditions, you can use which keyword to create compound search conditions? (a) CREATE (b) AND (c) JOIN (d) MERGE
Answer: (b) AND
Which of the following statements best describes what this INSERT statement does? INSERT INTO InvoiceArchive SELECT * FROM Invoices WHERE TermsID = 1; (a) Adds all of the rows in the Invoices table to the InvoiceArchive table and sets the TermsID column to 1 in each row. (b) Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table. (c) Adds one row from the Invoices table to the InvoiceArchive table. (d) Updates all of the rows in the InvoiceArchive table that have 1 in the TermsID column to the rows in the Invoices table.
Answer: (b) Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table.
When you code a DELETE statement for one or more rows, which clause specifies which row or rows are to be deleted? (a) USING (b) WHERE (c) HAVING (d) MERGE
Answer: (b) WHERE
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery) (a) a table (b) a single value (c) a subquery can't be introduced in this way (d) a column of one or more rows
Answer: (b) a single value
You can't update (a) a column value (b) an identity column (c) one or more rows (d) a row
Answer: (b) an identity column
You specify the conditions that must be met for a row to be deleted in the which clause? (a) HAVING (b) FROM (c) WHERE (d) ORDER BY
Answer: (c) WHERE
When you use the SELECT INTO technique to create tables (a) only the column definitions and data are copied (b) primary keys, foreign keys, and default values aren't retained (c) a and b (d) none of the above
Answer: (c) a and b
When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is ________________ a new table. (a) updated in (b) deleted from (c) copied into (d) moved into
Answer: (c) copied into
You can use the DELETE statement to delete one or more rows from the table you name in the ________________ clause. (a) HAVING (b) WHERE (c) FROM (d) DELETE
Answer: (d) DELETE
You use the UPDATE statement to modify one or more rows in the table named in the ________________ clause. (a) WHERE (b) INSERT (c) FROM (d) UPDATE
Answer: (d) UPDATE
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what? (a) a backslash (b) a colon (c) a semi-colon (d) a comma
Answer: (d) a comma
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) (a) a column of one or more rows (b) a subquery can't be introduced in this way (c) a table (d) a single value
Answer: (d) a single value
You can code a subquery (a) in the SET clause to return the value that's assigned to a column (b) in the FROM clause to identify the rows that are available for update (c) in the WHERE clause to provide one or more values used in the search condition (d) all of the above
Answer: (d) all of the above
If you omit the WHERE clause from a DELETE statement (a) the table definition will be deleted (b) all rows in the table will be deleted (c) all columns in the table will be deleted (d) none of the above
Answer: (b) all rows in the table will be deleted
To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause? (a) PERCENT (b) ALL (c) ORDER BY (d) DISTINCT
(d) DISTINCT
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; This join is coded using what syntax? (a) implicit (b) explicit (c) SQL-92 (d) both b and c
(d) both b and c
All of the aggregate functions ignore null values, except for which function? (a) COUNT(*) (b) MAX (c) MIN (d) AVG
Answer: (a) COUNT(*)
Write an aggregate expression for the number of entries in the VendorName column, including null values (a) COUNT(*) (b) COUNT(Invoice + NULL) (c) COUNT(VendorName) (d) SUM(VendorName)
Answer: (a) COUNT(*)
Write an aggregate expression for the number of unique values in the VendorID column (a) COUNT(DISTINCT VendorID) (b) SUM(VendorID) (c) AVG(VendorID) (d) COUNT(VendorID)
Answer: (a) COUNT(DISTINCT VendorID)
Which of the following is not a SQL DML statement? (a) CreateTable (b) Update (c) Select (d) Insert
Answer: (a) CreateTable
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword? (a) DISTINCT (b) ORDER BY (c) AVG (d) GROUP BY
Answer: (a) DISTINCT
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause? (a) WHERE (b) ORDER BY (c) FROM (d) any of the above
Answer: (a) WHERE
A join that joins a table with itself is called . . . (a) a self-join (b) a U-join (c) an outer join (d) an inner join
Answer: (a) a self-join
When you need to code multiple conditions in a join, it's best to . . . (a) code only join conditions in the ON clause (b) code only join conditions in the WHERE clause (c) code all conditions in the ON clause (d) code all conditions in the WHERE clause
Answer: (a) code only join condition in the ON clause
The interface between an application program and the DBMS is usually provided by the . . . (a) programmer (b) data access API (c) back end (d) front end
Answer: (b) data access API
A union combines the rows from two or more what? (a) queries (b) result tables (c) SELECT statements (d) all of the above
Answer: (d) all of the above
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; The name "v" is known as a? (a) placeholder (b) correlation name (c) table alias (d) both b and c
Answer: (d) both b and c
To store and manage the databases of the client/server system, each server requires what? (a) Structured Query Language (SQL) (b) data access API (application programming interface) (c) tables (d) database management system (DBMS)
Answer: (d) database management system (DBMS)
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; 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 city with invoice average over $500 (c) each vendor with invoice average over $500 (d) each vendor with invoice totals over $500
Answer: (d) each vendor with invoice totals over $500
Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL. (a) dialect/variant (b) flavor (c) style (d) parlance
(a) dialect/variant
When you code a SELECT statement, you must code the four main clauses in the following order: (a) SELECT, WHERE, ORDER BY, FROM (b) SELECT, FROM, WHERE, ORDER BY (c) SELECT, FROM, ORDER BY, WHERE (d) SELECT, ORDER BY, FROM, WHERE
(b) SELECT, FROM, WHERE, ORDER BY
Unless you assign a ________________, the column name in the result set is the same as the column name in the base table. (a) column alias (b) unique syntax (c) qualification (d) all of the above
Answer: (a) column alias
A view is a SELECT statement that is stored with the ________________. (a) database (b) join (c) query (d) calculated values
Answer: (a) database
To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table. (a) foreign key (b) index (c) primary key (d) composite primary key
Answer: (a) foreign key
When you code a union with the INTERSECT keyword to combine two result sets, the union . . . (a) includes only rows that occur in both result sets (b) excludes rows from the second result set if they also occur in the first result set (c) includes all rows that occur in both result sets if the primary keys are the same (d) excludes rows from the first result set if they also occur in the second result set
Answer: (a) includes only rows that occur in both result sets
A correlated subquery is one that . . . (a) is executed once for each row in the outer query (b) uses correlation names for the tables in the subquery (c) uses correlation names for one or more of the tables in a join (d) uses correlation names for the tables in the outer query
Answer: (a) is executed once for each row in the outer query
What can you use to combine data from two or more tables into a single result set? (a) join (b) view (c) result set (d) virtual table
Answer: (a) join
The three main hardware components of a client/server system are the clients, the server, and the ________________. (a) network (b) application (c) hard drive (d) data access API
Answer: (a) network
What uniquely identifies each row in a table? (a) primary key (b) field (c) foreign key (d) cell
Answer: (a) primary key
This is typically modeled after a real-world entity, such as an invoice or a vendor. (a) table (b) column (c) row (d) cell
Answer: (a) table
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; 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 above
Answer: (a) the number of rows in the Invoices table
To return all of the columns from the base table, which wildcard character do you include in the SELECT clause? (a) - (b) * (c) = (d) +
Answer: (b) *
To concatenate character strings in a string expression, which operator do you use? (a) = (b) + (c) * (d) -
Answer: (b) +
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values (a) SUM(InvoiceTotal) (b) AVG(InvoiceTotal) (c) either a or b (d) none of the above
Answer: (b) AVG(InvoiceTotal)
___________________ names can be used when you want to assign a temporary name to a table. (a) Qualified (b) Correlation (c) Table (d) Object
Answer: (b) Correlation
When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500? (a) PaymentDate IS NULL AND InvoiceTotal > 500 (b) PaymentDate IS NOT NULL AND InvoiceTotal >= 500 (c) NOT (PaymentDate IS NULL AND InvoiceTotal <= 500) (d) PaymentDate IS NOT NULL OR InvoiceTotal >= 500
Answer: (b) PaymentDate IS NOT NULL AND InvoiceTotal >= 500
Which of the following types of statements isn't an action query? (a) Delete (b) Select (c) Insert (d) Update
Answer: (b) Select
What SQL dialect does Microsoft SQL Server use? (a) SQL-92 (b) Transact-SQL (c) SQL-dialect (d) ANSI
Answer: (b) Transact-SQL
If you want to filter the result set that's returned by a SELECT statement, you must include which clause? (a) ORDER BY (b) WHERE (c) FROM (d) SELECT
Answer: (b) WHERE
A search condition in the ________________ clause is applied before the rows are grouped while a search condition in the _________________ clause isn't applied until after the grouping. (a) WHERE, FROM (b) WHERE, HAVING (c) FROM, WHERE (d) HAVING, WHERE
Answer: (b) WHERE, HAVING
If introduced as follows, the subquery can return which of the values listed below? WHERE InvoiceTotal > ALL (subquery) (a) a table (b) a column of one or more rows (c) a subquery can't be introduced in this way (d) a single value
Answer: (b) a column of one or more rows
If you define a column as an identity column, . . . (a) you can't use the column as a primary key column (b) a number is generated for that column whenever a row is added to the table (c) you must also define the column with a default value (d) you must provide a unique numeric value for that column whenever a row is added to the table
Answer: (b) a number is generated for that column whenever a row is added to the table
The processing that's done by the DBMS is typically referred to as . . . (a) database management system (b) back-end processing (c) front-end processing (d) the file server
Answer: (b) back-end processing
Expressions coded in the WHERE clause . . . (a) can use either aggregate search conditions or non-aggregate search conditions (b) can use non-aggregate search conditions but can't use aggregate search conditions (c) must refer to columns in the SELECT clause (d) can use aggregate search conditions but can't use non-aggregate search conditions
Answer: (b) can use non-aggregate search conditions but can't use aggregate search conditions
The intersection of a row and a column is commonly called what? (a) primary key (b) cell (c) intersection (d) foreign key
Answer: (b) cell
To run a SELECT statement from an application program, you store the statement in the ________________ object for the database connection. (a) table (b) command (c) database (d) CLR
Answer: (b) command
To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like . . . (a) queries, connections, and data readers (b) commands, connections, and data readers (c) commands, connections, and databases (d) queries, connections, and databases
Answer: (b) commands, connections, and data readers
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; This type of join is called a/an (a) right join (b) inner join (c) outer join (d) left join
Answer: (b) inner join
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax . . . (a) lets you combine inner and outer joins (b) lets you combine the join and search conditions (c) is easier to read and understand (d) lets you separate the join and search conditions
Answer: (b) lets you combine the join and search conditions
SELECT VendorName, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > (SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal) FROM Invoices) GROUP BY VendorName ORDER BY BalanceDue DESC; When this query is executed, the NumberOfInvoices column for each row will show the number . . . (a) 1 (b) of invoices for each vendor that have a larger balance due than the average balance due for all invoices (c) of invoices in the Invoices table (d) of invoices for each vendor
Answer: (b) of invoices for each vendor that have a larger balance due than the average balance due for all invoices
If you define a column with a default value, that value is used whenever a row . . . (a) in the table is updates (b) that doesn't include a value for that column is added to the table (c) is added to the table (d) with a zero value for that column is added to the table
Answer: (b) that doesn't include a value for that column is added to the table
Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total? (a) (InvoiceTotal - PaymentTotal - CreditTotal) / 10 (b) ((InvoiceTotal - PaymentTotal) - CreditTotal) / 10 (c) InvoiceTotal - CreditTotal - PaymentTotal / 10 (d) (InvoiceTotal - (PaymentTotal + CreditTotal)) * 0.10
Answer: (c) InvoiceTotal - CreditTotal - PaymentTotal / 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 InvoiceTotal value of $1000 or less? (a) InvoiceTotal <= 1000 (b) InvoiceTotal BETWEEN 0 AND 1000 (c) InvoiceTotal IN (0,1000) (d) NOT (InvoiceTotal > 1000)
Answer: (c) InvoiceTotal IN (0,1000)
Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask? (a) OR (b) WHERE (c) LIKE (d) ORDER BY
Answer: (c) LIKE
Write an aggregate expression to find the VendorName column that's last in alphabetical order (a) SUM(VendorName) (b) COUNT(VendorName) (c) MAX(VendorName) (d) either b or c
Answer: (c) MAX(VendorName)
The order of precedence for the logical operators in a WHERE clause is . . . (a) And, Or, Not (b) Or, And, Not (c) Not, And, Or (d) Not, Or, And
Answer: (c) Not, And, Or
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; Although this query runs as coded, it contains this logical error: (a) The condition in the HAVING clause should be coded in the WHERE clause (b) The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence (c) The column name for the fifth column in the result set doesn't match the data (d) The condition in the WHERE clause should be coded in the HAVING clause
Answer: (c) The column name for the fifth column in the result set doesn't match the data
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorState, COUNT(*) AS Column2 FROM Vendors GROUP BY VendorState HAVING COUNT(*) > 1; (a) The duplicate vendors from each state (b) The names of the vendors in each state (c) The number of vendors in each state having more than one vendor (d) The number of vendors in each state
Answer: (c) The number of vendors in each state having more than one vendor
When you code a union that combines two result sets, which of the following is not true? (a) The corresponding columns in the result sets must have compatible data types. (b) The result sets may or may not have any duplicate rows. (c) The result sets must be derived from different tables. (d) Each result set must have the same number of columns.
Answer: (c) The result sets must be derived from different tables.
Which of the following is not a valid column alias name? (a) TotalSales (b) "Total Sales" (c) Total Sales (d) Total
Answer: (c) Total Sales
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery) (a) a table (b) a single value (c) a column of one or more rows (d) a subquery can't be introduced in this way
Answer: (c) a column of one or more rows
When a column in a table is defined, what determines the kind of data it can store? (a) an index (b) a primary key (c) a data type (d) a relationship
Answer: (c) a data type
A combination of column names and operators that evaluate to a single value is called . . . (a) a predicate (b) a view (c) an expression (d) a query
Answer: (c) an expression
When you code an ORDER BY clause, you can specify a . . . (a) column name or alias (b) column name, alias, or expression (c) column name, alias, expression, or column number (d) column name or expression
Answer: (c) column name, alias, expression, or column number
You can use the OVER clause with an aggregate function to . . . (a) omit summary rows with values over a specified amount (b) perform the summary over a range of values (c) include the rows used to calculate the summary in the result set (d) include values in the summary only if they're over a specified amount
Answer: (c) include the rows used to calculate the summary in the result set
If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use what kind of join? (a) right outer (b) cross (c) left outer (d) full outer
Answer: (c) left outer
SELECT VendorName, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > (SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal) FROM Invoices) GROUP BY VendorName ORDER BY BalanceDue DESC; When this query is executed, the result set will contain . . . (a) one row for each invoice for each vendor 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 vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices (d) one row for each invoice that has a larger balance due than the average balance due for all invoices
Answer: (c) one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices
What is the most common type of relationship between two tables? (a) foreign (b) many-to-many (c) one-to-many (d) one-to-one
Answer: (c) one-to-many
To override the order of precedence in an arithmetic expression, you can use . . . (a) braces (b) double quotes (c) parentheses (d) single quotes
Answer: (c) parentheses
To retrieve or update the data in a database, the client sends a ________________ to the database. (a) server (b) web browser (c) query (d) database
Answer: (c) query
A full outer join includes rows that satisfy the join condition, plus . . . (a) the Cartesian product of the two tables (b) rows in the left table that don't satisfy the join condition (c) rows in both tables that don't satisfy the join condition (d) rows in the right table that don't satisfy the join condition
Answer: (c) rows in both tables that don't satisfy the join condition
In a join, column names only need to be qualified where? (a) when the code is confusing (b) in inner joins (c) when the same names are used in both tables (d) in outer joins
Answer: (c) when the same names are used in both tables
Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 20th row? (a) ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH FIRST 10 ROWS (b) ORDER BY InvoiceTotal DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS (c) ORDER BY InvoiceTotal DESC OFFSET 10 ROWS FETCH FIRST 20 ROWS (d) ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS
Answer: (d) ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS
WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; When this query is executed, there will be how many rows in the result table? (a) Unknown (b) 6 (c) 1 (d) 5
Answer: (d) 5
To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause? (a) DISTINCT (b) ASC (c) ALL (d) DESC
Answer: (d) DESC
SQL statements that define the tables in a database are referred to as ________________ statements. (a) ASCII (b) Data Manipulation Language (DML) (c) SELECT (d) Data Definition Language (DDL)
Answer: (d) Data Definition Language (DDL)
Write an aggregate expression to find the latest date in the InvoiceDate column (a) COUNT(InvoiceDate) (b) MIN(InvoiceDate) (c) SUM(InvoiceDate) (d) MAX(InvoiceDate)
Answer: (d) MAX(InvoiceDate)
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, ORDER BY, WHERE, GROUP BY, HAVING (d) SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Answer: (d) SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorID, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2 FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 GROUP BY VendorID; (a) The total of paid invoices for each VendorID (b) The unpaid balance for each invoice (c) The total amount invoiced by each VendorID (d) The total unpaid balance due for each VendorID
Answer: (d) The total unpaid balance due for each VendorID
Which of the following recommendations won't improve the readability of your SQL statements? (a) Start each clause on a new line. (b) Indent continued lines. (c) Break long clauses into multiple lines. (d) Use comments to describe what each statement does.
Answer: (d) Use comments to describe what each statement does.
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) (a) a column of one or more rows (b) a single value (c) a table (d) a subquery can't be introduced in this way
Answer: (d) a subquery can't be introduced in this way
A subquery that's used in a WHERE or HAVING clause is called what? (a) an introduction (b) an outer query (d) an aggregate value (d) a subquery search condition
Answer: (d) a subquery search condition
A string expression can consist of . . . (a) one or more character columns (b) one or more literal values (c) a combination of character columns and literal values (d) all of the above
Answer: (d) all of the above
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table. (a) SELECT (b) primary (c) unique (d) foreign
Answer: (d) foreign
WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; In this query, the table named Top5 is used as part of a . . . (a) correlated subquery (b) noncorrelated subquery (c) correlated table expression (d) join
Answer: (d) join
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; 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 above
Answer: (d) none of the above
A relational database consists of one or more what? (a) cells (b) rows (c) columns (d) tables
Answer: (d) tables
If you assign a correlation name to one table in a join, (a) you have to qualify all of the column names for that table (b) you have to qualify every column name in the query (c) you have to assign them to all of the tables in the query (d) you have to use that name for the table in the query
Answer: (d) you have to use that name for the table in the query
When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers? (a) TOP 10 InvoiceTotal (b) TOP 10 PERCENT WITH TIES InvoiceNumber (c) TOP InvoiceNumber, InvoiceTotal (d) TOP 10 PERCENT InvoiceNumber (e) TOP 10 InvoiceNumber
Answer: (e) TOP 10 InvoiceNumber
