sql server admin final studying

If you want to use the Management Studio to modify the data for a table, you can right-click on the table and select the

Edit Top 200 Rows command

The Query Editor of the Management Studio lets you enter and execute all types of

SQL statements

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

You can create a database diagram for

any combination of the tables in a database

When you set the compatibility level of SQL Server 2012, you make sure it is compatible with a specific version of

SQL Server

SQL Server comes with complete documentation and reference information called ____________________________________.

Books Online

A database __________________ is a schematic drawing that shows you the relationships between the tables you're working with.

diagram

Which of the following isn't a common error when entering and executing SQL statements?

Forgetting to attach the required database

Before you can use the Management Studio to work with the objects in a database, you must ___________ the database files to an instance of SQL Server.

attach

Within the Management Studio, you can build a SQL statement without having to write your own code by using the ____________________________________.

Query Designer

The ________________________________________ is a graphical user interface for working with the objects in a SQL Server database.

Management Studio

One limitation of the Query Designer is that you can't use it for

certain types of complex queries

Which of the following types of statements isn't an action query?

Select

Which of the following types of SQL statements isn't a DML statement?

CreateTable

The SQL dialect that Microsoft SQL Server uses is called _______________.

Transact SQL

If you define a column as an identity column,

a number is generated for that column whenever a row is added to the table

To run a SELECT statement from an application program, you store the statement in the ____________ object for the database connection.

command

Insert, Update, and Delete statements can be referred to as ______________ queries.

action

SQL statements that define the tables in a database are referred to as _______________ statements.

DDL OR Data Definition Langauge

The three main hardware components of a client/server system are the clients, the server, and the __________________________________.

network

To retrieve or update the data in a database, the client sends a/an ________________ to the database.

query

Which of the following recommendations won't improve the readability of your SQL statements?

Use comments to describe what each statement does.

A/An __________________ uniquely identifies each row in a table.

primary key

Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own _______________ of SQL.

dialect OR variant

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

A view is a SELECT statement that is stored with the ______________.

database

The processing that's done by the DBMS is typically referred to as

back-end processing

The most common type of relationship between two tables is a/an ___________________ relationship.

one to many

The intersection of a row and a column is commonly called a/an _______________.

cell

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, it is given a data _________ that determines what kind of data it can store.

type

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

A relational database consists of one or more ___________.

tables

To relate one table to another, a/an __________________ in one table is used to point to the primary key in another table.

foreign key

To sort the records that are retrieved by a SELECT statement in descending sequence by InvoiceTotal, you code _________ after ORDER BY InvoiceTotal.

DESC

Which ORDER BY clause will cause 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

To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.

LIKE

If you want to filter the result set that's returned by a SELECT statement, you must include a/an ___________________ clause.

WHERE

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

Not, And, Or

To concatenate character strings, you use the _____________ operator in a string expression.

*

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

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

Unless you assign a/an _____________________________, the column name in the result set is the same as the column name in the base table.

column alias

To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.

DISTINCT

The ________________ clause of the SELECT statement names the table that contains the data to be retrieved.

FROM

To return all of the columns from the base table, you can code the ___________ operator in the SELECT clause.

*

When you code a SELECT statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

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 following is not a valid column alias name?

Total Sales

When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?

InvoiceTotal IN (0,1000)

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 a/an _______________ join.

left outer

When you use the implicit syntax for coding inner joins, the join conditions are coded in the ____________ clause.

WHERE

Code example 4-1
SELECT VendorName AS Vendor, InvoiceDate AS DateFROM Vendors AS V JOIN Invoices AS ION V.VendorID = I.VendorID;
(Refer to code example 4-1.) The column name for the second column in the result set will be __________________________.

Date

A union combines the rows from two or more _______________________.

SELECT statements

You don't ever need to code a right outer join because

right outer joins can be converted to left outer joins

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.

Code example 4-1
SELECT VendorName AS Vendor, InvoiceDate AS DateFROM Vendors AS V JOIN Invoices AS ION V.VendorID = I.VendorID;
(Refer to code example 4-1.) The name "V" is known as a/an ____________________________________.

table alias

Code example 4-1
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 __________________ join.

inner

When you need to code multiple conditions in a join, its best to

code only join conditions in the ON clause

Code example 4-2
SELECT VendorName, InvoiceNumberFROM Invoices LEFT JOIN VendorsON Invoices.VendorID = Vendors.VendorID;
(Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal

none of the above

A full outer join includes rows that satisfy the join condition, plus

rows in both tables that don't satisfy the join condition

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

Code example 4-2
SELECT VendorName, InvoiceNumberFROM Invoices LEFT JOIN VendorsON 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

Code example 4-2
SELECT VendorName, InvoiceNumberFROM Invoices LEFT JOIN VendorsON Invoices.VendorID = Vendors.VendorID;

the number of rows in the Vendors table

If you want to join all of the rows in two tables whether or not they are matched, you use a/an _______________ join.

full 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

can be used for more than two tables

If you assign a correlation name to one table in a join,

you have to use that name for the table

In a join, column names need to be qualified only

when the same names are used in both tables

Code example 4-1
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 join is coded using the _____________________________ syntax.

explicit

In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.

equal

___________________ names can be used when you want to assign a temporary name to a table.

Correlation

Code example 4-2SELECT VendorName, InvoiceNumberFROM Invoices LEFT JOIN VendorsON Invoices.VendorID = Vendors.VendorID;

(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 Vendors table

In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.

foreign

In a cross join, all of the rows in the first table are joined with all of the

rows in the second table

Expressions coded in the WHERE clause

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

Write an aggregate expression for the number of unique values in the VendorID column:

COUNT(DISTINCT VendorID)

By default, all duplicate values are included in the aggregate calculation, unless you specify the ___________________________ keyword.

DISTINCT

Which of the statements below best describes the result set returned by this SELECT statement?SELECT VendorID,SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2FROM InvoicesWHERE InvoiceTotal - PaymentTotal - CreditTotal > 0GROUP BY VendorID;

The total unpaid balance due for each VendorID

(Please refer to code example 5-1.) Although this query runs as coded, it contains this logical error:

The column name for the fifth column in the result set doesn't match the data.

You can use the OVER clause with an aggregate function to

include the rows used to calculate the summary in the result set

Expressions coded in the HAVING clause

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

The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it

All of the above. (Only increases summary rows, only adds summary rows for specified groups, allows you to use additional sets of parentheses to create composite groups)

All of the aggregate functions ignore null values, except for the ______________________________ function.

COUNT(*)

Which of the statements below best describes the result set returned by this SELECT statement?SELECT VendorState, COUNT() AS Column2FROM VendorsGROUP BY VendorStateHAVING COUNT() > 1;

The number of vendors in each state having more than one vendor

(Please refer to code example 5-1.) When this summary query is executed, the result set will contain one summary row for

each vendor with invoice totals over $500

Write an aggregate expression to find the VendorName column that's last in alphabetical order:

MAX(VendorName)

Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values:

AVG(InvoiceTotal)

Write an aggregate expression for the number of entries in the VendorName column, including null values:

COUNT(*)

A SELECT statement that includes aggregate functions is often called a/an _____________________________ query.

summary

The six clauses of the SELECT statement must be coded in the following order:

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding the ___________________________ operator.

ROLLUP

A subquery is a/an ______________ statement that's coded within another SQL statement.

SELECT

(Please refer to code example 6-1.) When this query is executed, the NumberOfInvoices column for each row will show the number

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

A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.

HAVING

(Please refer to code example 6-2.) In this query, the table named Top10 is coded as a/an ________________________.

CTE

(Please refer to code example 6-2.) When this query is executed, the result table will contain one row for

each vendor in the Top10 table

(Please refer to code example 6-2.) In this query, the table named Top10 is used as part of a ________________________.

join

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