SQL – Creating Other Schema Objects


SQL – Creating Other Schema Objects Questions

”;


Previous

Next

1. Which database object among the following provides a layer of abstraction between the users and the data?

  1. Table
  2. Rows
  3. Views
  4. 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?

  1. Synonyms
  2. Views
  3. Tables
  4. 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?

  1. They are equal to tables
  2. They store data from one or many tables
  3. We can execute SELECT and other DMLs on Simple views
  4. 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)

  1. Because they have shorter names than tables
  2. To prevent users from accessing the columns of tables
  3. To simplify user SQL
  4. 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?

  1. View contains GROUP BY clause
  2. Base tables contain NOT NULL columns but not selected in the view query
  3. View query uses ROWNUM pseudocolumn
  4. 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?

  1. Tables from the same schema
  2. Tables from different schema
  3. Both A and B
  4. 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;
  1. 0
  2. 1
  3. 2
  4. 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?

  1. SELECT E.*, dept_name
FROM departments D join emp_details E
ON (E.department_id= D.dept_id);
  2. SELECT hire_date, job, salary, dept_name FROM emp_details
  3. This is not possible a view cannot be joined to other tables
  4. 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?

  1. Select dept_name, e.salary
FROM departments D join emp_details E
On (E.department_id= D.dept_id);
  2. Select dept_name, max(salary)
FROM departments D join emp_details E
On (E.department_id= D.dept_id)
Group by dept_name;
  3. View cannot appear in queries using group functions
  4. 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?

  1. PUBLIC and PRIVATE synonyms can have the same name for the same table
  2. DROP SYNONYM will remove a synonym
  3. DROP PUBLIC SYNONYM can be executed only by a SYSDBA
  4. 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 answer)

  1. A view can only be created from a table
  2. A view can only be created from one table
  3. A view can be created from one or many tables or views
  4. 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?

  1. CREATE TABLE system privilege
  2. CREATE VIEW system privilege
  3. ALTER VIEW system privilege
  4. 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?

  1. CREATE ANY VIEW
  2. CREATE VIEW
  3. Both A and B
  4. 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?

  1. LOBs
  2. Object types
  3. REF data types
  4. All of the above

Answer: D.

15. What among the following are different types of Views?

  1. Simple views
  2. Complex views
  3. Both A and B
  4. 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?

  1. DML statements can be issued most of the times against simple views
  2. There is only one source base table
  3. No group functions are used
  4. 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?

  1. DML statements cannot be issued against complex views
  2. Contain multiple base tables
  3. Aggregations cannot be performed
  4. 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?

  1. CREATE VIEW
  2. REPLACE VIEW
  3. CREATE OR REPLACE VIEW
  4. 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?

  1. As a WHERE clause
  2. As a CREATE statement
  3. As an UPDATE statement
  4. As a SELECT statement

Answer: D.

20.Which of the following can contain single-row functions?

  1. Inline Views
  2. Simple Views
  3. Complex Views
  4. 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?

  1. Composite View
  2. Simple View
  3. Complex View
  4. 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?

  1. Sub-queries can be embedded in a CREATE VIEW statement
  2. A sub-query used in the CREATE VIEW statement has to have a simple SELECT syntax
  3. You cannot use a WHERE clause in a sub-query when it is used in the CREATE VIEW statement
  4. 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?

  1. NOFORCE
  2. FORCE
  3. OR REPLACE
  4. 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?

  1. NOFORCE
  2. FORCE
  3. WITH READ ONLY
  4. 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?

  1. It creates a view even if the base table(s) does not exist.
  2. It creates a view only if the base table(s) exists.
  3. It is the default while creating a view.
  4. None of the above

Answer: B, C. 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.

26.What is true about the OR REPLACE keyword?

  1. Object privileges are lost when a view is created using this keyword
  2. There is no need of re granting the object privileges previously granted on it
  3. Neither of A nor B
  4. None of the above

Answer: B. 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.

27.What is true with respect to accessing the below view? (Assume the table structure given)

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 salVU100
AS SELECT employee_id  ID_NUMBER, last_name NAME, salary*12 ANNUAL_SAL
FROM employees E 
WHERE department_id= 100;
  1. The view has to be accessed by the original column names defined in the base table
  2. The view has to be accessed by the aliases given in the view query
  3. View is a simple view
  4. None of the above

Answer: B, C. View must refer the column alias if the view definition contains alias for the columns.

28.What is true with respect to accessing the below view? (Assume the table structure given)

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 salVU100 (ID_NUMBER, NAME, ANNUAL_SAL)
AS SELECT employee_id , last_name, salary*12 
FROM employees E 
WHERE department_id= 100;
  1. It is not mandatory that the number of aliases match the no. of expressions in the sub-query
  2. It is mandatory that the no. of aliases listed must match the no. of expressions selected in the sub-query
  3. It is mandatory to give aliases while creating a view
  4. None of the above

Answer: B. If the alias are specified in the view header, same number of columns must be selected in the SELECT query.

29. Consider the following statement and the given table structure:

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)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
CREATE OR REPLACE VIEW empVU100
(ID_NUMBER, NAME, ANNUAL_SAL, DEPT_ID)
AS 
SELECT employee_id , first_name ||'' ''|| last_name, salary, department_id
FROM employees
WHERE department_id= 100;

What is true about the column aliases as in the above query?

  1. Column aliases are listed in a random order as the columns in the sub-query
  2. Column aliases are listed in the same order as the columns in the sub-query
  3. Column aliases are mandatory while using the CREATE OR REPLACE keyword
  4. We cannot use concatenation when we use the CREATE OR REPLACE

Answer: B.

Consider the following statement and answer the questions 30 to 34 that follow:

CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) 
AS 
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name;

30.What can be said about the statement given above?

  1. Alternative names have been given for the view
  2. Giving alternative names is mandatory if any column is derived from a function or an expression
  3. Both A and B
  4. None of the above

Answer: C. Specifying alias name is good practice to improve the readability of the code and the view queries.

31.What will happen if the above statement is modified as below?

CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal)
AS 
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name;
  1. It will be no different than the original statement
  2. It will execute successfully giving the same results but change in alias names.
  3. It will throw an ORA error
  4. None of the above

Answer: B. The sequence of the column alias not matters much as they don”t carry any behavioral attribute.

32.Determine the output of the below DELETE statement.

DELETE FROM dept_sum_vu;
  1. It will delete the view
  2. It will remove all the rows from the view but the structure of the view will remain the same
  3. It will throw an ORA error
  4. None of the above

Answer: C. The view DEPT_SUM_VU is a complex view. DML operations cannot be performed on a complex view.

33.Suppose you modify the query given above to the following: 

CREATE OR REPLACE VIEW dept_sum_vu(name, sal) 
AS 
SELECT d.dept_name, e.salary 
FROM employees e JOIN departments d 
ON (e.department_id= d.dept_id)
Where rownum < 10;

What will be the impact of the modification?

  1. The view can be updated to update the values in EMPLOYEES and DEPARTMENTS tables
  2. Data in EMPLOYEES and DEPARTMENTS tables cannot be deleted through view
  3. Data in EMPLOYEES and DEPARTMENTS tables can be inserted through view
  4. A column can be added to EMPLOYEES table through the view

Answer: B. DML operations cannot be performed on complex views. DEPT_SUM_VU is a complex view as it joined multiple tables. DDL operations are not possible on views.

34.Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

  1. The rows will get removed without any error
  2. Only the first 10 rows will get removed
  3. The rows cannot be deleted.
  4. None of the above

Answer: C. The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.

35.When can the rows from a view be removed?

  1. Deletion of rows through a view is not possible
  2. It should be a simple view
  3. It should be a complex view
  4. None of the above

Answer: B. DML operations are possible only on simple views.

36.When can the data in a view not be modified?

  1. When there are group expressions
  2. When there is a GROUP BY clause
  3. When ROWNUM is used in the view query
  4. All of the above

Answer: D. UPDATE is not possible on a view containing group functions, pseudocolumns or DISTINCT keyword.

37. The JOB_HISTORY table is owned by a user “Andy”. Andy grants the SELECT privilege on the JOB_HISTORY table to another user “HR”. Which statement would create a synonym EMP_JOBS so that “HR” can execute the following query successfully?(Assume the structure of tables as given)

SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT * from EMP_JOBS;
  1. Andy issues – 
    CREATE SYNONYM EMP_JOBS for JOB_HISTORY
  2. HR issues – 
    CREATE SYNONYM EMP_JOBS for andy.JOB_HISTORY
  3. HR issues – 
    CREATE PUBLIC SYNONYM EMP_JOBS FOR andy.JOB_HISTORY
  4. None of the above

Answer: B. Only SYSDBA or a user with DBA privileges can create public synonyms.

38.Which keyword can assure that the DML operations performed on the view stay in the domain of the view?

  1. OR REPLACE
  2. CREATE
  3. WITH CHECK OPTION
  4. None of the above

Answer: C. The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don”t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause.

Consider the following table structure and the given statement and answer the questions 39 and 40 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 OR REPLACE VIEW empvu100
AS 
SELECT * FROM employees 
WHERE department_id= 100
WITH CHECK OPTION CONSTRAINT empvu100_ck;

39.What will the above statement do?

  1. It will allow the users to perform INSERT or UPDATE on all departments
  2. It will allow the user to perform INSERT or UPDATE any row which has department 100
  3. The user can UPDATE any row in the employees table
  4. The user can INSERT rows without any restriction in the employees table

Answer: B. The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don”t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause. An ORA error will be thrown if an INSERT or UPDATE will be executed on any row with a department_id other than 100.

40.Suppose you fire an UPDATE statement as shown below:

UPDATE empvu100
Set department_id = 200
Where employee_id  = 121;

What will be the outcome of this statement?

  1. No rows are updated
  2. An ORA error is thrown
  3. Both A and B
  4. None of the above

Answer: C. If the view with CHECK OPTION is updated and new record”s value violates the scope of the view, ORA exception “ORA-01402: view WITH CHECK OPTION where-clause violation” is raised.

41.What is true about the WITH CHECK CONSTRAINT?

  1. INSERTs or UPDATEs performed through the view cannot create rows that the view cannot select
  2. Only INSERTs performed through the view cannot create rows that the view cannot select
  3. Only UPDATEs performed through the view cannot create rows that the view cannot select
  4. None of the above

Answer: A.

42.How can you prevent DML operations on a View?

  1. By defining a WITH CHECK OPTION constraint
  2. By defining a WITH READ ONLY option
  3. Neither of A nor B
  4. None of the above

Answer: B. 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.

Consider the table structure and the given statement and answer the questions 43, 44 and 45 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 OR REPLACE empvu100(employee_id , first_name, job)
AS 
SELECT employee_id , last_name, job
FROM employees
WHERE department_id = 100
WITH READ ONLY;

43.What is true about the above statement?

  1. The view will not be created
  2. INSERT operation on this view an will throw an ORA error
  3. On UPDATING the rows for all the employees in department 100, an ORA error will be thrown
  4. None of the above

Answer: B, C. DML operations are not permitted on view which are created with READ ONLY option.

44.How many rows can be deleted from the view as shown above?

  1. All rows of the view
  2. All the rows of only the department 100
  3. No rows
  4. None of the above

Answer: C. DML operations are not permitted on view which are created with READ ONLY option.

45.Which of the following statements will drop the view created as above?

  1. DROP READ ONLY VIEW empvu100;
  2. DROP NOFORCE empvu100;
  3. DROP VIEW empvu100;
  4. None of the above

Answer: C. Read only view can be dropped using the DROP VIEW command.

46.What is true about dropping a View?

  1. The columns in the view from the base tables are also dropped
  2. The definition of the view is removed from the database
  3. Dropping of a view has no effect on the underlying base table
  4. None of the above

Answer: B, C.

47.Which of the following privileges should a user have to drop a view?

  1. CREATE ANY VIEW
  2. CREATE VIEW
  3. DROP ANY VIEW
  4. DROP VIEW

Answer: C.

48.What is true about sequences?

  1. It generates integers
  2. It is a shareable object
  3. Can be used to create a PRIMARY KEY value
  4. All of the above

Answer: D. A sequence speeds up the efficiency of accessing sequence values when cached in memory

49.What is true about a sequence?

  1. It is created when the Oracle Database is installed
  2. It is created by a user who has CREATE SEQUENCE system privilege
  3. It cannot be shared by more than two users
  4. It drops automatically when the database is disconnected.

Answer: B, C. CREATE SEQUENCE system privilege is required by a user to create a sequence in its own schema which cannot be shared by other users.

50.What among the following options is true about Sequences?

  1. The integers generated through a sequence for one table cannot be used by other tables
  2. A sequence can only be incremented
  3. A sequence becomes invalid if another sequence generates the same integers
  4. A sequence can be used by many tables and they can be incremented or decremented

Answer: D.

Consider the following statement and answer the questions 51 to 59 that follow:

CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 9999
NOCACHE
NOCYCLE;

51.What will be the first value generated by this sequence?

  1. 1
  2. 100
  3. 101
  4. 9999

Answer: C. The START WITH clause establishes the starting value for the sequence. Oracle 11g begins each sequence at 1 unless another value is specified in the START WITH clause.

52.What can be the last value generated by this sequence?

  1. 0
  2. 100
  3. 101
  4. 9999

Answer: D. The MINVALUE and MAXVALUE clauses establish a minimum or maximum value for the sequence.

53.What will be the 2nd value generated by this sequence?

  1. 102
  2. 100
  3. 99
  4. 9999

Answer: A. The INCREMENT BY clause specifies the interval between two sequential values. If the sequence is incremented by a positive value, the values the sequence generates are in ascending order. However, if a negative value is specified, the values the sequence generates are in descending order. If the INCREMENT BY clause isn”t included when the sequence is created, the default setting is used, which increases the sequence by one for each integer generated.

54.What will be the next value after the maximum integer 9999 is reached by this sequence?

  1. 101
  2. No value
  3. It will throw an ORA error
  4. None of the above

Answer: B. The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value.

55.How many values will Oracle pre allocate in memory based on the sequence given above?

  1. 20
  2. 0
  3. 100
  4. 9999

Answer: A.

56.You execute the below query:

SELECT dept_depid_seq.NEXTVAL from dual;

Assuming that the last value the sequence generated was 200, what will be the outcome of this query?

  1. 200
  2. 101
  3. 9999
  4. 201

Answer: D. The NEXTVAL pseudocolumn will generate the next unique integer of the sequence.

57.You execute the below query:

SELECT dept_depid_seq.CURRVAL from dual;

Assuming that the last value the sequence generated was 200, what will be the outcome of this query?

  1. 200
  2. 101
  3. 9999
  4. 201

Answer: A. The CURRVAL pseudocolumn will generate the current unique integer already generated by the sequence.

58.Suppose you need to change the start value of this sequence to 1000. Which of the following statements will help?

  1. ALTER dept_deptid_seq
INCREMENT BY 100
START WITH 1000
MAXVALUE 9999
NOCACHE
NOCYCLE;
  2. The sequence has to be dropped and re-created to start the sequence from 1000. 
  3. ALTER SEQUENCE dept_deptid_seq
START WITH 101
  4. ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
CYCLE;

Answer: B. Starting number of a sequence cannot be modified. Oracle raises the exception “ORA-02283: cannot alter starting sequence number”.

59.Suppose that the above sequence is altered as below:

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 99
NOCACHE
NOCYCLE;

What will be the outcome of this alteration?

  1. ORA error
  2. The maximum value for the altered sequence will now be 99
  3. Neither of A nor B
  4. None of the above

Answer: A. The MAXVALUE cannot be less than the START WITH value while altering a sequence.

60.When can we use the CYCLE option in Sequences?

  1. If we want to purge the old rows faster
  2. If we do not want to use the sequence to generate PRIMARY KEY values
  3. Both A and B
  4. None of the above

Answer: C. The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value. If the CYCLE option is specified and Oracle 11g reaches the maximum value for an ascending sequence or the minimum value for a descending sequence, the CYCLE option initiates the cycle of numbers again.

61.What is true about NEXTVAL pseudo column?

  1. It re-generates the CURRVAL of a sequence
  2. It returns the next available sequence value
  3. It can return duplicate values
  4. It generates the same values for different users

Answer: B. The pseudocolumn NEXTVAL (NEXT VALUE) is used to actually generate the sequence value. In other words, it calls the sequence object and requests the value of the next number in the sequence. After a value is generated, it”s stored in the CURRVAL (CURRENT VALUE) pseudocolumn so that you can reference it again.

62.What is true about CURRVAL pseudo column?

  1. CURRVAL can be used before NEXTVAL with respect to a sequence
  2. CURRVAL gives the current value of a sequence
  3. CURRVAL can give duplicate values
  4. None of the above

Answer: B.

63.When can NEXTVAL and CURRVAL be used?

  1. SET clause of an INSERT statement
  2. VALUES clause of an UPDATE statement
  3. The SELECT list of a SELECT statement that is not part of a sub-query
  4. The SELECT list of an INSERT statement

Answer: C, D. The sequence can be used in SELECT query, PL/SQL cursor or in IAS (INSERT-AS-SELECT)direct operations.

64.When can NEXTVAL and CURRVAL not be used?

  1. The SELECT list of a view
  2. The SELECT statement with the DISTINCT keyword
  3. A sub-query in SELECT, DELETE or UPDATE statement
  4. All of the above

Answer: D.

Consider the given statement and answer the questions 65 and 66 that follow:

CREATE TABLE employees 
(employee_id  NUMBER(4) DEFAULT emp_empid_seq.CURRVAL,
 department_id NUMBER(4));

65.What will be the outcome of this statement? (Assume that emp_empid_seq is sequence used to generate employee ID values)

  1. Table will be created
  2. The department_id column will have the values from the sequence generated for the employee ID
  3. The department_id column will have a DEFAULT value
  4. ORA error

Answer: D. Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

66.What will be the outcome of this statement if the CURRVAL is replaced with NEXTVAL? (Assume that emp_empid_seq is generated to generate employee ID values)

  1. Table will be created
  2. The department_id column will have the values from the sequence generated for the employee ID
  3. The department_id column will have a DEFAULT value
  4. ORA error

Answer: D. Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

Examine the given exhibit giving the structures of the tables Departments and Location. Answer the questions 67 and 68 that follow:

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)
SQL> desc locations
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 LOCATION_ID		 NOT NULL NUMBER(4)
 STREET_ADDRESS 		  VARCHAR2(40)
 POSTAL_CODE			  VARCHAR2(12)
 CITY			 NOT NULL VARCHAR2(30)
 STATE_PROVINCE 		  VARCHAR2(25)
 COUNTRY_ID			  CHAR(2)

67.You need to insert a new department named “HR” in the location ID 1000. Which of the following statements will give you the required results?

  1. INSERT INTO departments (dept_id, dept_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, ''HR'', 1000); 
  2. INSERT INTO departments (dept_id, dept_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, "HR", 1000); 
  3. INSERT INTO departments (dept_id, dept_name, location_id)
VALUES (dept_deptid_seq.CURRVAL, ''HR'', 1000);
  4. None of the above

Answer: A.The option C will cause a ”Unique constraint violation” as it will try to insert current value of department id which aleady exists in the DEPARTMENTS table.

68.Suppose you execute the below query before inserting the values as shown in the option A in question 67. What will be the outcome of the query?

SELECT dept_deptid_seq.CURRVAL FROM DUAL;
  1. ORA error
  2. It will give the current value of the sequence
  3. Neither of A nor B
  4. None of the above

Answer: B. When a user logs in to Oracle 11g, no value is initially stored in the CURRVAL pseudocolumn; the current value is NULL. After a NEXTVAL call has been issued to generate a sequence value, CURRVAL stores that value until the next value is generated. CURRVAL contains only the last value generated.

69.How can gaps occur in the values of a sequence?

  1. When a rollback occurs
  2. The system crashes
  3. A sequence is used in another table
  4. All of the above

Answer: D.

70.What is true about caching sequence values?

  1. Caching sequence values is not possible in Oracle
  2. The cache is populated when the maximum limit of the sequence is reached
  3. Caching starts the first time when the sequence is referred
  4. None of the above

Answer: C. If the NOCACHE option is specified when the sequence is created, each number is generated when the request is received. However, if an organization”s transactions require large amounts of sequential numbers throughout a session, the CACHE option can be used to have Oracle 11g generate a set of values ahead of time and store them in the server”s memory. Then, when a user requests a sequence value, the next available value is assigned-without Oracle 11g having to generate the number. On the other hand, if the CACHE option isn”t specified, Oracle 11g assumes a default option of CACHE 20 and stores 20 sequential values in memory automatically for users to access.

71.The following query for the sequence EMP_EMPID_SEQ is executed after a transaction which inserted five employee details.

Select emp_empID_seq.CURRVAL from dual;

Suppose the employee transaction rolled back. What will be the result of the above query?

  1. The sequence value at the starting of employee transaction
  2. NULL
  3. The sequence value at the end of employee transaction
  4. None of the above

Answer: C. Sequence values are unaffected by commit or rollback. If a transaction which uses sequence generator is rolled back, the sequence values are wasted and cannot be recovered.

72.Which of the following privileges are required to modify a sequence?

  1. CREATE OR REPLACE privilege
  2. ALTER privilege for the sequence
  3. ALTER TABLE privilege
  4. UPDATE privilege

Answer: B. To alter a sequence, the sequence must be in your own schema, or you must have the ALTER object privilege on the sequence, or you must have the ALTER ANY SEQUENCE system privilege.

73.What happens when a sequence is altered?

  1. The existing integers already generated by the sequence are altered as well
  2. Only the future integers are affected
  3. The sequence stops caching the future integers
  4. None of the above

Answer: B. By using the ALTER SEQUENCE command, any changes are applied only to values generated after the modifications are made.

74.Suppose you need to drop a sequence. Which of the following commands will help?

  1. ALTER SEQUENCE sequence_name START WITH NULL;
  2. DROP sequence_name;
  3. DROP SEQUENCE sequence_name;
  4. None of the above

Answer: C. The DROP command is used to drop a sequence

75.Which of the following privileges will allow you to drop a sequence? (Choose the most appropriate answer)

  1. ALTER SEQUENCE
  2. ALTER TABLE
  3. DROP SEQUENCE
  4. DROP ANY SEQUENCE

Answer: D. To drop a sequence, either the sequence must be in your own schema or you must have the DROP ANY SEQUENCE system privilege.

76.What is true about Indexes?

  1. Indexes are only manually created
  2. Indexes are only automatically created
  3. Both A and B
  4. None of the above

Answer: D. Indexes can be created manually as well as automatically following certain actions like creating a primary key or unqiue constraint.

77.Which of the following is used by an index to locate the data quickly?

  1. ROWNUM
  2. ROWID
  3. Sequence
  4. None of the above

Answer: B. An Oracle 11g index is a database object that stores a map of column values and the ROWIDs of matching table rows. A ROWID is the physical address of a table row.

78.What happens when there is no index on a column of a table?

  1. The data is located quickly
  2. There is a full table scan
  3. The table cannot be created
  4. The table cannot be altered

Answer: B.

79.What among the following is true about an Index?

  1. Index reduces the disk I/O
  2. Index locates data quickly
  3. Indexes are logically and physically independent of the table that they index
  4. All of the above

Answer: D.

80.What will happen if an index is dropped?

  1. The column on which the index is created, is dropped too
  2. The table on which the index is created, is dropped too
  3. Indexes once created cannot be dropped
  4. As Indexes are logically and physically independent objects, they can be dropped without affecting other objects

Answer: D. Indexes are the objects which are physically stored in schema. Dropping an index doesn”t impacts other objects.

81.What happens when a table is dropped?

  1. The indexes still remain as they are logically and independent objects
  2. The indexes in the table are also dropped
  3. Neither of A nor B
  4. None of the above

Answer: B.

82.How are indexes created automatically?

  1. When we create a table
  2. When a table is altered
  3. When a PRIMARY KEY is defined on a column (or group of columns) of a table
  4. When a UNIQUE KEY constraint is defined in the table definition

Answer: C, D.

83.For which of the following objects, a synonym can be created?

  1. Tables and views only
  2. Table, view and sequence
  3. Stored procedure, function, or package
  4. Synonym

Answer: B, C, D. The schema object for which you are creating the synonym can be of the following types:Table or object table, View or object view, Sequence, Stored procedure, function, or package, Materialized view, Java class schema object, User-defined object type, Synonym

84. Which of the following can you use to reference a table owned by another user?

  1. INDEX
  2. TABLE
  3. SYNONYMS
  4. SEQUENCES

Answer: C. A synonym is an alternative name or alias for a database object.

85.What among of the following is an example of a Non-unique index?

  1. PRIMARY KEY
  2. UNIQUE KEY
  3. FOREIGN KEY
  4. None of the above

Answer: C.

86.Which of the following is the main and basic type of an Index?

  1. Bitmap
  2. B-tree
  3. Unique
  4. Non-unique

Answer: A, B. The B-tree (balanced-tree) index is the most common index used in Oracle. You can create this type of index with a basic CREATE INDEX statement. A bitmap index varies in structure and use from a B-tree index. This index is useful for improving queries on columns that have low selectivity (low cardinality, or a small number of distinct values).

87.You need to speed up a query by creating an index on the FIRST_NAME of the EMPLOYEES table. Which of the following statements can you use? (Assume the table structure as shown)

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)
  1. CREATE INDEX emp_first_name_idx 
ON employees (first_name); 
  2. CREATE INDEX emp_first_name_idx 
ON employees first_name; 
  3. ALTER INDEX emp_first_name_idx 
ON employees (first_name);
  4. None of the above

Answer: A.

88.What does the UNIQUE keyword do while creating indexes?

  1. It specifies that the value of the column(s) upon which the index is created must be unique
  2. You cannot use the UNIQUE keyword when creating indexes
  3. It specifies that the index that is created can be used only by one table
  4. None of the above

Answer: A. A unique index is typically created automatically when a PRIMARY KEY or UNIQUE constraint is defined on a column. Unique indexes can also be explicitly created by including the UNIQUE keyword in the CREATE INDEX statement.

89.What will happen when you specify the keyword BITMAP while creating an Index?

  1. It creates the index with a bitmap for each distinct key.
  2. It does not create the index on each row separately
  3. Both A and B
  4. None of the above

Answer: C.

90.You have queries written which are expected to retrieve less than 2% to 4% of rows. Which of the following can be applied on the relevant tables to achieve the query performance of such query? (Choose the best answer)

  1. Indexes
  2. UNION set operator
  3. MINUS set operator
  4. None of the above

Answer: A. Indexes are the best way to achieve query performance. Heavy IO operations can be reduced and simplified using index scans.

91.In what scenarios can Indexes be useful?

  1. If the table(s) is very large
  2. If a column has less values
  3. If a column contains a large number of NULL values
  4. If a column has a wide range of values

Answer: C, D.

92.The table EMPLOYEES is updated frequently. When can Indexes be created on this table? (Choose the most appropriate answer)

  1. Indexes should not be created if a table is updated frequently
  2. Indexes should be created at the time when the table is created
  3. Neither of A nor B
  4. None of the above

Answer: A. Frequent or bulk DML operations on a table with an index add an overhead of maintaining the index segment, which might affect the DML operation performance.

93.Consider the following query and answer the following query. Assume that the EMPLOYEE_ID , DEPARTMENT_ID and FIRST_NAME columns of EMPLOYEES table are indexed. (Assume the table structure as given)

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)
SELECT first_name, last_name 
FROM employees 
WHERE comm IS NULL;

Will the existing indexes help in this case if there are 1 million rows in the table EMPLOYEES?

  1. Yes
  2. No
  3. It might help
  4. None of the above

Answer: B. Indexes are not used when the query predicates do not contain the columns on which the index is created.

94.Which of the following will remove an Index?

  1. DELETE FROM index_name; 
  2. DROP INDEX index_name; 
  3. DROP INDEX;
  4. None of the above

Answer: B. You must have the DROP ANY INDEX privilege to drop an index.



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *