MySQL Multiple Choice Questions on “Condition Types”.
1. Find the error in the following MySQL statement?
SELECT cust_id, fed_id FROM customer WHERE cust_id = ’I’ AND fed_id BETWEEN 5000-00-000 AND 9999-999-000;
a) Yes
b) No
c) Range too high to compare
d) None of the mentioned
Answer: b
Clarification: In the following query, there will be no error in executing as 5000-00-000 AND 9999-999-000 is “string ranges”.
2. What will be the output of the following MySQL statement?
SELECT account_id, product_id, cust_id FROM account WHERE product_id IN (‘sav’, ‘chd’, ‘mm’);
a) Only those values are selected whose product_id is either ‘sav’, ‘chd’, ‘mm’
b) Only those values are selected whose product_id is either ‘sav’’
c) Only those values are selected whose product_id is either ‘sav’, ‘chd’
d) All of the mentioned
Answer: a
Clarification: “IN” operator is used to combine the expressions.
3. What will be the output of the following MySQL statement?
SELECT account_id, product_id, cust_id FROM account WHERE product_id NOT IN (‘sav’, ‘chd’, ‘mm’);
a) Only those values are selected whose product_id does not belong to either ‘sav’, ‘chd’, ‘mm’
b) Only those values are selected whose product_id does not belong to either ‘sav’’
c) Only those values are selected whose product_id does not belong to either ‘sav’, ‘chd’
d) All of the mentioned
Answer: a
Clarification: “NOT IN” worked opposite of “IN” operator.
4. Consider a database name “” whose attributes are intern_id (primary key), subject.
Intern_id = {1, 2, 3, 4, 5, 6}
Subject = {sql, oop, sql, oop, c, c++}
If these are one to one relation then what will be the output of the following MySQL statement?
SELECT intern_id FROM WHERE subject IN (c, c++);
a) {5, 6}
b) {1, 2, 3}
c) {3, 4}
d) None of the mentioned
Answer: a
Clarification: Only those value whose subjects are c and c++.
5. Consider a database name “” whose attributes are intern_id (primary key), subject.
Intern_id= {1, 2, 3, 4, 5, 6}
Subject= {sql, oop, sql, oop, c, c++}
If these are one to one relation then what will be the output of the following MySQL statement?
SELECT intern_id FROM WHERE subject NOT IN (c, c++);
a) {5, 6}
b) {1, 2, 3}
c) {3, 4}
d) None of the mentioned
Answer: d
Clarification: Only those value whose subjects are not c and c++ therefore answer is {1, 2, 3, 4}.
6. Consider a database 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 WHERE subject IN (SELECT subject FROM WHERE subject_value = 3);
a) {5, 6}
b) {1, 2, 3}
c) {3, 4}
d) None of the mentioned
Answer: a
Clarification: Only those value will be selected whose subjects are c and c++. In the sub query subject_value=3 which belongs to subject {c, c++} after that this subject belong to intern_id {5, 6).
7. Consider a database 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 WHERE subject IN (SELECT subject FROM WHERE subject_value IN (3, 2));
a) {5, 6}
b) {1, 2, 3}
c) {3, 4}
d) {1, 2, 3, 4, 5, 6}
Answer: d
Clarification: Only those value will be selected whose subjects are c, c++, sql, oop. In the sub query subject_value=3 OR 2 which belongs to subject {c, c++, sql, oop} after that this subject belong to intern_id (1, 2, 3, 4, 5, 6).
8. Consider a database name “” whose attributes are intern_id (primary key), subject.
Intern_id = {1, 2, 3, 4, 5, 6}
Subject = {sql, oop, sql, oop, c, c++}
If these are one to one relation then what will be the output of the following MySQL statement?
SELECT intern_id FROM WHERE subject NOT IN (c, c++, oop);
a) {5, 6}
b) {1, 2, 3}
c) {3, 4}
d) {1, 4}
Answer: d
Clarification: Only those value whose subjects are not {c, c++, oop} therefore answer is {1, 4}.
9. Consider a database name “” whose attributes are intern_id (primary key), subject.
Intern_id = {1, 2, 3, 4, 5, 6}
Subject = {sql, oop, sql, oop, c, c++}
If these are one to one relation then what will be the output of the following MySQL statement?
SELECT intern_id FROM WHERE subject IN (c, c++, oop);
a) {5, 6}
b) {1, 2, 3}
c) {3, 4}
d) {2, 4, 5, 6}
Answer: d
Clarification: Only those value whose subjects are {c, c++, oop} therefore answer is {2, 4, 5, 6}.
10. Consider a database name “” whose attributes are intern_id (primary key), subject.
Intern_id = {1, 2, 3, 4, 5, 6}
Subject = {sql, oop, sql, oop, c, c++}
If these are one to one relation then what will be the output of the following MySQL statement?
SELECT intern_id FROM WHERE subject IN (c, c++, oop, SQL);
a) {5, 6}
b) {1, 2, 3}
c) {3, 4}
d) Empty set
Answer: d