TCS Xplore Hackathon Java - Hackerrank SQL

Unit - Hackerrank Activities - SQL

Q1. Employee Count in Each Department(Department wise)

Write the SQL query to display the name of the department along with the count of employees working in it.

Solution:
select Departments.deptName,COUNT(Employees.eDeptId) From departments LEFT Join Employees on Departments.deptId=Employees.eDeptId Group By Departments.deptId,Departments.deptName Order by count(Employees.eDeptId) Desc, departments.deptName;

Q2. Display Department Details

Write the SQL query to print the department id and name of the respective Department for all the departments which are located in Ground Floor

Solution:
select Dept_Id,Dept_Name from Department where Dept_Location='Ground Floor';

Q3.Display non-Programmer Department name

Write the SQL query to print the names of the departments which does not have any Programmers.

Solution:
select distinct Dept_name from Departments,Employees where Dept_ID=Emp_Dept_Id and Dept_name not in (select distinct Dept_name from Departments,Employees where Dept_id=Emp_Dept_Id and Emp_Skill like'Programmer');