Oracle Mock Test 2 (SQL +arch)
(488).jpg "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 this 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!
- 1.
Which SELECT statement should you use if you want to display unique combinations of the POSITION and MANAGER values from the EMPLOYEE table?
- A.
SELECT DISTINCT position, manager FROM employee;
- B.
SELECT position, manager DISTINCT FROM employee;
- C.
SELECT position, manager FROM employee;
- D.
SELECT position, DISTINCT manager FROM employee;
Correct Answer
A. SELECT DISTINCT position, manager FROM employee;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.Rate this question:
-
- 2.
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)
- A.
Updating
- B.
Viewing
- C.
Deleting
- D.
Inserting
- E.
Truncating
Correct Answer(s)
B. Viewing
D. InsertingExplanation
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.Rate this question:
-
- 3.
Evaluate this SQL*Plus command: START delaccount Which task will this command accomplish?
- A.
It executes the DELACCOUNT PL/SQL routine.
- B.
It runs the DELACCOUNT.SQL script file.
- C.
It creates the DELACCOUNT file using the default file extension.
- D.
It invokes the editor to edit the contents of the DELACCOUNT file.
Correct Answer
B. It runs 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.Rate this question:
-
- 4.
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?
- A.
The DESCRIBE DEPT statement displays the structure of the DEPT table.
- B.
The ROLLBACK statement frees the storage space occupies by the DEPT table.
- C.
The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
- D.
The DESCRIBE DEPT statement displays the structure of the DEPT table only if the us a COMMIT statement introduced before the ROLLBACK statement..
Correct Answer
A. The DESCRIBE DEPT statement displays the structure of the DEPT table.Explanation
The DESCRIBE DEPT statement displays the structure of the DEPT table.Rate this question:
-
- 5.
Which /SQL*Plus feature can be used to replace values in the WHERE clause?
- A.
Substitution variables
- B.
Replacement variables
- C.
Prompt variables
- D.
Instead-of variables
Correct Answer
A. Substitution variablesExplanation
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.Rate this question:
-
- 6.
You want to use a function in your column clause of a SQL statement. The NVL function accomplishes which of the following tasks?
- A.
Assists in the distribution of output across multiple columns.
- B.
Enables you to specify alternate output for non-NULL column values.
- C.
Enables you to specify alternated out for NULL column values.
- D.
Nullifies the value of the column out put.
Correct Answer
C. Enables you to specify alternated out for NULL column values.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.Rate this question:
-
- 7.
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?
- A.
The SQL*Plus Keyword.
- B.
The username
- C.
The password.
- D.
The database name.
Correct Answer
D. The database name.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.Rate this question:
-
- 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;
- A.
1
- B.
2
- C.
3
- D.
4
- E.
0
Correct Answer
A. 1Explanation
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.Rate this question:
-
- 9.
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?
- A.
SELECT last_name FROM EMP WHERE last_name LIKE ‘_A%’;
- B.
SELECT last_name FROM EMP WHERE last name =’*A%’
- C.
SELECT last_name FROM EMP WHERE last name =’_A%’;
- D.
SELECT last_name FROM EMP WHERE last name LIKE ‘*A%’
Correct Answer
A. SELECT last_name FROM EMP WHERE last_name LIKE ‘_A%’;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.Rate this question:
-
- 10.
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?
- A.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
- B.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end ASC, gpa ASC;
- C.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa DESC;
- D.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
- E.
SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;.
Correct Answer
C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa DESC;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.Rate this question:
-
- 11.
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)
- A.
SUM(start_date)
- B.
AVG(start_date)
- C.
COUNT(start_date)
- D.
AVG(start_date, end_date)
- E.
MIN(start_date)
- F.
MAXIMUM(start_date)
Correct Answer(s)
C. COUNT(start_date)
E. MIN(start_date)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.Rate this question:
-
- 12.
You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
- A.
SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;
- B.
SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') FROM dual;
- C.
SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;
- D.
SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
- E.
SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
Correct Answer
C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;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.Rate this question:
-
- 13.
A subquery can be used to ___.
- A.
Create groups of data
- B.
Sort data in a specific order
- C.
Convert data to a different format
- D.
Retrieve data based on an unknown condition
Correct Answer
D. Retrieve data based on an unknown conditionExplanation
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.Rate this question:
-
- 14.
In which scenario would an index be most useful?
- A.
The indexed column is declared as NOT NULL.
- B.
The indexed columns are used in the FROM clause.
- C.
The indexed columns are part of an expression.
- D.
The indexed column contains a wide range of values.
Correct Answer
D. The indexed column contains a wide range of values.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.Rate this question:
-
- 15.
Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?
- A.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
- B.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
- C.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM') NEW_DATE FROM dual;
- D.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;
Correct Answer
A. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;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".Rate this question:
-
- 16.
Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?
- A.
0
- B.
1
- C.
0.00
- D.
An error statement
Correct Answer
A. 0Explanation
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.Rate this question:
-
- 17.
Which three statements correctly describe the functions and use of constraints? (Choose three.)
- A.
Constraints provide data independence.
- B.
Constraints make complex queries easy.
- C.
Constraints enforce rules at the view level.
- D.
Constraints enforce rules at the table level.
- E.
Constraints prevent the deletion of a table if there are dependencies.
- F.
Constraints prevent the deletion of an index if there are dependencies.
Correct Answer(s)
C. Constraints enforce rules at the view level.
D. Constraints enforce rules at the table level.
E. Constraints prevent the deletion of a table if there are dependencies.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.Rate this question:
-
- 18.
Which clause should you use to exclude group results?
- A.
WHERE
- B.
HAVING
- C.
RESTRICT
- D.
GROUP BY
- E.
ORDER BY
Correct Answer
B. HAVINGExplanation
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.Rate this question:
-
- 19.
Which three pieces of information are considered while deciding the size of the undo tablespace in your database? (Choose three.)
- A.
The size of an undo block
- B.
The size of the redo log files
- C.
Undo blocks generated per second
- D.
The size of the database buffer cache
- E.
The value of the UNDO_RETENTION parameter
Correct Answer(s)
A. The size of an undo block
C. Undo blocks generated per second
E. The value of the UNDO_RETENTION parameterExplanation
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.Rate this question:
-
- 20.
Which statement regarding the contents of the V$PARAMETER view is true?
- A.
Displays only the list of default values
- B.
Displays only the list of all basic parameters
- C.
Displays the currently in effect parameter values
- D.
Displays only the list of all advanced parameters
- E.
Displays the list of all the parameter files of a database
- F.
Displays the current contents of the server parameter file
Correct Answer
C. Displays the currently in effect parameter valuesExplanation
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.Rate this question:
-
- 21.
According to your backup strategy, you performed an incremental level 0 backup of your database. Which statement regarding this backup is true?
- A.
The backup is similar to image copy.
- B.
The backup contains all used data blocks.
- C.
The backup contains only unused data blocks.
- D.
The backup contains all data blocks changed since the last incremental level 1 backup.
Correct Answer
B. The backup contains all used data blocks.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.Rate this question:
-
- 22.
Because of a power outage, instance failure has occurred. From what point in the redo log does recovery begin and where does it end?
- A.
Current redo log and inactive redo log
- B.
Checkpoint position to end of redo log
- C.
Beginning of redo log to end of redo log
- D.
All redo logs before the point of last commit
- E.
Beginning of redo log to checkpoint position
Correct Answer
B. Checkpoint position to end of redo logExplanation
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.Rate this question:
-
- 23.
Which two operations require undo data?
- A.
Committing a transaction
- B.
Rolling back a transaction
- C.
Recovering from failed transactions
- D.
Recording a transaction to redo log files
- E.
Rolling forward during instance recovery
Correct Answer(s)
B. Rolling back a transaction
C. Recovering from failed transactionsExplanation
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.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
May 10, 2023Quiz Edited by
ProProfs Editorial Team -
Mar 29, 2011Quiz Created by
Unrealvicky
Back to top