Untitled

USE Enterprise
--Q1 What are the full details of all employees in the alphabetical order of last then first names within every department?
SELECT * 
FROM [Enterprise.Employee] e
WHERE e.departmentId IS NOT NULL
ORDER BY e.departmentId, e.employeeLastName, e.employeeFirstName;

--Q2 How many employee couples?
SELECT COUNT(*) AS 'NumberOfCouples'
FROM [Enterprise.Marry];

--Q3 What are the employees’ names, the corresponding department names and phone numbers, in the alphabetical order of last then first names?
SELECT e.employeeFirstName, e.employeeLastName, d.departmentName, d.departmentPhone
FROM [Enterprise.Employee] e
JOIN [Enterprise.Department] d ON e.departmentId = d.departmentId
ORDER BY e.employeeLastName, e.employeeFirstName;

--Q4 For each department name, what are the corresponding phone number and the number of employees in the department, in the order of department names?
SELECT d.departmentName, d.departmentPhone, COUNT(e.employeeId) AS 'NumberOfEmployees'
FROM [Enterprise.Department] d
LEFT JOIN [Enterprise.Employee] e ON d.departmentId = e.departmentId
GROUP BY d.departmentName, d.departmentPhone
ORDER BY d.departmentName;

--Q5 For each department name, what are the corresponding phone number and the number of employees in the department, in the descending order of numbers of employees in the department?
SELECT d.departmentName, d.departmentPhone, COUNT(e.employeeId) AS 'NumberOfEmployees'
FROM [Enterprise.Department] d
LEFT JOIN [Enterprise.Employee] e ON d.departmentId = e.departmentId
GROUP BY d.departmentName, d.departmentPhone
ORDER BY 'NumberOfEmployees' DESC;

--Q6 What are employee names, in the alphabetical order of their last then first names, and numbers of assigned projects, where each employee was assigned to at least two projects?
SELECT e.employeeFirstName, e.employeeLastName, COUNT(A.projectId) AS 'NumberOfProjects'
FROM [Enterprise.Employee] e
JOIN [Enterprise.Assign] a ON e.employeeId = a.employeeId
GROUP BY e.employeeFirstName, e.employeeLastName
HAVING COUNT(a.projectId) >= 2
ORDER BY E.employeeLastName, E.employeeFirstName;

--Q7 What are all details of employees in the departments, for which more than five employees work in?
SELECT e.*
FROM [Enterprise.Employee] e
JOIN (
    SELECT departmentId
    FROM [Enterprise.Employee]
    GROUP BY departmentId
    HAVING COUNT(employeeId) > 5
)
d ON e.departmentId = d.departmentId;

--Q8 For each project, what are the project number and the difference between its estimated cost and the overall average estimated cost of all projects?
SELECT p.projectId, p.projectName, 
       (p.projectEstCost - (SELECT AVG(projectEstCost) FROM [Enterprise.Project])) AS 'CostDifference'
FROM [Enterprise.Project] p;

--Q9 For the city with population above the average population among all cities, what are all details about the city and how many more population compared to the average population?
SELECT c.*, (c.cityPopulation - (SELECT AVG(cityPopulation) FROM [Enterprise.City])) AS 'PopulationDifference'
FROM [Enterprise.City] c
WHERE c.cityPopulation > (SELECT AVG(cityPopulation) FROM [Enterprise.City]);

--Q10 What are all details about the skills in the order of their descriptions, which employees apply on projects with estimated cost higher than 15000, using subquery
SELECT S.*
FROM [Enterprise.Skill] s
WHERE s.skillId IN (
    SELECT a.skillId
    FROM [Enterprise.Apply] a
    JOIN [Enterprise.Project] p ON a.projectId = p.projectId
    WHERE p.projectEstCost > 15000
)
ORDER BY s.skillName;

--Q11 What are all details about the oldest employee, using uncorrelated subquery?
SELECT *
FROM [Enterprise.Employee] e
WHERE e.employeeDOB = (SELECT MIN(employeeDOB) FROM [Enterprise.Employee]);

--Q12 What are all details about the oldest employee, using correlated subquery?
SELECT e1.*
FROM [Enterprise.Employee] e1
WHERE NOT EXISTS (
    SELECT 1
    FROM [Enterprise.Employee] e2
    WHERE e2.employeeDOB < e1.employeeDOB
);

--Q13 What are the details about projects, which employees either are assigned to or apply skills on, without using subquery?
SELECT DISTINCT p.*
FROM [Enterprise.Project] p
JOIN [Enterprise.Assign] a ON p.projectId = a.projectId
JOIN [Enterprise.Apply] ap ON p.projectId = ap.projectId;

--Q14 What are the details about projects, which employees either are assigned to or apply skills on, using subquery?
SELECT *
FROM [Enterprise.Project] p
WHERE p.projectId IN (
    SELECT projectId
    FROM [Enterprise.Assign] a
) OR p.projectId IN (
    SELECT projectId
    FROM [Enterprise.Apply] a
);

--Q15 What are the numbers of project assignments for all possible combinations of employees and then cities? (Hint: This is an OLAP query using GROUP BY CUBE.)
SELECT e.employeeId, c.cityId, COUNT(a.projectId) AS NumberOfAssignments
FROM [Enterprise.Employee] e
JOIN [Enterprise.Assign] a ON e.employeeId = a.employeeId
JOIN [Enterprise.City] c ON a.cityId = c.cityId
WHERE e.employeeId IS NOT NULL AND c.cityId IS NOT NULL
GROUP BY CUBE(e.employeeId, c.cityId)
HAVING COUNT(a.projectId) > 0;