Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DSO SQL > Oracle__8. Oracle 1Z0-051 Exam - View Objects > Flashcards

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

DSO SQL (15 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions