The SQL Workshop: A New, Interactive Approach to Learning SQL

Cover......Page 1
FM......Page 2
Copyright......Page 3
Table of Contents......Page 4
Preface......Page 12
Chapter 1: SQL Basics......Page 16
Understanding Data......Page 17
An Overview of Basic SQL Commands......Page 18
Creating Databases......Page 20
Data Types in SQL......Page 22
Creating Simple Tables......Page 25
Exercise 1.01: Building the PACKT_ONLINE_SHOP Database......Page 27
Populating Your Tables......Page 30
Exercise 1.02: Inserting Values into the Customers Table of the PACKT_ONLINE_SHOP Database......Page 32
Activity 1.01: Inserting Values into the Products Table in the PACKT_ONLINE_SHOP Database......Page 33
Summary......Page 34
Chapter 2: Manipulating Data......Page 36
Performing a Simple INSERT......Page 37
Exercise 2.01: Inserting One Row of Data into a Table......Page 38
Multiple Inserts......Page 39
Exercise 2.02: Specifying Default Values......Page 40
Using an INSERT Statement to Add Data from Another Dataset......Page 42
The DELETE Operation......Page 43
Exercise 2.03: Deleting a record from a table......Page 44
Exercise 2.04: Manipulating the Auto-Increment Values in a Table......Page 45
The UPDATE Operation......Page 46
The Basic UPDATE Statement......Page 47
ALIASING......Page 48
Limiting the Records Using an UPDATE Statement......Page 49
The DROP Operation......Page 50
Summary......Page 51
Chapter 3: Normalization......Page 54
Primary Key Constraints......Page 55
Foreign Key Constraints......Page 56
Preserving Data Integrity......Page 59
The Concept of Normalization......Page 60
Second Normal Form (2NF)......Page 64
Third Normal Form (3NF)......Page 69
Exercise 3.01: Building a Relationship between Two Tables......Page 71
Activity 3.01: Building a Relationship between the Orders and the OrderItems table......Page 74
Summary......Page 75
Chapter 4: The SELECT Statement......Page 78
Introduction......Page 79
What Does the SELECT Statement Do?......Page 80
Retrieving All Columns of a Table......Page 81
Exercise 4.01: Selecting Columns from a Table......Page 82
Exercise 4.02: Aliasing the Column Headers......Page 83
Ordering Results......Page 85
Ordering Rows According to a Particular Column......Page 86
Ordering Rows According to Multiple Columns......Page 87
Using LIMIT......Page 90
Exercise 4.03: Using the LIMIT Keyword......Page 91
Using DISTINCT......Page 92
Exercise 4.04: Calculating the Line Item Total......Page 94
Exercise 4.05: Calculating Discount......Page 95
Summary......Page 96
Chapter 5: Shaping Data with the WHERE Clause......Page 98
Introduction......Page 99
The WHERE Clause Syntax......Page 100
Exercise 5.01: Implementing Logical Operators in the WHERE Clause......Page 101
The Not Equal Operator......Page 103
Exercise 5.03: Using the != and <> Operators......Page 104
The LIKE Operator......Page 105
Exercise 5.05: Using the LIKE Operator to Check for a Specified Length......Page 107
Exercise 5.06: Searching for NULL Values......Page 108
Exercise 5.07: Querying Multiple Conditions......Page 109
Activity 5.01: Combining Conditions to Extract Store Data......Page 111
Summary......Page 112
Chapter 6: JOINS......Page 114
INNER JOIN......Page 115
Exercise 6.01: Extracting Orders and Purchaser Information......Page 116
RIGHT JOIN......Page 118
Exercise 6.02: Implementing RIGHT JOIN......Page 119
LEFT JOIN......Page 121
Exercise 6.03: Implementing LEFT JOIN......Page 122
CROSS JOIN......Page 123
Exercise 6.04: Implementing CROSS JOINS......Page 124
UNION JOIN......Page 125
Exercise 6.05: Implementing a UNION JOIN......Page 126
Summary......Page 127
Chapter 7: Subqueries, Cases, and Views......Page 130
Subqueries......Page 131
Exercise 7.01: Working with Subqueries......Page 133
Activity 7.01: Finding the Product Category Name Using a Subquery......Page 134
Case Statements......Page 135
Exercise 7.02: Using Case Statements......Page 137
Activity 7.02: Categorizing the Shipments Using CASE Statements......Page 139
Views......Page 140
Exercise 7.03: Building a View......Page 142
Activity 7.03: Building a View......Page 143
Summary......Page 144
Chapter 8: SQL Programming......Page 146
Programming for SQL Products – The Basics......Page 147
Stored Procedures......Page 153
Exercise 8.01: Building a MySQL Stored Procedure That Returns a List of Packt Online Shop Order Details......Page 164
Exercise 8.02: Altering a MySQL Stored Procedure......Page 169
Activity 8.01: Building a Stored Procedure......Page 170
Functions......Page 171
Exercise 8.03: Build a MySQL Function......Page 181
Triggers......Page 183
Exercise 8.04: Build a MySQL Trigger......Page 197
Summary......Page 199
Chapter 9: Security......Page 202
Introduction......Page 203
Exercise 9.01: Creating New MySQL users......Page 204
Exercise 9.02: Granting EXECUTE permission in MySQL......Page 206
Summary......Page 208
Chapter 10: Aggregate Functions......Page 210
Aggregate Functions (SUM, COUNT, AVG, MIN, and MAX) and the GROUP BY Clause......Page 211
Exercise 10.01: Implementing Aggregate Functions......Page 218
The HAVING Clause......Page 219
Exercise 10.02: Implementing the HAVING Clause......Page 221
The Differences between the SQL HAVING and WHERE Clauses......Page 223
SQL OVER and PARTITION BY......Page 226
The RANK and DENSE_RANK Functions......Page 229
Exercise 10.03: Implementing RANK......Page 233
Summary......Page 235
Chapter 11: Advanced SQL......Page 238
String Functions......Page 239
Exercise 11.01: Building a MySQL Query that Returns the OrderID, Quantity, and Notes Columns......Page 242
Exercise 11.02: Using LIKE in a Stored Procedure......Page 243
Dealing with NULL and COALESCE......Page 244
The COALESCE Function......Page 246
Exercise 11.03: Using the COALESCE Function to Handle a NULL Value in a Combined Set of Values......Page 248
Finding Duplicate Table Rows......Page 249
Transactions......Page 252
Summary......Page 256
Appendix......Page 258
Index......Page 280