DBMS - 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 −

This is the Products Table −

And, the third one is the Orders Table –

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 −

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 –

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 −

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 −

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 –

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 −

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 −

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 −

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 −

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 –

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

Conclusion

In this chapter, we explained in detail how to query a database in SQL. Starting with the basics of retrieving data with SELECT, we moved on to filtering using the WHERE clause.

We understood how sorting works with the ORDER BY clause. Thereafter, we touched upon the process of grouping relevant data using the GROUP BY clause. We also highlighted functions like COUNT and AVG for aggerating the results. Finally, we covered some advanced topics like joins, aliases, and nested queries, all with practical examples.