Comprehensive guide to crafting powerful queries.
This section dives deep into the art and science of writing effective SQL queries. Whether you're fetching single records, joining multiple tables, or performing complex aggregations, mastering SQL querying is fundamental for data management and analysis.
The SELECT statement is the cornerstone of data retrieval in SQL. It allows you to specify which columns you want to retrieve from one or more tables.
Basic Syntax:
SELECT column1, column2, ...
FROM table_name;To select all columns, you can use the asterisk wildcard:
SELECT *
FROM Customers;The WHERE clause is used to filter records based on specified conditions. This is crucial for retrieving only the data that meets your criteria.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;Common operators used in WHERE clauses include:
= (Equal)<> or != (Not equal)> (Greater than)< (Less than)>= (Greater than or equal to)<= (Less than or equal to)BETWEEN (Between an inclusive range)LIKE (Pattern matching)IN (Matches any value in a list)Retrieve all information for customers located in 'London':
SELECT *
FROM Customers
WHERE City = 'London';JOIN clauses are used to combine rows from two or more tables based on a related column between them. This is essential when data is normalized across multiple tables.
INNER JOIN: Returns records that have matching values in both tables.LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table.Syntax for INNER JOIN:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;List customer names along with their order IDs:
SELECT C.CustomerName, O.OrderID
FROM Customers AS C
INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID;The ORDER BY clause is used to sort the result-set in ascending or descending order. By default, it sorts in ascending (ASC) order. You can specify DESC for descending order.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC|DESC, column2 ASC|DESC;The GROUP BY statement groups rows that have the same values in specified columns into summary rows. It's often used with aggregate functions like COUNT, MAX, MIN, SUM, and AVG.
Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
ORDER BY column_name;Count how many customers are in each city:
SELECT City, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY City
ORDER BY NumberOfCustomers DESC;A subquery is a query nested inside another SQL query. Subqueries can be used in the WHERE clause, FROM clause, or even in the SELECT clause.
Find customers whose CustomerID exists in the Orders table:
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);| Command | Description |
|---|---|
SELECT |
Retrieves data from a database. |
INSERT INTO |
Adds new data into a table. |
UPDATE |
Modifies existing data in a table. |
DELETE |
Removes data from a table. |
CREATE TABLE |
Creates a new table. |
ALTER TABLE |
Modifies an existing table. |
DROP TABLE |
Deletes a table. |
JOIN |
Combines rows from two or more tables. |
WHERE |
Filters records based on a condition. |
GROUP BY |
Groups rows with the same values. |
HAVING |
Filters groups based on a condition. |
ORDER BY |
Sorts the result set. |