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: \ _ % /
_
