SQL
Notes for ProfessionalsSQL
Notes for Professionals
100+ pages
of professional hints and tricks
GoalKicker.com
Free Programming Books
Disclaimer
This is an unocial free book created for educational purposes and is
not aliated with ocial SQL group(s) or company(s).
All trademarks and registered trademarks are
the property of their respective owners
Contents
About
...................................................................................................................................................................................
1
Chapter 1: Getting started with SQL
...................................................................................................................
2
Section 1.1: Overview
Chapter 2: Identifier
......................................................................................................................................................
2
....................................................................................................................................................
3
Section 2.1: Unquoted identifiers
..................................................................................................................................
3
Chapter 3: Data Types
...............................................................................................................................................
4
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
............................................................................................................................
.......................................................................................................................................
......................................................................................................................................................
......................................................................................................................
4
..........................................................................................................................
................................................................................................................................
..........................................................................................................................
...................................................................................................................................
Chapter 4: NULL
............................................................................................................................................................
6
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
.....................................................................................................................
.......................................................................................................................
...........................................................................................................................
...............................................................................................................
....................................................................................................
8
Section 5.1: Auto Shop Database
Section 5.2: Library Database
Section 5.3: Countries Table
.................................................................................................................................
8
....................................................................................................................................
.......................................................................................................................................
Chapter 6: SELECT
......................................................................................................................................................
14
..........................................................
14
...............................................................................................................
......................................................................................................................
.................................................................................................
.........................................................................................................................
................................................................................................................................
.......................................................................................................................
..............................................................
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
................................................................................................
................................................................................................................
..................................................................................................................................
.....................................................................................................
.........................................................................................................
....................................................................................................
....................................................................................................
.....................................................................................................
.......................................................................
4
4
4
4
5
5
5
6
6
6
7
10
13
15
18
19
20
20
21
21
22
23
23
24
24
25
25
25
26
Advertisement
Chapter 7: GROUP BY
...............................................................................................................................................
27
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
......................................................................................................................
.................................................................................
27
28
................................................................................................................................................................................
28
Section 7.4: ROLAP aggregation (Data Mining)
.......................................................................................................
29
Chapter 8: ORDER BY
...............................................................................................................................................
31
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
....................................................................................................................
.........................................................................................................................................
..................................................................................................................
....................................................................................
31
...............................
31
Chapter 9: AND & OR Operators
Section 9.1: AND OR Example
.........................................................................................................................
34
.....................................................................................................................................
34
Chapter 10: CASE
.........................................................................................................................................................
35
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
.................................................................................................................
..................................................................................................................
.........................................................................................................................
......................................
35
...........................................................
..............................................................................................
......................................
Chapter 11: LIKE operator
.......................................................................................................................................
39
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
....................................................................................................................
.........................................................................................................................
...................................................................................................
40
.........................................................................................................
...........................................................................................................................
..............................................................................................................................
Chapter 12: IN clause
.................................................................................................................................................
43
Section 12.1: Simple IN clause
Section 12.2: Using IN clause with a subquery
.....................................................................................................................................
43
.........................................................................................................
43
Chapter 13: Filter results using WHERE and HAVING
................................................................................
44
.............................................................................................................
..............................................................................................
.....................................................................................
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
...................................................................................................................................................
...........................................................................................................................................
...........................................................
46
.......................................................................
.............................................................................
.........................................................................................................................................
48
................................................................
48
Chapter 14: SKIP TAKE (Pagination)
..................................................................................................................
50
Section 14.1: Limiting amount of results
Section 14.2: Skipping then taking some results (Pagination)
Section 14.3: Skipping some rows from result
................................................................................
50
..........................................................................................................
51
....................................................................................................................
50
Chapter 15: EXCEPT
....................................................................................................................................................
52
Section 15.1: Select dataset except where values are in this other dataset
..........................................................
52
Chapter 16: EXPLAIN and DESCRIBE
Section 16.1: EXPLAIN Select query
Section 16.2: DESCRIBE tablename;
..................................................................................................................
53
............................................................................................................................
...........................................................................................................................
Chapter 17: EXISTS CLAUSE
Section 17.1: EXISTS CLAUSE
...................................................................................................................................
54
.......................................................................................................................................
54
Advertisement
Chapter 18: JOIN
..........................................................................................................................................................
55
32
32
33
36
36
36
37
37
38
39
40
41
41
41
44
45
45
46
46
47
47
53
53
...................................................................................................................................................
...............................................................................................
.......................................................................................
59
Section 18.1: Self Join
Section 18.2: Dierences 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
...................................................................................................................................................
76
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
.....................................................................................................................
......................................................................................................................
.................................................................................................................................
.................................................................................................................
................................................................................................
76
Chapter 20: CREATE Database
Section 20.1: CREATE Database
............................................................................................................................
78
.................................................................................................................................
78
Chapter 21: CREATE TABLE
....................................................................................................................................
79
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
.....................................................................................................................
..............................................................................................................................
.....................................................................................................
...................................................................................................................................
..........................................................................................
80
Chapter 22: CREATE FUNCTION
Section 22.1: Create a new Function
...........................................................................................................................
82
..........................................................................................................................
82
Chapter 23: TRY/CATCH
..........................................................................................................................................
83
Section 23.1: Transaction In a TRY/CATCH
..............................................................................................................
83
Chapter 24: UNION / UNION ALL
Section 24.1: Basic UNION ALL query
Section 24.2: Simple explanation and Example
.......................................................................................................................
........................................................................................................................
.......................................................................................................
85
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
......................................................................................................................................
.......................................................................................................................................
.........................................................................................................................................
..................................................................................................................................
86
.........................................................................................................................................
....................................................................................................................................
Chapter 26: INSERT
....................................................................................................................................................
87
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
.....................................................................................................................................
............................................................................................................
................................................................................................................
.................................................................................
87
Chapter 27: MERGE
....................................................................................................................................................
88
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
...............................................................................................
88
........................................................................................................
Advertisement
................................................................................................
Chapter 28: cross apply, outer apply
..............................................................................................................
90
Section 28.1: CROSS APPLY and OUTER APPLY basics
...........................................................................................
90
55
56
69
70
71
72
73
74
74
75
77
77
77
77
79
79
79
80
84
84
86
86
86
86
86
87
87
87
88
88
Chapter 29: DELETE
...................................................................................................................................................
92
.....................................................................................................................................
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
......................................................................................................
................................................................................................................................
.................................................
92
92
92
92
Chapter 30: TRUNCATE
............................................................................................................................................
94
Section 30.1: Removing all rows from the Employee table
.....................................................................................
94
Chapter 31: DROP Table
..........................................................................................................................................
95
Section 31.1: Check for existence before dropping
Section 31.2: Simple drop
............................................................................................................................................
95
...................................................................................................
95
Chapter 32: DROP or DELETE Database
.........................................................................................................
96
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
....................................................................................................................................
96
..............................................................................................................................
97
...........................................................................................................................
97
.......................................................................................................................
99
........................................................................................................................
99
Chapter 35: XML
........................................................................................................................................................
100
Section 35.1: Query from XML Data Type
...............................................................................................................
100
Chapter 36: Primary Keys
....................................................................................................................................
101
Section 36.1: Creating a Primary Key
Section 36.2: Using Auto Increment
......................................................................................................................
101
........................................................................................................................
101
Chapter 37: Indexes
.................................................................................................................................................
102
.........................................................................................................................................
......................................................................................................................
...............................................................................................................................
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
.........................................................................
103
.............................................................................................
.......................................................................................................................................
............................................................................................................
Chapter 38: Row number
......................................................................................................................................
105
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
.......................................................................................................
.............................................................................................................
............................................................................................................
..................................................................................
105
Section 39.1: Dierence between GROUP BY and DISTINCT
................................................................................
106
Chapter 40: Finding Duplicates on a Column Subset with Detail
....................................................
107
Advertisement
Section 40.1: Students with same name and date of birth
...................................................................................
107
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
..............................................................................................................................
.........................................................................................................................................
..................................................................................................................................................
.............................................................................................................................
.............................................................................................................................
109
.......................................................................................................................................................
.................................................................................................................................................
.................................................................................................................................................
..............................................................................................................................................
......................................................................................................................................................
......................................................................................................................................
102
102
102
103
104
104
105
105
106
108
108
108
109
109
110
110
110
110
110
111
111
112
112
114
114
116
116
116
117
118
119
120
124
125
125
128
128
129
130
130
131
131
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
..............................................................................................................................................
..........................................................................................................................................
..........................................................................
111
...................................................................................................................................................
.......................................................................................................................................
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
................................................................................................................
114
....................................................................................................................
.............................................................................................................................
......................................................................................................................................................
...................................................................................................................................................
...................................................................................................................................................
........................................................................................................................................................
.......................................................................................................................................................
Chapter 43: Functions (Scalar/Single Row)
...............................................................................................
119
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
.....................................................................................................................................
....................................................................................................................
..........................................................................................
120
...................................................................................................
121
Chapter 44: Functions (Analytic)
.....................................................................................................................
123
.....................................................................................................................................
123
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
......................................................................................................................................
.......................................................................................................................................
...................................................................................................
..................................................................................
123
Chapter 45: Window Functions
.........................................................................................................................
127
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
.....