Unlocking the Power of SQL: A Comprehensive Guide to Structured Query Language and Database Operations Part 2

Continuing the thread from my previous SQL article, I invite you to explore the previous phase of our discussion, which can be found at the following link:

https://medium.com/@noransaber685/unlocking-the-power-of-sql-a-comprehensive-guide-to-structured-query-language-and-database-2f9d6e6cad0d

Table of content

1. NULL Value
2. DELETE statement
3. COUNT(), AVG(), SUM()
4. Allies
5. JOIN, Types, INNER, LEFT, RIGHT

How to Test for NULL Values?

• You can’t test for NULL values with comparison operators, such as =, <, or <>.
• We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

• Imagine your data is like a treasure chest. Sometimes, a treasure chest might be empty (NULL). To check if a treasure chest is empty, you use special operators, not the usual ones.

Example:

-- Find customers with missing phone numbers
SELECT CustomerName
FROM Customers
WHERE Phone IS NULL;

• Here, you’re looking in the “Customers” treasure chest to see if any of them have an empty Phone box. The IS NULL operator helps you do that.

IS NOT NULL Syntax

On the other hand, if you want to find customers who do have a phone number (a non-empty box), you use the IS NOT NULL operator.

Example:

-- Find customers with valid email addresses
SELECT CustomerName
FROM Customers
WHERE Email IS NOT NULL;

• Now, you’re checking if any customers have a filled Email box. The IS NOT NULL operator helps you find these.

Combining Conditions

• You can also combine these NULL tests with other conditions using logical operators like AND and OR.

Example:

-- Find customers with missing phone numbers or emails
SELECT CustomerName
FROM Customers
WHERE Phone IS NULL OR Email IS NULL;

• This query looks for customers who either have no Phone or no Email.

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Deleting Specific Records

Imagine your table as a collection of items, and you want to remove specific items that meet a certain condition.

Example:

-- Delete orders that were shipped before a specific date
DELETE FROM Orders
WHERE ShippedDate < '2022-01-01';

• In this example, you’re deleting orders that were shipped before January 1, 2022. The WHERE clause specifies the condition that needs to be met for the deletion to occur.

Deleting All Records

If you want to clear out your entire table, you can omit the WHERE clause.

Example:

-- Delete all records from the Products table
DELETE FROM Products;

• This will remove all records from the “Products” table, effectively clearing it.

Deleting with Subqueries

You can use subqueries to specify more complex conditions for deletion.

Example:

-- Delete customers who have not placed any orders
DELETE FROM Customers
WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders);

• This query deletes customers who have not placed any orders. The subquery finds distinct CustomerIDs from the “Orders” table, and the main query deletes customers not present in that subquery result.

Deleting from Joined Tables

• You can use joins to delete records based on conditions involving multiple tables.

Example:

-- Delete products from a specific supplier
DELETE FROM Products
WHERE SupplierID IN (SELECT SupplierID FROM Suppliers WHERE Country = 'USA');

• Here, you’re deleting products supplied by companies located in the USA. The subquery identifies SupplierIDs of suppliers from the USA, and the main query deletes products associated with those suppliers.

The SQL COUNT(), AVG() and SUM() Functions

COUNT() — Counting Rows: Think of your table as a collection of items, and you want to count how many items match a certain condition.

Example:

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example

-- count the number of orders for a specific customer
SELECT COUNT(OrderID)
FROM Orders
WHERE CustomerID = 'ALFKI';

• In this example, we are counting the number of orders made by the customer with the ID ‘ALFKI’. The COUNT() function helps you do that.

AVG() Syntax

The AVG() function returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

AVG() — Finding Average: Imagine you have a column with numbers, and you want to find the average (mean) of those numbers.

Example:

-- Find the average price of products
SELECT AVG(Price)
FROM Products;

• Here, you’re calculating the average price of all products in the “Products” table. The AVG() function computes this for you.

SUM() Syntax

The SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

SUM() — Adding It Up: If you have a numeric column and you want to add up all the values, you can use the SUM() function.

Example

 — Calculate the total revenue from orders
SELECT SUM(TotalPrice)
FROM OrderDetails;

• This query sums up the “TotalPrice” values from the “OrderDetails” table, giving you the total revenue.

Combining Functions with Conditions

You can combine these functions with conditions to get more specific results.

-- Count the number of expensive products
SELECT COUNT(ProductID)
FROM Products
WHERE Price > 100;

• Here we’re counting how many products have a price higher than $100.

SQL Aliases

Aliases — Giving Things Nicknames:

• Think of aliases as nicknames you give to your tables or columns. They’re like shorthand names you use to make your SQL queries easier to read and understand.
• Aliases are often used to make column names more readable.
• An alias only exists for the duration of that query.
• An alias is created with the AS keyword.

Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax

SELECT column_name(s)
FROM table_name AS alias_name;

Column Aliases — Readable Names

Imagine you have a long column name, and you want to make it simpler and more readable in your query result.

Example:

-- Show product names with shorter column name "Name"
SELECT ProductName AS Name
FROM Products;

• In this query, you’re renaming the “ProductName” column as just “Name” in the query result.

Table Aliases — Shortening Table Names

• Sometimes you’re working with multiple tables, and their names can get lengthy. Aliases can make things cleaner.

Example:

-- Select orders with customer names from an alias "C"
SELECT O.OrderID, C.CustomerName
FROM Orders AS O
JOIN Customers AS C ON O.CustomerID = C.CustomerID;

• Here, you’re using “O” as an alias for the “Orders” table and “C” as an alias for the “Customers” table. This makes the query easier to write and read.

SQL JOINs

Imagine you have different pieces of information spread across different tables. SQL JOINs help you put those pieces together to make one big picture.

In a relational database, data is often spread across multiple tables. SQL JOINs allow you to combine data from two or more tables based on a related column, creating a single result set that combines information from different sources.

Basic Syntax of a JOIN

You must provide the tables you want to join and the columns on which to do so in a JOIN’s basic syntax. This is the overall organisation:

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

• table1 and table2 are the tables you're joining.
• columns represent the specific columns you want to retrieve from the combined result.
• ON specifies the condition for joining, usually based on a common column between the tables.

Different Types of SQL JOINs

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either the left or right table

SQL INNER JOIN Keyword

INNER JOIN:

• Returns only the rows where there’s a match between the specified columns in both tables.
• If there’s no match, the row is excluded from the result.

Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• You can think about SELECT as return, so when you write select you try to return specific data from the table
• You can think about ON like it because it deals with conditions: if Customers.CustomerID EQUAL Orders.CustomerID return Data you selected

SQL LEFT JOIN Keyword

LEFT JOIN (or LEFT OUTER JOIN):

• Returns all rows from the left table and the matching rows from the right table.
• If there’s no match, NULL values are included for the columns from the right table.

Example:

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side if there is no match.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID

• Again let’s think about SELECT as return, so return Customer Name, and OrderID
• LEFT JOIN: We will join the order table even if there is nothing matched
• ON: let’s think about it like if, if the CustomerID EQUAL CustomerID in the order

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side if there is no match.

RIGHT JOIN Syntax

• Returns all rows from the right table and the matching rows from the left table.
• If there’s no match, NULL values are included for the columns from the left table.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SQL FULL OUTER JOIN Keyword

• Returns all rows when there’s a match in either the left or right table.
• If there’s no match, NULL values are included for the columns from the non-matching table.

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

The SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

Grouping Similar Things: Imagine you have a bunch of items, like fruits, and you want to organize them based on something they have in common, like their colours. GROUP BY helps you put similar things together.

Why Grouping Matters: Think of GROUP BY like making piles of the same stuff. It’s like putting all the red fruits in one pile, the green ones in another, and so on.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result set by one or more columns.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example — Counting Items by Category: Imagine you have a table of products, and you want to count how many products are in each category.

-- Count the number of products in each category
SELECT Category, COUNT(*) as NumberOfProducts
FROM Products
GROUP BY Category;

• SELECT Category, COUNT(*) as NumberOfProducts: Here, you’re selecting the “Category” column from the “Products” table and using the COUNT(*) function to count the number of items in each category. You're also giving this count the name "NumberOfProducts."
• FROM Products: You’re looking at the “Products” table.
• GROUP BY Category: This is where the magic happens. You’re telling SQL to group the products by their categories. So, you’ll have a separate row for each category.

Example — Calculating Average Price by Supplier: Let’s calculate the average price of products supplied by each supplier.

-- Calculate the average price of products by each supplier
SELECT SupplierID, AVG(Price) as AveragePrice
FROM Products
GROUP BY SupplierID;

• SELECT SupplierID, AVG(Price) as AveragePrice: You’re selecting the “SupplierID” column and using the AVG(Price) function to calculate the average price of products for each supplier. You're giving this average the name "AveragePrice."
• FROM Products: You’re working with the “Products” table.
• GROUP BY SupplierID: Here, you’re telling SQL to group the data by supplier. So, you’ll get the average price for each supplier.

Example — Total Revenue by Order: Let’s find the total revenue for each order.

-- Calculate total revenue for each order
SELECT OrderID, SUM(Quantity * UnitPrice) as TotalRevenue
FROM OrderDetails
GROUP BY OrderID;

• SELECT OrderID, SUM(Quantity * UnitPrice) as TotalRevenue: You’re selecting the “OrderID” column and using the SUM(Quantity * UnitPrice) function to calculate the total revenue for each order. You're giving this total the name "TotalRevenue."
• FROM OrderDetails: You’re working with the “OrderDetails” table.
• GROUP BY OrderID: You’re telling SQL to group the data by order. So, you’ll see the total revenue for each order.

I invite you to explore the Next phase of our discussion in my Next article, which can be found at the following link:

https://medium.com/@noransaber685/unlocking-the-power-of-sql-a-comprehensive-guide-to-structured-query-language-and-database-5edb02084a12

Feel free to connect 🌹me on:

Twitter: https://twitter.com/Noransaber11

Linkedin: https://www.linkedin.com/in/noran-saber-abdelfattah-6198471ba/

Github: https://github.com/Noransaber

Sincerely, Noran🌹