Notes

Here are the SQL commands to create the tables, insert records, and fulfill the requested queries.
1. Create Tables and Insert Data 🛠️
Create Tables
These commands define the structure for the DEPARTMENT and EMPLOYEE tables, including primary keys and the foreign key relationship.
-- Table 2: DEPARTMENT
CREATE TABLE DEPARTMENT (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL,
Location VARCHAR(30)
);

-- Table 1: EMPLOYEE
CREATE TABLE EMPLOYEE (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL,
DeptID INT,
Salary INT,
FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID)
);

Insert Records
These commands populate both tables with at least five sample records.
-- Insert records into DEPARTMENT
INSERT INTO DEPARTMENT (DeptID, DeptName, Location) VALUES
(10, 'IT', 'Delhi'),
(20, 'HR', 'Noida'),
(30, 'Sales', 'Mumbai'),
(40, 'Finance', 'Delhi'),
(50, 'Marketing', 'Bangalore');

-- Insert records into EMPLOYEE
INSERT INTO EMPLOYEE (EmpID, EmpName, DeptID, Salary) VALUES
(101, 'Alice Johnson', 10, 60000),
(102, 'Bob Williams', 20, 45000),
(103, 'Charlie Brown', 10, 75000),
(104, 'David Lee', 30, 50000),
(105, 'Eve Davis', 40, 90000),
(106, 'Frank Miller', 20, 48000); -- Added an extra record for better query results

2. SQL Queries 📋
Display Employees in 'IT' Department (Equi Join)
This query uses an Equi Join (an INNER JOIN with an equality condition) to link the tables based on DeptID and filters the result by the department name.
SELECT
E.EmpName,
E.Salary,
D.DeptName
FROM
EMPLOYEE E
INNER JOIN
DEPARTMENT D ON E.DeptID = D.DeptID
WHERE
D.DeptName = 'IT';

Display Employees Located in 'Delhi'
This query joins the tables and filters the results based on the Location column in the DEPARTMENT table.
SELECT
E.EmpName,
D.DeptName,
D.Location
FROM
EMPLOYEE E
INNER JOIN
DEPARTMENT D ON E.DeptID = D.DeptID
WHERE
D.Location = 'Delhi';

Total Number of Employees in Each Department (GROUP BY)
This query uses the aggregate function COUNT() and the GROUP BY clause to count employees for each unique DeptName.
SELECT
D.DeptName,
COUNT(E.EmpID) AS TotalEmployees
FROM
EMPLOYEE E
INNER JOIN
DEPARTMENT D ON E.DeptID = D.DeptID
GROUP BY
D.DeptName;

Display All Employees Sorted by Salary (Descending)
This query retrieves all columns from the EMPLOYEE table and orders the results from the highest salary to the lowest salary using the ORDER BY clause.
SELECT
EmpID,
EmpName,
DeptID,
Salary
FROM
EMPLOYEE
ORDER BY
Salary DESC;