Database-Management-System

Question 267

Comprehension:

Consider the following table structures related to a university for Q96 to Q100:-

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 (DANAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID);

PROJECT WORK:

        EMPID VARCHAR (30) NOT NULL,

        PROJNO INT (5) NOT NULL,

        PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNO),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

97: 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.

A	SELECT EMPID FROM PROJECTWORK WHERE PROJNO=(20,30,40);
B	SELECT EMPID FROM PROJECTWORK WHERE PROJNO IN (20,30,40);
C	SELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO IN (20,30,40);
D	SELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO=20,30,40;

Database-Management-SystemSQLUGC NET JRF November 2020 Paper-2

Question 267 Explanation:

Option (1): Syntactical error.
mysql> select empid from projectwork where projno=(20,30,40);
ERROR : Operand should contain 1 column(s)
Option (2): (Duplicate empid’s present)
mysql> select empid from projectwork where projno in(20,30,40);

Option (3): (Unique empid’s present)
mysql> select distinct empid from projectwork where projno in(20,30,40);

Option (4): Syntactical error
mysql> select distinct empid from projectwork where projno=20,30,40;
SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘,30,40’ at line 1

Correct Answer: C

Question 267 Explanation:

Option (1): Syntactical error.
mysql> select empid from projectwork where projno=(20,30,40);
ERROR : Operand should contain 1 column(s)
Option (2): (Duplicate empid’s present)
mysql> select empid from projectwork where projno in(20,30,40);

Option (3): (Unique empid’s present)
mysql> select distinct empid from projectwork where projno in(20,30,40);

Option (4): Syntactical error
mysql> select distinct empid from projectwork where projno=20,30,40;
SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘,30,40’ at line 1

Database-Management-System

Database-Management-System

Database-Management-System

Database-Management-System

Mandeep Singh

Related posts

ISO-OSI-layers