Home › Quizzes › DBMS (SQL)

DBMS (SQL)

SQL query practice covering aggregate functions, filtering, pattern matching, joins, and date operations

15 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

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);

  1. select count(ENAME) from EMP where DEPTNO=30 and COMM is NULL;
  2. select count(ENAME) from EMP where DEPTNO=30 and COMM is not NULL;
  3. select count(ENAME) from EMP where DEPTNO<30 and COMM is not NULL;
  4. select count(ENAME) from EMP where DEPTNO=30 and COMM has not NULL value;
  5. none of these
Question 2 Multiple Choice (Single Answer)

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);

  1. select count(ENAME) from EMP where DEPTNO=30;
  2. select count(EMPNO) from EMP where DEPTNO=30;
  3. select count(SAL) from EMP where DEPTNO=30;
  4. options 1 and 2
  5. none of these
Question 3 Multiple Choice (Single Answer)

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);

  1. select count(distinct DEPTNO) from EMP;
  2. select count(DEPTNO) from EMP;
  3. select number(distinct DEPTNO) from EMP;
  4. select count(distinct DEPTNO) from EMP
  5. none of these
Question 4 Multiple Choice (Single Answer)

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);

  1. select oldest(HIREDATE), newest(HIREDATE) from EMP;
  2. select old(HIREDATE), new(HIREDATE) from EMP;
  3. select max(HIREDATE), min(HIREDATE) from EMP;
  4. select max(HIREDATE), mini(HIREDATE) from EMP;
  5. options 1 and 2
Question 5 Multiple Choice (Single Answer)

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)...

  1. select max(SAL), min(SAL) from EMP;
  2. select high(SAL), low(SAL) from EMP;
  3. select hi(SAL), lo(SAL) from EMP;
  4. select best(SAL), min(SAL) from EMP;
  5. none of these
Question 6 Multiple Choice (Single Answer)

Select a query to display the average commission of employees.

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

  1. select avg(COMM) from EMP;
  2. select avg(COMM) from EMP where COMM is not NULL ;
  3. select avge(COMM) from EMP;
  4. select avg(COMM) from EMP where COMM not NULL ;
  5. options 1 and 2
Question 7 Multiple Choice (Single Answer)

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);

  1. select ENAME from EMP where ENAME like '%S' ;
  2. select ENAME from EMP where ENAME like '%%S' ;
  3. select ENAME from EMP where ENAME like 'S' ;
  4. select ENAME from EMP where ENAME like '%S%' ;
  5. none of these
Question 8 Multiple Choice (Single Answer)

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);

  1. select ENAME, round(HIREDATE/7) from EMP where DEPTNO=10;
  2. select ENAME, to_day(SYSDATE-HIREDATE)*7 from EMP where DEPTNO=10;
  3. select ENAME, round((SYSDATE-HIREDATE)/7) from EMP where DEPTNO=10;
  4. select ENAME, round((SYSDATE-HIREDATE)/7) from EMP where DEPTNO=20;
  5. none of these
Question 9 Multiple Choice (Single Answer)

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);

  1. select ENAME, HIREDATE from EMP where HIREDATE=to_date('February 22 1981','month dd yyyy');
  2. select ENAME, HIREDATE from EMP where HIREDATE=to_date('Feb 22 1981','mmm dd yyyy');
  3. select ENAME, HIREDATE from EMP where HIREDATE=to_form('February 22 1981','month dd yyyy') ;
  4. select ENAME, HIREDATE from EMP where HIREDATE=todate('Feb 22 1981','mmm dd yyyy');
  5. options 1 and 2
Question 10 Multiple Choice (Single Answer)

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);

  1. select ENAME from EMP where JOB not like 'CLERK' and DEPTNO not like 30
  2. select ENAME from EMP where JOB not like 'CLERK' and DEPTNO not like 30;
  3. select ENAME from EMP where JOB not as 'CLERK' and DEPTNO not as 30;
  4. select ENAME from EMP where JOB not like 'CLARK' and DEPTNO not like 30;
  5. none of these
Question 11 Multiple Choice (Single Answer)

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);

  1. select ENAME from EMP where SAL>=1000 or SAL<=1500;
  2. select ENAME from EMP where SAL>=1000 and SAL<=1500;
  3. select ENAME from EMP where SAL=1000 and SAL<=1500;
  4. select ENAME from EMPLOYEE where SAL>=1000 and SAL<=1500;
  5. none of these
Question 12 Multiple Choice (Single Answer)

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);

  1. select average(SAL), sum(SAL) from EMP;
  2. select avge(SAL), sum(SAL) from EMP ;
  3. select avg(SAL), summation(SAL) from EMP;
  4. select average(SALARY), sum(SAL) from EMP;
  5. none of these
Question 13 Multiple Choice (Single Answer)

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);

  1. select ENAME, SAL, MGR, from EMP where MGR in (7902, 7566, 7788);
  2. select ENAME, SAL, MGR, from EMP where MGR =(7902, 7566, 7788);
  3. select ENAME, SAL, MGR, from EMP where MGR among (7902, 7566, 7788);
  4. select ENAME, SAL, MGR, from EMP where MGR into (7902, 7566, 7788);
  5. none of these
Question 14 Multiple Choice (Single Answer)

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\\' ).... ]

  1. select ENAME, SAL, GRADE from EMP where EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
  2. select ENAME, SAL, GRADE from EMP,SALGRADE where EMP.SAL between SALGRADE.LOSAL and SALARY.HISAL ;
  3. select ENAME, SAL, GRADE from EMP,SALGRADE where EMP.SAL among SALGRADE.LOSAL and SALGRADE.HISAL;
  4. select ENAME, SAL, GRADE from EMP,SALGRADE where EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
  5. none of these
Question 15 Multiple Choice (Single Answer)

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\\' ).... ]

  1. 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;
  2. select ENAME, JOB, DNAME, SAL, GRADE from EMP, DEPT, SALGRADE where DEPT.DEPTNO=EMP.DEPTNO and EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
  3. select EMP.ENAME, EMP.JOB, DEPT.DNAME, EMP.SAL, SALGRADE.GRADE from EMP, DEPT, SALGRADE where EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;
  4. options 1 and 2
  5. none of these
← Back to Quizzes