Oracle Mock Test 2 (SQL +arch)

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Unrealvicky
U
Unrealvicky
Community Contributor
Quizzes Created: 6 | Total Attempts: 10,559
| Attempts: 7,033 | Questions: 23
Please wait...
Question 1 / 23
0 %
0/100
Score 0/100
1. Which SELECT statement should you use if you want to display unique combinations of the POSITION and MANAGER values from the EMPLOYEE table?

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.

Submit
Please wait...
About This Quiz
Oracle Mock Test 2 (SQL +arch) - Quiz

Are you learning the Oracle software? Do you want to test your knowledge about Oracle? Try this interesting and well-researched Oracle mock test 2 (SQL + Arch). With... see morethis test, you'll get the chance to test your understanding of Oracle and its related concepts. Oracle, as most of us know, makes software called the database management systems (DBMS) to create and manage databases. If you think you have good knowledge about it, then just give this quiz a try. Best of luck! see less

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?

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.

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

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.

Submit
4. A subquery can be used to ___.

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.

Submit
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?

Explanation

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

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

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.

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

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.

Submit
8. 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;

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.

Submit
9. In which scenario would an index be most useful?

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.

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

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.

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

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.

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

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.

Submit
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?

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.

Submit
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)

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?

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.

Submit
16. Which SQL statement displays the date March 19, 2001 in a format that appears as "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".

Submit
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?

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.

Submit
18. Which two operations require undo data? 

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?

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.

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

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.

Submit
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)

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.)

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.)

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
View My Results

Quiz Review Timeline (Updated): May 10, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • May 10, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Mar 29, 2011
    Quiz Created by
    Unrealvicky
Cancel
  • All
    All (23)
  • Unanswered
    Unanswered ()
  • Answered
    Answered ()
Which SELECT statement should you use if you want to display unique...
You need to display the last names of those employees who have the...
You want to use a function in your column clause of a SQL statement....
A subquery can be used to ___.
Evaluate the set of SQL statements: ...
Evaluate the SQL statement: ...
Which /SQL*Plus feature can be used to replace values in the WHERE...
The EMPloyee table contains these columns: ...
In which scenario would an index be most useful?
You would like to display the system date in the format "Monday,...
Which clause should you use to exclude group results?
Evaluate this SQL*Plus command: ...
You want to use SQL*Plus to connect to the oracle database. Which of...
Examine the description of the STUDENTS table: ...
The STUDENT_GRADES table has these columns: ...
Which SQL statement displays the date March 19, 2001 in a format that ...
Because of a power outage, instance failure has occurred. From what...
Which two operations require undo data? 
According to your backup strategy, you performed an incremental level...
Which statement regarding the contents of the V$PARAMETER view is ...
From SQL*Plus, you issue this SELECT statement: ...
Which three statements correctly describe the functions and use of ...
Which three pieces of information are considered while deciding the...
Alert!

Advertisement