Awesome q2a theme
0 votes
10 views

EMPLOYEE(Fname,Lname,Ssn,Bdate,Address,Sex,Salary,SuperSsn,Dnum)

WORKS_ON(ESsn,Pno,Hours)

PROJECT(Pname,Pnumber,Plocation,Dnum)

Retrieve

“Name of each Employee who works on all the Project controlled by department number 5”

Which of the following is correct?

A)Select Fname,Lname from Employee where not exists((Select Pnumber from Project where Dnum=5) EXCEPT (Select Pno from Works_On where Ssn=ESsn));

B)Select LName,Fname from Employee where Ssn in(Select ESsn from Works_On Bwhere B.Pno=All(Select Pnumber from Project where Dnum=5));


Which option is correct and why?

in Databases by (635 points) | 10 views
0

@Satbir @Shaik help plz

0
A is correct.
(Select Pnumber from Project where Dnum=5) - this returns all projects of Dept. No 5
Select Pno from Works_On where Ssn=ESsn - this returns all the projects that a person works on, for each employee.

When we take the difference of the two and this returns as empty, this means that either the person works on all projects of Dept 5 or a superset of it.
Not exists is true when the condition is empty, and hence, this tuple is selected.

When will this fail? When there are projects of dept 5 which the employee doesn't work on. In that case, not exists will not be empty and the query will not be selected.
0

When we take the difference of the two and this returns as empty,

we are not taking difference. right?? How empty relation output  " who works on all the Project controlled by department number 5"??

I havenot got.

Give some example 

0
EXCEPT in SQL is used to take difference.
0
give some example plz.

Please log in or register to answer this question.

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
Welcome to GATE CSE Doubts, where you can ask questions and receive answers from other members of the community.
Top Users Jan 2020
  1. shashin

    1163 Points

  2. Vimal Patel

    307 Points

  3. Deepakk Poonia (Dee)

    305 Points

  4. Debapaul

    237 Points

  5. Satbir

    192 Points

  6. SuvasishDutta

    137 Points

  7. Pratyush Priyam Kuan

    118 Points

  8. tp21

    108 Points

  9. DukeThunders

    96 Points

  10. pranay562

    95 Points

Monthly Top User and those within 60% of his/her points will get a share of monthly revenue of GO subject to a minimum payout of Rs. 500. Current monthly budget for Top Users is Rs. 75.
2,989 questions
1,509 answers
8,936 comments
89,814 users