Computer Knowledge

Database and SQL

4,213 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice

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
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

At the time of using group by we use having instead of where.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

It will fetch the correct data

Multiple choice

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
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

It's a query of self joining.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Student may be confused to see option 1 and 2,  the two different process to get the correct data,.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Option 1 and 2 both will fetch the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

It will fetch the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

It will fetch the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

To get maximum of average salary from all department we use max(avg(SAL))

Multiple choice

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
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

It will fetch the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

It will fetch the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

It will fetch the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In the inner loop the query will fetch the employee number of King and matches with the manager number of all employees in the outer loop..so It will fetch the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

It will give the correct data.

Multiple choice

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
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

It will fetch the correct data.