Question 1

Select a query to display the number of employees who can earn commission in department number 30.

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

select count(ENAME) from EMP where DEPTNO=30 and COMM is NULL;
select count(ENAME) from EMP where DEPTNO=30 and COMM is not NULL;
select count(ENAME) from EMP where DEPTNO<30 and COMM is not NULL;
select count(ENAME) from EMP where DEPTNO=30 and COMM has not NULL value;
none of these

Answer

Correct Option: B

Question 2

Select a query to display the number of employees in department number 30.

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

select count(ENAME) from EMP where DEPTNO=30;
select count(EMPNO) from EMP where DEPTNO=30;
select count(SAL) from EMP where DEPTNO=30;
options 1 and 2
none of these

Answer

Correct Option: D

Question 3

Select a query to display the number of departments in employee table.

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

select count(distinct DEPTNO) from EMP;
select count(DEPTNO) from EMP;
select number(distinct DEPTNO) from EMP;
select count(distinct DEPTNO) from EMP
none of these

Answer

Correct Option: A

Question 4

Select a query to display hire date of the oldest employee and the newest employee.

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

select oldest(HIREDATE), newest(HIREDATE) from EMP;
select old(HIREDATE), new(HIREDATE) from EMP;
select max(HIREDATE), min(HIREDATE) from EMP;
select max(HIREDATE), mini(HIREDATE) from EMP;
options 1 and 2

Answer

Correct Option: C

Question 5

Select a query to display the highest and the lowest salary for all the employees.

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

select max(SAL), min(SAL) from EMP;
select high(SAL), low(SAL) from EMP;
select hi(SAL), lo(SAL) from EMP;
select best(SAL), min(SAL) from EMP;
none of these

Answer

Correct Option: A

Question 6

Select a query to display the average commission of employees.

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

select avg(COMM) from EMP;
select avg(COMM) from EMP where COMM is not NULL ;
select avge(COMM) from EMP;
select avg(COMM) from EMP where COMM not NULL ;
options 1 and 2

Answer

Correct Option: E

Question 7

Select a query to display the name of employees whose name ends with 'S' .

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

select ENAME from EMP where ENAME like '%S' ;
select ENAME from EMP where ENAME like '%%S' ;
select ENAME from EMP where ENAME like 'S' ;
select ENAME from EMP where ENAME like '%S%' ;
none of these

Answer

Correct Option: A

Question 8

Select a query to display employee name, the number of weeks employed for all the employees in the department number 10.

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

select ENAME, round(HIREDATE/7) from EMP where DEPTNO=10;
select ENAME, to_day(SYSDATE-HIREDATE)*7 from EMP where DEPTNO=10;
select ENAME, round((SYSDATE-HIREDATE)/7) from EMP where DEPTNO=10;
select ENAME, round((SYSDATE-HIREDATE)/7) from EMP where DEPTNO=20;
none of these

Answer

Correct Option: C

Question 9

Select a query to display the names and hire date for all the employees who joined on February 22, 1981.

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

select ENAME, HIREDATE from EMP where HIREDATE=to_date('February 22 1981','month dd yyyy');
select ENAME, HIREDATE from EMP where HIREDATE=to_date('Feb 22 1981','mmm dd yyyy');
select ENAME, HIREDATE from EMP where HIREDATE=to_form('February 22 1981','month dd yyyy') ;
select ENAME, HIREDATE from EMP where HIREDATE=todate('Feb 22 1981','mmm dd yyyy');
options 1 and 2

Answer

Correct Option: E

Question 10

Select a query to display all the employees who are not clerks and not in department number 30.

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

select ENAME from EMP where JOB not like 'CLERK' and DEPTNO not like 30
select ENAME from EMP where JOB not like 'CLERK' and DEPTNO not like 30;
select ENAME from EMP where JOB not as 'CLERK' and DEPTNO not as 30;
select ENAME from EMP where JOB not like 'CLARK' and DEPTNO not like 30;
none of these

Answer

Correct Option: B

Question 11

Select a query to display all the employees whose salary is between 1000 and 15000.

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

select ENAME from EMP where SAL>=1000 or SAL<=1500;
select ENAME from EMP where SAL>=1000 and SAL<=1500;
select ENAME from EMP where SAL=1000 and SAL<=1500;
select ENAME from EMPLOYEE where SAL>=1000 and SAL<=1500;
none of these

Answer

Correct Option: B

Question 12

Select a query to display the average and sum of salaries for all employees.

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

select average(SAL), sum(SAL) from EMP;
select avge(SAL), sum(SAL) from EMP ;
select avg(SAL), summation(SAL) from EMP;
select average(SALARY), sum(SAL) from EMP;
none of these

Answer

Correct Option: E

Question 13

Select a query to display the employee names, their salary and manager numbers for all the employees whose manager's employee numbers are 7902, 7566, 7788.

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

select ENAME, SAL, MGR, from EMP where MGR in (7902, 7566, 7788);
select ENAME, SAL, MGR, from EMP where MGR =(7902, 7566, 7788);
select ENAME, SAL, MGR, from EMP where MGR among (7902, 7566, 7788);
select ENAME, SAL, MGR, from EMP where MGR into (7902, 7566, 7788);
none of these

Answer

Correct Option: A

Question 14

Select a query to display the employee names, their salary along with the salary grades.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE). ... SALGRADE(LOSAL,HISAL,GRADE).... [NOTE::LOSAL-HISAL (i.e. low salary and high salary ) is a range for a particular grade... as example (LOSAL=2000 HISAL=4000 then GRADE=\\'A\\' ).... ]

select ENAME, SAL, GRADE from EMP where EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
select ENAME, SAL, GRADE from EMP,SALGRADE where EMP.SAL between SALGRADE.LOSAL and SALARY.HISAL ;
select ENAME, SAL, GRADE from EMP,SALGRADE where EMP.SAL among SALGRADE.LOSAL and SALGRADE.HISAL;
select ENAME, SAL, GRADE from EMP,SALGRADE where EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
none of these

Answer

Correct Option: D

Question 15

Select a query to display employee name, job, department name, salary, and their salary grade.

Consider the following relation schema ..... EMP(ENAME,EMPNO,SAL,COMM,DEPTNO,JOB,MGR,HIREDATE). ... SALGRADE(LOSAL,HISAL,GRADE).... [NOTE::LOSAL-HISAL (i.e. low salary and high salary ) is a range for a particular grade... as example (LOSAL=2000 HISAL=4000 then GRADE=\\'A\\' ).... ]

select EMP.ENAME, EMP.JOB, DEPT.DNAME, EMP.SAL, SALGRADE.GRADE from EMP, DEPT, SALGRADE where DEPT.DEPTNO=EMP.DEPTNO and EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
select ENAME, JOB, DNAME, SAL, GRADE from EMP, DEPT, SALGRADE where DEPT.DEPTNO=EMP.DEPTNO and EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
select EMP.ENAME, EMP.JOB, DEPT.DNAME, EMP.SAL, SALGRADE.GRADE from EMP, DEPT, SALGRADE where EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
options 1 and 2
none of these

Answer

Correct Option: D

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

DBMS (SQL)

Find more quizzes from top tags