SQL Jeopardy

SELECT Key Words

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

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

300

A key word which will eliminate duplicate rows in the query results.

What is DISTINCT?

Example:
select DISTINCT OrderTypeCode from OrderDetail

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

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)

More than just a key word

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',...)

200

The key word which can be used to limit rows returned, allowing for wildcard characters before the matching character string.

What is LIKE?

Example:
select ClientName from client
where ClientName like '%Chase'

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

400

The two words used together to build a table.

What is CREATE TABLE?

Example:

CREATE TABLE TempTable
(
OrderDetailId int,
OrderTransactionId int,
ProductType Varchar(20)
)

500

Three of the four required key words used to evaluate a list of conditions and return one out of multiple possible result expressions.

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

Potpourri

100

The words that make up the acronym "SQL".

What is Structured Query Language?

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?

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?

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?

500

The decade in which SQL was invented.

What is the 70s?

Aggregations

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'

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

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

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

500

This operator, which is only available in certain languages, allows you to filter on a window aggregate.

What is QUALIFY?

Multiple Choice

100

Which of the following functions can be used on both numeric as well as non-numeric data?

A) COUNT

B) AVG

C) STDDEV

D) VARIANCE

A

200

Which of the following is not true about the COUNT function?

A) COUNT(*) returns the number of rows in the table.

B) COUNT(exp) returns the number of rows with non-null values for the exp.

C) COUNT(DISTINCT exp) returns the number of unique, non-null values in the column.

D) All are true.

D

300

Which of the following is not true about removing rows from a table?

A) You can use a subquery in a DELETE statement.

B) Specific rows are deleted based on the WHERE clause condition.

C) A statement like, DELETE , would cause deletion of the table from the database.

D) All of the above.

C

400

Which statement is false for ORDER BY statement?

A) Requires a ASC or DESC keyword explicitly to sort the result set. 

B) Sorts the result set in descending order using the DESC keyword. 

C) Can sort based on multiple columns. 

D) None of the above.

A

500

Query to select all records with "bar" in their name?

A. SELECT * FROM people WHERE name = '%bar%';

B. SELECT * FROM people WHERE name LIKE '%bar%'; 

C. SELECT * FROM people WHERE name IN ('bar'); 

D. SELECT * FROM people WHERE name = '_bar_'

B