Oracle__8. Oracle 1Z0-051 Exam - View Objects

Question 1

What happens to transactions if an ALTER VIEW is executed?

Answer 1

An implicit commit will be execute before the GRANT command.

Question 2

Can a view be created upon a view?

Answer 2

Yes

Question 3

Can a table and a view have the same name in the same schema?

Answer 3

No

Question 4

Can a table and a synonym have the same name in the same schema?

Answer 4

Yes.But do not know why someone would do it

Question 5

What happens with privileges with these 2 statements? DROP VIEW sales_vu; CREATE VIEW sales_vu as SELECT * FROM sales_tbl

Answer 5

Specific privileges to the view will be lost. This also means specific REVOKE privileges will also be lost.

Question 6

What happens with privileges with this statement? CREATE OR REPLACE VIEW sales vu as SELECT * FROM sales_tbl

Answer 6

The privileges remain the same as previous

Question 7

What happens to a VIEW if the underlying table is renamed?

Answer 7

The reference in the view is also changed to the new table name

Question 8

What is the syntax for an insert statement if more than one record?

Answer 8

INSERT ALL INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …)SELECT * FROM dual;

Question 9

What is the syntax of an INSERT statement where the field name list can be eliminated?

Answer 9

INSERT INTO table2 SELECT * FROM table1All the fieldname and data type must match and be in the same order between the 2 tables

Question 10

What is left after a TRUNCATE table statement?

Answer 10

The table structure is left intact, but all data has been removed

Question 11

Are dates format sensitive when sorted?

Answer 11

Yes.

Question 12

What keyword from a SEQUENCE object refers to the last sequence number that has been generated?

Answer 12

CURRVAL

Question 13

Are numbers generated from a sequence object only available for one table?

Answer 13

No.Sequence number are available to multiple tables or queries.

Question 14

What happens when the sequence object reached the MAXVALUE.

Answer 14

an additional request for a NEXTVAL will receive an error

Question 15

What statement can be used to increase the maximum value of a Sequence object?

Answer 15

ALTER SEQUENCE sequencename MAXVALUE newvalue

Question 16

What happens with sequence number if any are used in a transaction and the transactions is rolled back?

Answer 16

They are lost.

Question 17

What is the syntax to remove a sequence object from a database?

Answer 17

DROP SEQUENCE sequencename

Question 18

What happens to a Sequence object when a table is dropped?

Answer 18

Nothing

Question 19

What is also created when a primary key is created?

Answer 19

A unique index.Primary keys cannot be duplicated

Question 20

Can a public synonym and a private synonym exist with the same name for the same table?

Answer 20

YesThe private synonym takes precedence over the public synonym

Question 21

What is a composite index?

Answer 21

An index with 2 or more columns

Question 22

Savepoint can be used to rollback DML statement or DDL statements or both?

Answer 22

Only DML statements

Question 23

What happens to a SAVEPOINT after a commit?

Answer 23

It is deleted

Question 24

What happens with this series of statements in a transaction? DELETE * FROM table1 WHERE field = 1 SAVEPOINT test DELETE * FROM table1 WHERE field = 2 SAVEPOINT test ROLLBACK TO test;

Answer 24

The second SAVEPOINT over writes the first SAVEPOINT because the savepoint name is the same.

Question 25

What DML statements can be performed on this view? CREATE VIEW v3 AS SELECT * FROM sales WHERE cust_id = 2023 WITH CHECK OPTION

Answer 25

SELECT, INSERT, UPDATE and DELETE can be performed

Question 26

What DML statements can be performed on this view? CREATE VIEW v3 as SELECT prod_id, prod_name, sum(qty) FROM sales GROUP BY prod_id, prod_name WITH CHECK OPTION

Answer 26

only the SELECTThe GROUP BY clause in a create view does not allow inserts, updates or deletes

Question 27

What is the default minimum value of an ascending Sequence?

Answer 27

1

Question 28

What is the default sequence value of an ascending Sequence?

Answer 28

1

Question 29

What is returned with this statement? SELECT na FROM promo ORDER BY 1 DESC UNION SELECT na FROM promo WHERE cat = 'TV'

Answer 29

an error because the ORDER BY clause must the last statement for any set operators such as UNION

Question 30

What is returned with this statement? SELECT na name1 FROM promo UNION SELECT na name2 FROM promo WHERE cat = 'TV' ORDER BY 1 DESC

Answer 30

The field name will be name1 and it will be sorted in descending order

Question 31

What 4 privileges can be granted to a view?

Answer 31

DELETEINSERTSELECTUPDATE

Question 32

How are views stored in the data dictionary?

Answer 32

as SELECT statements

Question 33

What are the 2 classification of VIEWs?

Answer 33

1. Simple2. Complex

Question 34

How many tables can be in a simple view?

Answer 34

only 1

Question 35

Can simple views contain functions or groupings?

Answer 35

no

Question 36

Can DML operations be performed on Complex VIEWs?

Answer 36

not always. Group by complex view do not allow updates or deletes

Question 37

What option can be used to replace a view without re-granting object privileges?

Answer 37

OR REPLACE

Question 38

What option is added to view so that INSERTs and UPDATEs cannot be created which cannot be seen in the view?

Answer 38

WITH CHECK OPTION CONSTRAINT

Question 39

What option is added to a view to make it read only?

Answer 39

WITH READ ONLY

Question 40

What will the FORCE keyword do in a CREATE VIEW statement?

Answer 40

It will create the view regardless if the table or view exists.

Question 41

What is an inline VIEW?

Answer 41

a subquery that is part of the FROM clause

Question 42

Can a view be created between tables in different schemas?

Answer 42

Yes

Question 43

Can a CHECK OPTION be added to a view?

Answer 43

YesCHECK OPTIONS are used for UPDATE and INSERT

Question 44

Can any DML operation be performed on this view? SELECT VIEW sales AS SELECT prod FROM Products GROUP BY prod

Answer 44

No. The GROUP BY clause does not allow any DML operations.

Question 45

Can a DML operation be performed on a VIEW that does not include all the NOT NULL columns from a table?

Answer 45

No.All NOT NULL columns must be included in a view before any DML operation can be performed.

Question 46

Can a DML operation be performed on a VIEW that has a pseudo column ROWNUM keyword?

Answer 46

No.Pseudo columns in a VIEW will stop any DML operations from being performed on the table.

Question 47

List 5 scenarios where a DML command cannot be used to update a view.

Answer 47

1. VIEW has GROUP BY2. VIEW has DISTINCT3. VIEW has ROWNUM4. VIEW has expressions5. If the VIEW does not include all the NOT NULL columns