Computer Knowledge

Database and SQL

3,929 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 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.

Multiple choice

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

We use max for oldest and min for newest.

Multiple choice

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

We use max keyword to get the highest value and we use min keyword to get the lowest value.