Definition of Terms

Question 1

Set Operations

what are the 3/4 set operators?

Answer 1

allow the results of multiple queries to be combined into a single result;

UNION, INTERSECT, EXCEPT/MINUS

Question 2

Subquery

what are the different clauses it can be used in?
(under what conditions is it used in the different clauses)

Answer 2

a nested query within another query or subquery;
can be within a SELECT, WHERE, IN, or FROM clause

used in SELECT clause when returning a single value, example a calculation

used in a WHERE clause went attempting to limit the results based on a condition

used with IN or NOT IN when the subquery return more than one value

when in the FROM clause the results from the subquery are used as a temporary table

Question 3

Correlated Subquery

Answer 3

subquery that uses data from the outer query/ is dependant on the outer query;

example:
SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT 
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)

The outer query filters only products whose buy price is greater than the average buy price per product line from the subquery

Question 4

stored procedure

Answer 4

prepared SQL code that you can call to execute at a later time

Question 5

Common Table Expression (CTE)

syntax

Answer 5

named temporary result set that exists only within the execution scope of a single SQL statement

unlike derived tables, a CTE can be self-referencing (recursive CTE) or can be referenced multiple times in the same query

syntax:
WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;

Question 6

Derived Table

Answer 6

a virtual table (construct to define things that are not tables, so they can be used in a SQL statement as if they are tables) returned from a SELECT statement; similar to a temporary table with fewer steps to create the table

Question 7

Recursive CTE

(what does the acronym mean?)

(syntax)

Answer 7

a recursive common table expression

CTE with a subquery which refers to the CTE name itself

syntax:
WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

Question 8

Transactions

what must a transaction be?

Answer 8

a logical unit of work that ends with it either being committed or rolled back; usually results in one or more changes to the database

transactions must be ACID (Atomic, Consistent, Isolated, Durable)

Question 9

Atomicity

Answer 9

SQL transaction property

all operations with the work unit are completed successfully

Question 10

Consistency

Answer 10

SQL transaction property

changes to data written or removed from the database must be valid and follow predefined rules

Question 11

Isolation

Answer 11

SQL transaction property

transaction integrity is visible to other transactions

Question 12

Durability

Answer 12

SQL transaction property

results of the transaction with persist (commit and stored permanently)

Question 13

Benefits of a CTE

3 main benefits

Answer 13

CTEs improve the readability of the code without compromising performance.

They help improve the maintainability of the code without compromising performance.

They make writing recursive code in T-SQL significantly easier than the previous SQL Server versions.

Question 14

Window Functions

Answer 14

allows calculations over a subset of rows in a SELECT statement (by partitioning over selected column);

contrast to a GROUP BY clauses which calculates over all of the selected rows (by requiring all columns in the SELECT statement also be in the GROUP BY clause)