Post midterm SQL

Question 1

How are alias that include whitespace referenced outside of the SELECT statement?

Answer 1

in brackets: [alias name].

Question 2

DATEDIFF arguments in order:
DATEDIFF(1,2,3)

Answer 2

1: time metric.
2: date1, subtracted from date2
3: date2.

Question 3

What is the use and data type output from MONTH(), YEAR(), or DAY()?

Answer 3

Use: isolate a time metric in a date data type.
Output: integer.

Question 4

What is the Syntax for OFFSET & FETCH?

Answer 4

OFFSET ?? ROWS
FETCH NEXT ?? ROWS ONLY;

Question 5

How do you filter for NULL or NON NULL values in the WHERE clause (Syntax)?

Answer 5

“attribute IS NULL” or “attribute IS NOT NULL.”

Question 6

The 5 aggregate functions can be sorted into two categories based on output. What are they and what are their possible outputs?

Answer 6

COUNT(), AVG(), and SUM() create numeric outputs. They can be decimal or integer.
MIN() and MAX() can have string, numeric, or date outputs.

Question 7

What two ways can you use COUNT() to find the number of rows in a table?

Answer 7

COUNT(PK_attribute) or COUNT(*).

Question 8

What does DISTINCT do?

Answer 8

Selects unique values from the first attribute in the SELECT statement.

Question 9

What stipulate does the use of GROUP BY put on the SELECT statement?

Answer 9

All SELECT statement attributes must be either in the GROUP BY clause or an aggregate function.

Question 10

You can assign DESC order to each attribute in ORDER BY clause. True or False?

Answer 10

True.

Question 11

Alphabetical Ascending

Answer 11

A to Z.

Question 12

Alphabetical Descending

Answer 12

Z to A.

Question 13

Date Ascending

Answer 13

Oldest date to newest date.
2003 to 2024.

Question 14

Date Descending

Answer 14

Newest date to oldest date.
2024 to 2003.

Question 15

CONCAT(IDNum, State) is ordered as a number because the values start with integers. True or false?

Answer 15

False. They are treated as alphanumeric values.

Question 16

How is OVER(PARTITION BY …) used? What a syntax example?

Answer 16

Used with an aggregate to display the aggregate alongside each record of certain groups. It appears in the SELECT statement.
Ex: “AVG(Salary) OVER(PARTITION BY Gender).”

Question 17

What does COUNT(*) OVER() do in the SELECT statement?

Answer 17

Returns total number of rows which is displayed with each row retrieved.

Question 18

What is the syntax for a CASE column in the SELECT statement?

Answer 18

CASE
WHEN (…) THEN ‘value’
WHEN (…) THEN ‘value’
ELSE ‘value’
END AS ‘Alias’

Question 19

Components in a CASE column in the SELECT statement?

Answer 19

CASE, WHEN, THEN, ELSE, & END.

Question 20

ALTER is DML and UPDATE is DDL. True of False?

Answer 20

ALTER is data definition language and UPDATE is data manipulation language.

Question 21

You cannot use CASE within UPDATE. It must be UPDATE SET WHERE. True of False?

Answer 21

False.

Question 22

How do you use ALL or ANY?

Answer 22

They are used in non-correlated subqueries to get the compare the MAX() or MIN() of the inner query respectively. They go right before the inner query.
Ex: “WHERE Salary > ANY (…);”

Question 23

How do you hardcode record labels (SYNTAX)?

Answer 23

“SELECT ‘string’ AS ‘ ‘ …”
There must be one whitespace in the alias.

Question 24

ERROR_NUMBER()

Answer 24

Returns the error number when an error is caught, part of the Catch in a procedure.

Question 25

ERROR_MESSAGE()

Answer 25

Returns the error message when an error is caught, part of the Catch in a procedure.

Question 26

Declare a local variable and assign a value.

Answer 26

“Declare @variable_name”
“SELECT @variable_name=AVG(Salary)”

Question 27

SCOPE_IDENTITY()

Answer 27

Used when updating values with a procedure. It makes the update only to the most recent record.
Ex: “WHERE EmpID = SCOPE_IDENTITY().”

Question 28

Benefits of Procedures?

Answer 28

Enhanced security, enhanced performance, sharing, and centralization.

Question 29

Sequential File Org

Answer 29

Records are recorded in sequence of PK values. A program scans the file from beginning to end. Useful in an operation involving the majority of records.

Question 30

Hashing File Org

Answer 30

Technique for directly searching the location data on a disk using a hash function. Uses MD2 (128 Bits, Char(16)) or SHA2_512 (512 Bits, Char(64)). Best when looking for discrete, precise, or random records.

Question 31

Indexing File Org

Answer 31

Creates an index table with an index value (pointers) and PK column. The indexes are used for results. Best used when returning a range, joins, where conditions, or aggregate functions. This is the default for RDBMS.

Question 32

B+ Index Search

Answer 32

Binary tree search: starts at a root node and works its way through the tree (internal nodes) to find leaf nodes the quickest. Tree structure needs to be rebalanced or rebuilt regularly for efficiency.

Question 33

Create a non-clustered index.

Answer 33

CREATE INDEX index_name AS table _name (attr1[, attr2]);

Question 34

What does MAX(Date) and MIN(Date) return?

Answer 34

MAX(Date) finds the most recent date.
MIN(Date) finds the furthest date in the past.

Question 35

Date Descending

Answer 35

most recent date to oldest date.

Question 36

Date Ascending

Answer 36

oldest date to most recent date.