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?

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? — choroba
– choroba, Commented Oct 8, 2019 at 20:47
What flavour of SQL? They're unfortunately not all the same. — Reinderien
– Reinderien, Commented Oct 9, 2019 at 2:12

Toby Speight · Accepted Answer · 2020-01-15 18:24:14Z

```
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.

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';

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.

Stack Exchange Network

SQL query on relational schema

1 Answer 1

You must log in to answer this question.

Hot Network Questions

SQL query on relational schema

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions