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

View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)


In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:

Which two JOIN operations can be used to obtain the required output?


Options are :

  • FULL OUTER JOIN
  • JOIN
  • RIGHT OUTER JOIN
  • LEFT OUTER JOIN

Answer :FULL OUTER JOIN LEFT OUTER JOIN

OCP Oracle Database 11g Administrator Certified Professional Set 4

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.


Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)

AS -
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?


Options are :

  • The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
  • The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
  • The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
  • The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

Answer :The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

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

CUST_NAME -
-------------------

Renske Ladwig -

Jason Mallin -

Samuel McCain -

Allan MCEwen -

Irene Mikkilineni -

Julia Nayer -
You need to display customers' second names where the second name starts with "Mc" or "MC".
Which query gives the required output?


Options are :

  • SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) = INITCAP ('MC%');
  • SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE SUBSTR(cust_name, INSTR (cust_name, ' ')+1) LIKE INITCAP ('MC%');
  • SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) LIKE 'Mc%';
  • SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) = 'Mc';

Answer :SELECT SUBSTR(cust_name, INSTR (cust_name, ' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) LIKE 'Mc%';

You must create a SALES table with these column specifications and data types: (Choose the best answer.)

SALESID: Number -

STOREID: Number -

ITEMID: Number -
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified
Which statement would create the table?


Options are :

  • CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");
  • CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT 'CASH');
  • CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT 'SYSDATE', payment VARCHAR2(30) DEFAULT CASH);
  • CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT = 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT = "CASH");

Answer :CREATE TABLE sales( salesid NUMBER(4), storeid NUMBER(4), itemid NUMBER(4), qty NUMBER DEFAULT 1, slsdate DATE DEFAULT SYSDATE, payment VARCHAR2(30) DEFAULT 'CASH');

1Z0-969 Oracle Payroll Cloud 2017 Implementation Essentials Set 2

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)


Options are :

  • Finding the quotient
  • Subtraction
  • Raising to a power
  • Finding the lowest value
  • Addition

Answer :Raising to a power Finding the lowest value Addition

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.


Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.

SELECT EMPLOYEE_ID -

FROM EMPLOYEES -
WHERE JOB_ID = 'SA_MAN'
-------------------------------------

SELECT EMPLOYEE_ID -

FROM JOB_HISTORY -
WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result.


Options are :

  • INTERSECT
  • UNION
  • UNION ALL
  • MINUS

Answer :UNION UNION ALL

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.


The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)

AS -
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true?


Options are :

  • The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
  • The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
  • The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
  • The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Answer :The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

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

Examine the structure of the ORDERS table: (Choose the best answer.)


You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders
GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?


Options are :

  • It executes successfully and gives the correct output.
  • It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
  • It returns an error because the TO_CHAR function is not valid.
  • It executes successfully but does not give the correct output.

Answer :It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)


Options are :

  • During execution, the Oracle server may read data from storage if the required data is not already in memory.
  • During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
  • During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.
  • During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.

Answer :During execution, the Oracle server may read data from storage if the required data is not already in memory.

Examine the structure of the SALES table.


Examine this statement:

Which two statements are true about the SALES1 table? (Choose two.)


Options are :

  • It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
  • It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
  • It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
  • It will not be created because of the invalid WHERE clause.
  • It is created with no rows.

Answer :It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table. It is created with no rows.

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

View the Exhibit and examine the description for the SALES and CHANNELS tables. (Choose the best answer.)



You issued this SQL statement:

Which statement is true regarding the result?


Options are :

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

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

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


Evaluate this SQL statement:
SELECT TO_CHAR (list_price, '$9,999')
From product_information;
Which two statements are true regarding the output?


Options are :

  • A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
  • A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
  • A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.
  • A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.

Answer :A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.

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


You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?


Options are :

  • SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);
  • SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;
  • SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id)
  • SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;

Answer :SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);

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

These are the steps for a correlated subquery, listed in random order:
1. The WHERE clause of the outer query is evaluated.
2. A candidate row is fetched from the table specified in the outer query.
3. This is repeated for the subsequent rows of the table, until 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.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?


Options are :

  • 4, 1, 2, 3
  • 2, 1, 4, 3
  • 2, 4, 1, 3
  • 4, 2, 1, 3

Answer :2, 4, 1, 3

Evaluate the following statement.


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


Options are :

  • Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses.
  • The INSERT statement will return an error because the ELSE clause is missing.
  • All rows are evaluated by all the three WHEN clauses.
  • Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.

Answer :All rows are evaluated by all the three WHEN clauses.

View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.



Evaluate the following SQL statement:

What would be the outcome of the above SQL statement?


Options are :

  • It displays prod IDs in the promos with the highest cost in the same time interval.
  • It displays prod IDs in the promo with the lowest cost.
  • It displays prod IDs in the promos which cost less than the highest cost in the same time interval.
  • It displays prod IDs in the promos with the lowest cost in the same time interval.

Answer :It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

1Z0-969 Oracle Payroll Cloud 2017 Implementation Essentials Set 3

Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.)


Options are :

  • You can explicitly provide the join condition with a NATURAL JOIN.
  • You can use the ON clause to specify multiple conditions while joining tables.
  • You can use the USING clause to join tables on more than one column.
  • You can use the JOIN clause to join only two tables.

Answer :You can use the ON clause to specify multiple conditions while joining tables. You can use the USING clause to join tables on more than one column.

Evaluate the following CREATE TABLE command:


Which statement is true regarding the above SQL statement?


Options are :

  • It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
  • It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.
  • It would give an error because the USING INDEX clause cannot be used on a composite primary.
  • It would execute successfully and only ORD_ITM_IDX index would be created.

Answer :It would execute successfully and only ORD_ITM_IDX index would be created.

Which three statements are true regarding the SQL WHERE and HAVING clauses?


Options are :

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

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

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

You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’.

Which SQL statement would give the required result?


Options are :

  • SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’) FROM DUAL
  • SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’) FROM DUAL
  • SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’) FROM DUAL
  • SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’)) FROM DUAL

Answer :SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’) FROM DUAL

View the exhibit and examine the structure and data in the INVOICE table.


Which two SQL statements would execute successfully? (Choose two.)


Options are :

  • SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
  • SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice
  • SELECT AVG(inv_date) FROM invoice
  • SELECT MAX(inv_date), MIN(cust_id) FROM invoice

Answer :SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice SELECT MAX(inv_date), MIN(cust_id) FROM invoice

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


You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?


Options are :

  • SELECT order_id, order_date FROM orders WHERE order_date > IN (SELECT order_date FROM orders WHERE customer_id = 101);
  • SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
  • SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROM orders WHERE customer_id = 101);
  • SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;

Answer :SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);

OCA Oracle Database 11g Administrator Certified Associate Set 3

Examine these SQL statements that are executed in the given order:


What will be the status of the foreign key EMP_MGR_FK?


Options are :

  • It will remain disabled and can be re-enabled manually.
  • It will be enabled and deferred.
  • It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
  • It will be enabled and immediate.

Answer :It will remain disabled and can be re-enabled manually.

View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)


You executed this SQL statement:
SELECT first_name, department_id, salary

FROM employees -
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)


Options are :

  • The values in all columns would be returned in descending order.
  • The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
  • The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
  • The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
  • The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Answer :The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:


You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?


Options are :

  • SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)
  • SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
  • SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON (c.depatrment_id=d.department_id)
  • SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)

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

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

Which two statements best describe the benefits of using the WITH clause? (Choose two.)


Options are :

  • It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
  • It enables sessions to store the results of a query permanently.
  • It enables sessions to store a query block permanently in memory and use it to create complex queries.
  • It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

Answer :It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

Which three statements are true? (Choose three.)


Options are :

  • The USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply.
  • The usernames of all the users including database administrators are stored in the data dictionary.
  • The data dictionary views consist of joins of dictionary base tables and user-defined tables.
  • Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user.
  • Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.
  • The data dictionary is created and maintained by the database administrator.

Answer :The USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply. The usernames of all the users including database administrators are stored in the data dictionary. Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.

View the Exhibit and examine the structure in the EMPLOYEES tables.


Evaluate the following SQL statement:
SELECT employee_id, department_id

FROM employees -
WHERE department_id= 50 ORDER BY department_id

UNION -
SELECT employee_id, department_id

FROM employees -

WHERE department_id=90 -

UNION -
SELECT employee_id, department_id

FROM employees -
WHERE department_id=10;

What would be the outcome of the above SQL statement?


Options are :

  • The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
  • The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
  • The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
  • The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Answer :The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

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

You issued this command:
SQL > DROP TABLE employees;
Which three statements are true? (Choose three.)


Options are :

  • The EMPLOYEES table may be moved to the recycle bin.
  • Sequences used in the EMPLOYEES table become invalid.
  • If there is an uncommitted transaction in the session, it is committed.
  • The EMPLOYEES table can be recovered using the ROLLBACK command.
  • The space used by the EMPLOYEES table is always reclaimed immediately.
  • All indexes and constraints defined on the table being dropped are also dropped.

Answer :The EMPLOYEES table may be moved to the recycle bin. If there is an uncommitted transaction in the session, it is committed. All indexes and constraints defined on the table being dropped are also dropped.

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)


Options are :

  • The nested query executes first and then the outer query executes.
  • The nested query executes after the outer query returns the row.
  • The outer query executes only once for the result returned by the inner query.
  • Each row returned by the outer query is evaluated for the results returned by the inner query.

Answer :The nested query executes after the outer query returns the row. Each row returned by the outer query is evaluated for the results returned by the inner query.

The BOOKS_TRANSACTIONS table exists in your schema in this database.
You execute this SQL statement when connected to your schema in your database instance.
SQL> SELECT * FROM books_transactions ORDER BY 3;
What is the result?


Options are :

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

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

1Z0-330 Oracle Fusion Workforce Compensation Cloud Test Set 4

Which three statements are true regarding group functions? (Choose three.)


Options are :

  • They can be passed as an argument to another group function.
  • They can be used on columns or expressions.
  • They can be used on only one column in the SELECT clause of a SQL statement.
  • They can be used only with a SQL statement that has the GROUP BY clause.
  • They can be used along with the single-row function in the SELECT clause of a SQL statement.

Answer :They can be passed as an argument to another group function. They can be used on columns or expressions. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Which two statements are true regarding single row functions? (Choose two.)


Options are :

  • MOD : returns the quotient of a division.
  • DYSDATE : returns the database server current date and time.
  • CONCAT : can be used to combine any number of values.
  • INSTR : can be used to find only the first occurrence of a character in a string.
  • TRIM : can be used to remove all the occurrences of a character from a string.
  • TRUNC : can be used with NUMBER and DATE values.

Answer :DYSDATE : returns the database server current date and time. TRUNC : can be used with NUMBER and DATE values.

Which two statements are true regarding the USING and ON clauses in table joins?


Options are :

  • Both USING and ON clauses can be used for equijoins and nonequijoins.
  • A maximum of one pair of columns can be joined between two tables using the ON clause.
  • The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
  • The ON clause can be used to join tables on columns that have different names but compatible data types.

Answer :The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause. The ON clause can be used to join tables on columns that have different names but compatible data types.

1Z0-330 Oracle Fusion Workforce Compensation Cloud Test Set 5

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


Examine the following SQL statement:
SELECT order_id, product_id, unit_price

FROM order_items -
WHERE unit_price =
(SELECT MAX(unit_price)

FROM order_items -
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. What correction should be made in the above SQL statement to achieve this?


Options are :

  • Replace = with the IN operator
  • Remove the GROUP BY clause from the subquery and place it in the main query
  • Replace = with the >ANY operator
  • Replace = with the >ALL operator

Answer :Replace = with the IN operator

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


Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have "Not Available" displayed.
Which SQL statement would produce the required result?


Options are :

  • SELECT NVL(cust_credit_limit*.15, ‘Not Available’) “NEW CREDIT” FROM customers;
  • SELECT NVL(TO_CHAR(cust_credit_limit*.15), ‘Not Available’) “NEW CREDIT” FROM customers;
  • SELECT TO_CHAR(NVL(cust_credit_limit*.15, ‘Not Available’)) “NEW CREDIT” FROM customers;
  • SELECT NVL(cust_credit_limit, ‘Not Available’)*.15 “NEW CREDIT” FROM customers;

Answer :SELECT NVL(TO_CHAR(cust_credit_limit*.15), ‘Not Available’) “NEW CREDIT” FROM customers;

View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.


You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?


Options are :

  • UPDATE dept d SET city = ANY (SELECT city FROM locations l)
  • UPDATE dept d SET city = ALL (SELECT city FROM locations l WHERE d.location_id = l.location_id);
  • UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id = l.location_id);
  • UPDATE dept d SET city = (SELECT city FROM locations l) WHERE d.location_id = l.location_id;

Answer :UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id = l.location_id);

1Z0-409 Oracle Linux Fundamentals Practice Exam Set 5

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.


The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)

AS -
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?


Options are :

  • The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
  • The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
  • The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
  • The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

Answer :The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

Examine the structure of the EMPLOYEES table.



You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)


Options are :

  • SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365) GROUP BY maxsal, minsal;
  • SELECT MIN(Salary), MAX(salary) FROM (SELECT salary FROM employees WHERE hire_date < SYSDATE-365);
  • SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary));
  • SELECT MIN(Salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);

Answer :SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365) GROUP BY maxsal, minsal; SELECT MIN(Salary), MAX(salary) FROM (SELECT salary FROM employees WHERE hire_date < SYSDATE-365);

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


Options are :

  • A subquery can retrieve zero or more rows.
  • A subquery can be used only in SQL query statements.
  • A subquery can appear on either side of a comparison operator.
  • There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
  • Only two subqueries can be placed at one level.

Answer :A subquery can retrieve zero or more rows. A subquery can appear on either side of a comparison operator.

OCA Oracle Database 10g Administrator I-1Z0-042 & II-1Z0-043 Set 3

Which two statements are true regarding the SQL GROUP BY clause?


Options are :

  • If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.
  • You can use a column alias in the GROUP BY clause.
  • Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
  • Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
  • The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.

Answer :If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.

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


You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the "INTERNET" category.
Which query would give you the required output?


Options are :

  • SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = "INTERNET");
  • SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ANY (SELECT promo_begin_date FROM promotions WHERE promo_category= "INTERNET");
  • SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date) FROM promotions) AND promo_category= "INTERNET";
  • SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category= "INTERNET");

Answer :SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = "INTERNET");

You execute the SQL statement:


What is the outcome?


Options are :

  • It succeeds and an index is created for CITIZEN_ID.
  • It succeeds and CITY can contain only "SEATTLE" or null for all rows.
  • It fails because the condition for the CNAMES constraint is not valid.
  • It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.

Answer :It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.

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

Examine the following query:
SQL> SELECT prod_id, amount_sold

FROM sales -

ORDER BY amount_sold -
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?


Options are :

  • It displays 5 percent of the products with the lowest amount sold.
  • It results in an error because the ORDER BY clause should be the last clause.
  • It displays 5 percent of the products with the highest amount sold.
  • It displays the first 5 percent of the rows from the SALES table.

Answer :It displays 5 percent of the products with the lowest amount sold.

The first DROP operation is performed on PRODUCTS table using this command:
DROP TABLE products PURGE;
Then a FLASHBACK operation is performed using this command:
FLASHBACK TABLE products TO BEFORE DROP;
Which is true about the result of the FLASHBACK command?


Options are :

  • It recovers only the table structure.
  • It is not possible to recover the table structure, data, or the related indexes.
  • It recovers the table structure, data, and the indexes.
  • It recovers the table structure and data but not the related indexes.

Answer :It is not possible to recover the table structure, data, or the related indexes.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions