Unanswered questions

why packages? global variables loads to buffer memory thereby performance Dimensional databses - star and snowflake schema Fact and dimensional tables Index organized tables pipelined functions local and global indexes

FOR ALL Bulk Collect

create table test_ins (table_name varchar2(3000));
truncate table test_ins;
select * from test_ins;

declare

type nes_tab is table of user_tables.table_name%TYPE index by pls_integer;
nest_obj nes_tab;

cursor c1 is
select table_name from user_tables ;

begin

open c1;

loop

    fetch c1 bulk collect into nest_obj limit 5;

    forall i in 1..nest_obj.count save exceptions         
        insert into test_ins values(nest_obj(i));
        commit;
        exit when c1%notfound;   
end loop;

close c1;
exception when others then
dbms_output.put_line('Exception occurred :'||SQLERRM || SQLCODE);
end;
/

UNIX

awk command :
https://www.geeksforgeeks.org/awk-command-unixlinux-examples/

awk options 'selection _criteria {action }' input-file > output-file

Cursor Examples

set serveroutput on;

declare
v_tab user_tables.table_name%TYPE;
begin
    select table_name
    into v_tab
    from user_tables
    where tablespace_name = 'EXAMPLE';

    dbms_output.put_line('Table Names are : ' || v_tab);
   
    exception
        when too_many_rows then
        dbms_output.put_line('More than  1 rows are fetched');
       
        when others then
        dbms_output.put_line('Other Exception occurred');   
end;
/

--=============================================================================================
--simple for cursor
set serveroutput on;
declare
begin

for rec in  (
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE')

loop
    dbms_output.put_line(rec.table_name);
end loop;
end;
/
--===============================================================================================

--assigning data retrived from  for cursor into collection 
--1. Associate Arrays

set serveroutput on;
declare

--Associate arrays
TYPE ass_arr is table of user_tables.table_name %TYPE index by pls_integer;
ass_arr_obj ass_arr;

i number :=0;
begin

for rec in  (
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE')

loop
    ass_arr_obj(i):= rec.table_name;
    i:=i+1;
end loop;

dbms_output.put_line('Table Names are :');

for j in ass_arr_obj.first.. ass_arr_obj.last
loop
    dbms_output.put_line(ass_arr_obj(j));
end loop;
end;
/

2. Nested Tables

--Persistent collection - Can be stored in the databse and reused

CREATE OR REPLACE TYPE nes_tab is table of VARCHAR2(100);


declare

TYPE nes_tab is table of VARCHAR2(100);
nes_tab_val nes_tab := nes_tab(
                                'LOCATIONS',
                                'JOB_HISTORY',
                                'EMPLOYEES',
                                'JOBS',
                                'DEPARTMENTS',
                                'REGIONS');
                               
begin
dbms_output.put_line('The table names are :');

for i in 1..nes_tab_val.count
loop
    dbms_output.put_line(nes_tab_val(i));
end loop;
end;
/

3. VARRAYS


--Using cursors to fetch records by looping into a variable

set serveroutput on;
declare

v_tab user_tables.table_name%TYPE;

cursor tab_cur is
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE';
   
begin

open tab_cur;
   
    loop
        fetch tab_cur into v_tab;
            exit when tab_cur%notfound;
            dbms_output.put_line('Table Names are : ' || v_tab);   
    end loop;
close tab_cur;
   
   
    exception
        when too_many_rows then
        dbms_output.put_line('More than  1 rows are fetched');
       
        when others then
        dbms_output.put_line('Other Exception occurred');   
end;
/

--==================================================================================
--Using cursors with bulkcollect and collection

set serveroutput on;
declare

TYPE v_col_tab IS TABLE OF user_tables.table_name%TYPE index by PLS_INTEGER;
v_tab v_col_tab;

cursor tab_cur is
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE';
   
begin

open tab_cur;
    fetch tab_cur bulk collect into v_tab;
    dbms_output.put_line('Table Names are :');
    for i in 1..v_tab.count   
    loop
        dbms_output.put_line( v_tab(i));   
    end loop;
close tab_cur;
   
   
    exception
        when too_many_rows then
        dbms_output.put_line('More than  1 rows are fetched');
       
        when others then
        dbms_output.put_line('Other Exception occurred');   
end;
/

Exception Handling

Examples :

--User defined exception

--1. Variable -- By using EXCEPTION datatype
set serveroutput on;
declare
a number := 1;
 new_exp exception;
begin
if (a = 1) then
raise new_exp;
end if;
exception
when new_exp then
dbms_output.put_line('New_exp occured');
when others then
dbms_output.put_line('Unknown Exception Occurred');
end;
/

--2. Function - raise_application_error()

declare
a number :=1;
begin
if ( a=1) then
raise_application_error(-20000,'Error Message');
end if;
exception when others then
dbms_output.put_line('Exception Occurred:' || SQLERRM || SQLCODE);
end;
/

3. PRAGMA EXCEPTION  INIT -- To assign exception name with error number
--used when more than 1 user defined exception

set serveroutput on;
declare

my_exp EXCEPTION ;
PRAGMA EXCEPTION_INIT(my_exp,-20009);
a number :=1;
begin
if (a=1) then
--raise my_exp;
raise_application_error(-20009,'User defined excecption text');
end if;
exception when my_exp then
dbms_output.put_line('Excecption Occurred'||SQLCODE||SQLERRM);
end;
/

IZ0-071

https://www.itexams.com/exam/1z0-071

01. Evaluate the following ALTER TABLE statement:

ALTER TABLE orders SET UNUSED order_date;

Which statement is true?

a) The DESCRIBE command would still display the ORDER_DATE column.

b) ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

c) The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.

d) After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. 

02. Examine the business rule: Each student can take up multiple projects and each project can have multiple students. You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:

STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK

Which two statements are true in this scenario?

a) The ERD must have a1:M relationship between the students and projects entitles.

b) The ERD must have a M:M relationship between the students and projects entities that must be resolved into 1:M relationships.

c) STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.

d) PROJECT_ID must be the primary key in the projects entity and foreign key in the STUDENTS entity.

e) An associative table must be created with a composite key of STUDENT_ID andPROJECT_ID; which is the foreign key linked to theSTUDENTSandPROJECTSentities.

03. The first DROP operation is performed on PRODUCTS table using the following command:

DROP TABLE products PURGE;

Then you performed the FLASHBACK operation by using the following command:

FLASHBACK TABLE products TO BEFORE DROP;

Which statement describes the outcome of the FLASHBACK command?

a) It recovers only the table structure.

b) It recovers the table structure, data, and the indexes.

c) It recovers the table structure and data but not the related indexes.

d) It is not possible to recover the table structure, data, or the related indexes.

04. The following are the steps for a correlated subquery, listed in random order:

1) The WHERE clause of the outer query is evaluated.

2) The candidate row is fetched from the table specified in the outer query.

3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.

4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.

Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.

a) 4,2,1,3

b) 4,1,2,3

c) 2,4,1,3

d) 2,1,4,3

05. The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:

GRANT ALL ON orders, order_items TO PUBLIC;

What correction needs to be done to the above statement?

a) PUBLIC should be replaced with specific usernames.

b) ALL should be replaced with a list of specific privileges.

c) WITH GRANT OPTION should be added to the statement.

d) Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

06. Which statement correctly grants a system privilege?

a) GRANT EXECUTE ON prod TO PUBLIC;

b) GRANT CREATE VIEW ON tablel TO used;

c) GRANT CREATE TABLE TO used ,user2;

d) GRANT CREATE SESSION TO ALL;

07. Which statement is true regarding external tables?

a) The default REJECT LIMIT for external tables is UNLIMITED.

b) The data and metadata for an external table are stored outside the database.

c) ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

d) The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.

08. Which three statements are true regarding the data types?

a) Only one LONG column can be used per table.

b) ATIMESTAMP data type column stores only time values with fractional seconds.

c) The BLOB data type column is used to store binary data in an operating system file.

d) The minimum column width that can be specified for a varchar2 data type column is one.

e) The value for a CHAR data type column is blank-padded to the maximum defined column width.

09. You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products; What is the implication of this command? (Choose all that apply.)

a) All data in the table are deleted but the table structure will remain

b) All data along with the table structure is deleted

c) All viewsand synonyms will remain but they are invalidated

d) The pending transaction in the session is committed

e) All indexes on the table will remain but they are invalidated

10. You want to display 5 percent of the rows from the sales table for products with the lowestAMOUNT_SOLD and also want to include the rows that have the sameAMOUNT_SOLDeven if this causes the output to exceed 5 percent of the rows.

Which query willprovide the required result?

a) SELECT prod_id, cust_id, amount_sold FROM sales

ORDER BY amount_sold

FETCH FIRST 5 PERCENT ROWS WITH TIES;

b) SELECT prod_id, cust_id, amount_sold FROM sales

ORDER BY amount_sold

FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

c) SELECT prod_ id, cust_id, amount_sold FROM sales

ORDER BY araount_sold

FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;

d) SELECT prod_id, cust_id, amount_sold FROM sales

ORDER BY amount sold

FETCH FIRST 5 PERCENT ROWS ONLY;

QUESTION: 01 Answer: d	QUESTION: 02 Answer: d, e	QUESTION: 03 Answer: d	QUESTION: 04 Answer: c	QUESTION: 05 Answer: d
QUESTION: 06 Answer: c	QUESTION: 07 Answer: d	QUESTION: 08 Answer: a, d, e	QUESTION: 09 Answer: b, c, d	QUESTION: 10 Answer: a

Question 1

Examine the structure of the invoice table.

Which two SQL statements would execute successfully?
A)

B)

C)

D)

·         A. Option A

·         B. Option B

·         C. Option C

·         D. Option D

Expose Correct Answer

Answer : C

Next Question

Question discussion

Top of Form

SUBMIT

Bottom of Form

Question 2

Which statement is true regarding the INTERSECT operator?

·         A. It ignores NULL values

·         B. The number of columns and data types must be identical for all SELECT statements in the query

·         C. The names of columns in all SELECT statements must be identical

·         D. Reversing the order of the intersected tables the result

Expose Correct Answer

Answer : B

Explanation: INTERSECT Returns only the rows that occur in both queries result sets, sorting them and removing duplicates. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

Next Question

Question discussion

Top of Form

SUBMIT

Bottom of Form

Question 3

Which task can be performed by using a single Data Manipulation Language (OML)
statement?

·         A. adding a column constraint when inserting a row into a table

·         B. adding a column with a default value when inserting a row into a table

·         C. removing all data only from one single column on which a unique constraint is defined

·         D. removing all data only from one single column on which a primary key constraint is defined

Expose Correct Answer

Answer : D

Next Question

Question discussion

Top of Form

SUBMIT

Bottom of Form

Question 4

You want to display the date for the first Monday of the next month and issue the following
command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first
Monday for"fmmonth rrrr') FROM DUAL;
What is the outcome?

·         A. It executes successfully and returns the correct result.

·         B. It executes successfully but does not return the correct result.

·         C. It generates an error because TO_CHAR should be replaced with TO_DATE.

·         D. It generates an error because rrrr should be replaced by rr in the format string.

·         E. It generates an error because fm and double quotation marks should not be used in the format string.

Expose Correct Answer

Answer : A

Explanation: NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.

Next Question

Question discussion

Top of Form

SUBMIT

Bottom of Form

Question 5

Which statements are true? (Choose all that apply.)

·         A. The data dictionary is created and maintained by the database administrator.

·         B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.

·         C. The usernames of all the users including the database administrators are stored in the data dictionary.

·         D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.

·         E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.

·         F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary

Expose Correct Answer

Answer : C,D,F

Next Question

Question 1

Which three statements are true about the ALTER TABLE ----DROP COLUMN
.....command?

·         A. A column can be dropped only if it does not contain any data.

·         B. A column can be dropped only if another column exists in the table.

·         C. A dropped column can be rolled back.

·         D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

·         E. A parent key column in the table cannot be dropped.

Expose Correct Answer

Answer : C

Next Question

Question discussion

You need to signup or login to add a comment

Question 2

Examine the following query:

What is the output of this query?

·         A. It displays 5 percent of the products with the highest amount sold.

·         B. It displays the first 5 percent of the rows from the SALES table.

·         C. It displays 5 percent of the products with the lowest amount sold.

·         D. It results in an error because the ORDER BY clause should be the last clause.

Expose Correct Answer

Answer : C

Next Question

Question discussion

You need to signup or login to add a comment

Question 3

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table
where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has
the value 102088. You executed the following SQL statement:
SELECT product_name, list_price
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?

·         A. It would execute but the output would return no rows.

·         B. It would execute and the output would display the desired result.

·         C. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.

·         D. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.

Expose Correct Answer

Answer : A

Next Question

Question discussion

You need to signup or login to add a comment

Question 4

Which statements are true regarding the WHERE and HAVING clauses in a SELECT
statement?
(Choose all that apply.)

·         A. The HAVING clause can be used with aggregate functions in subqueries.

·         B. The WHERE clause can be used to exclude rows after dividing them into groups.

·         C. The WHERE clause can be used to exclude rows before dividing them into groups.

·         D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.

·         E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

Expose Correct Answer

Answer : A,C

Next Question

Question discussion

You need to signup or login to add a comment

Question 5

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the
ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

·         A. DELETE order_id FROM orders WHERE order_total < 1000;

·         B. DELETE orders WHERE order_total < 1000;

·         C. DELETE FROM orders WHERE (SELECT order_id FROM order_items);

·         D. DELETE orders o, order_items i WHERE o.order id = i.order id;

Expose Correct Answer

Answer : B

Next Question

Question discussion

You need to signup or login to add a comment

Question 6

Which two statements are true regarding constraints? (Choose two.)

·         A. A foreign key cannot contain NULL values.

·         B. A column with the UNIQUE constraint can contain NULL.

·         C. A constraint is enforced only for the INSERT operation on a table.

·         D. A constraint can be disabled even if the constraint column contains data.

·         E. All the constraints can be defined at the column level as well as the table level

Expose Correct Answer

Answer : B,D

Next Question

Question discussion

You need to signup or login to add a comment

Question 7

Examine the structure of the employees table.

There is a parent/child relationship betweenEMPLOYEE_IDandMANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same
manager asthe employee whoseEMPLOYEE_ID123.
Which query provides the correct output?
A)

B)

C)

D)

·         A. Option A

·         B. Option B

·         C. Option C

·         D. Option D

Expose Correct Answer

Answer : B

Next Question

Question discussion

You need to signup or login to add a comment

Question 8

See the Exhibit and Examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shows an increase in the
credit limit by 15% for all customers. Customers whose credit limit has not been entered
should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

·         A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;

·         B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;

·         C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;

·         D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

Expose Correct Answer

Answer : D

Explanation: NVL Function Converts a null value to an actual value: Data types that can be used are date, character, and number. Data types must match: NVL(commission_pct,0) NVL(hire_date,'01-JAN-97') NVL(job_id,'No Job Yet')

Next Question

Question discussion

You need to signup or login to add a comment

Question 9

Examine the structure of the members table:

Which query can be used to display the last names and city names only for members from
the states MO and MI?
A)

B)

C)

D)

·         A. Option A

·         B. Option B

·         C. Option C

·         D. Option D

Expose Correct Answer

Answer : C

Next Question

Question discussion

You need to signup or login to add a comment

Question 10

The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the
following
GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?

·         A. PUBLIC should be replaced with specific usernames.

·         B. ALL should be replaced with a list of specific privileges.

·         C. WITH GRANT OPTION should be added to the statement.

·         D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Expose Correct Answer

Answer : D

Next Question

Question discussion

You need to signup or login to add a comment

Question 11

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the
managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

·         A. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE m.manager_id=100;

·         B. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON m.employee_id = e.manager_id WHERE e.manager_id=100;

·         C. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e ON e.employee_id = m.manager_id WHERE m.manager_id=100;

·         D. SELECT m.last_name "Manager", e.last_name "Employee" FROM employees m JOIN employees e WHERE m.employee_id = e.manager_id AND e.manager_id=100;

Expose Correct Answer

Answer : B

Next Question

Question discussion

You need to signup or login to add a comment

Question 12

Which two tasks can be performed by using Oracle SQL statements?

·         A. changing the password for an existing database

·         B. connecting to a database instance

·         C. querying data from tables across databases

·         D. starting up a database instance

·         E. executing operating system (OS) commands in a session

Expose Correct Answer

Answer : C,E

Next Question

Question discussion

You need to signup or login to add a comment

Question 13

See the Exhibit and examine the structure of the PROMOTIONS table:
Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the
range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:

What would be the outcome?

·         A. It generates an error because multiple conditions cannot be specified for the WHEN clause

·         B. It executes successfully and gives the required result

·         C. It generates an error because CASE cannot be used with group functions

·         D. It generates an error because NULL cannot be specified as a return value

Expose Correct Answer

Answer : B

Explanation: CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END