TCMG 316 Quiz Review
The order of precedence for the logical operators in a WHERE clause is:
Not, And, Or
Which one of the following objects is not considered a DDL:
Select
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
If you define a column with a default value, that value is used whenever a row:
that does not include a value for that column is added to the table
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
the number of rows in the Vendors table
In a join, column names need to be qualified only
when the same column names exist in both tables
SQL statements are case sensitive.
False
Which of the following is not a common API:
DCCM
The * is used in a SQL statement to retrieve a single column in a table.
False
The LIMIT function joins two or more strings or literal values in an SQL statement.
False
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
Which SELECT clauses allows the user to return a specific number of row data matching the SQL statement parameters:
LIMIT
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
The Database Administrator (DBA) is responsible for maintaining, tuning, backing up, and restoring of data within the database.
True
Which of the following is not true when using MySQL Workbench to test a script that contains more than one SQL statement?
You cannot run all of the statements in the script at once
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
The interface between an application program and the DBMS is usually provided by the:
back end
The processing that is done by the DBMS is typically referred to as:
back-end processing
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
A database driver is software that lets the:
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
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)
The type of operation that retrieves data from two or more tables is called a:
join
Which of the following is not a valid column alias?
total sales
The following SQL statement will prevent duplicate rows from being included in the result set:Select DISTINCT desktop_os FROM operating_systems ORDER BY desktop_os
true
To run SQL statements at the command line, you can use the:
MySQL Command Line Client
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?
. UPDATE invoices SET invoice_total = invoice_total - (invoice_total * .01) WHERE vendor_id IN (SELECT vendor_idFROM vendorsWHERE vendor_state = 'AZ')
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
Which of the following statements best describes what this INSERT statement does? INSERT INTO invoices_copy SELECT *FROM invoices WHERE terms_id = 1
Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table
When you use the implicit syntax for coding inner joins, the join conditions
are coded on the WHERE clause
When you code a calculated value in a SELECT clause, the expression for the calculated value can include:
arithmetic operators and functions only
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
The SQL statements that work with the data in a database are called:
data manipulation language
The MySQL database server can also be referred to as the:
database engine
Before you can delete a row that has related rows in another table, you should
delete the related rows
SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty,SUM(invoice_total) AS invoice_average FROM invoices JOIN vendorsON 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
Which of the following types of diagrams can be used to show how the tables in a database are defined and related?
entity relationship
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
Which of the following is not a hardware component of a client/server database system?
printer
A single table in a relational database consists of:
rows and columns
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
To save your own SQL statement in MySQL Workbench so you to prevent having to type it in each time, you can create a new:
snippet
To start the server in MySQL Workbench, you can click the Start Server button in the:
tab that is displayed when you select Startup / Shutdown from the Navigator window
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 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
the number of rows in the Invoices table
To uniquely identify each row in a table, you can define a:
unique key or primary key
A full outer join returns
unmatched rows from both the left and right tables
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$'
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 (team_name) VALUES ('Angels')
2, Angels, 0
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)
2, NULL, 3
Which of the following statements about sorting the rows in a result set is not true?
A column that is 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 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) none of the above D) a and b are both correct*
Which of the following can you do when working with a table in MySQL Workbench?
A) view the data for the table B) edit the column definitions for the table C) view the column definitions for the table D) all of the above*
Which of the following SQL keywords are considered DML:
All of the above
Which of the following is a part of a database:
All of the above
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 partion.
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.
A function performs an operation in a SQL statement and does not return a value.
False
Comments cannot be used when writing SQL statements as the database may interpret them.
False
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
Which of the following would return a maximum of 7 rows, starting with the 4th row?
LIMIT 3, 7
Before you can use MySQL Workbench to work with a MySQL database, you must:
Open a connection to the database server
SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty,SUM(invoice_total) AS invoice_average FROM invoices JOIN vendorsON 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.
Common SQL statement operators include AND, OR, NOT, LIKE, and IN.
True
Common causes of errors when executing a SQL statement include: misspelling the table or column name, misspelling a keyword, omitting or not having proper quotation marks when using strings, and selecting the wrong database.
True
DDL stands for Data Definition Language and is used to work with objects within the database.
True
It is the role of the Database Administrator (DBA) to maintain existing databases, tune them for better performance, and create new databases.
True
Most SQL errors are caused by incorrect syntax.
True
MySQL Workbench can be used to start and stop a database instance.
True
Select * from employees where employee_name='Benjamin Franklin'; will return all records in the table associated with Benjamin Franklin.
True
Select count(*) from a database table will return he number of records contained in the table.
True
To access two or more tables in the database, you can use the SQL keyword join to have the data combined into a single result set.
True
Using the ORDER BY clause results in the rows being sorted in a specified sequence.
True
Which of the following recommendations won't improve the readability of your SQL statements?
Use comments to describe what each statement does.
When you use a subquery to insert rows from one table into another table, which of the following statements is not true?
You must omit the column list from the INSERT statement.
You can simulate a full outer join by using
a union
Which is not true about the USING keyword?
you code a USING clause in addition to the ON clause
Which of the following types of relationships can a relational database not define?
zero-to-many
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 types of statements does not modify the data in a table?
Select
When you run a SQL statement that contains a coding error, the error message that MySQL Workbench displays does not include:
The likely cause of the error
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(*) > 1ORDER 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
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
A SQL script contains one or more SQL statements.
True
A column alias can be used to substitute a column name in the result set.
True
A semicolon is required at the end of each SQL statement if a script contains more than a single SQL statement.
True
An API uses a piece of software called a database driver to communicate with the database.
True
In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it?
create a database diagram
The online version of the MySQL Reference Manual lets you:
A) use the links in the left sidebar to drill down to the information being looked for B) search for a specific word or phrase C) return to the Home page D) all of the above*
Which of the following statements best describes what this INSERT statement does? INSERT INTO invoices_copy SELECT *FROM invoices WHERE terms_id = 1
Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table.
Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements?
By default, the changes are automatically committed to the database.
How would you identify the many side of a relationship between two tables in a database?
By defining a foreign key in one table that is related to the primary key in the other table
Which of the following types of SQL statements is not a DML statement?
CREATE TABLE
Which of the following is not a database object in MySQL?
EER diagram
Which clause of the SELECT statement specifies the table that contains the data to be retrieved?
FROM
A database index contains the actual data value that is returned to the end user (client) application when using a select statement.
False
SQL notates Sequence Quantity Language while performing database administration.
False
The LIMIT phrase is used to create a column alias.
False
Using the LIMIT clause in a SQL statement will return all matching rows as the resulting data set.
False
When MySQL Workbench encounters an error, the error code, brief description, and directions on how to correct the error are displayed in the Output Tab.
False
When using MySQL Workbench to run a SQL script with multiple SQL statements, the results of each of the SQL statements that returns data will be displayed in a single Result Grid.
False
(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
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
When you code a SELECT statement, you must code the four main clauses in the following order:
SELECT, FROM, WHERE, ORDER BY
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"?
SmithSteve
Which of the following statements about creating a table using the CREATE TABLE AS statement with a SELECT statement is not true?
The definitions of primary keys, foreign keys, and indexes are included in the new table
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')
The number of items in the column list doesn't match the number in the VALUES list
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_totalFROM 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
The ALL and DISTINCT keywords specify whether or not duplicate rows are returned in the data set.
True
The Database Management System (DBMS) allows for the organizing, storing, retrieving, and manipulation of data from a single table or multiple tables.
True
The order of precedence in an SQL statement is multiplication, division, modulo, addition, and subtraction.
True
Using parentheses controls the sequence of operations in the SQL statement.
True
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
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?
WHERE
Which of the following WHERE clauses will return vendor names from A to C?
WHERE vendor_name < 'D'
Which of the following is a valid type of join in a select statement:
inner join
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
