Data Querying Techniques with SQL

SQL (Structured Query Language) is the standard language used to manage and query relational databases. It provides commands to insert, update, delete, and retrieve data. In this guide, we'll explore how to use the SELECT statement, which is essential for data retrieval and analysis.

1. Basic Queries with SELECT

The SELECT statement is the most fundamental SQL command used to retrieve data from a table.

SELECT * FROM Customers;

This query returns all rows and columns from the Customers table.

To select specific columns:

SELECT FirstName, LastName FROM Customers;

This query returns only the FirstName and LastName columns.

2. Filtering Data with WHERE

The WHERE clause is used to filter records based on specific conditions.

SELECT * FROM Customers WHERE City = 'Istanbul';

This query returns only the customers located in 'Istanbul'.

Logical operators can also be used:

SELECT * FROM Customers WHERE Age > 30 AND City = 'Ankara';

This query returns customers over the age of 30 who live in 'Ankara'.

3. Sorting Results with ORDER BY

The ORDER BY clause sorts the result set based on one or more columns.

SELECT * FROM Customers ORDER BY Age ASC;

This query sorts customers in ascending order by age.

To sort in descending order:

SELECT * FROM Customers ORDER BY Age DESC;

4. Grouping Data with GROUP BY and Filtering with HAVING

GROUP BY is used to group rows that have the same values into summary rows.

SELECT City, COUNT(*) AS CustomerCount FROM Customers GROUP BY City;

This query counts the number of customers in each city.

Use HAVING to filter groups:

SELECT City, COUNT(*) AS CustomerCount FROM Customers GROUP BY City HAVING COUNT(*) > 5;

This query returns only cities with more than 5 customers.

5. Limiting Results with LIMIT

LIMIT restricts the number of rows returned by a query.

SELECT * FROM Customers LIMIT 10;

This query returns the first 10 records.

In PostgreSQL, you can use OFFSET to skip rows:

SELECT * FROM Customers ORDER BY ID ASC LIMIT 10 OFFSET 20;

This query returns 10 records starting from the 21st row.

6. Joining Tables with JOIN

JOIN is used to combine rows from two or more tables based on a related column.

SELECT Customers.FirstName, Orders.OrderDate 
FROM Customers 
INNER JOIN Orders ON Customers.ID = Orders.CustomerID;

This query returns customer names and their respective order dates.

SELECT Customers.FirstName, Orders.OrderDate 
FROM Customers 
LEFT JOIN Orders ON Customers.ID = Orders.CustomerID;

This query returns all customers, including those without orders.

SQL querying is essential for accessing and analyzing data in relational databases. Commands like SELECT, WHERE, ORDER BY, GROUP BY, LIMIT, and JOIN are fundamental tools for efficient data retrieval and reporting.