Oracle 12c SQL Certified Associate 1Z0-071 Real Exam 2019 Set 1

Examine the structure of the DEPARTMENTS table.


You execute the following command:


Which two statements are true?


Options are :

  • Views created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.
  • Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.
  • Synonyms existing of the DEPARTMENTS table would have to be re-created.
  • Unique key constraints defined on the COUNTRY column are removed.
  • A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

Answer :Unique key constraints defined on the COUNTRY column are removed. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

1Z0-517 Oracle EBS R12.1 Payables Essentials Practice Exam Set 4

Which two statements are true? (Choose two.)


Options are :

  • DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
  • All the dynamic performance views prefixed with v$ are accessible to all the database users.
  • The USER_SYNONYMS view can provide information about private synonyms.
  • The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
  • The USER_OBJECTS view can provide information about the tables and views created by the user who queries the view.

Answer :The USER_SYNONYMS view can provide information about private synonyms. The USER_OBJECTS view can provide information about the tables and views created by the user who queries the view.

Which statement is true regarding the SESSION_PRIVS dictionary view?


Options are :

  • It contains the object privileges granted to other users by the current user session.
  • It contains the current object privileges available in the user session.
  • It contains the current system privileges available in the user session.
  • It contains the system privileges granted to other users by the current user session.

Answer :It contains the current system privileges available in the user session.

Examine the structure of the SHIPMENTS table:


You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:

Which statement is true regarding the above commands?


Options are :

  • Only the first query executes successfully and gives the correct result.
  • Only the second query executes successfully and gives the correct result.
  • Only the first query executes successfully but gives a wrong result.
  • Only the second query executes successfully but gives a wrong result.
  • Both execute successfully and give correct results.

Answer :Only the first query executes successfully and gives the correct result.

OCA Oracle Database 11g Administrator Certified Associate Set 2

Examine the structure of the EMPLOYEES table:


There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the name, joining date, and manager for all employees. Newly hired employees are yet to be assigned a department or a manager. For them, ‘No Manager’ should be displayed in the MANAGER column.
Which SQL query gets the required output?


Options are :

  • SELECT e.last_name, e.hire_date, NVL(m.last_name, "No Manager") Manager FROM employees e RIGHT OUTER JOIN employees m ON (e.manager_id = m.employee_id);
  • SELECT e.last_name, e.hire_date, NVL(m.last_name, "No Manager") Manager FROM employees e JOIN employees m ON (e.manager_id = m.employee_id);
  • SELECT e.last_name, e.hire_date, NVL(m.last_name, "No Manager") Manager FROM employees e NATURAL JOIN employees m ON (e.manager_id = m.employee_id).
  • SELECT e.last_name, e.hire_date, NVL(m.last_name, "No Manager") Manager FROM employees e LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id);

Answer :SELECT e.last_name, e.hire_date, NVL(m.last_name, "No Manager") Manager FROM employees e LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id);

View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.


You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which two DELETE statements are valid (Choose two.)


Options are :

  • DELETE * FROM order_items WHERE order_id IN (SELECT order_id) FROM orders WHERE order_status IN (0,1));
  • DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));
  • DELETE FROM (SELECT * FROM order_items I,orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));
  • DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE orders_status in (0,1));

Answer :DELETE FROM (SELECT * FROM order_items I,orders o WHERE i.order_id = o.order_id AND order_status IN (0,1)); DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE orders_status in (0,1));

Examine the data in the CUST_NAME column of the CUSTOMERS table.


You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:

Which two queries give the required output?


Options are :

  • SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)<>0;
  • SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0;
  • SELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,-2)<>0;
  • SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0;

Answer :SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)<>0; SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0;

Oracle 12c SQL Certified Associate 1Z0-071 Real Exam 2019 Set 2

View the Exhibit and examine the structure of the SALES table.


The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 items.

Which statement is true regarding this SQL statement?


Options are :

  • It produces an error because COUNT (*) should be only in the HAVING clause and not in the WHERE clause.
  • It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
  • It produces an error because COUNT (*) should be specified in the SELECT clause also.
  • It executes successfully and generates the required result.

Answer :It produces an error because COUNT (*) should be only in the HAVING clause and not in the WHERE clause.

The PRODUCTS table has the following structure.


Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ‘ ‘) FROM products;
SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products;
Which statement is true regarding the outcome?


Options are :

  • Both the statements execute and give different results
  • Both the statements execute and give the same result
  • Only the second SQL statement executes successfully
  • Only the first SQL statement executes successfully

Answer :Both the statements execute and give different results

View the Exhibit and examine the structure of the BOOKS table.


The BOOKS table contains details of 100 books.
Examine the commands executed and their outcome:

Which statement is true?


Options are :

  • The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.
  • The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.
  • Both ROLLBACK commands restore the 101 rows that were deleted.
  • Both ROLLBACK commands restore the 100 rows that were deleted.

Answer :The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.

Oracle 1Z0-516 EBS R12 General Essential Practice Exam Set 9

View the Exhibit and examine the data in the PRODUCTS table.


Which statement would add a column called PRICE, which cannot contain NULL?


Options are :

  • ALTER TABLE products ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL.
  • ALTER TABLE products ADD price NUMBER(8,2) NOT NULL;
  • ALTER TABLE products ADD price NUMBER(8,2) DEFAULT NOT NULL;
  • ALTER TABLE products ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;

Answer :ALTER TABLE products ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.



There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?


Options are :

  • INSERT INTO orders (order_id, order_data, order_mode, (SELECT customer_id FROM customers
  • INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name=Roberts AND c.credit_limit=600 ) VALUES (1, 10-mar-2007, direct, (SELECT customer_id FROM customers WHERE cust_last_name=Roberts AND credit_limit=6 00), 1000);
  • INSERT INTO orders VALUES(1, 10-mar-2007, direct, (SELECT customer_id FROM customers WHERE cust_last_name=Roberts AND credit_limit=600), 1000);
  • INSERT INTO orders (order_id, order_data, order_mode, (SELECT customer_id FROM customers WHERE cust_last_name=Roberts AND credit_limit=600), order_total) VALUES(1, 10-mar-2007, direct, &&customer_id, 1000);

Answer :INSERT INTO orders VALUES(1, 10-mar-2007, direct, (SELECT customer_id FROM customers WHERE cust_last_name=Roberts AND credit_limit=600), 1000);

Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?


Options are :

  • DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table.
  • DELTE and TRUNCATE statements can have a rollback done to restore data into a table.
  • DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.
  • DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed.

Answer :DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.

1Z1-574 Oracle IT Architecture Essentials Practice Exam Set 3

View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values "direct" and 0 respectively.


Which two INSERT statements are valid? (Choose two.)


Options are :

  • INSERT INTO orders VALUES (1,"09-mar-2007", "online","", 1000);
  • INSERT INTO orders (order_id,order_date,order_mode, (customer_id,order_total) VALUES (1,TO_DATE(NULL), "online", 101, NULL);
  • INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,"09-mar-2007", 101);
  • INSERT INTO orders VALUES (1,"09-mar-2007", DEFAULT, 101, DEFAULT);
  • INSERT INTO orders (order_id,order_date,order_mode,order_total) VALUES (1,"10-mar-2007","online",1000);

Answer :INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,"09-mar-2007", 101); INSERT INTO orders VALUES (1,"09-mar-2007", DEFAULT, 101, DEFAULT);

Examine the structure and data in the PRICE_LIST table:


You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?


Options are :

  • SELECT TO_CHAR (TO_NUMBER(prod_price, "$99,999.99") * . 25, "$99,999.00") FROM PRICE_LIST
  • SELECT TO_CHAR (prod_price* .25, "$99,999.99") FROM PRICE_LIST
  • SELECT TO_CHAR (TO_NUMBER(prod_price) * .25, "$99,999.00") FROM PRICE_LIST
  • SELECT TO_NUMBER (TO_NUMBER(prod_price, "$99,999.99") * . 25, "$99,999.00") FROM PRICE_LIST

Answer :SELECT TO_CHAR (TO_NUMBER(prod_price, "$99,999.99") * . 25, "$99,999.00") FROM PRICE_LIST

You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE.
Examine the sample output:


Which query will provide the required output?


Options are :

  • SELECT LISTAGG(last_name, '; ') Emp_list, MIN(hire_date) Earliest FROM employees WHERE department_id = 30; WITHIN GROUP ORDER BY (hire_date);
  • SELECT LISTAGG(last_name) WITHIN GROUP ORDER BY (hire_date) Emp_list, MIN(hire_date) Earliest FROM employees WHERE department_id = 30;
  • SELECT LISTAGG(last_name, '; ') WITHIN GROUP ORDER BY (hire_date) Emp_list, MIN(hire_date) Earliest FROM employees WHERE department_id = 30;
  • SELECT LISTAGG(last_name, '; ') Emp_list, MIN(hire_date) Earliest FROM employees WHERE department_id = 30; ORDER BY (hire_date);

Answer :SELECT LISTAGG(last_name, '; ') WITHIN GROUP ORDER BY (hire_date) Emp_list, MIN(hire_date) Earliest FROM employees WHERE department_id = 30;

1Z0-520 Oracle EBS R12 Purchasing Essentials Practice Exam Set 2

View the Exhibit and examine the description for the PRODUCTS and SALES table.

is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the

PROD_ID -
last three years. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.
Which is the valid DELETE statement?


Options are :

  • DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);
  • DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );
  • DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );
  • DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE );

Answer :DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);

Examine the command to create the BOOKS table.



The BOOK_ID value 101 does not exist in the table.
Examine the SQL statement:


Which statement is true?


Options are :

  • It executes successfully only if NULL is explicitly specified in the INSERT statement.
  • It executes successfully only if the PUBLISHER_ID column name is added to the columns list and NULL is explicitly specified in the INSERT statement.
  • It executes successfully only if the PUBLISHER_ID column name is added to the columns list in the INSERT statement.
  • It executes successfully and the row is inserted with a rule PUBLISHER_ID.

Answer :It executes successfully and the row is inserted with a rule PUBLISHER_ID.

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


Options are :

  • A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.
  • A subquery can be used to access data from one or more tables or views.
  • Only two subqueries can be placed at one level.
  • The columns in a subquery must always be qualified with the name or alias of the table used.
  • If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.

Answer :A subquery can be used to access data from one or more tables or views. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.

1Z0-521 Oracle E-Business Suite R12.1Oracle Order Managment Set 3

View the Exhibit and examine the structure of the CUSTOMERS table.


Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?


Options are :

  • It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
  • It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
  • It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
  • It executes successfully.

Answer :It executes successfully.

You executed the following CREATE TABLE statement that resulted in an error:
SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2
(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk
REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename));
Identify two reasons for the error.


Options are :

  • Only one LONG column can be used per table
  • The FOREIGN KEY keyword is missing in the constraint definition
  • The NOT NULL constraint on the ENAME column must be defined as the column level
  • FOREIGN KEY defined on the DEPT_ID column must be at the table level only
  • The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only

Answer :Only one LONG column can be used per table The NOT NULL constraint on the ENAME column must be defined as the column level

Which two statements are true regarding operators used with subqueries (Choose two.)


Options are :

  • The IN operator cannot be used in single-row subqueries.
  • =ANY and =ALL operators have the same functionality.
  • The
  • The NOT operator can be used with IN, ANY and ALL operators.
  • The NOT IN operator is equivalent to IS NULL.

Answer :The

1Z0-521 Oracle E-Business Suite R12.1Oracle Order Managment Set 1

View the Exhibit and examine the description of the EMPLOYEES table.


Evaluate the following SQL statement:
SELECT first_name, employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) “Review FROM employees;
The query was written to retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees. The review date is the firsts Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.
Which statement is true regarding this query?


Options are :

  • The query would not execute because the NEXT_DAY function accepts a string as argument.
  • The query would execute to give the desired output.
  • The query would execute but the output would give review dates that are Sundays.
  • The query would not execute because date functions cannot be nested.

Answer :The query would execute but the output would give review dates that are Sundays.

You need to display the first names of all customers from the CUSTOMERS table that contain the character e and have the character a in the second last position.


Options are :

  • SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, e)<>0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name), -2)=a;
  • SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, e)IS NOT NULL AND SUBSTR(cust_first_name, 1, -2)=a;
  • SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, e)<>'' AND SUBSTR(cust_first_name, -2, 1)=a;
  • SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, e)<>0 AND SUBSTR(cust_first_name, -2, 1)=a;

Answer :SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, e)<>0 AND SUBSTR(cust_first_name, -2, 1)=a;

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


Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"

FROM order_items -
GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)


Options are :

  • Statement 1 would not return give the same output.
  • Statement 2 would return multiple rows of output.
  • Both the statements would give the same output.
  • Statement 1 would return only one row of output.
  • Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.

Answer :Statement 2 would return multiple rows of output. Statement 1 would return only one row of output. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.

1Z0-520 Oracle EBS R12 Purchasing Essentials Practice Exam Set 2

Evaluate the following query:


What would be the outcome of the above query?


Options are :

  • It executes successfully and introduces an 's at the end of each promo_name in the output.
  • It produces an error because the data types are not matching.
  • It executes successfully and displays the literal " {'s start date was \> " for each row in the output.
  • It produces an error because flower braces have been used.

Answer :It executes successfully and introduces an 's at the end of each promo_name in the output.

View the Exhibit and examine the structure of the CUSTOMERS table.

is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.

CUSTOMER_VU -
need to be updated to reflect the latest information about the customers.

CUSTOMERS -
What is the error in the following MERGE statement?


Options are :

  • The WHERE clause cannot be used with INSERT.
  • The CUSTOMER_ID column cannot be updated.
  • CUSTOMER_VU cannot be used as a data source.
  • The INTO clause is misplaced in the command.

Answer :The CUSTOMER_ID column cannot be updated.

Evaluate the following CREATE TABLE commands:

CREATE_TABLE orders -
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER (4));

CREATE TABLE ord_items -
(ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );
Why would the ORD_ITEMS table not get created?


Options are :

  • The BETWEEN clause cannot be used twice for the same table.
  • ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
  • The CHECK constraint cannot be placed on columns having the DATE data type.
  • SYSDATE cannot be used with the CHECK constraint.

Answer :SYSDATE cannot be used with the CHECK constraint.

1Z0-520 Oracle EBS R12 Purchasing Essentials Practice Exam Set 2

Evaluate the following CREATE SEQUENCE statement:

CREATE SEQUENCE seq1 -

START WITH 100 -

INCREMENT BY 10 -

MAXVALUE 200 -

CYCLE -
NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?


Options are :

  • 10
  • an error
  • 1
  • 100

Answer :1

View the Exhibit and examine the structure of the CUSTOMERS table.


You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:



Which statement is true regarding the execution of the above queries?


Options are :

  • Both execute successfully but do not give the required result
  • Only the first query gives the correct result
  • Both execute successfully and give the same result
  • Only the second query gives the correct result

Answer :Only the first query gives the correct result

Examine the description of the EMP_DETAILS table given below:


Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?


Options are :

  • You cannot add a new column to the table with LONG as the data type.
  • You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
  • An EMP_IMAGE column cannot be included in the ORDER BY clause.
  • An EMP_IMAGE column can be included in the GROUP BY clause.

Answer :You cannot add a new column to the table with LONG as the data type. An EMP_IMAGE column cannot be included in the ORDER BY clause.

1Z0-409 Oracle Linux Fundamentals Practice Exam Set 1

View the Exhibit and examine the structure of the PRODUCT table.


Which two tasks would require subqueries? (Choose two.)


Options are :

  • display the total number of products supplied by supplier 102 and have product status as "OBSOLETE"
  • display the minimum list price for each product status
  • display the number of products whose list prices are more than the average list price
  • display all products whose minimum list price is more than the average list price of products having the status "orderable"
  • display all suppliers whose list price is more than 1000

Answer :display the number of products whose list prices are more than the average list price display all products whose minimum list price is more than the average list price of products having the status "orderable"

Evaluate the following SQL statement:


Which statement is true regarding the outcome of the above query?


Options are :

  • It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
  • It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.
  • t produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.
  • It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

Answer :t produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

View the exhibit and examine the description for the SALES and CHANNELS tables.


You issued this SQL statement:

Which statement is true regarding the result?


Options are :

  • The statement will fail because a subquery cannot be used in a VALUES clause.
  • The statement will fail because the VALUES clause is not required with a subquery.
  • The statement will execute and a new row will be inserted in the SALES table.
  • The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.

Answer :The statement will execute and a new row will be inserted in the SALES table.

1Z0-516 Oracle EBS R12 General Ledger Essentials Exam Set 5

Which two statements are true regarding the DELETE and TRUNCATE commands?


Options are :

  • DELETE can be used to remove data from specific columns as well as complete rows.
  • DELETE can be used to remove rows from multiple tables in one statement.
  • DELETE can be used to remove rows from only one table in one statement.
  • DELETE can be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent.
  • DELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule.

Answer :DELETE can be used to remove rows from only one table in one statement. DELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule.

Examine the data in the ORD_ITEMS table:


Evaluate this query:

Which statement is true regarding the result?


Options are :

  • It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.
  • It returns an error because the HAVING clause should be specified after the GROUP BY clause.
  • It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.
  • It returns an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list.

Answer :It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.

The customers table has the following structure:


You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?


Options are :

  • SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
  • SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level <> NULL AND tax_amount <> NULL;
  • SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND tax_amount IS NOT NULL;
  • SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE (cust_income_level, tax_amount) IS NOT NULL;

Answer :SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

Oracle 1Z0-516 EBS R12 General Essential Practice Exam Set 8

What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models?


Options are :

  • RDB allows the definition of relationships between different tables, whereas OODB does not allow this
  • RDB supports only E.F. Codd's rules, whereas OODB does not support them.
  • OODB supports multiple objects in the same database, whereas RDB supports only tables.
  • OODB incorporates methods with data structure definition, whereas RDB does not allow this.

Answer :OODB incorporates methods with data structure definition, whereas RDB does not allow this.

View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables.


You are asked to retrieve the ORDER_ID, product_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
prder_id, product_id, unit_price*quantity "Total Price"

SELECT -

FROM order_items -
WHERE unit_price*quantity > 50000
NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?


Options are :

  • The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.
  • The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.
  • The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.
  • The statement would execute and provide the desired result.

Answer :The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.

Which three tasks can be performed by DDL statements?


Options are :

  • modifying a table to prevent data that violate certain conditions from being entered in a column
  • preventing any data modification to a table
  • providing an alternative name for a table
  • preventing data retrieval from a table outside of office hours
  • creating multiple savepoints to enable partial rollback of a transaction

Answer :modifying a table to prevent data that violate certain conditions from being entered in a column preventing any data modification to a table providing an alternative name for a table

1Z0-517 Oracle EBS R12.1 Payables Essentials Practice Exam Set 7

Which three statements indicate the end of a transaction? (Choose three.)


Options are :

  • after a SELECT statement is issued
  • after a COMMIT is issued
  • after a ROLLBACK is issued
  • after a SAVEPOINT is issued
  • after a CREATE statement is issued

Answer :after a COMMIT is issued after a ROLLBACK is issued after a CREATE statement is issued

Examine the structure of the BOOKS_TRANSACTIONS table.


You want to update this table such that BOOK_ID is set to 'INVALID' for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:

Which condition must be used in the WHERE clause to perform the required update?


Options are :

  • MEMBER_ID = '';
  • MEMBER_ID = "";
  • MEMBER_ID IS NULL;
  • MEMBER_ID = NULL;

Answer :MEMBER_ID IS NULL;

View the Exhibit and examine the data in EMP and DEPT tables.


In the DEPT table, DEPTNO is the PRIMARY KEY.
In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the DEPTNO column in the DEPT table.
What would be the outcome of the following statements executed in the given sequence?
DROP TABLE emp;
FLASHBACK TABLE emp TO BEFORE DROP;
INSERT INTO emp VALUES (2, "SCOTT", 10);
INSERT INTO emp VALUES (3, "KING", 55);


Options are :

  • Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.
  • Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
  • Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.
  • Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.

Answer :Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

1Z0-520 Oracle EBS R12.1 Purchasing Essentials Practice Test Set 8

Evaluate the following SQL commands:


The command to create a table fails. Identify the reason for the SQL statement failure.



Options are :

  • You cannot use SYSDATE in the condition of a CHECK constraint.
  • You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
  • You cannot use the BETWEEN clause in the condition of a CHECK constraint.
  • You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

Answer :You cannot use SYSDATE in the condition of a CHECK constraint.

View the exhibit and examine the description of SALES and PROMOTIONS tables.


You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which three DELETE statements are valid? (Choose three.)


Options are :

  • DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN = "blowout sale","everyday low price"));
  • DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promo_name = "blowout sale") AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = "everyday low price") FROM promotions WHERE promo_name = "everyday low price");
  • DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = "blowout sale") OR promo_name = "everyday low price");
  • DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = "blowout sale") OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = "everyday low price")

Answer :DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN = "blowout sale","everyday low price")); DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = "blowout sale") OR promo_name = "everyday low price"); DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = "blowout sale") OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = "everyday low price")

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions