SQL
#sql
Table of Contents
About 1
Chapter 1: Getting started with SQL 2
Remarks 2
Versions 2
Examples 2
Overview 2
Chapter 2: ALTER TABLE 4
Introduction 4
Syntax 4
Examples 4
Add Column(s) 4
Drop Column 4
Drop Constraint 4
Add Constraint 4
Alter Column 5
Add Primary Key 5
Chapter 3: AND & OR Operators 6
Syntax 6
Examples 6
AND OR Example 6
Chapter 4: Cascading Delete 7
Examples 7
ON DELETE CASCADE 7
Chapter 5: CASE 9
Introduction 9
Syntax 9
Remarks 9
Examples 9
Searched CASE in SELECT (Matches a boolean expression) 9
Use CASE to COUNT the number of rows in a column match a condition. 10
Shorthand CASE in SELECT 11
CASE in a clause ORDER BY 11
Using CASE in UPDATE 12
CASE use for NULL values ordered last 12
CASE in ORDER BY clause to sort records by lowest value of 2 columns 13
Sample data 13
Query 13
Results 14
Explanation 14
Chapter 6: Clean Code in SQL 15
Introduction 15
Examples 15
Formatting and Spelling of Keywords and Names 15
Table/Column Names 15
Keywords 15
SELECT * 15
Indenting 16
Joins 17
Chapter 7: Comments 19
Examples 19
Single-line comments 19
Multi-line comments 19
Chapter 8: Common Table Expressions 20
Syntax 20
Remarks 20
Examples 20
Temporary query 20
recursively going up in a tree 21
generating values 21
recursively enumerating a subtree 22
Oracle CONNECT BY functionality with recursive CTEs 23
Recursively generate dates, extended to include team rostering as example 24
Refactoring a query to use Common Table Expressions 25
Example of a complex SQL with Common Table Expression 25
Chapter 9: CREATE Database 27
Syntax 27
Examples 27
CREATE Database 27
Chapter 10: CREATE FUNCTION 28
Syntax 28
Parameters 28
Remarks 28
Examples 28
Create a new Function 28
Chapter 11: CREATE TABLE 30
Introduction 30
Syntax 30
Parameters 30
Remarks 30
Examples 30
Create a New Table 30
Create Table From Select 31
Duplicate a table 31
CREATE TABLE With FOREIGN KEY 31
Create a Temporary or In-Memory Table 32
PostgreSQL and SQLite 32
SQL Server 32
Chapter 12: cross apply, outer apply 34
Examples 34
CROSS APPLY and OUTER APPLY basics 34
Chapter 13: Data Types 36
Examples 36
DECIMAL and NUMERIC 36
FLOAT and REAL 36
Integers 36
MONEY and SMALLMONEY 36
BINARY and VARBINARY 37
CHAR and VARCHAR 37
NCHAR and NVARCHAR 37
UNIQUEIDENTIFIER 38
Chapter 14: DELETE 39
Introduction 39
Syntax 39
Examples 39
DELETE certain rows with WHERE 39
DELETE all rows 39
TRUNCATE clause 39
DELETE certain rows based upon comparisons with other tables 39
Chapter 15: DROP or DELETE Database 41
Syntax 41
Remarks 41
Examples 41
DROP Database 41
Chapter 16: DROP Table 42
Remarks 42
Examples 42
Simple drop 42
Check for existence before dropping 42
Chapter 17: Example Databases and Tables 43
Examples 43
Auto Shop Database 43
Relationships between tables 43
Departments 43
Employees 44
Click here to download full PDF material
