Home › Quizzes › SQL Aggregates and Subqueries

SQL Aggregates and Subqueries

Practice SQL queries using GROUP BY, HAVING, aggregate functions (AVG, MAX, MIN, COUNT), and subqueries with the EMP table schema.

15 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

Select a query to display the department wise average salary.

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

  1. select avg(SAL) from EMP grouped by DEPTNO;
  2. select avg(SAL) from EMP group by DEPTNO;
  3. select avg(SAL) from EMP grouping with DEPTNO ;
  4. select avge(SAL) from EMP group by DEPTNO;
  5. none of these
Question 2 Multiple Choice (Single Answer)

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

  1. select avg(SAL), DEPTNO, from EMP group by DEPTNO having avg(SAL)>2000;
  2. select avg(SAL), DEPTNO, from EMP where group by DEPTNO having avg(SAL)>2000;
  3. select avg(SAL), DEPTNO, from EMP group by DEPTNO where avg(SAL)>2000;
  4. select avg(SAL), DEPTNO, from EMP group by DEPTNO having avg(SAL)>2000
  5. none of these
Question 3 Multiple Choice (Single Answer)

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

  1. select max(SAL), DEPTNO from EMP group by DEPTNO having max(SAL)>2600;
  2. select max(SAL), DEPTNO from EMP group by DEPTNO having max(SAL)>2900;
  3. select max(SAL), DEPTNO from EMP group by DEPTNO have max(SAL)>2900;
  4. options 1 and 2
  5. none of these
Question 4 Multiple Choice (Single Answer)

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

  1. select A.ENAME, A.EMPNO || 'works for' || B.ENAME,B.EMPNO where A.MGR=B.EMPNO;
  2. select A.ENAME, A.EMPNO || 'works for' || B.ENAME,B.EMPNO where B.MGR=A.EMPNO;
  3. select A.ENAME, A.EMPNO || 'works for' || B.ENAME,B.EMPNO where A.EMPNO=B.EMPNO;
  4. options 2 and 3
  5. none of these
Question 5 Multiple Choice (Single Answer)

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

  1. select A.ENAME, B.EMPNO from EMP A, EMP B where A.MGR=B.EMPNO;
  2. select ENAME, MGR from EMP;
  3. select A.ENAME, B.EMPNO from EMP A, EMP B where B.MGR=A.EMPNO;
  4. options 1 and 2
  5. none of these
Question 6 Multiple Choice (Single Answer)

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

  1. select ENAME from EMP where JOB = (select JOB from EMP where EMPNO=7369;
  2. select ENAME from EMP where JOB in (select JOB from EMP where EMPNO=7369);
  3. select ENAME from EMP where JOB is (select JOB from EMP where EMPNO=7369;
  4. select ENAME from EMP where JOB = (select JOB from EMP where EMPNO=7399;
  5. options 1 and 2
Question 7 Multiple Choice (Single Answer)

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

  1. select count(ENAME), JOB from EMP group by JOB;
  2. select count(ENAME), JOB from EMP group by DEPTNO;
  3. select count(ENAME), JOB from EMP group by JOB;
  4. select counter(ENAME), JOB from EMP group by MGR;
  5. none of these
Question 8 Multiple Choice (Single Answer)

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

  1. select ENAME from EMP where SAL>(select EMPNO from EMP where EMPNO=7566);
  2. select ENAME from EMP where SAL=(select SAL from EMP where EMPNO=7566);
  3. select ENAME from EMP where SAL>(select SAL from EMP where EMPNO=7566);
  4. select EMPNAME from EMP where SAL>(select SAL from EMP where EMPNO=7566);
  5. none of these
Question 9 Multiple Choice (Single Answer)

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

  1. select ENAME, JOB, SAL from EMP where SAL = (select low(SAL) from EMP);
  2. select ENAME, JOB, SAL from EMP where SAL equal (select min(SAL) from EMP ;
  3. select ENAME, JOB, SAL from EMP where SAL = (select min(SAL) from EMP);
  4. select ENAME, JOB, SAL from EMP where SAL == (select min(SAL) from EMP);
  5. none of these
Question 10 Multiple Choice (Single Answer)

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

  1. select max(avg(SAL)) from EMP group by DEPTNO;
  2. select max(SAL) and avg(SAL) from EMP group by DEPTNO;
  3. select max(avg(SAL)) from EMP grouped by DEPTNO ;
  4. select best(avg(SAL)) from EMP group by DEPTNO;
  5. none of these
Question 11 Multiple Choice (Single Answer)

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

  1. select ENAME, EMPNO from EMP where SAL > (select avg(SAL) from EMP);
  2. select ENAME, EMPNO from EMP where SAL = (select avg(SAL) from EMP );
  3. select ENAME, EMPNO from EMP where SAL > (select avge(SAL) from EMP );
  4. select ENAME, EMPNUM from EMP where SAL > (select avg(SAL) from EMP );
  5. none of these
Question 12 Multiple Choice (Single Answer)

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

  1. select ENAME, HIREDATE from EMP where DEPTNO = (select DEPTNO from EMP where ENAME='BLACK');
  2. select ENAME, HIREDATE from EMP where DEPTNO = (select DEPTNO from EMP where ENAME='BLAKE' ;
  3. select ENAME, HIREDATE from EMP where DEPTNO = (select EMPNO from EMP where ENAME='BLAKE');
  4. select ENAME, HIREDATE from EMP where DEPTNO = (select DEPTNO from EMP where ENAME=BLAKE);
  5. none of these
Question 13 Multiple Choice (Single Answer)

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

  1. select ENAME, EMPNO from EMP where DEPTNO = (select DEPTNO from EMP where ENAME like 'T%');
  2. select ENAME, EMPNO from EMP where DEPTNO = (select DEPTNO from EMP where ENAME like '%T%');
  3. select ENAME, EMPNO from EMP where DEPTNO in (select DEPTNO from EMP where ENAME like '%T%');
  4. select ENAME, EMPNO from EMP where DEPTNO in (select DEPTNO from EMP where ENAME like '%T');
  5. none of these
Question 14 Multiple Choice (Single Answer)

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

  1. select ENAME, SAL from EMP where MGR = (select MGR from EMP where ENAME='KING');
  2. select ENAME, SAL from EMP where MGR = (select EMPNO from EMP where ENAME='KNG');
  3. select ENAME, SAL from EMP where MGR = (select EMPNO from EMP where ENAME='KING');
  4. select ENAME, SAL from EMP where EMPNO = (select EMPNO from EMP where ENAME='KING');
  5. none of these
Question 15 Multiple Choice (Single Answer)

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

  1. select ENAME, DEPTNO, JOB from EMP where DEPTNO = (select DEPTNO from DEPT where LOC='DALLAS');
  2. select ENAME, DEPTNO, JOB from EMP where DEPTNO = (select DEPTNO from EMP where LOC='DALLAS');
  3. select ENAME, DEPTNO, JOB from EMP where DEPTNO == (select DEPTNO from DEPT where LOC='DALLAS');
  4. select ENAME, DEPTNO, JOB from EMP where DEPTNO = (select DEPTNO from DEPT where LOC='DALAS');
  5. options 1 and 4
← Back to Quizzes