TCMG 316 Mid Term

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

A SQL script contains one or more SQL statements.

true

You can use MySQL Command Line Client to work with any of the database instances on the database server.

true

Using parentheses controls the sequence of operations in the SQL statement.

True

Snippets contain data for creating a new database.

False

The * is used in a SQL statement to retrieve a single column in a table.

False

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

Select count(*) from a database table will return he number of records contained in the table.

True

The ALL and DISTINCT keywords specify whether or not duplicate rows are returned in the data set.

True

The Database Administrator (DBA) is responsible for maintaining, tuning, backing up, and restoring of data within the database.

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 most common clause to retrieve data is the SELECT clause.

True

Given five invoices with invoice totals of 20.00, 20.00, 30.00, 50.00, and 50.00, what values will the following function return for these rows? DENSE_RANK() OVER (ORDER BY invoice_total)

1, 1, 2, 3, 3

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

Before you 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 ANS: D

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 ANS: D

To define peer rows for an aggregate window function, you must include all but one of the following clauses. Which one is it?

BETWEEN

The DATE data type can store

Dates only

Which clause of the SELECT statement specifies the table that contains the data to be retrieved?

FROM

To enter a SQL statement in MySQL Workbench you would use the Output Tab.

False

Which of the following is not true if you use a string in a numeric expression?

If the string doesn't contain any numbers, an error occurs

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.

In which of the following ways can you not use a subquery in a SELECT statement?

In an ORDER BY clause as a column specification

To use a self join, you must do all but one of the following. Which one is it?

Include the DISTINCT keyword

what dose the GROUPING function do?

It evaluates an expression and returns 1 if the expression is null because it's in a summary row.

Which of the following statements is not true about the CHAR type?

It includes one byte that indicates the actual length of the string.

The order of precedence for the logical operators in a WHERE clause is:

Not, And, Or

Which of the following SELECT statements would you use to prevent duplicate rows from being returned?

ORDER BY vendor_id SELECT vendor_id FROM invoices WHERE DISTINCT

Which of the following statements best describes what the following SELECT statement does? SELECT MONTH(invoice_date) AS month, vendor_name, invoice_total, FIRST_VALUE(vendor_name) OVER (PARTITION BY MONTH(invoice_date) ORDER BY invoice_total DESC) AS 'first_value' FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id

Sorts the invoices by invoice total in descending sequence within each month and displays the name of the vendor with the largest invoice total for each row of that month.

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.

If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery)

a list of values

Which of the following SELECT clauses could you use to assign an alias to the invoice_total column?

a and b are both correct

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

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)

a table of values

The intersection of a row and column is commonly called a:

cell

a database driver is software that lets the:

data access model communicate with the database

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

each vendor with invoice totals over $500

The LIMIT phrase is used to create a column alias.

false

Which of the following is a valid type of join in a select statement:

inner join

SELECT vendor_name, COUNT(*) AS number_of_invoices, MAX(invoice_total - payment_total -credit_total) AS balance_due FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id WHERE invoice_total - payment_total - credit_total > (SELECT AVG(invoice_total - payment_total - credit_total) FROM invoices) GROUP BY vendor_name ORDER BY balance_due DESC

of invoices for each vendor that has a larger balance due than the average balance due for all invoices

(Refer to code example 7-2.) When this query is executed, each row in the result table will show

the largest invoice total related to that row

SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON 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

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 DATE_ADD function to remove the time values from the dates in each column

In a join, column names need to be qualified only

when the same column names exist in both tables

A function performs an operation in a SQL statement and does not return a value.

False

Which of the following types of data would you not store in a binary large object (BLOB) type?

text files

(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?

2, Null, 3

Most SQL errors are caused by incorrect syntax

True

If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? CONCAT(LOWER(LEFT('Edward',1)), LOWER(LEFT('Williams',7))) AS solution

ewilliam

Which of the following is not a common error when entering and executing SQL statements?

forgetting to connect to a database server

Before you can use MySQL Workbench to work with a MySQL database, you must:

open a connection to the database server

(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

What is the result of the function that follows? ROUND(2857.83457, -2)

2900

Which of the following types of real numbers is considered an exact numeric type?

DECIMAL

Common SQL statement operators include AND, OR, NOT, LIK E, and IN.

True

All of the following values can be stored in a column that's defined as DECIMAL(6,2), except

a. 32492.05 b. -246 c. 0 d. 2479.95 ANS: a

Which code returns the date in the format Friday, April 20th, 2019?

DATE_FORMAT('2019-04-20', '%W, %M %D, %Y')

Which of the following is not true about coding inner joins?

The join condition must consist of a single comparison.

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.

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 ORDER BY vendor_state

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

Which of the following can you not use in a join condition?

arithmetic operators

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.

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)

What is the result of the function that follows? REGEXP_INSTR('TR1-268-468R', '8-4')

7

What is the result of the function that follows? REGEXP_SUBSTR('TR1-268-468R', '[1-9]-[1-9]*[A-Z]')

8-468R

The Database Administrator (DBA) is responsible for which of the following:

A. Database resource monitoring B. Creating new databases C. Performance tuning of the database D. All of the above ANS: D

Which of the following is a part of a database:

A. Tables B. Indexes C. Data Attributes D. All of the above ANS: D

SELECT vendor_id, invoice_date, invoice_total, SUM(invoice_total) OVER() AS total_invoices SUM(invoice_total) OVER(PARTITION BY vendor_id FROM invoices ORDER BY invoice_total) AS vendor_total (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.

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

SQL statements are case sensitive

False

The LIMIT function joins two or more strings or literal values in an SQL statement.

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.

Which of the following is not typically used to parse a string into its individual components?

LENGTH

Which of the following types of statements does not modify the data in a table?

SELECT

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

It is the role of the Database Administrator (DBA) to maintain existing databases, tune them for better performance, and create new databases.

True

MySQL Workbench can be used to start and stop a database instance.

True

The order of precedence in an SQL statement is multiplication, division, modulo, addition, and subtraction.

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

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 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 uses the least amount of storage?

a. 'ex' stored in a column of type VARCHAR2(20) b. 'example' stored in a column of type VARCHAR2(20) c. 'exam' stored in a column of type VARCHAR2(20) d. they all use the same amount of storage ANS: a

Which uses the least amount of storage?

a. 'exam' stored in a column of type CHAR(20) b. they all use the same amount of storage c. 'ex' stored in a column of type CHAR(20) d. 'example' stored in a column of type CHAR(20) ANS: B

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

Expressions coded in the HAVING clause

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

Expressions coded in the WHERE clause

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

SELECT i.vendor_id, MAX(i.invoice_total) AS largest_invoice FROM invoices i JOIN (SELECT vendor_id, AVG(invoice_total) AS average_invoice FROM invoices GROUP BY vendor_id HAVING AVG(invoice_total) > 100) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Refer to code example 7-2.) When this query is executed, there will be one row

for each vendor with an average invoice total that's greater than 100

Numbers that don't include a decimal point are known as

integers

All but one of the following is true about the WITH ROLLUP operator. Which one is it?

it is part of standard SQL

Which of the following indicates the total number of digits that can be stored in a real number?

precision

Which of the following is not a hardware component of a client/server database system?

printer

When the following query is executed, the result set will include one row for each invoice with an invoice total that's greater than SELECT vendor_name, invoice_number, invoice_total FROM vendors JOIN invoices ON vendors.vendor_id = invoices.vendor_id WHERE invoice_total > ANY (SELECT invoice_total FROM invoices WHERE vendor_state = 'CA')

the minimum invoice total for vendors in California

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

a full outer join returns

unmatched rows from both the left and right tables

Which of the following is the default date format for MySQL?

yyyy-mm-dd

Which of the following types of relationships can a relational database not define?

zero-to-many