Posts by SIteDevadmin446oU

Triggers DML Triggers DDL Triggers (INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM Advanced SQL book, PRAGMATIC WORKS INTRO AND ADVANCED, DATABASESTAR ACADEMY, SQL COOKBOOK, Udemy, WENZEL) (COMPARE WITH ADVANCED SQL BOOK ONE LAST TIME) Triggers Triggers are stored procedures that cannot be executed explicitly. Instead it’s attached to an event, meaning whenever an event occurs…

(INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM Advanced SQL book, PRAGMATIC WORKS INTRO AND ADVANCED, DATABASESTAR ACADEMY, SQL COOKBOOK, Udemy, WENZEL) (COMPARE WITH ADVANCED SQL BOOK ONE LAST TIME) Stored procedures A stored procedure is code that can have input and output parameters, and they return the result sets of queries. Stored procedures are allowed…

(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…