SQL
If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(LEFT(first_name,1) + LEFT(last_name,7)) AS solution ewilliams ewilliam EWilliams EWilliam
ewilliam
????Code example 4-1 SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id (Refer to code example 4-1.) This join is coded using the BLANK syntax.
explicit
To override the order of precedence in an arithmetic expression, you can use BLANK.
parentheses
BLANK diagram can be used to show how the table in a database are defined and related
entity relationship (ER)
If a string consists of two or more components, you can BLANK it into its individual components using the SUBSTRING_INDEX, SUBSTRING, and LOCATE functions.
parse
Numbers that include a decimal point are knows as blank
real numbers
A correlated subquery is a subquery that is executed once for each BLANK in the main query.
row
application server
server that stores the business components
primary, foreign
tables are typically joined on the relationship between the ______ key in one table and a ______ key in the other table
If you define a column with a default value, that value is used whenever a row that doesn't include a value for that column is added to the table with a zero value for that column is added to the table in the table is updated is added to the table
that doesn't include a value for that column is added to the table
ORDER BY
the ______ ______ clause determines how the rows in the result set are sorted
SELECT
the ______ clause is always the first clause in a SELECT statement. It identifies the columns in the result set.
FROM
the ______ clause names the base table from which the query retrieves the data.
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) The total number of rows returned by this query must equal
the number of rows in the Invoices table
The DATETIME data type includes not only the date, but also a BLANK
time
A full outer join returns rows in the right table that don't satisfy the join condition rows in the left table that don't satisfy the join condition unmatched rows from both the left and right tables the Cartesian product of the two tables
unmatched rows from both the left and right tables
WHERE
the ______ clause specifies a search condition that's used to filter the rows in the base table. When this condition is true, the row is included in the result set.
You can use the DELETE statement to delete one or more rows from the table you name in the ________________ clause.
. DELETE
Which keyword lets you control the number of rows that are returned by a query?
. TOP b. DISTINCT c. ALL Correctd. all of the above
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 you're 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.
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.
.table
A database driver is software that lets the application program communicate with the database data access model communicate with the application program data access model communicate with the database application program communicate with the data access model
data access model communicate with the database
union
A ______ combines the result sets of two or more SELECT statements into one result set
self-join
A ______-______ joins a table to itself. Although rare, they are sometimes useful for retrieving data that can't be retrieved any other way.
To concatenate character strings, you use the ___________________ function in a string expression.
CONCAT
The BLANK function concatenates a string with the specified separator string added in between. CONCAT_WS LTRIM SUBSTRING_INDEX CONCAT
CONCAT_WS
The _________ function concatenates a string with the specified separator string added in between.
CONCAT_WS
Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type:
CONVERT(invoice_date, DATETIME)
All of the aggregate functions ignore null values, except for the _________ function.
COUNT
All of the aggregate functions ignore null values, except for the __________ function
COUNT
SQL statements that define the tables in a database are referred to as BLANK statements.
data definition language
A graphical system used to capture the nature and relationships among data is called a(n):
data model
DDL (Data Definition Language)
database administrators (DBAs) typically work with the ______ statements.
The DATE data type can store
dates only
The DATE data type can store dates only dates and times times only all of the above
dates only
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 BLANK
desc
data type
determines the type of information that can be stored in the column.
Numbers that include a decimal point are knows as
double
Comment
double dash "--"
To be in the third normal form,
each non-key column must depend only on the primary key
attribute
each record is identified by a field referred to as an ______ containing a unique value.
union
each result set in a ______ must return the same number of columns, and the corresponding columns in each result set must have compatible data types
tuples
each table is composed of records referred to as
Code example 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 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Please refer to code example 7-2.) When this query is executed, the result table will contain one row for
each vendor in the table named ia
commas
if a function requires two or more arguments, separate them with
qualify
if two columns in a join condition have the same name, you must ______ them with the table name so MySQL can distinguish between them.
USING, ON
if you can't get the ______ clause to work correctly because of the way your database is structured, you can always use the ______ clause instead.
column alias
if you don't specify a ______ ______ for a column that's based on a calculated value, MySQL uses the expression for the calculated value as the column name
explicit conversion
if you want to control how a conversion is performed you use this
When MySQL automatically converts one data type to another, it's known as a/an ______________________ conversion.
implicit
file-handling
in a ______-______ system, all of the processing is done on the clients
client/server
in a ______/______ system, the processing done by an application is typically divided between the client and the server.
local area network
in a simple client/server system, the clients and the server are part of a ______ ______ ______ (LAN)
When you code a subquery in a FROM clause, it returns a result set that can be referred to as an BLANK view.
inline
When you code a subquery in a FROM clause, it returns a result set that can be referred to as an ____________.
inline view
When you code a subquery in a FROM clause, it returns a result set that can be referred to as an _____________.
inline view
In MySQL Workbench, you can start or stop the MySQL database server by clicking the Start Server button in the BLANK window.
Navigator
In MySQL Workbench, you can start or stop the MySQL database server by clicking the Start Server button in the _______________________ window.
Navigator
This JOIN returns all of the rows from one or more tables in the JOIN.
Outer
Refer to code example. This type of join is called a/an ___________. SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id;
inner join
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This type of join is called a/an
inner join
one-to-one relationship
instance of one entity can be related to only one instance of a related entity
WHERE
instead of coding a join condition in the FROM clause, you can code it in the ______ clause along with any search conditions. In that case, you list the tables in the FROM clause separated by commas
Numbers that don't include a decimal point are known as ___________________
integers
Numbers that don't include a decimal point are known as ____________________.
integers
Numbers that don't include a decimal point are known as blank
integers
DML (Data Manipulation Language)
select, insert, update and delete statements fall under DML or DDL language
web server
server that stores web applications and web services
To save your own code in MySQL Workbench so that you do not have to type it each time, you can create a new
snippets
database driver
software used by an API used to communicate with the database
SQL
standard language for working with a relationship database
USE
statement that selects the current database
A SQL script is a file that contains one or more
statements
A _________ can return a list of values, a table of values or a single value.
subquery
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.
subquery
To insert several rows selected from another table into a table, you can code an INSERT statement with a/an ______________ in place of the VALUES clause.
subquery
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.
subquery select
A SELECT statement that includes aggregate functions is often called a/an BLANK query.
summary
implicit syntax
syntax for coding joins is referred to as the ______ ______,used prior to the SQL-92 standards, which introduced the explicit syntax.
You can use the AS keyword with the CREATE TABLE statement to create a copy of a table from a ____________________ statement.
SELECT
When you code a SELECT statement, you must code the four main clauses in the following order SELECT, WHERE, ORDER BY, FROM SELECT, ORDER BY, FROM, WHERE SELECT, FROM, WHERE, ORDER BY SELECT, FROM, ORDER BY, WHERE
SELECT FROM WHERE ORDER BY
Write an aggregate expression for the number of entries in the vendor_name column
SELECT COUNT(vendor_name) FROM vendors
Write an aggregate expression for the number of entries in the vendor_name column.
SELECT COUNT(vendor_name) FROM vendors
Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type
SELECT invoice_date CONVERT(invoice_date, DATETIME) AS FROM invoices
Write the code for converting the DATE data type in a column named invoice_date to the DATETIME data type: ______
SELECT invoice_date CONVERT(invoice_date, DATETIME) AS FROM invoices
Write the code for a SELECT statement that uses the ROUND function to return the payment_total column with 1 decimal digit.
SELECT payment_total ROUND(1)???? ROUND(payment_total,1)
The vendor ASC Signs moved from Fresno to Los Angeles. Use an UPDATE statement to change the vendor_city to Los Angeles and vendor_zip_code to 90025 for this vendor.
UPDATE vendor SET vendor_city='Los Angeles', vendor_zip_code='90025' WHERE vendor
Use this when you want to combine data from two or more tables using multiple queries.
Union
Which of the following are "set operators" (Select all that apply)
Union, Except, Intersect
schema
a ______ is a visual and logical architecture of a database created on a database management system
function
a ______ performs an operation and returns a value
If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery)
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE invoice_total > ALL (subquery) a table a subquery can't be introduced in this way a column of one or more rows a single value
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery)
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN (subquery) a subquery can't be introduced in this way a table a column of one or more rows a single value
a column of one or more rows
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what?
a comma
SQL script file
a file that contains one or more SQL statements
result table/set
a logical table returned by the SELECT statement that's created temporarily within the database.
composite key
a primary key that consists of two or more columns
one-to-many relationship
a relationship where one record in a table may have links to many records in another table.
The result of a SELECT statement is
a result set
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)
a single value
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery) a single value a column of one or more rows a table a subquery can't be introduced in this way
a single value
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)
a single value
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) a table a column of one or more rows a single value a subquery can't be introduced in this way
a single value
To be in the first normal form, each cell in a table must contain
a single, scalar value
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)
a subquery can't be introduced in this way
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) a subquery can't be introduced in this way a column of one or more rows a table a single value
a subquery can't be introduced in this way
enterprise
a system consisting of networks, mid-range systems, & mainframe systems, often spread throughout the country or world, it is commonly referred to as an ______ system
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)
a table
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) a subquery can't be introduced in this way a column of one or more rows a table a single value
a table
A subquery can return a list of values, ________.
a table of values or a single value
You can simulate a full outer join by using
a union
unique key
also called a non-primary key, it isn't the primary key but also uniquely identifies each row in a table
row
also called a record or tuple
natural
although ______ joins are easy to code, you'll usually want to avoid using them for production code.
AND, OR
although a join condition typically consists of a single comparison, you can include two or more comparisons in a join condition using the ______ and ______ operators.
outer join
an ______ ______ retrieves all rows that satisfy the join condition, plus unmatched rows in the left or right table
expression
an ______ can include any of the functions that are supported by MySQL
entity-relationship (ER) or entity-enhanced-relationship (EER)
an ______-______ diagram can be used to show how the tables in a database are defined and related.
API
application programming interface
queries
application software communicates with the DBMS by sending SQL ______ through the data access API
When searching for floating point numbers such as the DOUBLE and FLOAT types, you'll want to search for BLANK values.
approximate
Multiplication, Division, DIV, MOD, Addition, Subtraction
arithmetic operators in order of precedence
To return all of the columns from the base table, you can code the BLANK operator in the SELECT clause.
asterisk
Implicit conversion
automatic conversion
The processing that's done by the DBMS is typically referred to as the file server back-end processing the user interface front-end processing
back-end processing
When you code a subquery in a FROM clause, you must assign a/an _______ to it.
table alias
When you code a subquery in a FROM clause, you must assign a/an__________ to it.
table alias
A relational database consists of one or more
tables
If you define a column with a default value, that value is used whenever a row
that doesn't include a value for that column is added to the table
clients
the ______ are usually the PCs that are already available on the desktops throughout a company
LIMIT
the ______ clause simply returns a specified number of rows, regardless of the size of the full result set
In a SQL Editor tab of MySQL Workbench, you can do all but one of the following. Which one is it? navigate through database objects code SQL statements run SQL scripts create a database diagram
create a database diagram
inner join, outer join, union
create result sets that contain data from two or more tables by using an ______ ______, an ______ ______, or a ______
DDL (Data Definition Language)
create, alter and drop statements fall under DML or DDL language
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax
d. lets you combine inner and outer joins
When you have access to a table through MySQL Workbench, you can view both the column definitions for the table and the BLANK in the table.
data
When you have access to a table through MySQL Workbench, you can view both the column definitions for the table and the ______________________ in the table.
data
The interface between an application program and the DBMS is usually provided by the
data access API
The interface between an application program and the DBMS is usually provided by the back end data access API front end programmer
data access API
A database driver is software that lets the
data access model communicate with the database
network
the ______ consists of the cabling, communication lines, and other components that connect the clients and the servers of the system
DIV
the ______ operator returns the integer quotient of two numbers
MOD (modulo)
the ______ operator returns the remainder of a division of two integers
AND, OR
the ______ or ______ logical operators create compound conditions that consist of two or more conditions.
DATE_FORMAT
the _____________ function uses the percent sign (%) to identify a format code.
AVG
the average of non null values
scalability
the capability of a system, network, or process to handle a growing amount of work, or its potential to be enlarged to accommodate that growth
front
the client is typically referred to as the ______ end
SELECT
the column names in the final result set of a union are taken from the first ______clause.
service, engine
the database server can also be referred to as the database ______ or the database ______
back
the database server is referred to as the______ end
When you define a column with the DEFAULT attribute:
the default value is used if another value isn't specified when a row is added to the database.
SELECT, FROM, WHERE, ORDER BY, and LIMIT
the five main clauses of the SELECT statement:
MAX
the highest non null values
cell
the intersection of each row and column, representing a value
Code example 7-2 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 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Please 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, but only if it's larger than the average for all invoices the largest invoice total related to that row the average invoice total related to that row, but only if it's greater than 100 the average invoice total related to that row
the largest invoice total related to that row
When you run a SQL statement that contains a coding error, MySQL Workbench displays an error message that does not include
the likely cause of the error
MIN
the lowest non null values
inner
the most common type of join returns rows from both tables only if their related columns match
COUNT
the number of non null values
inner, outer
unlike an ______ join, an ______ join returns all of the rows from one of the tables involved in the join, regardless of whether the join condition is true
If you assign an alias to one table in a join, you have to qualify every column name in the query assign them to all of the tables use that alias to refer to the table throughout your 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 DATE_ADD function to remove the time values from the dates in each column
WHERE, ORDER BY, and LIMIT
which clauses are optional in the SELECT statement
When coding a query, you can add one or more summary rows to a result set that uses grouping and aggregates by coding the ______________ operator
with rollup
ad hoc
you can also join tables based on relationships not defined in the database, these are called ______ ______ relationships.
UNION
you can simulate a full outer join by using the ______ keyword to combine the results from a left outer join and a right outer join
multi-table
you can think of a ______-______ join as a series of two-table joins proceeding from left to right.
parentheses
you can use ________ to change the order of how an expression is evaluated.
comparison operator
you can use a ______ ______to compare any two expressions.
NATURAL
you can use the ______ keyword to create a natural join that joins two tables based on all columns in the two tables that have the same name
LEFT, RIGHT
you include the ______ or ______ keyword to specify the type of outer join you want to perform.
When you create a script for creating a database:
you need to create the referred to tables before you create the tables that refer to them
link or join
you should ______ tables when the data in the tables has a one-to-one or a many-to-one relationship.
The default date format for MySQL is _________________.
yyyy-mm-dd
The default date format for MySQL is _________________. mm/dd/yy yy/dd/mm mon-dd-yyyy yyyy-mm-dd
yyyy-mm-dd
subquery
A SELECT statement that's coded within another SQL statement.
table
where data is stored within a relational database
Which uses the least amount of storage?
'ex' stored in a column of type VARCHAR2(20)
Which uses the least amount of storage? 'exam' 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) 'example' stored in a column of type VARCHAR2(20)
'ex' stored in a column of type VARCHAR2(20)
single-line comment
--followed by text is an example of a _____-______ _____
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? expiration_date - SYSDATE() AS exp_days
-45
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? expiration_date - SYSDATE() AS exp_days 1.5 45 -1.5 -45
-45
If you TRUNCATE the number 17.99, you get a value of:
17
If you TRUNCATE the number 17.99, you get a value of: 20 18 17.5 17
17
All of the following values can be stored in a column that's defined as DECIMAL(6,2), except
32492.05
All of the following values can be stored in a column that's defined as DECIMAL(6,2), except -246.29 0 32492.05 2479.95
32492.05
If you wanted to summarize the data in two or more tables you could use:
A join with a summary Query
summary query
A query that contains one or more aggregate functions
calculated values
A result set can include ______ ______ that are calculated from columns in the table
Correlated subquery
A subquery that is executed once for each row in the main query. In contrase, an uncorrelated subquery is executed only once.
You can simulate a full outer join by using a left outer join a self join a union the ON clause
A union
You use _____ to test that a comparison condition is true for all values returned in a subquery
ALL
Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause add zeros to the column values using + 0 all of the above use the CAST function to convert the column values to numbers use the LPAD function to pad the numbers with leading zeros
ALL OF THE ABOVE
Which of these are JOIN conditions? (Select all that apply)
AND, OR
You can use the BLANK keyword in a WHERE clause to test whether a condition is true for one or more of the values returned by the subquery.
ANY
You can use the ________ keyword in a WHERE clause to test whether a condition is true for one or more of the values returned by the subquery.
ANY
You can use the _________ keyword in a WHERE clause to test whether a condition is true for one or more of the values returned by the subquery
ANY
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
Write an aggregate expression to calculate the average value of the invoice_total column, excluding null values: BLANK
AVG(invoice_total)
join condition, join
A______ ______ names a column in each of the two tables involved in the ______ and indicates how the two columns should be compared. In most cases, you use the equal operator to retrieve rows with matching columns. However, you can also use any of the other comparison operators
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 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 to the Invoices_Copy table and sets the terms_id column to 1 in each row. Adds one row from the Invoices table to the Invoices_Copy table. 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.
Adds all of the rows in the Invoices table that have 1 in the terms_id column to the Invoices_Copy table.
These perform calculations on the values in a set of selected rows
Aggregate Functions
Column Functions
Aggregate functions typically operate on the values in columns, they are sometimes referred to as this
Code example 4-1 SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id (Refer to code example 4-1.) The "v" in this example is known as a/an BLANK.
Alias
Which of the following is true about creating indexes?
All of the above
Which is true when you define a column as the primary key?
All of the above.
Aggregate Function
Allow you to do jobs like calculate averages, summarize totals, or find the highest value for a given column, and you'll use them in summary queries
To concatenate character strings, you use the ________ function in a string expression.
CONCAT
The ____________________ data types can store large, unstructured data such as text, images, sound, and video.
BLOB (Binary Large Object)
The data types can store large, unstructured data such as text, images, sound, and video.
BLOB (Binary Large Object)
Which of the following statements is true when you use MySQL Workbench to run INSERT, UPDATE, and DELETE statements? If autocommit mode is on you can rollback the changes by clicking on the Rollback button. By default, the changes are automatically committed to the database. By default, the changes are rolled back when you exit MySQL Workbench unless you commit them. If autocommit mode is on you can commit the changes by clicking on the Commit button.
By default, the changes are automatically committed to the database.
The BLANK function is an ANSI-standard function that you can use to perform an explicit conversion.
CAST
The _____________________ function is an ANSI-standard function that you can use to perform an explicit conversion.
CAST
The BLANK function returns the smallest integer that is greater than or equal to the number. SIGN CEILING FLOOR TRUNCATE
CEILING
The __________ function returns the smallest integer that is greater than or equal to the number.
CEILING
The BLANK 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 _________ 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 _________________ 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
All of the aggregate functions ignore null values, except for the BLANK function.
COUNT(*)
All of the aggregate functions ignore null values, except for the ______________________________ function.
COUNT(*)
Write an aggregate expression for the number of unique values in the vendor_id column: BLANK
COUNT(DISTINCT vendor_id)
Which of the following types of SQL statements is not a DML statement?
CREATE TABLE
Which of the following types of SQL statements is not a DML statement? INSERT CREATE TABLE UPDATE SELECT
CREATE TABLE
Write a SQL statement that creates a complete copy of the vendors table call vendors_backup.
CREATE TABLE vendors_backup AS SELECT * FROM vendors;
Like a join, a union combines data from two or more tables. But, instead of combining columns from base tables, a union by default, does not eliminate duplicate rows is coded in the ORDER BY clause combines the result sets of two or more SELECT statements combines columns from the same table
Combined the result set of two or more SELECT statements
A ________ creates a temporary table that can be used by a query that follows
Common Table Expressions
A/An ______________model is a representation of the entities, or objects, of the database including the tables, views, and stored programs.
EER model
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values.
NULL
This type of subquery is executed only once for each row processed by the outer query
Correlated
These are temp table names assigned in the FROM clause
Correlation
The DATE data type can store
DATES only
Which code returns the date in the format Friday, April 20th, 2015?
DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')
Which code returns the date in the format Friday, April 20th, 2015? DATE_FORMAT('2015-04-20', '%e-%b-%y') DATE_FORMAT('2015-04-20', '%m/%d/%y') DATE_FORMAT('2014-04-20', '%W, %M %D, %Y') DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')
DATE_FORMAT('2015-04-20', '%W, %M %D, %Y')
You use the _________________ to add a specified number of date parts to a date.
DATE_SUB
SQL statements that define the tables in a database are referred to as ----- statements.
DDL
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
Write the code for a DELETE statement that deletes every row in the Invoices_Copy table:
DELETE FROM Invoices_Copy
Write the code for a DELETE statement that deletes every row in the Invoices_Copy table: _______________________________________________________________
DELETE FROM Invoices_Copy
By default, all duplicate values are included in the aggregate calculation, unless you specify the BLANK keyword.
DISTINCT
By default, all duplicate values are included in the aggregate calculation, unless you specify the ___________ keyword
DISTINCT
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 BLANK keyword in the SELECT clause.
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________ keyword in the SELECT clause.
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code which keyword in the SELECT clause?
DISTINCT
When MySQL automatically converts one data type to another, it's known as a/an BLNAK conversion.
Data type???? implicit
DBA
Database Administrator
Which of the following is software used to create, maintain, and provide controlled access to databases?
Database management system
A subquery that is coded in the FROM creates a result set called a ______
Derived Table
Which of the following statements best describes what this SELECT statement does? SELECT invoice_number, invoice_date, CASE WHEN (SYSDATE() - invoice_date) >= 30 AND (SYSDATE() - invoice_date) < 60 THEN invoice_total ELSE 0 END AS "30-60", CASE WHEN (SYSDATE() - invoice_date) >= 60 AND (SYSDATE() - invoice_date) < 90 THEN invoice_total ELSE 0 END AS "60-90", CASE WHEN (SYSDATE() - invoice_date) > 90 THEN invoice_total ELSE 0 END AS "Over 90" FROM invoices
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.
This keyword is used to remove duplicate rows from the data set.
Distinct
When you use the ROLLUP operator, you cannot use the ______ keyword
Distinct
To test whether one or more rows are returned by a subquery, you can use the BLANK operator.
EXISTS
You use the BLANK to add a specified number of date parts to a date.
EXTRACT????? DATEADD??
In most cases, the join condition of an inner join uses the BLANK operator to compare two keys.
Equal
In most cases, the join condition of an inner join uses the ___ operator to compare two keys.
Equals
To test whether one or more rows are returned by a subquery, you can use the ______ operator.
Exists
A constraint is a rule in a database system that can be violated by users.
FALSE
. The ________ clause of the SELECT statement specifies the table that contains the data
FROM
The BLANK clause of the SELECT statement specifies the table that contains the data.
FROM
The most common source of database failures in organizations is:
Failure to implement a strong database administration function
T or F: A WHERE clause can use aggregate functions
False
In most cases, the join condition of an inner join compares the primary key of one table to the BLANK key of another table.
Foreign
Which of the following is not a common error when entering and executing SQL statements?
Forgetting to connect to a database
Which of the following is not a common error when entering and executing SQL statements? Forgetting to connect to a database Selecting the wrong database Misspelling a keyword Misspelling the name of a table
Forgetting to connect to a database
The _____ operator creates a summary row for each specified group
Grouping Sets
A subquery can be coded in a WHERE, FROM, SELECT or __________clause
HAVING
A subquery can be coded in a WHERE, FROM, SELECT, or BLANK clause.
HAVING
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.
Having
This clause specifies a search condition for a group or an aggregate.
Having
The BLANK function lets you test an expression and return one value if the expression is true and another value if the expression is false.
IF
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
You should create an _______________ when a column is used frequently in search conditions or joins. DKNF
INDEX
Data processed in a way that increases a user's knowledge is:
INFORMATION
Write an INSERT statement to insert a row in the general_ledger_accounts table using these values account_number: 82915 account_description: Main Street Books
INSERT INTO general_ledger (account_number, account_description) VALUES (82915, Main Street Books)
To search for null values, use the BLANK clause.
IS NULL
Which of the following types of statements does not modify the data in a table? INSERT UPDATE DELETE SELECT
SELECT
When you use the statement INNER JOIN, what syntax are you using?
Implicit
Four ways to introduce a subquery
In a Where clause as a search condition In a HAVING clause as a search condition In the FROM clause as a table specification In the SELECT clause as a column specification
The default storage engine for MySQL 5.5 and later. This engine supports foreign keys and transactions.
InnoDB
This keyword combines two result sets in union and includes only rows that occur in both sets
Intersect
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax lets you combine inner and outer joins is easier to read and understand lets you separate the join and search conditions is an older syntax that works with legacy code
Is an older syntax that works with Legacy code
In many cases, a subquery can be restated as a/an BLANK
JOIN
The type of operation that retrieves data from two or more tables is called a
JOIN
To retrieve rows in which an expression matches a string pattern called a mask, you can use the BLANK keyword followed by the mask.
LIKE
Which of the following would return a maximum of 7 rows, starting with the 4th row?
LIMIT 4, 7
Which of the following would return a maximum of 7 rows, starting with the 4th row? LIMIT 4, 7 LIMIT 7, 4 LIMIT 4 LIMIT 4, 6
LIMIT 7, 4
Use the BLANK function to find a character in a string.
LOCATE
Use the _______________ function to find a character in a string
LOCATE()
The BLANK function returns the string with any leading spaces removed.
LTRIM
The ______________ function returns the string with any leading spaces removed.
LTrim
To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.
Like
Write an aggregate expression to find the vendor_name column that's last in alphabetical order: BLANK
MAX(vendor_name)
Write an aggregate expression to find the oldest date in the invoice_date column: BLANK
MIN(invoice_date)
The MySQL Reference Manual can be found at the BLANK web site.
MySQL
___________________________ is a command-line tool that you can use for running SQL statements.
MySQL Command Line Client
NOT, AND then OR
MySQL evaluates a compound condition, it evaluates the operators in this sequence:
DML (Data Manipulation Language)
MySQL programmers typically work with the ______ statements
???Code example 4-2 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id (Refer to code example 4-2.) 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 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
NOT the number of rows in the vendors table NOT The number of rows in the invoice table plus the number of rows in the vendors table
The order of precedence for the logical operators in a WHERE clause is
Not, And, Or
The order of precedence for the logical operators in a WHERE clause is Not, And, Or Or, And, Not Not, Or, And And, Or, Not
Not, And, Or
he COALESCE function provides one way to substitute other values for __________________________ values.
Null
The latin1 character set uses how many bytes per character?The latin1 character set uses how many bytes per character?
ONE
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
The most common type of relationship between two tables is a/an _______________________ relationship.
One-to-many
Scalar Function
Operates on a single value and returns a single value
(PIC) Which column or columns in each table should be defined as the primary key?
Orders: OrderID; OrderLineItems: OrderID and OrderSequence; Products: ProductID
(PIC) Which column or columns in each table are foreign keys?
Orders: none; OrderLineItems: OrderID and ProductID; Products: none
You can combine inner and outer joins within a single _________statement
SELECT
Each table in a database should have a _____________________________ that uniquely identifies each row.
Primary
BLANK uniquely identifies each row in a table
Primary Key
When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding which operator?
ROLLUP
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)
A _____ query is able to loop through a result set and perform processing to return a final set
Recursive
Which of the following is NOT an advantage of database systems?
Redundant data
The _______ operator adds a summary row for each group specified
RollUp
A subquery is a/an BLANK statement that's coded within another SQL statement.
SELECT
A/An BLANK statement is used to retrieve selected columns and rows from a base table.
SELECT
An _________________ statement is used to retrieve selected columns and rows from a base table.
SELECT
Complete Exercise 3 from Chapter 4 by joining the vendors and general_ledger_accounts tables. What will you join these ON? In Workbench, right click on each table and select 'Table Inspector' and examine the Columns and Foreign keys tabs to find a relationship between these tables.
SELECT vendor_name, default_account_number AS default_account, account_description AS description FROM vendors v JOIN general_ledger_accounts gl ON v.default_account_number = gl.account_number ORDER BY account_description, vendor_name
Write the correct SELECT statement for Chapter 3 Exercise 6.
SELECT vendor_name, vendor_contact_last_name, vendor_contact_first_name FROM vendors ORDER BY vendor_contact_last_name, vendor_contact_first_name
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
The six clauses of the SELECT statement must be coded in the following order: SELECT, FROM, ORDER BY, WHERE, GROUP BY, HAVING 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, WHERE, GROUP BY, HAVING, ORDER BY
When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, FROM, WHERE, ORDER BY
Five main clauses
SELECT, FROM, WHERE, ORDER BY, LIMIT
CREATE
SQL DDL statement that creates a new database, table or index
ALTER
SQL DDL statement; changes the definition of an existing table or changes the structure of an existing index
DROP
SQL DDL statement; deletes an existing database, table, or index
INSERT
SQL DML statement; adds new rows to a table
UPDATE
SQL DML statement; changes the values in one or more rows of a table based on the condition you specify
DELETE
SQL DML statement; deletes one or more rows from a table based on the condition you specify.
SELECT
SQL DML statement; retrieves data from one or more tables
A SQL script is a file that contains one or more ____________.
SQL statement
A SQL script is a file that contains one or more ___________________.
SQL statements
DML & DDL
SQL statements can be divided into two categories:
. A/An ______ statement is used to retrieve selected columns and rows from a base table.
Select
You can combine inner and outer joins within a single BLANK statement.
Select
A HAVING clause can only refer to a column included in the _____
Select or group by
When we JOIN a table to itself, it's called a ________
Self-Join
To save your own code in MySQL Workbench so that you do not have to type it each time, you can create a new
Snippet
SQL
Structured Query Language
. A ______ is a SELECT statement codes within another SQL statement
Subquery
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.
Subquery
Nested Subquery
Subquery in a Subquery
Inline View
Subquery that's coded in the FROM clause returns a result set that can be referred to as this
A SELECT statement that includes aggregate functions is often called a/an ___________ query
Summary
A SELECT statement that includes aggregate functions is often called a/an ____________ query.
Summary
To format TIME values, you use the BLANK function.
TIME_FORMAT
A data model is a graphical system used to capture the nature and relationships among data.
TRUE
A modern database management system automates more of the backup and recovery tasks than a file system.
TRUE
A person is an example of an entity.
TRUE
A physical schema contains the specifications for how data from a conceptual schema are stored in a computer's secondary memory
TRUE
TRUE
TRUE OR FALSE: after you assign a table alias, you must use the alias in place of the original table name throughout the query
To delete all data from a table without deleting the definition for the table, you use the ______________ statement.
TRUNCATE TABLE
When a column in a table is defined, it is given a data ________________ that determines what kind of data it can store.
TYPE
A union combines the rows from two or more _______________________.
Tables
TRUE
True or False: You must code the clauses in the order shown or you'll get a syntax error
true
True or False: a file-handling system isn't a client/server system
true
True or False: line breaks, white space, indentation, and capitalization have no effect on the operation of a statement
DISTINCT
The ______ keyword prevents duplicate (identical) rows from being included in the result set.
Which of the following is not a good guideline for deciding when to create an index for a column?
The column is frequently updated.
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName;
The column name for the fifth column in the result set doesn't match the data
expression
a ______ is a combination of column names and operators that evaluate to a single value
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
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.
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.
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') There are too few items in the VALUES list. The column names in the column list are in the wrong sequence. The number of items in the column list doesn't match the number in the VALUES list. There are too many items in the column 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 number of vendors in each state that has more than one vendor
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? The corresponding columns in the result sets must have compatible data types. Each result set must have the same number of columns. The result sets may or may not have any duplicate rows. The result sets must be derived from different tables.
The result set must be derived from different tables
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 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 total amount invoiced by each vendor_id The unpaid balance for each invoice The total of paid invoices for each vendor_id The total unpaid balance due for each vendor_i
The total unpaid balance due for each vendor_i
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 total unpaid balance due for each vendor_id
null value
a ______ ______represents a value that's unknown, unavailable, or not applicable. It isn't the same as a zero or an empty string ('').
Java
To execute a SQL statement from a ______ application, you can use JDBC objects such as the Connection, Statement, and ResultSet objects
T or F: Keywords ANY and SOME are equivalent
True
T or F: You can use AND and OR operators to code compound search conditions in a HAVING clause
True
T or F: You can use CTEs with SELECT, INSERT, UPDATE and DELETE statements
True
T or F: You can use the IN operator only when the subquery returns a single column of value
True
Which of the following recommendations won't improve the readability of your SQL statements? Start each clause on a new line Break long clauses into multiple lines Use comments to describe what each statement does Indent continued lines
Use comments to describe what each statement does
Languages, menus, and other facilities by which users interact with the database are collectively called a(n):
User Interface
Which of the following types of data can be stored in a database?
Voice,Letters,Numbers ....ALL OF THE ABOVE
If you want to filter the result set that's returned by a SELECT statement, you must include which clause?
WHERE
When you use the inner joins, the join conditions are coded in the _____ clause.
WHERE
Which of the following WHERE clauses will return vendor names from A to C? WHERE vendor_name < 'D' WHERE vendor_name < 'C' WHERE vendor_name = D WHERE vendor_name = 'D'
WHERE vendor_name < 'D'
A subquery can be coded in a _______________
WHERE, FROM, SELECT or HAVING clause.
When coding a query, you can add one or more summary rows to a result set that uses grouping and aggregates by coding the BLANK operator.
WITH ROLLUP
When coding a query, you can add one or more summary rows to a result set that uses grouping and aggregates by coding the ____OPERATOR
WITH ROLLUP
cross join
a ______ ______ produces a result set that includes each row from the first table joined with each row from the second table, the result set is known as the Cartesian product of the tables
database server
a ______ ______, is a computer that stores the files and databases of the system and provide services to the clients of the system
aliases
When you code a self-join, you must use ______ for the tables, and you must qualify each column name with it
full outer join
When you use a ______ ______ ______, the result set includes all the rows from both tables.
When you use the implicit syntax for coding joins, the join conditions are coded in the BLANK clause.
Where
Which of the following is not true when you're 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. You can run each statement in the script by itself. When you run a script, the results are displayed in the Results tab.
You cannot run all of the statements in the script at once
Which is not true about the USING keyword? you code a USING clause in addition to the ON clause you use it to simplify the syntax for joining tables it can be used with inner or outer joins the join must be an equijoin, meaning the equals operator is used to compare the two columns
You code a USING clause in addition to the ON clause
cross joins
______ ______have few practical uses. As a result, you'll rarely, if ever, need to use one
LIKE, REGEXP
______ and ______ operators retrieve rows that match a string pattern, called a mask. The mask determines which values in the column satisfy the condition
horizontal
______ database scalability is adding more capacity by adding more machines
vertical
______ database scalability is adding more capacity to a single machine
schemas
a MySQL server can store tables in multiple databases these databases are sometimes referred to as ______
table alias
a ______ ______ is an alternative table name that's typically just a letter or two. This makes it easier to qualify the column names in the rest of the statement
literal value
a ______ ______ is any part of a statement or expression that is to be used exactly as it is, rather than as a variable or a Lingo element
natural join
a ______ ______ often yields unexpected results for complex queries. As a result, it's more common to use the ON or USING clause to join tables
To enforce referential integrity for a delete operation, a MySQL database can
a, b, or c
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) The name "v" is known as a?
a. both correlation name and table alias
When you use the SELECT INTO technique to create tables
a. a and b b. only the column definitions and data are copied
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName;
a. includes summary rows b. adds summary rows for specified groups c. allows you to use additional sets of parentheses to create composite groups Correctd. all of the above
join
a______ lets you combine columns from two or more tables into a single result set based on the conditions you specify, only those rows that satisfy the ______ condition are included in the result set
AS
after each column specification, you can code an ______ clause to specify the name for the column in the result set
A table BLANK can be used when you want to assign a temporary name to a table.
alias
A table _______ can be used when you want to assign a temporary name to a table.
alias
Unless you assign a/an BLANK, the column name in the result set is the same as the column name in the base table.
alias
When you code a subquery in a FROM clause, you must assign a/an BLANK to it.
alias
Which of the following is the way to sort a VARCHAR column that contains numbers in numeric sequence? In the ORDER BY clause
all of the above
column
also called a field, and it represents some attribute of the entity represented by the table
Code example 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 (Please refer to code example 7-1.) When this query is executed, the rows will be sorted by
balance_due in descending sequence
Code example 7-1 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 (Please refer to code example 7-1.) When this query is executed, the rows will be sorted by vendor_id and then by balance_due in descending sequence invoice_id balance_due in descending sequence vendor_id
balance_due in descending sequence
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 (Please refer to code example 7-1.) When this query is executed, the rows will be sorted by
balance_due in descending sequence
SELECT column FROM table
basic syntax of a SELECT statement
Java
before you can use ______ to work with MySQL, you must install a database driver.
Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This join is coded using what syntax?
both explicit and SQL-92
column alias
by default, a column in the result set is given the same name as the column in the base table. If that's not what you want, you can specify a substitute name, or ______ ______
eliminates
by default, a union ______duplicate rows
If you assign an alias to one table in a join, you have to
c. use that alias to refer to the table throughout your query
Expressions coded in the HAVING clause
can use aggregate search conditions but can't use non-aggregate search conditions
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
Expressions coded in the HAVING clause can use non-aggregate search conditions but can't use aggregate search conditions can use aggregate search conditions but can't use non-aggregate search conditions can refer to any column in the base tables can use either aggregate search conditions or non-aggregate search conditions
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
Expressions coded in the WHERE clause can use non-aggregate search conditions but can't use aggregate search conditions must refer to columns in the SELECT 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
In the collation, utf8_spanish_ci, the ci stands for:
case-insensitive
The intersection of a row and a column is commonly called a/an
cell
relational database
collection of data items with pre-defined relationships between them, every table shares at least one field with another table
When you code an ORDER BY clause, you can specify a column name, alias, or expression only column name or alias only column name, alias, expression, or column number column name or expression only
column name, alias, expression, or column number
primary key
column that uniquely identifies that record in the table
PHP, Java, C# or VB.NET
common options for accessing MySQL data
Before you can use MySQL Workbench to work with a SQL database, you need to open a BLANK to the database.
connection
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
Code example 7-2 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 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Please refer to code example 7-2.) When this query is executed, the result table will contain one row for each vendor each invoice in the table named ia each vendor in the table named ia each invoice
each vendor in the table named ia
Code example 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) When this summary query is executed, the result set will contain one summary row for
each vendor with invoice totals over $500
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) When this summary query is executed, the result set will contain one summary row for
each vendor with invoice totals over $500
The database server can also be referred to as the database
engine
A/An ______________ diagram can be used to show how the tables in a database are defined and related..
enhanced entity relationship
A person, place, an object , an event or concept about which the organization wishes to maintain data is called a(n):
entity
In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.
equal
If first_name contains Edward and last_name contains Williams, what will the solution column contain when this code is executed? LOWER(LEFT(first_name,1) + LEFT(last_name,7)) AS solution
ewilliam
many-to-many relationship
example of what relationship
You can use the SQL Editor tab to enter and BLANK one or more SQL statements.
execute
You can use the SQL Editor tab to enter and ______ one or more SQL statement
execute
BLANK is a command-line tool that you can use for running SQL statements.
execute current statement
Uncorrelated subquery
executed only once
Code example 4-1 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
JDBC (Java Database Connectivity).
for Java applications, the most common data access API for MySQL is
order of precedence.
for arithmetic expressions, the operations in an expression take place from left to right in the ______ ______ ______
Code example 7-2 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 ORDER BY average_invoice DESC) ia ON i.vendor_id = ia.vendor_id GROUP BY i.vendor_id ORDER BY largest_invoice DESC (Please refer to code example 7-2.) When this query is executed, there will be one row for each vendor with a maximum invoice total that's greater than 100 for each invoice with an invoice total that's greater than the average invoice total for the vendor and also greater than 100 for each vendor with an average invoice total that's greater than 100 for each vendor
for each vendor with an average invoice total that's greater than 100
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
foreign
If two tables have a one-to-many relationship, you need to add a _______________________ column to the table on the "many" side.
foreign key
In most cases, the join condition of an inner join compares the primary key of one table to the _____ of another table.
foreign key
To relate one table to another, a/an BLANK in one table is used to point to the primary key in another table.
foreign key
Which feature does the MyISAM storage engine not support?
foreign keys
You can't code which one of the following as part of a CREATE TABLE statement:
function-based indexes
??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 - 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
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)
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 invoice_total IN (0, 1000) NOT (invoice_total > 1000) invoice_total BETWEEN 0 AND 1000
invoice_total IN (0, 1000) TRYING
MySQL
is a popular open-source database that runs on all major operating systems and is commonly used for web applications.
DB2
is typically used for large, mission-critical systems that run on legacy IBM mainframe systems.
Oracle
is typically used for large, mission-critical systems that run on one or more Unix servers
Microsoft (MS) SQL Server
is typically used for small-to-medium-sized systems that run on one or more Windows servers.
data manipulation language (DML)
language that lets you work with the data in the database and the data definition language
data definition language (DDL)
language that lets you work with the objects in the database.
The BLANK data types can store large, unstructured data such as text, images, sound, and video.
large object (LOB)??? large object
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 what kind of join?
left outer
When you run a SQL statement that contains a coding error, MySQL Workbench displays an error message that does not include the likely cause of the error an error code brief description of the error
likely cause of the error
A database is an organized collection of ________ related data.
logically
referential integrity
makes sure that any changes to the data in the database don't create invalid relationships between tables
degrade performance
most LIKE and REGEXP phrases significantly ______ ______ compared to other types of searches, so use them only when necessary
The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type?
mushrooms, sausage, peppers
In MySQL Workbench, you can start or stop the MySQL database server by clicking the Start Server button in the __________ window
navigator
The three main hardware components of a client/server system are the clients, the server, and the
network
To normalize a data structure, you apply the ______________________ in sequence
normal forms
pseudocode
not real code but like a rough draft
auto increment column
numeric column whose value is generated automatically when a row is added to a table
many-to-many relationship
occurs when multiple records in a table are associated with multiple records in another table, usually not implemented in a relational database
Code example 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 (Please refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number
of invoices for each vendor that have a larger balance due than the average balance due for all invoices
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 (Please refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number
of invoices for each vendor that have a larger balance due than the average balance due for all invoices
foreign key
one or more columns in a table that refer to a primary key in another table
Code example 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 (Please refer to code example 7-1.) When this query is executed, the result set will contain
one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices
The most common type of relationship between two tables is a/an BLANK relationship.
one-to-many
join
operation whereby the data from two tables are brought into a single result set
To override the order of precedence in an arithmetic expression, you can use __________________.
parentheses
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
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 OR invoice_total >= 500 payment_date IS NOT NULL AND invoice_total >= 500 NOT (payment_date IS NULL AND invoice_total <= 500) payment_date IS NULL AND invoice_total > 500
payment_date IS NOT NULL AND invoice_total >= 500
The BLANK of a real number indicates the total number of digits that can be stored in the data type.
precision
The ____________ of a real number indicates the total number of digits that can be stored in the data type
precision
The _____________________ of a real number indicates the total number of digits that can be stored in the data type.
precision
index
provides an efficient way to access data from a table based on the values in specific columns
data access API
provides the interface between the application program and the DBMS
All of the following are primary purposes of a database management system (DBMS) EXCEPT:
providing an integrated development environment
To retrieve or update the data in a database, the client sends a/an BLANK to the database.
query
Numbers that include a decimal point are knows as ____________________.
real numbers
To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This
reduces redundancy and makes maintenance easier
constraint
referential integrity ______ state happens where relation refers to a key attribute of a different or same relation. However, that key element must exist in the table.
null value
represents a value that's unknown, unavailable, or not applicable.
The result of a SELECT statement is a result set a stored procedure a calculated value
result set
In a cross join, all of the rows in the first table are joined with all of the
rows from the second table
In a cross join, all of the rows in the first table are joined with all of the unmatched columns in the second table matched rows in the second table rows from the second table distinct rows in the second table
rows from the second table
???Code example 4-2 SELECT vendor_name, invoice_number FROM invoices LEFT JOIN vendors ON invoices.vendor_id = vendors.vendor_id (Refer to code example 4-2.) 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 Invoice table
COUNT(*)
the number of rows selected by query
front-end
the processing done by the client software is typically referred to as ______-______ processing
back-end
the processing that's done by the DBMS is typically referred to as ______-______ processing
Cartesian
the result set returned by a cross join is known as a ______ product.
WHERE
the search condition of a ______ clause consists of one or more Boolean expressions that result in a true, false, or null value.
When you identify the data elements in a new database, you typically subdivide data elements into
the smallest practical components
SUM
the sum of non null values
base table
the table referred to in the FROM column for a query
inclusive
the two expressions used in the BETWEEN phrase for the range of values are ______, meaning, the result set includes values that are equal to the upper or lower limit
Which uses the least amount of storage?
they all use the same amount of storage
Which uses the least amount of storage? 'ex' stored in a column of type CHAR(20) they all use the same amount of storage 'example' stored in a column of type CHAR(20) 'exam' stored in a column of type CHAR(20)
they all use the same amount of storage
Which uses the least amount of storage?
they all use the same amount of storage (Just know that 'VARCHAR' saves the amount of characters plus one. The others save a fixed amount.)
Typically, most database designers consider a database structure normalized if it's in the ________________________ normal form.
third
outer
this type of join returns rows from one table in the join even if the other table doesn't contain a matching row.
The clients, The network, and The server.
three hardware components of a client/server system
The DATETIME data type includes not only the date, but also a ________________________.
time
AS
to assign a column alias, you code the column specification followed by the ______ keyword and the new name
parentheses
to code a function, code the function name followed by a set of
single quotes ( ' ) or double quotes ( " )
to code a literal value for a string, enclose one or more characters within
qualified column name
to code a______ ______ ______, type the table name, followed by a period, followed by the column name
single quotes, double quotes
to include spaces or special characters in an alias, enclose the alias in ______ ______ or ______ _______
CONCAT
to join, or concatenate, two or more string columns or literal values, use the ______ function
after
to sort the rows in a union's final result set, code an ORDER BY clause ______ the last SELECT statement, this clause must refer to the column names assigned in the first SELECT clause.
database management system
to store and manage the databases of the client/server system, each server requires a ______ ______ ______ like MySQL
FROM
to test expressions that include arithmetic operators and functions you don't have to code a ______ clause.
IS NULL
to test for null values, use the ______ ______ clause
quotes
to use a string literal or a date literal in a comparison, enclose it in ______. To use a numeric literal, enter the number without ______.
SELECT
to use the DISTINCT or ALL keyword, code it immediately after the ______ keyword
Which of the following isn't a valid column alias? total total_sales "Total Sales" total sales
total sales
Connector/J and Connector/Net
two commonly used MySQL drivers
wide area network
two or more LANs that reside at separate geographical locations can be connected as part of a larger network such as a ______ ______ ______ (WAN)
When a column in a table is defined, it is given a data BLANK that determines what kind of data it can store.
type
right outer join
when you use a ______ ______ ______, the result set includes all the rows from the second, or right, 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 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_ADD function to remove the time values from the dates in each column 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
USING
use the ______ keyword to simplify the syntax for joining tables.
NOT
use the ______ operator to negate a condition.
NOT
use the ______ operator to test for an expression that's not in the list of expressions
BETWEEN
use the ______ phrase to test whether an expression falls within a range of values.
IN
use the ______ phrase to test whether an expression is equal to a value in a list of expressions.
asterisk (*)
used as a shorthand to indicate that all of the columns and rows should be retrieved
mysqli or PDO
used by PHP to access MySQL data
nested sort
using the ORDER BY clause to sort by more than one column, can be referred to as a ______ ______because one sort is nested within another.
USING
using the ______ clause tables must be joined by a column that has the same name in both tables
A subquery can return a list of values, a table of values, or a single BLANK
value
default value
value used if another value isn't provided when a row Is added to a table
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 '^SA' vendor_city REGEXP 'NA$'
vendor_city REGEXP 'NA$'
query
what a SELECT statement is commonly referred to as being
In a join, column names need to be qualified only when the same column names exist in both tables in outer joins in inner joins when the code is confusing
when the same column names exist in both tables
semicolon ;
when you code multiple SQL statements within a script, you must add a ______ at the end of each statement.
referential integrity
when you define a foreign key for a table in MySQL, you can have that foreign key enforce ______ ______
SELECT
when you run a ______ statement against one database, you can join to a table in another database if you have appropriate permissions. To do that, you must prefix the table name in the other database with the name of that database.
left outer join
when you use a ______ ______ ______, the result set includes all the rows from the first, or left, table
If you want to filter the rows that are returned by a SELECT statement, you must include a/an BLANK clause.
WHERE
Code example 4-1 SELECT vendor_name, invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i.vendor_id (Refer to code example 4-1.) This type of join is called a/an BLANK join.
inner
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.
A union combines the rows from two or more BLANK.
Select statements
Code example 6-1 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.) Although this query runs as coded, it contains this logical error: The three columns in the ORDER BY clause should use the DESC keyword so the city totals will be in the right sequence. The column name for the fifth column in the result set doesn't match the data. The condition in the WHERE clause should be coded in the HAVING clause. 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.
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 clause specifies the number of rows that should be retrieved after skipping the specified number of rows?
d. FETCH
DBMS
database management system
Code example 6-1 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 vendor with invoice average over $500 each city with invoice average over $500 each city with invoice totals over $500 each vendor with invoice totals over $500
each vendor with invoice totals over $500
The ENUM data type stores values that are mutually exclusive. Which choice is not appropriate for the ENUM type? yes, no, maybe mushrooms, sausage, peppers small, medium, large cash, credit, debit
mushrooms, sausage, peppers
If an error occurs, each of the numeric functions returns a/an BLANK value.
null
The COALESCE function provides one way to substitute other values for BLANK values.
null
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow ____________________________ values.
null
Each database includes tables, views, and routines. These are called database
objects
Code example 7-1 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 ?????(Please refer to code example 7-1.) When this query is executed, the number_of_invoices for each row will show the number 1 of invoices in the Invoices table of invoices for each vendor of invoices for each vendor that have a larger balance due than the average balance due for all invoices
of invoices for each vendor that have a larger balance due than the average balance due for all invoices
Code example 7-1 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 (Please refer to code example 7-1.) When this query is executed, the result set will contain one row for each invoice that has a larger balance due than the average balance due for all invoices one row for the invoice with the largest balance due for each vendor one row for each invoice for each vendor that has a larger balance due than the average balance due for all invoices one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices
one row for each vendor that shows the largest balance due for any of the vendor's invoices, but only if that balance due is larger than the average balance due for all invoices