Is the query correct

Question

I have 6 tables (SQL Fiddle):

CREATE TABLE Department(
    Dname varchar(10),
    Dnumber int(5),
    Mgrno int(5),
    PRIMARY KEY(Dnumber)
    );

CREATE TABLE Employee(
    Fname varchar(20),
    Lname varchar(20),
    Empno int(5),
    Bdate date,
    Address varchar(10),
    Salary float(5),
    Dnumber int(5),
    PRIMARY KEY(Empno),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
    );


CREATE TABLE Location(
    Dnumber int(5),
    Dlocation varchar(10),
    PRIMARY KEY(Dlocation),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
    );

CREATE TABLE Project(
    Pname varchar(10),
    Pnumber int(5),
    Location varchar(10),
    Dnumber int(5),
    PRIMARY KEY(Pnumber),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
    );

CREATE TABLE Timesheet(
    Empno int(5),
    Pnumber int(5),
    Hours_per_day int(5),
    FOREIGN KEY(Empno) REFERENCES Employee(Empno),
    FOREIGN KEY(Pnumber) REFERENCES Project(Pnumber)
    );

 CREATE TABLE Dependent(
    Empno int(5),
    Dependent_name varchar(10),
    DOB date,
    Sex varchar(5),
    Relationship varchar(10),
    FOREIGN KEY(Empno) REFERENCES Employee(Empno)
    );

I am asked to write a query to retrieve the details of all the employees who works on the project number 30,40,50

Here is my attempt:

      SELECT * FROM Employee JOIN Timesheet
      ON  Employee.Empno = Timesheet.Empno 
      WHERE (Pnumber = 30 OR Pnumber = 40 OR Pnumber = 50)

Is my implementation of the condition after the WHERE clause appropriate ?

The question is from a written exam so, I didn't provide any data set.

Thanks in advance!

Answer 1

Yes, the conditions in the WHERE clause appear to be valid.

There's a whole lot of table definitions, but we don't see any definition of the Works_on table. I expect this was actually meant to be a reference to Timesheet table.

Note that the join operation will cause multiple rows to be returned for an employee; this query will return the Employee row for each matching row in Timesheet.

I recommend qualifying all column references, even if they aren't ambiguous in the statement. That aids the future reader, and also prevents the query from breaking when columns of the same name are added to another table.

Also, I'd recommend ditching the * in the SELECT list, and explicitly list the columns to be returned. Or, if we have to use a * to return all columns, qualify that with a table name (or table alias).

---

As written, the query would return a list of employees that had worked on any one of the projects. That is an Employee that worked on projects 30 and 40, but has not worked on project 50, would be included.

Another interpretation of the requirement would be to return only those employees that have worked on all three of the projects. That result would require a different query.

Here's one (rather long winded) possible approach) to satisfying that specification:

SELECT e.*
  FROM Employee e
 WHERE EXISTS ( SELECT 1 
                  FROM Timesheet t1
                 WHERE t1.empno = e.empno
                   AND t1.pnumber = 30
              ) 
   AND EXISTS ( SELECT 1
                  FROM Timesheet t2
                 WHERE t2.empno = e.empno
                   AND t2.pnumber = 40
              ) 
   AND EXISTS ( SELECT 1
                  FROM Timesheet t3
                 WHERE t3.empno = e.empno
                   AND t3.pnumber = 50
              ) 

There are other query patterns that would also satisfy the specification.

For example, using an aggregate expression in the HAVING clause...

SELECT e.*
  FROM ( SELECT t.empno 
           FROM Timesheet t
          WHERE t.pnumber in (30,40,50)
          GROUP
             BY t.empno
         HAVING COUNT(DISTINCT t.pnumber) = 3
       ) q
  JOIN Employee e
    ON e.empno = q.empno 
 ORDER
    BY e.empno

Or, as @MKhalidJunaid suggests, we could use conditional aggregation ...

SELECT e.*
  FROM ( SELECT t.empno
           FROM Timesheet t
          GROUP
             BY t.empno
         HAVING MAX(IF( t.pnumber = 30 ,1,0))
            AND MAX(IF( t.pnumber = 40 ,1,0))
            AND MAX(IF( t.pnumber = 50 ,1,0))
       ) q
  JOIN Employee e
    ON e.empno = q.empno
 ORDER
    BY e.empno

Answer 2

You could simply use IN.

     SELECT * FROM Employee JOIN timesheet
  ON  Employee.Empno = timesheet.Empno 
  WHERE Pnumber in( 30,40,50)

Since it's exam, they sometimes do trick of words and any of project or must be all the project. My answer is for Anyone of project.

If it's later, you need to use AND.