Database systems Final Review

Which of the following types of SQL statements is not a Data Manipulation Language (DML) statement? a. INSERT b. SELECT c. UPDATE d. CREATE TABLE

d. CREATE TABLE

Which one of the following results shows the correct output of the statement: A. 11-Feb-2022 B. 02-11-22 C. 11-02-22 D. 02/11/22

B. 02-11-22

Which of the following is not a common error when entering and executing SQL statements? a. misspelling the name of a table b. misspelling a keyword c. selecting the wrong database d. forgetting to connect to a database server

Forgetting to connect to a database

Which of the following is not a hardware component of a client/server system? a. network b. client c. printer d. server

c. printer

Which of the following can you do when working with a table in MySQL Workbench?

A) view the data for the tableB) edit the column definitions for the tableC) view the column definitions for the tableD) all of the above*

Which one of the following statements is correct in using subqueries? A. SELECT order_id, order_dateFROM orders WHERE order_id =(SELECT invoice_id FROM invoices WHERE invoice_date between '2020-09-18' AND '2020-10-18') B. SELECT order_id, order_dateFROM orders WHERE order_id IN (SELECT invoice_id FROM invoices WHERE invoice_date between '2020-09-18' AND '2020-10-18') C. SELECT order_id, order_dateFROM orders WHERE order_id LIKE(SELECT invoice_id FROM invoices WHERE invoice_date between '2020-09-18' AND '2020-10-18') D. SELECT order_id, order_dateFROM orders WHERE order_id IN (SELECT invoice_id FROM invoices WHERE invoice_date IN '2020-09-18' AND '2020-10-18')

B. SELECT order_id, order_dateFROM orders WHERE order_id IN (SELECT invoice_id FROM invoices WHERE invoice_date between '2020-09-18' AND '2020-10-18')

Which one of the following statements is correct to get first name, last name and initials of each person? A. SELECT first_name, last_name, CONCAT(LEFT(first_name), LEFT(last_name)) AS initials FROM names B. SELECT first_name, last_name, CONCAT(LEFT(first_name, 1), LEFT(last_name, 1)) AS initials FROM names C. SELECT first_name, last_name, LEFT(CONCAT(first_name, last_name), 2) AS initials FROM names D. SELECT first_name, last_name, CONCAT(LEFT(first_name, 1), right(last_name, 1)) AS initials FROM names

B. SELECT first_name, last_name, CONCAT(LEFT(first_name, 1), LEFT(last_name, 1)) AS initials FROM names

Which one of the following statements returns an output like this: A. SELECT DATE_FORMAT(CURRENT_DATE, '%e/%b/%y') B. SELECT DATE_FORMAT(CURRENT_DATE, '%e/%b/%Y') C. SELECT DATE_FORMAT(CURRENT_DATE, '%D/%M/%Y') D. SELECT DATE_FORMAT(CURRENT_DATE, '%e-%b-%Y')

B. SELECT DATE_FORMAT(CURRENT_DATE, '%e/%b/%Y')

How would you identify the many side of a relationship between two tables in a database? a. By defining a primary key in one table that's related to the primary key in the other table b. By defining a foreign key in one table that's related to a foreign key in the other table c. By defining a primary key in one table that's related to a unique key in the other table d. By defining a foreign key in one table that's related to the primary key in the other table

By defining a foreign key in one table that's related to the primary key in the other table

Which one of the following statements is NOT returning the information about orders completed within January 2022? A. SELECT * FROM orders WHERE NOT (order_date < '2022-01-01' OR order_date > '2022-01-31') B. SELECT * FROM orders WHERE order_date >= '2022-01-01' AND order_date <= '2022-01-31' C. SELECT * FROM orders WHERE order_date between ('2022-01-01', '2022-01-31') D. SELECT * FROM orders WHERE order_date IN ('2022-01-01', '2022-01-31') Question 6

SELECT * FROM orders WHERE order_date IN ('2022-01-01', '2022-01-31')

Which of the following SELECT statements would you use to prevent duplicate rows from being returned? a. SELECT vendor_idFROM DISTINCT invoicesORDER BY vendor_id b. SELECT vendor_id DISTINCTFROM invoicesORDER BY vendor_id c. SELECT vendor_idFROM invoicesWHERE DISTINCTORDER BY vendor_id d. SELECT DISTINCT vendor_idFROM invoicesORDER BY vendor_id

SELECT DISTINCT vendor_idFROM invoicesORDER BY vendor_id

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

Use comments to describe what each statement does.

Which of the following statements best describes what this UPDATE statement does?UPDATE invoicesSET credit_total = invoice_total - payment_totalWHERE vendor_id = 10 AND invoice_total - payment_total - credit_total > 0 a. Updates the credit_total column for all invoices with 10 in the vendor_id column and a balance due so they don't have a balance due. b. Updates the credit_total column for the first invoice with 10 in the vendor_id column and a balance so it doesn't have a balance due. c. Updates the credit_total column for all invoices with a balance due so they don't have a balance due. d. Updates the credit_total column for the first invoice with a balance due so it doesn't have a balance due.

a. Updates the credit_total column for all invoices with 10 in the vendor_id column and a balance due so they don't have a balance due.

A single table in a relational database consists of a. rows only b. columns only c. rows and columns d. matrixes

c. rows and columns

Code example 5-1team_id INT NOT NULL AUTO_INCREMENT,team_name VARCHAR(50), team_rank INT NOT NULL DEFAULT 0 (Refer to code example 5-1.) What are the values of the team_id, team_name, and team_rank columns after the following statement is executed, assuming that the Teams table has one row in it with a team_id of 1?INSERT INTO teams (team_name)VALUES ('Angels') a. 2, Angels, 0 b. 1, Angels, 1 c. 1, Angels, 0 d. 2, Angels, 1

a. 2, Angels, 0

Code example 6-1SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty,SUM(invoice_total) AS invoice_averageFROM invoices JOIN vendorsON invoices.vendor_id = vendors.vendor_idWHERE vendor_state < 'e'GROUP BY vendor_state, vendor_city, vendor_nameHAVING SUM(invoice_total) > 500ORDER 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: a. The column name for the fifth column in the result set doesn't match the data. 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 condition in the HAVING clause should be coded in the WHERE clause. d. The condition in the WHERE clause should be coded in the HAVING clause.

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

What determines the kind of data that can be stored in a column of a table? a. The data type that's assigned to the column b. Whether the column can contain a null value c. The relationship between the column and the primary key of the table d. The default value that's assigned to the column

a. The data type that's assigned to the column

Which of the statements below best describes the result set returned by this SELECT statement?SELECT vendor_state, COUNT(*) AS column_2FROM vendorsGROUP BY vendor_stateHAVING COUNT(*) > 1ORDER BY vendor_state a. The number of vendors in each state that has more than one vendor b. The duplicate vendors from each state c. The names of the vendors in each state d. The number of vendors in each state

a. The number of vendors in each state that has more than one vendor

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

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? a. columns that are not functionally dependent on a column used for grouping b. aggregate functions c. columns used for grouping d. columns that are functionally dependent on a column used for grouping

a. 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 IN (0, 1000) b. invoice_total BETWEEN 0 AND 1000 c. NOT (invoice_total > 1000) d. invoice_total <= 1000

a. invoice_total IN (0, 1000)

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 LIKE 'SAN%' c. vendor_city LIKE 'S_N%'

a. vendor_city LIKE 'SAN$'

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. AVG b. MAX c. MIN d. COUNT

a. AVG

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

a. The result sets must be derived from different tables.

Which clause do you use to filter the rows returned by a SELECT statement? a. WHERE b. FROM c. SELECT d. ORDER BY

a. WHERE

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'

a. WHERE vendor_name < 'D'

Which of the following is not true when you're using MySQL Workbench to test a script that contains more than one SQL statement? a. You cannot run all of the statements in the script at once. b. When you run a script, the results are displayed in the Result grid. c. You can run each statement in the script by itself.

a. You cannot run all of the statements in the script at once.

When you use a subquery to insert rows from one table into another table, which of the following statements is not true? a. You must omit the column list from the INSERT statement. b. You must include a SELECT statement that retrieves the rows to be inserted. c. The target table must already exist. d. You must omit the VALUES clause from the INSERT statement.

a. You must omit the column list from the INSERT statement.

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

a. back-end processing

To join each row from the first table with each row from the second table, you use a a. cross join b. left join c. right join d. natural join

a. cross join

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. assign an alias to the table in the other database c. prefix the name of each table with the name of the database that contains it 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

SELECT vendor_name, invoice_numberFROM invoices LEFT JOIN vendorsON invoices.vendor_id = vendors.vendor_id (Refer to code example 4-1.) 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

a. the number of rows in the Invoices table

A file that contains one or more SQL statements is called a a. stored procedure b. statement list c. script d. snippet

c. script

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

a. use that alias to refer to the table throughout your query

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

a. when the same column names exist in both tables

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_amountFROM 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

Code example 6-2SELECT 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_totalFROM invoices (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. All of the rows are treated as a single partion. c. The rows for each vendor are treated as a separate partition. d. The rows for each invoice date are treated as a separate partition.

b. All of the rows are treated as a single partion.

Code example 6-2SELECT 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_totalFROM invoices (Refer to code example 6-2.) What does the ORDER BY clause in the OVER clause for the second SUM function do? a. It sorts all rows in the result set by the invoice_total column. b. 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. 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 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.

b. 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 a common reason for using the IF function with the GROUPING function? a. To add a summary row for each group only if the group has more than one row. b. To replace the nulls that are generated by WITH ROLLUP with literal values. c. To add a summary row for the result set only if the result set has more than one row. d. To replace the nulls in grouped columns with literal values.

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

When you code a calculated value in a SELECT clause, the expression for the calculated value can include a. arithmetic operators, comparison operators, and functions only b. arithmetic operators and functions only c. arithmetic operators, comparison operators, logical operators, and functions d. arithmetic operators only

b. arithmetic operators and functions only

When you code a calculated value in a SELECT clause, the expression for the calculated value can include a. arithmetic operators and functions only b. arithmetic operators, comparison operators, logical operators, and functions c. arithmetic operators, comparison operators, and functions only d. arithmetic operators only

b. arithmetic operators, comparison operators, logical operators, and functions

Code example 6-1SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty,SUM(invoice_total) AS invoice_averageFROM invoices JOIN vendorsON invoices.vendor_id = vendors.vendor_idWHERE vendor_state < 'e'GROUP BY vendor_state, vendor_city, vendor_nameHAVING SUM(invoice_total) > 500ORDER 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 a. each vendor with invoice average over $500 b. each vendor with invoice totals over $500 c. each city with invoice totals over $500 d. each city with invoice average over $500

b. each vendor with invoice totals over $500

Which of the following types of diagrams can be used to show how the tables in a database are defined and related? a. table relationship b. entity relationship c. object relationship d. all of the above

b. entity relationship

Which of the following statements best describes what this INSERT statement does?INSERT INTO invoices_copySELECT *FROM invoicesWHERE terms_id = 1 a. Adds one row from the Invoices table to the Invoices_Copy table. b. Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table. c. Adds all of the rows in the Invoices table to the Invoices_Copy table and sets the terms_id column to 1 in each row. d. Updates all of the rows in the Invoices_Copy table that have 1 in the terms_id column to the rows in the Invoices table.

b. Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table.

All but one of the following is true about the WITH ROLLUP operator. Which one is it? a. It is coded on the GROUP BY clause. b. It is part of standard SQL. c. It adds a summary row to the end of the result set. d. It adds a summary row for each group specified in the GROUP BY clause.

b. It is part of standard SQL.

Which of the following would return a maximum of 7 rows, starting with the 5th row? a. LIMIT 4, 6 b. LIMIT 4, 7 c. LIMIT 7, 4 d. LIMIT 4

b. LIMIT 4, 7

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, FROM, WHERE, ORDER BY c. SELECT, WHERE, ORDER BY, FROM d. SELECT, ORDER BY, FROM, WHERE

b. SELECT, FROM, WHERE, ORDER BY

Which of the following is not true about coding inner joins? a. The join can be done between tables in different databases. b. The join condition must consist of a single comparison. c. The join condition typically compares the primary key of one table to a foreign key of another table. d. The join can be done between the same table.

b. The join condition must consist of a single comparison.

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. a base table column c. a function d. an arithmetic expression

b. a base table column

You can simulate a full outer join by using a. the ON clause b. a union c. a self join d. a left outer join

b. a union

In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it? a. run a SQL script b. create a database diagram c. code SQL statements d. display a SQL script

b. create a database diagram

A database driver is software that lets the a. application program communicate with the data access model b. data access model communicate with the database c. application program communicate with the database d. data access model communicate with the application program

b. data access model communicate with the database

Before you can use MySQL Workbench to work with a MySQL database, you must a. create a user other than the root user b. open a connection to the database server c. edit the parameters for the connection you will be using so they will work with the database server d. all of the above

b. open a connection to the database server

To save your own SQL statement in MySQL Workbench so you don't have to type it each time, you can create a new a. custom statement b. snippet c. snapshot d. stored statement

b. snippet

SELECT vendor_name, invoice_numberFROM invoices LEFT JOIN vendorsON 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 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

b. the number of rows in the Vendors table

Code example 5-1team_id INT NOT NULL AUTO_INCREMENT,team_name VARCHAR(50), team_rank INT NOT NULL DEFAULT 0 (Refer to code example 5-1.) What are the values of the team_id, team_name, and team_rank columns after the following statement is executed, assuming that the Teams table has one row in it with a team_id of 1?INSERT INTO teamsVALUES (DEFAULT, NULL, 3) a. 0, NULL, 0 b. 2, NULL, 1 c. 2, NULL, 3 d. 0, NULL, 3

c. 2, NULL, 3

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 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. d. 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.

Which of the following SELECT statements would you use to prevent duplicate rows from being returned? a. SELECT vendor_id DISTINCTFROM invoicesORDER BY vendor_id b. SELECT vendor_idFROM DISTINCT invoicesORDER BY vendor_id c. SELECT DISTINCT vendor_idFROM invoicesORDER BY vendor_id d. SELECT vendor_idFROM invoicesWHERE DISTINCTORDER BY vendor_id

c. SELECT DISTINCT vendor_idFROM invoicesORDER BY vendor_id

What is the result of the expression that follows if the value of last_name is "Smith" and the value of first_name is "Steve"?CONCAT(last_name, first_name) a. Smith, Steve b. Smith,Steve c. SmithSteve d. Smith Steve

c. SmithSteve

Assuming that all of the table and column names are spelled correctly, what's wrong with the INSERT statement that follows?INSERT INTO invoices(vendor_id, invoice_number, invoice_total, payment_total, credit_total,terms_id, invoice_date, invoice_due_date)VALUES(97, '456789', 8344.50, 0, 0, 1, '2012-08-31') a. There are too many items in the VALUES list. b. The column names in the column list are in the wrong sequence. c. The number of items in the column list doesn't match the number in the VALUES list. d. All of the values should be enclosed by single quotes.

c. The number of items in the column list doesn't match the number in the VALUES list.

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_2FROM invoicesWHERE invoice_total - payment_total - credit_total > 0GROUP BY vendor_idORDER BY vendor_id a. The unpaid balance for each invoice b. The total amount invoiced by each vendor_id c. The total unpaid balance due for each vendor_id d. The total of paid invoices for each vendor_id

c. The total unpaid balance due for each vendor_id

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

c. a result set

Expressions coded in the HAVING clause a. can use aggregate search conditions but can't use non-aggregate search conditions b. can refer to any column in the base tables c. can use either aggregate search conditions or non-aggregate search conditions d. can use non-aggregate search conditions but can't use aggregate search conditions

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

The intersection of a row and column is commonly called a a. record b. box c. cell d. field

c. cell

The MySQL database server can also be referred to as the a. database manager b. database driver c. database engine d. database controller

c. database engine

To start the server in MySQL Workbench, you can click the Start Server button in the a. MySQL Connections section of the Home page b. toolbar for an open database connection c. tab that's displayed when you select Startup / Shutdown from the Navigator window d. Schemas tab of the Navigator window

c. tab that's displayed when you select Startup / Shutdown from the Navigator window

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

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

To define peer rows for an aggregate window function, you must include all but one of the following clauses. Which one is it? a. ORDER BY b. PARTITION BY c. BETWEEN d. RANGE

c. BETWEEN

Which of the following is not a database object in MySQL? a. table b. function c. EER diagram d. view

c. EER diagram

To use a self join, you must do all but one of the following. Which one is it? a. qualify each column name with an alias b. assign aliases to the tables c. Include the DISTINCT keyword

c. Include the DISTINCT keyword

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

c. Not, And, Or

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

c. SELECT

How would you code a SELECT clause so it returns all columns from the base table? a. SELECT COLUMNS b. SELECT *.* c. SELECT * d. SELECT ALL

c. SELECT *

Which of the following can you not use in a join condition? a. logical operators b. comparison operators c. arithmetic operators

c. arithmetic operators

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

c. data access API

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

c. 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? a. NOT (invoice_total > 1000) b. invoice_total BETWEEN 0 AND 1000 c. invoice_total IN (0, 1000) d. invoice_total <= 1000

c. invoice_total IN (0, 1000)

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

c. payment_date IS NOT NULL AND invoice_total >= 500

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

c. 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. unmatched columns in the second table b. distinct rows in the second table c. rows in the second table d. matched rows in the second table

c. rows in the second table

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

When you run a SQL statement that contains a coding error, the error message that MySQL Workbench displays does not include a. an error code b. brief description of the error c. the likely cause of the error

c. the likely cause of the error

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

c. unmatched rows from both the left and right tables

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

c. you code a USING clause in addition to the ON clause

Which of the following types of relationships can a relational database not define? a. one-to-one b. many-to-many c. zero-to-many d. one-to-many

c. zero-to-many

Which of the following statements is not true about a frame for an aggregate window function? a. It can be defined as a range of values based on the value of the current row. b. It can be defined as the number of rows before and after the current row. c. It defines a subset of the current partition. d. If you don't specify the ending row for a frame, the ending row is the last row in the partition.

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

Code example 6-2SELECT 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_totalFROM invoices (Refer to code example 6-2.) What rows make up the partitions for the second SUM function? a. The rows for each invoice date are treated as a separate partition. b. Each row is treated as a separate partition. c. All of the rows are treated as a single partion. d. The rows for each vendor are treated as a separate partition.

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

Which of the following statements would you use to change the invoice_total value in the Invoices table so it's 1% less than the current invoice total for every vendor in the state of Arizona? a. UPDATE invoicesSET invoice_total = invoice_total * .01WHERE vendor_id =(SELECT vendor_idFROM vendorsWHERE vendor_state = 'AZ') b. UPDATE invoicesSET invoice_total = invoice_total - (invoice_total * .01)WHERE vendor_id =(SELECT vendor_idFROM vendorsWHERE vendor_state = 'AZ') c. UPDATE invoicesSET invoice_total = invoice_total * .01WHERE vendor_id IN(SELECT vendor_idFROM vendorsWHERE vendor_state = 'AZ') d. UPDATE invoicesSET invoice_total = invoice_total - (invoice_total * .01)WHERE vendor_id IN(SELECT vendor_idFROM vendorsWHERE vendor_state = 'AZ')

d. UPDATE invoicesSET invoice_total = invoice_total - (invoice_total * .01)WHERE vendor_id IN(SELECT vendor_idFROM vendorsWHERE vendor_state = 'AZ')

Expressions coded in the WHERE clause a. can use either aggregate search conditions or non-aggregate search conditions b. must refer to columns in the SELECT clause c. can use aggregate search conditions but can't use non-aggregate search conditions d. can use non-aggregate search conditions but can't use aggregate search conditions

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

Which clause of the SELECT statement specifies the table that contains the data to be retrieved? a. WHERE b. ORDER BY c. SELECT d. FROM

d. FROM

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

d. Not, And, Or

Before you can run a SQL statement, you must a. open a SQL tab b. enter the statement in the SQL editor c. select the database that the statement will be executed against d. all of the above

d. all of the above

Which of the following can you do when working with a table in MySQL Workbench? a. view the column definitions for the table b. view the data for the table c. edit the column definitions for the table d. all of the above e. a and b only

d. all of the above

When you use the implicit syntax for coding inner joins, the join conditions a. aren't coded b. are coded on the USING clause c. are coded on the ON clause d. are coded on the WHERE clause

d. are coded on the WHERE clause

Unlike a join, a union a. by default, does not eliminate duplicate rows b. is coded in the ORDER BY clause 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

The SQL statements that work with the data in a database are called a. data modification language b. data manipulation statements c. data modification statements d. data manipulation language

d. data manipulation language

Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax a. lets you combine inner and outer joins b. lets you separate the join and search conditions c. is easier to read and understand d. is an older syntax that works with legacy code

d. is an older syntax that works with legacy code

Which of the following is not a valid column alias? a. total b. total_sales c. "Total Sales" d. total sales

d. total sales

To uniquely identify each row in a table, you can define a a. primary key or foreign key b. unique key or foreign key c. foreign key d. unique key or primary key

d. unique key or primary key

Which of the following SELECT clauses could you use to assign an alias to the invoice_total column? 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 d. all of the above e. a and b only

e. a and b only