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

Write a query to display students details whose STUDENT_ID is 119 OR COURSE_ID is 301.

  1. SELECT * FROM STUDENTS WHERE STUDENT_ID = 119 OR COURSE_ID = 301

  2. SELECT ALL FROM STUDENTS WHERE STUDENT_ID = 119 OR COURSE_ID = 301

  3. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE STUDENT_ID = 119 OR COURSE_ID = 301

  4. SELECT ALL STUDENT_ID FROM STUDENTS WHERE STUDENT_ID = 119 OR COURSE_ID = 301

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

Yes, this is the correct syntax.

Multiple choice

Write a query to choose all the students who got the highest marks in the exam.

  1. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MAX(MARKS)

  2. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS = ( SELECT MAX(MARKS) FROM STUDENTS )

  3. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS = MAX(MARKS)

  4. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS = MAX

  5. All of the above

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

Yes, it is true.

Multiple choice

Which of the following statements is TRUE about a subquery?

  1. Subqueries are mainly of two types - nested and correlated.

  2. A subquery usually have an outer query and an inner query.

  3. A subquery can be written using either a single or multiple tables.

  4. Generally, the innermost subquery is executed first and based on its results, outer queries will execute.

  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

Write a query to display students details whose marks are greater than the student whose STUDENT_ID is 123.

  1. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS > 123

  2. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS > ( STUDENT_ID = 123 )

  3. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS > 123 ( SELECT MARKS FROM STUDENTS WHERE STUDENT_ID = 123 )

  4. SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS > ( SELECT MARKS FROM STUDENTS WHERE STUDENT_ID = 123 )

  5. All of the above

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

Yes, it returns the desired result.

Multiple choice

SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS > ( SELECT MARKS FROM STUDENTS WHERE STUDENT_ID = 123 )

What will be the output of the above query? Assume STUDENT_ID to be a primary key column.

  1. It always returns only one row with STUDENT_ID 123.

  2. It does not return any row as MARKS cannot be compared with STUDENT_ID.

  3. It returns n number of rows, where n is the number of students whose marks are greater than that of student whose STUDENT_ID is 123.

  4. It returns all the rows in the query.

  5. There is syntax error in the query.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Yes, it returns n number of rows, where n is the number of students whose marks are greater than that of student whose STUDENT_ID is 123. So, this option is correct.

Multiple choice

SELECT STUDENT_ID, MARKS FROM STUDENTS WHERE MARKS > 80

What will be the output of the above query?

  1. There is a syntax error in this query.

  2. Only one row will be returned irrespective of the data.

  3. It always returns zero rows.

  4. It returns n number of rows, where n is the number of students whose marks are greater than 80.

  5. It returns all the rows in the table.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Yes, it is true. n varies with the data in the table.

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.