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

Which of the following statements is TRUE about a correlated subquery?

  1. Correlated subquery appears to be a nested subquery, but is little different.

  2. Correlated subquery is the one that is executed after the outer query.

  3. Execution of correlated subqueries is different from that of normal subqueries.

  4. Only 1 and 3 are correct.

  5. All the three statements are correct.
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, all the three statements are correct.

Multiple choice

Which of the following statements is TRUE about nested subquery?

  1. Nested subquery is one type of subquery whereas other one is a correlated subquery.

  2. A subquery is nested when you are having a subquery in the WHERE or HAVING clause of another subquery.

  3. For a nested subquery, execution starts from the innermost query and is based on its result, then the next inner query will execute and finally outer query will execute.

  4. Oracle allows upto 255 levels of subqueries.

  5. All of the above

Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, all of the above statements are correct.

Multiple choice

Which of the following guidelines is correct for writing a subquery?

  1. Subquery must be enclosed in a paranthesis.

  2. Subquery must be placed on the right side of the comparison operator.

  3. When a subquery returns a null value, the outer query should not return any row.

  4. Indentation is very helpful in understanding a subquery.

  5. All of the above
Reveal answer Fill a bubble to check yourself
E Correct answer
Explanation

Yes, all are correct.

Multiple choice

Write a query to choose all the courses in which the least marks obtained is greater than the highest marks obtained in the COURSE_ID 201.

  1. SELECT COURSE_ID, MIN(MARKS) FROM STUDENTS GROUP BY COURSE_ID HAVING MIN(MARKS) > ( SELECT MAX(MARKS) FROM STUDENTS WHERE COURSE_ID = 201 )

  2. SELECT COURSE_ID, MIN(MARKS) FROM STUDENTS WHERE MIN(MARKS) > ( SELECT MAX(MARKS) FROM STUDENTS WHERE COURSE_ID = 201 )

  3. SELECT COURSE_ID, MIN(MARKS) FROM STUDENTS GROUP BY COURSE_ID HAVING MIN(MARKS) > MAX(MARKS) AND COURSE_ID = 201

  4. SELECT COURSE_ID, MIN(MARKS) FROM STUDENTS GROUP BY COURSE_ID WHERE MIN(MARKS) > ( SELECT MAX(MARKS) FROM STUDENTS WHERE COURSE_ID = 201 )

  5. All of the above

Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

This is the correct syntax of the query in which the least marks obtained is greater than the highest marks obtained in the COURSE_ID 201.

Multiple choice

Write a query to get the details of all the students who are all enrolled in the same course as the student with STUDENT_ID 123.

  1. SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = 123)

  2. SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = ( SELECT COURSE_ID FROM STUDENT WHERE STUDENT_ID = 123)

  3. SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = ( SELECT STUDENT_ID FROM STUDENT WHERE STUDENT_ID = 123)

  4. SELECT STUDENT_ID FROM STUDENT WHERE STUDENT_ID = ( SELECT COURSE_ID FROM STUDENT WHERE COURSE_ID = 123)

  5. None of the above

Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Yes, this is the correct syntax to get the details of all the students who are all enrolled in the same course as the student with STUDENT_ID 123.

Multiple choice

Consider the following tables structucres:

STUDENT(STUDENT_ID, STUDENT_NAME, COURSE_ID, JOIN_DATE) RESULTS(STUDENT_ID, SUB1_MARKS, SUB2_MARKS, SUB3_MARKS, SUB4_MARKS) COURSE(COURSE_ID, COURSE_NAME)

Write a query to get the results of all the students who are all enrolled in the same course as the student with STUDENT_ID 123.

  1. SELECT * FROM RESULTS WHERE STUDENT_ID IN ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_NAME = ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = 123))

  2. SELECT * FROM RESULTS WHERE STUDENT_ID IN ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = 123))

  3. SELECT * FROM RESULTS WHERE STUDENT_ID IN ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = ( SELECT COURSE_ID FROM STUDENT WHERE STUDENT_ID = 123))

  4. SELECT * FROM RESULTS WHERE STUDENT_ID IN ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = ( SELECT STUDENT_ID FROM STUDENT WHERE COURSE_ID = 123))

  5. None of the above

Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Yes, this is the correct syntax.

Multiple choice

Consider the following table structures:

STUDENTS (STUDENT_ID, STUDENT_NAME, COURSE_ID, JOIN_DATE) RESULTS(STUDENT_ID, SUB1_MARKS, SUB2_MARKS, SUB3_MARKS, SUB4_MARKS) COURSE(COURSE_ID, COURSE_NAME)

Find out the details of all the students who have appeared for more than 4 papers of the enrolled course.

  1. SELECT * FROM STUDENTS WHERE COUNT < ( SELECT COUNT(*) FROM RESULT WHERE STUDENT_ID = STUDENT_ID)

  2. SELECT * FROM STUDENTS S WHERE 4 < ( SELECT COURSE_ID FROM RESULT R WHERE STUDENT_ID = R.STUDENT_ID)

  3. SELECT * FROM STUDENTS S WHERE COUNT < ( SELECT COUNT(*) FROM RESULT WHERE STUDENT_ID = STUDENT_ID)

  4. SELECT * FROM STUDENTS S WHERE 4 < ( SELECT COUNT(*) FROM RESULT R WHERE R.STUDENT_ID = S.STUDENT_ID)

  5. None of the above

Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Yes, this is the correct syntax.

Multiple choice

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

We use group by for grouping with respect to any attribute , it will fetch the correct data.

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.