Relational Database Queries

Welcome to this tutorial on querying relational databases. This section will guide you through the fundamental concepts and practical techniques for retrieving data from relational database systems using SQL (Structured Query Language).

Understanding Relational Databases

Relational databases organize data into tables, where each table consists of rows (records) and columns (fields). Relationships between tables are established using primary and foreign keys, allowing for complex data structures and efficient data retrieval.

Introduction to SQL

SQL is the standard language for managing and manipulating relational databases. It allows you to perform operations such as querying data, inserting new records, updating existing records, and deleting data.

The SELECT Statement

The most fundamental SQL statement for retrieving data is the SELECT statement. It allows you to specify which columns you want to retrieve and from which table(s).

Basic SELECT Statement

To retrieve all columns from a table named Customers:

SELECT *
FROM Customers;

To retrieve specific columns (e.g., CustomerID and CompanyName) from the Customers table:

SELECT CustomerID, CompanyName
FROM Customers;

Filtering Data with WHERE Clause

The WHERE clause is used to filter records based on specified conditions. This allows you to retrieve only the data that meets your criteria.

Using the WHERE Clause

To retrieve customers from 'USA':

SELECT CompanyName, Country
FROM Customers
WHERE Country = 'USA';

To retrieve customers whose CustomerID is greater than 50:

SELECT CustomerID, CompanyName
FROM Customers
WHERE CustomerID > 50;

Sorting Data with ORDER BY Clause

The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order. If not specified, the default order is ascending.

Using the ORDER BY Clause

To retrieve all customers, sorted by CompanyName in ascending order:

SELECT CompanyName, Country
FROM Customers
ORDER BY CompanyName ASC;

To retrieve all products, sorted by Price in descending order:

SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

Joining Tables

In relational databases, data is often spread across multiple tables. The JOIN clause is used to combine rows from two or more tables based on a related column between them.

Using INNER JOIN

To retrieve order information along with customer names:

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

Aggregate Functions

SQL provides several aggregate functions to perform calculations on sets of rows, such as COUNT, SUM, AVG, MIN, and MAX.

Using COUNT and AVG

To count the total number of customers:

SELECT COUNT(*) AS TotalCustomers
FROM Customers;

To calculate the average price of all products:

SELECT AVG(Price) AS AveragePrice
FROM Products;

Grouping Data with GROUP BY Clause

The GROUP BY clause is used in conjunction with aggregate functions to group rows that have the same values in specified columns into summary rows. This is often used with HAVING to filter groups.

Using GROUP BY and HAVING

To count the number of customers in each country and show only countries with more than 5 customers:

SELECT Country, COUNT(*) AS NumberOfCustomers
FROM Customers
GROUP BY Country
HAVING COUNT(*) > 5;

Conclusion

This tutorial has covered the basics of querying relational databases. Mastering these fundamental SQL statements will provide you with a strong foundation for data retrieval and manipulation. Continue exploring advanced SQL concepts like subqueries, unions, and different types of joins to further enhance your database skills.