Awesome q2a theme
0 votes
15 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 (817 points) | 15 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
Top Users 2020 Aug 10 - 16
  1. Arkaprava

    40 Points

  2. jayeshasawa001

    15 Points

  3. Nilabja Sarkar

    8 Points

  4. siddharths067

    4 Points

  5. 404 found

    3 Points

  6. shashankrustagi2021

    2 Points

  7. Ashutosh777

    2 Points

  8. Mellophi

    2 Points

  9. sparshgarg

    1 Points

  10. sonam13

    1 Points

Weekly Top User (excluding moderators) will get free access to GATE Overflow Test Series for GATE 2021
Welcome to GATE CSE Doubts, where you can ask questions and receive answers from other members of the community.
Top Users Aug 2020
  1. Mellophi

    152 Points

  2. Ashutosh777

    78 Points

  3. anurag sharma

    49 Points

  4. Arkaprava

    40 Points

  5. jayeshasawa001

    20 Points

  6. Kushagra गुप्ता

    15 Points

  7. Shaik Masthan

    13 Points

  8. srestha

    13 Points

  9. shashankrustagi2021

    12 Points

  10. munishchaturvedi

    10 Points

7,746 questions
1,845 answers
11,192 comments
95,103 users