The SQL Workshop

The SQL Workshop

by Frank Solomon, Prashanth Jayaram, Awni Al Saqqa
Released December 2019
Publisher(s): Packt Publishing
ISBN: 9781838642358

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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 getting started. Pick up a Workshop today, and let Packt help you develop skills that stick with you for life.

Table of contents

  1. Preface
    1. About the Book
    2. About the Chapters
      1. Conventions
      2. Before You Begin
        1. To Install MySQL
        2. To Install the Code Bundle
  2. 1. SQL Basics
    1. Introduction
    2. Understanding Data
    3. An Overview of Basic SQL Commands
      1. Creating Databases
      2. The Use of Semicolons
    4. Data Types in SQL
    5. Creating Simple Tables
      1. Exercise 1.01: Building the PACKT_ONLINE_SHOP Database
    6. Populating Your Tables
      1. Exercise 1.02: Inserting Values into the Customers Table of the PACKT_ONLINE_SHOP Database
      2. Activity 1.01: Inserting Values into the Products Table in the PACKT_ONLINE_SHOP Database
    7. Summary
  3. 2. Manipulating Data
    1. Introduction
    2. The INSERT Operation
      1. Performing a Simple INSERT
      2. Exercise 2.01: Inserting One Row of Data into a Table
      3. Multiple Inserts
      4. Exercise 2.02: Specifying Default Values
      5. Using an INSERT Statement to Add Data from Another Dataset
    3. The DELETE Operation
      1. Exercise 2.03: Deleting a record from a table
    4. The ALTER Operation
      1. Exercise 2.04: Manipulating the Auto-Increment Values in a Table
    5. The UPDATE Operation
      1. The Basic UPDATE Statement
      2. ALIASING
      3. Conditional Update of Records
      4. Limiting the Records Using an UPDATE Statement
      5. Exercise 2.05: UPDATE Using Computed Values
      6. The DROP Operation
      7. Activity 2.01: Inserting Additional values to the Products table
    6. Summary
  4. 3. Normalization
    1. Introduction
    2. Primary Key Constraints
    3. Foreign Key Constraints
    4. Preserving Data Integrity
    5. Types of Data Integrity
    6. The Concept of Normalization
    7. First Normal Form (1NF)
    8. Second Normal Form (2NF)
    9. Third Normal Form (3NF)
    10. Denormalization
      1. Exercise 3.01: Building a Relationship between Two Tables
      2. Activity 3.01: Building a Relationship between the Orders and the OrderItems table
    11. Summary
  5. 4. The SELECT Statement
    1. Introduction
      1. What Does the SELECT Statement Do?
    2. Retrieving All Columns of a Table
      1. Selecting Limited Columns
      2. Exercise 4.01: Selecting Columns from a Table
      3. Using Naming Aliases
      4. Exercise 4.02: Aliasing the Column Headers
      5. Activity 4.01: Displaying Particular Columns from the Table
      6. Ordering Results
      7. Ordering Rows According to a Particular Column
      8. Ordering Rows According to Multiple Columns
      9. Using LIMIT
      10. Exercise 4.03: Using the LIMIT Keyword
      11. Using DISTINCT
      12. Using Mathematical Expressions
      13. Exercise 4.04: Calculating the Line Item Total
      14. Exercise 4.05: Calculating Discount
      15. Activity 4.02: Extracting the Top Five Highest Priced Items
    3. Summary
  6. 5. Shaping Data with the WHERE Clause
    1. Introduction
    2. The WHERE Clause Syntax
      1. Exercise 5.01: Implementing Logical Operators in the WHERE Clause
      2. Exercise 5.02: Using the BETWEEN Operator
    3. The Not Equal Operator
      1. Exercise 5.03: Using the != and <> Operators
      2. The LIKE Operator
      3. Exercise 5.04: Using the LIKE Operator to Check a Pattern at the Beginning of a String
      4. Exercise 5.05: Using the LIKE Operator to Check for a Specified Length
    4. Checking for NULLS
      1. Exercise 5.06: Searching for NULL Values
    5. Combining Conditions with the AND, OR, and NOT Operators
      1. Exercise 5.07: Querying Multiple Conditions
      2. Activity 5.01: Combining Conditions to Extract Store Data
    6. Summary
  7. 6. JOINS
    1. Introduction
    2. INNER JOIN
      1. Exercise 6.01: Extracting Orders and Purchaser Information
    3. RIGHT JOIN
      1. Exercise 6.02: Implementing RIGHT JOIN
    4. LEFT JOIN
      1. Exercise 6.03: Implementing LEFT JOIN
    5. CROSS JOIN
      1. Exercise 6.04: Implementing CROSS JOINS
    6. UNION JOIN
      1. Exercise 6.05: Implementing a UNION JOIN
      2. Activity 6.01: Implementing JOINS
    7. Summary
  8. 7. Subqueries, Cases, and Views
    1. Introduction
    2. Subqueries
      1. Exercise 7.01: Working with Subqueries
      2. Activity 7.01: Finding the Product Category Name Using a Subquery
    3. Case Statements
      1. Exercise 7.02: Using Case Statements
      2. Activity 7.02: Categorizing the Shipments Using CASE Statements
    4. Views
      1. Exercise 7.03: Building a View
      2. Activity 7.03: Building a View
    5. Summary
  9. 8. SQL Programming
    1. Introduction
    2. Programming for SQL Products – The Basics
      1. Stored Procedures
      2. Exercise 8.01: Building a MySQL Stored Procedure That Returns a List of Packt Online Shop Order Details
      3. Exercise 8.02: Altering a MySQL Stored Procedure
      4. Activity 8.01: Building a Stored Procedure
    3. Functions
      1. Exercise 8.03: Build a MySQL Function
      2. Activity 8.02: Working with MySQL Functions
      3. Triggers
      4. Exercise 8.04: Build a MySQL Trigger
      5. Activity 8.03: Building a Trigger
    4. Summary
  10. 9. Security
    1. Introduction
    2. Access Control (Authorization)
      1. Exercise 9.01: Creating New MySQL users
      2. Exercise 9.02: Granting EXECUTE permission in MySQL
      3. Activity 9.01: Grant UPDATE permission on a table in MySQL
    3. Summary
  11. 10. Aggregate Functions
    1. Introduction
    2. Aggregate Functions (SUM, COUNT, AVG, MIN, and MAX) and the GROUP BY Clause
      1. Exercise 10.01: Implementing Aggregate Functions
    3. The HAVING Clause
      1. Exercise 10.02: Implementing the HAVING Clause
      2. The Differences between the SQL HAVING and WHERE Clauses
      3. SQL OVER and PARTITION BY
      4. The RANK and DENSE_RANK Functions
      5. Exercise 10.03: Implementing RANK
      6. Activity 10.01: Working with Aggregates
    4. Summary
  12. 11. Advanced SQL
    1. Introduction
    2. String Functions
      1. Exercise 11.01: Building a MySQL Query that Returns the OrderID, Quantity, and Notes Columns
      2. Exercise 11.02: Using LIKE in a Stored Procedure
      3. Activity 11.01: Implementing the LIKE Operator
      4. Dealing with NULL and COALESCE
      5. The COALESCE Function
      6. Exercise 11.03: Using the COALESCE Function to Handle a NULL Value in a Combined Set of Values
      7. Finding Duplicate Table Rows
    3. Transactions
      1. Activity 11.02: Using Transactions
    4. Summary
  13. Appendix
    1. 1. SQL Basics
      1. Activity 1.01: Inserting Values into the Products Table in the PACKT_ONLINE_SHOP Database
    2. 2. Manipulating Data
      1. Activity 2.01: Inserting Additional values to the Products Table
    3. 3. Normalization
      1. Activity 3.01: Building a Relationship between the Orders and the OrderItems table
    4. 4. The SELECT Statement
      1. Activity 4.01: Displaying Particular Columns from the Table
      2. Activity 4.02: Extracting the Top Five Highest Paid Items
    5. 5. Shaping Data with the WHERE Clause
      1. Activity 5.01: Combining Conditions to Extract Store Data
    6. 6. JOINS
      1. Activity 6.01: Implementing JOINS
    7. 7. Subqueries, Cases, and Views
      1. Activity 7.01: Finding the Product Category Name Using a Subquery
      2. Activity 7.02: Categorizing the Shipments Using CASE Statements
      3. Activity 7.03: Building a View
    8. 8. SQL Programming
      1. Activity 8.01: Building a Stored Procedure
      2. Activity 8.02: Working with MySQL Functions
      3. Activity 8.03: Building a Trigger
    9. 9. Security
      1. Activity 9.01: Grant UPDATE permission on a table in MySQL
    10. 10. Aggregate Functions
      1. Activity 10.01: Working with Aggregates
    11. 11. Advanced SQL
      1. Activity 11.01: Implementing the LIKE Operator
      2. Activity 11.02: Using Transactions
    12. What Next?
      1. The Python Workshop
        1. The Applied SQL Data Analytics Workshop
        2. The Java Workshop
        3. Please leave a review

Product information

  • Title: The SQL Workshop
  • Author(s): Frank Solomon, Prashanth Jayaram, Awni Al Saqqa
  • Release date: December 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781838642358