Database SQL Exam 1
To retrieve or update the data in a database, the client sends a _________ to the database.
query
This is typically modeled after a real-world entity, such as an invoice or vendor.
table
A relational database consists of one or more what?
tables
___________________ names can be used when you want to assign a temporary name to a table.
Correlation
Which of the following is not a SQL DML statement?
Create Table
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
Write the code for a DELETE statement that deletes every row in the Vendors table
DELETE Vendors;
To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause?
DISTINCT
When using the Query Designer, you select the tables and columns you want to use in the
Diagram pane
Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it.
If you want to use the Management Studio to modify the data for a table, you can right-click on the table and select what?
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
One limitation of the Query Designer is that you can't use it for
certain types of complex queries
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.
HAVING
If introduced as follows, the subquery can return which of the values listed below? FROM (subquery)
a table
In a join, column names only need to be qualified where?
when the same names are used in both tables
Which functions perform a calculation on the values of a column from selected rows?
Aggregate
Which of the following is not a valid column alias name?
Total Sales
What SQL dialect does Microsoft SQL Server use?
Transact-SQL
You use the UPDATE statement to modify one or more rows in the table named in the ________________ clause.
UPDATE
Which of the following recommendation WON'T improve the readability of your SQL statement?
Use commends to describe what each statement 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 statement 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
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause?
WHERE
If you define a column as an identity column,
a number is generated for that column whenever a row is added to the table.
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? WHERE 2 < (subquery)
a single 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
A subquery that's used in a WHERE or HAVING clause is called what?
a subquery search condition
When you code a column list in an INSERT statement, you can omit identify columns, columns that have default values, and columns that allow __________ values.
null
A full outer join includes rows that satisfy 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 of the
rows in the second table
In an UPDATE statement, the WHERE clause will
specifiy the condition a row must meet to be updated
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
A SELECT statement that includes aggregate functions is often called a/an ________________ query.
summary
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.
table
If you assign a correlation name to one table in a join,
you have to use that name for the table in the query
If you try to move a database file that's attached to a server, you'll get an error message that indicates the file is in use. To get around this, you need to ________________ the database from the server.
detach
A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with.
diagram
Although the American National Standards Institute publishes the specification for a standard SQL language, each DBMS vendor has its own _______ of SQL.
dialect/variant
A correlated subquery is one that
is executed once for each row in the outer query
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
Within the Management Studio, you can build a SQL statement without having to write your own code by using the ________________.
Query Designer
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
In Management Studio, if a statement returns data, that data is displayed in the __________ tab.
Results tab
The Query Editor of the Management Studio lets you enter and execute all types of
SQL statements
You specify the conditions that must be met for a row to be deleted in the which clause?
WHERE
To work with the data in a SQL server database from a .NET application, you can use ADO.NET objects like
commands, connections, and data readers
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 that occur in both result sets
SELECT VendorName AS Vendor, InvoiceDate AS DateFROM Vendors AS v JOIN Invoices AS iON v.VendorID = i.VendorID; (Refer to code example 4-1.) This type of join is called a/an
inner join
The CUBE operator is similar to the ROLLUP operator except that
it adds summary rows for every combination of groups
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
To store and manage the databases of the client/server system, each server required what?
database management system (DBMS)
Which of the following types of statements isn't an action query?
Select
Which keyword lets you control the number of rows that are returned by a query?
TOP, ALL, DISTINCT
In Management Studio, if you want to modify a column, or view more detailed information about the columns in a table, what tool would you use?
Table Designer
If you have the files for an existing SQL Server database, the easiest way to create the database is to ________________ those files to the database server.
attach
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
Which of the following statements best describes what this INSERT statement does? INSERT INTO InvoiceArchive SELECT * FROM Invoices WHERE TermsID = 1;
Adds all of the rows in the Invoices table that have 1 in the TermsID column to the InvoiceArchive table.
When coding search conditions, you can use which keyword to create compound search conditions?
AND
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(*)
Write an aggregate expression for the number of entries in the VendorName column, including null values
COUNT(*)
Write an aggregate expression for the number of unique values in the VendorID column
COUNT(DISTINCT VendorID)
When using the Query Designer, you can sort the sequence for the query in the
Criteria pane
You can use the DELETE statement to delete one or more rows from the table you name in the ________________ clause.
DELETE
The column name for the second column in the result set will be?
Date
To test whether one or more rows are returned by a subquery, you can use which operator?
EXISTS
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
Write an aggregate expression to find the latest date in the InvoiceDate column
MAX(InvoiceDate)
Write an aggregate expression to find the VendorName column that's last in alphabetical order
MAX(VendorName)
A subquery is a/an ______________ statement that's coded within another SQL statement.
SELECT
The six clauses of the SELECT statement 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
When you set the compatibility level of SQL Server 2016, you make sure it is compatible with a specific version of
SQL Server
A graphical tool that you can use to start and stop the database server is called what?
SQL Server Configuration Manager
When using the Query Designer, where is the generated SQL statement displayed?
SQL pane
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorState, COUNT(*) AS Column2 FROM Vendors GROUP BY VendorState HAVING COUNT(*) > 1;
The number of vendors in each state having more than one vendor
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.
If introduced as follows, the subquery can return which of the values listed below? WHERE InvoiceTotal > ALL (subquery)
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery)
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
Insert, Update, and Delete statements can be referred to as _______ queries.
action
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
If you use ________________ in the select list, 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
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
The intersection of a row and a column is commonly called what?
cell
When you need to code multiple conditions in a join, it's best to
code only join conditions in the ON clause
In Management Studio, the Query Editor uses the IntelliSense feature to automatically display ________________ lists that you can use to enter parts of the SQL statement.
completion
When you use the SELECT INTO statement, the result set that's defined by the SELECT statement is ________________ a new table.
copied into
The interface between an application program and the DBMS is usually provided by the
data access API
When a column in a table is defined, what determines the kind of data it can store?
data type
A view is a SELECT statement that is stored with the _________.
database
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
foreign
To relate one table to another, a/an ________ in one table is used to point to the primary key in another table.
foreign key
In many cases, a subquery can be restated as a/an ______________.
join
What 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
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
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax
lets you combine the join and search conditions
A string expression can consist of
one or more character columns, a combination of character columns and literal values, a combination of character columns and literal values
What is the most common type of relationship between two table.
one-to-many
What uniquely identifies each row in a table?
primary key
Since the MERGE operation often involves updating existing rows and inserting new rows, the MERGE statement is sometimes referred to as what?
the upsert statement
When you use Windows authentication to connect to a database, SQL Server
uses the login name and password that you use for your PC to authorize your connection
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?
DISTINCT
Which of the following isn't a common error when entering and executing SQL statements?
Forgetting to attach the required database
Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?
InvoiceTotal - CreditTotal - PaymentTotal / 10
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorID,SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2 FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0GROUP BY VendorID;
The total unpaid balance due for each VendorID
A join that joins a table with itself is called
a self-join
You can create a database diagram for
any combination of the tables in a database
To run a SELECT statement from an application program, you store the statement in the ________ object for the database connection.
command
A SQL Server database consists of two files: a database file and a ___________.
log file
Subqueries can be ________________ within other subqueries.
nested
The three main hardware components of a client/server system are the client, the server, and the __________.
network
