Book Image

The SQL Workshop

By : Frank Solomon, Prashanth Jayaram, Awni Al Saqqa
Book Image

The SQL Workshop

By: Frank Solomon, Prashanth Jayaram, Awni Al Saqqa

Overview of this 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.

Related Content you might be interested in

Current Title:

Small book image
The SQL Workshop
Frank Solomon | Prashanth Jayaram | Awni Al Saqqa
Dec, 2019 | 288 pages
Small book image
Learn SQL Database Programming
Josephine Bush
May, 2020 | 564 pages
Small book image
The MySQL Workshop
Thomas Pettit | Scott Cosentino
Apr, 2022 | 726 pages
Small book image
SQL for Data Analytics
Upom Malik | Matt Goldwasser | Benjamin Johnston
Aug, 2019 | 386 pages
Table of Contents (13 chapters)
Preface
Preface
About the Book
About the Chapters
Free Chapter
1
1. SQL Basics
1. SQL Basics
Introduction
Understanding Data
An Overview of Basic SQL Commands
Data Types in SQL
Creating Simple Tables
Populating Your Tables
Summary
2
2. Manipulating Data
2. Manipulating Data
Introduction
The INSERT Operation
The DELETE Operation
The ALTER Operation
The UPDATE Operation
Summary
3
3. Normalization
3. Normalization
Introduction
Primary Key Constraints
Foreign Key Constraints
Preserving Data Integrity
Types of Data Integrity
The Concept of Normalization
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Denormalization
Summary
4
4. The SELECT Statement
4. The SELECT Statement
Introduction
Retrieving All Columns of a Table
Summary
5
5. Shaping Data with the WHERE Clause
5. Shaping Data with the WHERE Clause
Introduction
The WHERE Clause Syntax
The Not Equal Operator
Checking for NULLS
Combining Conditions with the AND, OR, and NOT Operators
Summary
6
6. JOINS
6. JOINS
Introduction
INNER JOIN
RIGHT JOIN
LEFT JOIN
CROSS JOIN
UNION JOIN
Summary
7
7. Subqueries, Cases, and Views
7. Subqueries, Cases, and Views
Introduction
Subqueries
Case Statements
Views
Summary
8
8. SQL Programming
8. SQL Programming
Introduction
Programming for SQL Products – The Basics
Functions
Summary
9
9. Security
9. Security
Introduction
Access Control (Authorization)
Summary
10
10. Aggregate Functions
10. Aggregate Functions
Introduction
Aggregate Functions (SUM, COUNT, AVG, MIN, and MAX) and the GROUP BY Clause
The HAVING Clause
Summary
11
11. Advanced SQL
11. Advanced SQL
Introduction
String Functions
Transactions
Summary
Appendix
Appendix
1. SQL Basics
2. Manipulating Data
3. Normalization
4. The SELECT Statement
5. Shaping Data with the WHERE Clause
6. JOINS
7. Subqueries, Cases, and Views
8. SQL Programming
9. Security
10. Aggregate Functions
11. Advanced SQL
What Next?

LEFT JOIN

This type of JOIN is used when you want to select records that are available in the first table and match records in the second one. It can be represented with the following Venn diagram:

Figure 6.7: LEFT JOIN

The LEFT JOIN represents the highlighted section from TABLE A and the intersected section from TABLE B. Let's look at the syntax:

SELECT [Column List]
  FROM [Table 1] LEFT OUTER JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

The syntax can also be as follows:

SELECT [Column List]
  FROM [Table 1] LEFT JOIN [Table 2] 
    ON [Table 1 Column Name] = [Table 2 Column Name]
WHERE [Condition]

Note

The OUTER word in the query is optional.

This type of join is very similar to the RIGHT JOIN, with the only difference being that it executes the table on the opposite (left) side. Now that we have seen how we can implement LEFT JOIN...

Previous Section
End of Section 5
Next Section