SQL Statements (to use in the database)
SQL (Structured Query Language) is used to communicate with databases. It allows you to query, update, and manage data efficiently. Here are the most fundamental SQL commands to understand:
SELECT and FROM
SELECT: Used to choose specific columns from a table or multiple tables.
FROM: Indicates the source table or tables for the data retrieval.
Basic Example:
SELECT CustomerName
FROM Customers;
This retrieves the CustomerName
column from the Customers
table.
AND and OR for Conditional Filtering
AND: Ensures that all conditions in the
WHERE
clause must be true.OR: Allows any of the conditions to be true.
Example with AND and OR:
SELECT *
FROM Orders
WHERE OrderDate > '2023-01-01' AND Status = 'Shipped'
OR Status = 'Processing';
This query selects all columns from Orders
where the OrderDate
is after January 1, 2023, and the Status
is either 'Shipped' or 'Processing'.
LIKE for Pattern Matching
Scenario: You need to find all customers whose names start with the letter 'A'. This is useful when searching for records with partial matches, such as names, addresses, or any text-based column.
Example with LIKE:
SELECT CustomerName, ContactName
FROM Customers
WHERE CustomerName LIKE 'A%';
This retrieves the CustomerName
and ContactName
from the Customers
table where CustomerName
starts with 'A'.
UPDATE for Modifying Data
Scenario: You need to adjust prices for all products within a specific category. For example, all products in CategoryID
2 need to reflect a 10% price increase.
Example of UPDATE:
UPDATE Products
SET Price = Price * 1.10
WHERE CategoryID = 2;
This increases the Price
by 10% for all products in the Products
table that belong to CategoryID
2.
INSERT for Adding New Records
Scenario: You’ve recently onboarded a new customer and need to add their information to your Customers
table.
Example of INSERT:
INSERT INTO Customers (CustomerName, Country)
VALUES ('New Customer', 'Germany');
This adds a new record to the Customers
table with CustomerName
as 'New Customer' and Country
as 'Germany'.
DELETE for Removing Records
Scenario: You have an Orders
table that contains historical data, and you want to remove old records. For instance, you need to delete all orders placed before January 1, 2022, to clean up outdated data.
Example of DELETE:
DELETE FROM Orders
WHERE OrderDate < '2022-01-01';
This deletes records from the Orders
table where the OrderDate
is before January 1, 2022.
ORDER BY for Sorting Results
Scenario: You want to display a list of all customers, but you want the names to appear in alphabetical order.
Example of ORDER BY:
SELECT * FROM Customers
ORDER BY CustomerName ASC;
This query selects all columns from the Customers table and sorts the result set in ascending order based on the CustomerName column. The ASC keyword sorts the names from A to Z. You can also use DESC if you want to sort the names in reverse order, from Z to A.
Using IN to Filter Specific Values
Scenario: You want to find all customers who are based in specific countries, such as the USA, Canada, or the UK.
Example of IN:
SELECT * FROM Customers
WHERE Country IN ('USA', 'Canada', 'UK');
This query selects all columns from the Customers table but only includes rows where the Country column matches any value in the specified list. It’s more concise than using multiple OR conditions.
JOINS
A JOIN is used in SQL to combine rows from two or more tables based on a related column between them. This allows you to bring together data that is stored across different tables, making it possible to create more complex and meaningful queries.
Key Concepts:
Tables: Think of each table as a collection of data about a specific subject (e.g., Customers, Orders, Products).
Keys: A key is a column that uniquely identifies a record. Common examples are CustomerID, OrderID, etc. These are often used in joins to link tables together.
Primary Key: A unique identifier for each record in a table (e.g., CustomerID in the Customers table).
Foreign Key: A column that refers to the primary key in another table, creating a link between the tables (e.g., CustomerID in the Orders table).
How to Choose the Right Columns for JOIN:
Identify the Relationship: Understand how the tables relate. For example, each order belongs to one customer, so use CustomerID to join Orders with Customers.
Primary Key and Foreign Key Relationship: Typically, you’ll join a Primary Key from one table with a Foreign Key from another. This ensures you are correctly linking data that corresponds to each other.
Types of Joins:
While there are several types of joins, the most common one is the INNER JOIN, which only returns rows when there is a match in both tables.
INNER JOIN: Combining Data with Matches in Both Tables
Scenario: You have two tables, and want to find all orders along with the names of the customers who placed those orders;
Customers (information about customers)
Orders (details of each order placed)
What to Join On:
Customers.CustomerID (Primary Key)
Orders.CustomerID (Foreign Key)
These columns hold related data. Orders.CustomerID points to the CustomerID in Customers, meaning that each order is associated with a specific customer.
SQL Example:
SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
This query joins the Customers table with the Orders table using CustomerID as the link. It returns only the rows where there is a match, i.e., where a customer has placed an order. It outputs the customer's name along with the corresponding order IDs.
Joining Three Tables (INNER JOIN)
Scenario: Sometimes you may need to join more than two tables to get data. For instance, to get the order details along with customer and product information.
SQL Example:
SELECT Customers.CustomerName, Orders.OrderID, Products.ProductName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN Products ON Orders.ProductID = Products.ProductID;
This query helps retrieve information that spans across three tables. It is especially useful when you need a complete view of related entities, like which customers ordered which products.
JOIN with Aggregation (GROUP BY): Summarizing Data
Scenario: You want to find out how many orders each customer has placed.
SQL Example:
SELECT Customers.CustomerName, COUNT(Orders.OrderID) AS OrderCount
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerName;
This query joins Customers with Orders and then groups the results by each customer's name, counting the number of orders for each customer.
JOIN with Filtering (WHERE): Combining Data and Filtering Results
Scenario: You want to see only the orders placed by customers after January 1, 2023.
SQL Example:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate > '2023-01-01';
This query joins Customers with Orders based on CustomerID and then filters the results to include only those orders placed after the specified date.