Oracle SQL Exam Trivia Quiz!

Explanation
Group functions can be nested in SQL. This means that the result of one group function can be used as an input for another group function within the same query. For example, we can calculate the average of the maximum values of a column by nesting the MAX() and AVG() functions. Therefore, the statement "Group functions cannot be nested" is incorrect.

Explanation
The given statement is false. In a SELECT statement, more than two tables can be joined using the JOIN keyword. JOIN allows combining rows from multiple tables based on a related column between them. This enables querying data from multiple tables simultaneously, providing a comprehensive result set.

Explanation
NONEQUIJOINS are a type of JOIN used to select data between ranges of values. Unlike other types of JOINs, NONEQUIJOINS do not require an exact match between the values being compared. Instead, they allow for the selection of data within a specified range or condition. Therefore, the statement "NONEQUIJOINS are JOINs used to SELECT data between ranges of values" is true.

Explanation
The statement "The main query executes before the subquery" is false. In SQL, subqueries are executed before the main query. A subquery is a query nested within another query, and it is typically used to retrieve data that is then used in the main query. The subquery is executed first, and the result is then used in the main query to perform further operations or filtering. This allows for more complex queries and the ability to retrieve specific data based on conditions or calculations performed in the subquery.

Explanation
When performing operations involving SET operators (such as UNION, INTERSECT, or EXCEPT), the number of columns in the SELECT list must match for all queries involved. If the number of columns does not match, an error will occur. Therefore, the statement "The expression in SELECT list may not match in number when doing operations involving SET operators" is false.

Explanation
When using the UNION ALL operator, duplicate rows are not eliminated. This means that all rows from both tables involved in the UNION ALL operation will be included in the result, even if there are duplicate rows. In contrast, the UNION operator eliminates duplicate rows from the result set. Therefore, the statement "Duplicate rows are eliminated in UNION ALL" is false.

Explanation
In SET operations, such as UNION or INTERSECT, the ORDER BY clause can only be used once at the end of the entire compound query. This means that if multiple SELECT statements are combined using SET operations, the ORDER BY clause can only be applied to the final result set, rather than each individual SELECT statement. This ensures that the ordering is consistent across all the combined rows.

Explanation
The statement is true because the DELETE statement in SQL is used to remove existing rows from a table. It allows you to specify conditions to determine which rows should be deleted. By using the DELETE statement, you can easily remove unwanted data from a table, making it a useful tool for managing and maintaining data in a database.

Explanation
The TRUNCATE statement is used to remove all rows from a table, effectively deleting all data within the table. However, it does not remove the table structure itself, meaning that the columns, indexes, and constraints defined for the table remain intact. Therefore, the statement "TRUNCATE statement removes all rows from a table, leaving the table empty and the table structure intact" is true.

Explanation
An automatic rollback occurs when there is abnormal termination of SQL Developer or system failure because in such cases, any uncommitted changes made to the database may be lost or cause inconsistencies. To prevent this, the system automatically rolls back any pending transactions that were not successfully completed before the termination or failure. This ensures data integrity and avoids potential issues that could arise from incomplete or inconsistent transactions.

Explanation
The statement is false because a sequence in a database management system is used to generate unique numbers, typically for primary key values. It does not represent subsets of data from tables.

Explanation
Table and column names in a database must begin with a letter because it is a standard naming convention in most database management systems. Starting with a letter ensures that the names are easily identifiable and distinguishable from other elements in the database. It also helps to avoid conflicts with reserved keywords or special characters used in the database system. By following this rule, it promotes consistency and clarity in the database design, making it easier for developers and users to understand and work with the data.

Explanation
A view is a virtual table created by a query that retrieves data from one or more underlying tables in the database. While it is true that a view is based on underlying tables, it is not true that removing a view will result in data loss. Removing a view only eliminates the virtual representation of the data, but the actual data in the underlying tables remains unaffected. Therefore, the statement is false.

Explanation
When you define a PRIMARY KEY or UNIQUE KEY constraint in a table definition, a unique index is automatically created. This index ensures that the values in the specified column(s) are unique, meaning no duplicate values are allowed. This helps enforce data integrity and prevents duplicate records from being inserted into the table. Therefore, the statement "A unique index is created automatically when you define a PRIMARY KEY or UNIQUE KEY constraints in a table definition" is true.

Explanation
This statement is true because in most database management systems, constraints can be added to a table even after it has been created. Constraints are used to enforce rules and restrictions on the data in a table, such as specifying that a certain column cannot contain null values or that a column must be unique. Adding constraints after table creation allows for greater flexibility and control over the data in the database.

Explanation
The given statement is true. The DROP INDEX command is used to remove an index from a database table. By specifying the name of the index after the DROP INDEX command, the index can be deleted from the table. Therefore, to remove an index, the DROP INDEX command can be used.

Explanation
A table can be created from the result of a subquery. Subqueries are queries that are nested within another query and can return a result set that can be used as a temporary table. This allows for more complex and dynamic queries to be performed by using the results of one query as the input for another query. Therefore, the statement that a table cannot be created from the result of a subquery is false.

Explanation
In most database management systems, it is possible to add a new column to an existing table even after it has been created. This can be done using the ALTER TABLE statement, which allows modifications to the structure of a table. Adding a new column can be useful when new data needs to be stored or when the table design needs to be updated. Therefore, the statement "A column can be added even the table is already created" is true.

Explanation
Regular expressions can actually be used on CHECK constraints. Regular expressions are patterns that can be used to match and validate strings. They are commonly used in programming to validate input data. In the context of CHECK constraints, regular expressions can be used to enforce specific patterns or formats for column values. For example, a CHECK constraint can be used to ensure that a column only contains alphanumeric characters or that a column follows a specific date format. Therefore, the statement "Regular Expressions cannot be used on CHECK Constraints" is false.

Explanation
SQL statements are not case sensitive. This means that you can write SQL keywords and identifiers in any combination of uppercase and lowercase letters and the database system will interpret them in the same way. For example, "SELECT" and "select" are treated as the same keyword. However, the data stored in the database, such as table names and column values, may be case sensitive depending on the database system and its configuration.

Explanation
The statement is true because in programming, date and character literal values are typically enclosed within single quotation marks. This is a common convention used to distinguish them from other types of values and to indicate that they are specific values rather than variables or expressions. By using single quotation marks, it helps to clearly identify and differentiate these types of values within the code.

Explanation
A SELECT statement does not always require the presence of a WHERE clause. The WHERE clause is used to filter the rows returned by the SELECT statement based on certain conditions. However, it is not mandatory to include a WHERE clause in every SELECT statement. If a WHERE clause is not included, the SELECT statement will return all rows from the specified table(s). Therefore, the correct answer is False.

Explanation
The default order provided by the ORDER BY clause is ascending order, not descending order. When no specific order is specified in the ORDER BY clause, the result set will be sorted in ascending order by default.

Explanation
The statement is false because you can use case-conversion functions, such as UPPER() or LOWER(), in the WHERE clause of a SQL query. These functions allow you to convert the case of a string value to either uppercase or lowercase, which can be useful for comparing and filtering data.

Explanation
The MOD function is a mathematical function that calculates the remainder of a division operation. When using the MOD function, the result will be the remainder after dividing one number by another. Therefore, the given statement is true as it accurately describes the functionality of the MOD function.

Explanation
Single row functions in SQL can indeed be nested to any level. This means that the output of one single row function can be used as an input for another single row function, and this nesting can be continued to any depth. This allows for complex calculations and transformations to be performed on data in SQL queries, providing flexibility and versatility in data manipulation.

Explanation
Zero is not considered a NULL value. In programming and databases, a NULL value represents the absence of a value or an unknown value. Zero, on the other hand, is a specific numeric value that represents the quantity of nothing or absence of magnitude. Therefore, zero is not the same as NULL and is not considered a NULL value.

Explanation
The statement suggests that "AS" is not optional when creating aliases. However, this is incorrect. In SQL, "AS" is optional when creating aliases for tables or columns. Aliases can be created without using the "AS" keyword, making the statement false.

Explanation
Group functions, also known as aggregate functions, are used to perform calculations on a set of rows that share a common value in a specified column, known as the grouping column. These functions include operations such as sum, count, average, maximum, and minimum. The result of a group function is a single value for each group, rather than individual values for each row. Therefore, the statement that group functions operate on a set of rows to give one result per group is true.

Explanation
The statement "GROUP BY column does have to be in SELECT list" is false. In SQL, when using the GROUP BY clause, the columns specified in the GROUP BY clause do not necessarily have to be included in the SELECT list. The SELECT list can include additional columns that are not part of the GROUP BY clause. However, any columns in the SELECT list that are not part of an aggregate function must be included in the GROUP BY clause.