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 Cut through the noise and get real results with a step-by-step approach to learning SQL Key Features A structured, straightforward introduction to SQL for beginners A SQL tutorial with step-by-step exercises and activities that help build key skills Structured to let you progress at your own pace, on your own terms Use your physical print copy to redeem free access to the online interactive edition Book Description You already know you want to learn SQL, and the best way to learn SQL is to learn by doing. The SQL Workshop focuses on building up your SQL database management skills so that you can easily store and retrieve data or become the in-house expert on complex queries. You'll learn from real examples that lead to real results. Throughout The SQL Workshop, you'll take an engaging step-by-step approach to understanding SQL programming. You won't have to sit through any unnecessary theory. If you're short on time you can jump into a single exercise each day or spend an entire weekend learning how to create functions and stored procedures. It's your choice. Learning on your terms, you'll build up and reinforce key skills in a way that feels rewarding. Every physical print copy of The SQL Workshop unlocks access to the interactive edition. With videos detailing all exercises and activities, you'll always have a guided solution. You can also benchmark yourself against assessments, track progress, and receive content updates. You'll even earn a secure credential that you can share and verify online upon completion. It's a premium learning experience that's included with your printed copy. To redeem, follow the instructions located at the start of your book. Fast-paced and direct, The SQL Workshop is the ideal companion for SQL beginners. You'll build and iterate on your code like a software developer, learning along the way. This process means that you'll find that your new skills stick, embedded as best practice. A solid foundation for the years ahead. What you will learn Learn how to create normalized databases Explore how to insert data into tables efficiently Explore various techniques to retrieve data from multiple tables Build advanced queries using techniques like subqueries and views Learn advanced SQL programming techniques such as functions and triggers Who this book is for Our goal at Packt is to help you be successful, in whatever it is you choose to do. The SQL Workshop is an ideal SQL tutorial for the SQL beginner who is just gett .. Get to grips with SQL fundamentals and learn how to efficiently create, read and update information stored in databasesKey FeaturesUnderstand the features and syntax of SQL and use them to query databasesLearn how to create databases and tables and manipulate the data within themCreate advanced queries and apply them on realistic databases with hands-on activitiesBook DescriptionMany software applications are backed by powerful relational database systems, meaning that the skills to be able to maintain a SQL database and reliably retrieve data are in high demand. With its simple syntax and effective data manipulation capabilities, SQL enables you to manage relational databases with ease. The SQL Workshop will help you progress from basic to advanced-level SQL queries in order to create and manage databases successfully.This Workshop begins with an introduction to basic CRUD commands and gives you an overview of the different data types in SQL. You'll use commands for narrowing down the search results within a database and learn about data retrieval from single and multiple tables in a single query. As you advance, you'll use aggregate functions to perform calculations on a set of values, and implement process automation using stored procedures, functions, and triggers. Finally, you'll secure your database against potential threats and use access control to keep your data safe.Throughout this Workshop, you'll use your skills on a realistic database for an online shop, preparing you for solving data problems in the real world.By the end of this book, you'll have built the knowledge, skills and confidence to creatively solve real-world data problems with SQL.What you will learnCreate databases and insert data into themUse SQL queries to create, read, update, and delete dataMaintain data integrity and consistency through normalizationCustomize your basic SQL queries to get the desired outputRefine your database search using the WHERE and HAVING clausesUse joins to fetch data from multiple tables and create custom reportsImprove web application performance by automating processesSecure a database with GRANT and REVOKE privilegesWho this book is forThis Workshop is suitable for anyone who wants to learn how to use SQL to work with databases. No prior SQL or database experience is necessary. Whether you're an aspiring software developer, database engineer, data scientist, or systems administrator, this Workshop will quickly get you up and running. Filled with practical examples and realistic exercises, The SQL Workshop will get you up to speed with the fundamentals of SQL. You'll learn how to use SQL queries and functions to create and manage relational databases, and apply your new skills to creatively solve real-world problems.