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

Multiple choice

Write the syntax to delete a login.

  1. DELETE LOGIN login_name

  2. Truncate LOGIN login_name

  3. DROP LOGIN login_name

  4. DISABLE LOGIN login_name

  5. A login cannot be deleted but can only be disabled.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

It is the actual syntax to drop a login.

Multiple choice

Write the correct syntax to create new login on the SQL server.

  1. CREATE LOGIN login_id USE password

  2. CREATE LOGIN login_id WITH USE password

  3. CREATE LOGIN login_id WITH PASSWORD password

  4. USE LOGIN login_id WITH PASSWORD password

  5. UPDATE LOGIN login_id WITH PASSWORD password
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

This is the correct syntax to create new login on SQL server.

Multiple choice

The end of SQL command is

  1. System.exit()
    • Q

  3. an ‘enter key’ marker

  4. a semicolon(;)

  5. End
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The sqlite3 program looks for a semicolon to know when your SQL command is complete.