/
SQL Statements (to use in the database)

SQL Statements (to use in the database)

Owned by Kerem Tazedal (Unlicensed)
Apr 21, 2025

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.