Oracle Mock Test 2 (SQL +arch)

23 Questions | By Unrealvicky | Updated: May 10, 2022 | Attempts: 6794

Questions

Settings

Feedback

During the Quiz End of Quiz

Play as

Quiz Flashcard

Create your own 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!

Questions and Answers

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;
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
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.
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..
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
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.
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.
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
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%’
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;.
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)
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;
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
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.
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;
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
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.
18.

Which clause should you use to exclude group results?
- A.
  
  WHERE
- B.
  
  HAVING
- C.
  
  RESTRICT
- D.
  
  GROUP BY
- E.
  
  ORDER BY
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
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
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.
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
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