Home › Quizzes › Oracle SQL Queries and Subqueries

Oracle SQL Queries and Subqueries

Practice writing and understanding Oracle SQL queries including aggregate functions, subqueries, nested queries, and correlated subqueries

20 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

Find the odd one out of the following Oracle functions.
A. avg()
B. sqrt()
C. sum()
D. max()
E. count()

  1. avg()
  2. sqrt()
  3. sum()
  4. max()
  5. count()
Question 2 Multiple Choice (Single Answer)

Find the odd one out of the following SQL operations in ORACLE.

  1. date + number
  2. date - number
  3. date - date
  4. date + (number/24)
  5. none of the above
Question 3 Multiple Choice (Single Answer)

Predict the output of the following query.
Select INITCAP('Delhi is the capital city of INDIA') from DUAL

  1. Delhi is the capital city of INDIA
  2. Delhi is the capital city of India
  3. Delhi Is The Capital City Of INDIA
  4. Delhi Is The Capital City Of India
  5. Delhi Is The Capital City of India
Question 4 Multiple Choice (Single Answer)

SELECT STUDENT_ID, MARKS
FROM STUDENTS
WHERE STUDENT_ID = 119

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

  1. There is a syntax error in the query.
  2. It may return one row if the STUDENT_ID 119 is present in the table else it will return zero rows.
  3. It always returns zero rows.
  4. It returns n number of rows, where n is the number of students whose STUDENT_ID is 119.
  5. It returns all the rows in the table irrespective of the data.
Question 5 Multiple Choice (Single Answer)

Which of the following statements will create a new user?

  1. CREATE USER kumar
  2. CREATE OR REPLACE USER kumar
  3. CREATE USER kumar IDENTIFIED BY indian
  4. CREATE NEW USER kumar IDENTIFIED BY indian
  5. CREATE OR REPLACE USER kumar IDENTIFIED BY indian
Question 6 Multiple Choice (Single Answer)

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
Question 7 Multiple Choice (Single Answer)

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
Question 8 Multiple Choice (Single Answer)

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
Question 9 Multiple Choice (Single Answer)

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
Question 10 Multiple Choice (Single Answer)

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.
Question 11 Multiple Choice (Single Answer)

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.
Question 12 Multiple Choice (Single Answer)

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.
Question 13 Multiple Choice (Single Answer)

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

  1. SELECT STUDENT_ID, MARKS
    FROM STUDENTS
    WHERE MIN(MARKS)
  2. SELECT STUDENT_ID, MARKS
    FROM STUDENTS
    WHERE MARKS =
    (
    SELECT MIN(MARKS)
    FROM STUDENTS
    )
  3. SELECT STUDENT_ID, MARKS
    FROM STUDENTS
    WHERE MARKS = MIN(MARKS)
  4. SELECT STUDENT_ID, MARKS
    FROM STUDENTS
    WHERE MARKS = MIN
  5. All of the above are correct.
Question 14 Multiple Choice (Single Answer)

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
Question 15 Multiple Choice (Single Answer)

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
Question 16 Multiple Choice (Single Answer)

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
Question 17 Multiple Choice (Single Answer)

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
Question 18 Multiple Choice (Single Answer)

Write a query to display students details whose marks are greater than 80.
Note: Choose the most appropriate answer from the given choices.

  1. SELECT *
    FROM STUDENTS
    WHERE MARKS > 80
  2. SELECT ALL
    FROM STUDENTS
    WHERE MARKS > 80
  3. SELECT STUDENT_ID, MARKS
    FROM STUDENTS
    WHERE MARKS > 80
  4. SELECT ALL STUDENT_ID
    FROM STUDENTS
    WHERE MARKS > 80
  5. All of the above
Question 19 Multiple Choice (Single Answer)

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
Question 20 Multiple Choice (Single Answer)

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
← Back to Quizzes