SELECT Key Words
More than just a key word
Potpourri
Aggregations
TS Databases
100
A key word used to limit the rows returned by a query by applying conditions to be met by one or more columns in the query.
What is WHERE?

Example:
select * from OrderDetail
WHERE OrderDetailId = 59156135
100
The two key words which are used together to add new records in an existing table.
What are INSERT INTO?

Example:
INSERT INTO Client
VALUES ('LENDER',9999999,'New Client Name',...)
100
The words that make up the acronym "SQL".
What is Structured Query Language?
100
The key word used to total the number of records in a result set or group.
What is COUNT()

Example:
select COUNT(*) from OrderDetail od
&nbsp &nbsp join Client c on od.clientid = c.clientid
where c.ClientNumber='20555' and od.StateCode='AK'
100
The table and column where you would find the Loan Number.
What are OrderTransaction and ClientLoanNumber?
200
A key word that can be used to assign an alias name to a column in the result set.
What is AS?

Example:
select OrderDetailId AS 'Order Number' from OrderDetail
&nbsp &nbsp where OrderDetailId = 59156135
200
The two key words and symbol which can be used together to limit rows returned, allowing for wildcard characters before the matching character string. Also accept '_' as wildcard character
What are WHERE LIKE %?
'_' can also be a wildcard for one single character

Example:
select ClientName from client
where ClientName like '%Chase'
200
The data type best used to store string data where the length of the string can change, up to 8,000 characters.
What is VARCHAR?
200
The key word which returns the greatest numeric value within the result set or group.
What is MAX()

Example:
select max(ot.loanamount) from OrderDetail od
&nbsp &nbsp join Client c on od.clientid = c.clientid
&nbsp &nbsp join OrderTransaction ot on od.orderdetailid = ot.orderdetailid
where od.StateCode='ca' and c.clientnumber=20555
200
The table that tracks when you have an order (or any other record) locked.
What is ApplicationLock?
300
A key word which will eliminate duplicate rows in the query results.
What is DISTINCT?

Example:
select DISTINCT OrderTypeCode from OrderDetail
300
The key word used to create a new local variable and the first character in the variable's name.
What is DECLARE @?

Example:
declare @ParentClentId int
300
A rule that enforces that all values in the referenced column must match a unique key from another table within the same database.
What is a Foreign Key Constraint?
300
The key word used to set a filter condition on the result of an aggregation.
What is HAVING?

select c.clientnumber, count(od.orderdetailid) from client c
&nbsp &nbsp join orderdetail od on c.clientid = od.clientid
where od.statecode='CA'
group by c.clientnumber
having count(od.orderdetailid) > 5000
300
The CalculatedPremiumAmount from this table is where you will find the actual Title Premium cost.
What is TitlePolicyInsurance?
400
The type of join used to return all the rows from the first table, regardless of a match found in the second table.
What is OUTER?

Example:
select top 10 * from OrderTransaction ot
&nbsp &nbsp inner join TitleDetail td on ot.OrderTransactionId = td.OrderTransactionId
&nbsp &nbsp left outer join closingdetail cd on ot.OrderTransactionId = cd.OrderTransactionId
where cd.ClosingDetailId is null and ot.orderdetailid > 55000000
400
The two words used together to build a temporary table and the first character of the table's name.
What is CREATE TABLE #?

Example:
CREATE TABLE #TempTable
(
&nbsp &nbsp OrderDetailId int,
&nbsp &nbsp OrderTransactionId int,
&nbsp &nbsp ProductType Varchar(20)
)
400
The value returned by this expression:
Select DATEDIFF(hour, '2014-05-30 09:58:00', '2014-05-30 13:01:00')
What is 4?
400
This function allows for calculating aggregate values yet returns all rows instead of just one row per group.
What is OVER?

Example:
select ot.orderdetailid, ot.loanamount, od.statecode,
&nbsp &nbsp MAX(loanamount) over (partition by od.statecode) as 'State Max Amt',
&nbsp &nbsp AVG(loanamount) over (partition by od.statecode) as 'State Avg Amt',
&nbsp &nbsp MIN(LoanAmount) over (partition by od.statecode) as 'State Min Amt',
&nbsp &nbsp COUNT(LoanAmount) over (partition by od.statecode) as 'State Order Count'
from OrderTransaction ot
&nbsp &nbsp join OrderDetail od on ot.OrderDetailId = od.OrderDetailId
&nbsp &nbsp join Client c on od.ClientId = c.ClientId
where c.ClientNumber='20555' and ot.OrderDetailId>'59000000'
order by ot.OrderDetailId
400
This table links a HUD Line Transaction to the actuall Payee.
What is ClosingDetailPayee?
500
A key word which will merge the result sets from two different queries, having like columns, into one result.
What is UNION?

Example:
select ClientName, ClientNumber from Client
&nbsp &nbsp where ClientNumber = 98826300
UNION
select ClientName, ClientNumber from Client
&nbsp &nbsp where ParentClientId =
&nbsp &nbsp &nbsp &nbsp (select ClientId from client where ClientNumber = 98826300)
500
Three of the four required key words used to evaluate a list of conditions and return one out of multiple possible result expressions. Also accept END
What are CASE, WHEN, THEN, END?
ELSE is not required but is optional

Example:
Select
CASE
&nbsp &nbsp when pwrh.ProductCode = 'TITLE' then 1
&nbsp &nbsp when pwrh.ProductCode = 'TAX' then 2
&nbsp &nbsp when pwrh.ProductCode = 'CLOSING' then 3
&nbsp &nbsp when pwrh.ProductCode = 'RECORDING' then 4
&nbsp &nbsp when pwrh.ProductCode = 'APPRAISAL' then 5
END as 'ProductNumber'
FROM ProductWorkflowRequirementHistory pwrh
500
The year in which Microsoft released their first version of SQL Server.
What is 1989?
500
This operator can be used to JOIN two SELECT statements where the right statement is evaluated for each row returned by the left. It is especially useful for aggregated sub-queries.
What is APPLY? (OUTER APPLY / CROSS APPLY)

Select ui.UserName,Replies.[Number of Replies], Completes.[Number Completed]
from UserInfo ui
cross apply
(select ui2.UserId, COUNT(1) as 'Number of Replies'
From Atlas.dbo.UserInfo ui2 &nbsp &nbsp join Newman.dbo.Mail m on m.CreatedBy = ui2.UserId
where m.MailboxCode = 'JPMCProcessing'
&nbsp &nbsp and ui2.username <> 'Nexus'
&nbsp &nbsp and ui2.UserId = ui.UserId
group by ui2.UserId
) Replies

cross apply
(select ui3.UserId, COUNT(1) as 'Number Completed'
from Atlas.dbo.UserInfo ui3
&nbsp &nbsp join Newman.dbo.Mail m on m.updatedby = ui3.UserId
&nbsp &nbsp where m.MailboxCode = 'JPMCProcessing'
&nbsp &nbsp and ui3.username <> 'Nexus'
&nbsp &nbsp and ui3.UserId = ui.UserId
group by ui3.UserId
) Completes

order by ui.UserName
500
This Nexus table is where you will find tracking records for outbound faxes, emails, and mobile notifications.
What is MessageDelivery?
M
e
n
u