Functional Questions
Merging Trigger Batches
SubDerFuge
Pivot/CTE
Windows Groupies
100
This function will change a varchar to a datetime2(0)
What is CAST or CONVERT?
100
This special table only exists within a trigger and contains all the new data created by the statement
What is INSERTED
100
The example below is of this type of query:

                            SELECT PatientID
                                  , FirstName
                                  , LastName
                            FROM Patient
                            WHERE PracticeID IN (SELECT PracticeID
                                                 FROM Practice
                                                 WHERE PracticeTypeID = 'DSO')
What is SUBQUERY or NESTED SUBQUERY or NON-CORRELATED SUBQUERY
100
A CTE that references itself an arbitrary number of times
What is a Recursive CTE
100
The window function that returns the rank of a row within a group, without any numeric gaps
What is DENSE_RANK
200
This function can return the number of days between two datetime2(4) variables
What is DATEDIFF
200
This type of trigger executes before the INSERT, UPDATE or DELETE happens
What is INSTEAD OF
200
The clause(s) (among SELECT, FROM, WHERE, GROUP BY, etc.) where you can have a derived table.
What is FROM
200
The word that starts a CTE
What is WITH
200
The operator that returns a row for all permutations of the columns listed in the GROUP BY
What is CUBE
300
The output of this statement: SELECT Friday = NULLIF(IIF(1=0,'Tuesday', 'Wednesday'), 'Thursday')
What is Wednesday
300
This type of trigger is capable of capturing schema changes
What is a DDL Trigger
300
The example below is of this type of query:
                            SELECT PatientID
                                   , FirstName
                                   , LastName
                                   , (SELECT MAX(AppointmentDate)
                                      FROM Appointment
                                      WHERE Appointment.PatientID = Patient.PatientID)
                            FROM Patient
                            WHERE PracticeID = 43
What is CORRELATED SUBQUERY
300
The clause (among SELECT, FROM, WHERE, GROUP BY, etc.) where you use the PIVOT operator.
What is FROM
300
The window function that returns a value from the next row in sequence
What is LEAD
400
The function that when placed in the blank below returns the number of bytes used by @PracticeName
__________________(@PracticeName)
What is DATALENGTH
400
The two commands that can follow a WHEN MATCHED clause of the MERGE statement
What is UPDATE and DELETE
400
Name two of the 4 constructs discussed in class so far that can be a drop-in replacement for a derived table.
What is CTE, VIEW, TEMPORARY TABLE, TABLE VARIABLE
400
The operator that creates a new column for values held in a set of rows.
What is PIVOT
400
The window function that splits a query result into a specified number of equally sized sub groups and returns a numeric value indicating which group each row is in
What is NTILE
500
This function, new in SQL 2012, allows datetimes to be formatted using .NET format strings and cultures
What is FORMAT
500
Daily Double
The condition, exhibited by Bobby Tables, can occur when user input is used to dynamically create a SQL Query
500
The word that needs to be added to the query below to get it to run without throwing an error:
                            SELECT SalesOrderID
                                   , (SELECT Name
                                      FROM Sales.SalesTerritory
                                      WHERE TerritoryID = TerritoryID)
                                   AS TerritoryName
                            FROM Sales.SalesOrderHeader
What is SalesOrderHeader.
500
The Performance of a non-recursive CTE is most likely to be similar to the performance of this database object
What is VIEW
500
The function that indicates if a row is aggregated on a specified column
What is GROUPING
M
e
n
u