Multiple Choice Quiz Questions Of Oracle

The control file is a type of file that is part of the Oracle database. It is a binary file that stores important metadata about the database, such as the database name, datafile locations, and log file information. The control file is crucial for the proper functioning of the database as it is used during database startup and recovery operations. It helps in maintaining the consistency and integrity of the database by keeping track of changes made to the database. Therefore, the control file is an essential component of the Oracle database.

To create a database, the privilege required is SYSDBA. SYSDBA is a powerful privilege that allows a user to perform administrative tasks, including creating and managing databases, as well as performing backup and recovery operations. This privilege is typically granted to database administrators who need full control over the database system. SYSDBA users have the highest level of access and authority within the database, making them responsible for managing critical operations and ensuring the overall stability and security of the database environment.

If user B is experiencing a hung UPDATE statement, it means that user A's transaction is still in progress and has not been committed. By asking user A to commit the transaction, it will release the lock on the emp table and allow user B to continue working without any issues.

The GROUP BY clause is placed after the WHERE clause in a SELECT statement. This allows for the grouping of rows based on a specified column or columns. The WHERE clause is used to filter the rows based on certain conditions before the grouping is applied. Placing the GROUP BY clause after the WHERE clause ensures that the filtering is done before the grouping is performed.

The correct answer is "SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;". This statement selects the student_id and gpa columns from the student_grades table. It uses the BETWEEN operator to filter the records where the semester_end date falls between '01-JAN-2001' and '31-DEC-2001'. Additionally, it includes a condition to filter only those records where the gpa is greater than 3.0.

Sue should use the command "SELECT * FROM SCOTT.DEPT" to select from Scott's DEPT table. This command specifies the schema (SCOTT) and table name (DEPT) that Sue wants to select from.

The correct answer is DESCRIBE because it is a valid SQL*Plus command used to display the structure of a table, including the column names, datatypes, and constraints. It provides information about the table's structure and allows users to understand the layout of the table without querying the data itself.

A bitmap index would be appropriate in this scenario. This is because the PROD_ID column has a relatively low cardinality (96 distinct values), and the table is mostly queried rather than being updated frequently. Bitmap indexes are efficient for columns with low cardinality and are especially useful for read-intensive operations. They use a bitmap for each distinct value in the column, making them suitable for queries that involve multiple values or combinations of values.

The SQL statement is evaluating the expression ROUND(TRUNC(MOD(1600,10),-1),2).
First, the MOD(1600,10) calculates the remainder of 1600 divided by 10, which is 0.
Then, the TRUNC(0,-1) function truncates the value 0 to the nearest 10th, which is still 0.
Finally, the ROUND(0,2) function rounds the value 0 to 2 decimal places, resulting in 0.00.
Since the question asks for what will be displayed, the correct answer is 0.

Substitution variables in SQL* Plus can be used to replace values in the where clause. They allow users to dynamically input values at runtime, making queries more flexible and interactive. By using the ampersand (&) symbol followed by a variable name, users can prompt for input and substitute the entered value into the query. This feature is particularly useful when the values in the where clause need to be changed frequently or when the user wants to filter the results based on specific criteria.

A subquery can be used to retrieve data based on an unknown condition. This means that the subquery can be used to fetch data from a table based on a condition that is not known or specified beforehand. The subquery can be used to dynamically retrieve data based on the result of another query or based on certain calculations or comparisons within the subquery itself. This allows for more flexible and dynamic data retrieval, as the condition can be determined at runtime.

The correct answer is "CREATE INDEX NAME_IDX ON employees (first_name, last_name)". This statement is used to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. It specifies the table name (employees), the index name (NAME_IDX), and the columns to be included in the index (first_name and last_name).

The correct answer is SELECT TO_CHAR(SYSDATE, ‘yyyy’) FROM dual. This statement uses the TO_CHAR function to convert the system date (SYSDATE) into the specified format (‘yyyy’), which represents the year. The result will be displayed as "1998" or any other current year. The FROM dual clause is used to ensure that the query is executed properly.

The HAVING clause should be used to exclude group results. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause filters groups after they have been formed. It allows you to specify conditions that must be met by the groups, allowing you to exclude certain groups based on those conditions. This is useful when you want to filter the results based on aggregate functions such as COUNT, SUM, or AVG. The HAVING clause is typically used in conjunction with the GROUP BY clause to perform group-level filtering.

During the rebuilding of an index, a new index is created using an existing index as the data source. This means that the existing index is used as a reference to build the new index. Additionally, during the rebuild process, enough space is required to accommodate both the old and the new index in their respective tablespaces. This is because the old index needs to be kept until the new index is fully built and ready to replace it.

The correct answer is "ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);". This statement adds a constraint named "stud_id_pk" to the "students" table, specifying that the "student_id" column should be the primary key. This ensures that each value in the "student_id" column is unique and not null, effectively creating a primary key on that column.

DBA_TAB_PRIVS is the correct answer because it is a data dictionary view that provides information about object privileges for all database users. This view contains columns such as GRANTEE (the user who has been granted the privilege), OWNER (the owner of the object), TABLE_NAME (the name of the object), and PRIVILEGE (the type of privilege granted). By querying this view, you can obtain a comprehensive list of object privileges for all users in the database.

At the end of this transaction, the table "new_emp" will have no rows. This is because the transaction starts with creating the table "new_emp" and inserting data from the "employees" table into it. However, after saving a savepoint (s2) and deleting all rows from "new_emp", the rollback to s2 restores the table to its state before the delete statement, which means the rows are still deleted. The subsequent update statement does not affect any rows because the employee with ID 180 was already deleted. Therefore, the table will have no rows in the end.

The correct answer is SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’) because the TO_CHAR function is used to convert the system date to a character string in the specified format. The 'FMDay' format specifier will display the full name of the day, 'DD' will display the day of the month, 'Month' will display the full name of the month, and 'YYYY' will display the year in four digits. This will result in the desired format of "Monday, 01 June, 2001".

TRIM and REPLACE are character manipulation functions. TRIM is used to remove leading and trailing spaces from a string, while REPLACE is used to replace a specific substring within a string with another substring. These functions are commonly used in database queries or programming languages to manipulate and modify character data. TRUNC, TO_DATE, and MOD are not character manipulation functions; TRUNC is used for truncating a date or number, TO_DATE is used for converting a string to a date, and MOD is used for finding the remainder of a division operation.

Views can be created as read-only, meaning that they can be used to retrieve data but not to modify it. Views can also be created as a join on two or more tables, allowing users to retrieve data from multiple tables as if it were a single table. However, views can have an ORDER BY clause in the SELECT statement, so the statement that says a view cannot have an ORDER BY clause is incorrect. Similarly, views can be created with a GROUP BY clause in the SELECT statement, so the statement that says a view cannot be created with a GROUP BY clause is also incorrect. Lastly, views do not necessarily require aliases for the column names in the SELECT statement, so the statement that says a view must have aliases defined for the column names is incorrect.

The first statement, "SELECT COUNT (*) FROM customers;", finds the number of customers by counting the total number of rows in the customers table.

The fourth statement, "SELECT COUNT(costomer_id) FROM customer;", also finds the number of customers by counting the total number of non-null values in the customer_id column of the customer table.

The TRUNCATE TABLE command in this scenario has the following implications:
1. All rows of the table are deleted: This means that all data within the "departments" table will be removed.
2. Any associated indexes are truncated: Any indexes associated with the "departments" table will also be truncated, essentially removing them.
3. No undo data is generated for the table's rows: Unlike other deletion methods, such as DELETE, the TRUNCATE TABLE command does not generate any undo data for the deleted rows. This means that the action cannot be rolled back or undone.
4. It reduces the number of extents allocated to the DEPARTMENTS table to the original setting for MINEXTENTS: This implies that the TRUNCATE TABLE command will reduce the number of extents allocated to the "departments" table to its original setting, which is determined by the MINEXTENTS parameter.

The correct answer is the fourth option: UPDATE emplouees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP'. This statement updates the job_id to the default value, the sal to the maximum salary earned for the job_id 'SA_REP', the comm_pct to the default value, and the department_id to the value provided by the user through the substitution variable &did. It also specifies that the update should only be applied to the employee_id 103 and 115.

When the user issues the COMMIT statement, the user process notifies the server process that the resource locks can be released. This means that any locks held by the user process on the affected data are released, allowing other processes to access and modify the data. This ensures data consistency and concurrency in a multi-user environment.

Aggregate functions can be used only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement. This means that aggregate functions cannot be used in any other clause of a SELECT statement such as GROUP BY, HAVING, or ORDER BY. Additionally, aggregate functions allow for the passing of various parameters such as column names, expressions, constants, or functions. This allows for more flexibility in performing calculations on the data.

The given SQL statement provides one of the desired results, which is to display the salary multiplied by the commission_pct. It achieves this by selecting the LAST_NAME and multiplying the SALARY by COMMISSION_PCT from the EMPLOYEES table, and only including rows where the COMMISSION_PCT is not null. However, it does not address the other two desired results, which are to exclude employees with a zero commission_pct and to display a zero for employees with a null commission value.

The three DATETIME data types that can be used when specifying column definitions are TIMESTAMP, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH. These data types are commonly used in databases to store date and time information. TIMESTAMP stores both date and time values, INTERVAL DAY TO SECOND stores a duration in days, hours, minutes, and seconds, and INTERVAL YEAR TO MONTH stores a duration in years and months. These data types provide flexibility in storing and manipulating date and time data in a database.

The given answer is a valid SQL query that retrieves the last name, salary, department ID, and the maximum salary for each department from the EMPLOYEES table. It achieves this by using a subquery to calculate the maximum salary for each department and then joining it with the EMPLOYEES table using the department ID. The result is a list of employees with their respective last name, salary, department ID, and the maximum salary for each department.

Constraints provide data independence by ensuring that the data remains consistent and accurate regardless of changes made to the database structure. Constraints enforce rules at both the view level and the table level to maintain data integrity and prevent invalid or inconsistent data from being entered. These rules can include enforcing unique values, referential integrity, and data type constraints. Constraints also prevent the deletion of a table or an index if there are dependencies, ensuring that data dependencies are maintained and preventing data loss or corruption.