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

Multiple choice

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

It will fetch the required data.

Multiple choice

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

Options 1 and 2 both will fetch the correct data.

Multiple choice

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

The condition is appropriately emphasized, so it will give the right answer.

Multiple choice

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

Here we are dealing with three numbers(7902, 7566, 7788), so 'in' keyword is appropriate for this.

Multiple choice

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

LOSAL-HISAL (i.e. low salary and high salary ) is a range for a particular grade, for example (LOSAL=2000 HISAL=4000 then GRADE='A' ). So, 'between' clause is used to join both the tables.

Multiple choice

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

Option 1 and option 2 both will fetch the correct data

Multiple choice

Primary key and foreign key are the terms used in ________.

  1. programming

  2. database

  3. gaming

  4. presentation

  5. accounts
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Primary key and foreign key are used in database. Primary Key A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they are frequently defined on an identity column. Foreign Key A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.