Question 1

Select a query to display the department wise average salary.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select avg(SAL) from EMP grouped by DEPTNO;
select avg(SAL) from EMP group by DEPTNO;
select avg(SAL) from EMP grouping with DEPTNO ;
select avge(SAL) from EMP group by DEPTNO;
none of these

Answer

Correct Option: B

Question 2

Select a query to display the average salary of those departments that have an average salary greater than 2000.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select avg(SAL), DEPTNO, from EMP group by DEPTNO having avg(SAL)>2000;
select avg(SAL), DEPTNO, from EMP where group by DEPTNO having avg(SAL)>2000;
select avg(SAL), DEPTNO, from EMP group by DEPTNO where avg(SAL)>2000;
select avg(SAL), DEPTNO, from EMP group by DEPTNO having avg(SAL)>2000
none of these

Answer

Correct Option: A

Question 3

Select a query to display the department number and maximum for those departments whose maximum salary is greater than 2900.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select max(SAL), DEPTNO from EMP group by DEPTNO having max(SAL)>2600;
select max(SAL), DEPTNO from EMP group by DEPTNO having max(SAL)>2900;
select max(SAL), DEPTNO from EMP group by DEPTNO have max(SAL)>2900;
options 1 and 2
none of these

Answer

Correct Option: B

Question 4

Select a query to display the employee name, employee number and manager name along with the manager number for whom employee works.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select A.ENAME, A.EMPNO || 'works for' || B.ENAME,B.EMPNO where A.MGR=B.EMPNO;
select A.ENAME, A.EMPNO || 'works for' || B.ENAME,B.EMPNO where B.MGR=A.EMPNO;
select A.ENAME, A.EMPNO || 'works for' || B.ENAME,B.EMPNO where A.EMPNO=B.EMPNO;
options 2 and 3
none of these

Answer

Correct Option: A

Question 5

Select a query to display the employee name and it's manager number.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select A.ENAME, B.EMPNO from EMP A, EMP B where A.MGR=B.EMPNO;
select ENAME, MGR from EMP;
select A.ENAME, B.EMPNO from EMP A, EMP B where B.MGR=A.EMPNO;
options 1 and 2
none of these

Answer

Correct Option: D

Question 6

Select a query to display all the employees whose job title is the same as that of employee 7369.

consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME from EMP where JOB = (select JOB from EMP where EMPNO=7369;
select ENAME from EMP where JOB in (select JOB from EMP where EMPNO=7369);
select ENAME from EMP where JOB is (select JOB from EMP where EMPNO=7369;
select ENAME from EMP where JOB = (select JOB from EMP where EMPNO=7399;
options 1 and 2

Answer

Correct Option: E

Question 7

Select a query to display the number of people in each job.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select count(ENAME), JOB from EMP group by JOB;
select count(ENAME), JOB from EMP group by DEPTNO;
select count(ENAME), JOB from EMP group by JOB;
select counter(ENAME), JOB from EMP group by MGR;
none of these

Answer

Correct Option: C

Question 8

Select a query to display the employee name whose salary is greater than the employee 7566.

consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME from EMP where SAL>(select EMPNO from EMP where EMPNO=7566);
select ENAME from EMP where SAL=(select SAL from EMP where EMPNO=7566);
select ENAME from EMP where SAL>(select SAL from EMP where EMPNO=7566);
select EMPNAME from EMP where SAL>(select SAL from EMP where EMPNO=7566);
none of these

Answer

Correct Option: C

Question 9

Select a query to display the employee name, job, and salary for all employees whose salary is equals to the minimum salary.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME, JOB, SAL from EMP where SAL = (select low(SAL) from EMP);
select ENAME, JOB, SAL from EMP where SAL equal (select min(SAL) from EMP ;
select ENAME, JOB, SAL from EMP where SAL = (select min(SAL) from EMP);
select ENAME, JOB, SAL from EMP where SAL == (select min(SAL) from EMP);
none of these

Answer

Correct Option: C

Question 10

Select a query to display the maximum average salary amongst all departments.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select max(avg(SAL)) from EMP group by DEPTNO;
select max(SAL) and avg(SAL) from EMP group by DEPTNO;
select max(avg(SAL)) from EMP grouped by DEPTNO ;
select best(avg(SAL)) from EMP group by DEPTNO;
none of these

Answer

Correct Option: A

Question 11

Select a query to display employee name, employee number for all employees who earn more than average salary.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME, EMPNO from EMP where SAL > (select avg(SAL) from EMP);
select ENAME, EMPNO from EMP where SAL = (select avg(SAL) from EMP );
select ENAME, EMPNO from EMP where SAL > (select avge(SAL) from EMP );
select ENAME, EMPNUM from EMP where SAL > (select avg(SAL) from EMP );
none of these

Answer

Correct Option: A

Question 12

Select a query to display the employee name, hire date for all employees in the department as Blake.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME, HIREDATE from EMP where DEPTNO = (select DEPTNO from EMP where ENAME='BLACK');
select ENAME, HIREDATE from EMP where DEPTNO = (select DEPTNO from EMP where ENAME='BLAKE' ;
select ENAME, HIREDATE from EMP where DEPTNO = (select EMPNO from EMP where ENAME='BLAKE');
select ENAME, HIREDATE from EMP where DEPTNO = (select DEPTNO from EMP where ENAME=BLAKE);
none of these

Answer

Correct Option: B

Question 13

Select a query to display employee name, employee number for all employee who work in a department with any employee whose name contains letter 'T' .

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME, EMPNO from EMP where DEPTNO = (select DEPTNO from EMP where ENAME like 'T%');
select ENAME, EMPNO from EMP where DEPTNO = (select DEPTNO from EMP where ENAME like '%T%');
select ENAME, EMPNO from EMP where DEPTNO in (select DEPTNO from EMP where ENAME like '%T%');
select ENAME, EMPNO from EMP where DEPTNO in (select DEPTNO from EMP where ENAME like '%T');
none of these

Answer

Correct Option: C

Question 14

Select a query to display the employee name, salary for all employees who report to king.

consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME, SAL from EMP where MGR = (select MGR from EMP where ENAME='KING');
select ENAME, SAL from EMP where MGR = (select EMPNO from EMP where ENAME='KNG');
select ENAME, SAL from EMP where MGR = (select EMPNO from EMP where ENAME='KING');
select ENAME, SAL from EMP where EMPNO = (select EMPNO from EMP where ENAME='KING');
none of these

Answer

Correct Option: C

Question 15

Select a query to display the employee name, department number, job for all employee whose department location is Dallas.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE)...

select ENAME, DEPTNO, JOB from EMP where DEPTNO = (select DEPTNO from DEPT where LOC='DALLAS');
select ENAME, DEPTNO, JOB from EMP where DEPTNO = (select DEPTNO from EMP where LOC='DALLAS');
select ENAME, DEPTNO, JOB from EMP where DEPTNO == (select DEPTNO from DEPT where LOC='DALLAS');
select ENAME, DEPTNO, JOB from EMP where DEPTNO = (select DEPTNO from DEPT where LOC='DALAS');
options 1 and 4

Answer

Correct Option: A

Description: SQL SQL
Number of Questions: 15
Created by: Preeti Dasgupta
Tags: SQL

DBMS-6

Find more quizzes from top tags