SQL Challenge Assignment that tests various SQL concepts, including querying, joins, subqueries, and more.

[Pankaj-Str]

SQL Challenge Assignment

Instructions:

• Write SQL queries to solve the following challenges.
• Ensure your queries are optimized and return the correct results.
• You may use any SQL database for testing.

Database Schema:

Table 1: Employees

Column Name	Data Type	Description
employee_id	INT PRIMARY KEY	Unique ID of the employee
first_name	VARCHAR(50)	First name of the employee
last_name	VARCHAR(50)	Last name of the employee
department	VARCHAR(50)	Department of the employee
salary	DECIMAL(10, 2)	Salary of the employee
hire_date	DATE	Date the employee was hired

Table 2: Departments

Column Name	Data Type	Description
department_id	INT PRIMARY KEY	Unique ID of the department
department_name	VARCHAR(50)	Name of the department
location	VARCHAR(50)	Location of the department

Table 3: Projects

Column Name	Data Type	Description
project_id	INT PRIMARY KEY	Unique ID of the project
project_name	VARCHAR(50)	Name of the project
department_id	INT FOREIGN KEY	Department responsible for the project

Table 4: Employee_Project

Column Name	Data Type	Description
employee_id	INT FOREIGN KEY	ID of the employee
project_id	INT FOREIGN KEY	ID of the project
hours_worked	DECIMAL(5, 2)	Number of hours worked by the employee on the project

Challenges:

1. Highest Salary Employee in Each Department: Write a query to find the employee with the highest salary in each department.

2. Department with the Most Employees: Write a query to find the department with the highest number of employees.

3. Average Salary by Department: Write a query to calculate the average salary for each department.

4. Employees Who Joined in the Last 6 Months: Write a query to find all employees who joined the company in the last 6 months.

5. Projects by Department: Write a query to list all the projects along with the department name they belong to.

6. Employee Hours on Projects: Write a query to find the total hours worked by each employee on all projects combined.

7. Employees Not Assigned to Any Project: Write a query to find all employees who are not assigned to any project.

8. Employees Working on Multiple Projects: Write a query to find all employees who are working on more than one project.

9. Department with the Highest Total Hours Worked on Projects: Write a query to find the department with the highest total hours worked on all projects.

10. Salary Increase by 10% for Employees in a Specific Department: Write a query to update the salaries of all employees in a specific department (e.g., 'IT') by 10%.

11. List All Employees Along with Their Project Names: Write a query to list all employees along with the names of the projects they are working on. If an employee is not working on any project, still include them in the list with a NULL project name.

12. Find Employees in the Same Department with the Same Salary: Write a query to find employees who work in the same department and have the same salary.

13. Top 3 Highest Paid Employees in the Company: Write a query to find the top 3 highest-paid employees in the company.

14. Department with the Longest-Serving Employees: Write a query to find the department with the longest-serving employees (based on the earliest hire_date).

15. Employee Project Participation Report: Write a query that generates a report showing the employee's name, department, and the total number of projects they are participating in.

Bonus Challenge:

16. Project Budget Calculation: Assume each department has a budget allocated for projects. Write a query to calculate the total budget used by each department based on employee hours worked. Assume each hour worked is billed at $50.

17. Employees Hired on the Same Day: Write a query to find employees who were hired on the same day.

18. Monthly Salary Expenditure by Department: Write a query to calculate the total monthly salary expenditure for each department.

19. Find the Project with the Maximum Number of Employees Assigned: Write a query to find the project with the maximum number of employees assigned to it.

20. Identify Gaps in Employee IDs: Write a query to identify any gaps in the employee IDs sequence.

---

Submission Instructions:

• Submit your SQL queries in a text file or SQL script file.
• Ensure that your queries run correctly on your database of choice.

---

This challenge assignment will help solidify your SQL skills by tackling real-world database scenarios. Good luck!

[AnmolDixitB13]

use mydb

------------------	Creating tables & adding suitable constraints	-----------------

create table Employees(
employee_id int primary key,
first_name varchar(20),
last_name varchar(20),
department varchar(20),		
salary decimal(10, 2),
hire_date date
) ;

create table Departments(
department_id int primary key,	
department_name varchar(20),
location varchar(20)
) ;

create table Projects(
project_id int primary key,
project_name varchar(20),
department_id int) ;

alter table Projects
alter column project_name varchar(50) ;

alter table Projects
add constraint fkey_dept_id foreign key(department_id) references Departments(department_id) ;

create table Employee_Project(
employee_id int,
project_id int,		
hours_worked decimal(5, 2)) ;


alter table Employee_Project
add constraint f_key_emp_id foreign key(employee_id) references Employees(employee_id) ;

alter table Employee_Project
add constraint f_key_proj_id foreign key(project_id) references Projects(project_id) ;

-- dept workprofile, for reference only
-- you may assume your own dept workprofiles
-- 1, 2 = CELL CULTURE
-- 3 = PHARMA
-- 4 = IT

--____________________________________________________________________
-------------------		Inserting some sample records		------------------

-- sample dept id's = 1, 2, 3, 4
-- sample department names = aaaa, bbbb, cccc, dddd


-- empid, fname, lname, dept name, sal, hiredate
insert into Employees output inserted.* values(1221, 'yo', 'y', 'aaaa', 2000, '2023-05-31') ;

-- dept id, dept name, location
insert into Departments values(1, 'aaaa', 'Los Angeles') ;
insert into Departments values(2, 'bbbb', 'Houston') ;
insert into Departments values(3, 'cccc', 'Chicago') ;
insert into Departments values(4, 'dddd', 'Atlanta') ;

-- proj id, proj name, dept id
insert into Projects output inserted.* values(14, 'hepatocyte cell culture', 1) ;

-- empid, projid, hoursworked
insert into Employee_Project output inserted.* values(5555, 12, 20) ;

select * from  Departments ;
select * from  Employees ;
select * from  Projects ;
select * from  Employee_Project ;


--____________________________________________________________________
------------------	Questions in the assignment		------------------

--1. highest salary employee in each department
select max(salary), department from Employees group by department ;


--2. department with the most employees
-- steps
-- apply join on tables suitably and fetch the suitable data into a new table, then apply queries on this
-- new table to get the desired/required O/P

create table Question2(
dept_id tinyint,
dept_name varchar(5),
proj_id tinyint,
emp_id smallint
) ;

insert into Question2 output inserted.* 
select d.department_id, d.department_name, p.project_id, ep.employee_id
from Departments d
full join Projects p
on d.department_id = p.department_id 
full join Employee_Project ep
on p.project_id = ep.project_id ;

-- select dept_name, count(emp_id) as cnt from Question2 group by dept_name
-- works fine

select dept_name, cnt from 
(select dept_name, count(emp_id) as cnt from Question2 group by dept_name) as dept_cnt
where cnt = (
select max(cnt) from (
select count(emp_id) as cnt from Question2 group by dept_name
) as inner_cnt
) ;
-- works well
-- writing aliasis inner_cnt and dept_cnt are compulsory, else the query throws error


--3. avg sal by dept
select department, avg(salary) as avg_sal from Employees group by department ;


--4. employees who joined in last 6 months
-- select * from Employees where ( datediff(month, getdate(), (select hire_date from Employees)) <= 6) ;
select * from Employees where hire_date >= dateadd(month, -6, getdate()) ;

--5. group projects by departments
select count(project_name) as proj_cnt, department_id from Projects group by department_id ;

select count(project_name) as proj_cnt, department_id, project_id from Projects group by department_id ;
-- to include project id also
SELECT p.project_id, p.department_id, dc.proj_cnt
FROM Projects p
JOIN (
    SELECT department_id, COUNT(project_id) AS proj_cnt
    FROM Projects
    GROUP BY department_id
) dc ON p.department_id = dc.department_id;

-- select Projects.project_id, Projects.department_id, Projects.project_name, count(Projects.project_name)
-- from Projects join (select count(project_name), department_id from Projects group by department_id) p
-- on Projects.department_id = p.department_id ;

-- dc: This is an alias given to the derived table or subquery that calculates the count of projects
-- per department.



--6. employee hours on project
select employee_id, sum(hours_worked) as emp_hours_on_proj from Employee_Project group by employee_id ;



--7. employees not assigned to any project
select * from Employees where employee_id not in (select employee_id from Employee_Project) ;


-- projects having multiple employees
select employee_id, count(project_id) as cnt from Employee_Project group by employee_id having count(project_id) > 1 ;
-- doubt, no o/p

select * from Employees where employee_id in (select project_id, count(employee_id) as cnt from Employee_Project group by project_id having count(employee_id) > 1)
-- error

select project_id, count(employee_id) as cnt from Employee_Project group by project_id having count(employee_id) > 1 ;
-- works

select * from Employees
where employee_id in (
    select employee_id
    from Employee_Project
    where project_id in (
        select project_id
        from Employee_Project
        group by project_id
        having count(employee_id) > 1
    )
);

select * from Projects
select * from Employee_Project


--8. employees working on multiple projects
SELECT e.*
FROM Employees e
JOIN (
    SELECT employee_id
    FROM Employee_Project
    GROUP BY employee_id
    HAVING COUNT(project_id) > 1
) ep ON e.employee_id = ep.employee_id;


--9. department with the highest total hours worked on all projects

create table Question9(
d_id tinyint,
d_name varchar(5),
p_id tinyint, 
hrs smallint) ;

insert into Question9 output inserted.* 
select d.department_id, d.department_name, p.project_id, ep.hours_worked
from Departments d
full join Projects p
on d.department_id = p.department_id
full join Employee_Project ep 
on p.project_id = ep.project_id ;

select d_name, hrs from Question9 where hrs = 
(select max(mh) from 
(select d_name, sum(hrs) as mh from Question9 group by d_name) as dept_hrs) ;
-- produces blank o/p

select d_name, total_hours
from (
    select d_name, SUM(hrs) AS total_hours
    from Question9
    group by d_name
) as dept_hrs
where total_hours = (
    select MAX(total_hours)
    from (
        select SUM(hrs) as total_hours
        from Question9
        group by d_name
    ) as max_hrs
);
-- works well


--10. give a 10% salary hike to all the employees of a department, say department 'cccc'
update Employees
set salary = (salary + 0.1*salary) output inserted.* where department = 'cccc' ;

-- update Employees
-- set salary = (salary + 0.1*salary) output inserted.* where department = 'cccc' and hire_date > dateadd(year, -5, getdate()) ;



--11. List All Employees Along with Their Project Names

select ep.employee_id, ep.project_id, p.project_name
from Employee_Project ep
join Projects p
on ep.project_id = p.project_id ;
-- works well

select ep.employee_id, e.first_name, ep.project_id, p.project_name
from Employee_Project ep
full join Projects p
on ep.project_id = p.project_id 
full join Employees e
on e.employee_id = ep.employee_id ;
-- works well
-- if full is not written, null records are not displayed

select e.employee_id, e.first_name, p.project_id, p.project_name
from Employees e
full join Employee_Project ep
on e.employee_id = ep.employee_id
full join Projects p
on ep.project_id = p.project_id ;
-- works better than previous ones



--12. employees who work in same department and have same salary
select e1.employee_id as employee_id_1, e1.department, e1.salary as salary_1,
       e2.employee_id as employee_id_2, e2.salary as salary_2
from Employees e1
join Employees e2
  on e1.department = e2.department
  and e1.salary = e2.salary
  and e1.employee_id <> e2.employee_id
-- order by e1.department, e1.salary, e1.employee_id, e2.employee_id;



--13. top 3 highest paid employees in the company
select top 3 * from Employees
order by salary desc ;



--14. dept with longest serving employee
select * from Employees where hire_date = (select min(hire_date) from Employees) ;

-- alternate method
alter table Employees
add employment_years tinyint ;

update Employees
set employment_years = datediff(year, hire_date, getdate())
where employee_id is not null ;

select * from Employees where employment_years = (select max(employment_years) from Employees) ;
-- this approach also works fine



--15. Write a query that generates a report showing the employee's name, department, and the total number
-- of projects they are participating in

-- steps 
-- shift necessary data to a new table and then perform suitable counting operation on this new table

create table emp_proj_part_report(
emp_id int,
name varchar(50),
dept varchar(50),
proj_id tinyint 
) ;

insert into emp_proj_part_report
select e.employee_id, e.first_name + ' ' +  e.last_name as name, e.department, ep.project_id
from Employees e
join Employee_Project ep
on e.employee_id = ep.employee_id ;

select * from Employee_Project ;

select name, count(proj_id) from emp_proj_part_report group by name ;


--__________________________________________________________________________________
---------------	Bonus Questions		----------------

--16. find total budget used by all departments
-- steps
-- perform suitable joins and transfer the obtained necessary data to a new table and then perform 
-- calculations on this new table

create table budget(
d_id tinyint,
d_name varchar(30),
p_id tinyint,
hours_worked float,
budget float) ;


insert into budget(d_id, d_name, p_id, hours_worked) output inserted.* 
select d.department_id, d.department_name, p.project_id, ep.hours_worked
from Departments d
join Projects p
on d.department_id = p.department_id
join Employee_Project ep
on p.project_id = ep.project_id ;

update budget
set budget = 50*hours_worked output inserted.* where d_id is not null ;



--17. Employees Hired on the Same Day

select e1.employee_id, e1.hire_date, e2.employee_id, e2.hire_date
from Employees e1
join Employees e2
on e1.hire_date = e2.hire_date and e1.employee_id <> e2.employee_id ;
-- works fine



--18. Monthly Salary Expenditure by Department
-- steps
-- apply suitable joins, transfer this obtained data to a new table and then perform calculations on this
-- new table

create table budget2(
emp_id int,
d_name varchar (20),
d_id tinyint,
sal int,
) ;

insert into budget2 output inserted.*
select e.employee_id, e.department, d.department_id, e.salary
from Employees e
join Departments d
on e.department = d.department_name ;

select d_name, sum(sal) from budget2 group by d_name ;
-- works well



--19. Project with the Maximum Number of Employees Assigned
SELECT *
FROM Employee_Project
WHERE project_id IN (
    SELECT project_id
    FROM (
        SELECT project_id, COUNT(employee_id) AS employee_count
        FROM Employee_Project
        GROUP BY project_id
    ) AS project_counts
    WHERE employee_count = (
        SELECT MAX(employee_count)
        FROM (
            SELECT project_id, COUNT(employee_id) AS employee_count
            FROM Employee_Project
            GROUP BY project_id
        ) AS max_counts
    )
);


--20. identify gaps in employee id sequence
SELECT e1.employee_id AS current_id, e2.employee_id AS next_id
FROM Employees e1
LEFT JOIN Employees e2
  ON e1.employee_id + 1 = e2.employee_id
WHERE e2.employee_id IS NULL
ORDER BY e1.employee_id;