SQL query on relational schema

Question

I'm trying to find the best solutions for operations on the following database:

 Employee (EmployeeId, Surname, Salary, DepartmentId)
 Department (DepartmentId, DeptName, City, Director)
 Project (ProjectId, ProjectName, Budget, Director)
 Works (EmpoyeeId, ProjectId)

My queries:

1 SQL query to find the surname of the employees who are directors of a department.

SELECT Surname
FROM Employee
WHERE Employee.EmployeeId IN (
SELECT Department.Director
FROM Department
);

2 SQL query to find the employee surname and salary of all employees who work in Haarlem.

SELECT Surname, Salary, City
FROM Employee,Department
WHERE Department.City = ‘Haarlem’;

3 SQL query to find the names of the projects whose budget is higher than 100 and the surnames of the employees who work on them.

SELECT ProjectName, Budget, Surname
FROM Project, Employee, Works
WHERE Project.Budget > 100
AND Works.EmployeeId = Employee.EmployeeId
AND Works.ProjectId = Project.ProjectId;

Are there any better ways to get the information needed?

• choroba: Can anyone be a director of a department that's different to their department from the Employee table? Why don't you use a JOIN for the first query?
• Reinderien: What flavour of SQL? They're unfortunately not all the same.

Answer 1

1. SELECT Surname
   FROM Employee
   JOIN Department ON Department.Director = Employee.EmployeeId;
   

   Other than not requiring a subquery, this is generally a better thing to do because you can then easily access the matching Department table if you ever need to select a column from it.

2. Are you sure that this even works? You aren't joining the employee's department to the department ID. This should likely be:

   SELECT Surname, Salary, City
   FROM Employee
   JOIN Department ON Department.DepartmentId = Employee.DepartmentId
    AND Department.City = 'Haarlem';
   

3. SELECT ProjectName, Budget, Surname
   FROM Employee
   JOIN Works ON Works.EmployeeId = Employee.EmployeeId
   JOIN Project ON Project.ProjectId = Works.ProjectId
    AND Project.Budget > 100;
   

   This should be functionally equivalent but uses cleaner syntax.