SQL

Réussis tes devoirs et examens dès maintenant avec Quizwiz!

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 you TRUNCATE the number 17.99, you get a value of:

If you TRUNCATE the number 17.99, you get a value of: 20 18 17.5 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.

The ______________ function lets you test an expression and return one value if the expression is true and another value if the expression is false.

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.

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.

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

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

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

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

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

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

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

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

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.

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

Voir tous les ensembles d'études

SQL

Ensembles d'études connexes

Chapter 5: The circulation of the Oceans

chapter 5 understanding children from birth to age two

MISY Test 3

Multiplication Tables (10)

Oceanography: Chapter 7 Ocean Circulation

BUAD 332 Dynamic Study

Recruitment and Retention

Life, Health, & Law Outline - KY State Law (Chapter 15)

Electricity

H. ELA Connecting With Nature

Chapter 37: Management of Patients With HIV Infection and AIDS

Nutrition Test 3

LVN NCLEX REVIEW

PCI DSS

SAT

Climate Change - causes, renewable/non-renewable resources, ways to reduce climate change

ATI Questions 2

Spanish 1014: Final Review #2 combine, Lesson 5 Contextos Review (and final exam review)

econ ch 8

Online