Exam 1 Review

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices Group BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.IncoiceTotal) AS LargestInvoice FROM Invoices Join Top5 ON Invoices.VendorID = Top5.VendorID Group BY Invoices.VendorID Order BY LargestInvoice DESC; (Please refer to code example 6-2 above.) How many rows will there be in the result table when this query is executed? Question 4 options: 1 5 6 Unknown

5

When coding search conditions, you can use which keyword to create compound search conditions? Question 16 options: MERGE CREATE AND JOIN

AND

Some database designers write their own SQL statements for creating a database, its tables, and its indexes instead of using the Management Studio. Why? Question 24 options: All of these choices They want to have complete control over how the database is created. It's easier to modify your own script if you want to use it to create the same database for another database management system later on. The scripts generated by the Management Studio are harder to understand.

All of these choices

Which of the following best describes what would be returned by a query with the following criteria? WHERE CustomerLastName LIKE 'T%' Question 10 options: All rows with customer last names that start with T. All rows with customer last names that have a T and a percent sign. All rows with customer last names with a T in them. All rows with customer last names that end with T.

All rows with customer last names that start with T.

Which of the following best describes what would be returned by these query criteria? SELECT * FROM CustomersWHERE CustomerCity = 'Seattle' OR CustomerLastName LIKE 'M%' All the customers in Seattle and all the customers whose last names start with M. All the customers in Seattle whose last names start with M. All the customers in Seattle and all the customers whose last names contain an M. All the customers whose names start with M who are not in Seattle.

All the customers in Seattle and all the customers whose last names start with M.

Which of the following best describes what would be returned by these query criteria? WHERE CustomerCity = 'Seattle' AND CustomerLastName LIKE 'M%' Question 11 options: All the customers in Seattle whose last names start with M. All the last names of customers are not in Seattle and the last names of the customers starting with M. All the customers whose names start with M who are not in Seattle.

All the customers in Seattle whose last names start with M.

Which of the following clauses can you use to set the collation so UTF-8 encoding can be used to store string data? Question 1 options: COLLATE Latin1_General_100_CI_AS_UTF8 COLLATE SQL_Latin1_General_CP1_CI_AS_SC_UTF8 COLLATE SQL_Latin1_General_CP1_CI_AS_UTF8 COLLATE Latin1_General_100_CI_AS_SC_UTF8

COLLATE Latin1_General_100_CI_AS_SC_UTF8

Which of the following is not acceptable of aliasing a column called CustomerFirstName? Question 19 options: CustomerFirstName AS "First Name". CustomerFirstName <First Name>. CustomerFirstName AS [First Name]. CustomerFirstName [First Name].

CustomerFirstName <First Name>.

Each of the following column attributes is a column constraint, except Question 12 options: DEFAULT NOT NULL UNIQUE PRIMARY KEY

DEFAULT

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? Question 14 options: DEFAULT VALUE none of these choices NULL

DEFAULT

To sort records in descending order, follow the name of the sort key with the ____________________ operator.​

DESC

After the word FROM in a SELECT statement, you list any conditions (restrictions) that apply to the data you want to retrieve.​ Question 14 options: True False

False

In a nested query, the outer query is evaluated first.​ True False

False

When you query a database, there is a defined order in which the results are displayed.​ True False

False

When you create a script for creating a database, which keyword do you use to signal the end of a batch and cause all the statements in the batch to be executed? Question 15 options: EXECUTE START END GO

GO

A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause. Question 10 options: FROM ORDER BY GROUP BY HAVING

HAVING

The ____ clause is used to restrict the groups that will be included in a query result.​ Question 12 options: ​HAVING ​WHERE ​LIKE ​HAVE

HAVING

You can use the ____ operator followed by a collection of values to provide a concise way of phrasing certain conditions.​ Question 16 options: ​LIKE ​VALUES ​IN ​CONTAIN

IN

When multiple OR conditions are combined, it is easiest to use the SQL keyword(s): CONCAT BETWEEN IN LIKE

LIKE

Which of the following are common encodings used by SQL Server? Question 7 options: a) Unicode b) Latin1 c) ASCII d) both a and c

Latin1

Preceding a condition by the ____ operator reverses the truth of the original condition.​ ​REVERSE ​NOT ​OR ​NULL

NOT

(Refer to figure 10-1.) Which column or columns in each table should be defined as the primary key? Question 14 options: Orders: OrderID and OrderDateOrderLineItems: OrderID and OrderSequenceProducts: ProductID Orders: OrderIDOrderLineItems: OrderID, OrderSequence, and ProductIDProducts: ProductID and ProductName Orders: OrderIDOrderLineItems: OrderIDProducts: ProductID Orders: OrderIDOrderLineItems: OrderID and OrderSequenceProducts: ProductID

Orders: OrderIDOrderLineItems: OrderIDProducts: ProductID

You can use the Object Dependencies dialog box of the Management Studio to do all but one of the following. Which one is it? Question 21 options: Review the tables that depend on a specific table Review the tables that a specific table depends on Review the stored procedures and views that depend on a specific table Review the stored procedures and views that a specific table depends on

Review the stored procedures and views that a specific table depends on

Which of the following is an example for sorting the results by a column alias? Question 7 options: SELECT Name, CITY + ', ' + State + ' ' = ZIPCode AS AddressFROM VENDORSORDER BY ContactLastName + ', ' + ContactFirstName SELECT Name, CITY + ', ' + State + ' ' = ZIPCode AS AddressFROM VENDORSORDER BY Address, Name SELECT Name, CITY + ', ' + State + ' ' = ZIPCode AS AddressFROM VENDORSORDER BY State, City SELECT Name, CITY + ', ' + State + ' ' = ZIPCode AS AddressFROM VENDORSORDER BY Name

SELECT Name, CITY + ', ' + State + ' ' = ZIPCode AS AddressFROM VENDORSORDER BY Address, Name

Which of the following is the correct order of keywords for SQL SELECT statements? Question 13 options: SELECT,WHERE,FROM FROM, WHERE, SELECT WHERE, FROM,SELECT SELECT, FROM, WHERE

SELECT, FROM, WHERE

Which of the following is not a good guideline for deciding when to create an index for a column? Question 4 options: The column is frequently updated. The column contains a large number of unique values. The column is a foreign key. The column is frequently used in search conditions or joins.

The column is frequently updated.

Which of the following is true about ASCII characters? Question 10 options: They always require four bytes of storage They always require two bytes of storage The number of bytes depends on the encoding They always require one byte of storage

The number of bytes depends on the encoding

In the INSERT statement that follows, assume that all of the table and column names are spelled correctly, that none of the columns are identity columns, and that none of them have default values or accept null values. What's wrong with the statement?

The number of items in the column list doesn't match the number in the VALUES list.

Which of the following is true about supplementary characters? Question 18 options: The number of bytes depends on the encoding They always require two bytes of storage They always require one byte of storage They always require four bytes of storage

They always require four bytes of storage

The basic code structure for many SQL statements and objects can be found in which section of the SQL Server Management Studio? Question 17 options: Transact-SQL snippets DDL Query Editor Code Warehouse

Transact-SQL snippets

Aggregate functions apply to groups of rows.​ Question 18 options: True False

True

You can use the DELETE statement to delete one or more rows from the table you name in the ________________ clause.

WHERE

You specify the conditions that must be met for a row to be deleted in the which clause? Question 18 options: FROM ORDER BY HAVING WHERE

WHERE

Which of the following would be the correct syntax for a WHERE clause to return all the NULL values in the CustomerPhone column? Question 8 options: WHERE CustomerPhone < NULL WHERE CustomerPhone IS NULL WHERE CustomerPhone=NULL WHERE CustomerPhone IS NOT NULL

WHERE CustomerPhone IS NULL

The UTF-8 encoding can be used with Question 9 options: a) the char data type b) the varchar data type c) the nchar data type d) all of the above e) a and b only

a and b only

If introduced as follows, the subquery can return which of the values listed below? FROM (subquery) Question 9 options: a single value a subquery can't be introduced in this way a column of one or more rows a table

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? Question 19 options: a colon a comma a backslash a semi-colon

a comma

To enforce referential integrity, the database can Question 10 options: a) return an error instead of doing the requested action b) do the requested action and do the related changes to the related tables c) do the requested action and mark any orphans in related tables d) a or b

a or b

What do you use to uniquely identify each row in a table? Question 8 options: an index a primary key a foreign key a skeleton key

a primary key

What SQL Server feature can you use to generate a series of integer values that can be used by more than one table? Question 13 options: a cascading update a batch a constraint a sequence

a sequence

If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) Question 6 options: a single value a subquery can't be introduced in this way a column of one or more rows a table

a single value

To be in the first normal form, each cell in a table must contain Question 7 options: a non-redundant value a unique value a non-unique value a single, scalar value

a single, scalar value

SQL has special functions, called ____________________ functions, to calculate sums, averages, counts, maximum values, and minimum values.​

aggregate

Which of the following statements about the SPARSE attribute is true? Question 20 options: It optimizes the storage of null values for a column all of these choices You should only use it when a column contains a high percentage of null values It requires more overhead to retrieve non-null values

all of these choices

You use data definition language (DDL) to create, modify, and delete the ________________ of a database. Question 3 options: objects sequences all of these choices tables

all of these choices

You can't update Question 11 options: a column value an identity column one or more rows a row

an identity column

A table can contain information about two or more entities in Question 6 options: a normalized data structure a foreign key constraint an unnormalized data structure none of these choices

an unnormalized data structure

Which of the following are common character sets used by SQL Server? Question 19 options: a) Unicode b) Latin1 c) ASCII d) both a and c

both a and c

A regular identifier Question 8 options: all of these choices can be a Transact-SQL reserved keyword can contain a number sign (#) can contain spaces

can contain a number sign (#)

When you code a table-level check constraint, the constraint can refer to data in more than one Question 2 options: database value column table

column

After you identify and subdivide all of the data elements for a database, you should group them by the entities with which they're associated. The entities will become the tables of the database, and the elements will be come the Question 3 options: rows cells columns indexes

columns

When you subdivide a data element, you can easily rebuild it when necessary by ________________ the individual components. Question 16 options: assigning matching updating concatenating

concatenating

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices Group BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.IncoiceTotal) AS LargestInvoice FROM Invoices Join Top5 ON Invoices.VendorID = Top5.VendorID Group BY Invoices.VendorID Order BY LargestInvoice DESC; (Please refer to code example 6-2 above.) In this query, the table named Top5 is coded as a Question 3 options: common table expression (CTE) correlated subquery none of these choices noncorrelated subquery

correlated subquery

In SQL Server, what do you typically use to enforce referential integrity? Question 13 options: stored procedures custom functions triggers declarative referential integrity

declarative referential integrity

Which of the following does not violate the referential integrity of a database? Question 18 options: updating a foreign key with a value that doesn't match a primary key in the related table deleting a row in a foreign key table without deleting the related row in the related primary key table inserting a new row into a table with a foreign key that doesn't match a primary key in the related table updating a primary key in a primary key table without also updating the foreign keys for the related rows in all related tables

deleting a row in a foreign key table without deleting the related row in the related primary key table

To be in the third normal form, Question 2 options: each non-key column must depend on the entire primary key the non-key columns must not contain repeating values each non-key column must depend only on the primary key all of these choices

each non-key column must depend only on the primary key

When you use an UPDATE statement, you can code a subquery Question 12 options: in the WHERE clause to provide one or more values used in the search condition in the FROM clause to identify the rows that are available for update in the SET clause to return the value that's assigned to a column all of these choices

in the WHERE clause to provide one or more values used in the search condition

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices Group BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.IncoiceTotal) AS LargestInvoice FROM Invoices Join Top5 ON Invoices.VendorID = Top5.VendorID Group BY Invoices.VendorID Order BY LargestInvoice DESC; (Please refer to code example 6-2 above.) In this query, the table named Top5 is used as part of a Question 2 options: correlated subquery join noncorrelated subquery correlated table expression

join

If two tables have a many-to-many relationship, you need to define a _____________ table that relates their records. Question 15 options: linking unique composite primary

linking

The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and Question 11 options: an index file a key file none of these choices log file

log file

Subqueries can be ________________ within other subqueries. Question 8 options: restated joined nested grandfathered

nested

By default, what kind of index does the CREATE INDEX statement create? Question 5 options: composite non clustered referential clustered

non clustered

To normalize a data structure, what do you apply in sequence? Question 17 options: composite indexes normal forms none of these choices indexes

normal forms

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. Question 15 options: date time null string

null

(Please refer to code example 6-1 above.) When this query is executed, the result set will contain Question 5 options: one row for each invoice that has a larger balance due than the average balance due for all invoices 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 the invoice with the largest balance due for each vendor

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 called what? Question 5 options: one-to-one relationship primary relationship many-to-many relationship one-to-many relationship

one-to-many relationship

When you use the SELECT INTO technique to create tables Question 17 options: none of the two choices primary keys, foreign keys, and default values aren't retained only the column definitions and data are copied both of the two choices

primary keys, foreign keys, and default values aren't retained

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 Question 11 options: increases redundancy but makes the data more consistent reduces redundancy and makes maintenance easier reduces redundancy but makes maintenance more difficult increases redundancy but makes maintenance easier

reduces redundancy and makes maintenance easier

When you use the Management Studio to create a foreign key constraint, you specify the relationship between two tables as well as the rules for enforcing what? Question 25 options: column properties table design column values referential integrity

referential integrity

An index improves performance when SQL Server Question 14 options: searches a table none of these choices deletes a row from a table inserts a row in a table

searches a table

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. Question 13 options: summary query WHERE clause HAVING clause subquery

subquery

A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows. Question 7 options: expression table none of these choices aggregate

table

To model a database on a real-world system, you typically represent each real-world entity as a Question 20 options: column table cell document

table

You can set a collation for all but one of the following. Which one? Question 6 options: table column server database

table

Check constraints you create using DDL can be defined at the Question 22 options: column level table level none of these both table and column levels

table level

When you define a foreign key constraint, you can specify all but one of the following. Which one is it? Question 4 options: that the deletion of a related row in a primary key table should be cascaded down to the rows in the foreign key table that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table that no action should be taken and an error should be returned when a related row in a related table is deleted or updated that the updating of a primary key in a primary key table should be cascaded down to the rows in the foreign key table

that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table

WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices Group BY VendorID ORDER BY AvgInvoice DESC ) SELECT Invoices.VendorID, MAX(Invoices.IncoiceTotal) AS LargestInvoice FROM Invoices Join Top5 ON Invoices.VendorID = Top5.VendorID Group BY Invoices.VendorID Order BY LargestInvoice DESC; When this query is executed, each row in the result table will show Question 1 options: the largest invoice amount related to that row, but only if it's larger then the average for all invoices related to that row the average invoice amount related to that row the largest invoice amount related to that row the largest invoice amount related to that row, but only if it's larger than the average for all invoices

the largest invoice amount related to that row

When you identify the data elements in a new database, you typically subdivide data elements into Question 1 options: components that can be easily parsed each time you use them the smallest practical components the largest practical components

the smallest practical components

Typically, what form do most database designers consider a database structure to be normalized? Question 19 options: third fifth second fourth

third

In the following SQL query, what is the alias? SELECT SUM(standardPrice) as TotalPrice FROM Products WHERE productType = 'WOOD' SUM totalPrice Products standardPrice WOOD

totalPrice

When you use the Management Studio to create a check constraint, you can specify whether you want the constraint enforced for insert or ________________ operations. Question 23 options: invoice log update Both update and delete delete

update

When you code a script for creating a database, Question 16 options: you need to code the primary key column first in each table you need to create the indexes before you create the tables that they apply to you need to create the referred to tables before you create the tables that refer to them you can create the tables in whatever sequence you prefer

you need to create the referred to tables before you create the tables that refer to them

Which of the following operators can be used for a column computation?​ Question 9 options: ​** ​^ ​All of these choices ​+

​+

When using the LIKE operator, the ____ wildcard symbol represents any individual character.​ Question 20 options: ​\ ​_ ​% ​/

​_