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.