SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK SQL NOTES FOR PROFESSIONALS

SUMMARY

279

About 1
Chapter 1: Getting started with SQL 2
Section 11: Overview 2
Chapter 2: Identifier 3
Section 21: Unquoted identifiers 3
Chapter 3: Data Types 4
Section 31: DECIMAL and NUMERIC 4
Section 32: FLOAT and REAL 4
Section 33: Integers 4
Section 34: MONEY and SMALLMONEY 4
Section 35: BINARY and VARBINARY 4
Section 36: CHAR and VARCHAR 5
Section 37: NCHAR and NVARCHAR 5
Section 38: UNIQUEIDENTIFIER 5
Chapter 4: NULL 6
Section 41: Filtering for NULL in queries 6
Section 42: Nullable columns in tables 6
Section 43: Updating fields to NULL 6
Section 44: Inserting rows with NULL fields 7
Chapter 5: Example Databases and Tables 8
Section 51: Auto Shop Database 8
Section 52: Library Database 10
Section 53: Countries Table 13
Chapter 6: SELECT 14
Section 61: Using the wildcard character to select all columns in a query 14
Section 62: SELECT Using Column Aliases 15
Section 63: Select Individual Columns 18
Section 64: Selecting specified number of records 19
Section 65: Selecting with Condition 20
Section 66: Selecting with CASE 20
Section 67: Select columns which are named after reserved keywords 21
Section 68: Selecting with table alias 21
Section 69: Selecting with more than 1 condition 22
Section 610: Selecting without Locking the table 23
Section 611: Selecting with Aggregate functions 23
Section 612: Select with condition of multiple values from column 24
Section 613: Get aggregated result for row groups 24
Section 614: Selection with sorted Results 25
Section 615: Selecting with null 25
Section 616: Select distinct (unique values only) 25
Section 617: Select rows from multiple tables 26
Chapter 7: GROUP BY 27
Section 71: Basic GROUP BY example 27
Section 72: Filter GROUP BY results using a HAVING clause 28
Section 73: USE GROUP BY to COUNT the number of rows for each unique entry in a given column 28
Section 74: ROLAP aggregation (Data Mining) 29
Chapter 8: ORDER BY 31
Section 81: Sorting by column number (instead of name) 31
Section 82: Use ORDER BY with TOP to return the top x rows based on a columns value 31
Section 83: Customizeed sorting order 32
Section 84: Order by Alias 32
Section 85: Sorting by multiple columns 33
Chapter 9: AND & OR Operators 34
Section 91: AND OR Example 34
Chapter 10: CASE 35
Section 101: Use CASE to COUNT the number of rows in a column match a condition 35
Section 102: Searched CASE in SELECT (Matches a boolean expression) 36
Section 103: CASE in a clause ORDER BY 36
Section 104: Shorthand CASE in SELECT 36
Section 105: Using CASE in UPDATE 37
Section 106: CASE use for NULL values ordered last 37
Section 107: CASE in ORDER BY clause to sort records by lowest value of 2 columns 38
Chapter 11: LIKE operator 39
Section 111: Match open-ended pattern 39
Section 112: Single character match 40
Section 113: ESCAPE statement in the LIKE-query 40
Section 114: Search for a range of characters 41
Section 115: Match by range or set 41
Section 116: Wildcard characters 41
Chapter 12: IN clause 43
Section 121: Simple IN clause 43
Section 122: Using IN clause with a subquery 43
Chapter 13: Filter results using WHERE and HAVING 44
Section 131: Use BETWEEN to Filter Results 44
Section 132: Use HAVING with Aggregate Functions 45
Section 133: WHERE clause with NULL/NOT NULL values 45
Section 134: Equality 46
Section 135: The WHERE clause only returns rows that match its criteria 46
Section 136: AND and OR 46
Section 137: Use IN to return rows with a value contained in a list 47
Section 138: Use LIKE to find matching strings and substrings 47
Section 139: Where EXISTS 48
Section 1310: Use HAVING to check for multiple conditions in a group 48
Chapter 14: SKIP TAKE (Pagination) 50
Section 141: Limiting amount of results 50
Section 142: Skipping then taking some results (Pagination) 50
Section 143: Skipping some rows from result 51
Chapter 15: EXCEPT 52
Section 151: Select dataset except where values are in this other dataset 52
Chapter 16: EXPLAIN and DESCRIBE 53
Section 161: EXPLAIN Select query 53
Section 162: DESCRIBE tablename; 53
Chapter 17: EXISTS CLAUSE 54
Section 171: EXISTS CLAUSE 54
Chapter 18: JOIN 55
Section 181: Self Join 55
Section 182: Di?erences between inner/outer joins 56
Section 183: JOIN Terminology: Inner, Outer, Semi, Anti 59
Section 184: Left Outer Join 69
Section 185: Implicit Join 70
Section 186: CROSS JOIN 71
Section 187: CROSS APPLY & LATERAL JOIN 72
Section 188: FULL JOIN 73
Section 189: Recursive JOINs 74
Section 1810: Basic explicit inner join 74
Section 1811: Joining on a Subquery 75
Chapter 19: UPDATE 76
Section 191: UPDATE with data from another table 76
Section 192: Modifying existing values 77
Section 193: Updating Specified Rows 77
Section 194: Updating All Rows 77
Section 195: Capturing Updated records 77
Chapter 20: CREATE Database 78
Section 201: CREATE Database 78
Chapter 21: CREATE TABLE 79
Section 211: Create Table From Select 79
Section 212: Create a New Table 79
Section 213: CREATE TABLE With FOREIGN KEY 79
Section 214: Duplicate a table 80
Section 215: Create a Temporary or In-Memory Table 80
Chapter 22: CREATE FUNCTION 82
Section 221: Create a new Function 82
Chapter 23: TRY/CATCH 83
Section 231: Transaction In a TRY/CATCH 83
Chapter 24: UNION / UNION ALL 84
Section 241: Basic UNION ALL query 84
Section 242: Simple explanation and Example 85
Chapter 25: ALTER TABLE 86
Section 251: Add Column(s) 86
Section 252: Drop Column 86
Section 253: Add Primary Key 86
Section 254: Alter Column 86
Section 255: Drop Constraint 86
Chapter 26: INSERT 87
Section 261: INSERT data from another table using SELECT 87
Section 262: Insert New Row 87
Section 263: Insert Only Specified Columns 87
Section 264: Insert multiple rows at once 87
Chapter 27: MERGE 88
Section 271: MERGE to make Target match Source 88
Section 272: MySQL: counting users by name 88
Section 273: PostgreSQL: counting users by name 88
Chapter 28: cross apply, outer apply 90
Section 281: CROSS APPLY and OUTER APPLY basics 90
Chapter 29: DELETE 92
Section 291: DELETE all rows 92
Section 292: DELETE certain rows with WHERE 92
Section 293: TRUNCATE clause 92
Section 294: DELETE certain rows based upon comparisons with other tables 92
Chapter 30: TRUNCATE 94
Section 301: Removing all rows from the Employee table 94
Chapter 31: DROP Table 95
Section 311: Check for existence before dropping 95
Section 312: Simple drop 95
Chapter 32: DROP or DELETE Database 96
Section 321: DROP Database 96
Chapter 33: Cascading Delete 97
Section 331: ON DELETE CASCADE 97
Chapter 34: GRANT and REVOKE 99
Section 341: Grant/revoke privileges 99
Chapter 35: XML 100
Section 351: Query from XML Data Type 100
Chapter 36: Primary Keys 101
Section 361: Creating a Primary Key 101
Section 362: Using Auto Increment 101
Chapter 37: Indexes 102
Section 371: Sorted Index 102
Section 372: Partial or Filtered Index 102
Section 373: Creating an Index 102
Section 374: Dropping an Index, or Disabling and Rebuilding it 103
Section 375: Clustered, Unique, and Sorted Indexes 103
Section 376: Rebuild index 104
Section 377: Inserting with a Unique Index 104
Chapter 38: Row number 105
Section 381: Delete All But Last Record (1 to Many Table) 105
Section 382: Row numbers without partitions 105
Section 383: Row numbers with partitions 105
Chapter 39: SQL Group By vs Distinct 106
Section 391: Di?erence between GROUP BY and DISTINCT 106
Chapter 40: Finding Duplicates on a Column Subset with Detail 107
Section 401: Students with same name and date of birth 107
Chapter 41: String Functions 108
Section 411: Concatenate 108
Section 412: Length 108
Section 413: Trim empty spaces 109
Section 414: Upper & lower case 109
Section 415: Split 109
Section 416: Replace 110
Section 417: REGEXP 110
Section 418: Substring 110
Section 419: Stu? 110
Section 4110: LEFT - RIGHT 110
Section 4111: REVERSE 111
Section 4112: REPLICATE 111
Section 4113: Replace function in sql Select and Update query 111
Section 4114: INSTR 112
Section 4115: PARSENAME 112
Chapter 42: Functions (Aggregate) 114
Section 421: Conditional aggregation 114
Section 422: List Concatenation 114
Section 423: SUM 116
Section 424: AVG() 116
Section 425: Count 116
Section 426: Min 117
Section 427: Max 118
Chapter 43: Functions (Scalar/Single Row) 119
Section 431: Date And Time 119
Section 432: Character modifications 120
Section 433: Configuration and Conversion Function 120
Section 434: Logical and Mathmetical Function 121
Chapter 44: Functions (Analytic) 123
Section 441: LAG and LEAD 123
Section 442: PERCENTILE_DISC and PERCENTILE_CONT 123
Section 443: FIRST_VALUE 124
Section 444: LAST_VALUE 125
Section 445: PERCENT_RANK and CUME_DIST 125
Chapter 45: Window Functions 127
Section 451: Setting up a flag if other rows have a common property 127
Section 452: Finding "out-of-sequence" records using the LAG() function 127
Section 453: Getting a running total 128
Section 454: Adding the total rows selected to every row 128
Section 455: Getting the N most recent rows over multiple grouping 129
Chapter 46: Common Table Expressions 130
Section 461: generating values 130
Section 462: recursively enumerating a subtree 130
Section 463: Temporary query 131
Section 464: recursively going up in a tree 131
Section 465: Recursively generate dates, extended to include team rostering as example 132
Section 466: Oracle CONNECT BY functionality with recursive CTEs 132
Chapter 47: Views 134
Section 471: Simple views 134
Section 472: Complex views 134
Chapter 48: Materialized Views 135
Section 481: PostgreSQL example 135
Chapter 49: Comments 136
Section 491: Single-line comments 136
Section 492: Multi-line comments 136
Chapter 50: Foreign Keys 137
Section 501: Foreign Keys explained 137
Section 502: Creating a table with a foreign key 137
Chapter 51: Sequence 139
Section 511: Create Sequence 139
Section 512: Using Sequences 139
Chapter 52: Subqueries 140
Section 521: Subquery in FROM clause 140
Section 522: Subquery in SELECT clause 140
Section 523: Subquery in WHERE clause 140
Section 524: Correlated Subqueries 140
Section 525: Filter query results using query on di?erent table 140
Section 526: Subqueries in FROM clause 141
Section 527: Subqueries in WHERE clause 141
Chapter 53: Execution blocks 142
Section 531: Using BEGIN END 142
Chapter 54: Stored Procedures 143
Section 541: Create and call a stored procedure 143
Chapter 55: Triggers 144
Section 551: CREATE TRIGGER 144
Section 552: Use Trigger to manage a "Recycle Bin" for deleted items 144
Chapter 56: Transactions 145
Section 561: Simple Transaction 145
Section 562: Rollback Transaction 145
Chapter 57: Table Design 146
Section 571: Properties of a well designed table 146
Chapter 58: Synonyms 147
Section 581: Create Synonym 147
Chapter 59: Information Schema 148
Section 591: Basic Information Schema Search 148
Chapter 60: Order of Execution 149
Section 601: Logical Order of Query Processing in SQL 149
Chapter 61: Clean Code in SQL 150
Section 611: Formatting and Spelling of Keywords and Names 150
Section 612: Indenting 150
Section 613: SELECT * 151
Section 614: Joins 152
Chapter 62: SQL Injection 153
Section 621: SQL injection sample 153
Section 622: simple injection sample 154
Credits 155
You may also like 159