Oracle Mock Test 2 (SQL +arch)

1. Which SELECT statement should you use if you want to display unique combinations of the POSITION and MANAGER values from the EMPLOYEE table?

SELECT DISTINCT position, manager FROM employee;

SELECT position, manager DISTINCT FROM employee;

SELECT position, manager FROM employee;

SELECT position, DISTINCT manager FROM employee;

The correct answer is "SELECT DISTINCT position, manager FROM employee;". This statement includes the DISTINCT keyword after the SELECT clause, which ensures that only unique combinations of the POSITION and MANAGER values are displayed. The other options either have the DISTINCT keyword in the wrong position or do not include it at all.

Explanation

The correct answer is "SELECT DISTINCT position, manager FROM employee;". This statement includes the DISTINCT keyword after the SELECT clause, which ensures that only unique combinations of the POSITION and MANAGER values are displayed. The other options either have the DISTINCT keyword in the wrong position or do not include it at all.

2. You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results?

SELECT last_nameFROM EMPWHERE last_name LIKE ‘_A%’;

SELECT last_nameFROM EMPWHERE last name =’*A%’

SELECT last_nameFROM EMPWHERE last name =’_A%’;

SELECT last_nameFROM EMPWHERE last name LIKE ‘*A%’

The correct answer is "SELECT last_name FROM EMP WHERE last_name LIKE '_A%';". This SQL statement uses the LIKE operator with the pattern '_A%' to match last names that have the letter 'A' as the second character. The underscore (_) represents any single character, so it ensures that the second character is 'A'. The percentage symbol (%) represents any number of characters after 'A'. Therefore, this statement will display the last names of employees who have the letter 'A' as the second character in their names.

Explanation

The correct answer is "SELECT last_name FROM EMP WHERE last_name LIKE '_A%';". This SQL statement uses the LIKE operator with the pattern '_A%' to match last names that have the letter 'A' as the second character. The underscore (_) represents any single character, so it ensures that the second character is 'A'. The percentage symbol (%) represents any number of characters after 'A'. Therefore, this statement will display the last names of employees who have the letter 'A' as the second character in their names.

3. You want to use a function in your column clause of a SQL statement. The NVL function accomplishes which of the following tasks?

Assists in the distribution of output across multiple columns.

Enables you to specify alternate output for non-NULL column values.

Enables you to specify alternated out for NULL column values.

Nullifies the value of the column out put.

The NVL function in SQL enables you to specify alternate output for NULL column values. It allows you to replace NULL values with a specified default value or an alternate expression. This can be useful when you want to display a specific value or perform calculations on non-NULL values in your query results.

Explanation

The NVL function in SQL enables you to specify alternate output for NULL column values. It allows you to replace NULL values with a specified default value or an alternate expression. This can be useful when you want to display a specific value or perform calculations on non-NULL values in your query results.

4. A subquery can be used to ___.

Create groups of data

Sort data in a specific order

Convert data to a different format

Retrieve data based on an unknown condition

A subquery can be used to retrieve data based on an unknown condition. This means that the subquery can be used to filter data based on a condition that is not known beforehand, allowing for more flexible and dynamic queries. By using a subquery, the main query can select data based on the results of the subquery, allowing for more complex and specific data retrieval.

Explanation

A subquery can be used to retrieve data based on an unknown condition. This means that the subquery can be used to filter data based on a condition that is not known beforehand, allowing for more flexible and dynamic queries. By using a subquery, the main query can select data based on the results of the subquery, allowing for more complex and specific data retrieval.

5. Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set?

The DESCRIBE DEPT statement displays the structure of the DEPT table.

The ROLLBACK statement frees the storage space occupies by the DEPT table.

The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

The DESCRIBE DEPT statement displays the structure of the DEPT table.

Explanation

The DESCRIBE DEPT statement displays the structure of the DEPT table.

6. In which scenario would an index be most useful?

The indexed column is declared as NOT NULL.

The indexed columns are used in the FROM clause.

The indexed columns are part of an expression.

The indexed column contains a wide range of values.

An index is most useful when the indexed column contains a wide range of values. This is because an index helps in quickly locating specific values within a large dataset. When the column has a wide range of values, it means that there is a greater likelihood of needing to search for specific values within that range. By using an index, the database can efficiently narrow down the search space and retrieve the required data more quickly.

Explanation

An index is most useful when the indexed column contains a wide range of values. This is because an index helps in quickly locating specific values within a large dataset. When the column has a wide range of values, it means that there is a greater likelihood of needing to search for specific values within that range. By using an index, the database can efficiently narrow down the search space and retrieve the required data more quickly.

7. Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?

0

1

0.00

An error statement

The SQL statement first calculates the modulus of 1600 divided by 10, which is 0. Then it truncates this result to the nearest tens place, which is still 0. Finally, it rounds this result to two decimal places, which is still 0. Therefore, when the statement is executed, the value displayed will be 0.

Explanation

The SQL statement first calculates the modulus of 1600 divided by 10, which is 0. Then it truncates this result to the nearest tens place, which is still 0. Finally, it rounds this result to two decimal places, which is still 0. Therefore, when the statement is executed, the value displayed will be 0.

8. Which /SQL*Plus feature can be used to replace values in the WHERE clause?

Substitution variables

Replacement variables

Prompt variables

Instead-of variables

Substitution variables can be used to replace values in the WHERE clause. Substitution variables are denoted by an ampersand (&) followed by a variable name, and they allow users to dynamically input values during the execution of a SQL statement. This feature is useful when the values in the WHERE clause need to be changed frequently or when the values are not known in advance. By using substitution variables, the query can be made more flexible and adaptable to different scenarios.

Explanation

Substitution variables can be used to replace values in the WHERE clause. Substitution variables are denoted by an ampersand (&) followed by a variable name, and they allow users to dynamically input values during the execution of a SQL statement. This feature is useful when the values in the WHERE clause need to be changed frequently or when the values are not known in advance. By using substitution variables, the query can be made more flexible and adaptable to different scenarios.

9. The EMPloyee table contains these columns: Empno Number(4) Ename Varchar2(10) job varchar2(10) sal Varchar2(10) You need to display the employee's information by using this query. How many columns are presented after executing this query: SELECT Empno||','||Ename||','||Job "Employee Information" FROM employee;

1

2

3

4

0

The given query selects the concatenation of Empno, Ename, and Job columns from the Employee table, and renames the resulting column as "Employee Information". Therefore, after executing the query, only one column will be presented, which is the "Employee Information" column.

Explanation

The given query selects the concatenation of Empno, Ename, and Job columns from the Employee table, and renames the resulting column as "Employee Information". Therefore, after executing the query, only one column will be presented, which is the "Employee Information" column.

10. You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?

SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY')FROM dual;

SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY')FROM dual;

SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY')FROM dual;

SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;

SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY')FROM dual;

The correct answer is "SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;". This statement uses the TO_CHAR function to convert the system date (SYSDATE) into a character string in the desired format. The 'FMDay' format specifier ensures that the day of the week is displayed in full, 'DD' displays the day of the month, 'Month' displays the month name, and 'YYYY' displays the four-digit year. The FROM dual clause is used to select the result from a dummy table.

Explanation

The correct answer is "SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;". This statement uses the TO_CHAR function to convert the system date (SYSDATE) into a character string in the desired format. The 'FMDay' format specifier ensures that the day of the week is displayed in full, 'DD' displays the day of the month, 'Month' displays the month name, and 'YYYY' displays the four-digit year. The FROM dual clause is used to select the result from a dummy table.

11. Which clause should you use to exclude group results?

WHERE

HAVING

RESTRICT

GROUP BY

ORDER BY

The HAVING clause is used to exclude group results in a SQL query. It is used in conjunction with the GROUP BY clause to filter the results based on a condition applied to the grouped data. This allows for more specific and targeted filtering of the grouped data, excluding certain groups based on the specified condition.

Explanation

The HAVING clause is used to exclude group results in a SQL query. It is used in conjunction with the GROUP BY clause to filter the results based on a condition applied to the grouped data. This allows for more specific and targeted filtering of the grouped data, excluding certain groups based on the specified condition.

12. Evaluate this SQL*Plus command: START delaccount Which task will this command accomplish?

It executes the DELACCOUNT PL/SQL routine.

It runs the DELACCOUNT.SQL script file.

It creates the DELACCOUNT file using the default file extension.

It invokes the editor to edit the contents of the DELACCOUNT file.

This SQL*Plus command will run the DELACCOUNT.SQL script file. The START command is used in SQL*Plus to execute a script file, and in this case, it will execute the DELACCOUNT.SQL script file.

Explanation

This SQL*Plus command will run the DELACCOUNT.SQL script file. The START command is used in SQL*Plus to execute a script file, and in this case, it will execute the DELACCOUNT.SQL script file.

13. You want to use SQL*Plus to connect to the oracle database. Which of the following choices does not indicate a component you must specify when logging into the oracle?

The SQL*Plus Keyword.

The username

The password.

The database name.

When using SQL*Plus to connect to the Oracle database, the database name does not need to be specified. The SQL*Plus keyword, username, and password are required components for logging in. The database name is typically specified in the connection string or TNS entry and does not need to be provided separately when logging in.

Explanation

When using SQL*Plus to connect to the Oracle database, the database name does not need to be specified. The SQL*Plus keyword, username, and password are required components for logging in. The database name is typically specified in the connection string or TNS entry and does not need to be provided separately when logging in.

14. Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE. Which two aggregate functions are valid on the START_DATE column? (Choose two)

SUM(start_date)

AVG(start_date)

COUNT(start_date)

AVG(start_date, end_date)

MIN(start_date)

MAXIMUM(start_date)

The COUNT() function can be used to count the number of rows in a table, and it can also be used on a specific column to count the number of non-null values in that column. In this case, COUNT(start_date) would return the number of non-null values in the START_DATE column.

The MIN() function can be used to find the minimum value in a column. In this case, MIN(start_date) would return the earliest date in the START_DATE column.

Explanation

The COUNT() function can be used to count the number of rows in a table, and it can also be used on a specific column to count the number of non-null values in that column. In this case, COUNT(start_date) would return the number of non-null values in the START_DATE column.

The MIN() function can be used to find the minimum value in a column. In this case, MIN(start_date) would return the earliest date in the START_DATE column.

Submit

15. The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this?

SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end DESC, gpa DESC;

SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end ASC, gpa ASC;

SELECT student_id, semester_end, gpaFROM student_gradesORDER BY semester_end, gpa DESC;

SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end DESC;

SELECT student_id, semester_end, gpaFROM student_gradesORDER BY gpa DESC, semester_end ASC;.

The correct answer is SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end, gpa DESC; because it sorts the grade point averages (GPA) in ascending order within each semester, starting from the earliest date.

Explanation

The correct answer is SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end, gpa DESC; because it sorts the grade point averages (GPA) in ascending order within each semester, starting from the earliest date.

16. Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?

SELECTTO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATEFROM dual;

SELECTTO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATEFROM dual;

SELECTTO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM')NEW_DATEFROM dual;

SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth "of" Month YYYY fmtHH:MI:SS AM')NEW_DATEFROM dual;

The correct answer is the first option:
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

This statement uses the TO_CHAR function to convert the date '19-Mar-2001' into the desired format. The 'fmDdspth "of" Month YYYY fmHH:MI:SS AM' format model is used to specify the format of the output. 'fmDdspth' displays the day as 'Nineteenth', 'of' adds the word 'of' between the day and the month, 'Month' displays the month as 'March', 'YYYY' displays the year as '2001', 'fmHH:MI:SS' displays the time as '12:00:00 AM'. The result is a date string in the format "Nineteenth of March 2001 12:00:00 AM".

Explanation

The correct answer is the first option:
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

This statement uses the TO_CHAR function to convert the date '19-Mar-2001' into the desired format. The 'fmDdspth "of" Month YYYY fmHH:MI:SS AM' format model is used to specify the format of the output. 'fmDdspth' displays the day as 'Nineteenth', 'of' adds the word 'of' between the day and the month, 'Month' displays the month as 'March', 'YYYY' displays the year as '2001', 'fmHH:MI:SS' displays the time as '12:00:00 AM'. The result is a date string in the format "Nineteenth of March 2001 12:00:00 AM".

17. Because of a power outage, instance failure has occurred. From what point in the redo log does recovery begin and where does it end?

Current redo log and inactive redo log

Checkpoint position to end of redo log

Beginning of redo log to end of redo log

All redo logs before the point of last commit

Beginning of redo log to checkpoint position

Recovery begins from the checkpoint position in the redo log and continues until the end of the redo log. The checkpoint position represents a point in the redo log where all the changes have been flushed to disk, ensuring data consistency. By starting from the checkpoint position, the recovery process can apply any changes that were not yet written to disk before the instance failure occurred, ensuring that the database is brought back to a consistent state. The recovery process ends at the end of the redo log, as it has applied all the necessary changes to restore the database.

Explanation

Recovery begins from the checkpoint position in the redo log and continues until the end of the redo log. The checkpoint position represents a point in the redo log where all the changes have been flushed to disk, ensuring data consistency. By starting from the checkpoint position, the recovery process can apply any changes that were not yet written to disk before the instance failure occurred, ensuring that the database is brought back to a consistent state. The recovery process ends at the end of the redo log, as it has applied all the necessary changes to restore the database.

18. Which two operations require undo data?

Committing a transaction

Rolling back a transaction

Recovering from failed transactions

Recording a transaction to redo log files

Rolling forward during instance recovery

Rolling back a transaction requires undo data because it involves reversing the changes made by the transaction. Undo data is used to restore the database to its previous state before the transaction was executed. Similarly, recovering from failed transactions also requires undo data to undo the changes made by the failed transaction and restore the database to a consistent state. Both of these operations rely on the availability of undo data to ensure data integrity and consistency.

Explanation

Rolling back a transaction requires undo data because it involves reversing the changes made by the transaction. Undo data is used to restore the database to its previous state before the transaction was executed. Similarly, recovering from failed transactions also requires undo data to undo the changes made by the failed transaction and restore the database to a consistent state. Both of these operations rely on the availability of undo data to ensure data integrity and consistency.

Submit

19. According to your backup strategy, you performed an incremental level 0 backup of your database. Which statement regarding this backup is true?

The backup is similar to image copy.

The backup contains all used data blocks.

The backup contains only unused data blocks.

The backup contains all data blocks changed since the last incremental level 1 backup.

The correct answer is that the backup contains all used data blocks. This means that the backup includes all the data blocks that are currently being used by the database. It does not include any unused data blocks or data blocks that have been changed since the last incremental level 1 backup. This type of backup is similar to an image copy, as it captures all the data blocks in the database at a specific point in time.

Explanation

The correct answer is that the backup contains all used data blocks. This means that the backup includes all the data blocks that are currently being used by the database. It does not include any unused data blocks or data blocks that have been changed since the last incremental level 1 backup. This type of backup is similar to an image copy, as it captures all the data blocks in the database at a specific point in time.

20. Which statement regarding the contents of the V$PARAMETER view is true?

Displays only the list of default values

Displays only the list of all basic parameters

Displays the currently in effect parameter values

Displays only the list of all advanced parameters

Displays the list of all the parameter files of a database

Displays the current contents of the server parameter file

The V$PARAMETER view in Oracle displays the currently in effect parameter values. This means that it shows the current values of the parameters that are being used by the database. It does not display the default values, all basic parameters, all advanced parameters, the parameter files of the database, or the current contents of the server parameter file.

Explanation

The V$PARAMETER view in Oracle displays the currently in effect parameter values. This means that it shows the current values of the parameters that are being used by the database. It does not display the default values, all basic parameters, all advanced parameters, the parameter files of the database, or the current contents of the server parameter file.

21. From SQL*Plus, you issue this SELECT statement: SELECT* From orders; You use this statement to retrieve data from a data table for __________. (Choose all that apply)

Updating

Viewing

Deleting

Inserting

Truncating

The given SELECT statement is used to retrieve data from a data table. By using the "*" wildcard character after SELECT, it indicates that all columns from the "orders" table should be returned. Therefore, the statement is used for viewing the data. Additionally, the statement does not include any clauses or keywords related to updating, deleting, or truncating data, so it cannot be used for those purposes. However, it can be used for inserting data if the appropriate INSERT INTO statement is added after the SELECT statement.

Explanation

The given SELECT statement is used to retrieve data from a data table. By using the "*" wildcard character after SELECT, it indicates that all columns from the "orders" table should be returned. Therefore, the statement is used for viewing the data. Additionally, the statement does not include any clauses or keywords related to updating, deleting, or truncating data, so it cannot be used for those purposes. However, it can be used for inserting data if the appropriate INSERT INTO statement is added after the SELECT statement.

Submit

22. Which three statements correctly describe the functions and use of constraints? (Choose three.)

Constraints provide data independence.

Constraints make complex queries easy.

Constraints enforce rules at the view level.

Constraints enforce rules at the table level.

Constraints prevent the deletion of a table if there are dependencies.

Constraints prevent the deletion of an index if there are dependencies.

Constraints enforce rules at the view level, meaning that they can be used to restrict the data that is visible in a view. Constraints also enforce rules at the table level, ensuring that data entered into a table meets certain criteria. Additionally, constraints prevent the deletion of a table if there are dependencies, meaning that if other objects or tables rely on the data in a table, it cannot be deleted until those dependencies are resolved.

Explanation

Constraints enforce rules at the view level, meaning that they can be used to restrict the data that is visible in a view. Constraints also enforce rules at the table level, ensuring that data entered into a table meets certain criteria. Additionally, constraints prevent the deletion of a table if there are dependencies, meaning that if other objects or tables rely on the data in a table, it cannot be deleted until those dependencies are resolved.

Submit

23. Which three pieces of information are considered while deciding the size of the undo tablespace in your database? (Choose three.)

The size of an undo block

The size of the redo log files

Undo blocks generated per second

The size of the database buffer cache

The value of the UNDO_RETENTION parameter

The size of the undo tablespace in a database is determined by considering three pieces of information: the size of an undo block, the number of undo blocks generated per second, and the value of the UNDO_RETENTION parameter. The size of an undo block is important because it determines the amount of space needed to store undo data. The number of undo blocks generated per second is important because it affects the rate at which the undo tablespace grows. Finally, the value of the UNDO_RETENTION parameter is important because it determines how long undo data needs to be retained, which in turn affects the size of the undo tablespace.

Explanation

The size of the undo tablespace in a database is determined by considering three pieces of information: the size of an undo block, the number of undo blocks generated per second, and the value of the UNDO_RETENTION parameter. The size of an undo block is important because it determines the amount of space needed to store undo data. The number of undo blocks generated per second is important because it affects the rate at which the undo tablespace grows. Finally, the value of the UNDO_RETENTION parameter is important because it determines how long undo data needs to be retained, which in turn affects the size of the undo tablespace.

Submit