SQL / T-SQL

(INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM Advanced SQL book, SQL COOKBOOK, Udemy) User Defined Functions: Scalar functions Inline table-valued functions Multistatement table-valued functions User-Defined System functions Other UDF considerations User-defined functions SQL Server supports three types of routines: user-defined functions, stored procedures, and triggers. A user-defined function (UDF) is used to encapsulate logic that…

Theoretical background A relational database management system (RDBMS) is based on the relational model, which in turn is based on two branches of mathematics: Set Theory and Predicate Logic. SQL Data in a RDBMS can be managed and queried using the SQL language, and Microsoft’s dialect and extension of SQL is T-SQL. SQL is a…

What is a Common Table Expression? Writing a CTE WITH Syntax Optional Columns Query Definition Calling or running the CTE Terminate previous statements Scope Why use a CTE? Simplifying queries with a CTE Recursive queries CTEs instead of Derived Tables Overwhelming Derived Tables: Cleanup with a CTE Performance considerations Reducing mistakes Recursive CTEs Syntax Anchor…

Set Operators The UNION operator The UNION ALL operator The UNION (DISTINCT) operator Difference between a Join and UNION The INTERSECT Operator The INTERSECT (DISTINCT) operator The INTERSECT ALL operator The EXCEPT operator The EXCEPT (DISTINCT) operator The EXCEPT ALL operator Precedence Circumventing unsupported logical phases (INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM SQL COOKBOOK,…

Window Functions Ranking Window Functions ROW_NUMBER RANK DENSE_RANK NTILE Analytical Window Functions LAG LEAD FIRST_VALUE LAST_VALUE Aggregate window functions Window Framing Alternative Methods to Window Functions (INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM, SQL COOKBOOK, Udemy) Window Functions Window functions return a scalar result for each row based on a calculation against a subset of…

Subqueries in SQL Self-contained scalar subqueries Subqueries in a SELECT clause as a column expression Subqueries in a WHERE clause as a filter criterion. EXISTS and NOT EXISTS Subqueries in the HAVING clause as a filter criterion Subquery in a function call Subquery to control the TOP clause Self-contained multivalued subquery IN and NOT IN…

JOINs CROSS JOINS Self-Cross Joins The Dating Service Scenario Producing tables of numbers INNER JOIN as CROSS JOIN Performance Considerations INNER JOINs Inner joins syntax Join Query Table Order More join examples: Composite joins Non-equi joins Non-equi join to calculate running total Non-equi join to check for duplicate values Non-equi join to compare a range…

Working with NULLs TRY_CAST and TRY_CONVERT Functions ISNULL and COALESCE Functions NULLIF Function Joining Tables on Nullable columns Subqueries and NULLs Set Operators and NULLs Enforcing Uniqueness with NULLs Handling NULLs when creating tables and constraints Working with NULLs: Two-valued predicate logic limits values to be True or False. However, missing values are neither True or False; they…

Single-table queries Elements of the SELECT statement –20 The FROM clause –18 underlined Delimiting identifier names The WHERE clause The GROUP BY clause The HAVING clause The SELECT clause The ORDER BY clause The TOP and OFFSET-FETCH filters The TOP filter The OFFSET-FETCH filter Predicates and operators CASE expressions NULLs All-at-one operations Working with character…

Background to T-SQL Querying and Programming Theoretical background SQL Set Theory Predicate Logic The relational model Propositions, predicates, and relations Missing values Constraints Normalization 1NF 2NF 3NF Types of Database systems Online transactional processing Data warehouses SQL Server Architecture The ABCs of Microsoft RDBMS flavors Box Appliance Cloud SQL Server instances Databases Database layout Schemas…