Author: saqibkhan

Triggers and schema modifications are quite useful in managing and automating database operations. Triggers are special programs that automatically run when certain events take place in a database, like inserting, updating, or deleting data. Schema modification, on the other hand, focuses on changing the structure of the database itself. Schema modifications are used for adding or removing tables, columns, or constraints. Read this chapter to learn how to use triggers for automation and how to modify database schemas.

Sample Tables and Data

In the examples of this chapter, we will use an e-commerce database. The associated tables and the data are given below −

Given below is the Customers table −

customer_id	first_name	last_name	email	phone
1	Anu	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

Here is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

The Orders table is as follows −

order_id	customer_id	product_id	quantity	order_date
1	1	101	1	2023-11-01 10:00:00
2	2	103	2	2023-11-02 12:30:00
3	3	102	1	2023-11-03 15:45:00

Triggers in SQL

Triggers are like “event listeners” in a database. They run automatically when specific actions take place in a table. The operations could be anything like INSERT, UPDATE, or DELETE.

Creating a Trigger

First of all, one must understand how to create a trigger. In the following example, when a new order is placed, the stock quantity for the ordered product should automatically decrease.

CREATETRIGGER UpdateStock
AFTERINSERTON Orders
FOR EACH ROWBEGINUPDATE Products
   SET stock_quantity = stock_quantity - NEW.quantity
   WHERE product_id = NEW.product_id;END;

This trigger automatically updates the stock quantity after processing new orders. It does the following −

• The AFTER INSERT trigger runs whenever a new row is added inside the Orders table.
• The NEW keyword refers to the newly inserted row.
• Finally, the trigger updates the Products table by reducing the stock for the ordered product.

Adding an Order

When an order is placed for a product, the Orders table gets updated −

INSERTINTO Orders (order_id, customer_id, product_id, quantity, order_date)VALUES(4,1,102,3,'2023-11-05 10:00:00');

The trigger updates the Products table and now it would be as follows −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	17
103	Office Chair	Furniture	150.00	5

Here, we can see the stock_quantity for Smartphone decreases from 20 to 17 after the order is placed.

Deleting a Trigger

If we no longer need a trigger, we can simply remove it. We can drop an existing trigger using the DROP TRIGGER statement.

DROPTRIGGER UpdateStock;

It removes the UpdateStock trigger from the database.

Schema Modification in SQL

Schema modification refers to altering the structure of the database, which can include adding or removing tables, columns, or constraints.

Adding a New Table

Let’s create an Employees table to track the employees in the system −

CREATETABLE Employees (
   employee_id INTPRIMARYKEYAUTO_INCREMENT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   position VARCHAR(50),
   salary DECIMAL(10,2),
   hire_date DATEDEFAULTCURRENT_DATE);

The Employees Table Schema looks as follows −

employee_id	first_name	last_name	position	salary	hire_date
(empty)

The new table is now ready for storing employee data.

Modifying an Existing Table

Sometimes we may want to update a table by adding or removing its columns. The following example demonstrates how you can add a column for loyalty points.

Example: Add a Column for Loyalty Points

To track customer loyalty points, let us see how to add a column to the Customers table −

ALTERTABLE Customers
ADD loyalty_points INTDEFAULT0;

The updated Customers table would be as follows −

customer_id	first_name	last_name	email	phone	loyalty_points
1	Anu	Joshi	[email protected]	123-456-7890	0
2	Bimal	Saha	[email protected]	234-567-8901	0
3	Chandan	Tudu	[email protected]	345-678-9012	0

Removing a Column

If a column is no longer needed, we can simply drop it. Use the following query to remove the loyalty_points column from the Customers table −

ALTERTABLE Customers
DROPCOLUMN loyalty_points;

The updated Customers table would be as follows −

customer_id	first_name	last_name	email	phone
1	Anu	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

Renaming a Table

To rename a table, we can use the RENAME TABLE statement. Use the following query to rename the Orders table to CustomerOrders −

RENAMETABLE Orders TO CustomerOrders;

The table Orders is now called CustomerOrders.

Dropping a Table

If a table is no longer needed, we can remove it entirely. You can use the following query to drop the Employees table −

DROPTABLE Employees;

It deletes the Employees table along with all its data.

When to Use Triggers and Schema Modifications?

We generally use triggers for automation, like updating related data or maintaining logs. One must avoid overusing triggers because they can make debugging harder.

Schema Modifications are used while adapting to new business requirements. One must be cautious when dropping columns or tables. As it may lead to data loss.

Views in SQL are like virtual tables that do not store data themselves but act as saved SQL queries that we can reuse. We can think of views as a snapshot of a query result. Views give us a simplified way to look at or interact with the data.

Views are quite handy in improving query readability. They can also be used for restricting access to sensitive data, or combining data from multiple tables into a single view. In this chapter, we will use a set of examples to demonstrate what views are, their types, and how to use them effectively in practical aspects.

What are SQL Views?

A view is a predefined SQL query stored in the database. It acts like a virtual table that updates automatically whenever the underlying data changes. We can use views to −

• Simplify complex queries
• Create customized perspectives for different users
• Restrict access to specific columns or rows

Sample Tables and Data

We will use the following set of tables in the examples of this chapter to demonstrate the practical aspects of using views in SQL −

Here is the Customers table −

customer_id	first_name	last_name	email	phone
1	Antara	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

This is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

And, the Orders table is as follows −

order_id	customer_id	product_id	quantity	order_date
1	1	101	1	2023-11-01 10:00:00
2	2	103	2	2023-11-02 12:30:00
3	3	102	1	2023-11-03 15:45:00
4	1	103	1	2023-11-04 14:00:00

Creating Views in SQL

To work with views we must see how we can make a view from the existing table. We can create a view using the CREATE VIEW statement. When a view is created, we can query it like a table.

Example: View for Customer Orders

Let us create a view to display customer names along with the products they ordered.

CREATEVIEW CustomerOrders ASSELECT c.first_name, c.last_name, p.product_name, o.quantity, o.order_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Products p ON o.product_id = p.product_id;

Use the following query to fetch all the records from the CustomerOrders view −

SELECT*FROM CustomerOrders;

It will fetch all the records from the view −

first_name	last_name	product_name	quantity	order_date
Antara	Joshi	Laptop	1	2023-11-01 10:00:00
Bimal	Saha	Office Chair	2	2023-11-02 12:30:00
Chandan	Tudu	Smartphone	1	2023-11-03 15:45:00
Antara	Joshi	Office Chair	1	2023-11-04 14:00:00

This view combines data from three tables, which makes it easier to fetch customer orders.

Updating the Data Using Views

Some views allow updates, but it depends on the complexity of the view and database constraints.

Example: Update through a Simple View

Let us make a view to display product stock levels.

CREATEVIEW ProductStock ASSELECT product_id, product_name, stock_quantity
FROM Products;

Use the following query to fetch the details from the ProductStock view −

SELECT*FROM ProductStock;

It will fetch the records from the ProductStock view −

product_id	product_name	stock_quantity
101	Laptop	10
102	Smartphone	20
103	Office Chair	5

Updating the Stock Quantity through the View

Let us now use the following query to update the ProductStock view −

UPDATE ProductStock
SET stock_quantity = stock_quantity -1WHERE product_id =101;

Now, query the ProductStock view again −

SELECT*FROM ProductStock;

It will fetch the updated data from the ProductStock view −

product_id	product_name	stock_quantity
101	Laptop	9
102	Smartphone	20
103	Office Chair	5

Here, we can see the update reflects in the underlying Products table as well.

Types of Views in SQL

SQL views can be categorized into two main types −

• Simple Views
• Complex Views

Let’s start with a simple example of Simple Views.

Simple Views

Simple views are based on a single table. Simple views allow data updates if no calculations or aggregate functions are used.

Example: Simple View for Customers

In this query, we are creating a simple view from the Customers table −

CREATEVIEW CustomerDetails ASSELECT first_name, last_name, email
FROM Customers;

Complex Views

Complex Views combine data from multiple tables or include calculations. They can also be aggregate functions or GROUP BY clauses. Complex Views generally do not allow updates.

Example: Complex View for Total Revenue

Let us make a view to calculate the total revenue for each product −

CREATEVIEW ProductRevenue ASSELECT p.product_name,SUM(o.quantity * p.price)AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUPBY p.product_name;

Use the following query to fetch the data from the ProductRevenue view −

SELECT*FROM ProductRevenue;

This complex view fetches the revenue generated by each product −

product_name	total_revenue
Laptop	1000.00
Smartphone	800.00
Office Chair	450.00

Dropping the Views

Like we delete tables, we can delete views as well. If we no longer need a view, we can delete it using the DROP VIEW statement.

Example: Drop a View

Use the following query to drop the ProductStock view −

DROPVIEW ProductStock;

It removes the ProductStock view but does not affect the underlying Products table.

Advantages and Disadvantages of Using Views

The following table highlights the advantages and disadvantages of using views in SQL queries −

Advantages	Disadvantages
Simplifies Complex Queries − We can save a complicated query as a view and reuse it easily.Restricts Data Access − Views can limit the columns or rows that certain users can see.Encourages Reusability − Once a view is created, it can be used in multiple queries.Improves Readability − Queries referencing views are generally easier to read and understand to work with.	Performance Overhead − Complex views can slow down performance, because the query runs every time we access the view.Limited Updates − Not all views allow data updates.Dependency Issues − Dropping or modifying the underlying tables can break views.

In SQL querying, we sometimes need to fetch data from more than one table, for which we need to join the tables together or apply subqueries. Subqueries and the Join operation help us retrieve related data by linking the tables or running nested queries within a larger query.

In this chapter, we will have an elaborate discussion on joins and subqueries with the help of clear examples and their resulting tables.

Sample Tables and Data

For the examples in this chapter, we will use the following three tables: Customers, Orders, and Products. We will use these three tables to demonstrate joins and subqueries.

Here is the Customers table −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

This is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

And, the Orders table is as follows −

order_id	customer_id	product_id	quantity	order_date
1	1	101	1	2023-11-01 10:00:00
2	2	103	2	2023-11-02 12:30:00
3	3	102	1	2023-11-03 15:45:00
4	1	103	1	2023-11-04 14:00:00

Joins in SQL

Joins combine rows from two or more tables based on a related column. In SQL we have option for several types of joins. We will see the most commonly used techniques.

The Inner Join

The Inner Join retrieves rows that have matching values in both tables. If some matching is not there the entire row will be neglected.

Example: Fetch Customer Orders

Use the following query to fetch all the customer orders −

SELECT c.first_name, c.last_name, p.product_name, o.quantity, o.order_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Products p ON o.product_id = p.product_id;

This query combines the Orders, Customers, and Products tables to display complete order details −

first_name	last_name	product_name	quantity	order_date
Amrita	Joshi	Laptop	1	2023-11-01 10:00:00
Bimal	Saha	Office Chair	2	2023-11-02 12:30:00
Chandan	Tudu	Smartphone	1	2023-11-03 15:45:00
Amrita	Joshi	Office Chair	1	2023-11-04 14:00:00

The Left Join

The Left Join retrieves all the rows from the left table and the matching rows from the right table. If no match exists, NULL values are returned for the right table’s columns.

Example: Customers with or without Orders

Use the following query to fetch the customer details with or without orders −

SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
LEFTJOIN Orders o ON c.customer_id = o.customer_id;

It fetches all customers, including those with no orders (if there were any).

first_name	last_name	order_id
Amrita	Joshi	1
Amrita	Joshi	4
Bimal	Saha	2
Chandan	Tudu	3

The Right Join

The Right Join retrieves all the rows from the right table and the matching rows from the left table.

Example: Products with or without Orders

Use the following query to fetch the products with or without orders −

SELECT p.product_name, o.order_id
FROM Products p
RIGHTJOIN Orders o ON p.product_id = o.product_id;

The query fetches all the rows from the Orders table. When it is a product that does not match, it will be NULL. All products are matched in this case.

product_name	order_id
Laptop	1
Office Chair	2
Smartphone	3
Office Chair	4

The Full Outer Join

Combining both the Left and Right join makes the new join a Full Outer Join. It will fetch all the rows from both the tables.

Subqueries in SQL

Subqueries are nested queries inside another query. They can return single values or entire tables. Subqueries are useful for solving complex problems step-by-step.

Subqueries in WHERE Clause

Subqueries are mostly used in the WHERE clause to filter data based on the result of another query.

Example: Find Customers Who Ordered the Most Expensive Product

Here, we have three queries all together. The innermost query finds the product_id having the maximum number of products. Then, this product_id is matched with the Orders table in the second inner query, from where it gets the customer_id. Based on the customer_id, it fetches the name of the customer.

SELECT first_name, last_name
FROM Customers
WHERE customer_id IN(SELECT customer_id
   FROM Orders
   WHERE product_id =(SELECT product_id
  FROM Products
  WHERE price =(SELECTMAX(price)FROM Products)));</pre>


This query identifies the customers who ordered the most expensive product (Laptop).



first_name
last_name
Amrita
Joshi



Subqueries in SELECT Clause



Subqueries can also be used in the SELECT clause to include calculated data in the output.



Example: Total Quantity Ordered by Each Product



Use the following query to get the details of the total quantity ordered by each product −



SELECT product_name,(SELECTSUM(quantity)FROM Orders
   WHERE Orders.product_id = Products.product_id)AS total_quantity_ordered
FROM Products;



This query calculates the total quantity ordered for each product using a subquery. It is an advanced version of GROUP BY querying.



product_name
total_quantity_ordered
Laptop
1
Smartphone
1
Office Chair
3



Subqueries in FROM Clause



Subqueries in the FROM clause are called derived tables and are used to create temporary tables.



Example: Find Revenue per Product



Use the following query to get the revenue details per product −



SELECT product_name, revenue
FROM(SELECT p.product_name,SUM(o.quantity * p.price)AS revenue
   FROM Orders o
   JOIN Products p ON o.product_id = p.product_id
   GROUPBY p.product_name
)AS ProductRevenue;



The subquery calculates revenue per product, and the main query fetches it for display.



product_name
revenu
Laptop
1000.00
Smartphone
800.00
Office Chair
450.00



When to Use Joins vs. Subqueries



Both the approaches, joins and subqueries, are equally useful and often interchangeable. However, the joining operation is avoided sometimes due to its resulting large intermediate table size. In such cases, using an inner query is much more efficient. But, choosing the right option depends on the situation.




Use Joins when you need to combine rows from multiple tables directly.



Use Subqueries when you need to break down a problem into steps or filter the data based on calculated values.

In SQL querying sometimes we use special functions called aggregate functions that let us perform calculations on our data to find summaries, totals, averages, and other meaningful insights. If we have ever wanted to know the total sales for a month, the average rating of a product, or the number of customers who placed an order, then we would have to use the aggregate functions. Read this chapter to learn how to use aggregate functions in SQL.

Sample Tables and Data

We will take two sample tables and a set of examples to show how aggregate functions work. Here we are using the following schema and data from an e-commerce setup. The two tables we are going to use are Orders and Products.

Here is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5
104	Desk	Furniture	250.00	8

And, the Orders table is as follows −

order_id	product_id	quantity	order_date
1	101	2	2023-11-01 10:00:00
2	103	1	2023-11-02 12:30:00
3	102	3	2023-11-03 15:45:00
4	103	2	2023-11-04 17:20:00
5	101	1	2023-11-05 14:00:00

Aggregate Functions in SQL

Aggregate functions in SQL include the following functions −

• The COUNT function
• The SUM function
• The AVG function
• The MIN and MAX Functions

In the following sections of this chapter, we will have examples to demonstrate how you can apply these aggregate functions in SQL.

The COUNT Function: Counting the Rows

The COUNT function is used to find the number of rows in a table or the number of records that match a condition. It is like counting the items in a shopping cart.

Example: Total Number of Orders

In this query, we have use the aggregate function COUNT to get the total number of orders from the Orders table −

SELECTCOUNT(*)AS total_orders
FROM Orders;

It will fetch the following output −

total_orders
5

Here, we can see it is simply counting all the rows in the Orders table. The result tells us there are five orders in the Orders table.

Example: Count the Products in Each Category

Use the following query to count the products in each category −

SELECT category,COUNT(*)AS product_count
FROM Products
GROUPBY category;

It will fetch the following data from the Products table −

category	product_count
Electronics	2
Furniture	2

Here, this query is grouping the products by category and counting how many products are there in each category.

The SUM Function: Adding the Values

The SUM function calculates the total of a numeric column. It is quite useful in finding totals, like sales or stock quantities.

Example: Total Quantity of Products Ordered

Use the following query to get the total quantity of products ordered −

SELECTSUM(quantity)AS total_quantity_ordered
FROM Orders;

It will fetch the following data from the Orders table −

total_quantity_ordered
9

The SUM function adds up the quantity column in the Orders table. It gives us the total number of products ordered across all orders.

Example: Total Revenue Generated

To calculate the total revenue generated, we need to join the Orders and Products tables and multiply the quantity by price for each product.

SELECTSUM(o.quantity * p.price)AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id;

Here, we will get the following output −

total_revenue
4900.00

The above query simply multiplies the quantity of each order by the price of the corresponding product. Then, it adds up the results to calculate the total revenue.

The AVG Function: Calculating Averages

The AVG function calculates the average value of a numeric column. It’s useful for finding the trends or understanding typical values.

Example: Average Price of Products

You can use the following query to get the average price of products in the Products table −

SELECTAVG(price)AS average_price
FROM Products;

It will fetch the following data −

average_price
550.00

The AVG function gives the average price of all the products in the Products table.

Example: Average Quantity Ordered per Order

Use the AVG function as shown in the following query to get the average quantity ordered per order −

SELECTAVG(quantity)AS average_quantity_per_order
FROM Orders;

It will produce the following output −

average_quantity_per_order
1.80

In this query, we used the AVG function to calculate the average number of items ordered in each order.

The MIN and MAX Functions: Finding the Minimum and Maximum Values

The MIN and MAX functions are used to find the smallest and largest values in a column.

Example: Cheapest and Most Expensive Products

Use the following query to find the cheapest and the most expensive products in the Products table −

SELECTMIN(price)AS cheapest_product,MAX(price)AS most_expensive_product
FROM Products;

It will fetch the following data −

cheapest_product	most_expensive_product
150.00	1000.00

Here, we can see that the MIN function finds the lowest price and the MAX function finds the highest price.

Example: Earliest and Latest Order Dates

Here is another example of how you can use the MIN and MAX functions to get the earliest and latest order dates −

SELECTMIN(order_date)AS first_order_date,MAX(order_date)AS last_order_date
FROM Orders;

It will fetch the following data from the Orders table −

first_order_date	last_order_date
2023-11-01 10:00:00	2023-11-05 14:00:00

Here, this query shows the earliest and most recent dates when orders were placed.

The GROUP BY Clause: Aggregating Data by Groups

The GROUP BY clause works with the aggregate functions to organize data into groups. Think of it as sorting the data into buckets before performing calculations.

Example: Total Quantity Ordered per Product

You can use the following query to find the total quantity ordered per product −

SELECT p.product_name,SUM(o.quantity)AS total_quantity
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUPBY p.product_name;

It will produce the following output −

product_name	total_quantity
Laptop	3
Smartphone	3
Office Chair	3

This query groups the orders by product and then calculates the total quantity ordered for each.

Example: Revenue per Category

Use the following query to calculate the revenue per each product category −

SELECT p.category,SUM(o.quantity * p.price)AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUPBY p.category;

It will give you the following data as the output −

category	total_revenue
Electronics	4400.00
Furniture	500.00

Here, we are grouping the products by category and then calculating the total revenue for each.

Importance of Aggregate Functions

Aggregate functions help us perform data analysis. Instead of going through each of the records manually, we can use aggregate functions to quickly find totals, averages, and other key metrics with just a few lines of SQL. Aggregate functions are quite useful in creating reports or dashboards that summarize large datasets.

To work with a relational database, one must understand how to create a database with SQL and then learn how to insert, read, update, and delete the data, because these are the basic operations for transactions. CRUD stands for Create, Read, Update, and Delete. The four fundamental operations for managing data in a database. If databases were like a library, the CRUD operations would be the ways we can add new books, search for a specific title, edit details of an existing book, or remove one from the shelf.

Rea this chapter to get a good understanding of all the four CRUD operations. We will take a sample table and a set of examples to explain the CRUD operations in detail.

Sample Tables and Data

To understand the CRUD operations, we must take examples. We will use the following sample table throughout this chapter −

Here is the Customers table −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

And, this is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

With these tables in place, let us move ahead and explore the CRUD operations!

The Create Operation (Inserting Data into Tables)

Creating data that involves adding new rows to a table using the INSERT statement. This is like putting new files into a cabinet or adding new contacts to our phone contacts.

Example: Adding a New Customer

Use the following SQL command to insert a new customer row in the existing Customers table −

INSERTINTO Customers (customer_id, first_name, last_name, email, phone)VALUES(4,'Deb','Mallick','[email protected]','456-789-0123');

The Updated Customers Table would be as follows −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012
4	Deb	Mallick	[email protected]	456-789-0123

Example: Adding a New Product

Use the following command to insert a new product row into the Products table −

INSERTINTO Products (product_id, product_name, category, price, stock_quantity)VALUES(104,'Desk','Furniture',250.00,8);

The Updated Products Table would be as follows −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5
104	Desk	Furniture	250.00	8

The Read Operation (Fetching Data from Tables)

After creating we must read the data from the database. For that the ‘R’ in CRUD comes. This is done using the SELECT statement. It is like opening a book and searching through it to find the information we need.

Example: Fetch All Customers

Fetch all the data from the Customers table −

SELECT*FROM Customers;

It will get you the following output −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012
4	Deb	Mallick	[email protected]	456-789-0123

Since we inserted a new record (Deb) in the Customers table, it is present there in the current table.

Example: Fetch Products with Stock Below 10

Now, let’s use the WHERE clause to filter the data and fetch something specific −

SELECT product_name, stock_quantity
FROM Products
WHERE stock_quantity <10;

This query will fetch all the products with their stock quantity less than 10 −

product_name	stock_quantity
Office Chair	5

The Update Operation (Modifying the Existing Data)

Updating the data means changing the values in existing rows, for which we use the UPDATE command. Think of it as editing a contact’s phone number on our phone.

Example: Updating a Customer’s Email

You can use the following command to update a customer’s email in the existing records −

UPDATE Customers
SET email ='[email protected]'WHERE customer_id =1;

After executing the above query, the Updated Customers Table would look as follows −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012
4	Deb	Mallick	[email protected]	456-789-0123

Example: Restocking a Product

Use the following query to update the Products table after restocking a product −

UPDATE Products
SET stock_quantity = stock_quantity +5WHERE product_id =103;

Now, the Updated Products Table would reflect the updated stock quantities −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	10
104	Desk	Furniture	250.00	8

The stock of “Office Chair” has now been updated from 5 to 10.

The Delete Operation (Removing Data from Tables)

Deleting the data means removing existing rows from a table, which is done using the DELETE statement. Consider erasing a contact from our phone. We are deleting the record but not the whole table.

Example: Deleting a Customer

The following query shows how you can use the DELETE command to remove a customer’s record from the Customers table −

DELETEFROM Customers
WHERE customer_id =4;

With the above query, the Updated Customers Table would look as follows −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

Notice that Deb’s record has been removed.

Example: Removing a Product from Inventory

Use the following query to remove a product from the inventory −

DELETEFROM Products
WHERE product_id =104;

The Updated Products Table will now look as follows −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	10

The “Desk” product is no longer in the inventory.

Why CRUD Operations Are Important

CRUD operations are the most basic operations for any database operator; they allow us to:

• Add new information (like customers or products)
• Retrieve specific details when needed
• Keep the data up to date
• Remove outdated or incorrect data

Without CRUD operations, we cannot update a database, in which case the data will be historical and of no use in real-time applications.

We use SQL for querying a relational database. SQL is a command-based language to operate on tables and databases, which is why querying is at the heart of using SQL. SQL is the medium by which we ask a database questions and get the answers we need. Whether you are retrieving customer orders, checking stock levels, or just counting the number of employees, querying makes it all happen.

Read this chapter to learn the basics of querying in SQL. We will start with some sample tables with data and then understand how to write queries with their results.

Sample Tables and Data

To make things clear, we will use three tables: Customers, Products, and Orders. Given below are the tables with some initial data −

Here’s the Customers Table −

customer_id	first_name	last_name	email	phone
1	Alice	Johnson	[email protected]	123-456-7890
2	Bob	Smith	[email protected]	234-567-8901
3	Carol	Taylor	[email protected]	345-678-9012

This is the Products Table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

And, the third one is the Orders Table –

order_id	customer_id	product_id	quantity	order_date
1	1	101	1	2023-11-01 10:00:00
2	2	103	2	2023-11-02 12:30:00
3	3	102	1	2023-11-03 15:45:00

With this setup, let us start writing some SQL queries. After each query, we will see the results and explain what is happening.

Retrieving the Data with SELECT Command

The SELECT statement is used for querying. It is used to fetch data from one or more tables.

Example: Get All Customer Data

Use the following command −

SELECT * FROM Customers;

It will fetch the following rows −

customer_id	first_name	last_name	email	phone
1	Alice	Johnson	[email protected]	123-456-7890
2	Bob	Smith	[email protected]	234-567-8901
3	Carol	Taylor	[email protected]	345-678-9012

It fetches everything in the Customers table. The “*” symbol means “all columns”.

Example: Fetch Specific Columns

To fetch a specific set of columns, use the following command −

SELECT first_name, email FROM Customers;

It will fetch the following rows –

first_name	email
Alice	[email protected]
Bob	[email protected]
Carol	[email protected]

Here, we are only fetching the first_name and the email. This command is useful when we do not need all the data.

Filtering the Data with WHERE Clause

The WHERE clause lets us filter rows based on conditions.

Example: Find Customers with Last Name ‘Smith’

Here, we have used the WHERE clause to filter the data −

SELECT * FROM Customers
WHERE last_name ='Smith';

It will fetch the following rows −

customer_id	first_name	last_name	email	phone
2	Bob	Smith	[email protected]	234-567-8901

The WHERE clause filters only those rows where the last_name is ‘Smith.’

Example: Products below a Certain Price

Here is another example of how the WHERE clause is used −

SELECT product_name, price
FROM Products
WHERE price <900;

It will fetch the following rows −

product_name	price
Office Chair	150.00
Smartphone	800.00

Here, we only get a list of those products that are priced below 900.

Sorting the Data with ORDER BY Clause

The ORDER BY clause helps us sort the results.

Example: Sort the Products by Price (Low to High)

Use the following query to sort the products by their price –

SELECT product_name, price
FROM Products
ORDER BY price ASC;

It will fetch the following rows –

product_name	price
Office Chair	150.00
Smartphone	800.00
Laptop	1000.00

The ASC keyword sorts in ascending order. To reverse, use DESC.

Aggregating the Data with Functions

SQL has built-in functions like COUNT, SUM, and AVG for analyzing data.

Example: Count the Total Products

Use the following command to get a count of the total number of products −

SELECT COUNT(*) AS total_products
FROM Products;

It will fetch the following rows −

total_products
3

This query counts the total number of rows in the Products table.

Example: Find the Average Product Price

Use the following query to get the average product price −

SELECT AVG(price) AS average_price
FROM Products;

It will fetch the following rows −

average_price
650.00

The AVG function calculates the average price of all products.

Joining the Tables

Joins are used to combine data from two or more tables based on a related column.

Example: Find Customer Orders

SELECT Customers.first_name, Customers.last_name, Products.product_name, Orders.quantity
FROM Orders
JOIN Customers ON Orders.customer_id = Customers.customer_id
JOIN Products ON Orders.product_id = Products.product_id;

It will fetch the following rows −

first_name	last_name	product_name	quantity
Alice	Johnson	Laptop	1
Bob	Smith	Office Chair	2
Carol	Taylor	Smartphone	1

This query combines the Orders, Customers, and Products tables. It shows which customer ordered what.

Using Aliases for Simplicity

Aliases let us give the temporary names to tables or columns for easier reading.

Example: Simplify a Join

SELECT c.first_name, c.last_name, p.product_name, o.quantity
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Products p ON o.product_id = p.product_id;

It gives the same output as Example 8 but it uses short aliases (o, c, p) for tables.

Nested Queries

Subqueries, or queries within queries, are handy for more complex tasks. Let’s understand how with the help of the following example −

Example: Find Customers Who Ordered the Most Expensive Product

Here’s an example of a set of nested queries to find the customers who ordered the most expensive product −

SELECT first_name, last_name
FROM Customers
WHERE customer_id =(
	SELECT customer_id
	FROM Orders
	WHERE product_id =(
		SELECT product_id
		FROM Products
		WHERE price =(SELECT MAX(price) FROM Products)));

It will fetch the following rows −

first_name	last_name
Alice	Johnson

Here, we are finding the customer who ordered the product with the highest price.

Grouping the Data with GROUP BY Clause

The GROUP BY clause organizes the rows into groups based on one or more columns.

Example: Total Quantity Ordered Per Product

Here is an example that demonstrates how you can use the GROUP BY clause −

SELECT product_id, SUM(quantity) AS total_quantity
FROM Orders
GROUP BY product_id;

It will fetch the following rows –

product_id	total_quantity
101	1
102	1
103	2

It groups the orders by product and calculates the total quantity for each.

Developers and analysts use the structured query language (SQL) to interact with relational databases. SQL is made up of different types of sub-languages that let us perform different tasks. Think of it like a toolkit; each tool is specially used for a particular job. Read this chapter to get a clear understanding of the types of SQL languages and how they are used.

The Four Types of SQL Languages

SQL can be divided into four main types −

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

These are used for distinct purpose. Let us see them one by one.

Data Definition Language (DDL)

DDL commands are used for defining and modifying the structure of a database. They help in setting up the foundation of the database. DDL commands are is used for creating tables, adding or deleing columns, and changing table properties.

Common Commands in DDL

Here’s a list of the common commands used in DDL −

• CREATE − To make new tables, databases, or other structures
• ALTER − To modify an existing structure, like adding a column
• DROP − To delete tables or databases
• TRUNCATE − To quickly empty a table without removing its structure

Example: Setting Up a Table

Let’s understand the steps by setting up a table −

CREATE TABLE Customers (
   customer_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100) UNIQUE
);

Here, we are creating a table called Customers. It has columns for customer ID, first name, last name, and email. Observe closely how we define the data types: INT for numbers and VARCHAR for text.

Modifying an Existing Table

Sometimes we need to change an existing table. For example, if you need to add a phone number column in the Customers table, then you can use the ALTER command −

ALTER TABLE Customers
ADD phone VARCHAR(15);

Data Manipulation Language (DML)

DML commands are used for querying the tables and fetching relevant data. When the tables are set up, we actually work with the data inside them. It is all about adding, updating, deleting, and fetching the stored data.

Following are the common commands in DML −

• INSERT − To add new data
• UPDATE − To change existing data
• DELETE − To remove data
• SELECT − To retrieve data

Example: Adding Data

Let’s add a new row in our existing Customers table −

INSERT INTO Customers (customer_id, first_name, last_name, email, phone)
VALUES (1, 'Alice', 'Johnson', '[email protected]', '123-456-7890');

It adds a new row for Alice.

Fetching Data

To know what is inside the Customers table, we can use the SELECT command −

SELECT first_name, last_name, email
FROM Customers;

It fetches the first name, last name, and email of all customers.

Updating the Records

If Alice changes her phone number, we can update it in the table −

UPDATE Customers
SET phone ='987-654-3210'
WHERE customer_id =1;

Deleting the Records

Let us say Alice asks to remove her data entirely, then you would use the following command −

DELETE FROM Customers
WHERE customer_id =1;

Data Control Language (DCL)

DCL commands are all about managing the access. It is like setting up locks and keys to make sure only authorized people can access or change the precious data.

Following are the common commands in DCL −

• GRANT − To give permissions
• REVOKE − To take permissions away

Example: Granting Access

Suppose you want to give a new employee permission to view the Customers data, then you would use the following command −

GRANT SELECT ON Customers TO new_employee;

Revoking Access

If an employee leaves, then you can take back their access using the following command −

REVOKE SELECT ON Customers FROM new_employee;

DCL ensures the database is secure and only the authorized people have the right access.

Transaction Control Language (TCL)

Transactions are like bundles of database actions that should be treated as a single unit. TCL commands ensure that these actions are completed fully or not at all. TCL commands help keep the database consistent.

Following are the common commands in TCL –

• COMMIT − To save the changes permanently
• ROLLBACK − To undo the changes
• SAVEPOINT − To set a checkpoint in a transaction

Example: A Transaction in Action

Imagine we are processing an order where stock quantities need to be updated. Now, an order record needs to be created. These actions must be carried out together −

BEGIN TRANSACTION;

UPDATE Products
SET stock_quantity = stock_quantity - 1
WHERE product_id =101;

INSERT INTO Orders (order_id, customer_id, order_date, status, total_amount)
VALUES (1001, 1, NOW(), 'Pending', 99.99);

COMMIT;

If something goes wrong during the transaction, we can roll it back −

ROLLBACK;

How Do Different Types of SQL Commands Work Together?

All the four types SQL sub-languages (DDL, DML, DCL, and TCL) work in unison to provide seamless support and service to the end-users.

• We can use DDL to set up the database structure.
• DML helps us to populate and manage the data.
• DCL secures the data by controlling who can access it.
• TCL ensures the database stays consistent during critical operations.

Let us see at a practical scenario using an e-commerce example −

• We create a Products table using DDL.
• We insert product details using DML.
• We grant the team permission to view and update the Products table using DCL.
• During a big sale, we process orders and during such events, TCL ensures data consistency.

You can mix and match these different types of SQL commands to handle a variety of database tasks. Whether you are building a system from scratch, have some troubleshooting issues, or need to optimize the performance of an existing database, you will invariably need to use these SQL commands. For instance, without DDL, we cannot even have a database structure. Without DML, the tables would just sit there. DCL ensures the data is not tampered with, while TCL protects it from corruption during complex operations.