Preface

About

This section briefly introduces the coverage of this book, the technical skills you'll need to get started, and the software requirements required to complete all of the included activities and exercises.

About the Book

Many 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.

About the Chapters

Chapter 1, SQL Basics, explains how to create a simple database and how to create tables within databases. We will also learn how to populate data within a table.

Chapter 2, Manipulating Data, guides us through how to alter tables and delete and update entries within a table.

Chapter 3, Normalization, explains how to normalize tables within a database such that data integrity is maintained.

Chapter 4, The SELECT Statement, covers how to write basic queries to retrieve data from the database.

Chapter 5, Shaping Data with the WHERE Clause, covers implementing conditional clauses within our queries such that we get fine-grained control over our data.

Chapter 6, JOINS, talks about retrieving data from multiple tables by performing various join operations.

Chapter 7, Subqueries, Cases, and Views, talks about ways to retrieve data from intermediary tables using views and then sub-queries to further filter down results.

Chapter 8, SQL Programming, talks about advanced SQL concepts such as the functions and triggers.

Chapter 9, Security, looks at providing and revoking access to users on tables and databases.

Chapter 10, Aggregate Functions, teaches how use SQL aggregate functions and how to solve problems with them. We will also look at advanced clauses, such as the GROUP BY and the HAVING clauses, and see how they can help us to fine-tune our results

Chapter 11, Advanced SQL, looks at functions in SQL and how they can be used as powerful filtering tools.

Conventions

Code words in text, database table names, screen text, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "We'll start with the Orders table since the Orders table ties the orders together with the OrderItems table."

A block of code is set as follows:

USE PACKT_ONLINE_SHOP;

Before You Begin

Each great journey begins with a humble step. Our upcoming adventure in the land of SQL is no exception. Before you can begin, you need to be prepared with the most productive environment. In this section, you will see how to do that.

To Install MySQL

To install MySQL, follow the steps present in the following documentation: https://packt.live/2rxXXv1

To Install the Code Bundle

Download the code files from GitHub at https://packt.live/2QCKNqB and place them in a new folder called C:\Code on your local system. Refer to these code files for the complete code bundle.