SQL Exam 2

Which statement is used to modify the structure of an existing table? A) ALTER TABLE B) CREATE TABLE C) CHANGE TABLE D) UPDATE

A) Alter Table

Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements? A) By default, the changes are automatically committed to the database. B) If autocommit mode is on, you can rollback the changes by clicking on the Rollback button. C) If autocommit mode is on, you can commit the changes by clicking on the Commit button. D) By default, the changes are rolled back when you exit MySQL Workbench unless you commit them.

A) By default, the changes are automatically committed to the database.

If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, which is the default setting? A) NULL B) NOT NULL C) This will cause an error D) none of the given options

A) NULL

The order of precedence for the logical operators in a WHERE clause is: A) Not, And, Or B) And, Or, Not C) Or, And, Not D) Not, Or, And

A) Not, And, Or

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 indexes? A) They can help speed up insert, update and delete operations on columns that are updated frequently. B) MySQL automatically creates an index for unique constraints. C) They can improve performance when MySQL searches for rows in a table. D) They can be deleted by using the DROP INDEX statement.

A) They can help speed up insert, update and delete operations on columns that are updated frequently.

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 invoices SET invoice_total = invoice_total - (invoice_total * .01) WHERE vendor_id IN (SELECT vendor_id FROM vendors WHERE vendor_state = 'AZ') B) UPDATE invoices SET invoice_total = invoice_total * .01 WHERE vendor_id IN (SELECT vendor_id FROM vendors WHERE vendor_state = 'AZ') C) UPDATE invoices SET invoice_total = invoice_total - (invoice_total * .01) WHERE vendor_id = (SELECT vendor_id FROM vendors WHERE vendor_state = 'AZ') D) UPDATE invoices SET invoice_total = invoice_total * .01 WHERE vendor_id = (SELECT vendor_id FROM vendors WHERE vendor_state = 'AZ')

A) UPDATE invoices SET invoice_total = invoice_total - (invoice_total * .01) WHERE vendor_id IN (SELECT vendor_id FROM vendors WHERE vendor_state = 'AZ')

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

Which of the following statements best describes what this INSERT statement does? INSERT INTO invoices_copy SELECT * FROM invoices WHERE 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.

The CREATE TABLE statement: A) creates a new table in the specified database B) creates a new table in the current database C) specifies attributes for an existing table D) all of the given options

B) Creates a new table in the current database

Which of the following keywords does not define a column constraint? A) UNIQUE B) DEFAULT C) PRIMARY KEY D) NOT NULL

B) DEFAULT

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

What kind of constraint enforces referential integrity between tables? A) primary key B) foreign key C) unique key D) all of the given options

B) Foreign Key

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

Which of the following statements do you use to delete all data from a table without deleting the definition for the table? A) RENAME TABLE B) TRUNCATE TABLE C) CREATE TABLE D) DROP TABLE

B) TRUNCATE TABLE

Which of the following can you not specify for a column when you create a table using the CREATE TABLE statement? A) data type B) value C) column name D) attributes

B) Value

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'

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

B) You must omit the column list from the INSERT statement.

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

Which of the following is not a valid column alias? A) total B) total sales C) total_sales D) "Total Sales"

B) total sales

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

When you code a script for creating a database: A) you can create the tables in whatever sequence you prefer B) you need to create the referred to tables before you create the tables that refer to them C) you need to create the indexes before you create the tables that they apply to D) you need to code the primary key column first in each table

B) you need to create the referred to tables before you create the tables that refer to them

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.

Which of the following attributes do you use to generate the values for a column as unique numbers in sequence? A) AUTO_NUMBER B) UNIQUE_NUMBER C) AUTO_INCREMENT D) UNIQUE_INCREMENT

C) AUTO_INCREMENT

When you code a table-level constraint, the constraint can refer to data in more than one: A) database B) table C) column D) value

C) Column

Before you can delete a row that has related rows in another table, you should: A) turn safe mode off B) change all of the related rows so they point to a row other than the one you want to delete C) delete the related rows D) delete the related table

C) Delete the related rows

Which of the following statements best describes what this UPDATE statement does? UPDATE invoices SET credit_total = invoice_total - payment_total WHERE vendor_id = 10 AND invoice_total - payment_total - credit_total > 0 A) Updates the credit_total column for all invoices with 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 10 in the vendor_id column and a balance due so they don't have a balance due. D) Updates the credit_total column for all invoices with a balance due

C) 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.

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

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 define a column with the DEFAULT attribute: A) a null value is used if another value isn't specified when a row is added to the database. B) the default value you specify does not need to correspond with the data type for the column. C) the default value is used if another value isn't specified when a row is added to the database.

C) the default value is used if another value isn't specified when a row is added to the database.

Code example 5-1 team_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 VALUES (DEFAULT, NULL, 3) A) 0, NULL, 0 B) 2, NULL, 1 C) 0, NULL, 3 D) 2, NULL, 3

D) 2, NULL, 3

Which is true when you define a column as the primary key? A) The column is forced to be NOT NULL. B) The column is forced to contain a unique value for each row. C) An index is automatically created based on the column. D) All of the given options

D) All of the given options

Which of the following is true about creating indexes? A) MySQL automatically creates indexes for primary keys. B) MySQL automatically creates indexes for foreign keys. C) You can create an index that's based on more than one column. D) All of the given options

D) All of the given options

You use data definition language (DDL) to create, modify, and delete: A) tables only B) databases and tables only C) tables and indexes only D) databases, tables, and indexes

D) Databases, tables, and indexes

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 *

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

Which of the following statements about creating a table using the CREATE TABLE AS statement with a SELECT statement is not true? A) The table must not exist. B) Each column name in the SELECT clause must be unique. C) You can include any of the clauses of the SELECT statement. D) The definitions of primary keys, foreign keys, and indexes are included in the new table.

D) The definitions of primary keys, foreign keys, and indexes are included in the new table.

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

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

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