Database Programming with SQL Final Exam - Oracle iLearning

(Answer all questions in this section)

1. A multi-table insert statement must have a subquery at the end of the statement. (True or False?) Mark for Review
(1) Points

True (*)

False

2. In a conditional multi-table insert, you can specify either __________ or __________. Mark for Review
(1) Points

All; First (*)

First; Second

All; Second

Null; Default

3. The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(25)
PROD_PRICE NUMBER(3)

You want to add the following row of data to the PRODUCTS table:

(1) a NULL value in the PROD_ID column
(2) "6-foot nylon leash" in the PROD_NAME column
(3) "10" in the PROD_PRICE column

You issue this statement:

INSERT INTO products
VALUES (null,'6-foot nylon leash', 10);

What row data did you add to the table?

 Mark for Review
(1) Points

The row was created with the correct data in two of three columns.

The row was created completely wrong. No data ended up in the correct columns.

The row was created with the correct data in one of the three columns.

The row was created with the correct data in all three columns. (*)

4. You need to add a row to an existing table. Which DML statement should you use? Mark for Review
(1) Points

UPDATE

CREATE

INSERT (*)

DELETE

5. Using your knowledge of the employees table, what would be the result of the following statement:
DELETE FROM employees; Mark for Review
(1) Points

The first row in the employees table will be deleted.

Deletes employee number 100.

All rows in the employees table will be deleted if there are no constraints on the table. (*)

Nothing, no data will be changed.
(Answer all questions in this section)

6. Which of the following statements best describes what will happen to the student table in this SQL statement?
UPDATE students
SET lunch_number =
    (SELECT lunch_number
     FROM student
     WHERE student_id = 17)
WHERE student_id = 19;

 Mark for Review
(1) Points

Inserts a new row into the students table.

Deletes student 17's lunch_number and inserts a new value from student 19.

Does nothing as you cannot use subqueries in UPDATE statements.

The statement updates the student_table by replacing student id 19's lunch number with student id 17's lunch number. (*)

7. You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(5) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
PHONE NUMBER(10)

Which DELETE statement will delete the appropriate record without deleting any additional records?

 Mark for Review
(1) Points

DELETE 'jones'
FROM employees;

DELETE FROM employees
WHERE employee_id = 348;
(*)

DELETE FROM employees
WHERE last_name = jones;

DELETE *
FROM employees
WHERE employee_id = 348;

8. Assuming there are no Foreign Keys on the EMPLOYEES table, if the following subquery returns one row, how many rows will be deleted from the EMPLOYEES table?
DELETE FROM employees
WHERE department_id =
     (SELECT department_id
     FROM departments
     WHERE department_name LIKE '%Public%');

 Mark for Review
(1) Points

No rows will be deleted.

All the rows in the EMPLOYEES table with department_ids matching the department_id returned by the subquery. (*)

One row will be deleted, as the subquery only returns one row.

All rows in the EMPLOYEES table will be deleted, regardless of their department_id.

Section 13
(Answer all questions in this section)

9. You can use the ALTER TABLE statement to: Mark for Review
(1) Points

Add a new column

Modify an existing column

Drop a column

All of the above (*)

10. You need to remove all the data in the SCHEDULE table, the structure of the table, and the indexes associated with the table. Which statement should you use? Mark for Review
(1) Points

ALTER TABLE

DELETE TABLE

TRUNCATE TABLE

DROP TABLE (*)
Section 13


11. Which statement about a column is NOT true? Mark for Review
(1) Points

You can convert a DATE data type column to a VARCHAR2 column.

You can convert a CHAR data type column to the VARCHAR2 data type.

You can increase the width of a CHAR column.

You can modify the data type of a column if the column contains non-null data. (*)


12. RENAME old_name to new_name can be used to: Mark for Review
(1) Points

Rename a row.

Rename a column.

Rename a table. (*)

All of the above.

13. When you use ALTER TABLE to add a column, the new column: Mark for Review
(1) Points

Becomes the last column in the table (*)

Becomes the first column in the table

Can be placed by adding a GROUP BY clause

Will not be created because you cannot add a column after the table is created

14. Examine the structure of the DONATIONS table.
DONATIONS:
PLEDGE_ID NUMBER
DONOR_ID NUMBER
PLEDGE_DT DATE
AMOUNT_PLEDGED NUMBER (7,2)
AMOUNT_PAID NUMBER (7,2)
PAYMENT_DT DATE

You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true?

 Mark for Review
(1) Points

You must drop and recreate the DONATIONS table to achieve these results.

Both changes can be accomplished with one ALTER TABLE statement. (*)

You must use the ADD OR REPLACE option to achieve these results.

You CANNOT decrease the width of the AMOUNT_PLEDGED column.

15. A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use? Mark for Review
(1) Points

TIMESTAMP

DATETIME

INTERVAL DAY TO SECOND (*)

INTERVAL YEAR TO MONTH
(Answer all questions in this section)

16. To store time with fractions of seconds, which datatype should be used for a table column? Mark for Review
(1) Points

TIMESTAMP (*)

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

DATE

17. Which statement about creating a table is true? Mark for Review
(1) Points

With a CREATE TABLE statement, a table will always be created in the current user's schema.

If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema. (*)

If no schema is explicitly included in a CREATE TABLE statement, the CREATE TABLE statement will fail.

If a schema is explicitly included in a CREATE TABLE statement and the schema does not exist, it will be created.


18. Once they are created, external tables are accessed with normal SQL statements. (True or False?) Mark for Review
(1) Points

True (*)

False

19. Examine this CREATE TABLE statement:
CREATE TABLE emp_load
(employee_number CHAR(5),
employee_dob CHAR(20),
employee_last_name CHAR(20),
employee_first_name CHAR(15),
employee_middle_name CHAR(15),
employee_hire_date DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY def_dir1
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(2),
      employee_dob CHAR(20),
      employee_last_name CHAR(18),
      employee_first_name CHAR(11),
      employee_middle_name CHAR(11),
      employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"))
LOCATION ('info.dat'));

What kind of table is created here?

 Mark for Review
(1) Points

An external table with the data stored in a file outside the database. (*)

A View.

An external table with the data stored in a file inside the database.

None. This is in invalid statement.

Section 14
(Answer all questions in this section)

20. Which constraint can only be created at the column level? Mark for Review
(1) Points

UNIQUE

FOREIGN KEY

CHECK

NOT NULL (*)

21. A table can only have one unique key constraint defined. True or False? Mark for Review
(1) Points

True

False (*)

22. A column defined as NOT NULL can have a DEFAULT value of NULL. True or False? Mark for Review
(1) Points

True

False (*)

23. Evaluate this statement
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;
For which task would you issue this statement?
 Mark for Review
(1) Points

To disable an existing constraint on the EMPLOYEES table

To activate the previously disabled constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index (*)

To add a new constraint to the EMPLOYEES table

To activate a new constraint while preventing the creation of a PRIMARY KEY index

24. You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use? Mark for Review
(1) Points

ALTER TABLE employees
ADD CONSTRAINT PRIMARY KEY (emp_id);

ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(emp_id); (*)

ALTER TABLE employees
MODIFY CONSTRAINT PRIMARY KEY (emp_id);

ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;

25. When creating a referential constraint, which keyword(s) identifies the table and column in the parent table? Mark for Review
(1) Points

ON DELETE SET NULL

ON DELETE CASCADE

REFERENCES (*)


FOREIGN KEY
(Answer all questions in this section)

26. The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table? Mark for Review
(1) Points

ON DELETE CASCADE

ON DELETE SET NULL

Neither A nor B (*)

Both A and B


Section 15
(Answer all questions in this section)

27. When you drop a view, the data it contains is also deleted. True or False? Mark for Review
(1) Points

True

False (*)


28. 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? Mark for Review
(1) Points

A WHERE clause

The AS keyword

A GROUP BY clause (*)

The IN keyword

29. You create a view on the EMPLOYEES and DEPARTMENTS tables to display salary information per department.
What will happen if you issue the following statement?
CREATE OR REPLACE VIEW sal_dept
AS SELECT SUM(e.salary) sal, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

 Mark for Review
(1) Points

A complex view is created that returns the sum of salaries per department. (*)

A simple view is created that returns the sum of salaries per department, sorted by department name.

A complex view is created that returns the sum of salaries per department, sorted by department id.

Nothing, as the statement contains an error and will fail.


30. Only one type of view exists. True or False? Mark for Review
(1) Points

True

False (*)
Section 15
(Answer all questions in this section)

31. 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. Mark for Review
(1) Points

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

32. A view can contain a select statement with a subquery. True or False? Mark for Review
(1) Points

True (*)

False

33. 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? Mark for Review
(1) Points

Create a view. (*)

Create a subquery.
Create an index.

Create a report for him.


34. Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False? Mark for Review
(1) Points

True

False (*)

Section 16
(Answer all questions in this section)

35. What would you create to make the following statement execute faster?
SELECT *
FROM employees
WHERE LOWER(last_name) = 'chang';

 Mark for Review
(1) Points

A synonym

An index, either a normal or a function_based index (*)

A composite index

Nothing; the performance of this statement cannot be improved.
Section 16
(Answer all questions in this section)

36. 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?

 Mark for Review
(1) Points

Create a composite index on (salary,12).

Create an index on (salary).

Create a function-based index on (salary * 12). (*)

Create a function_based index on ((salary * 12) > 100000).

37. Indexes can be used to speed up queries. True or False? Mark for Review
(1) Points

True (*)

False

38. Which pseudocolumn returns the latest value supplied by a sequence? Mark for Review
(1) Points

NEXT

CURRVAL (*)

CURRENT

NEXTVAL

39. In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement? Mark for Review
(1) Points
CACHE

MAXVALUE

NOCACHE (*)

40. Which of the following best describes the function of the NEXTVAL virtual column? Mark for Review
(1) Points


The NEXTVAL virtual column displays only the physical locations of the rows in a table.


The NEXTVAL virtual column displays the order in which Oracle retrieves row data from a table.


The NEXTVAL virtual column returns the integer that was most recently supplied by the sequence.


The NEXTVAL virtual column increments a sequence by a predetermined value. (*)
Section 17
(Answer all questions in this section)

41. The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME PRIVILEGE ADMIN_OPTION
USCA_ORACLE_SQL01_S08 CREATE VIEW NO
USCA_ORACLE_SQL01_S08 CREATE TABLE NO
USCA_ORACLE_SQL01_S08 CREATE SYNONYM NO
USCA_ORACLE_SQL01_S08 CREATE TRIGGER NO
USCA_ORACLE_SQL01_S08 CREATE SEQUENCE NO
USCA_ORACLE_SQL01_S08 CREATE DATABASE NO
 Mark for Review
(1) Points

role_tab_privs (lists table privileges granted to roles)

user_tab_privs_recd (lists object privileges granted to the user

role_sys_privs (lists system privileges granted to roles)

user_sys_privs (lists system privileges granted to the user) (*)


42. User CHANG has been granted SELECT, UPDATE, INSERT, and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still allowing him to read and modify existing rows. Which statement should you use to do this? Mark for Review
(1) Points

REVOKE ALL ON employees FROM chang;

REMOVE INSERT, DELETE ON employees FROM chang

REVOKE INSERT AND DELETE ON employees FROM chang;

REVOKE INSERT, DELETE ON employees FROM chang; (*)

43. REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?) Mark for Review
(1) Points

True (*)
False

44. Select the correct REGULAR EXPRESSION functions: (Choose two) Mark for Review
(1) Points

REGEXP_REPLACE, REGEXP_REFORM

REGEXP_INSTR, REGEXP_SUBSTR (*)

REGEXP_LIKE, REGEXP_REPLACE (*)

REGEXP_LIKE, REGEXP_NEAR

45. When a user is logged into one database, he is restricted to working with objects found in that database. True or False? Mark for Review
(1) Points
True
False (*)

46. To join a table in your database to a table on a second (remote) Oracle database, you need to use: Mark for Review
(1) Points

A database link (*)

An Oracle gateway product

A remote procedure call

An ODBC driver


47. User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table? Mark for Review
(1) Points
Yes
No (*)

Section 18
(Answer all questions in this section)

48. Which of the following best describes the term "read consistency"? Mark for Review
(1) Points

It prevents other users from querying a table while updates are being executed on it

It ensures that all changes to a table are automatically committed

It prevents users from querying tables on which they have not been granted SELECT privilege

It prevents other users from seeing changes to a table until those changes have been committed (*)

49. Which SQL statement is used to remove all the changes made by an uncommitted transaction? Mark for Review
(1) Points

ROLLBACK; (*)

ROLLBACK TO SAVEPOINT;

UNDO;

REVOKE;

Section 19
(Answer all questions in this section)

50. Unit testing is done prior to a database going into production to ensure a random number of business requirements functions properly. True or False? Mark for Review
(1) Points

True
False (*)

Oracle Ilearning for Database Design

Pelajar yang menyelesaikan Desain & Pemrograman Database Oracle Academy dengan kursus SQL dapat sebagai gantinya (atau juga) memilih untuk mempersiapkan dan mengikuti ujian sertifikasi SQL Server SQL Expert. Namun, ujian Oracle Database SQL Expert berisi topik tambahan yang akan membutuhkan lebih banyak belajar mandiri dan berlatih sendiri untuk mempersiapkan.



Membutuhkan penyelesaian yang berhasil sebelum Ujian Sertifikasi Oracle Database SQL Expert, Pengantar Oracle Ujian Sertifikasi SQL, atau Oracle Database : Ujian Sertifikasi SQL Fundamentals.



Di atas adalah soal dan jawaban kuis oracle ilearning - oracle academy, sebagai penambah ilmu pengetahuan untuk kita semua, yang pada intinya jangan lupa untuk dipahami dan dikuasai.

Pembahasan didalam Oracle Ilearning - Oracle Academy

• Oracle Ilearning for Database Design


• Oracle Ilearning for Course Resources


• Oracle Ilearning for Introduction


• Oracle Ilearning for Entities and Attributes


• Oracle Ilearning for Relationship Basics


• Oracle Ilearning for Super/Sub Types and Business Rules


• Oracle Ilearning for Relationship Fundamentals


• Oracle Ilearning for UIDs and Normalization


• Oracle Ilearning for Arcs, Hierarchies, and Recursive Modeling


• Oracle Ilearning for Changes and Historical Modeling


• Oracle Ilearning for Mapping


• Oracle Ilearning for Creating Database Projects


• Oracle Ilearning for Presenting Database Projects


• Oracle Ilearning quiz answers for Database Design English Quizzes and Exams


• Oracle Ilearning quiz answers for Database Design Midterm Exam


• Oracle ilearning quiz answers for Database Design Final Exam

Selain Sebagai Penyedia Panduan Belajar Database dan Tutorial Pemrograman, Kami Juga Membagikan Kumpulan Source Code Program Aplikasi dan Ebook Pemrograman Terlengkap yang Bisa Anda Dapatkan Secara Gratis di Halaman :