Answers Final Exam Semester 2 Part 2 2013-2015 Oracle Academy English Database Programming with SQL - Student

Here you have Final Exam Semester 2 Part 1 at Oracle Academy. I hope this will help you.

PS: The test has just 50 question, but here you have more possible question.

1.         You need to create a new view on the EMPLOYEES table to update salary information for employees in Department 50. You need to ensure that DML operations through the view can not change salary values in other departments. Which clause should be included in the CREATE VIEW statement?                                                ·         FORCE ·         OR REPLACE ·         WITH READ ONLY ·         WITH CHECK OPTION (*)             [Correct]                     Correct             2.         You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE, or DELETE operations. Which of the following statements should you issue?                                      ·         CREATE VIEW sales_view AS (SELECT companyname, city, orderid, orderdate, total FROM customers, orders WHERE custid = custid) WITH READ ONLY;                      ·         CREATE VIEW sales_view (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total FROM customers c, orders o WHERE c.custid = o.custid) WITH READ ONLY;                      ·         CREATE VIEW sales_view AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total FROM customers c, orders o WHERE c.custid = o.custid);           ·         CREATE VIEW sales_view AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total FROM customers c, orders o WHERE c.custid = o.custid) WITH READ ONLY; (*)                                     [Incorrect]                  Incorrect. Refer to Section 11 Lesson 2.             3.         Which option would you use when creating a view to ensure that no DML operations occur on the view?                                                                       ·         FORCE ·         NOFORCE ·         WITH READ ONLY (*) ·         WITH ADMIN OPTION [Incorrect]                  Incorrect. Refer to Section 11 Lesson 2. 4.         Your manager has just asked you to create a report that illustrates the salary range of all the employees at your company. Which of the following SQL statements will create a view called SALARY_VU based on the employee last names, department names, salaries, and salary grades for all employees? Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.                                       ·         CREATE OR REPLACE VIEW salary_vu AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade" FROM employees e, departments d, job_grades j WHERE e.department_id equals d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal; ·         CREATE OR REPLACE VIEW salary_vu AS SELECT e.empid "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade" FROM employees e, departments d, job_grades j WHERE e.department_id = d.department_id NOT e.salary BETWEEN j.lowest_sal and j.highest_sal;             ·         CREATE OR REPLACE VIEW salary_vu AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade" FROM employees e, departments d, job_grades j WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal; (*)                   ·         CREATE OR REPLACE VIEW salary_vu AS (SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade" FROM employees emp, departments d, job grades j WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal);             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 2.                                                5.         You cannot insert data through a view if the view includes ______.             ·         A WHERE clause ·         A join ·         A column alias ·         A GROUP BY clause (*)             [Correct]                     Correct             6.         Which statement about performing DML operations on a view is true? ·         You can perform DML operations on simple views. (*) ·         You cannot perform DML operations on a view that contains the WITH CHECK OPTION clause. ·         You can perform DML operations on a view that contains the WITH READ ONLY option. ·         You can perform DML operations on a view that contains columns defined by expressions, such as COST + 1.                                                [Incorrect]                  Incorrect. Refer to Section 11 Lesson 2.             7.         You can create a view if the view subquery contains an inline view. True or False?             ·         True (*)           ·         False ·           [Incorrect]                  Incorrect. Refer to Section 11 Lesson 2.             8.         The CUSTOMER_FINANCE table contains these columns: CUSTOMER_ID NUMBER(9) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest. Which SELECT statement clause is included in your query?                                             ·         Inner query: ORDER BY new_balance DESC (*) ·         Inner query: WHERE ROWNUM = 800 ·         Outer query: ORDER BY new_balance DESC ·         Inner query: SELECT customer_id, new_balance ROWNUM             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 3. 9.         You must create a view that will display the name, customer identification number, new balance, finance charge, and credit limit of all customers. You issue this statement: CREATE OR REPLACE VIEW CUST_CREDIT_V AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit FROM customers c, accounts a WHERE c.account_id = a.account_id WITH READ ONLY; Which type of SQL command can be issued on the CUST_CREDIT_V view?                            ·         UPDATE ·         DELETE ·         INSERT ·         SELECT (*)             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 3.             10.       An inline view is an unnamed select statement found:        ·         In the user_views data dictionary view. ·         In a special database column of a users table. ·         Enclosed in parentheses within the select list of a surrounding query. ·         Enclosed in parentheses within the FROM clause of a surrounding query. (*)             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 3.             11.       Which statement about an inline view is true?                                             ·         An inline view is a schema object. ·         An inline view is a subquery in the FROM clause, often named with an alias. (*) ·         An inline view is a complex view. ·         An inline view can be used to perform DML operations.                                                [Correct]                     Correct             12.       You want to create a view based on the SALESREP table. You plan to grant access to this view to members of the Sales department. You want Sales employees to be able to update the SALESREP table through the view, which you plan to name SALESREP_VIEW. What should not be specified in your CREATE VIEW statement?                                    ·         The AS keyword ·         A WHERE clause ·         The IN keyword ·         A GROUP BY clause (*)                                                [Incorrect]                  Incorrect. Refer to Section 11 Lesson 3. 13.       You administer an Oracle database which contains a table named EMPLOYEES. Luke, a database user, must create a report that includes the names and addresses of all employees. You do not want to grant Luke access to the EMPLOYEES table because it contains sensitive data. Which of the following actions should you perform first? ·         Create a report for him. ·         Create a view. (*) ·         Create a subquery. ·         Create an index. [Correct]                     Correct             14.       You need to create a view that will display the name, employee identification number, first and last name, salary, and department identification number. The display should be sorted by salary from lowest to highest, then by last name and first name alphabetically. The view definition should be created regardless of the existence of the EMPLOYEES table. No DML may be performed when using this view. Evaluate these statements: CREATE OR REPLACE NOFORCE VIEW EMP_SALARY_V AS SELECT employee_id, last_name, first_name, salary, department_id FROM employees WITH READ ONLY; SELECT * FROM emp_salary_v ORDER BY salary, last_name, first_name; Which statement is true?                                     ·         When both statements are executed all of the desired results are achieved. ·         The CREATE VIEW statement will fail if the EMPLOYEES table does not exist. (*) ·         The statements will NOT return all of the desired results because the WITH CHECK OPTION clause is NOT included in the CREATE VIEW statement. ·         To achieve all of the desired results this ORDER ON clause should be added to the CREATE VIEW statement: 'ORDER ON salary, last_name, first_nameﾒ.             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 1. 15.       Evaluate this view definition: CREATE OR REPLACE VIEW part_name_v AS SELECT DISTINCT part_name FROM parts WHERE cost >= 45; Which of the following statements using the PART_NAME_V view will execute successfully?                                               ·         SELECT * FROM part_name_v; (*) ·         UPDATE part_name_v SET cost = cost * 1.23 WHERE part_id = 56990; ·         DELETE FROM part_name_v WHERE part_id = 56897; ·         INSERT INTO part_name_v (part_id, part_name, product_id, cost) VALUES (857986, ﾑcylinderﾒ, 8790, 3.45);  [Correct]                    Correct             16.       Which statement about the CREATE VIEW statement is true?                                                    ·         A CREATE VIEW statement CAN contain a join query. (*) ·         A CREATE VIEW statement CANNOT contain an ORDER BY clause. ·         A CREATE VIEW statement CANNOT contain a function. ·         A CREATE VIEW statement CANNOT contain a GROUP BY clause.                                                [Incorrect]                  Incorrect. Refer to Section 11 Lesson 1.             17.       Which of the following statements is a valid reason for using a view?                                                            ·         Views allow access to the data because the view displays all of the columns from the table. ·         Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*) ·         Views are used when you only want to restrict DML operations using a WITH CHECK OPTION. ·         Views are not valid unless you have more than one user.             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 1. 18.       Which keyword(s) would you include in a CREATE VIEW statement to create the view whether or not the base table exists?                                    ·         FORCE (*) ·         NOFORCE ·         OR REPLACE ·         WITH READ ONLY [Incorrect]                  Incorrect. Refer to Section 11 Lesson 1.             19.       A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?            ·         True ·         False (*)             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 1.             20.       Unique indexes are automatically created on columns that have which two types of constraints?           ·         NOT NULL and UNIQUE ·         UNIQUE and PRIMARY KEY (*) ·         UNIQUE and FOREIGN KEY ·         PRIMARY KEY and FOREIGN KEY             [Incorrect]                   Incorrect. Refer to Section 12 Lesson 2.             21.       You want to speed up the following query by creating an index: SELECT * FROM employees WHERE (salary * 12) > 100000; Which of the following will achieve this?                                    ·         Create a composite index on (salary,12). ·         Create a function-based index on (salary * 12). (*) ·         Create an index on (salary). ·         Create a function_based index on ((salary * 12) > 100000).             [Correct]                     Correct             22.       For which column would you create an index?                                                A column which has only 4 distinct values.             A column that is updated frequently             A column with a large number of null values (*)             A column that is infrequently used as a query search condition             [Incorrect]                  Incorrect. Refer to Section 12 Lesson 2.             23.       Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?                                    ·         CREATE index_name, table_name, uniqueness FROM user_indexes WHERE table_name = 'EMPLOYEES'; ·         SELECT index_name, table_name, uniqueness FROM 'EMPLOYEES'; ·         SELECT index_name, table_name, uniqueness FROM user_indexes WHERE table_name = 'EMPLOYEES'; (*) ·         SELECT index_name, table_name, uniqueness FROM user_indexes WHERE index = EMPLOYEES;  [Incorrect]                 Incorrect. Refer to Section 12 Lesson 2.             24.       The CLIENTS table contains these columns: CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY LAST_NAME VARCHAR2(15) FIRST_NAME VARCHAR2(10) CITY VARCHAR2(15) STATE VARCHAR2(2) You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement: CREATE INDEX clients ON address_index (city, state); Which result does this statement accomplish?                                                                        ·         An index named ADDRESS_INDEX is created on the CITY and STATE columns. ·         An index named CLIENTS is created on the CITY and STATE columns. ·         An index named CLIENTS_INDEX is created on the CLIENTS table. ·         An error message is produced, and no index is created. (*)  [Incorrect]                 Incorrect. Refer to Section 12 Lesson 2.             25.       To see the most recent value that you fetched from a sequence named "my_seq" you should reference:                                    ·         my_seq.nextval ·         my_seq.(currval) ·         my_seq.(lastval) ·         my_seq.currval (*)             [Incorrect]                  Incorrect. Refer to Section 12 Lesson 1.             26.       Evaluate this CREATE SEQUENCE statement: CREATE SEQUENCE line_item_id_seq INCREMENT BY -1; Which statement is true?             ·         The statement will not execute successfully. ·         The sequence will generate sequential descending values. (*) ·         The starting value of the LINE_ITEM_ID_SEQ sequence will by -1. ·         The minimum value of the LINE_ITEM_ID_SEQ will be the smallest possible integer value.             [Incorrect]                  Incorrect. Refer to Section 12 Lesson 1. 27.       Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?           ·         True (*) ·         False             [Correct]                     Correct             28.       Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True or False? ·         True (*) ·         False             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 3.             29.       _________________ are special characters that have a special meaning, such as a wildcard character, a repeating character, a non-matching character, or a range of characters. You can use several of these symbols in pattern matching. ·         Clip Art ·         Meta characters (*) ·         Alphanumeric values ·         Reference checks [Incorrect]                  Incorrect. Refer to Section 13 Lesson 3. 30.       When granting an object privilege, which option would you include to allow the grantee to grant the privilege to another user? ·         WITH GRANT OPTION (*) ·         WITH ADMIN OPTION ·         PUBLIC ·         FORCE             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.             31.       Which data dictionary view shows which system privileges have been granted to a user?            ·         USER_TAB_PRIVS ·         USER_SYS_PRIVS (*) ·         USER_SYSTEM_PRIVS ·         USER_SYSTEM_PRIVILEGES             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.             32.       Granting an object privilege WITH GRANT OPTION allows the recipient to grant all object privileges on the table to other users. True or False?                                    ·         True ·         False (*)             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.             33.       Which of the following best describes the purpose of the REFERENCES object privilege on a table?   ·         It allows a user's session to read from the table but only so that foreign key constraints can be checked. ·         It allows a user to refer to the table in a SELECT statement. ·         It allows a user to create foreign key constraints on the table. (*) ·         It allows the user to create new tables which contain the same data as the referenced table.                                                [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.             34.       User BOB's schema contains an EMPLOYEES table. BOB executes the following statement: GRANT SELECT ON employees TO mary WITH GRANT OPTION; Which of the following statements can MARY now execute successfully? (Choose two) (Choose all correct answers)                                                                     ·         SELECT FROM bob.employees; (*) ·         REVOKE SELECT ON bob.employees FROM bob; ·         GRANT SELECT ON bob.employees TO PUBLIC; (*) ·         DROP TABLE bob.employees;             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.             35.       Which statement would you use to remove an object privilege granted to a user? ·         ALTER USER ·         REVOKE (*) ·         REMOVE ·         DROP             [Correct]                     Correct             36.       User Kate wants to create indexes on tables in her schema. What privilege must be granted to Kate so that she can do this?                                             ·         CREATE INDEX ·         CREATE ANY INDEX ·         ALTER TABLE ·         None; users do not need extra privileges to create indexes on tables in their own schema. (*)             [Correct]                     Correct             37.       You want to grant privileges to user CHAN that will allow CHAN to update the data in the EMPLOYEES table. Which type of privileges will you grant to CHAN?                                      ·         User privileges ·         Object privileges (*) ·         System privileges ·         Administrator privileges [Incorrect]                   Incorrect. Refer to Section 13 Lesson 1. 38.       Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database?        ·         ALTER SESSION ·         CREATE SESSION (*) ·         OPEN SESSION ·         RESTRICTED SESSION             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 1.             39.       User SUSAN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the FIRST_NAME and LAST_NAME columns of EMPLOYEES. User RUDI needs to be able to access employees' names but no other data from EMPLOYEES. Which statement should SUSAN execute to allow this?                         ·         SELECT * FROM emp_view FOR rudi; ·         CREATE SYNONYM emp_view FOR employees; ·         GRANT SELECT ON emp_view TO rudi; (*) ·         GRANT SELECT ON emp_view ONLY TO rudi;                                                [Correct]                     Correct             40.       Which of the following best describes a role in an Oracle database?                                                                     ·         A role is a type of system privilege. ·         A role is the part that a user plays in querying the database. ·         A role is a name for a group of privileges. (*) ·         A role is an object privilege which allows a user to update a table.                         [Incorrect]                  Incorrect. Refer to Section 13 Lesson 1.             41.       Examine the following statements: UPDATE employees SET salary = 15000; SAVEPOINT upd1_done; UPDATE employees SET salary = 22000; SAVEPOINT upd2_done; DELETE FROM employees; You want to retain all the employees with a salary of 15000; What statement would you execute next?                         ·         ROLLBACK; ·         ROLLBACK TO SAVEPOINT upd1_done; (*) ·         ROLLBACK TO SAVEPOINT upd2_done; ·         ROLLBACK TO SAVE upd1_done; ·         There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.                                               [Incorrect]                  Incorrect. Refer to Section 14 Lesson 1. 42.       A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?            ·         An update statement ·         A savepoint (*) ·         An object privilege ·         A database link ·         A sequence             [Incorrect]                  Incorrect. Refer to Section 14 Lesson 1.             43.       You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task? ·         SELECT last_name, first_name, salary FROM employees WHERE salary > 25000 AND dept_id = 10; ·         SELECT last_name, first_name, salary FROM employees WHERE salary = 25000 AND dept_id = 10; ·         SELECT last_name, first_name, salary FROM employees WHERE salary <= 25000 AND dept_id = 10; ·         SELECT last_name, first_name, salary FROM employees WHERE salary != 25000 AND dept_id = 10; (*)             [Correct]                     Correct 44.       The CUSTOMERS and SALES tables contain these columns: CUSTOMERS CUST_ID NUMBER(10) PRIMARY KEY COMPANY VARCHAR2(30) LOCATION VARCHAR2(20) SALES SALES_ID NUMBER(5) PRIMARY KEY CUST_ID NUMBER(10) FOREIGN KEY TOTAL_SALES NUMBER(30) Which SELECT statement will return the customer ID, the company and the total sales?                        ·         SELECT c.cust_id, c.company, s.total_sales FROM customers c, sales s WHERE c.cust_id = s.cust_id (+); ·         SELECT cust_id, company, total_sales FROM customers, sales WHERE cust_id = cust_id; ·         SELECT c.cust_id, c.company, s.total_sales FROM customers c, sales s WHERE c.cust_id = s.cust_id; (*) ·         SELECT cust_id, company, total_sales FROM customers c, sales s WHERE c.cust_id = s.cust_id; [Incorrect]                  Incorrect. Refer to Section 15 Lesson 1.             45.       What is the minimum number of join conditions required to join 5 tables together?             ·         3 ·         4 (*) ·         5 ·         One more than the number of tables             [Incorrect]                  Incorrect. Refer to Section 15 Lesson 1.             46.       Evaluate this SQL statement: SELECT e.employee_id, e.last_name, e.first_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id AND employees.department_id > 5000 ORDER BY 4; Which clause contains a syntax error?                                                                        ·         SELECT e.employee_id, e.last_name, e.first_name, d.department_name ·         FROM employees e, departments d ·         WHERE e.department_id = d.department_id ·         AND employees.department_id > 5000 (*) ·         ORDER BY 4;             [Incorrect]                  Incorrect. Refer to Section 15 Lesson 1.             47.       You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?                         ·         An equijoin ·         It is not possible to join these two tables. ·         A non-equijoin (*) ·         A full outer join             [Incorrect]                  Incorrect. Refer to Section 15 Lesson 2.             48.       Which of the following best describes the function of an outer join?                                                                                    ·         An outer join will return only those rows that do not meet the join criteria. ·         An outer join will return only data from the far left column in one table and the far right column in the other table. ·         An outer join will return data only if both tables contain an identical pair of columns. ·         An outer join will return all rows that meet the join criteria and will return NULL values from one table if no rows from the other table satisfy the join criteria. (*)             [Correct]                     Correct 49.       Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?                                    ·         (+) (*) ·         * ·         + ·         =                                                [Incorrect]                  Incorrect. Refer to Section 15 Lesson 3.                                                                        50.       Unit testing may be a composite of many different possible cases, or approaches, a user would opt to execute a transaction. True or False?                         True (*)             False             [Incorrect]                  Incorrect. Refer to Section 16 Lesson 1. 51.       The CUSTOMER_FINANCE table contains these columns: CUSTOMER_ID NUMBER(9) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest. Which SELECT statement clause is included in your query?                                                            ·         Inner query: ORDER BY new_balance DESC (*) ·         Inner query: WHERE ROWNUM = 800 ·         Outer query: ORDER BY new_balance DESC ·         Inner query: SELECT customer_id, new_balance ROWNUM             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 3.             52.       Which of the following describes a top-N query?                            ·         A top-N query returns the bottom 15 records from the specified table. ·         A top-N query returns the top 15 records from the specified table. ·         A top-N query returns a result set that is sorted according to the specified column values. ·         A top-N query returns a limited result set, returning data based on highest or lowest criteria. (*)                                                [Correct]                     Correct             53.       Which statement about performing DML operations on a view is true?                                        ·         You can delete data in a view if the view contains the DISTINCT keyword. ·         You cannot modify data in a view if the view contains a WHERE clause. ·         You cannot modify data in a view if the view contains a group function. (*) ·         You can modify data in a view if the view contains a GROUP BY clause.                                                [Correct]                     Correct             54.       You cannot modify data in a view if the view contains ______.                                       ·         The DISTINCT keyword (*) ·         A WHERE clause ·         A subquery in the FROM clause ·         The WITH CHECK OPTION clause             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 2.             55.       Which of the following is TRUE regarding simple views? ·         Simple views retrieve data from many tables, so they typically contain joins. ·         Simple views contain functions or groups of data. ·         Simple views can be used to perform DML operations. (*) ·         Simple views are not stored in the Data Dictionary.                                                [Correct]                     Correct             56.       What is the purpose of including the WITH CHECK OPTION clause when creating a view?                                     ·         To make sure that the parent table(s) actually exist ·         To keep views form being queried by unauthorized persons ·         To make sure that data is not duplicated in the view ·         To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)                                                [Correct]                     Correct             57.       Which statement would you use to alter a view?     ·         ALTER VIEW ·         MODIFY VIEW ·         ALTER TABLE ·         CREATE OR REPLACE VIEW (*)             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 1.                                    58.       You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?                                          ·         You must create the SALES table before creating the view. ·         By default, the view will be created even if the SALES table does not exist. ·         You can create the table and the view at the same time using the FORCE option. ·         You can use the FORCE option to create the view before the SALES table has been created. (*)             [Correct]                     Correct 59.       In order to query a database using a view, which of the following statements applies?             ·         Use special VIEW SELECT keywords. ·         You can retrieve data from a view as you would from any table. (*) ·         You can never see all the rows in the table through the view. ·         The tables you are selecting from can be empty, yet the view still returns the original data from those tables.                                                [Incorrect]                  Incorrect. Refer to Section 11 Lesson 1.             60.       As user Julie, you issue this statement: CREATE SYNONYM emp FOR sam.employees; Which task was accomplished by this statement?                                                You created a public synonym on the EMP table owned by user Sam.             You created a private synonym on the EMPLOYEES table that you own.             You created a public synonym on the EMPLOYEES table owned by user Sam.             You created a private synonym on the EMPLOYEES table owned by user Sam. (*)                                                [Incorrect]                  Incorrect. Refer to Section 12 Lesson 2.             61.       You create a table named CUSTOMERS and define a PRIMARY KEY constraint on the CUST_ID column. Which actions occur automatically?                                               ·         A CHECK constraint is defined on the CUST_ID column. ·         A trigger is created that will prevent NULL values from being accepted in the CUST_ID column. ·         A unique index is created on the CUST_ID column, if one does not already exist. (*) ·         A sequence is created that will generate a unique value in the CUST_ID column for each row that is inserted into the CUSTOMERS table.                                                [Incorrect]                  Incorrect. Refer to Section 12 Lesson 2.             62.       Which of the following best describes the function of an index?                                       ·         An index can increase the performance of SQL queries that search large tables. (*) ·         An index can reduce the time required to grant multiple privileges to users. ·         An index can run statement blocks when DML actions occur against a table. ·         An index can prevent users from viewing certain data in a table.                                    [Correct]                     Correct 63.       You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement: ALTER TABLE manufacturers MODIFY (location_id NUMBER(6)); Which statement about the LOCATION_ID_SEQ sequence is true? ·         The sequence is unchanged. (*) ·         The sequence is deleted and must be recreated. ·         The current value of the sequence is reset to zero. ·         The current value of the sequence is reset to the sequence's START WITH value.             [Incorrect]                  Incorrect. Refer to Section 12 Lesson 1.             64.       The ALTER SEQUENCE statement can be used to:                                             ·         Change the START WITH value of a sequence ·         Change the maximum value to a lower number than was last used ·         Change the name of the sequence ·         Change the amount a sequence increments each time a number is generated (*)                                                [Correct]                     Correct             65.       User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?                                    ·         He is not required to take any action because, by default, all database users can automatically access views. ·         He should assign the SELECT privilege to all database users for the INVENTORY table.                     ·         He should assign the SELECT privilege to all database users for INVENTORY_V view. (*)     ·         He must grant each user the SELECT privilege on both the INVENTORY table and INVENTORY_V view.                                                           [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.             66.       Which of the following simplifies the administration of privileges? ·         An index ·         A view ·         A trigger ·         A role (*)                                                [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.                                                                        67.       Which keyword would you use to grant an object privilege to all database users?                                     ·         ADMIN ·         ALL ·         PUBLIC (*) ·         USERS             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2. 68.       To join a table in your database to a table on a second (remote) Oracle database, you need to use:                         ·         A remote procedure call ·         An Oracle gateway product ·         An ODBC driver ·         A database link (*)             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2.             69.       Which of the following are system privileges? (Choose two)  (Choose all correct answers)                                                            ·         CREATE TABLE (*) ·         UPDATE ·         CREATE SYNONYM (*) ·         INDEX             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 1.             70.       User ADAM has successfully logged on to the database in the past, but today he receives an error message stating that (although he has entered his password correctly) he cannot log on. What is the most likely cause of the problem?         ·         One or more object privileges have been REVOKEd from Adam. ·         ADAM's CREATE SESSION privilege has been revoked. (*) ·         ADAM's CREATE USER privilege has been revoked. ·         ADAM's user account has been removed from the database.             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 1.             71.       You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?                                         ·         CREATE USER jones IDENTIFIED BY mark; GRANT CREATE TABLE TO jones; ·         CREATE USER jones IDENTIFIED BY mark; GRANT CREATE SESSION TO jones; GRANT CREATE TABLE TO jones; (*) ·         GRANT CREATE SESSION TO jones; GRANT CREATE TABLE TO jones; ·         CREATE USER jones IDENTIFIED BY mark; GRANT CREATE SESSION TO jones; [Correct]                     Correct             72.       You want to grant user BOB the ability to change other users' passwords. Which privilege should you grant to BOB?                                    ·         The ALTER USER privilege (*) ·         The CREATE USER privilege ·         The DROP USER privilege ·         The CREATE PROFILE privilege                                                [Incorrect]                  Incorrect. Refer to Section 13 Lesson 1. 73.       Parentheses are not used to identify the sub expressions within the expression. True or False?                ·         True ·         False (*)             [Correct]                     Correct             74.       If a database crashes, all uncommitted changes are automatically rolled back. True or False?                                         ·         True (*) ·         False  [Incorrect]                 Incorrect. Refer to Section 14 Lesson 1.             75.       Evaluate this SELECT statement: SELECT p.player_id, m.last_name, m.first_name, t.team_name FROM player p LEFT OUTER JOIN player m ON (p.manager_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id); Which join is evaluated first?             ·         The self-join of the player table (*) ·         The join between the player table and the team table on TEAM_ID ·         The join between the player table and the team table on MANAGER_ID ·         The join between the player table and the team table on PLAYER_ID                         [Incorrect]                  Incorrect. Refer to Section 15 Lesson 3.             76.       The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table. The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display. Which type of join should you use to display the data?                                          ·         Natural join ·         Self-join ·         Outer join (*) ·         Equijoin             [Correct]                     Correct             77.       What happens when you create a Cartesian product?                      ·         All rows from one table are joined to all rows of another table (*) ·         The table is joined to itself, one column to the next column, exhausting all possibilities ·         The table is joined to another equal table ·         All rows that do not match in the WHERE clause are displayed                                                            [Incorrect]                  Incorrect. Refer to Section 15 Lesson 1. 78.       You have the following EMPLOYEES table: EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) ADDRESS VARCHAR2(35) CITY VARCHAR2(25) STATE VARCHAR2(2) ZIP NUMBER(9) TELEPHONE NUMBER(10) DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY The BONUS table includes the following columns: BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY ANNUAL_SALARY NUMBER(10) BONUS_PCT NUMBER(3, 2) EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?                         ·         SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct FROM employees e, bonus b WHERE e.employee_id = b.employee_id; (*)       ·         SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct FROM employees e, bonus b WHERE e.employee_id = b.employee_id; ·         SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct FROM employees, bonus WHERE e.employee_id = b.employee_id; ·         SELECT first_name, last_name, annual_salary * bonus_pct FROM employees, bonus NATURAL JOIN;                                                [Incorrect]                  Incorrect. Refer to Section 15 Lesson 1.                                                      79.       Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true? ·         The ON keyword must be included. ·         The JOIN keyword must be included. ·         The FROM clause represents the join criteria. ·         The WHERE clause represents the join criteria. (*) ·                                                        [Incorrect]                  Incorrect. Refer to Section 15 Lesson 1. 80.       Unit testing is done prior to a database going into production to ensure a random number of business requirements functions properly. True or False?                                    ·         True ·         False (*)                                    [Incorrect]                  Incorrect. Refer to Section 16 Lesson 1.             81.       Evaluate this CREATE VIEW statement: CREATE VIEW emp_view AS SELECT SUM(salary) FROM employees; Which statement is true?             ·         You cannot update data in the EMPLOYEES table using the EMP_VIEW view. (*) ·         You can update any data in the EMPLOYEES table using the EMP_VIEW view. ·         You can delete records from the EMPLOYEES table using the EMP_VIEW view. ·         You can update only the SALARY column in the EMPLOYEES table using the EMP_VIEW view.  [Incorrect]                 Incorrect. Refer to Section 11 Lesson 1. 82.       Which of the following keywords cannot be used when creating a view? ·         HAVING ·         WHERE ·         ORDER BY (*) ·         They are all valid keywords when creating views.                                                [Correct]                     Correct 83.       The EMP_HIST_V view is no longer needed. Which statement should you use to the remove this view?             ·         DROP emp_hist_v; ·         DELETE emp_hist_v; ·         REMOVE emp_hist_v; ·         DROP VIEW emp_hist_v; (*)             [Incorrect]                  Incorrect. Refer to Section 11 Lesson 3. 84.       Evaluate this statement: CREATE SEQUENCE line_item_id_seq MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE; What will be the first five numbers generated by this sequence?                                    ·         The fifth number cannot be generated. ·         130120110100130 ·         100110120130100 ·         The CREATE SEQUENCE statement will fail because a START WITH value was not specified. (*)             [Correct]                     Correct             85.       You issue this statement: ALTER SEQUENCE po_sequence INCREMENT BY 2; Which statement is true?                         ·         Sequence numbers will be cached. ·         Future sequence numbers generated will increase by 2 each time a number is generated. (*) ·         If the PO_SEQUENCE sequence does not exist, it will be created. ·         The statement fails if the current value of the sequence is greater than the START WITH value.                        [Incorrect]                  Incorrect. Refer to Section 12 Lesson 1.             86.       Which one of the following statements about indexes is true?                                           ·         An index is created automatically when a PRIMARY KEY constraint is created. (*) ·         An index must be created by a database administrator when a PRIMARY KEY constraint is created. ·         An index is never created for a unique constraint. ·         An index cannot be created before a PRIMARY KEY constraint is created.             [Incorrect]                  Incorrect. Refer to Section 12 Lesson 2. 87.       The EMPLOYEES table has an index named LN_IDX on the LAST_NAME column. You want to change this index so that it is on the FIRST_NAME column instead. Which SQL statement will do this?                                    ·         ALTER INDEX ln_idx ON employees(first_name); ·         ALTER INDEX ln_idx TO employees(first_name); ·         ALTER INDEX ln_idx TO fn_idx ON employees(first_name); ·         None of the above; you cannot ALTER an index. (*) [Incorrect]                  Incorrect. Refer to Section 12 Lesson 2.             88.       Evaluate this statement: CREATE PUBLIC SYNONYM testing FOR chan.testing; Which task will this statement accomplish?                                    ·         It recreates the synonym if it already exists. ·         It forces all users to access TESTING using the synonym. ·         It allows only the user CHAN to access TESTING using the synonym. ·         It eliminates the need for all users to qualify TESTING with its schema. (*)             [Correct]                     Correct             89.       What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?                                    ·         CREATE SYNONYM d_sum ON dept_sum_vu; ·         CREATE d_sum SYNONYM FOR dept_sum_vu; ·         UPDATE dept_sum_vu ON SYNONYM d_sum; ·         CREATE SYNONYM d_sum FOR dept_sum_vu; (*)                         [Correct]                     Correct 90.       You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?                         ·         A system privilege (*) ·         An object privilege ·         A user privilege ·         An access privilege              [Incorrect]                 Incorrect. Refer to Section 13 Lesson 1. 91.       You create a view named EMPLOYEES_VIEW on a subset of the EMPLOYEES table. User AUDREY needs to use this view to create reports. Only you and Audrey should have access to this view. Which of the following actions should you perform?         ·         Do nothing. As a database user, Audrey's user account has automatically been granted the SELECT privilege for all database objects. ·         GRANT SELECT ON employees_view TO public; ·         GRANT SELECT ON employees_view TO audrey; (*) ·         GRANT SELECT ON employees AND employees_view TO audrey;                                                [Incorrect]                  Incorrect. Refer to Section 13 Lesson 1.             92.       Which of the following are object privileges? (Choose two)  (Choose all correct answers)                       ·         SELECT (*) ·         DROP TABLE ·         CREATE TABLE ·         INSERT (*)             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 1.             93.       Which statement would you use to grant a role to users?                                    ·         GRANT (*) ·         ALTER USER ·         CREATE USER ·         ASSIGN              [Incorrect]                 Incorrect. Refer to Section 13 Lesson 2. 94.       Which statement would you use to add privileges to a role?           ·         CREATE ROLE ·         ALTER ROLE ·         GRANT (*) ·         ASSIGN             [Incorrect]                  Incorrect. Refer to Section 13 Lesson 2. 95.       Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?             ·         REGEXP_REPLACE ·         REGEXP_LIKE ·         REGEXP (*) ·         REGEXP_SUBSTR                                                [Correct]                     Correct             96.       Which SQL statement is used to remove all the changes made by an uncommitted transaction? ·         UNDO; ·         ROLLBACK; (*) ·         ROLLBACK TO SAVEPOINT; ·         REVOKEﾅ;              [Incorrect]                 Incorrect. Refer to Section 14 Lesson 1. 97.       Which statement about outer joins is true?               ·         The tables must be aliased. ·         The FULL, RIGHT, or LEFT keyword must be included. ·         The OR operator cannot be used to link outer join conditions. (*) ·         Outer joins are always evaluated before other types of joins in the query.              [Correct]                    Correct 98.       You have been asked to create a report that lists all corporate customers and all orders that they have placed. The customers should be listed alphabetically beginning with the letter 'A', and their corresponding order totals should be sorted from the highest amount to the lowest amount. Which of the following statements should you issue?         ·         SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount FROM customers c, orders o WHERE c.custid = o.custid ORDER BY amount DESC, companyname; ·         SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount FROM customers c, orders o WHERE c.custid = o.custid ORDER BY companyname, amount DESC; (*) ·         SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount FROM customers c, orders o WHERE c.custid = o.custid ORDER BY companyname, amount; ·         SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount FROM customers c, orders o WHERE c.custid = o.custid ORDER BY companyname ASC, amount ASC;  [Incorrect]                 Incorrect. Refer to Section 15 Lesson 1.   99.       Nonequijoins are normally used with which of the following? (Choose two)  (Choose all correct answers)             ·         Ranges of numbers (*) ·         Ranges of text ·         Ranges of dates (*) ·         Ranges of rowids ·         ranges of columns  [Incorrect]                 Incorrect. Refer to Section 15 Lesson 2.