recategorized by
1,049 views
1 votes
1 votes

 

Consider the following table structures related to a university for the below question.

EMPLOYEE

    NAME VARCHAR (30) NOT NULL,
    EID VARCHAR (10) NOT NULL,
    DEPTNO INT (5) NOT NULL,
    HODEID VARCHAR (10),
    SALARY INT (10),
PRIMARY KEY (EID),
FOREIGN KEY (HODEID) REFERENCES EMPLOYEE (EID),
FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT (DID);

DEPARTMENT

    DID INT (5) NOT NULL,
    DNAME VARCHAR(30) NOT NULL,
    HODID VARCHAR (10) NOT NULL,
    HODNAME VARCHAR (30),
PRIMARY KEY (DID),
UNIQUE (DNAME),
FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID)

PROJECT WORK:

    EMPID VARCHAR (10) NOTNULL,
    PROJNO INT(5) NOT NULL,
    PROJECTLOC VARCHAR (30) NOT NULL,
PRIMARY KEY (EMPID, PROJNO),
FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

On the basis of above given table structures, retrieve the distinct employee ID (EMPID) of all employees of university who are working on project. No. $20, 30$ and $40$

  1. SELECT EMPID FROM PROJECTWORK WHERE PROJNO=(20,30,40);
  2. SELECT EMPID FROM PROJECTWORK WHERE PROJNO IN (20,30,40);
    
  3. SELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO IN(20,30,40);
    
  4. SELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO=20,30,40;
    
recategorized by

1 Answer

1 votes
1 votes

Since primary key is composite  PRIMARY KEY (EMPID, PROJNO),  same empid may be repeated for a projno

hence distinct keyword is needed and to match with any of multiple values of PROJNO  IN keyword is required   

Hence 

option C) SELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO IN(20,30,40) is correct ans

Answer:

Related questions