CIT 111 Final Exam Prep
Which uses the least amount of storage? 'example' stored in a column of type VARCHAR2(20) 'exam' stored in a column of type VARCHAR2(20) 'ex' stored in a column of type VARCHAR2(20) they all use the same amount of storage
'ex' stored in a column of type VARCHAR2(20)
To return all of the columns from the base table, you can code the ________________ operator in the SELECT clause.
*
If you truncate the number 17.99 you get a value of: 18 17 20 17.5
17
Write an aggregate expression to calculate the average value of the invoice_total column, excluding null values: _________________________.
AVG(invoice_total)
Which of the following statements best describes what this INSERT statement does? INSERT INTO invoices_copy SELECT * FROM invoices WHERE terms_id = 1 Adds one row from the Invoices table to the Invoices_Copy table. Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table. 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. Updates all of the rows in the Invoices_Copy table that have 1 in the TermsID column to the rows in the Invoices table.
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 is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause Use the CAST function to convert the column values to numbers Add zeros to the column using +0 Use the LPAD function to pad the numbers with leading zeros All of the above
All of the above
In MySQL 5.6 the CHAR and VARCHAR2 data types use which character set? A) Latin1 B) UTF-8 C) ASCII D) ISO 8859 E) UTF-16
B) UTF-8
The __________ function returns the smallest integer that is less than or equal to the number. FLOOR TRUNCATE SIGN CEILING
FLOOR
You can combine inner and outer joins within a single _______________ statement.
SELECT
You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement.
SELECT
The six clauses of the SELECT statement must be coded in the following order: SELECT, FROM, GROUP BY, HAVING, WHERE, ORDER BY SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
To insert rows selected from one or more tables into another table with an INSERT statement, you code a/an ___________________________ in place of the VALUES clause.
SELECT, subquery
When you use the implicit syntax for coding joins, the join conditions are coded in the ____________ clause.
WHERE
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
The default date format for MySQL is _________________. mm/dd/yy mon-dd-yyyy yyyy-mm-dd yy/dd/mm
yyyy-mm-dd
All of the aggregate functions ignore null values, except for the ______________________________ function.
COUNT(*)
The DATE data type can store time as well as date information? True False
False
The ___________ function returns the string with any leading spaces removed.
LTRIM
The DATE data type can store dates only dates and times times only all of the above
dates only
If a string consists of two or more components, you can ________________ it into its individual components using the SUBSTRING_INDEX, SUBSTRING, and LOCATE functions.
parse
The _____________________ of a real number indicates the total number of digits that can be stored in the data type.
precision
If expiration_date contains a value that's equivalent to November 2, 2011 and the SYSDATE function returns a value that's equivalent to December 17, 2011, what will the exp_days column contain when this code is executed? DATEDIFF(expiration_date, SYSDATE()) AS exp_days -45 -1.5 45 1.5
-45
All of the following values can be stored in a column that's defined as DECIMAL(6,2), except -246.29 0 2479.95 32492.05
32492.05 [because it exceeds the total number of allowed characters in the DECIMAL statement]
In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.
=, equals, equal
The default date format for MySQL is: A) '2014-08-19' B) '8/19/14' C) 'August 19, 2014' D) '19-AUG-14'
A) '2014-08-19'
This expression returns what: SUBSTRING('Hello World', 5) A) 'o World' B) 'Hello' C) Error Msg: Too few arguments D) 'World' E) NULL
A) 'o World' *start at position 5*
Which of the following functions are useful when dealing with nulls? (Select all that apply) A) IFNULL B) NVL C) COALESCE D) ISNULL E) NVL2
A) IFNULL C) COALESCE
The ____________________ data types can store large, unstructured data such as text, images, sound, and video.
BLOB
DECIMAL(5,2) can hold which of the following values: A) 23500.78 B) -23500.78 C) 50.78 D) 50.783 E) 730.00 F) -730.00
C) 50.78 E) 730.00 F) -730.00
The best data type for a standard zip code (e.g. 83440) is: A) INT B) NUMBER(5, 0) C) CHAR(5) D) CHAR(25) E) VARCHAR(5) F) VARCHAR(25)
C) CHAR(5)
The _____________________ function is an ANSI-standard function that you can use to perform an explicit conversion.
CAST
Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type:
CAST(invoice_date AS DATETIME)
The __________ function returns the smallest integer that is greater than or equal to the number. FLOOR TRUNCATE SIGN CEILING
CEILING
The _________________ data type is used for fixed-length strings, which use the same amount of storage for each value regardless of the actual length of the string.
CHAR
The _________ function concatenates a string with the specified separator string added in between. LTRIM CONCAT CONCAT_WS SUBSTRING_INDEX
CONCAT_WS
Write an aggregate expression for the number of unique values in the vendor_id column: _________________________.
COUNT(DISTINCT vendor_id)
You use the ______________ function to add a specified number of date parts to a date.
DATE_ADD
Which code returns the date in the format Friday, April 20th, 2015? DATE_FORMAT('2014-04-20', '%W, %M %D, %Y') DATE_FORMAT('2015-04-20', '%m/%d/%y') DATE_FORMAT('2015-04-20', '%W, %M %D, %Y') DATE_FORMAT('2015-04-20', '%e-%b-%y')
DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')
If you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the ____________________ keyword in the VALUES clause of the INSERT statement.
DEFAULT
By default, MySQL automatically commits changes to the database immediately after each INSERT, UPDATE, and ______________ statement that you issue
DELETE
Write the code for a DELETE statement that deletes every row in the Invoices_Copy table:
DELETE FROM(Invoices_Copy)
To sort the rows that are retrieved by a SELECT statement in descending sequence by invoice_total, you code this ORDER BY clause: ORDER BY invoice_total ________________
DESC
By default, all duplicate values are included in the aggregate calculation, unless you specify the ___________________________ keyword.
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.
DISTINCT
Which of the following statements best describes what this SELECT statement does? SELECT invoice_number, invoice_date, CASE WHEN DATEDIFF(SYSDATE(), invoice_date) >= 30 AND DATEDIFF(SYSDATE(), invoice_date) < 60 THEN invoice_total ELSE 0 END AS "30-60", CASE WHEN DATEDIFF(SYSDATE(), invoice_date) >= 60 AND DATEDIFF(SYSDATE(), invoice_date) < 90 THEN invoice_total ELSE 0 END AS "60-90", CASE WHEN DATEDIFF(SYSDATE(), invoice_date) > 90 THEN invoice_total ELSE 0 END AS "Over 90" FROM invoices Displays three columns for each invoice with a value in the third column that indicates how many days have elapsed between the invoice date and the current date. Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date. Displays three columns for each invoice with one of these messages in the third column: 30-60, 60-90, or Over 90. Displays five columns for each invoice with a message in one of the last three columns based on how many days have elapsed between the invoice date and the current date.
Displays five columns for each invoice with the invoice total in one of the last three columns based on how many days have elapsed between the invoice date and the current date.
The ________________ clause of the SELECT statement specifies the table that contains the data.
FROM
If you want to store a decimal number you must use the FLOAT data type. True False
False
The __________ function lets you test an expression and return one value if the expression is true and another value if the expression is false.
IF
To search for null values, use the _________________ clause.
IS NULL
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 a/an _______________ join.
LEFT OUTER
Which of the following would return a maximum of 7 rows, starting with the 5th row? LIMIT 7, 4 LIMIT 4, 7 LIMIT 4 LIMIT 4, 6
LIMIT 4, 7
Use the _____________ function to find a character in a string.
LOCATE
Write an aggregate expression to find the vendor_name column that's last in alphabetical order: _________________________.
MAX(vendor_name)
Write an aggregate expression to find the oldest date in the invoice_date column: _________________________.
MIN(invoice_date)
The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type? Small, medium, large Yes, no, maybe Mushrooms, sausage, peppers Cash, credit, debit
Mushrooms, sausage, peppers
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values.
NULL
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
Write the code for a SELECT statement that uses the ROUND function to return the payment_total column with 1 decimal digit.
ROUND(payment_total, 1)
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
To format TIME values, you use the _______________ function.
TIME_FORMAT
SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON 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 Although this query runs as coded, it contains this logical error: The condition in the WHERE clause should be coded in the HAVING clause. The column name for the fifth column in the result set doesn't match the data. The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence. The condition in the HAVING clause should be coded in the WHERE clause.
The column name for the fifth column in the result set doesn't match the data.
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 column names in the column list are in the wrong sequence. There are too few items in the column list. There are too many items in the VALUES list. The number of items in the column list doesn't match the number in the VALUES list.
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_state, COUNT(*) AS column_2 FROM vendors GROUP BY vendor_state HAVING COUNT(*) > 1 The names of the vendors in each state The duplicate vendors from each state The number of vendors in each state The number of vendors in each state that has more than one vendor
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? 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 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 The unpaid balance for each invoice The total unpaid balance due for each vendor_id The total amount invoiced by each vendor_id The total of paid invoices for each vendor_id
The total unpaid balance due for each vendor_id
The EXTRACT function can be used to parse a date? True False
True
If you want to filter the rows that are returned by a SELECT statement, you must include a/an ___________________ clause.
WHERE
When you code a DELETE statement for one or more rows, the _________________ clause specifies which row or rows are to be deleted.
WHERE
When you code an UPDATE statement for one or more rows, the SET clause names the columns to be updated and the values to be assigned to those columns, and the ______________________ clause specifies the conditions a row must meet to be updated.
WHERE
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'
You can simulate a full outer join by using a union the ON clause a left outer join a self join
a union
A table ___________________ can be used when you want to assign a temporary name to a table.
alias
SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id The "v" in this example is known as a/an ____________________________________.
alias
When searching for floating point numbers such as the DOUBLE and FLOAT types, you'll want to search for ______________ values.
approximate
Expressions coded in the HAVING clause can use either aggregate search conditions or non-aggregate search conditions can use aggregate search conditions but can't use non-aggregate search conditions can use non-aggregate search conditions but can't use aggregate search conditions can refer to any column in the base tables
can use either aggregate search conditions or non-aggregate search conditions
Expressions coded in the WHERE clause can use either aggregate search conditions or non-aggregate search conditions can use aggregate search conditions but can't use non-aggregate search conditions can use non-aggregate search conditions but can't use aggregate search conditions must refer to columns in the SELECT 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 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
SELECT vendor_state, vendor_city, vendor_name, COUNT(*) AS invoice_qty, SUM(invoice_total) AS invoice_average FROM invoices JOIN vendors ON 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 city with invoice totals over $500 each vendor with invoice totals over $500 each city with invoice average over $500 each vendor with invoice average over $500
each vendor with invoice totals over $500
If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(CONCAT(LEFT(first_name,1), LEFT(last_name,7))) AS solution EWilliams EWilliam ewilliams ewilliam
ewilliam
SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id This join is coded using the _____________________________ syntax.
explicit
When MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion.
implicit
Numbers that don't include a decimal point are known as ____________________.
integers
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
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
matched rows in the second table
If an error occurs, each of the numeric functions returns a/an ____________ value.
null
The COALESCE function provides one way to substitute other values for __________________________ values.
null
Numbers that include a decimal point are knows as ____________________.
real numbers
A SELECT statement that includes aggregate functions is often called a/an _____________________________ query.
summary
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
SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id 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 uses the least amount of storage? 'example' stored in a column of type CHAR2(20) 'exam' stored in a column of type CHAR2(20) 'ex' stored in a column of type CHAR2(20) they all use the same amount of storage
they all use the same amount of storage
The DATETIME data type includes not only the date, but also a ________________________.
time
Which of the following isn't a valid column alias? total total sales total_sales "Total Sales"
total sales
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
Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the DATETIME data type? use the MONTH, DAYOFMONTH, and YEAR functions in the WHERE clause to search for just those components search for a range of dates that are greater than or equal to the date you're looking for, and less than the date that follows the date you're looking for use the DATE_FORMAT function in the WHERE clause to return a formatted string that only contains the month, day, and year use the DATE_ADD function to remove the time values from the dates in each column
use the DATE_ADD function to remove the time values from the dates in each column
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$'
