Oracle Test 3

Reset the user's password

Grant create table privileges to the user

. Grant the CONNECT role to the user

. Unlock the user's account

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. 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. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence

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

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. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

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

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

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

D. to simplify the process of granting and revoking privileges

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

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

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

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

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

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

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

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

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

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

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

A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER),

B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

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. 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. 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. You must remove the existing view first, and then run the CRATE VIEW command with a new column list to modify a view.

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

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

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. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees

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

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

A. MAX

B. MIN

C. SUM

D. COUNT

E. TOTAL

F. LARGEST

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

A. SELECT TOTAL (*) FROM customers;

B. SELECT COUNT (*) FROM customers;

C. SELECT TOTAL (customer_id) FROM customer;

D. SELECT COUNT(customer_id) FROM customer;

E. SELECT COUNT(customers) FROM customers;

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

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

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

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;

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

A. all of the desired results

B. two of the desired results

C. one of the desired results

D. an error statement

E. create groups of data

F. sort data in a specific order

G. convert data to a different format

H. retrieve data based on an unknown condition

A. WHERE

B. HAVING

C. RESTRICT

D. GROUP BY

E. ORDER BY

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

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

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

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

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

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

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

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

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

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. 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. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

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

A. string

B. character

C. integer

E. numeric

F. translation

G. date

H. conversion

A. Selection, projection, join

B. Difference, projection, join

C. Selection, intersection, join

D. Intersection, projection, join

E. Difference, projection, product

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

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

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

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

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

A. SELECT dear customer, customer_name, FROM customers;

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

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

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

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

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

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

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

A) when the transaction is committed

B) during a data manipulation statement

C) when it is explicitly invoked by another construct

D) when an Oracle supplied package references the trigger

A) They reduce network round trips.

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

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

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

A) GET

B) GET_LINE

C) PUT_TEXT_LINE

D) PUT

A) DML trigger

B) INSTEAD OF trigger

C) Application trigger

E) This is an invalid trigger.

A) a change to the code of SET_BUDGET

B) a change to the code of GET_BUDGET

C) a change to the arguments of SET_BUDGET or GET_BUDGET

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

A. Server Virtualization

B. Storage Virtualization

C. Grid Computing

D. Storage Management

A. True

B. False

A. Dedicated Server Process

B. Share Server

C. Dispatcher

D. Server process

A. Shared Server Architecture

B. Dedicated Server Process

C. Share Server

D. Dispatcher

A) 1350

B) 1352

C) 1400

D) 1300

1. SQL cannot support object-orientation

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

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

4. SQL creates excessive locks within the Oracle database

1. Improving the optimizer.

2. Resetting the statistics in the catalog.

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

4. Performing a Forward Prefetch.

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

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

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

4. None

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

A) MOVIE_ID is not declared.

B) The precision of V_MOVIE_ID was not specified.

C) V_COST_PER_TICKET should be specified as OUT.

The declaration of V_COST_PER_TICKET cannot have a DEFAULT value.

A) source code only

B) compilation errors only

C) source code and compilation errors

D) nothing

A) A function allows Oracle group functions.

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

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

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

A. Delete

B. Drop

C. Truncate

D. Cascade

A. View has it’s own data

B. It is not a schema object

C. It borrows data from base table(s)

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

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

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

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

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

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

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

C. Only the CALCULATE_BUDGET procedure needs to be recompiled.

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

A. When the transaction is committed

B. When the associated event of the trigger takes place

C. When an Oracle supplied package references the trigger

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