1.Create Tables Using The script :
CREATE TABLE
Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
DepartmentID INT,
Salary DECIMAL(10,2),
ManagerID INT
);
CREATE TABLE
Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100),
DepartmentID INT
);
CREATE TABLE
EmployeeProjects (
EmployeeID INT,
ProjectID INT,
AssignedDate DATE,
PRIMARY KEY (EmployeeID, ProjectID),
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);
CREATE TABLE Salaries (
SalaryID INT PRIMARY KEY,
EmployeeID INT,
SalaryAmount DECIMAL(10,2),
EffectiveDate DATE,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
INSERT INTO
Employees (EmployeeID, Name, DepartmentID, Salary, ManagerID)
VALUES
(1, 'Alice', 101, 50000, NULL),
(2, 'Bob', 102, 60000, 1),
(3, 'Charlie', 101, 55000, 1),
(4, 'David', 103, 48000, 2),
(5, 'Emma', 102, 62000, 3);
GO
INSERT INTO
Departments (DepartmentID, DepartmentName)
VALUES
(101, 'HR'),
(102, 'IT'),
(103, 'Finance'),
(104, 'Marketing'),
(105, 'Operations');
GO
INSERT INTO Projects (ProjectID, ProjectName, DepartmentID)
VALUES
(201, 'Recruitment System', 101),
(202, 'AI Development', 102),
(203, 'Budget Analysis', 103),
(204, 'Ad Campaign', 104),
(205, 'Process Optimization', 105);
GO
INSERT INTO
EmployeeProjects (EmployeeID, ProjectID, AssignedDate)
VALUES
(1, 201, '2024-01-10'),
(2, 202, '2024-02-15'),
(3, 203, '2024-03-01'),
(4, 204, '2024-04-20'),
(5, 205, '2024-05-05');
GO
INSERT INTO Salaries (SalaryID, EmployeeID, SalaryAmount, EffectiveDate)
VALUES
(301, 1, 50000, '2023-12-01'),
(302, 2, 60000, '2023-12-15'),
(303, 3, 55000, '2024-01-01'),
(304, 4, 48000, '2024-02-01'),
(305, 5, 62000, '2024-02-15');
1 INNER JOIN
SELECT
E.Name, D.DepartmentName
FROM
Employees E
INNER
JOIN Departments D ON E.DepartmentID = D.DepartmentID;
2.LEFT JOIN/LEFT OUTER JOIN :
SELECT
E.Name, P.ProjectName
FROM
Employees E
LEFT JOIN
EmployeeProjects EP ON E.EmployeeID = EP.EmployeeID
LEFT JOIN
Projects P ON EP.ProjectID = P.ProjectID
WHERE
P.ProjectName IS NULL;
CROSS JOIN :
SELECT employees.name, departments.DepartmentName
FROM employees
CROSS JOIN
departments;
SELECT
E.Name, D.DepartmentName
FROM
Employees E
INNER
JOIN Departments D ON E.DepartmentID = D.DepartmentID;
SELECT
E.Name, P.ProjectName
FROM
Employees E
LEFT JOIN
EmployeeProjects EP ON E.EmployeeID = EP.EmployeeID
LEFT JOIN
Projects P ON EP.ProjectID = P.ProjectID
WHERE
P.ProjectName IS NULL;
SELECT
Name, Salary
FROM
Employees
WHERE
Salary = (SELECT MAX(Salary) FROM Employees);
SELECT employees.name, departments.DepartmentName
FROM employees
CROSS JOIN
departments;
FINDING MANAGER NAME WITHIN THE TABLE
SELECT *
FROM employees
REAL TIME EXAMPLE :
SELECT EMP.NAME,EMPM.NAME as mangername FROM
EMPLOYEES EMP
LEFT JOIN EMPLOYEES EMPM ON EMP.MANAGERID=EMPM.EmployeeID
FINDING DUPLICATE COUNT
WITH DuplicateCTE AS (
SELECT Name, COUNT(*) OVER(PARTITION BY Name) AS DuplicateCount
FROM Employees
)
SELECT DISTINCT Name, DuplicateCount FROM DuplicateCTE WHERE DuplicateCount > 1;
SELECT EmployeeID, Name, DepartmentID, Salary,
RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
FINDING MANAGER NAME WITHIN THE TABLE
SELECT *
FROM employees
SELECT EMP.NAME,EMPM.NAME as mangername FROM
EMPLOYEES EMP
LEFT JOIN EMPLOYEES EMPM ON EMP.MANAGERID=EMPM.EmployeeID
FINDING DUPLICATE COUNT
WITH DuplicateCTE AS (
SELECT Name, COUNT(*) OVER(PARTITION BY Name) AS DuplicateCount
FROM Employees
)
SELECT DISTINCT Name, DuplicateCount FROM DuplicateCTE WHERE DuplicateCount > 1;
SALARY RANKS
SELECT EmployeeID, Name, DepartmentID, Salary,
RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
