Querying in SQL

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.