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

You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same even if this causes the output to exceed 5 percent of the rows.

AMOUNT_SOLD -
Which query will provide the required result?


Options are :

  • SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
  • SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES;
  • SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
  • SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY;

Answer :SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES;

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

Which statement is true about an inner join specified in a query's WHERE clause?


Options are :

  • It applies for equijoin and nonequijoin conditions.
  • It only applies for equijoin conditions.
  • It requires column names to be the same in all tables being joined.
  • It must have primary-key and foreign-key constraints defined on the join columns.

Answer :It applies for equijoin and nonequijoin conditions.

Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the table, which are not present in the SALES2 table.

SALES1 -


Which set operator generates the required output?


Options are :

  • MINUS
  • INTERSECT
  • SUBTRACT
  • UNION
  • PLUS

Answer :MINUS

Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?


Options are :

  • first normal form
  • third normal form
  • fourth normal form
  • Second normal form

Answer :Second normal form

1Z0-238 R12 Oracle Install,Patch & Maintain Oracle APP Test Set 3

Which two statements are true about Data Manipulation Language (DML) statements?


Options are :

  • A DELETE FROM..... statement can remove rows based on only a single condition on a table.
  • A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
  • An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
  • An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
  • An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
  • An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.

Answer :A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.

View the Exhibit and examine the details of PRODUCT_INFORMATION table.


You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:

SELECT product_name -

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?


Options are :

  • It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
  • It would execute but the output would return no rows.
  • It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
  • It would execute and the output would display the desired result.

Answer :It would execute but the output would return no rows.

The BOOKS_TRANSACTIONS table exists in your database.
SQL>SELECT * FROM books_transactions ORDER BY 3;
What is the outcome on execution?


Options are :

  • Rows are displayed sorted in ascending order of the values in the third column in the table.
  • The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
  • Rows are displayed in the order that they are stored in the table only for the first three rows.
  • Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.

Answer :Rows are displayed sorted in ascending order of the values in the third column in the table.

OCA Oracle 12C Administration (1Z0-062) Practice Test Set 1

Evaluate the following two queries:


Which statement is true regarding the above two queries?


Options are :

  • Performance would degrade in query 2.
  • Performance would improve in query 2
  • There would be no change in performance.
  • Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column

Answer :There would be no change in performance.

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


Which two tasks would require subqueries or joins to be executed in a single statement?


Options are :

  • finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
  • finding the number of customers, in each city, whose marital status is 'married'.
  • listing of customers who do not have a credit limit and were born before 1980
  • finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
  • listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Answer :finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Which three tasks can be performed using SQL functions built into Oracle Database?


Options are :

  • displaying a date in a nondefault format
  • substituting a character string in a text expression with a specified string
  • finding the number of characters in an expression
  • combining more than two columns or expressions into a single column in the output

Answer :displaying a date in a nondefault format substituting a character string in a text expression with a specified string finding the number of characters in an expression

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

Which two tasks can be performed by using Oracle SQL statements? (Choose two.)


Options are :

  • executing operating system (OS) commands in a session
  • starting up a database instance
  • querying data from tables in different databases
  • connecting to a database instance
  • changing the password for an existing database user

Answer :querying data from tables in different databases changing the password for an existing database user

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


Options are :

  • Subqueries can contain ORDER BY but not the GROUP BY clause.
  • Only one column or expression can be compared between the main query and subquery.
  • Subqueries can contain GROUP BY and ORDER BY clauses.
  • Main query and subquery must get data from the same tables.
  • Main query and subquery can get data from different tables.
  • Multiple columns or expressions can be compared between the main query and subquery.

Answer :Subqueries can contain GROUP BY and ORDER BY clauses. Main query and subquery can get data from different tables. Multiple columns or expressions can be compared between the main query and subquery.

In which three situations does a transaction complete?


Options are :

  • when a TRUNCATE statement is executed after the pending transaction
  • when a PL/SQL anonymous block is executed
  • when a DELETE statement is executed
  • when a ROLLBACK command is executed
  • when a data definition language (DDL) statement is executed

Answer :when a TRUNCATE statement is executed after the pending transaction when a ROLLBACK command is executed when a data definition language (DDL) statement is executed

1Z0-497 Oracle Database 12c Essentials Certification Exam Set 4

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

GRANT ALL -

ON orders, order_items -
TO PUBLIC;
What must be done to fix the statement?


Options are :

  • Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.
  • WITH GRANT OPTION should be added to the statement.
  • ALL should be replaced with a list of specific privileges.
  • PUBLIC should be replaced with specific usernames.

Answer :Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.

Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp -
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2(15),
salary NUMBER (8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));

ALTER TABLE emp -
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp -
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_PK?


Options are :

  • It would be automatically enabled and deferred.
  • It would remain disabled and has to be enabled manually using the ALTER TABLE command.
  • It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
  • It would be automatically enabled and immediate.

Answer :It would remain disabled and has to be enabled manually using the ALTER TABLE command.

Evaluate the following ALTER TABLE statement:

ALTER TABLE orders -
SET UNUSED (order_date);
Which statement is true?


Options are :

  • The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
  • After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
  • ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
  • The DESCRIBE command would still display the ORDER_DATE column.

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

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

Evaluate the following statement.


Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?


Options are :

  • They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
  • The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
  • They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
  • They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

Answer :They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.


You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees:
SELECT employee_id, first_name, department_name

FROM employees -
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?


Options are :

  • The NATURAL JOIN clause is missing the USING clause.
  • The table prefix is missing for the column names in the SELECT clause.
  • The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
  • The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.

Answer :The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Examine the structure of the MEMBERS table:


You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?


Options are :

  • SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
  • SELECT * FROM MEMBERS WHERE state LIKE '%A_';
  • SELECT * FROM MEMBERS WHERE state LIKE 'A_';
  • SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Answer :SELECT * FROM MEMBERS WHERE state LIKE 'A_';

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

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. 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.

ORDER_ID -
Which DELETE statement would execute successfully?



Options are :

  • DELETE order_id FROM orders WHERE order_total < 1000;
  • DELETE orders o, order_items i WHERE o.order_id = i.order_id;
  • DELETE orders WHERE order_total < 1000;
  • DELETE FROM orders WHERE (SELECT order_id FROM order_items);

Answer :DELETE orders WHERE order_total < 1000;

View the exhibit and examine the description of the PRODUCT_INFORMATION table.


Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?


Options are :

  • SELECT COUNT (list_price) FROM product_information WHERE list_price is NULL
  • SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price is NULL
  • SELECT COUNT (NVL(list_price, 0)) FROM product_information WHERE list_price is NULL
  • SELECT COUNT (list_price) FROM product_information WHERE list_price i= NULL

Answer :SELECT COUNT (NVL(list_price, 0)) FROM product_information WHERE list_price is NULL

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


Options are :

  • You can have more than one column in a table as part of a primary key.
  • A constraint is enforced only for an INSERT operation on a table.
  • A foreign key cannot contain NULL values.
  • A column with the UNIQUE constraint can store NULLS.

Answer :You can have more than one column in a table as part of a primary key. A column with the UNIQUE constraint can store NULLS.

1Z0-238 R12 Oracle Install,Patch & Maintain Oracle APP Test Set 2

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)


Options are :

  • The HAVING clause conditions can use aliases for the columns.
  • The HAVING clause conditions can have aggregate functions.
  • The WHERE clause is used to exclude rows before the grouping of data.
  • The HAVING clause is used to exclude one or more aggregated results after grouping data.
  • WHERE and HAVING clauses cannot be used together in a SQL statement.

Answer :The HAVING clause conditions can have aggregate functions. The WHERE clause is used to exclude rows before the grouping of data. The HAVING clause is used to exclude one or more aggregated results after grouping data.

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


Options are :

  • A role can be granted to itself.
  • The REVOKE command can be used to remove privileges but not roles from other users.
  • Roles are named groups of related privileges that can be granted to users or other roles.
  • A user can be granted only one role at any point of time.
  • A role can be granted to PUBLIC.

Answer :Roles are named groups of related privileges that can be granted to users or other roles. A role can be granted to PUBLIC.

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.


You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the view successfully?


Options are :

  • CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date WHITH CHECK OPTION;
  • CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;
  • CREATE OR REPLACE VIEW ord_vu (order_id, order_date) AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;
  • CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;

Answer :CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;

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

Evaluate the following SQL statement:

Identify three ORDER BY clauses either one of which can complete the query.


Options are :

  • ORDER BY 2, 1
  • ORDER BY CUST_NO
  • ORDER BY "Last name"
  • ORDER BY "CUST_NO"
  • ORDER BY 2, cust_id

Answer :ORDER BY 2, 1 ORDER BY "Last name" ORDER BY 2, cust_id

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:


You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?


Options are :

  • SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);
  • SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);
  • SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);
  • SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);

Answer :SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);

View the exhibit and examine the ORDERS table.


The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?


Options are :

  • ALTER TABLE orders ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
  • ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  • ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  • ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

Answer :ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

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

Which statement is true about transactions?


Options are :

  • A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
  • A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
  • A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
  • Each Data Definition Language (DDL) statement executed forms a single transaction.

Answer :Each Data Definition Language (DDL) statement executed forms a single transaction.

You are designing the structure of a table in which two columns have the specifications: " must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row

COMPONENT_ID -
" contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons

EXECUTION_DATETIME -
between components.
Which two options define the data types that satisfy these requirements most efficiently? (Choose two.)


Options are :

  • The COMPONENT_ID column must be of CHAR data type.
  • The COMPONENT_ID must be of VARCHAR2 data type.
  • The EXECUTION_DATETIME must be of DATE data type.
  • The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
  • The COMPONENT_ID must be of ROWID data type.
  • The EXECUTION_DATETIME must be of TIMESTAMP data type.

Answer :The COMPONENT_ID column must be of CHAR data type. The EXECUTION_DATETIME must be of DATE data type.

Examine this SELECT statement and view the Exhibit to see its output:


SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status,

FROM user_constraints -
WHERE table_name = 'ORDERS';
Which two statements are true about the output? (Choose two.)


Options are :

  • In the second column, 'c' indicates a check constraint.
  • the STATUS column indicates whether the table is currently in use.
  • The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
  • The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.

Answer :In the second column, 'c' indicates a check constraint. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.

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

Which statement is true regarding the INTERSECT operator?


Options are :

  • The number of columns and data types must be identical for all SELECT statements in the query.
  • It ignores NULL values.
  • The names of columns in all SELECT statements must be identical.
  • Reversing the order of the intersected tables alters the result.

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

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


You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:

Which statement is true?


Options are :

  • It generates an error because the condition specified for the PROD_CATEGORY column is not valid.
  • It executes successfully and returns the required result.
  • It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
  • It executes successfully but returns no result.

Answer :It executes successfully but returns no result.

View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)


The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.
The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?


Options are :

  • SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
  • SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);
  • SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);
  • SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);

Answer :SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);

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

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)


Evaluate this SQL statement:

ALTER TABLE emp -
DROP COLUMN first_name;
Which two statements are true?


Options are :

  • The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
  • The FIRST_NAME column would be dropped provided at least one column remains in the table.
  • The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
  • The FIRST_NAME column would be dropped provided it does not contain any data.

Answer :The FIRST_NAME column would be dropped provided at least one column remains in the table.

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


Options are :

  • Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
  • A subquery can be used in the FROM clause of a SELECT statement.
  • If a subquery returns NULL, the main query may still return rows.
  • A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
  • The ORDER BY Clause can be used in a subquery.

Answer :A subquery can be used in the FROM clause of a SELECT statement. If a subquery returns NULL, the main query may still return rows. The ORDER BY Clause can be used in a subquery.

You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?


Options are :

  • All the table's indexes if any exist, are invalidated but retained.
  • Table data and the table structure are deleted.
  • Any uncommitted transaction in the session is committed.
  • Table data is deleted but the table structure is retained.
  • All existing views and synonyms that refer to the table are invalidated but retained.

Answer :All the table's indexes if any exist, are invalidated but retained. Table data and the table structure are deleted. All existing views and synonyms that refer to the table are invalidated but retained.

1Z0-409 Oracle Linux Fundamentals Practice Test Set 7

Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose three.)


Options are :

  • The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block
  • The WITH clause can hold more than one query.
  • It can be used only with the SELECT clause.
  • If the query block name and the table name are the same, then the table name takes precedence.

Answer :The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block The WITH clause can hold more than one query. It can be used only with the SELECT clause.

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


Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)


Options are :

  • WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') )
  • WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
  • WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
  • WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')

Answer :WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003' WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')

You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.)
Which query generates the required output?


Options are :

  • SELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_';
  • SELECT * FROM users Where user_name LIKE '%ch_%'ESCAPE'%';
  • SELECT * FROM users Where user_name LIKE '%ch_';
  • SELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\';

Answer :SELECT * FROM users Where user_name LIKE '%ch_%'ESCAPE'%';

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

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) );
The above command fails when executed. What could be the reason?


Options are :

  • 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.
  • The BETWEEN clause cannot be used for the CHECK constraint.
  • SYSDATE cannot be used with the CHECK constraint.

Answer :SYSDATE cannot be used with the CHECK constraint.

Examine the structure of the PROGRAMS table:


Which two SQL statements would execute successfully?


Options are :

  • SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
  • SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), "Ongoing") FROM programs
  • SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
  • SELECT NVL (MONTHS_BETWEEN (start_date, end_date), "Ongoing") FROM programs;

Answer :SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), "Ongoing") FROM programs SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;

Which statement is true about Data Manipulation Language (DML)?


Options are :

  • DML automatically disables foreign ley constraints when modifying primary key values in the parent table.
  • Each DML statement forms a transaction by default.
  • A transaction can consist of one or more DML statements.
  • DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

Answer :A transaction can consist of one or more DML statements.

Oracle 1Z0-207 Billing & Revenue Mgmt Pricing Practice Exam Set 4

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?


Options are :

  • SELECT cust_income_level "" cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.
  • SELECT DISTINCT cust_income_level "" cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.
  • SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers
  • SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.

Answer :SELECT DISTINCT cust_income_level "" cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers.

Evaluate the following query:
SQL> SELECT TRUNC (ROUND(156.00, -1),-1)
FROM DUAL;
What would be the outcome?


Options are :

  • 160
  • 16
  • 200
  • 100
  • 150

Answer :160

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions