ვის აქვს ჩაბარებული ეს გამოცდა ?
1. Which database object among the following provides a layer of abstraction between the users and the data?
Table
Rows
Views
Synonyms
Answer: C, D. Views and Synonyms do not store data themselves. A view is a temporary or virtual table used to retrieve data stored in underlying database tables.
2. Which of the following data base objects can generate serial numbers?
Synonyms
Views
Tables
Sequences
Answer: D. A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command.
3. What is true about views?
They are equal to tables
They store data from one or many tables
We can execute SELECT and other DMLs on Simple views
Views share the same namespace as tables and hence a table and a view cannot have the same name
Answer: C, D. DML operations aren't permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.
4. Why are views useful? (Choose the most appropriate answer)
Because they have shorter names than tables
To prevent users from accessing the columns of tables
To simplify user SQL
All of the above
Answer: B, C. A view is a temporary or virtual table used to retrieve data stored in underlying database tables. The view query must be executed each time the view is used. A view can be used to simplify queries or restrict access to sensitive data.
5. In which of the below scenarios, DML operations on a view are not possible?
View contains GROUP BY clause
Base tables contain NOT NULL columns but not selected in the view query
View query uses ROWNUM pseudocolumn
All of the above
Answer: D. DML operations aren't permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.
6. Where can views get their data from?
Tables from the same schema
Tables from different schema
Both A and B
None of the above
Answer: C.
Consider the given table structure and the following statement and answer the questions 7 to 9 that follow:
SQL> DESC employees
Name Null? Type
----------------------- -------- ----------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
CREATE VIEW emp_details AS
SELECT hire_date, job, salary, department_id FROM employees;
7. You issue the below query. How many columns will the user see as a result of the below query?
SELECT * FROM emp_details WHERE department_id= 100;
0
1
2
4
Answer: D. Since the view definition is based on four columns from the EMPLOYEES table, a query on a view with all column will show those four columns only.
8. You need to get the department name in addition to the above 4 columns. Which of the following query will give you the required results?
SELECT E.*, dept_name
FROM departments D join emp_details E
ON (E.department_id= D.dept_id);
SELECT hire_date, job, salary, dept_name FROM emp_details
This is not possible a view cannot be joined to other tables
None of the above
Answer: A. A view can be joined with other tables or views in a SELECT query.
9. You need to find the maximum salary along with the department name in addition to the 4 columns selected in the view. Which of the following query will give you the required results?
Select dept_name, e.salary
FROM departments D join emp_details E
On (E.department_id= D.dept_id);
Select dept_name, max(salary)
FROM departments D join emp_details E
On (E.department_id= D.dept_id)
Group by dept_name;
View cannot appear in queries using group functions
Select dept_name, max(salary)
FROM departments D join emp_details E
On (E.department_id= D.dept_id);
Answer: B.
10. What among the following is true about synonyms?
PUBLIC and PRIVATE synonyms can have the same name for the same table
DROP SYNONYM will remove a synonym
DROP PUBLIC SYNONYM can be executed only by a SYSDBA
None of the above
Answer: A, C. A synonym can be a private synonym, which users use to reference objects they own,or a public synonym, which users use to access another user's database objects. Only SYSDBA or a user with DBA privileges can create a public synonym.
11. What is true about creating a view? (Choose the most appropriate answer0
A view can only be created from a table
A view can only be created from one table
A view can be created from one or many tables or views
None of the above
Answer: C. A view containing expressions or functions or joining multiple tables is considered a complex view. A complex view can be used to update only one table.
12. Which of the following privileges are required to create views in one's own schema?
CREATE TABLE system privilege
CREATE VIEW system privilege
ALTER VIEW system privilege
CREATE ANY VIEW system privilege
Answer: B. CREATE VIEW privilege is required by a user to create a view in its own schema.
13. Which of the following privileges are required to create views in someone else's schema?
CREATE ANY VIEW
CREATE VIEW
Both A and B
None of the above
Answer: A. CREATE ANY VIEW privilege is required by a user to create a view in other user's schema.
14. Which of the following are supported for an object view or relational view?
LOBs
Object types
REF data types
All of the above
Answer: D.
15. What among the following are different types of Views?
Simple views
Complex views
Both A and B
None of the above
Answer: C. Simple and Complex views are two types of views. Simple views are based on a subquery that references only one table and doesn't include group functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.
16. What is true about a simple view?
DML statements can be issued most of the times against simple views
There is only one source base table
No group functions are used
All of the above
Answer: D. Simple views are based on a subquery that references only one table and doesn't include group functions, expressions, or GROUP BY clauses.
17. What is true about a complex view?
DML statements cannot be issued against complex views
Contain multiple base tables
Aggregations cannot be performed
All of the above
Answer: D. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.
18. Which keyword combination should be used to implicitly drop a view (if it exists) and create a new view with the same name?
CREATE VIEW
REPLACE VIEW
CREATE OR REPLACE VIEW
None of the above
Answer: C. The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view's previous version should be replaced with the one defined in the new command.
19. How is a view stored in the data dictionary?
As a WHERE clause
As a CREATE statement
As an UPDATE statement
As a SELECT statement
Answer: D.
20. Which of the following can contain single-row functions?
Inline Views
Simple Views
Complex Views
Composite Views
Answer: A, B. Single-row functions can be used in Inline as well as Simple views.
21. Which of the following can contain a group of data?
Composite View
Simple View
Complex View
None of the above
Answer: C. Complex view can use group function in the query.
22. What among the following is true about a View?
Sub-queries can be embedded in a CREATE VIEW statement
A sub-query used in the CREATE VIEW statement has to have a simple SELECT syntax
You cannot use a WHERE clause in a sub-query when it is used in the CREATE VIEW statement
None of the above
Answer: A. View definition can make use of sub-queries.
23. Which of the following can create a view even if the base table(s) does not exist?
NOFORCE
FORCE
OR REPLACE
CREATE VIEW
Answer: B. Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn't created.
24. Which of the following commands ensures that no DML operations can be performed on a view?
NOFORCE
FORCE
WITH READ ONLY
OR REPLACE
Answer: C. The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it's important that users can only query data, not make any changes to it.
25. What is true about the NOFORCE option in CREATE VIEW statement?
It creates a view even if the base table(s) does not exist.
It creates a view only if the base table(s) exists.
It is the default while creating a view.
None of the above
This post has been edited by pipii on 2 Oct 2014, 20:48
მიმაგრებული სურათი
1Z0-051 exam Oracle Database 11g: SQL Fundamental
