ExamVeda
Login
Home
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
Discuss
Answer & Solution
Answer: Option B
Solution:

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.

22
The SQL statement
SELECT SUBSTR('abcdefghij', INSTR('123321234', '2', 3, 2), 2) FROM DUAL;
prints
Discuss
Answer & Solution
Answer: Option A
Solution:

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

23
The SQL statement
SELECT ROUND(45.926, -1) FROM DUAL;
Discuss
Answer & Solution
Answer: Option D
Solution:

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.

For above given example, the output would be 50 because the nearest tens of 45.926, is 50.
Taking another example, if we have to round (44.678, -1), the out put would be 40, because the nearest tens of 44.678 is 40.

24
Which of the following must be enclosed in double quotes?
Discuss
Answer & Solution
Answer: Option D
Solution:
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?
Discuss
Answer & Solution
Answer: Option B
Solution:
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.

ROLLBACK (Option A) is used to undo the changes made during the transaction and return the database to its previous state.
TRUNCATE (Option C) is used to quickly delete all rows from a table, but it's not used to make changes performed by a transaction permanent.
DELETE (Option D) is used to remove specific rows from a table, but it doesn't finalize changes across an entire transaction.

So, Option B, the COMMIT command, is the correct choice for making transaction updates permanent in the database.
26
Which command undo all the updates performed by the SQL in the transaction?
Discuss
Answer & Solution
Answer: Option A
Solution:
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.

COMMIT (Option B) is used to make the changes performed by the transaction permanent in the database.
TRUNCATE (Option C) is used to quickly delete all rows from a table, but it doesn't address the concept of transactions or undoing updates.
DELETE (Option D) is used to remove specific rows from a table, but it doesn't undo all changes made within a transaction.

So, Option A, the ROLLBACK command, is the correct choice for undoing all updates performed by SQL statements in a transaction.
27
Find all the cities whose humidity is 89, Table Name weather
Discuss
Answer & Solution
Answer: Option B
Solution:
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.
28
Find the temperature in increasing order of all cities, Table Name 'weather'
Discuss
Answer & Solution
Answer: Option C
Solution:
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.
29
What is the meaning of LIKE '%0%0%'
Discuss
Answer & Solution
Answer: Option D
Solution:
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.
30
Find the names of these cities with temperature and condition whose condition is neither sunny nor cloudy
Discuss
Answer & Solution
Answer: Option A
Solution:
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."