Oracle Test 3

50 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Oracle questions


Questions and Answers
  • 1. 
    After creating a new user for your Oracle database, a user still complains he or she cannot log in because of insufficient privileges errors. Which of the following actions should you take?
    • A. 

      Reset the user's password

    • B. 

      Grant create table privileges to the user

    • C. 

      . Grant the CONNECT role to the user

    • D. 

      . Unlock the user's account

  • 2. 
    Which two statements about sequences are true? (Choose two)
    • A. 

      A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

    • B. 

      B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.

    • C. 

      C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence

    • D. 

      D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

    • E. 

      E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

    • F. 

      F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

  • 3. 
    . When should you create a role? (Choose two)
    • A. 

      A. to simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyyy statement

    • B. 

      B. to grant a group of related privileges to a user

    • C. 

      C. When the number of people using the database is very high

    • D. 

      D. to simplify the process of granting and revoking privileges

    • E. 

      E. To simplify profile maintenance for a user who is constantly traveling.

  • 4. 
     Click the Exhibit button to examine the structure of the EMPOLOYEES, DEPARTMENTS and TAX tables. EMPLOYEES   EMPLOYEE_ID             NUMBER                      NOT NULL        primary key EMP_NAME                  VARCHAR2 (30) JOB_ID                         VARCHAR2 (20) SALARY                       NUMBER MGR_ID                        NUMBER                      Reference EMPLOYEE_ID Column DEPARTMENT_ID         NUMBER                      Foreign key to DEPARTMENT_ID TO column of the DEPARTMENT table   DEPARTMENTS DEPARTMENT_ID         NUMBER                      NOT NULL        primary key DEPARTMENT_NAME VARCHAR2 (30) MGR_ID                        NUMBER                      Reference MGR_ID column of the EMPLOYEES table   TAX MIN_SALARY               NUMBER MAX_SALARY              NUMBER TAX_PERCENT            NUMBER For which situation would you use a nonequijoin query?
    • A. 

      A. to find the tax percentage for each of the employees

    • B. 

      B. to list the name, job id, and manager name for all the employees

    • C. 

      C. to find the name, salary and the department name of employees who are not working with Smith

    • D. 

      D. to find the number of employees working for the Administrative department and earning less than 4000

    • E. 

      E. to display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned

  • 5. 
    Which statement explicitly names a constraint?
    • A. 

      A. ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students (student_id);

    • B. 

      B. ALTER TABLE student_grades ADD CONSTRAINT NAME=student_id_fk FOREIGN KEY (student_id) REFERENCES student(student_id);

    • C. 

      C. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);

    • D. 

      D. ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id)

    • E. 

      E. ALTER TABLE student grades ADD NAME student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id)

  • 6. 
                 150. You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME   The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table.   You want to modify the view by adding a fourth column, MANAGER_Id of NUMBER data type from the EMPLOYEES table.   How can you accomplish this task?
    • A. 

      A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER),

    • B. 

      B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

    • C. 

      C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name Department_name, manager_id FROM employees e, departments d WHERE department_id = d.department_id;

    • D. 

      D. MODIFY VIEW emp_depat_vu AS SELECT employee_id, employee_name, Department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

    • E. 

      E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_ name, Department_name, manager _id FROM employees e, departments d WHERE e.department_id=d.department_id;

    • F. 

      F. You must remove the existing view first, and then run the CRATE VIEW command with a new column list to modify a view.

  • 7. 
     Examine the structure of the EMPLOYEES table: Column name              Data type                      Remarks EMPOYEE_ID               NUMBER                      NOT NULL, Primary Key EMP_NAME                  VARCHAR2 (30) JOB_ID                         VARCHAR2 (20)            NOT NULL SAL                              NUMBER MGR_ID                        NUMBER                      References EMPLOYEE_ID column DEPARTMENT_ID         NUMBER                      Foreign key to DEPARTMENT_ID column Of the DEPARTMENTS table   You need to create a view called EMP_VU that allows the users to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?  
    • A. 

      A. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, Department_id FROM employees WHERE mgr_id IN (102,120);

    • B. 

      B. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120);

    • C. 

      C. CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTAL SAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;

    • D. 

      D. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees

  • 8. 
    Click the Exhibit button to examine the data of the EMPLOYEES table.   EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)   EMPLOYEE_ID - EMP_NINE - DEPT_ID - MGR_ID - JOB_ID - SALARY   101 - Smith - 20 - 120 - SA_REP - 4000 102 - Martin - 10 - 105 - CLERK - 2500 103 - Chris - 20 - 120 - IT_ADMIN - 4200 104 - John - 30 - 108 - HR_CLERK - 2500 105 - Diana - 30 - 108 - HR_MGR - 5000 106 - Bryan - 40 - 110 - AD_ASST - 5000 108 - Jennifer - 30 - 110 - HR_DIR - 6500 110 - Bob - 40 - ** - EX_DIR - 8000 120 - Ravi - 20 - 110 - SA_DIR - 6500   Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000?  
    • A. 

      A. SELECT employee_id "Emp_id", emp_name "Employee", salary, employee_id "Mgr_id", emp_name "Manager" FROM employees WHERE salary > 4000;

    • B. 

      B. SELECT e.employee_id "Emp_id", e.emp_name "Employee" ,e.salary, m.employee_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;

    • C. 

      C. SELECT e.employee_id "Emp_id", e.emp_name "Employee" ,e.salary, m.employee_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;

    • D. 

      D. SELECT e.employee_id "Emp_id", e.emp_name "Employee" ,e.salary, m.mgr_id "Mgr_id", m.emp_name "manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;

    • E. 

      E. SELECT e.employee_id "Emp_id", e.emp_name "Employee" ,e.salary, m.mgr_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m WHERE e.employee_id = m.employee_id AND e.salary > 4000;

  • 9. 
     You need to calculate the total of all salaries in the accounting department. Which group function should you use?     
    • A. 

      A. MAX

    • B. 

      B. MIN

    • C. 

      C. SUM

    • D. 

      D. COUNT

    • E. 

      E. TOTAL

    • F. 

      F. LARGEST

  • 10. 
    Examine the data in the EMPLOYEES and EMP_HIST tables: EMPLOYEES EMPLOYEE_IDNAME   DEPT_ID          MGR_ID            JOB_ID SALARY 101 - Smith - 20 - 120 - SA_REP - 4000 102 - Martin - 10 - 105 - CLERK - 2500 103 - Chris - 20 - 120 - IT_ADMIN - 4200 104 - John - 30 - 108 - HR_CLERK - 2500 105 - Diana - 30 - 108 - IT_ADMIN - 5000 106 - Smith - 40 - 110 - AD_ASST - 3000 108 - Jennifer - 30 - 110 - HR_DIR - 6500 110 - Bob - 40 - ** - EX_DIR - 8000 120 - Ravi - 20 - 110 - SA_DIR - 6500   EMP_HIST EMPLOYEE_ID - NAME - JOB_ID - SALARY 101 - Smith - SA_CLERK - 2000 103 - Chris - IT_CLERK - 22 104 - John - HR_CLERK - 2000 106 - Smith - AD_ASST - 3000 108 - Jennifer - HR_MGR – 4500   The EMP_HIST table is updated at the end of every year. The employee ID, name, jobID, and salary of each existing employee are modified with the latest data. New employee details are added to the table. Which statement accomplishes this task?  
    • A. 

      A. UPDATE emp_hist SET employee_id, name, job_id, salary = (SELECT employee_id, name, job_id, salary FROM employees) WHERE employee_id IN (SELECT employee_id FROM employees);

    • B. 

      B. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.name, e.job_id id, e.salary);

    • C. 

      C. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE emp_hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);

    • D. 

      D. MERGE INTO emp_hist eh USING employees e WHEN MATCHED THEN UPDATE emp_hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);

  • 11. 
    The CUSTOMERS table has these columns:   CUSTOMER_ID             NUMBER (4)                 NOT NULL CUSTOMER_NAME      VARCHAR2 (100)          NOT NULL STREET_ADDRESS      VARCHAR2 (150) CITY_ADDRESS           VARCHAR2 (50) STATE_ADDRESS        VARCHAR2 (50) PROVINCE_ADDRESS VARCHAR2 (50) COUNTRY_ADDRESS   VARCHAR2 (50) POSTE_CODE              VARCHAR2 (12) CUSTOMER_PHONE    VARCHAR2 (20)   THE CUSTOMER_ID column is the primary key for the table which two statements find the number of customer? (Choose two.)  
    • A. 

      A. SELECT TOTAL (*) FROM customers;

    • B. 

      B. SELECT COUNT (*) FROM customers;

    • C. 

      C. SELECT TOTAL (customer_id) FROM customer;

    • D. 

      D. SELECT COUNT(customer_id) FROM customer;

    • E. 

      E. SELECT COUNT(customers) FROM customers;

  • 12. 
    Examine the structure of the EMPLOYEES table: EMPLOYEE_ID                         NUMBER                      NOT NULL EMP_ID                                    VARCHAR2 (30) JOB_ID                                     VARCHAR2 (20)            DEFAULT 'SA_REP' SAL                                          NUMBER COMM_PCT                              NUMBER MGR_ID                                    NUMBER DEPARTMENT_ID                     NUMBER   You need to update the records of employees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below: JOB_ID: Default value specified for this column definition SAL: maximum salary earned for the job ID SA_REP COMM_PCT: Default value is specified for the column, the value should be NULL DEPARTMENT_ID: Supplied by the user during run time through substitution variable Which UPDATE statement meets the requirements?  
    • A. 

      A. UPDATE employees SET job_id=DEFAULT AND Sal=(SELECT MAX(sal) FROM employees WHERE job_id='SA_REP' AND comm_pct=DEFALUT AND department_id =&did WHERE employee_id IN (103, 115),

    • B. 

      B. UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department _id = & did WHERE employee_id IN (103,115) AND job_id = 'SA_REP'

    • C. 

      C. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX (sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT, department _id = &did WHERE employee_id IN (103,115)

    • D. 

      D. UPDATE employees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP' E. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHERE employee_id IN (103,115)

  • 13. 
    154. Examine the structure of the EMPLOYEES table: Column name              Data type                      Remarks EMPOYEE_ID               NUMBER                      NOT NULL, Primary Key EMP_NAME                  VARCHAR2 (30) JOB_ID                         VARCHAR2 (20)            NOT NULL SAL                              NUMBER MGR_ID                        NUMBER                      References EMPLOYEE_ID column DEPARTMENT_ID         NUMBER                      Foreign key to DEPARTMENT_ID column Of the DEPARTMENTS table   You need to create a view called EMP_VU that allows the users to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?  
    • A. 

      E. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, Department_id FROM employees WHERE mgr_id IN (102,120);

    • B. 

      F. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120);

    • C. 

      G. CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTAL SAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;

    • D. 

      H. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees

  • 14. 
    The EMPLOYEES table contains these columns: LAST_NAME                VARCHAR2 (25) SALARY                       NUMBER (6, 2) COMMISSION_PCT      NUMBER (6)   You need to write a query that will produce these results: 1. Display the salary multiplied by the commission_pct 2. Exclude employees with a zero commission_pct 3. Display a zero for employees with a null commission value   Evaluate the SQL statement: SELECT LAST_NAME, SALARY * COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;   What does the statement provide?    
    • A. 

      A. all of the desired results

    • B. 

      B. two of the desired results

    • C. 

      C. one of the desired results

    • D. 

      D. an error statement

  • 15. 
     A subquery can be used to _________.     
    • A. 

      E. create groups of data

    • B. 

      F. sort data in a specific order

    • C. 

      G. convert data to a different format

    • D. 

      H. retrieve data based on an unknown condition

  • 16. 
     Which clause should you use to exclude group results?     
    • A. 

      A. WHERE

    • B. 

      B. HAVING

    • C. 

      C. RESTRICT

    • D. 

      D. GROUP BY

    • E. 

      E. ORDER BY

  • 17. 
    The EMP table has these columns: ENAME                        VARCHAR2 (35) SALARY                       NUMBER (8, 2) HIRE_DATE                  DATE Management wants a list of names of employees who have been with the company for more than five yeas. Which SQL statement displays the required results?    
    • A. 

      A. SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5

    • B. 

      B. SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5

    • C. 

      C. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5

    • D. 

      D. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5

  • 18. 
      122. Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:   LAST NAME                 VARCHAR2 (35)            NOT NULL SALARY                       NUMBER (9, 2) NOT NULL COMMISSION_PCT      NUMBER (4, 2)   Which statement ensures that a value is displayed in the calculated column for all employees?     
    • A. 

      A. SELECT last_name, 12 * salary* commission_pct FROM emp;

    • B. 

      B. SELECT last_name, 12 * salary* (commission_pct,0) FROM emp;

    • C. 

      C. SELECT last_name, 12 * salary* (nvl(commission_pct,0) FROM emp;

    • D. 

      D. SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;

  • 19. 
     Mark for review. Which two are true about aggregate functions? (Choose two.)     
    • A. 

      A. You can use aggregate functions in any clause of a SELECT statement.

    • B. 

      B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

    • C. 

      C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

    • D. 

      D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

    • E. 

      E. You can use aggregate functions on a table, only by grouping the whole table as one single group.

  • 20. 
     Mark for review. Which four are types of functions available in SQL? (Choose 4)     
    • A. 

      A. string

    • B. 

      B. character

    • C. 

      C. integer

    • D. 

      E. numeric

    • E. 

      F. translation

    • F. 

      G. date

    • G. 

      H. conversion

  • 21. 
     Evaluate this SQL statement: SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?    
    • A. 

      A. Selection, projection, join

    • B. 

      B. Difference, projection, join

    • C. 

      C. Selection, intersection, join

    • D. 

      D. Intersection, projection, join

    • E. 

      E. Difference, projection, product

  • 22. 
    Mark for review Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID                         NUMBER                      Primary Key FIRST_NAME                           VARCHAR2 (25) LAST_NAME                            VARCHAR2 (25) HIRE_DATE                              DATE   NEW_EMPLOYEES EMPLOYEE_ID                         NUMBER                      Primary Key NAME                                       VARCHAR2 (60) Which DELETE statement is valid?     
    • A. 

      A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);

    • B. 

      B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);

    • C. 

      C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name ='Carrey');

    • D. 

      D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name ='Carrey');

  • 23. 
     Mark for review. You need to design a student registration database that contains several tables storing academic information.   The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.   You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?     
    • A. 

      A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));

    • B. 

      B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

    • C. 

      C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));

    • D. 

      D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

  • 24. 
    The CUSTOMERS table has these columns: CUSTOMER_ID                         NUMBER (4)                 NOT NULL CUSTOMER_NAME                  VARCHAR2 (100)          NOT NULL CUSTOMER_ADDRESS            VARCHAR2 (150) CUSTOMER_PHONE                VARCHAR2 (20) You need to produce output that states "Dear Customer customer_name”. The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?     
    • A. 

      A. SELECT dear customer, customer_name, FROM customers;

    • B. 

      B. SELECT "Dear Customer", customer_name || ',' FROM customers;

    • C. 

      C SELECT 'Dear Customer ' || customer_name || ',' FROM customers;

    • D. 

      D SELECT 'Dear Customer ' || customer_name ',' FROM customers;

  • 25. 
     What is true about sequences?     
    • A. 

      A. Once created, a sequence belongs to a specific schema.

    • B. 

      B. Once created, a sequence is linked to a specific table.

    • C. 

      C. Once created, a sequence is automatically available to all users.

    • D. 

      D. Only the DBA can control which sequence is used by a certain table.

  • 26. 
    Procedures and functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?  
    • A. 

      A) when the transaction is committed

    • B. 

      B) during a data manipulation statement

    • C. 

      C) when it is explicitly invoked by another construct

    • D. 

      D) when an Oracle supplied package references the trigger

  • 27. 
     Why do stored procedures and functions improve performance? (Chose two)  
    • A. 

      A) They reduce network round trips.

    • B. 

      They postpone PL/SQL parsing until run time. c) c) They allow the application to perform high speed processing locally.

    • C. 

      D) They reduce the number of calls to the database and decrease network traffic by bundling commands.

    • D. 

      They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.

  • 28. 
      Which procedure of the DBMS_OUTPUT package would you use to append text to the current line of the output buffer?  
    • A. 

      A) GET

    • B. 

      B) GET_LINE

    • C. 

      C) PUT_TEXT_LINE

    • D. 

      D) PUT

  • 29. 
    • A. 

      A) DML trigger

    • B. 

      B) INSTEAD OF trigger

    • C. 

      C) Application trigger

    • D. 

      E) This is an invalid trigger.

  • 30. 
    . Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := get_budget (11); IF v_budget < 30000000 THEN set_budget (11, 300000000); END IF; END; For which reason would the recompilation of CALCULATE_BUDGET be unsuccessful?  
    • A. 

      A) a change to the code of SET_BUDGET

    • B. 

      B) a change to the code of GET_BUDGET

    • C. 

      C) a change to the arguments of SET_BUDGET or GET_BUDGET

    • D. 

      D) a change to the maximum length of the YEARLY_SALARY column of the STUDIO table

  • 31. 
    1. The Oracle Automatic Storage Management (ASM) feature of Oracle Database 10g provides a virtualization layer between the database and storage so that multiple disks can be treated as a single disk group and disks can be dynamically added or removed while keeping databases online.
    • A. 

      A. Server Virtualization

    • B. 

      B. Storage Virtualization

    • C. 

      C. Grid Computing

    • D. 

      D. Storage Management

  • 32. 
    1. Oracle uses Redologfile & Control file for databased recovery.
    • A. 

      A. True

    • B. 

      B. False

  • 33. 
    1. The user and server processes are separate, distinct processes. The separate server process created on behalf of each user process is called a dedicated server process (or shadow process), because this server process acts only on behalf of the associated user process.
    • A. 

      A. Dedicated Server Process

    • B. 

      B. Share Server

    • C. 

      C. Dispatcher

    • D. 

      D. Server process

  • 34. 
    1. Some architecture eliminates the need for a dedicated server process for each connection. A dispatcher directs multiple incoming network session requests to a pool of shared server processes. An idle shared server process from a shared pool of server processes picks up a request from a common queue, which means a small number of shared servers can perform the same amount of processing as many dedicated servers.
    • A. 

      A. Shared Server Architecture

    • B. 

      B. Dedicated Server Process

    • C. 

      C. Share Server

    • D. 

      D. Dispatcher

  • 35. 
    1. SELECT round(1350,-2) FROM dual
    What is the result?  
    • A. 

      A) 1350

    • B. 

      B) 1352

    • C. 

      C) 1400

    • D. 

      D) 1300

  • 36. 
    . Which is a major problem with SQL?  
    • A. 

      1. SQL cannot support object-orientation

    • B. 

      2. The same query can be written in many ways, each with vastly different execution plans.

    • C. 

      3. SQL syntax is too difficult for non-computer professionals to use

    • D. 

      4. SQL creates excessive locks within the Oracle database

  • 37. 
     Bulk binds improve performance by:  
    • A. 

      1. Improving the optimizer.

    • B. 

      2. Resetting the statistics in the catalog.

    • C. 

      3. Minimizing the number of context switches between the PL/SQL and SQL engines.

    • D. 

      4. Performing a Forward Prefetch.

  • 38. 
     With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?  
    • A. 

      1. SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

    • B. 

      2. SELECT FirstName='Peter', LastName='Jackson' FROM Persons

    • C. 

      3. SELECT * FROM Persons WHERE FirstName'Peter' AND LastName'Jackson'

    • D. 

      4. None

  • 39. 
    . (1Z0-007q195) Which of the following correctly shows the correct use of the TRUNC command on a date?  
    • A. 

      A. SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL;

    • B. 

      B. TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL;

    • C. 

      C. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

    • D. 

      . date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL

  • 40. 
     Mark for review. You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20. Which SQL statement would you use to create the view EMP_VU?    
    • A. 

      A. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20);

    • B. 

      B. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH READ ONLY;

    • C. 

      C. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH CHECK OPTION;

    • D. 

      D. CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20);]

    • E. 

      E. CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) NO UPDATE;

  • 41. 
      You execute this code: CREATE OR REPLACE PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34) IS v_seats_sold gross_receipt.seats_sold%TYPE; BEGIN null; END; Which statement is true?  
    • A. 

      A) The statement compiles, and the procedure is created.

    • B. 

      B) The statement complies with errors because there is no exception handling.

    • C. 

      C) The statement complies with errors because the body does not contain a SQL statement.

    • D. 

      D) The statement complies, and the procedure is stored as a data dictionary object in the database.

  • 42. 
      Which statement about  parameters is true?  
    • A. 

      A) You can assign a default value to an OUT parameter.

    • B. 

      B) An IN OUT parameter does not require a value before returning to the calling environment.

    • C. 

      C) You cannot assign a value to an IN parameter inside the procedure in which it is being used.

    • D. 

      D) You cannot change the value of an OUT parameter inside the procedure in which it is being used.

  • 43. 
     Examine this procedure: CREATE OR REPLACE PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER DEFAULT 0) IS BEGIN IF v_cost_per_ticket > 8.50 THEN SELECT cost_per_ticket INTO v_cost_per_ticket FROM gross_receipt WHERE movie_id = v_movie_id; END IF; END; Why does this statement fail when executed?  
    • A. 

      A) MOVIE_ID is not declared.

    • B. 

      B) The precision of V_MOVIE_ID was not specified.

    • C. 

      C) V_COST_PER_TICKET should be specified as OUT.

    • D. 

      The declaration of V_COST_PER_TICKET cannot have a DEFAULT value.

  • 44. 
     When creating the ADD_PROD procedure in SQL*Plus, you receive this message: Warning: Procedure created with compilation errors. What was saved to the data dictionary?  
    • A. 

      A) source code only

    • B. 

      B) compilation errors only

    • C. 

      C) source code and compilation errors

    • D. 

      D) nothing

  • 45. 
    Procedures and functions are very similar. For which reason would you choose a function over a procedure?  
    • A. 

      A) A function allows Oracle group functions.

    • B. 

      B) A function can be used in a SQL statement.

    • C. 

      C) A function guarantees read consistency, while a procedure cannot.

    • D. 

      D) A function can only be executed from a previously created procedure.

  • 46. 
    1. Which command will delete all data from a table and will not write to the rollback segment?
    • A. 

      A. Delete

    • B. 

      B. Drop

    • C. 

      C. Truncate

    • D. 

      D. Cascade

  • 47. 
    1. Which of the following in regards to view is correct
    • A. 

      A. View has it’s own data

    • B. 

      B. It is not a schema object

    • C. 

      C. It borrows data from base table(s)

    • D. 

      D. Complex view can be performed with DML operation always.

  • 48. 
    Which set of statements will successfully invoke this function within SQL*Plus?
    • A. 

      A. VARIABLE g_yearly_budget NUMBER EXECUTE g_yearly_budget := GET_BUDGET(11);

    • B. 

      B. VARIABLE g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

    • C. 

      C. VARIABLE :g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

    • D. 

      D. VARIABLE g_yearly_budget NUMBER :g_yearly_budget := GET_BUDGET(11);

  • 49. 
    You are about to add an argument to CALCULATE_BUDGET. What effect will this have?
    • A. 

      A. The GET_BUDGET function will be marked invalid and must be recompiled before the next execution.

    • B. 

      B. The SET_BUDGET function will be marked invalid and must be recompiled before the next execution.

    • C. 

      C. Only the CALCULATE_BUDGET procedure needs to be recompiled.

    • D. 

      D. All three procedures are marked invalid and must be recompiled.

  • 50. 
    1. Procedure and Functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
    • A. 

      A. When the transaction is committed

    • B. 

      B. When the associated event of the trigger takes place

    • C. 

      C. When an Oracle supplied package references the trigger

    • D. 

      D. During a data manipulation statement and when the transaction is committed