Database Midterm
A full outer join includes rows that satisy the join condition, plus
rows in both tables that don't satisfy the join condition
In a cross join, all of the rows in the first table are joined with all the
rows in the second table
In an UPDATE statement, the WHERE clause will
specify the condition a row must meet to be updated
To insert several rows from another table into a table, you can code an INSERY statemnt with a/an _______ in place of the VALUES clause.
subquery
A SELECT statement that includes aggregate functions is often called a/an _______ query.
summary
A common table expression(CTE) creates temporry _________ that can be used by a query that follows.
table
Which of the following is typically modeled after a real-world entity, such as an invoice or a vendor?
table
A relational database consists of one or more what?
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
Since the MERGE operation often involves updating existing rows and inserting new rows, the MERGE statemtn is somtimes referred to as what?
the upsert statament
___________ names can be used when you want to assign a temporary name to a table.
Correlation
If you code a column list in an INSERT statement that includes a column that has a default value, which keyword can you code in the VALUES clause to use the default value?
DEFAULT
Which of the following statments deletes every rows in the Vendors table?
DELETE Vendors;
What keyword do you code at the end of the ORDER BY clause to sort the recors that are retrieved by a SELECT statment in descending sequence?
DESC
What keyword can you code in the SELECT clause to prevent duplicate rows from being returned by a SELECT statement
DISTINCT
When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?
PaymentDate is NOT NULL AND InvoiceTotal >= 500
When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?
TOP 10 InvoiceNumber
You can use the UPDATE statement to modify one or more rows in the table named in the __________ clause
UPDATE
If you want to filter the result set that's returned by a SELECT statement, you must include which clause?
WHERE
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in which clause?
WHERE
To run a SELECT statement from an application program, you store the statement in the ________ object for the database connection.
command
The CUBE operator is similar to the ROLLUP operator except that
it adds summary rows for every combination of groups
Which of the following can you use to combine data from two or more tables into a single result set?
join
You can use a ______ in the ________ clause if you need to specify column values or search conditions other than the one named in the UPDATE clause.
join, FROM
What kind of join would you use 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?
left outer
Subqueries can be __________ within other subqueries
nested
The three main hardware components of a client/server system are the clients, the server, and the
network
When you code a column list in an INSERT statment, you can omit identify columns, columns that have default values, and columns that allow ________ values.
null
Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 20th row?
ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS
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
Which of the following won't improve the readability of your SQL statements?
Use comments to describe what each statemtns does
When you code a DELETE statement for one or more rows, which clause specifies which row or rows are to be deleted?
WHERE
When you code an UPDATE statemtn for one or more rows, the SET clause specifies the new data for the specified columns and the _______ clause specifies which row or rows are to be updated
WHERE
When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause
WHERE
A search condition in the __________ clause is applied before the rows are grouped while a search condition in the _______ clause isn't applied until after the grouping.
WHERE, HAVING
In a join, column names only need to be qualified where?
When the same names are used in both tables
When you use the SELECT INTO statement, the result set that's defined by the SELECT statment is ____________ a new table.
copied into
In the SQL statment shown beow, the name "v" is known as a?
correlation name or table alias
To overrride the order of precedence in an arithmetic expression, you can use
parentheses
What uniquely identifies each row in a table?
primary key
To retrieve or update the data in a database, the client sends a ______ to the database
query
What is the most comon type of reltionship between two tables?
one-to-many
Which wildcard character do you use in the SELECT clause to return all of the columns from the base table?
*
Which operator do you use to concatenate character strings in a string expression?
+
When coding search conditions, you can use which keyword to create compound search conditions?
AND
Which aggregate expression calculates the average value of the InvoiceTotal column, exculding null values?
AVG(InvoiceTotal)
The search condition of a WHERE clause consists of one or more
Boolean expressions
All of the aggregate functions ignore null values, except for which function?
COUNT(*)
Which aggregate expression gets the number of entries in the VendorName column, including null values?
COUNT(*)
Which aggregate expression gets the number of unique values in the VendorID column?
COUNT(DISTINCT VendorID)
Which operator can you use to test whether one or more rows are returned by a subquery
EXISTS
Which clause specifies the number of rows that should be retrieved after skipping the specifies number of rows?
FETCH
Correlation names are temporary table names assigned in which clause?
FROM
Which clause of the SELECT statement names the table that contains the data to be retrieved?
FROM
A subquery can be coded in a WHERE, FROM, SELECT, or _____________clause
HAVING
What keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?
LIKE
Which aggregate expression finds the latest date in the InvoiceDate column?
MAX(InvoiceDate)
Which aggregate expression finds the VendorName column that's last in alphabetical order
MAX(VendorName)
The order of precedence for the logical operators in a WHERE clause is
Not, And, Or
Which clause specifies the number of rows that should be skipped before rows are returned from the result set
OFFSET
A subquery is a/an ______ statement that's coded within another SQL statement.
SELECT
Which of the following statemnts isn't an action query?
SELECT
A union combines the rows from two or more what?
SELECT statments, result tables, queries
The six clauses of the SELECT statment must be coded in the following order:
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
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what?
a comma
When a column in a table is defined, what determines the kind of data it can store?
a data type
In many cases, a subquery can be restated as
a join
If you define a column as an identity column,
a number is generated for that column whenever a row is added to the table
A join that joins a table with itself is called
a self-join
Insert, Update, and Delete statements can be referred to as _____ queries.
action
Which functions perform a calculation on the values of a column from selected rows?
aggregate
If you omit the WHERE clause from a DELETE statement
all rows in the table will be deleted
A combination of column names and operators that evaluate to a single value is called
an expression
You can't update
an identity column
The processing that's done by the DBMS is typically referred to as
back-end-processing
Expressions coded in the WHERE clause
can use non-aggregate search conditions but cna't use aggregate search conditions
If you use ______ in the select list of a SELECT INTO statement, you must name the column since that name is used in the definition of the new table
calculated values
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
The interface between an application program and the DBMS is usually provided by the
data access API
A view is a SELECT statement that is stored with the
database
To store and manage the databases of the client/server system, each server requires what?
database management system (DBMS)
Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor -has its own ______ of SQL
dialect/variant
In most cases, the join condition of an inner join compares the primary key of one table to the _______ key of another table.
foreign key
To relate one table to another, a/an _____ in one table is used to point to the primary key in another table
foreign key
You can use the OVER clause with an aggregate function to
include the rows used to calculate the summary in the result set
When you code a union with the INTERSECT keyword to combine two result sets, the union
includes only rows thaqt occur in both result sets
A correlated subquery is one that
is executed once for each row in the outer query
If you assign a correlation name to one table in a join,
you have to use that name for the table in the query
You specify the conditions that must be met for a row to be deleted in the which clause
WHERE
A subquery that's used in a WHERE or HAVING clause is called what?
a subquery search condition
Which of the following is not a SQL DML statment?
Create Table
You can use the DELETE statement to delete one or more rows from the table you name in the ________ clause
DELETE
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword
DISTINCT
SQL statements that define the tables in a database are referred to as _______ statements.
Data Definition Language (DDL)
What SQL dialect does Microsoft SQL Server use?
Transact-SQL
The intersection of a row and a column is commonly called what?
cell
When you need to code multiple conditions in join, it's best to
code only join conditions in the ON clause
Unless you assign a _____, the column name in a result set is the same as the colum nme in the base table
column alias
When you code an ORDER BY clause, you can specify a
column name, alias, expression, or column number
To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like
commands, connection, and data readers