Oracle Test 3
Oracle questions
- 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
Correct Answer
C. . Grant the CONNECT role to the user -
- 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.
Correct Answer(s)
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 -
- 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.
Correct Answer(s)
B. B. to grant a group of related privileges to a user
D. D. to simplify the process of granting and revoking privileges -
- 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
Correct Answer
A. A. to find the tax percentage for each of the employees -
- 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)
Correct Answer
C. C. ALTER TABLE student_grades ADD CONSTRAINT 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.
Correct Answer
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; -
- 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
Correct Answer
B. B. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120); -
- 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;
Correct Answer
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; -
- 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
Correct Answer
C. C. SUM -
- 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);
Correct Answer
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); -
- 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;
Correct Answer(s)
B. B. SELECT COUNT (*) FROM customers;
D. D. SELECT COUNT(customer_id) FROM customer; -
- 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)
Correct Answer
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) -
- 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
Correct Answer
B. F. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120); -
- 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
Correct Answer
C. C. one of the desired results -
- 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
Correct Answer
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
Correct Answer
B. B. HAVING -
- 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
Correct Answer
C. C. 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;
Correct Answer
C. C. SELECT last_name, 12 * salary* (nvl(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.
Correct Answer(s)
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. -
- 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
Correct Answer(s)
B. B. character
D. E. numeric
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
Correct Answer
A. A. Selection, projection, join -
- 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');
Correct Answer
C. C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE 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));
Correct Answer
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;
Correct Answer
C. C 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.
Correct Answer
A. A. Once created, a sequence belongs to a specific schema. -
- 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
Correct Answer
B. B) during a data manipulation statement -
- 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.
Correct Answer
A. A) They reduce network round trips. -
- 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
Correct Answer
B. B) GET_LINE -
- 29.
Examine this code: CREATE OR REPLACE TRIGGER secure_emp BEFORE LOGON ON employees BEGIN IF (TO_CHAR(SYSDATE, ‘DY’) IN (‘SAT’, ‘SUN’)) OR (TO_CHAR(SYSDATE, ‘HH24:MI’) NOT BETWEEN ’08:00’ AND ’18:00’) THEN RAISE_APPLICATION_ERROR (-20500, ‘You may insert into the EMPLOYEES table only during business hours.’); END IF; END; / What type of trigger is it?
- A.
A) DML trigger
- B.
B) INSTEAD OF trigger
- C.
C) Application trigger
- D.
E) This is an invalid trigger.
Correct Answer
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
Correct Answer
C. C) a change to the arguments of SET_BUDGET or GET_BUDGET -
- 31.
- 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
Correct Answer
B. B. Storage Virtualization - 32.
- Oracle uses Redologfile & Control file for databased recovery.
- A.
A. True
- B.
B. False
Correct Answer
A. A. True - 33.
- 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
Correct Answer
A. A. Dedicated Server Process - 34.
- 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
Correct Answer
A. A. Shared Server Architecture - 35.
- SELECT round(1350,-2) FROM dual
- A.
A) 1350
- B.
B) 1352
- C.
C) 1400
- D.
D) 1300
Correct Answer
C. C) 1400 - 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
Correct Answer
A. 1. SQL cannot support object-orientation -
- 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.
Correct Answer
C. 3. Minimizing the number of context switches between the PL/SQL and SQL engines. -
- 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
Correct Answer
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
Correct Answer
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;
Correct Answer
C. C. CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH CHECK OPTION; -
- 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.
Correct Answer(s)
A. A) The statement compiles, and the procedure is created.
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.
Correct Answer
C. C) You cannot assign a value to an IN 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.
Correct Answer
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
Correct Answer
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.
Correct Answer
B. B) A function can be used in a SQL statement. -
- 46.
- 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
Correct Answer
C. C. Truncate - 47.
- 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.
Correct Answer
C. C. It borrows data from base table(s) - 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);
Correct Answer
B. B. VARIABLE g_yearly_budget NUMBER EXECUTE :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.
Correct Answer
C. C. Only the CALCULATE_BUDGET procedure needs to be recompiled. -
- 50.
- 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
Correct Answer
B. B. When the associated event of the trigger takes place
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Feb 03, 2013Quiz Edited by
ProProfs Editorial Team -
Nov 24, 2011Quiz Created by
Priyakl
Back to top