Essential SQL

Getting started with SQL
- Overview
SELECT
- SELECT
- Using the wildcard character to select all columns in a query.
- SELECT Using Column Aliases
- Select Individual Columns
- Selecting specified number of records
- Selecting with Condition
- Selecting with CASE
- Select columns which are named after reserved keywords
- Selecting with table alias
- Selecting with more than 1 condition.
- Selecting with Aggregate functions
- Get aggregated result for row groups
- Selection with sorted Results
- Selecting without Locking the table
- Select with condition of multiple values from column
- Select distinct unique values only
- Selecting with null
- Select rows from multiple tables
JOIN
- JOIN
- Self Join
- Differences between innerouter joins
- JOIN Terminology Inner Outer Semi Anti...
- Left Outer Join
- Implicit Join
- Basic explicit inner join
- Joining on a Subquery
- CROSS APPLY LATERAL JOIN
- FULL JOIN
- Recursive JOINs
- CROSS JOIN
Example Databases and Tables
- Auto Shop Database
- Library Database
- Countries Table
UPDATE
- UPDATE
- UPDATE with data from another table
- Modifying existing values
- Updating Specified Rows
- Updating All Rows
- Capturing Updated records
Indexes
- Indexes
- Creating an Index
- 0Clustered Unique and Sorted Indexes
- Sorted Index
- Dropping an Index or Disabling and Rebuilding it
- Partial or Filtered Index
- Inserting with a Unique Index
- Rebuild index
- SAP ASE Drop index
- Unique Index that Allows NULLS
- Clustered index
- Non clustered index
CREATE TABLE
- CREATE TABLE
- Create a New Table
- Create Table From Select
- CREATE TABLE With FOREIGN KEY
- Duplicate a table
- Create a Temporary or In-Memory Table
UNION, UNION ALL
- UNION UNION ALL
- Basic UNION ALL query
- Simple explanation and Example
ALTER TABLE
- ALTER TABLE
- Add Columns
- Drop Column
- Add Primary Key
- Drop Constraint
- Alter Column
- Add Constraint
CASE
- CASE
- Use CASE to COUNT the number of rows in a column match a condition.
- Searched CASE in SELECT Matches a boolean expression
- CASE in a clause ORDER BY
- Shorthand CASE in SELECT
- Using CASE in UPDATE
- CASE use for NULL values ordered last
- CASE in ORDER BY clause to sort records by lowest value of 2 columns
INSERT
- INSERT
- INSERT data from another table using SELECT
- Insert New Row
- Insert Only Specified Columns
- Insert multiple rows at once
Primary Keys
- Primary Keys
- Creating a Primary Key
- Using Auto Increment
ORDER BY
- Use ORDER BY with TOP to return the top x rows based on a columns value
- Sorting by column number instead of name
- Sorting by multiple columns
- Order by Alias
- Customizeed sorting order
GROUP BY
- GROUP BY
- Basic GROUP BY example
- USE GROUP BY to COUNT the number of rows for each unique entry in a given column
- Filter GROUP BY results using a HAVING clause
- ROLAP aggregation Data Mining
Filter results using WHERE and HAVING
- Filter results using WHERE and HAVING
- Use BETWEEN to Filter Results
- Use HAVING with Aggregate Functions
- WHERE clause with NULLNOT NULL values
- The WHERE clause only returns rows that match its criteria
- Equality
- AND and OR
- Use IN to return rows with a value contained in a list
- Use LIKE to find matching strings and substrings
- Use HAVING to check for multiple conditions in a group
- Where EXISTS
Window Fnctions
- Setting up a flag if other rows have a common property
- Getting a running total
- Finding out-of-sequence records using the LAG function
- Adding the total rows selected to every row
- Getting the N most recent rows over multiple grouping
Common Table Expressions
- Common Table Expressions
- generating values
- recursively enumerating a subtree
- Temporary query
- recursively going up in a tree
- Recursively generate dates extended to include team rostering as example
- Oracle CONNECT BY functionality with recursive CTEs
- Refactoring a query to use Common Table Expressions
- 0Example of a complex SQL with Common Table Expression
Views
- Views
- Simple views
- Complex views
LIKE operator
- LIKE operator
- Match open-ended pattern
- Single character match
- ESCAPE statement in the LIKE-query
- Match by range or set
- Search for a range of characters
- Wildcard characters
- Match ANY versus ALL
Functions Aggregate
- Functions Aggregate
- Conditional aggregation
- List Concatenation
- SUM
- AVG
- Count
- Max
- Min
DELETE
- DELETE
- DELETE all rows
- DELETE certain rows with WHERE
- TRUNCATE clause
- DELETE certain rows based upon comparisons with other tables
String Functions
- String Functions
- Concatenate
- Length
- Trim empty spaces
- Upper lower case
- Split
- Replace
- Stuff
- Substring
- REGEXP
- LEFT - RIGHT
- REVERSE
- REPLICATE
- Replace function in sql Select and Update query
- PARSENAME
- INSTR
Data Types
- Data Types
- DECIMAL and NUMERIC
- FLOAT and REAL
- Integers
- MONEY and SMALLMONEY
- BINARY and VARBINARY
- CHAR and VARCHAR
- NCHAR and NVARCHAR
- UNIQUEIDENTIFIER
AND OR operators
- AND OR Operators
- AND OR Example
Triggers
- Triggers
- CREATE TRIGGER
- Use Trigger to manage a Recycle Bin for deleted items
TRUNCATE
- TRUNCATE
- Removing all rows from the Employee table
MERGE
- MERGE
- MERGE to make Target match Source
- MySQL counting users by name
- PostgreSQL counting users by name
Foreign Keys
- Foreign Keys
- Creating a table with a foreign key
- Foreign Keys explained
Finding Duplicates on a Column Subset with Detail
- Finding Duplicates on a Column Subset with Detail
- Students with same name and date of birth
Sequence
- Sequence
- Create Sequence
- Using Sequences
Comments
- Comments
- Single-line comments
- Multi-line comments
Subqueries
- Subqueries
- Subquery in FROM clause
- Subquery in WHERE clause
- Subquery in SELECT clause
- Correlated Subqueries
- Subqueries in FROM clause
- Filter query results using query on different table
- Subqueries in WHERE clause
- Subqueries in SELECT clause
Execution blocks
- Execution blocks
- Using BEGIN ... END
Stored Procedures
- Stored Procedures
- Create and call a stored procedure
DROP Table
- DROP Table
- Check for existence before dropping
- Simple drop
Row number
- Row number
- Row numbers without partitions
- Row numbers with partitions
- Delete All But Last Record 1 to Many Table
Transactions
- Transactions
- Simple Transaction
- Rollback Transaction
CREATE FUNCTION
- CREATE FUNCTION
- Create a new Function
SQL Group by vs. Distinct
- SQL Group By vs Distinct
- Difference between GROUP BY and DISTINCT
Table Design
- Table Design
- Properties of a well designed table.
CROSS APPLY and OUTER APPLY
- cross apply outer apply
- CROSS APPLY and OUTER APPLY basics
Synonyms
- Synonyms
- Create Synonym
CREATE database
- CREATE Database
- CREATE Database
SKIP TAKE pagination
- SKIP TAKE Pagination
- Limiting amount of results
- Skipping then taking some results Pagination
- Skipping some rows from result
EXPLAIN and DESCRIBE
- EXPLAIN and DESCRIBE
- EXPLAIN Select query
- DESCRIBE tablename
Information Schema
- Information Schema
- Basic Information Schema Search
IN clause
- IN clause
- Simple IN clause
- Using IN clause with a subquery
NULL
- NULL
- Filtering for NULL in queries
- Nullable columns in tables
- Updating fields to NULL
- Inserting rows with NULL fields
SQL Injection
- SQL Injection
- SQL injection sample
- simple injection sample
Cascading Delete
- Cascading Delete
- ON DELETE CASCADE
Order of Execution
- Order of Execution
- Logical Order of Query Processing in SQL
DROP or DELETE database
- DROP or DELETE Database
- DROP Database
EXCEPT
- EXCEPT
- Select dataset except where values are in this other dataset
TRYCATCH
- TRYCATCH
- Transaction In a TRYCATCH
XML
- XML
- Query from XML Data Type
GRANT and REVOKE
- GRANT and REVOKE
- Grantrevoke privileges
Functions ScalarSingle Row
- Functions ScalarSingle Row
- Character modifications
- Date And Time
- Configuration and Conversion Function
- Logical and Mathmetical Function
Relational Algebra
- Relational Algebra
- Overview
EXISTS CLAUSE
- EXISTS CLAUSE
- EXISTS CLAUSE
Materialized Views
- Materialized Views
- PostgreSQL example
Functions Analytic
- Functions Analytic
- LAG and LEAD
- PERCENTILE DISC and PERCENTILE CONT
- FIRST VALUE
- LAST VALUE
- PERCENT RANK and CUME DIST
SQL CURSOR
- SQL CURSOR
- Example of a cursor that queries all rows by index for each database
Identifier
- Identifier
- Unquoted identifiers
Clean Code in SQL
- Clean Code in SQL
- Formatting and Spelling of Keywords and Names
- Indenting
- SELECT
- Joins
Contributors

Contents