SQL Notes for Professionals book

Content list
About
Chapter 1: Getting started with SQL
Section 1.1: Overview
Chapter 2: Identifier
Section 2.1: Unquoted identifiers
Chapter 3: Data Types
Section 3.1: DECIMAL and NUMERIC
Section 3.2: FLOAT and REAL
Section 3.3: Integers
Section 3.4: MONEY and SMALLMONEY
Section 3.5: BINARY and VARBINARY
Section 3.6: CHAR and VARCHAR
Section 3.7: NCHAR and NVARCHAR
Section 3.8: UNIQUEIDENTIFIER
Chapter 4: NULL
Section 4.1: Filtering for NULL in queries
Section 4.2: Nullable columns in tables
Section 4.3: Updating fields to NULL
Section 4.4: Inserting rows with NULL fields
Chapter 5: Example Databases and Tables
Section 5.1: Auto Shop Database
Section 5.2: Library Database
Section 5.3: Countries Table
Chapter 6: SELECT
Section 6.1: Using the wildcard character to select all columns in a query
Section 6.2: SELECT Using Column Aliases
Section 6.3: Select Individual Columns
Section 6.4: Selecting specified number of records
Section 6.5: Selecting with Condition
Section 6.6: Selecting with CASE
Section 6.7: Select columns which are named after reserved keywords
Section 6.8: Selecting with table alias
Section 6.9: Selecting with more than 1 condition
Section 6.10: Selecting without Locking the table
Section 6.11: Selecting with Aggregate functions
Section 6.12: Select with condition of multiple values from column
Section 6.13: Get aggregated result for row groups
Section 6.14: Selection with sorted Results
Section 6.15: Selecting with null
Section 6.16: Select distinct (unique values only)
Section 6.17: Select rows from multiple tables
Chapter 7: GROUP BY
Section 7.1: Basic GROUP BY example
Section 7.2: Filter GROUP BY results using a HAVING clause
Section 7.3: USE GROUP BY to COUNT the number of rows for each unique entry in a given column
Section 7.4: ROLAP aggregation (Data Mining)
Chapter 8: ORDER BY
Section 8.1: Sorting by column number (instead of name)
Section 8.2: Use ORDER BY with TOP to return the top x rows based on a column's value
Section 8.3: Customizeed sorting order
Section 8.4: Order by Alias
Section 8.5: Sorting by multiple columns
Chapter 9: AND & OR Operators
Section 9.1: AND OR Example
Chapter 10: CASE
Section 10.1: Use CASE to COUNT the number of rows in a column match a condition
Section 10.2: Searched CASE in SELECT (Matches a boolean expression)
Section 10.3: CASE in a clause ORDER BY
Section 10.4: Shorthand CASE in SELECT
Section 10.5: Using CASE in UPDATE
Section 10.6: CASE use for NULL values ordered last
Section 10.7: CASE in ORDER BY clause to sort records by lowest value of 2 columns
Chapter 11: LIKE operator
Section 11.1: Match open-ended pattern
Section 11.2: Single character match
Section 11.3: ESCAPE statement in the LIKE-query
Section 11.4: Search for a range of characters
Section 11.5: Match by range or set
Section 11.6: Wildcard characters
Chapter 12: IN clause
Section 12.1: Simple IN clause
Section 12.2: Using IN clause with a subquery
Chapter 13: Filter results using WHERE and HAVING
Section 13.1: Use BETWEEN to Filter Results
Section 13.2: Use HAVING with Aggregate Functions
Section 13.3: WHERE clause with NULL/NOT NULL values
Section 13.4: Equality
Section 13.5: The WHERE clause only returns rows that match its criteria
Section 13.6: AND and OR
Section 13.7: Use IN to return rows with a value contained in a list
Section 13.8: Use LIKE to find matching strings and substrings
Section 13.9: Where EXISTS
Section 13.10: Use HAVING to check for multiple conditions in a group
Chapter 14: SKIP TAKE (Pagination)
Section 14.1: Limiting amount of results
Section 14.2: Skipping then taking some results (Pagination)
Section 14.3: Skipping some rows from result
Chapter 15: EXCEPT
Section 15.1: Select dataset except where values are in this other dataset
Chapter 16: EXPLAIN and DESCRIBE
Section 16.1: EXPLAIN Select query
Section 16.2: DESCRIBE tablename;
Chapter 17: EXISTS CLAUSE
Section 17.1: EXISTS CLAUSE
Chapter 18: JOIN
Section 18.1: Self Join
Section 18.2: Dierences between inner/outer joins
Section 18.3: JOIN Terminology: Inner, Outer, Semi, Anti..
Section 18.4: Left Outer Join
Section 18.5: Implicit Join
Section 18.6: CROSS JOIN
Section 18.7: CROSS APPLY & LATERAL JOIN
Section 18.8: FULL JOIN
Section 18.9: Recursive JOINs
Section 18.10: Basic explicit inner join
Section 18.11: Joining on a Subquery
Chapter 19: UPDATE
Section 19.1: UPDATE with data from another table
Section 19.2: Modifying existing values
Section 19.3: Updating Specified Rows
Section 19.4: Updating All Rows
Section 19.5: Capturing Updated records
Chapter 20: CREATE Database
Section 20.1: CREATE Database
Chapter 21: CREATE TABLE
Section 21.1: Create Table From Select
Section 21.2: Create a New Table
Section 21.3: CREATE TABLE With FOREIGN KEY
Section 21.4: Duplicate a table
Section 21.5: Create a Temporary or In-Memory Table
Chapter 22: CREATE FUNCTION
Section 22.1: Create a new Function
Chapter 23: TRY/CATCH
Section 23.1: Transaction In a TRY/CATCH
Chapter 24: UNION / UNION ALL
Section 24.1: Basic UNION ALL query
Section 24.2: Simple explanation and Example
Chapter 25: ALTER TABLE
Section 25.1: Add Column(s)
Section 25.2: Drop Column
Section 25.3: Add Primary Key
Section 25.4: Alter Column
Section 25.5: Drop Constraint
Chapter 26: INSERT
Section 26.1: INSERT data from another table using SELECT
Section 26.2: Insert New Row
Section 26.3: Insert Only Specified Columns
Section 26.4: Insert multiple rows at once
Chapter 27: MERGE
Section 27.1: MERGE to make Target match Source
Section 27.2: MySQL: counting users by name
Section 27.3: PostgreSQL: counting users by name
Chapter 28: cross apply, outer apply
Section 28.1: CROSS APPLY and OUTER APPLY basics
Chapter 29: DELETE
Section 29.1: DELETE all rows
Section 29.2: DELETE certain rows with WHERE
Section 29.3: TRUNCATE clause
Section 29.4: DELETE certain rows based upon comparisons with other tables
Chapter 30: TRUNCATE
Section 30.1: Removing all rows from the Employee table
Chapter 31: DROP Table
Section 31.1: Check for existence before dropping
Section 31.2: Simple drop
Chapter 32: DROP or DELETE Database
Section 32.1: DROP Database
Chapter 33: Cascading Delete
Section 33.1: ON DELETE CASCADE
Chapter 34: GRANT and REVOKE
Section 34.1: Grant/revoke privileges
Chapter 35: XML
Section 35.1: Query from XML Data Type
Chapter 36: Primary Keys
Section 36.1: Creating a Primary Key
Section 36.2: Using Auto Increment
Chapter 37: Indexes
Section 37.1: Sorted Index
Section 37.2: Partial or Filtered Index
Section 37.3: Creating an Index
Section 37.4: Dropping an Index, or Disabling and Rebuilding it
Section 37.5: Clustered, Unique, and Sorted Indexes
Section 37.6: Rebuild index
Section 37.7: Inserting with a Unique Index
Chapter 38: Row number
Section 38.1: Delete All But Last Record (1 to Many Table)
Section 38.2: Row numbers without partitions
Section 38.3: Row numbers with partitions
Chapter 39: SQL Group By vs Distinct
Section 39.1: Dierence between GROUP BY and DISTINCT
Chapter 40: Finding Duplicates on a Column Subset with Detail
Section 40.1: Students with same name and date of birth
Chapter 41: String Functions
Section 41.1: Concatenate
Section 41.2: Length
Section 41.3: Trim empty spaces
Section 41.4: Upper & lower case
Section 41.5: Split
Section 41.6: Replace
Section 41.7: REGEXP
Section 41.8: Substring
Section 41.9: Stu
Section 41.10: LEFT - RIGHT
Section 41.11: REVERSE
Section 41.12: REPLICATE
Section 41.13: Replace function in sql Select and Update query
Section 41.14: INSTR
Section 41.15: PARSENAME
Chapter 42: Functions (Aggregate)
Section 42.1: Conditional aggregation
Section 42.2: List Concatenation
Section 42.3: SUM
Section 42.4: AVG()
Section 42.5: Count
Section 42.6: Min
Section 42.7: Max
Chapter 43: Functions (Scalar/Single Row)
Section 43.1: Date And Time
Section 43.2: Character modifications
Section 43.3: Configuration and Conversion Function
Section 43.4: Logical and Mathmetical Function
Chapter 44: Functions (Analytic)
Section 44.1: LAG and LEAD
Section 44.2: PERCENTILE_DISC and PERCENTILE_CONT
Section 44.3: FIRST_VALUE
Section 44.4: LAST_VALUE
Section 44.5: PERCENT_RANK and CUME_DIST
Chapter 45: Window Functions
Section 45.1: Setting up a flag if other rows have a common property
Section 45.2: Finding "out-of-sequence" records using the LAG() function
Section 45.3: Getting a running total
Section 45.4: Adding the total rows selected to every row
Section 45.5: Getting the N most recent rows over multiple grouping
Chapter 46: Common Table Expressions
Section 46.1: generating values
Section 46.2: recursively enumerating a subtree
Section 46.3: Temporary query
Section 46.4: recursively going up in a tree
Section 46.5: Recursively generate dates, extended to include team rostering as example
Section 46.6: Oracle CONNECT BY functionality with recursive CTEs
Chapter 47: Views
Section 47.1: Simple views
Section 47.2: Complex views
Chapter 48: Materialized Views
Section 48.1: PostgreSQL example
Chapter 49: Comments
Section 49.1: Single-line comments
Section 49.2: Multi-line comments
Chapter 50: Foreign Keys
Section 50.1: Foreign Keys explained
Section 50.2: Creating a table with a foreign key
Chapter 51: Sequence
Section 51.1: Create Sequence
Section 51.2: Using Sequences
Chapter 52: Subqueries
Section 52.1: Subquery in FROM clause
Section 52.2: Subquery in SELECT clause
Section 52.3: Subquery in WHERE clause
Section 52.4: Correlated Subqueries
Section 52.5: Filter query results using query on dierent table
Section 52.6: Subqueries in FROM clause
Section 52.7: Subqueries in WHERE clause
Chapter 53: Execution blocks
Section 53.1: Using BEGIN ... END
Chapter 54: Stored Procedures
Section 54.1: Create and call a stored procedure
Chapter 55: Triggers
Section 55.1: CREATE TRIGGER
Section 55.2: Use Trigger to manage a "Recycle Bin" for deleted items
Chapter 56: Transactions
Section 56.1: Simple Transaction
Section 56.2: Rollback Transaction
Chapter 57: Table Design
Section 57.1: Properties of a well designed table
Chapter 58: Synonyms
Section 58.1: Create Synonym
Chapter 59: Information Schema
Section 59.1: Basic Information Schema Search
Chapter 60: Order of Execution
Section 60.1: Logical Order of Query Processing in SQL
Chapter 61: Clean Code in SQL
Section 61.1: Formatting and Spelling of Keywords and Names
Section 61.2: Indenting
Section 61.3: SELECT *
Section 61.4: Joins
Chapter 62: SQL Injection
Section 62.1: SQL injection sample
Section 62.2: simple injection sample
Credits
You may also like