Oracle__8. Oracle 1Z0-051 Exam - View Objects Flashcards
What happens to transactions if an ALTER VIEW is executed?
An implicit commit will be execute before the GRANT command.
Can a view be created upon a view?
Yes
Can a table and a view have the same name in the same schema?
No
Can a table and a synonym have the same name in the same schema?
Yes.But do not know why someone would do it
What happens with privileges with these 2 statements? DROP VIEW sales_vu; CREATE VIEW sales_vu as SELECT * FROM sales_tbl
Specific privileges to the view will be lost. This also means specific REVOKE privileges will also be lost.
What happens with privileges with this statement? CREATE OR REPLACE VIEW sales vu as SELECT * FROM sales_tbl
The privileges remain the same as previous
What happens to a VIEW if the underlying table is renamed?
The reference in the view is also changed to the new table name
What is the syntax for an insert statement if more than one record?
INSERT ALL INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …) INTO (field1, field2…) VALUES (exp1, exp2, …)SELECT * FROM dual;
What is the syntax of an INSERT statement where the field name list can be eliminated?
INSERT INTO table2 SELECT * FROM table1All the fieldname and data type must match and be in the same order between the 2 tables
What is left after a TRUNCATE table statement?
The table structure is left intact, but all data has been removed
Are dates format sensitive when sorted?
Yes.
What keyword from a SEQUENCE object refers to the last sequence number that has been generated?
CURRVAL
Are numbers generated from a sequence object only available for one table?
No.Sequence number are available to multiple tables or queries.
What happens when the sequence object reached the MAXVALUE.
an additional request for a NEXTVAL will receive an error
What statement can be used to increase the maximum value of a Sequence object?
ALTER SEQUENCE sequencename MAXVALUE newvalue
What happens with sequence number if any are used in a transaction and the transactions is rolled back?
They are lost.
What is the syntax to remove a sequence object from a database?
DROP SEQUENCE sequencename
What happens to a Sequence object when a table is dropped?
Nothing
What is also created when a primary key is created?
A unique index.Primary keys cannot be duplicated
Can a public synonym and a private synonym exist with the same name for the same table?
YesThe private synonym takes precedence over the public synonym
What is a composite index?
An index with 2 or more columns
Savepoint can be used to rollback DML statement or DDL statements or both?
Only DML statements
What happens to a SAVEPOINT after a commit?
It is deleted
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;
The second SAVEPOINT over writes the first SAVEPOINT because the savepoint name is the same.
What DML statements can be performed on this view? CREATE VIEW v3 AS SELECT * FROM sales WHERE cust_id = 2023 WITH CHECK OPTION
SELECT, INSERT, UPDATE and DELETE can be performed
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
only the SELECTThe GROUP BY clause in a create view does not allow inserts, updates or deletes
What is the default minimum value of an ascending Sequence?
1
What is the default sequence value of an ascending Sequence?
1
What is returned with this statement? SELECT na FROM promo ORDER BY 1 DESC UNION SELECT na FROM promo WHERE cat = ‘TV’
an error because the ORDER BY clause must the last statement for any set operators such as UNION
What is returned with this statement? SELECT na name1 FROM promo UNION SELECT na name2 FROM promo WHERE cat = ‘TV’ ORDER BY 1 DESC
The field name will be name1 and it will be sorted in descending order
What 4 privileges can be granted to a view?
DELETEINSERTSELECTUPDATE
How are views stored in the data dictionary?
as SELECT statements
What are the 2 classification of VIEWs?
- Simple2. Complex
How many tables can be in a simple view?
only 1
Can simple views contain functions or groupings?
no
Can DML operations be performed on Complex VIEWs?
not always. Group by complex view do not allow updates or deletes
What option can be used to replace a view without re-granting object privileges?
OR REPLACE
What option is added to view so that INSERTs and UPDATEs cannot be created which cannot be seen in the view?
WITH CHECK OPTION CONSTRAINT
What option is added to a view to make it read only?
WITH READ ONLY
What will the FORCE keyword do in a CREATE VIEW statement?
It will create the view regardless if the table or view exists.
What is an inline VIEW?
a subquery that is part of the FROM clause
Can a view be created between tables in different schemas?
Yes
Can a CHECK OPTION be added to a view?
YesCHECK OPTIONS are used for UPDATE and INSERT
Can any DML operation be performed on this view? SELECT VIEW sales AS SELECT prod FROM Products GROUP BY prod
No. The GROUP BY clause does not allow any DML operations.
Can a DML operation be performed on a VIEW that does not include all the NOT NULL columns from a table?
No.All NOT NULL columns must be included in a view before any DML operation can be performed.
Can a DML operation be performed on a VIEW that has a pseudo column ROWNUM keyword?
No.Pseudo columns in a VIEW will stop any DML operations from being performed on the table.
List 5 scenarios where a DML command cannot be used to update a view.
- VIEW has GROUP BY2. VIEW has DISTINCT3. VIEW has ROWNUM4. VIEW has expressions5. If the VIEW does not include all the NOT NULL columns
