Consider a database name "db_name" whose attributes are intern_id (primary key), subject, subject_value.
Intern_id = {1, 2, 3, 4, 5, 6}
Subject = {sql, oop, sql, oop, c, c++}
Subject_value = {0, 0, 1, 1, 2, 2, 3, 3}
If these are one to one relation then what will be the output of the following MySQL statement?

SELECT intern_id
FROM db_name
WHERE subject IN (SELECT subject FROM db_name WHERE subject_value IN (3, 2));

Solution (By Examveda Team)

This question is about understanding how to use SQL queries to retrieve data from a database. Let's break down the code step by step:
1. Database Structure
Imagine a table named "db_name" which stores information about interns. This table has three columns:
* intern_id: A unique identifier for each intern (primary key). * subject: The subject the intern is studying (like SQL, OOP, etc.). * subject_value: A numerical value associated with the subject.
2. Understanding the Query
The provided SQL query is designed to find intern IDs based on specific subject values.
* SELECT intern_id: This part tells the database to retrieve the intern_id column.
* FROM db_name: This indicates that the data should be taken from the "db_name" table.
* WHERE subject IN (SELECT subject FROM db_name WHERE subject_value IN (3, 2)): This is the core of the query. It's a nested query: * Inner Query: `SELECT subject FROM db_name WHERE subject_value IN (3, 2)` - This query first selects the "subject" column from "db_name" where the subject_value is either 3 or 2. * Outer Query: `SELECT intern_id FROM db_name WHERE subject IN (...)` - This query then selects the intern_id from "db_name" where the subject matches the subjects found in the inner query. 3. Applying the Data
Let's apply the data given in the question to the query:
* Inner Query: * The inner query finds subjects with subject_value of 3 or 2, which are "c" and "c++".
* Outer Query: * The outer query then searches for intern IDs where the subject is "c" or "c++", resulting in intern_id 5 and intern_id 6. 4. The Answer
The output of the SQL query will be {5, 6}. Therefore, the correct answer is Option A.