in Databases edited by
1,368 views
2 votes
2 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),

Which of the following query/queries return the employee ID and name of employees whose salary is greater than the salary of all employees in department number $20$ of university. Order result by employee ID (refer table structures given above).

  1. SELECT EID, NAME 
    FROM EMPLOYEE 
    WHERE SALARY>(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)
    ORDER BY EID
    
  2. SELECT EID, NAME
    FROM EMPLOYEE
    WHERE SALARY>(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20);
    
  3. SELECT EID, NAME
    FROM EMPLOYEE
    WHERE SALARY > ALL(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)
    ORDER BY EID

     

Choose the correct answer from the options given below:

  1. $i$ and $ii$ only
  2. $i$ and $iii$ only
  3. $ii$ only
  4. $iii$ only
in Databases edited by
1.4k views

2 Answers

1 vote
1 vote

SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20 

this will return salary of all employees from employee table

so 

WHERE SALARY>(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20) 

this condition will be  true even if  LHS Salary is > salary of  at least one employee in deptno=20

but we want details of those whose salary is greater than the salary of all employees in department number 2020 of university.Hence all keyword is required which check condition with all employees

               SELECT EID, NAME  FROM EMPLOYEE

              WHERE SALARY > ALL(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)

               ORDER BY EID

Hence option 4) i.e  statement c only is correct ans 

  

0 votes
0 votes

Option 4 ic the correct answer.

Caption

 

Answer:

Related questions