Question

Comprehension

Comprehension: Consider the following table structures related to a university for given questions.

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) REFRENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFRENCES 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 WORE:

EMPID	VARCHAR (10)	NOT NULL,
PROJNO	INT (5)	NOT NULL,
PROJECTLOC	VARCHAR (30)	NOT NULL,

PRIMARY KEY (EMPID, PROJNQ),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

1.

SELECT EMPID

FROM PROJECTWORK

WHERE PROJNO=(20, 30, 40);

2.

SELECT EMPID

FROM PROJECTWOREK

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;

Answer 1

Correct Answer - Option 3 :

SELECT DISTINCT EMPID

FROM PROJECTWORK

WHERE PROJNO IN (20, 30, 40);

The correct answer is option 3.

Since the primary key is a combination of two keys (EMPID, PROJNQ) so duplicate values may occur in EMID and PROJNQ. So distinct keys are required to eliminate duplicates in the combined table. And IN is a set operator it matches all table values to the set values.

∴ Hence the correct answer is the below query.

SELECT DISTINCT EMPID

FROM PROJECTWORK

WHERE PROJNO IN (20, 30, 40);