Module 2

Introduction to SQL

SQL, or Structured Query Language, is a standard programming language specifically designed

for managing and manipulating relational databases. It is widely used for querying, updating,
and managing data stored in relational database management systems (RDBMS). Here’s a
detailed introduction to SQL, covering its key concepts, components, and examples.

Key Concepts

1. Relational Database Management System (RDBMS):

o SQL is used to interact with RDBMS, which stores data in tables. Examples of
RDBMS include MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

o A table in an RDBMS is a collection of related data entries consisting of rows and

columns.

2. SQL Syntax:

o SQL syntax is the set of rules that defines the structure and format of SQL
statements.

o Basic SQL statements include SELECT, INSERT, UPDATE, DELETE, CREATE,

and DROP.

Basic SELECT Queries in SQL

The SELECT statement is one of the most fundamental and widely used commands in SQL. It is
used to retrieve data from one or more tables in a database. Here’s a detailed explanation of
basic SELECT queries with examples:

Basic Syntax

The basic syntax of a SELECT statement is:

EX.

SELECT column1, column2, ...

FROM table_name;

• SELECT: Specifies the columns to retrieve.

• FROM: Specifies the table from which to retrieve the data.

Examples

1. Selecting All Columns

To retrieve all columns from a table, you can use the * wildcard:

EX.

SELECT * FROM Employees;

This query retrieves all columns and rows from the Employees table.
2. Selecting Specific Columns

To retrieve specific columns, list the column names after the SELECT keyword:

EX.

SELECT FirstName, LastName FROM Employees;

This query retrieves only the FirstName and LastName columns from the Employees table.

3. Using the WHERE Clause

The WHERE clause is used to filter records based on specified conditions:

EX.

SELECT FirstName, LastName FROM Employees WHERE Department = 'Sales';

This query retrieves the first and last names of employees who work in the Sales department.

4. Sorting Results with ORDER BY

The ORDER BY clause is used to sort the result set by one or more columns:

EX.

SELECT FirstName, LastName FROM Employees ORDER BY LastName ASC;

This query retrieves the first and last names of employees, sorted by last name in ascending
order.

5. Using Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value:

EX.

SELECT COUNT(*) AS TotalEmployees FROM Employees;

This query counts the total number of employees in the Employees table.

6. Grouping Results with GROUP BY

The GROUP BY clause groups rows that have the same values in specified columns into
summary rows:

EX.

SELECT Department, COUNT(*) AS NumberOfEmployees FROM Employees GROUP BY

Department;

This query retrieves the number of employees in each department.

7. Filtering Groups with HAVING

The HAVING clause is used to filter groups based on specified conditions:

EX.
SELECT Department, COUNT(*) AS NumberOfEmployees FROM Employees GROUP BY
Department HAVING COUNT(*) > 10;

This query retrieves departments with more than 10 employees.

8. Using Aliases

Aliases are used to give a table or a column a temporary name:

EX.

SELECT FirstName AS FName, LastName AS LName FROM Employees;

This query retrieves the first and last names of employees, with the columns renamed
to FName and LName.

9. Using DISTINCT to Remove Duplicates

The DISTINCT keyword is used to return only distinct (different) values:

EX.

SELECT DISTINCT Department FROM Employees;

ORDER BY Clause in SQL

The ORDER BY clause in SQL is used to sort the result set of a query by one or more columns. By
default, the ORDER BY clause sorts the data in ascending order. However, you can specify
descending order using the DESC keyword.

Syntax

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

1. Sorting by a Single Column

To sort the result set by a single column in ascending order (default):

Ex.

SELECT * FROM Employees

ORDER BY LastName;

This query retrieves all columns from the Employees table and sorts the rows by
the LastName column in ascending order.

To sort the result set by a single column in descending order:

Ex.
SELECT * FROM Employees

ORDER BY LastName DESC;

This query retrieves all columns from the Employees table and sorts the rows by
the LastName column in descending order.

2. Sorting by Multiple Columns

To sort the result set by multiple columns:

Ex.

SELECT * FROM Employees

ORDER BY Department, LastName;

This query retrieves all columns from the Employees table and sorts the rows first by
the Department column in ascending order, and then by the LastName column in ascending
order within each department.

To sort the result set by multiple columns with different sort orders:

Ex.

SELECT * FROM Employees

ORDER BY Department ASC, LastName DESC;

This query retrieves all columns from the Employees table and sorts the rows first by
the Department column in ascending order, and then by the LastName column in descending
order within each department.

Aggregate Functions in SQL

Aggregate functions in SQL are used to perform calculations on multiple rows of a table’s
column and return a single value. These functions are often used with the GROUP BY clause to
group rows that share a property so that an aggregate function can be applied to each group.
Here are some commonly used aggregate functions, along with examples:

Common Aggregate Functions

1. COUNT(): Counts the number of rows in a table or the number of non-NULL values in a
column.

Ex.

SELECT COUNT(*) AS TotalEmployees FROM Employees;

This query counts the total number of rows in the Employees table.
2. SUM(): Calculates the total sum of a numeric column.

Ex.

SELECT SUM(Salary) AS TotalSalary FROM Employees;

This query calculates the total sum of the Salary column in the Employees table.

3. AVG(): Calculates the average value of a numeric column.

EX.

SELECT AVG(Salary) AS AverageSalary FROM Employees;

This query calculates the average salary of employees.

4. MIN(): Finds the minimum value in a column.

EX.

SELECT MIN(Salary) AS MinimumSalary FROM Employees;

This query finds the minimum salary in the Employees table.

5. MAX(): Finds the maximum value in a column.

EX.

SELECT MAX(Salary) AS MaximumSalary FROM Employees;

This query finds the maximum salary in the Employees table.

Using Aggregate Functions with GROUP BY

The GROUP BY clause is used to group rows that have the same values in specified columns
into summary rows. Aggregate functions are then applied to each group.

Example:

SELECT Department, COUNT(*) AS NumberOfEmployees, AVG(Salary) AS AverageSalary

FROM Employees

GROUP BY Department;

This query groups the employees by department and calculates the number of employees and
the average salary for each department.

HAVING Clause in SQL

The HAVING clause in SQL is used to filter groups of rows based on a specified condition,
similar to how the WHERE clause filters individual rows. The HAVING clause is typically used in
conjunction with the GROUP BY clause to apply conditions to groups of rows after they have
been aggregated.

Syntax
SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1

HAVING condition;

• SELECT: Specifies the columns to retrieve.

• aggregate_function(column2): Applies an aggregate function

(e.g., COUNT, SUM, AVG, MIN, MAX) to the grouped data.

• FROM: Specifies the table from which to retrieve the data.

• GROUP BY: Groups the rows that have the same values in specified columns.

• HAVING: Filters the groups based on the specified condition.

Example

Consider a table named Sales with the following columns: SalesID, ProductID, Quantity,
and SaleDate.

Table: Sales

SalesID ProductID Quantity SaleDate

1 101 5 2024-01-01

2 102 3 2024-01-02

3 101 7 2024-01-03

4 103 2 2024-01-04

5 102 6 2024-01-05

Using HAVING with GROUP BY

To find products that have been sold in quantities greater than 10, you can use
the HAVING clause with the GROUP BY clause:

EX.

SELECT ProductID, SUM(Quantity) AS TotalQuantity

FROM Sales

GROUP BY ProductID

HAVING SUM(Quantity) > 10;

Explanation:
 • SELECT ProductID, SUM(Quantity) AS TotalQuantity: Selects the ProductID and the total
quantity sold for each product.

• FROM Sales: Specifies the Sales table.

• GROUP BY ProductID: Groups the rows by ProductID.

• HAVING SUM(Quantity) > 10: Filters the groups to include only those products where the
total quantity sold is greater than 10.

Result:

ProductID TotalQuantity

101 12

102 9

In this example, only ProductID 101 meets the condition specified in the HAVING clause.

Subqueries in SQL

A subquery, also known as a nested query or inner query, is a query embedded within another
SQL query. The outer query, which contains the subquery, is referred to as the main query or
outer query. Subqueries are used to perform operations that require data from multiple tables or
to perform calculations on a subset of data within a larger result set.

Syntax

SELECT column1, column2, ...

FROM table_name

WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);

1. Subquery with IN Operator

Find employees who work in departments located in a specific location:

Ex.

SELECT employee_id, first_name, last_name

FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id =

1700);

Explanation: The subquery retrieves department_id values from the departments table
where location_id is 1700. The outer query then retrieves employees
whose department_id matches any of these values.

2. Subquery with EXISTS Operator

Find departments that have at least one employee:

Ex.

SELECT department_id, department_name

FROM departments

WHERE EXISTS (SELECT 1 FROM employees WHERE employees.department_id =

departments.department_id);

Explanation: The subquery checks for the existence of employees in each department. The
outer query retrieves departments where the subquery returns at least one row.

Keys and Their Types in SQL

Keys are fundamental components of relational databases. They are used to identify and
establish relationships between records in different tables, ensuring data integrity and enabling
efficient querying and data manipulation. Here’s a detailed explanation of the different types of
keys in SQL:

1. Primary Key

• Definition: A primary key is a column or a set of columns in a table that uniquely

identifies each row in that table.

• Characteristics:

o Must contain unique values.

o Cannot contain NULL values.

o Each table can have only one primary key.

Ex.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

BirthDate DATE

);

In this example, EmployeeID is the primary key for the Employees table, ensuring each
employee has a unique identifier.

2. Foreign Key

• Definition: A foreign key is a column or a set of columns in one table that references the
primary key columns of another table.

• Characteristics:
 o Ensures referential integrity between tables.

o The values in the foreign key column must match values in the referenced
primary key column or be NULL.

Ex.

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

OrderDate DATE,

CustomerID INT,

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

Here, CustomerID in the Orders table is a foreign key that references CustomerID in
the Customers table.

3. Composite Key

• Definition: A composite key is a primary key composed of two or more columns.

• Characteristics:

o Used when a single column is not sufficient to uniquely identify a row.

Ex.

CREATE TABLE Enrollments (

StudentID INT,

CourseID INT,

EnrollmentDate DATE,

PRIMARY KEY (StudentID, CourseID)

);

Here, the combination of StudentID and CourseID forms the composite primary key for
the Enrollments table.

4. Candidate Key

• Definition: A candidate key is a column or a set of columns that can uniquely identify a
row in a table. It is a minimal super key.

• Characteristics:

o There can be multiple candidate keys in a table.

o One of the candidate keys is chosen as the primary key.

Ex.

CREATE TABLE Students (

StudentID INT,

Email VARCHAR(100),

PRIMARY KEY (StudentID),

UNIQUE (Email)

);

In this example, both StudentID and Email are candidate keys, but StudentID is chosen as the
primary key.

5. Surrogate Key

• Definition: A surrogate key is an artificial key that is used as a substitute for a natural
key.

• Characteristics:

o Typically a sequential number (e.g., auto-incremented).

o Has no business meaning.

Ex.

CREATE TABLE Products (

ProductID INT AUTO_INCREMENT PRIMARY KEY,

ProductName VARCHAR(100)

);

Here, ProductID is a surrogate key that uniquely identifies each product.

Constraints in SQL

Constraints in SQL are rules applied to columns in a table to ensure the accuracy, reliability, and
integrity of the data. They limit the type of data that can be inserted into a table and enforce
rules at the database level. Here are the main types of constraints in SQL:

Types of Constraints

1. NOT NULL Constraint

o Definition: Ensures that a column cannot have a NULL value.

Ex.

CREATE TABLE Employees (

EmployeeID INT NOT NULL,

 FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50) NOT NULL,

BirthDate DATE

);

In this example, the EmployeeID, FirstName, and LastName columns cannot contain NULL
values.

2. UNIQUE Constraint

o Definition: Ensures that all values in a column are unique.

Ex.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Email VARCHAR(100) UNIQUE

);

Here, the Email column must contain unique values, ensuring no two employees can have the
same email address.

3. PRIMARY KEY Constraint

o Definition: A combination of NOT NULL and UNIQUE. Uniquely identifies each row
in a table.

Ex.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50)

);

In this example, EmployeeID is the primary key, uniquely identifying each employee.

4. FOREIGN KEY Constraint

o Definition: Ensures that values in a column (or a set of columns) match values in
another table’s column(s), maintaining referential integrity.

Ex.

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

OrderDate DATE,
 CustomerID INT,

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

Here, CustomerID in the Orders table is a foreign key that references CustomerID in
the Customers table.

5. CHECK Constraint

o Definition: Ensures that all values in a column satisfy a specific condition.

Ex.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Salary DECIMAL(10, 2),

CHECK (Salary > 0)

);

This example ensures that the Salary column only contains positive values.

6. DEFAULT Constraint

o Definition: Sets a default value for a column if no value is specified.

Ex.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

HireDate DATE DEFAULT GETDATE()

);

In this example, if no hire date is specified, the HireDate column will default to the current date.

Applying Constraints

Constraints can be applied at the time of table creation using the CREATE TABLE statement or
added to an existing table using the ALTER TABLE statement.

Example of Adding Constraints to an Existing Table:

ALTER TABLE Employees

ADD CONSTRAINT chk_salary CHECK (Salary > 0);

This statement adds a CHECK constraint to the Employees table to ensure that
the Salary column contains only positive values.
Data Definition Commands in SQL

Data Definition Language (DDL) commands are used to define and manage database
structures, such as tables, indexes, and schemas. These commands are essential for creating,
modifying, and deleting database objects. Here are the main DDL commands, along with their
syntax and examples:

1. CREATE

The CREATE command is used to create new database objects, such as tables, indexes, views,
and schemas.

Syntax:

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

...

);

Example:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

BirthDate DATE,

Email VARCHAR(100) UNIQUE

);

This example creates a table named Employees with columns

for EmployeeID, FirstName, LastName, BirthDate, and Email.

2. ALTER

The ALTER command is used to modify the structure of an existing database object, such as
adding, deleting, or modifying columns in a table.

Syntax:

ALTER TABLE table_name

ADD column_name datatype;

Example:

ALTER TABLE Employees

ADD PhoneNumber VARCHAR(15);

This example adds a new column PhoneNumber to the Employees table.

3. DROP

The DROP command is used to delete an existing database object, such as a table, index, or
view.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Employees;

This example deletes the Employees table from the database.

4. TRUNCATE

The TRUNCATE command is used to remove all rows from a table, but the table structure
remains intact.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Employees;

This example removes all rows from the Employees table, but the table structure is preserved.

5. RENAME

The RENAME command is used to change the name of an existing database object.

Syntax:

RENAME TABLE old_table_name TO new_table_name;

Example:

RENAME TABLE Employees TO Staff;

This example renames the Employees table to Staff.

Views in SQL

A view in SQL is a virtual table that is based on the result set of an SQL query. Unlike a physical
table, a view does not store data itself but provides a way to look at data from one or more
tables. Views are used to simplify complex queries, enhance security by restricting access to
specific data, and present data in a specific format without altering the underlying tables.

Key Concepts

1. Virtual Table: A view behaves like a table but does not store data physically. It is a
stored query that can be treated as a table.

2. Read-Only or Updatable: Views can be read-only or updatable, depending on the SQL

implementation and the complexity of the view.

3. Security: Views can restrict access to specific columns or rows, providing a layer of
security.

Creating a View

The CREATE VIEW statement is used to create a view.

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

• CREATE VIEW view_name: Specifies the name of the view.

• AS SELECT column1, column2, ...: Defines the columns to include in the view.

• FROM table_name: Specifies the table from which to retrieve the data.

• WHERE condition: (Optional) Filters the rows to include in the view.

Example:

CREATE VIEW EmployeeNames AS

SELECT FirstName, LastName

FROM Employees;

This view, named EmployeeNames, includes the FirstName and LastName columns from
the Employees table.

Querying a View

Once a view is created, you can query it just like a regular table.

Example:

SELECT * FROM EmployeeNames;

This query retrieves all rows and columns from the EmployeeNames view.

Modifying a View
To modify an existing view, you can use the CREATE OR REPLACE VIEW statement.

Example:

CREATE OR REPLACE VIEW EmployeeNames AS

SELECT FirstName, LastName, Email

FROM Employees;

This statement modifies the EmployeeNames view to include the Email column.

Dropping a View

To delete a view, use the DROP VIEW statement.

Syntax:

DROP VIEW view_name;

Example:

DROP VIEW EmployeeNames;

This statement deletes the EmployeeNames view from the database.

Advantages of Using Views

1. Simplify Complex Queries: Views can encapsulate complex queries, making it easier
to reuse and maintain them.

2. Enhance Security: Views can restrict access to specific columns or rows, providing a
layer of security.

3. Data Abstraction: Views can present data in a specific format, hiding the complexity of
the underlying tables.

4. Consistency: Views can provide a consistent interface to data, even if the underlying
tables change.

Types of Views

1. Simple View: Based on a single table and does not contain functions or groups of data.

2. Complex View: Based on multiple tables and can contain functions, joins, and groups
of data.

Example of a Complex View:

CREATE VIEW EmployeeDepartment AS

SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName

FROM Employees e

JOIN Departments d ON e.DepartmentID = d.DepartmentID;

This view, named EmployeeDepartment, includes the EmployeeID, FirstName, LastName,

and DepartmentName columns by joining the Employees and Departments tables.
Triggers in SQL

A trigger in SQL is a special type of stored procedure that automatically executes in response to
certain events on a particular table or view. Triggers are used to enforce business rules,
maintain data integrity, and automate system tasks.

Key Concepts

1. Event-Driven: Triggers are activated by specific events such as INSERT, UPDATE,

or DELETE.

2. Automatic Execution: Once defined, triggers execute automatically when the specified
event occurs.

3. Associated with Tables: Triggers are always associated with a particular table or view.

Types of Triggers

1. BEFORE Triggers: Execute before the triggering event.

2. AFTER Triggers: Execute after the triggering event.

3. INSTEAD OF Triggers: Execute in place of the triggering event (commonly used with
views).

Syntax

CREATE TRIGGER trigger_name

[BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE]

ON table_name

[FOR EACH ROW]

BEGIN

-- trigger logic

END;

• trigger_name: The name of the trigger.

• BEFORE | AFTER | INSTEAD OF: Specifies when the trigger should execute.

• INSERT | UPDATE | DELETE: Specifies the event that activates the trigger.

• table_name: The table or view to which the trigger is associated.

• FOR EACH ROW: Specifies that the trigger should execute once for each row affected by
the event.

• BEGIN ... END: The block where the trigger logic is defined.

Examples

1. BEFORE INSERT Trigger

A trigger that ensures no employee under the age of 25 is inserted into the Employees table:

DELIMITER $$

CREATE TRIGGER CheckAge

BEFORE INSERT ON Employees

FOR EACH ROW

BEGIN

IF NEW.Age < 25 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ERROR: Age must be at least 25 years!';

END IF;

END $$

DELIMITER ;

• Explanation: This trigger checks the Age of the new row being inserted. If the age is less
than 25, it raises an error.

2. AFTER INSERT Trigger

A trigger that updates the total sales in the Products table after a new sale is recorded in
the Sales table:

DELIMITER $$

CREATE TRIGGER UpdateTotalSales

AFTER INSERT ON Sales

FOR EACH ROW

BEGIN

UPDATE Products

SET TotalSales = TotalSales + NEW.Quantity

WHERE ProductID = NEW.ProductID;

END $$

DELIMITER ;

• Explanation: This trigger updates the TotalSales column in the Products table whenever
a new sale is recorded.

3. BEFORE DELETE Trigger

A trigger that creates a backup of an employee record before it is deleted:

$$

CREATE TRIGGER BackupBeforeDelete

BEFORE DELETE ON Employees

FOR EACH ROW

BEGIN

INSERT INTO EmployeesBackup (EmployeeID, FirstName, LastName, Age, Department)

VALUES (OLD.EmployeeID, OLD.FirstName, OLD.LastName, OLD.Age, OLD.Department);

END $$

DELIMITER ;

• Explanation: This trigger inserts the old values of the row being deleted into
the EmployeesBackup table before the row is deleted from the Employees table.

Advantages of Using Triggers

1. Automatic Enforcement: Triggers automatically enforce business rules and data

integrity constraints.

2. Audit and Logging: Triggers can be used to log changes and maintain an audit trail.

3. Complex Validation: Triggers can perform complex validations that are not possible
with standard constraints.

Disadvantages of Using Triggers

1. Performance Overhead: Triggers can introduce performance overhead, especially if

they contain complex logic.

2. Debugging Difficulty: Debugging triggers can be challenging because they execute

automatically and may not provide immediate feedback.

3. Hidden Logic: Triggers can make the database logic less transparent, as the logic is not
visible in the application code.
Joins in MySQL

Joins in MySQL are used to combine rows from two or more tables based on a related column
between them. Joins are essential for querying data from multiple tables and establishing
relationships between different sets of information. Here are the main types of joins in MySQL,
along with examples:

Types of Joins

1. INNER JOIN

2. LEFT JOIN (LEFT OUTER JOIN)

3. RIGHT JOIN (RIGHT OUTER JOIN)

4. CROSS JOIN

1. INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

Syntax:

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Explanation: This query retrieves the OrderID, CustomerName, and OrderDate for orders where
there is a matching CustomerID in both the Orders and Customers tables.

2. LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records
from the right table (table2). The result is NULL from the right side if there is no match.
Syntax:

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.column = table2.column;

Example:

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Explanation: This query retrieves all customers and their orders. If a customer has no orders,
the OrderID will be NULL.

3. RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched
records from the left table (table1). The result is NULL from the left side if there is no match.

Syntax:

SELECT columns

FROM table1

RIGHT JOIN table2

ON table1.column = table2.column;

Example:

SELECT Orders.OrderID, Employees.EmployeeName

FROM Orders

RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID;

Explanation: This query retrieves all employees and their orders. If an employee has no orders,
the OrderID will be NULL.

4. CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of the two tables, meaning it returns all
possible combinations of rows from the two tables.

Syntax:

SELECT columns

FROM table1

CROSS JOIN table2;

Example:

SELECT Products.ProductName, Categories.CategoryName

FROM Products

CROSS JOIN Categories;

Explanation: This query retrieves all possible combinations of products and categories.

Combining Joins with Other Clauses

Joins can be combined with other SQL clauses such as WHERE, GROUP BY, and HAVING to
filter and group the result set.

Example with WHERE Clause:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

WHERE Orders.OrderDate > '2024-01-01';

Explanation: This query retrieves orders placed after January 1, 2024, along with the customer
names.

Example with GROUP BY and HAVING Clauses:

SELECT Customers.CustomerName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

GROUP BY Customers.CustomerName

HAVING COUNT(Orders.OrderID) > 5;

Explanation: This query retrieves customers who have placed more than five orders, along with
the count of their orders.