SQL MCQ Questions and Answers 2

Q 21. Table Employee has 10 records. It has a non-NULL SALARY column which is also UNIQUE. The SQL statement SELECT COUNT(*) FROM Employee WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEE); prints

A. 0

B. 5

C. 9

D. 11

Show Answer

Answer:-C. 9

Explanation

ANY compares a value with each of the values in a list or results from a query and evaluates to true if the result of an inner query contains at least one row. ANY must be preceded by comparison operators(=, >, <, <=, >=, <>). Employee table has 10 records and each value in non-NULL SALARY column is unique i.e different. So, in that 10 records one of the record will be minimum which cannot be greater than any nine value of the salary column. Hence the condition WHERE SALARY > ANY (SELECT SALARY FROM employee) will be true nine times. So, the COUNT(*) outputs 9.

Q 22. The SQL statement SELECT SUBSTR(‘abcdefghij’, INSTR(‘123321234’, ‘2’, 3, 2), 2) FROM DUAL; prints

A. gh

B. 23

C. bc

D. ab

Show Answer

Answer:-A. gh

Explanation

Another form of INSTR function used in ORACLE is: INSTR (str, pattern, [starting position, [nth location]]): Finds the starting location of the nth occurrence of pattern beginning in the starting position-th position in string str. Example: – SELECT INSTR(‘kolkata’, ‘a’, 1, 2) FROM DUAL; will output 7 as the starting location of 2nd occurrence of pattern ‘a’ from starting position 1 in string ‘kolkata’ is 7. In the above query INSTR(‘123321234’, ‘2’, 3, 2) will give the output 7 as the starting location of 2nd occurrence of pattern ‘2’ from starting location three in string ‘123321234’ is 7. Now SUBSTR function becomes SUBSTR(‘abcdefghij

Q 23. The SQL statement SELECT ROUND(45.926, -1) FROM DUAL;

A. is illegal

B. prints garbage

C. prints 045.926

D. prints 50

Show Answer

Answer:-D. prints 50

Explanation

The ROUND function in SQL is used to round a number to a specified precision. The syntax is: ROUND (expression, [decimal place]) where [decimal place] indicates the number of decimal points returned. A negative number means the rounding will occur to the digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens.

Q 24. Which of the following must be enclosed in double quotes?

A. Dates

B. Column Alias

C. Strings

D. All of the above

Show Answer

Answer:-D. All of the above

Explanation

In SQL, all of the above options must be enclosed in double quotes under specific circumstances. Dates: When using a date literal in SQL, it should be enclosed in single quotes, like ‘2023-10-20’. Double quotes are typically not used for dates. Column Alias: If you want to give a column an alias (a name that represents the column in the result set), you can enclose the alias in double quotes if it contains spaces or special characters. For example, if you want to alias a column as “Total Sales,” you would write it as “Total Sales”. However, it’s not necessary to use double quotes for aliases without spaces or special characters. Strings: When working with string values that contain spaces, special characters, or reserved keywords, you should enclose the string in single quotes (e.g., ‘John Doe’). Double quotes are sometimes used to enclose identifiers (like table or column names) but not for string values. So, the correct answer is Option D, as all of the mentioned elements may need double quotes, but it depends on the specific context and requirements in SQL.

25. Which of the following command makes the updates performed by the transaction permanent in the database?

A. ROLLBACK

B. COMMIT

C. TRUNCATE

D. DELETE

Show Answer

Answer:-B. COMMIT

Explanation

In SQL, the command that makes the updates performed by a transaction permanent in the database is the COMMIT command. When you issue the COMMIT command, it finalizes all the changes made within the current transaction, ensuring that they are saved to the database and cannot be rolled back. So, Option B, the COMMIT command, is the correct choice for making transaction updates permanent in the database.

Q 26. Which command undo all the updates performed by the SQL in the transaction?

A. ROLLBACK

B. COMMIT

C. TRUNCATE

D. DELETE

Show Answer

Answer:-A. ROLLBACK

Explanation

In SQL, the command that undoes all the updates performed by the SQL statements within a transaction is the ROLLBACK command. When you issue the ROLLBACK command, it cancels all the changes made during the transaction, effectively reverting the database to its previous state. So, Option A, the ROLLBACK command, is the correct choice for undoing all updates performed by SQL statements in a transaction.

Q 27. Find all the cities whose humidity is 89, Table Name weather

A. SELECT city WHERE humidity = 89;

B. SELECT humidity = 89 FROM weather;

C. SELECT city FROM weather WHERE humidity = 89;

D. SELECT city FROM weather;

Show Answer

Answer:-C. SELECT city FROM weather WHERE humidity = 89;

Explanation

To retrieve all the cities whose humidity is 89 from the “weather” table, you should use the SELECT city FROM weather WHERE humidity = 89; query. Option B is the correct choice because it specifically selects the “city” column from the “weather” table where the “humidity” is equal to 89. This query will return the desired result, listing the cities meeting the specified humidity condition.

Q 28. Find the temperature in increasing order of all cities, Table Name ‘weather’

A. SELECT city FROM weather ORDER BY temperature;

B. SELECT city, temperature FROM weather;

C. SELECT city, temperature FROM weather ORDER BY temperature;

D. SELECT city, temperature FROM weather ORDER BY city;

Show Answer

Answer:-C. SELECT city, temperature FROM weather ORDER BY temperature;

Explanation

To retrieve the temperature of all cities in increasing order, you should use the SQL query SELECT city, temperature FROM weather ORDER BY temperature; (Option C). This query selects both the “city” and “temperature” columns from the “weather” table and arranges the result set in ascending order based on the “temperature” column. As a result, you’ll get the temperature values for all cities listed from the lowest to the highest temperature.

Q 29. What is the meaning of LIKE ‘%0%0%’

A. Feature begins with two 0’s

B. Feature ends with two 0’s

C. Feature has more than two 0’s

D. Feature has two 0’s in it, at any position

Show Answer

Answer:-D. Feature has two 0’s in it, at any position

Explanation

The SQL pattern LIKE ‘%0%0%’ means that the “Feature” should have two 0’s in it, at any position within the text. The ‘%’ wildcard matches any sequence of characters, so the pattern can match any string as long as it contains two consecutive 0’s, regardless of their position within the text. So, Option D is the correct choice for the meaning of this pattern.

Q 30. Find the names of these cities with temperature and condition whose condition is neither sunny nor cloudy

A. SELECT city, temperature, condition FROM weather WHERE condition NOT IN (‘sunny’, ‘cloudy’);

B. SELECT city, temperature, condition FROM weather WHERE condition NOT BETWEEN (‘sunny’, ‘cloudy’);

C. SELECT city, temperature, condition FROM weather WHERE condition IN (‘sunny’, ‘cloudy’);

D. SELECT city, temperature, condition FROM weather WHERE condition BETWEEN (‘sunny’, ‘cloudy’);

Show Answer

Answer:-A. SELECT city, temperature, condition FROM weather WHERE condition NOT IN (‘sunny’, ‘cloudy’);

Explanation

To retrieve the names of cities with their respective temperature and condition where the condition is neither “sunny” nor “cloudy,” you should use the SQL query SELECT city, temperature, condition FROM weather WHERE condition NOT IN (‘sunny’, ‘cloudy’); (Option A). This query selects the desired columns from the “weather” table and filters the rows where the “condition” is not “sunny” or “cloudy.” This way, you’ll get the information for cities with conditions other than “sunny” or “cloudy.”

Q 31. Find the name of those cities with temperature and condition whose condition is either sunny or cloudy but temperature must be greater than 70oF.

A. SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ AND condition = ‘cloudy’ OR temperature > 70;

B. SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ OR condition = ‘cloudy’ OR temperature > 70;

C. SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ OR condition = ‘cloudy’ AND temperature > 70;

D. SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ AND condition = ‘cloudy’ AND temperature > 70;

Show Answer

Answer:-C. SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ OR condition = ‘cloudy’ AND temperature > 70;

Explanation

Option C correctly finds cities where the condition is either “sunny” or “cloudy” and the temperature exceeds 70°F. This results in a list of cities that meet both of these criteria.

Q 32. Find all the tuples having temperature greater than ‘Paris’.

A. SELECT * FROM weather WHERE temperature > (SELECT temperature FROM weather WHERE city = ‘Paris’)

B. SELECT * FROM weather WHERE temperature > (SELECT * FROM weather WHERE city = ‘Paris’)

C. SELECT * FROM weather WHERE temperature > (SELECT city FROM weather WHERE city = ‘Paris’)

D. SELECT * FROM weather WHERE temperature > ‘Paris’ temperature

Show Answer

Answer:-A. SELECT * FROM weather WHERE temperature > (SELECT temperature FROM weather WHERE city = ‘Paris’)

Explanation

To find all the tuples (rows) in the “weather” table where the temperature is greater than the temperature in Paris, you should use the SQL query SELECT * FROM weather WHERE temperature > (SELECT temperature FROM weather WHERE city = ‘Paris’) (Option A). This query correctly uses a subquery to first select the temperature in Paris and then compares it with the temperatures in other cities. If the temperature in a city is greater than that of Paris, the row is included in the result set. This is why Option A is the correct choice.

Q 33. Find all the cities with temperature, condition and humidity whose humidity is in the range of 63 to 79

A. SELECT * FROM weather WHERE humidity IN (63 to 79)

B. SELECT * FROM weather WHERE humidity NOT IN (63 AND 79)

C. SELECT * FROM weather WHERE humidity NOT BETWEEN 63 AND 79

D. SELECT * FROM weather WHERE humidity BETWEEN 63 AND 79

Show Answer

Answer:-D. SELECT * FROM weather WHERE humidity BETWEEN 63 AND 79

Explanation

To retrieve all the cities along with their temperature, condition, and humidity where the humidity falls within the range of 63 to 79, you should use the SQL query SELECT * FROM weather WHERE humidity BETWEEN 63 AND 79 (Option C). This query correctly uses the BETWEEN operator to specify the humidity range. It will include rows where the humidity value is equal to or greater than 63 and equal to or less than 79, thus giving you cities with humidity within the specified range. This is why Option C is the correct choice.

Q 34. Find the names of the countries whose condition is sunny.

A.SELECT country FROM location WHERE condition = ‘sunny’;

B. SELECT country FROM location WHERE city NOT IN (SELECT city FROM weather WHERE condition = ‘sunny’);

C. SELECT country FROM location WHERE city IN (SELECT city FROM weather WHERE condition = ‘sunny’);

D. SELECT country FROM location WHERE city UNION (SELECT city FROM weather WHERE condition = ‘sunny’);

Show Answer

Answer:-C. SELECT country FROM location WHERE city IN (SELECT city FROM weather WHERE condition = ‘sunny’);

Explanation

To find the names of countries where the condition is “sunny,” you should use the SQL query SELECT country FROM location WHERE city IN (SELECT city FROM weather WHERE condition = ‘sunny’); (Option B). This query correctly uses a subquery to first select the cities with a “sunny” condition from the “weather” table and then matches those cities to the “city” column in the “location” table to determine the corresponding countries. Option B is the appropriate choice for obtaining the desired result.

Q 35. Find the name of all cities with their temperature, humidity and countries.

A. SELECT city, temperature, humidity, country FROM location;

B. SELECT weather.city, temperature, humidity, country FROM weather, location;

C. SELECT weather.city, temperature, humidity, country FROM weather, location WHERE weather.city = location.city;

D. SELECT weather.city, temperature, humidity FROM weather SELECT country FROM location WHERE weather.city = location.city;

Show Answer

Answer:-C. SELECT weather.city, temperature, humidity, country FROM weather, location WHERE weather.city = location.city;

Explanation

To retrieve the names of all cities along with their temperature, humidity, and countries, you should use the SQL query SELECT weather.city, temperature, humidity, country FROM weather, location WHERE weather.city = location.city; (Option C). This query correctly joins the “weather” and “location” tables based on the “city” column, which is common between the two tables. This allows you to obtain the information for all cities, including their temperature, humidity, and corresponding countries. Option C is the appropriate choice for achieving this result.

Q 36. Find the name of cities with all entries whose temperature is in the range of 71 and 89

A. SELECT * FROM weather WHERE temperature NOT IN (71 to 89);

B. SELECT * FROM weather WHERE temperature NOT IN (71 and 89);

C. SELECT * FROM weather WHERE temperature BETWEEN 71 AND 89;

D. SELECT * FROM weather WHERE temperature NOT BETWEEN 71 to 89;

Show Answer

Answer:-C. SELECT * FROM weather WHERE temperature BETWEEN 71 AND 89;

Explanation

To retrieve the names of cities with entries where the temperature falls within the range of 71 to 89, you should use the SQL query SELECT * FROM weather WHERE temperature BETWEEN 71 AND 89; (Option D). This query utilizes the BETWEEN operator to specify the temperature range. It selects all entries in the “weather” table where the temperature is within the specified range. Option D is the correct choice for achieving this result.

Q 37. Which of the following query finds the names of the sailors who have reserved at least one boat?

A. SELECT DISTINCT s.sname FROM sailors s, reserves r WHERE s.sid = r.sid;

B. SELECT s.sname FROM sailors s, reserves r WHERE s.sid = r.sid;

C. SELECT DISTINCT s.sname FROM sailors, reserves WHERE s.sid = r.sid;

D. None of These

Show Answer

Answer:-A. SELECT DISTINCT s.sname FROM sailors s, reserves r WHERE s.sid = r.sid;

Explanation

To find the names of sailors who have reserved at least one boat, you should use the SQL query SELECT DISTINCT s.sname FROM sailors s, reserves r WHERE s.sid = r.sid; (Option A). This query joins the “sailors” and “reserves” tables on the “sid” column and selects the distinct “sname” (sailor names) from the “sailors” table. The use of DISTINCT ensures that you only get unique sailor names. Option A is the correct choice for achieving this result.

Q 38. Which of the following query finds colors of boats reserved by “Dustin”?

A. SELECT DISTINCT b.color FROM boats b, sailors s WHERE s.sname = ‘Dustin’ AND s.sid = b.sid

B. SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = ‘Dustin’ AND s.sid = r.sid AND r.bid = b.bid;

C. SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = ‘Dustin’ AND s.sid = r.sid

D. SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = ‘Dustin’ AND r.bid = b.bid

Show Answer

Answer:-B. SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = ‘Dustin’ AND s.sid = r.sid AND r.bid = b.bid;

Explanation

To find the colors of boats reserved by “Dustin,” you should use the SQL query SELECT DISTINCT b.color FROM boats b, reserves r, sailors s WHERE s.sname = ‘Dustin’ AND s.sid = r.sid AND r.bid = b.bid; (Option B). This query joins the “boats,” “reserves,” and “sailors” tables to find the colors of boats reserved by “Dustin.” It ensures that the sailor’s name is “Dustin” (s.sname = ‘Dustin’) and matches the sailor’s ID to the reservation’s sailor ID (s.sid = r.sid) and the reservation’s boat ID to the boat’s ID (r.bid = b.bid). The use of DISTINCT ensures you get unique boat colors. Option B is the correct choice for obtaining this information.

Q 39. What does the following query find? (SELECT DISTINCT r.sid FROM boats b, reserves r WHERE b.bid = r.bid AND b.color = ‘red’) MINUS (SELECT DISTINCT r.sid FROM boats b, reserves r WHERE b.bid = r.bid AND b.color = ‘green’)

A. Find the sailor IDs of at least one sailor who have reserved red boats but not green boats

B. Find the sailor IDs of all sailors who have reserved red boats but not green boats

C. Find the sailor Ids of atmost one sailor who have reserved red boats but not green boats

D. None of These

Show Answer

Answer:-B. Find the sailor IDs of all sailors who have reserved red boats but not green boats

Explanation

The given SQL query finds the sailor IDs of all sailors who have reserved red boats but not green boats. It does this by first selecting the sailor IDs (r.sid) of reservations where the boat color is ‘red’ and then subtracting the sailor IDs of reservations where the boat color is ‘green’. The MINUS operator is used to find the set difference, and in this context, it retrieves the sailor IDs of sailors who have reserved red boats but not green boats. Option A is the correct choice for describing the query’s result.

Q 40. Which of the following query finds the name of the sailors who have reserved at least two boats?

A. SELECT DISTINCT s.sname FROM sailors s, reserves r1, reserves r2 WHERE s.sid = r1.sid AND r1.sid = r2.sid AND r1.bid ≠ r2.bid

B. SELECT DISTINCT s.sname FROM sailors s, reserves r1, reserves r2 WHERE s.sid = r1.sid AND COUNT(r1.bid) > r2.bid

C. SELECT DISTINCT s.sname FROM sailors s, reserves r1, reserves r2 WHERE s.sid = r1.sid AND r1.sid = r2.sid AND r1.bid r2.bid

D. Both A and C

Show Answer

Answer:-D. Both A and C

Explanation

Both Option A and Option C can be used to find the name of sailors who have reserved at least two boats. They achieve this by joining the “sailors” and “reserves” tables twice, specifying that the sailor IDs (s.sid) should be the same in both reservations, and ensuring that the boat IDs (r1.bid and r2.bid) are different. This identifies sailors who have made multiple reservations. Therefore, the correct answer is Option E, which includes both A and C as valid choices for this query.