Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
255 Cards in this Set
- Front
- Back
expressions coded in the HAVING clause:
|
can use either aggregate search conditions or non-aggregate search conditions |
|
Assuming that all the table and column names are spelled correctly, what's wrong with the INSERT statement that follows?
INSERT INTO InvoiceCopy (VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDate, InvoiceDueDate) VALUES (97, '456789', 8344.50, 0, 0, 1, '2006-08-01') |
The number of items in the column list doesn't match the number in the VALUES list
|
|
In order to insert several rows into a table with an INSERT statement, you code a subquery in place of the VALUES clause.
|
True
|
|
If introduced as follows, the subquery can return which of the values listed below?
WHERE 2 < (subquery) |
a single value
|
|
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 define a column as an identity column, |
a number is generated for that column whenever a row is added to the table
|
|
By default, all duplicate values are included in the aggregate calculation, unless you specify the DISTINCT keyword
|
True
|
|
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 > 0 GROUP BY VendorID |
The total unpaid balance due for each VendorID |
|
Which of the following recommendations won’t improve the readability of your SQL statements? |
Use comments to describe what each statement does. |
|
A correlated subquery is one that |
is executed once for each row in the outer query |
|
Code example 6-1 (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
|
|
If introduced as follows, the subquery can return which of the values listed below? |
a single value
|
|
A null value is: |
A value in a column that is unknown
|
|
The six clauses of the SELECT statement must be coded in the correct order or it causes a syntax error. |
True
|
|
Code example 5-1
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it |
All of the above.
|
|
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
|
|
If you wanted to retrieve information from a column or row from a database table you would use the _____ statement. |
SELECT |
|
Referring to the following code, when this query is executed, the result table will contain one row for
WITH Top10 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC |
each vendor in the Top10 table
|
|
Which of the following types of statements isn’t an action query? |
Select
|
|
In a cross join, all of the rows in the first table are joined with all of the |
rows in the second table
|
|
Which column or columns in each table are foreign keys?
|
Orders: None; OrderLineItems: OrderID and ProductID; Products: None |
|
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
|
|
You do not have to apply the normal forms in sequence to normalize a data structure
|
False |
|
Implicit data type conversion is performed any time you mix values of different data types in an expression.
|
True |
|
The date/time data types can store |
all of the above |
|
The following code will cast a decimal field named InvoiceAmount to a varchar datatype using the CAST function:
CAST(InvoiceAmount AS varchar) |
True
|
|
Which uses the least amount of storage?
|
they all use the same amount of storage
|
|
Which of the following is not a good guideline for deciding when to create an index for a column?
|
The column is frequently updated |
|
When you define a foreign key constraint, you can specify all but one of the following. Which one is it?
|
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.
|
|
If RegistrationDate contains a value that’s equivalent to August 10, 2012, what will the Solution column contain when this code is executed? |
8 |
|
In order to insert several rows into a table with an INSERT statement, you code a subquery in place of the VALUES clause.
|
True
|
|
When you code an UPDATE statement for one or more rows the ______ clause specifies which row or rows are to be updated.
|
WHERE
|
|
All of the following values can be stored in a column that’s defined as decimal(6,2), except |
32492.05
|
|
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 |
reduces redundancy and makes maintenance easier
|
|
Using Figure 3 from the Figures Document:
Which column or columns in each table should be defined as the primary key? |
Orders: OrderID; OrderLineItems: OrderID and OrderSequence; Products: ProductID |
|
Referential integrity is the idea that if you delete a row in a primary key table, you must also delete any related rows in foreign key tables.
|
True
|
|
The COALESCE function provides one way to substitute constant values for null values.
|
True
|
|
If ExpirationDate contains a value that’s equivalent to June 2, 2012 and the GetDate function returns a value that’s equivalent to July 17, 2012, what will the Solution column contain when this code is executed? |
45
|
|
When you use the CREATE DATABASE statement to create a table you do not have to define the attributes and constraints for the columns of the table.
|
False |
|
When you code an UPDATE statement for one or more rows the _______ clause specifies the new data for the specified columns
|
SET
|
|
Which uses the least amount of storage? (varchar) |
'ex' stored in a column of type varchar(20) |
|
Which uses the least amount of storage? (nchar) |
they all use the same amount of storage
|
|
Foreach type of action query, a table can have
|
multipleAFTER triggers and one INSTEAD OF trigger
|
|
Thescope of a derived table is limited to
|
thestatement in which it’s defined
|
|
Usingthe following code: If the current date is 08/04/06, the earliest invoice duedate for invoices with unpaid balances is 06/09/06, and the latest invoice duedate for invoices with unpaid balances is 07/20/06, what will be printed bythis script? USE AP DECLARE @Date1 smalldatetime DECLARE @Date2 smalldatetime SELECT @Date1 = MIN(InvoiceDueDate), @Date2 =MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar,@Date1, 1) PRINT 'Latest past due date: ' + CONVERT(varchar,@Date2, 1) END ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar,@Date1, 1) ELSE PRINT 'No invoices past due' |
Earliestpast due date: 06/09/06
Latest past due date: 07/20/06 |
|
Datavalidation is the process of
|
preventingerrors due to invalid data
|
|
To return a message to a client, you use the SCALAR statement
|
false |
|
The WITH ENCRYPTION clause of the CREATE VIEW statement |
prevents users from seeing the code that defines the view |
|
Data validation is the process of |
preventing errors due to invalid data
|
|
You can invoke a table-valued user-defined function
|
anywhere you'd refer to a table or a view
|
|
Each of the following statements about triggers is true except for one. Which one is it?
|
A trigger can have more than one batch.
|
|
Given the following statements that declare a local variable and sets its value, which of the following will cause an error?
DECLARE @Example1 varchar(128) SET @Example1 = 'Invoices' |
SELECT * FROM @Example1
|
|
Parameters for stored procedures can be optional without a default value.
|
False
|
|
Which of the following statements can be coded in a batch with other statements? |
CREATE TABLE
|
|
Code example 14-1 (Refer to code example 14-1.) If the current date is 03/15/12, the earliest invoice due date for invoices with unpaid balances is 02/09/12, and the latest invoice due date for invoices with unpaid balances is 03/20/12, what will be printed by this script? |
Earliest past due date: 02/09/12
|
|
When passing a list of parameters to a stored procedure by name, you can omit optional parameters by |
omitting the parameter name and value from the list
|
|
Using the following code: What is the maximum value of the @Total variable? USE AP
SELECT * INTO #InvoiceCopy FROM Invoices
DECLARE @InvoiceID int, @InvoiceTotal money DECLARE @Total money SET @Total = 0
WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC IF @InvoiceTotal < 1000 BREAK ELSE BEGIN SET @Total = @Total + @InvoiceTotal DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID END END
PRINT 'Total: $' + CONVERT(varchar, @Total, 1) |
200,000.00 |
|
You typically use the return value of a stored procedure to
|
indicate to the calling program whether the stored procedure completed successfully
|
|
You can use the WHERE CURRENT OF clause to |
update or delete rows through a cursor |
|
To manually raise an error within a stored procedure you use the RAISERROR statement.
|
True
|
|
Using the following code: What can cause the WHILE loop in this script to end other than the expression on the statement becoming true?
USE AP
SELECT * INTO #InvoiceCopy FROM Invoices
DECLARE @InvoiceID int, @InvoiceTotal money DECLARE @Total money SET @Total = 0
WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC IF @InvoiceTotal < 1000 BREAK ELSE BEGIN SET @Total = @Total + @InvoiceTotal DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID END END
PRINT 'Total: $' + CONVERT(varchar, @Total, 1) |
When the value of the @InvoiceTotal variable becomes less than 1000
|
|
System stored procedures |
perform standard tasks on the current database are stored in the Master database and can change with each version of SQL Server |
|
How would you code the INSTEAD OF clause for a trigger that’s fired whenever a view is deleted? |
INSTEAD OF DROP_VIEW
|
|
A user-defined function
|
can return a single scalar value or a single table value |
|
Which of the following statements can be coded in a batch with other statements?
|
CREATE TABLE
|
|
Parameters for stored procedures and functions can be of any valid SQL Server data type except:
|
table |
|
Code example 14-2 (Refer to code example 14-2.) When does the expression on the WHILE statement in this script cause the loop to end? |
When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000 |
|
How would you code the AFTER clause for a trigger that's fired when a view is deleted?
|
AFTER OF DROP_VIEW |
|
How would you code the ON clause for a trigger that's fired after a table is deleted from the current database (assume that database_name is the name of the current database)?
|
ON DATABASE |
|
When you code a SELECT statement, you must code the four main clauses in the following order |
SELECT, FROM, WHERE, ORDER BY |
|
If written as follows, the subquery can return which of the values listed below?
WHERE InvoiceTotal > ALL (Subquery) |
a column of one or more rows |
|
Referring to the following code, when this query is executed, each row in the result table will show
WITH Top10 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC |
the largest invoice amount related to that row
|
|
To create a new table by using a SELECT statement you code the _____ clause.
|
INTO
|
|
If introduced as follows, the subquery can return which of the values listed below? |
a subquery can’t be introduced in this way
|
|
To sort the records that are retrieved by a SELECT statement in descending sequence by InvoiceTotal you code _________ after ORDER BY InvoiceTotal
|
DESC
|
|
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 |
|
True or False: the following code example shows the use of a correlated table expression
WITH Top10 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC |
True
|
|
Using the following code example:
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID This type of join is called a/an _________ join. |
INNER
|
|
You can use the OVER clause with an aggregate function to |
include the rows used to calculate the summary in the result set
|
|
If you define a column as an identity column, |
a number is generated for that column whenever a row is added to the table
|
|
Code example 4-2 (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 |
none of the above
|
|
Which of the following functions do not ignore null values?
|
COUNT()
|
|
Code example 6-1 (Please refer to code example 6-1.) When this query is executed, the result set will contain |
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 data type that is assigned to a column defines the type of information that can be stored there.
|
True
|
|
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 |
|
When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?
|
TOP 10 InvoiceNumber
|
|
When you code a column list into the INTO clause of an INSERT statement you can't include an identity column.
|
True
|
|
The order of precedence for the logical operators in a WHERE clause is
|
Not, And, Or
|
|
Code example 4-2 (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 |
|
You don't ever need to code a right outer join because:
|
right outer joins can be converted to left outer joins |
|
The most common type of JOIN is:
|
inner join |
|
The six clauses of the SELECT statement must be coded in the following order:
|
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY |
|
To test whether one or more rows are returned by a subquery you can use the HAVING operator.
|
False
|
|
Although this query runs as coded, it contains this logical error:
SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName |
The column name in the fifth column in the result set doesn't match the data |
|
If you define a column as an identity column, |
a number is generated for that column whenever a row is added to the table |
|
Which of the statements below best describes the result set returned by this SELECT statement? |
The total unpaid balance due for each VendorID |
|
Using the following code example:
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID The column name for the second column in the result set will be: |
Date |
|
To concatenate character strings, you use the __________ operator in a string expression.
|
+
|
|
The statements that work with the objects in the database is called: |
Data Definition Language
|
|
If you want to filter the result set that's returned by a SELECT statement, you must include a/an ____________ clause.
|
WHERE |
|
A full outer join includes rows that satisfy the join condition, plus
|
rows in both tables that don't satisfy the join condition |
|
Code example 5-1 (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
|
|
If you wanted to retrieve information from a column or row from a database table you would use the _____ statement. |
SELECT |
|
The key in a table that shares a relationship with a primary key in another table
|
A foreign key is
|
|
Using the following code example:
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID This type of join is called a/an _________ join |
INNER
|
|
If introduced as follows, the subquery can return which of the values listed below? |
a column of one or more rows
|
|
If written as follows, the subquery can return which of the values listed below?
SELECT (subquery) |
a single value |
|
The interface between an application program and the DBMS is usually provided by the |
data access API |
|
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) |
|
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 |
|
In a join, column names need to be qualified only |
when the same names are used in both tables
|
|
Which of these is an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values.
|
AVG(InvoiceTotal) |
|
Using the following code example:
SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID This join is code using which syntax? |
explicit SQL-92 |
|
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
|
|
Referring to the following code, when this query is executed, the result set will contain
SELECT VendorName, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > (SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal) FROM Invoices) GROUP BY VendorName ORDER BY BalanceDue DESC |
one row for each vendor that shows the largest balance due for any vendor's invoices, but only if that balance due is larger than the average balance due for all invoices.
|
|
Using the following code example:
SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal: |
None of the above |
|
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
|
|
The processing that’s done by the DBMS is typically referred to as |
back-end processing
|
|
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 (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 |
none of the above
|
|
Code example 5-1 The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it |
All of the above.
|
|
Which of the following is not a valid column alias name
|
Total Sales |
|
The ______ clause of the SELECT statement names the table that contains the data to be retrieved.
|
FROM |
|
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.
|
|
A hierarchical database provides only for parent/child relationships |
True
|
|
If you assign a correlation name to one table in a join,
|
you have to use the name for the table
|
|
Which of the statements below best describes the result set returned by this SELECT statement? |
The number of vendors in each state having more than one vendor
|
|
A join statement retrieves data from two or more tables into a single result set.
|
True |
|
The ______ is the computer that stores the file and databases of the system.
|
Server
|
|
Which of the following statements best describes what this DELETE statement does?
DELETE Vendors |
Deletes every row in the Vendors table
|
|
Which of the following types of SQL statements isn’t a DML statement? |
CreateTable
|
|
Expressions coded in the WHERE clause |
can use non-aggregate search conditions but can’t use aggregate search conditions
|
|
When you code an ORDER BY clause, you can specify a:
|
column name, alias, expression or column number
|
|
Assuming that all of the table and column names are spelled correctly, what’s wrong with the INSERT statement that follows? |
The number of items in the column list doesn’t match the number in the VALUES list.
|
|
ANSI stands for:
|
American National Standards Institute
|
|
If introduced as follows, the subquery can return which of the values listed below?
FROM (subquery) |
a table
|
|
A subquery can only be coded from a WHERE, FROM or SELECT clause |
False
|
|
A ______ consists of a SELECT statement that is stored with the database and can be managed independently of the applications that use them.
|
View
|
|
The result of a SELECT statement is called:
|
result set
|
|
A subquery is a SELECT statement that is coded within another SQL statement
|
True
|
|
A statement that would create a new database would look something like: |
CREATE DATABASE PH_TEST; |
|
Tables in relational database can be related to other tables by which relationship?
|
All of the above
|
|
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
|
|
Which of the following statements is true about the CONVERT and TRY_CONVERT functions? |
CONVERT returns an error if the expression can’t be converted, but TRY_CONVERT returns a NULL value.
|
|
The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and a log file.
|
True
|
|
To modify the structure of an existing table, you use the ______ statement.
|
ALTER TABLE
|
|
Using Figure 1 from the Figure Document:
Which of the diagrams best represents the relationship between a table of customers and a table of orders placed by customers? |
B |
|
To be in the third normal form, |
all of the above
|
|
Which of the following does not violate the referential integrity of a database? |
deleting a row in a foreign key table without deleting the related row in the related primary key table
|
|
The difference between the CAST function and the CONVERT function is that the _______ function accepts an optional style that lets you specify a format for the result.
|
CONVERT
|
|
When you create a script for creating a database, you can use the ________ keyword to signal the end of a batch and cause all the statements in the batch to be executed.
|
GO
|
|
To model a database on a real-world system, you typically represent each real-world entity as a table.
|
True
|
|
Which of the following diagrams best represents the relationship between a table of students and a table of classes for which the student is registered? |
|
|
A constraint that limits the values that can be stored in a column is called a/an _____ constraint.
|
check
|
|
Column lists of an INSERT statement cannot omit default values or columns that allow nulls.
|
False
|
|
Each of the following column attributes is a column constraint, except |
DEFAULT
|
|
If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? |
225.00
|
|
If FirstName contains “Edward” and LastName contains “Williams”, what will the Solution column contain when this code is executed? |
ewilliam
|
|
The precision of a decimal values indicates the total number of digits that can be stored in a decimal data type. |
True
|
|
Which of the diagrams best represents the relationship between a table of students and a table of classes for which the student is registered?
|
C
|
|
The most common type of relationship between two tables is a many-to-many
|
False
|
|
Typically, most database designers consider a database structure normalized if it's in the _______ normal form.
|
Third
|
|
Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that’s defined with the datetime data type and which might include time values? |
use the DatePart function to extract just the date from each datetime value
|
|
Unless you specify otherwise, the CREATE INDEX statement creates a clustered index for the specified column or columns.
|
False
|
|
A constraint that limits the values that can be stored in a column is called a reference constraint
|
False
|
|
When you create a script for creating a database, |
you need to create the referred to tables before you create the tables that refer to them
|
|
To be in the first normal form, each cell in a table must contain |
a single, scalar value
|
|
Which of the following statements is true? |
Implicit data type conversion is performed any time you mix values of different data types in an expression.
|
|
You use DDL to create, modify and delete the objects of a database
|
True
|
|
When you identify the data elements in a new database, you typically subdivide data elements into |
the smallest practical components
|
|
To store a date value without storing a time value, you can use the |
date data type
|
|
A constraint that enforces referential integrity between tables is called a reference constraint
|
True
|
|
The CREATE TABLE statement |
creates a new table in the current database
|
|
If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, the default setting is _________
|
NULL
|
|
If ZipCode is a varchar column that contains the value 93702, what will the Solution column contain when this code is executed? |
1
|
|
To enforce referential integrity, the database can:
|
A or B
|
|
If CustomerAddress contains “ 178 E Center Street ”, what will the Solution column contain when this code is executed? |
19
|
|
When you define a foreign key constraint, you can specify all but one of the following. Which one is it? |
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
|
|
Which data type is used to store whole numbers?
|
integer
|
|
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:
|
reduces redundancy and makes maintenance easier
|
|
An index is designed to improve performance when SQL Server does a search or a join based on a specific value in the indexed column
|
True
|
|
The WITH SCHEMABINDING clause of the CREATE VIEW statement |
all of the above
|
|
A series of SQL statements you can store in a file is called a script
|
True
|
|
The statement |
will fail because the SELECT statement returns two columns named VendorID
|
|
The scope of a derived table is limited to:
|
the statement in which it's defined
|
|
If you want to prevent users from examining the SQL code that defines a procedure, function or trigger, you code the CREATE statement with the HIDE option.
|
False
|
|
To repeatedly execute a statement or set of statements, you code a WHILE statement.
|
True
|
|
The scope of a temporary table is limited to:
|
the database session in which it's defined
|
|
The scope of a local variable is limited to:
|
the batch in which it's defined
|
|
If you delete a stored procedure, function, or trigger and then create it again |
you delete the security permissions assigned to the object
|
|
Stored procedures execute faster than an equivalent SQL script because stored procedures are precompiled.
|
True
|
|
If the current date is 04/04/12, the earliest invoice due date for invoices with unpaid balances is 02/09/12, and the latest invoice due date for invoices with unpaid balances is 03/20/12, what will be printed by this script?
|
Earliest past due date: 02/09/12
Latest past due date: 03/20/12 |
|
Using the following code: If the current date is 08/04/06, the earliest invoice due date for invoices with unpaid balances is 06/09/06, and the latest invoice due date for invoices with unpaid balances is 07/20/06, what will be printed by this script?
USE AP DECLARE @Date1 smalldatetime DECLARE @Date2 smalldatetime SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1) PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1) END ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1) ELSE PRINT 'No invoices past due' |
Earliest past due date: 06/09/06
Latest past due date: 07/20/06 |
|
The statement |
will fail because the second column isn’t named
|
|
When you store a BLOB in a database column with FILESTREAM storage, which of the following statements is not true? |
The BLOB must be smaller than 2GB.
|
|
To work with the data in a column that uses FILESTREAM storage from a .NET application, you must use all but one of the following. Which one is it? |
a SafeFileHandle object
|
|
For each type of action query, a table can have |
multiple AFTER triggers and one INSTEAD OF trigger
|
|
The statement |
will fail because the ORDER BY clause isn’t allowed in this view
|
|
If a user fetches a row through a cursor with the SCROLL_LOCKS concurrency option and a second user tries to fetch the same row, |
the second user can fetch the row but can’t update the row through the cursor until the first user has fetched a different row
|
|
What SQL Server function can you use to generate a globally unique value? |
NEWID
|
|
Which of the following statements will fail to fetch a row? |
FETCH RELATIVE FROM Ex_Cursor;
|
|
Given the following statements that declare a local variable and set its value, which of the following will cause an error?
|
SELECT * |
|
The statement |
will create an updatable view
|
|
Each of the following statements about triggers is true except for one. Which one is it? |
A trigger can have more than one batch.
|
|
To control the flow of execution based on a true/false condition, you code a/an:
|
All of the above
|
|
Each of the following is a benefit provided by using views except for one. Which one is it? |
You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.
|
|
The intersection of a row and a column is commonly called a/an _______________. |
cell
|
|
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause. |
DISTINCT
|
|
The most common type of relationship between two tables is a/an ___________________ relationship. |
one-to-many
|
|
When you code a DELETE statement for one or more rows, the _________________ clause specifies which row or rows are to be deleted. |
WHERE
|
|
The three main hardware components of a client/server system are the clients, the server, and the __________________________________. |
network
|
|
When you use the implicit syntax for coding inner joins, the join conditions are coded in the ____________ clause. |
WHERE
|
|
Insert, Update, and Delete statements can be referred to as ______________ queries. |
action
|
|
The ________________ clause of the SELECT statement names the table that contains the data to be retrieved. |
FROM
|
|
To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by a/an ______________. |
comma
|
|
To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask. |
LIKE
|
|
The MERGE statement is sometimes referred to as the _________________ statement. |
upsert
|
|
Write an aggregate expression to find the oldest date in the InvoiceDate column:
|
MIN(InvoiceDate)
|
|
A common table expression creates a temporary _____________ that can be used by a query that follows the CTE. |
table
|
|
A union combines the rows from two or more _______________________. |
SELECT Statements
|
|
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
|
|
Write an aggregate expression to find the VendorName column that’s last in alphabetical order: |
MAX(VendorName)
|
|
Write an aggregate expression for the number of unique values in the VendorID column: |
COUNT(DISTINCT VendorID)
|
|
When you code a column list in the INTO clause of an INSERT statement, you can’t include a/an _____________________ column. |
identity
|
|
In most cases, the join condition of an inner join uses the _______________ operator to compare two keys. |
equals
|
|
SQL statements that define the tables in a database are referred to as _______________ statements. |
DDL
|
|
To test whether one or more rows are returned by a subquery, you can use the ______________ operator. |
EXISTS
|
|
___________________ names can be used when you want to assign a temporary name to a table. |
Correlation
|
|
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values:
|
SELECT AVG(InvoiceTotal) FROM Invoices |
|
Write an aggregate expression to find the VendorName column that's last in alphabetical order:
|
SELECT MAX(VendorName) FROM Vendors
|
|
Write an aggregate expression to find the oldest date in the InvoiceDate column:
|
SELECT MIN(InvoiceDate) FROM Invoices
|
|
Write a query that returns the number of rows in a table named Vendors and puts it in a column named NumRows
|
SELECT COUNT(*) as NumRows FROM Vendors
|
|
Write an aggregate expression for the number of unique values in the VendorID column:
|
SELECT COUNT(DISTINCT VendorID) FROM Vendors |
|
Write the code for an ORDER BY clause that sorts a table into numeric sequence by the data in the PartCode column if that column contains whole numbers that are stored with the varchar data type.
|
ORDER BY CAST(PartCode As int)
|
|
Write the code for a DELETE statement that deletes every row in the Vendors table.
|
DELETE Vendors
|
|
Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function
|
CAST(InvoiceAmount AS varchar)
|
|
The IIF function determines the value it returns based on a/an ____________________ expression. |
Boolean
|
|
The most common type of relationship between two tables is called a/an _______________ relationship. |
one-to-many
|
|
The analytic functions provide a way to perform calculations on ____________________ result sets. |
ordered
|
|
Write the code for casting a decimal field named InvoiceAmount to a varchar datatype using the CAST function: __________________________________ |
CAST(InvoiceAmount AS varchar)
|
|
A constraint that limits the values that can be stored in a column is called a/an ______________________________ constraint. |
check
|
|
To maintain ______________, if you delete a row in a primary key table, you must also delete any related rows in foreign key tables.
|
referential integrity
|
|
If two tables have a many-to-many relationship, you need to define a/an _____________ table that relates their records. |
linking
|
|
You use DDL to create, modify, and delete the ___________________________ of a database. |
objects
|
|
You can use the ____________________ data type to store a date that includes a time zone offset. |
datetimeoffset
|
|
The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and a/an _______________________________________ file. |
log
|
|
To create a new table by using a SELECT statement you code the _______ clause
|
INTO
|
|
The rows in a table are kept in the sequence that’s based on its __________________________ index. |
clustered
|
|
When you code a table-level check constraint, the constraint can refer to data in more than one _____________________. |
column
|
|
The ___________ data types are used to store whole numbers.
|
Integer
|
|
A constraint that enforces referential integrity between tables is called a/an ______________________________ constraint. |
foreign key
|
|
____________________ characters can be used to encode the characters that are used in languages throughout the world. |
Unicode
|
|
When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow _________ values.
|
null
|
|
Typically, most database designers consider a database structure normalized if it’s in the ________________________ normal form. |
third
|
|
The _____________________ of a decimal value indicates the total number of digits that can be stored in a decimal data type. |
precision
|