-- Insert into department table
INSERT INTO department (dept_id, dept_name, location) VALUES
(1, 'IT', 'Mumbai'),
(2, 'HR', 'Pune'),
(3, 'Finance', 'Delhi'),
(4, 'Operations', 'Hyderabad'),
(5, 'Marketing', 'Bangalore');
-- Insert into project table
INSERT INTO project (proj_id, proj_name, dept_id) VALUES
(1, 'Project A', 1),
(2, 'Project B', 2),
(3, 'Project C', 3),
(4, 'Project D', 4),
(5, 'Project E', 5),
(6, 'Project F', 4);
-- Insert into employee table
INSERT INTO employee (emp_id, emp_name, dept_id, proj_id, salary) VALUES
(1, 'Rohan Verma', 1, 1, 60000),
(2, 'Aditi Rao', 2, 2, 50000),
(3, 'Rajeev Singh', 1, 1, 55000),
(4, 'Neha Mehta', 3, 3, 70000),
(5, 'Arjun Kapoor', 2, 2, 62000),
(6, 'Priya Joshi', 4, 4, 75000),
(7, 'Aman Verma', 5, NULL, 48000),
(8, 'Sonia Patel', 4, 6, 80000),
(9, 'Karan Singh', 3, NULL, 65000),
(10, 'Ravi Sharma', NULL, 5, 55000);
1. List all employees along with their department names and project names.
- Hint: Use an INNER JOIN to combine the `employee`, `department`, and `project` tables.
SELECT
e.emp_name AS Employee_Name,
d.dept_name AS Department_Name,
p.proj_name AS Project_Name
FROM
employee e
INNER JOIN
department d ON e.dept_id = d.dept_id
INNER JOIN
project p ON e.proj_id = p.proj_id;
2. Find all employees who work in departments located in 'Bangalore', including their project
names.
- Hint: Use an INNER JOIN to combine the `employee`, `department`, and `project` tables,
and filter
by location.
3. List all employees and their department names, including employees who are not
assigned to any
project.
- Hint: Use a LEFT JOIN between `employee` and `project`, and an INNER JOIN between
`employee`
and `department`.
4. Show all departments and the projects they are working on, including departments that
have no
employees assigned to any projects.
- Hint: Use a RIGHT JOIN between `department` and `project`, and a LEFT JOIN between
`department` and `employee`.
5. Find the total salary paid to employees in each department for each project. Include
departments
and projects where there are no employees assigned
- Hint: Use a LEFT JOIN between `department` and `employee`, and a RIGHT JOIN
between
`department` and `project`. Then, use a GROUP BY on the department and project names.
6. List all employees whose department is 'IT' or 'HR', and display their project names.
- Hint: Use an INNER JOIN to combine the `employee`, `department`, and `project` tables,
and filter
by department name.
7. Show all projects and their associated departments, including projects that have no
employees
working on them.
- Hint: Use a RIGHT JOIN between `project` and `employee`, and an INNER JOIN between
`project`
and `department`.
8. List the names of all employees who are assigned to projects that belong to the
'Operations'
department.
- Hint: Use an INNER JOIN to combine the `employee`, `project`, and `department` tables,
and filter
by department name.
9. Find all employees who are working on more than one project. Display their names along
with the
project names and department names.
- Hint: Use an INNER JOIN between the `employee`, `department`, and `project` tables, and
apply a
HAVING clause to count the projects.
10. Display all departments and the names of employees who are not assigned to any
department
but are working on projects.
- Hint: Use a RIGHT JOIN between `employee` and `project`, and a LEFT JOIN between
`employee`
and `department`, then filter where the `dept_id` is NULL.