final exam Info 2410
Which uses the least amount of storage?
'ex' stored in a column of type varchar(20)
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON 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
- the number of rows in the Invoices table - the number of rows in the Vendors table - the number of rows in the Invoices table plus the number of rows in the Vendors table the answer is....none of the above
Which uses the least amount of storage?
-'example' stored in a column of type nchar(20) ---'exam' stored in a column of type nchar(20) 'ex' --stored in a column of type nchar(20) they all use -the same amount of storage- answer
All of the following values can be stored in a column that's defined as decimal(6,2), except
32492.05
If ExpirationDate contains a value that's equivalent to November 2, 2008 and the GetDate function returns a value that's equivalent to December 17, 2008, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution
45
Which of the following is true of database testing?
A database should always be tested before putting it into production and You should test each requirement and You should record what you are testing, how you are testing it, the expected result and the actual result
Which of the following best defines the term scalar function?
A function that operates on one row at a time
Which of the following best defines the term VIEW?
A query or filter that presents data as a particular user would need to see it
Which of the following best defines a disaster recovery plan?
A set of policies and procedures for recovering data after disasters
Each of the following statements about triggers is true except for one. Which one is it?
A trigger can have more than one batch.
Which of the following best defines the term parameter?
A value passed to the procedure by the user
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values: _________________________.
AVG(InvoiceTotal)
What would have to be done to the following query to make it run without error? SELECT CustomerCity, Count(CustomerKey) FROM Customer
Add the clause GROUP BY CustomerCity
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.
Which of the following best describes what would be returned by this query criteria? WHERE CustomerCity = 'Seattle' AND CustomerLastName LIKE 'M%'
All the customers in Seattle whose last names start with M
Which of the following best describes what would be returned by this query criteria? WHERE CustomerCity = 'Seattle' OR CustomerLastName LIKE 'M%'
All the last names of customers in Seattle and the last names of the customers starting with M
All of the aggregate functions ignore null values, except for the ______________________________ function.
COUNT(*)
Write an aggregate expression for the number of entries in the VendorName column, including null values: _________________________.
COUNT(*)
Which of the following statements can be coded in a batch with other statements?
CREATE TABLE
Which of the following code samples shows a trigger that would intercept a DELETE before it happens?
CREATE TRIGGER tr_RemoveCustomer ON Customer INSTEAD OF DELETE
Each of the following column attributes is a column constraint, except
DEFAULT
Which of the following is the portion of SQL used to create and alter data objects?
Data definition language
Which of the following is true of threat analysis?
Data entry accidents are often as dangerous as intentional attacks
Which of the following is the portion of SQL used to select and edit data?
Data manipulation language
SQL is what kind of programming language?
Declarative
Code example 13-1 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' (Refer to code example 13-1.) If the current date is 07/15/08, the earliest invoice due date for invoices with unpaid balances is 06/09/08, and the latest invoice due date for invoices with unpaid balances is 07/20/08, what will be printed by this script?
Earliest past due date: 06/09/08
How would you code the INSTEAD OF clause for a trigger that's fired whenever a view is deleted?
INSTEAD OF DROP_VIEW
Which of the following is true of an outer join?
It returns all the records from one table and only the matching records from the other table
Which of the following is true of an inner join?
It returns all the related records from both tables
Which of the following best describes the function of the term DISTINCT in a SELECT clause?
It returns only unique rows in the results
Which of the following best describes what the following SQL code does? EXEC sys.sp_addrolemember StudentRole, StudentUser
It uses a system stored procedure to add user StudentUser to the role StudentRole
What would be the effect of this UPDATE statement? UPDATE Customer SET CustomerLastName ='Smith'
It would change every customer's last name to Smith
What would be the effect of the following DELETE statement? DELETE FROM Session WHERE SessionDate < GETDATE( )
It would remove all the rows from session where the Sessiondate was less than the current date
Write an aggregate expression to find the VendorName column that's last in alphabetical order: _________________________.
MAX(VendorName)
Write an aggregate expression to find the oldest date in the InvoiceDate column: _________________________.
MIN(InvoiceDate)
Which of the following best describes the use of DISTINCT with a function as in SUM(DISTINCT <column>)?
Only distinct values are totaled; repeated values are ignored
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
Only includes summary rows. and Only adds summary rows for specified groups. and Allows you to use additional sets of parentheses to create composite groups.
What does the SQL clause ORDER BY CustomerLastName DESC do?
Orders the customers by last name Z-A
Which of the following is the best definition of a schema?
Ownership of database objects
In the code sample below, what are @StudentKey, @SessionDate and @SessionTimekey? CREATE PROCEDURE usp_SessionSignUp @StudentKey NCHAR(10), @SessionDateKey DATE, @SessionTimeKey TIME
Parameters
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 WITH ROLLUP
A collection of related permissions is called a:
Role
Given the following statements that declare a local variable and set its value, which of the following will cause an error? DECLARE @Example1 varchar(128) SET @Example1 = 'Invoices'
SELECT * FROM @Example1
Given the following statements that declare a local variable and set its value, which of the following will cause an error? DECLARE @Example1 varchar(128) SET @Example1 = 'Invoices
SELECT * FROM @Example1
A union combines the rows from two or more _______________________.
SELECT statements
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
which of the following is the language for manipulating data and objects in relational databases?
SQL
Which of the following requires a username and password?
SQL Server Authentication
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 (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.
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorState, COUNT(*) AS Column2 FROM Vendors GROUP BY VendorState HAVING COUNT(*) > 1
The number of vendors in each state having more than one vendor
Which of the following offers the best definition of authorization?
The process of assigning the permission to a validated user
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.
The qualified name of a column includes:
The table name and the column name separated by a dot
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 best defines threat analysis?
Threat analysis identifies the way a database can be harmed
Which of the following would be the correct syntax for a WHERE clause to return all the NULL values in the CustomerPhone column?
WHERE CustomerPhone IS NULL
Code example 13-2 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 eID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC IF @InvoiceTotal < 1000 otal < 1000 BREAK ELSE BEGIN SET @Total = @Total + @InvoiceTotal DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID END END PRINT 'Total: $' + CONVERT(varchar, @Total, 1) (Refer to code example 13-2.) What can cause the WHILE loop in this script to end other than the expression on the statement becoming true?
When the value of the @InvoiceTotal variable becomes less than 1000
Which of the following uses the Windows login to map to a SQL server login?
Windows Authentication
What is the error in the following SQL statement? SELECT CustomerCity, Count(CustomerKey) FROM Customer GROUP BY CustomerCity WHERE Count(CustomerCity) > 20
You cannot use a WHERE clause when the criteria contains an aggregate function and The GROUP BY clause is in the wrong place
If introduced as follows, the subquery can return which of the values listed below? WHERE InvoiceTotal > ALL (subquery)
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery)
a column of one or more rows
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
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)
a subquery can't be introduced in this way
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
Which of the following is a built in database role that allows a user to read any data in the database?
db_datareader and db_owner
Code example 6-2 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 (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
Code example 6-2 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 (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
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 (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
In most cases, the join condition of an inner join uses the _______________ operator to compare two keys.
equal
If FirstName contains Edward and LastName contains Williams, what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution
ewilliam
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
foreign key
If you want to join all of the rows in two tables whether or not they are matched, you use a/an _______________ join.
full
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-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS V JOIN Invoices AS I ON V.VendorID = I.VendorID (Refer to code example 4-1.) This type of join is called a/an __________________ join.
inner
A correlated subquery is one that
is executed once for each row in the outer query
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
Code example 6-1 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 (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
Code example 6-1 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 DES 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
Data validation is the process of
preventing errors due to invalid data
The WITH CHECK option of the CREATE VIEW statement
prevents an update from being performed through the view if it causes a row to no longer be included in the view
The WITH ENCRYPTION clause of the CREATE VIEW statement
prevents users from seeing the code that defines the view
You don't ever need to code a right outer join because
right outer joins can be converted to left outer joins by changing the order of the tables in the statement
A full outer join includes rows that satisfy the join condition, plus
rows in both tables that don't satisfy the join condition
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
A SELECT statement that includes aggregate functions is often called a/an _____________________________ query.
summary
When coding a column definition for a column that will contain a high percentage of null values, what attribute can you use to optimize the storage?
the SPARSE attribute
The scope of a temporary table is limited to
the database session in which it's defined
Code example 6-2 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 (Please refer to code example 6-2.) When this query is executed, each row in the result table will show
the largest invoice amount related to that row
Code example 6-2 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 (Please refer to code example 6-2.) When this query is executed, each row in the result table will show
the largest invoice amount related to that row
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON 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, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON 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
Which uses the least amount of storage?
they all use the same amount of storage
In the following code sample, from which table will all the records be shown whether they have related records in the other table or not? SELECT t.TutorKey, TutorLastName, SessionDateKey FROM Tutor t LEFT OUTER JOIN Session s ON t.TutorKey = s.TutorKey
tutor
Which of the following is a database account mapped to a server account?
user
In a join, column names need to be qualified only
when the same names are used in both tables
The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0
will fail because the SELECT statement returns two columns named VendorID
The statement CREATE VIEW Example2 AS SELECT InvoiceNumber, DATEDIFF(day,InvoiceDate,InvoiceDueDate) FROM Invoices
will fail because the second column isn't named
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
