GATE CSE 1990 | Question: 10-a

Question

Consider the following relational database:

• employees (eno, ename, address, basic-salary)
• projects (pno, pname, nos-of-staffs-allotted)
• working (pno, eno, pjob)

The queries regarding data in the above database are formulated below in SQL. Describe in ENGLISH sentences the two queries that have been posted:

1. SELECT ename
   FROM employees
   WHERE eno IN 
       (SELECT eno
       FROM working
       GROUP BY eno
       HAVING COUNT(*)=
           (SELECT COUNT(*)
           FROM projects))

2. SELECT pname
   FROM projects
   WHERE pno IN
       (SELECT pno
       FROM projects
       MINUS
       SELECT DISTINCT pno
       FROM working);
   

Answer 1

1.SELECT ename
  FROM employees
  WHERE eno IN 
                 (SELECT eno
                 FROM working
                 GROUP BY eno
                 HAVING COUNT (*)=
                                    (SELECT COUNT (*)
                                    FROM projects));
This will return : Employee name who is working for all projects.
(ii)
SELECT pname
FROM projects
WHERE pno IN
                 (SELECT pno
                 FROM projects
                 MINUS
                 SELECT DISTINCT pno
                 FROM working);

This will return : Project name for which no employee is working.

Comments

For better understanding stmt (i):-

SELECT ename FROM employees WHERE eno IN (SELECT eno FROM working GROUP BY eno HAVING COUNT (*)= (SELECT COUNT (*) FROM projects));

First we calculate the no. of projects available. Then we match the count with each employee by GROUP BY clause. So if that particular $eno$ appears $count(*)$ number of times, it means the employee is working on all the projects. Finally we print the Employee Number.

---

SELECT eno
                 FROM working
                 GROUP BY eno
                 HAVING COUNT (*) 

 

How this query is working?

---

i think in working (pno,eno) should be key else follow scenario will arise
 

@Deepak Poonia sir can you please check