The SQL Workshop

A New, Interactive Approach to Learning SQL

Frank Solomon, Prashanth Jayaram, Awni Al Saqqa

• Publisher: Packt Publishing
• Year: 2019
• ISBN:
  9781838649081
• Edition: 1
• Topic: Computer Science
• Subtopic: Open Source Programming
• Index: Computer Science

Get to grips with SQL fundamentals and learn how to efficiently create, read and update information stored in databases

Key Features

• Understand the features and syntax of SQL and use them to query databases
• Learn how to create databases and tables and manipulate the data within them
• Create advanced queries and apply them on realistic databases with hands-on activities

Book Description

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.

What you will learn

• Create databases and insert data into them
• Use SQL queries to create, read, update, and delete data
• Maintain data integrity and consistency through normalization
• Customize your basic SQL queries to get the desired output
• Refine your database search using the WHERE and HAVING clauses
• Use joins to fetch data from multiple tables and create custom reports
• Improve web application performance by automating processes
• Secure a database with GRANT and REVOKE privileges

Who this book is for

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

1. SQL Basics

Overview

This chapter covers the very basic concepts of SQL that will get you started with writing simple commands. By the end of this chapter, you will be able to identify the difference between structured and unstructured data, explain the basic SQL concepts, create tables using the CREATE statement, and insert values into tables using SQL commands.

Introduction

The vast majority of companies today work with large amounts of data. This could be product information, customer data, client details, employee data, and so on. Most people who are new to working with data will do so using spreadsheets. Software such as Microsoft Excel has many tools for manipulating and analyzing data, but as the volume and complexity of the data you're working with increases, these tools may become inefficient.

A more powerful and controlled way of working with data is to store it in a database and use SQL to access and manipulate it. SQL works extremely well for organized data and can be used very effectively to insert, retrieve, and manipulate data with just a few lines of code. In this chapter, we'll get an introduction to SQL and see how to create databases and tables, as well as how to insert values into them.

Understanding Data

For most companies, storing and retrieving data is a day-to-day activity. Based on how data is stored, we can broadly classify data as structured or unstructured. Unstructured data, simply put, is data that is not well-organized. Documents, PDFs, and videos fall into this category—they contain a mixture of different data types (text, images, audio, video, and so on) that have no consistent relationship between them. Media and publishing are examples of industries that deal with unstructured data such as this.

In this book, our focus will be on structured data. Structured data is organized according to a consistent structure. As such, structured data can be easily organized into tables. Thanks to its consistent organization, working with structured data is easier, and it can be processed more effectively. Tables are collections of entities or tuples (rows) and attributes (columns).

For example, consider the following table:

Figure 1.1: An example student's database table

For each row, there is a clear relationship; a given student takes a particular subject and achieves a specific score in that subject. The columns are also known as fields, while the rows are known as records.

Data that is presented in tabular form can be stored in a relational database. Relational databases, as the name suggests, store data that has a certain relationship with another piece of data. A Relational Database Management System (RDBMS) is a system that's used to manage relational data. SQL works very well with relational data. Popular RDBMSs include Microsoft SQL Server, MySQL, and Oracle. Throughout this book, we will be working with MySQL. We can use various SQL commands to work with data in relational databases. We'll have a brief look at them in the next section.

An Overview of Basic SQL Commands

SQL (often pronounced "sequel") stands for Structured Query Language. A query in SQL is constructed using different commands. These commands are classified into what are called sublanguages of SQL. Even if you think you know them already, give this a read to see if these seem more relatable to you. There are five sublanguages in SQL, as follows:

• Data Definition Language (DDL): As the name suggests, the commands that fall under this category work with defining either a table, a database, or anything within. Any command that talks about creating something in SQL is part of DDL. Some examples of such commands are CREATE, ALTER, and DROP.
  The following table shows the DDL commands:
  

Figure 1.2: DDL commands

• Data Manipulation Language (DML): In DML, you do not deal with the containers of data but the data itself. When you must update the data itself, or perform calculations or operations on it, you use the DML. The commands that form part of this language (or sublanguage) include INSERT, UPDATE, MERGE, and DELETE.
  DML allows you to work on the data without modifying the container or stored procedures. A copy of the data is created and the operations are performed on this copy of the data. These operations are performed using the DML. The following table shows the DML commands:
  

Figure 1.3: DML commands

• Data Control Language (DCL): When we sit back and think about what the word control means in the context of data, we think of allowing and disallowing actions on the data. In SQL terms, or in terms of data, this is about authorization. Therefore, the commands that fall in this category are GRANT and REVOKE. They control access to the data. The following table explains them:

Figure 1.4: DCL commands

• Transaction Control Language (TCL): Anything that makes a change to the data is called a transaction. When you perform a data manipulation operation, the manipulation happens to data in a temporary location and not the table/database itself. The result is shown after the operation. In order to write or remove something from the database, you need to use a command to ask the database to update itself with the new content. Applying these changes to the database is called a transaction and is done using the TCL. The commands associated with this language are COMMIT and ROLLBACK. The following table explains these commands in detail:

Figure 1.5: TCL commands

• Data Query Language (DQL): The final part of this section regarding the classification of commands is the DQL. This is used to fetch data from the database with the SELECT command. It's explained in detail in the following table:

F...