DATABASE MIDTERM

To relate one table to another, a/an _______________________ in one table is used to point to the primary key in another table.

foreign key

Which of the following types of SQL statements is not a DML statement? INSERT UPDATE SELECT CREATE TABLE

CREATE TABLE

Which of the following would return a maximum of 7 rows, starting with the 4th row? LIMIT 7, 4 LIMIT 4, 7 LIMIT 4 LIMIT 4, 6

LIMIT 4, 7

The order of precedence for the logical operators in a WHERE clause is Not, And, Or And, Or, Not Or, And, Not Not, Or, And

Not, And, Or

Which of the following types of statements does not modify the data in a table? SELECT INSERT UPDATE DELETE

SELECT

When you code a SELECT statement, you must code the four main clauses in the following order SELECT, FROM, ORDER BY, WHERE SELECT, ORDER BY, FROM, WHERE SELECT, WHERE, ORDER BY, FROM SELECT, FROM, WHERE, ORDER BY

SELECT, FROM, WHERE, ORDER BY

When you code a union that combines two result sets, which of the following is not true? Each result set must have the same number of columns. The result sets may or may not have any duplicate rows. The corresponding columns in the result sets must have compatible data types. The result sets must be derived from different tables.

The result sets must be derived from different tables.

Which of the following recommendations won't improve the readability of your SQL statements? Break long clauses into multiple lines. Use comments to describe what each statement does. Start each clause on a new line. Indent continued lines.

Use comments to describe what each statement does.

Which of the following WHERE clauses will return vendor names from A to C? WHERE vendor_name = D WHERE vendor_name < 'D' WHERE vendor_name = 'D' WHERE vendor_name < 'C'

WHERE vendor_name < 'D'

The result of a SELECT statement is a result set a calculated value a stored procedure

a result set

You can simulate a full outer join by using a union the ON clause a left outer join a self join

a union

The processing that's done by the DBMS is typically referred to as front-end processing back-end processing the file server the user interface

back-end processing

When you code an ORDER BY clause, you can specify a column name or alias only column name or expression only column name, alias, or expression only column name, alias, expression, or column number

column name, alias, expression, or column number

Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union is coded in the ORDER BY clause by default, does not eliminate duplicate rows combines columns from the same table combines the result sets of two or more SELECT statements

combines the result sets of two or more SELECT statements

The interface between an application program and the DBMS is usually provided by the back end front end data access API programmer

data access API

A database driver is software that lets the data access model communicate with the application program data access model communicate with the database application program communicate with the data access model application program communicate with the database

data access model communicate with the database

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 (invoice_total - payment_total - credit_total) / 10 (invoice_total - (payment_total + credit_total)) * 0.10 ((invoice_total - payment_total) - credit_total) / 10

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 <= 1000 NOT (invoice_total > 1000) invoice_total IN (0, 1000) invoice_total BETWEEN 0 AND 1000

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 easier to read and understand lets you separate the join and search conditions is an older syntax that works with legacy code lets you combine inner and outer joins

is an older syntax that works with legacy code

Code example 4-2 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id (Refer to code example 4-2.) 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 Invoices table the number of rows in the Vendors table the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above

none of the above

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 NULL AND invoice_total > 500 payment_date IS NOT NULL OR invoice_total >= 500 NOT (payment_date IS NULL AND invoice_total <= 500) payment_date IS NOT NULL AND invoice_total >= 500

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 distinct rows in the second table matched rows in the second table unmatched columns in the second table rows from the second table

rows from the second table

If you define a column with a default value, that value is used whenever a row is added to the table in the table is updated that doesn't include a value for that column is added to the table with a zero value for that column is added to the table

that doesn't include a value for that column is added to the table

When you use the USING keyword for a join, the join can't be an outer join the join can't be done on more than one column the join must be based on a column or columns that have the same name in both tables the join must be an inner join

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

Code example 4-2 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id (Refer to code example 4-2.) The total number of rows returned by this query must equal the number of rows in the Invoices table the number of rows in the Vendors table the number of rows in the Invoices table plus the number of rows in the Vendors table none of the above

the number of rows in the Invoices table

Which of the following isn't a valid column alias? total total sales total_sales "Total Sales"

total sales

When a column in a table is defined, it is given a data ________________ that determines what kind of data it can store.

type

A full outer join returns rows in the left table that don't satisfy the join condition unmatched rows from both the left and right tables rows in the right table that don't satisfy the join condition the Cartesian product of the two tables

unmatched rows from both the left and right tables

If you assign an alias to one table in a join, you have to assign them to all of the tables use that alias to refer to the table throughout your query qualify every column name in the query qualify all of the column names for that table

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 LIKE 'SAN%' vendor_city REGEXP 'NA$' vendor_city REGEXP '^SA'

vendor_city REGEXP 'NA$'

In a join, column names need to be qualified only in inner joins in outer joins when the code is confusing when the same column names exist in both tables

when the same column names exist in both tables

Which is not true about the USING keyword? you use it to simplify the syntax for joining tables you code a USING clause in addition to the ON clause it can be used with inner or outer joins the join must be an equijoin, meaning the equals operator is used to compare the two columns

you code a USING clause in addition to the ON clause