Oracle__8. Oracle 1Z0-051 Exam - View Objects Flashcards

1
Q

What happens to transactions if an ALTER VIEW is executed?

A

An implicit commit will be execute before the GRANT command.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Can a view be created upon a view?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

No

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

Yes.But do not know why someone would do it

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

The privileges remain the same as previous

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is left after a TRUNCATE table statement?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Are dates format sensitive when sorted?

A

Yes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

CURRVAL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What happens when the sequence object reached the MAXVALUE.

A

an additional request for a NEXTVAL will receive an error

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

ALTER SEQUENCE sequencename MAXVALUE newvalue

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

They are lost.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

DROP SEQUENCE sequencename

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

Nothing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is also created when a primary key is created?

A

A unique index.Primary keys cannot be duplicated

20
Q

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

A

YesThe private synonym takes precedence over the public synonym

21
Q

What is a composite index?

A

An index with 2 or more columns

22
Q

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

A

Only DML statements

23
Q

What happens to a SAVEPOINT after a commit?

A

It is deleted

24
Q

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;

A

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

25
Q

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

A

SELECT, INSERT, UPDATE and DELETE can be performed

26
Q

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

A

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

27
Q

What is the default minimum value of an ascending Sequence?

A

1

28
Q

What is the default sequence value of an ascending Sequence?

A

1

29
Q

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

A

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

30
Q

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

A

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

31
Q

What 4 privileges can be granted to a view?

A

DELETEINSERTSELECTUPDATE

32
Q

How are views stored in the data dictionary?

A

as SELECT statements

33
Q

What are the 2 classification of VIEWs?

A
  1. Simple2. Complex
34
Q

How many tables can be in a simple view?

A

only 1

35
Q

Can simple views contain functions or groupings?

A

no

36
Q

Can DML operations be performed on Complex VIEWs?

A

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

37
Q

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

A

OR REPLACE

38
Q

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

A

WITH CHECK OPTION CONSTRAINT

39
Q

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

A

WITH READ ONLY

40
Q

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

A

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

41
Q

What is an inline VIEW?

A

a subquery that is part of the FROM clause

42
Q

Can a view be created between tables in different schemas?

A

Yes

43
Q

Can a CHECK OPTION be added to a view?

A

YesCHECK OPTIONS are used for UPDATE and INSERT

44
Q

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

A

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

45
Q

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

A

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

46
Q

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

A

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

47
Q

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

A
  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