Make success possible with our Latest and Unique Oracle Database 12c 1z0-071 Practice Exam!
Name: Oracle Database 12c SQL
Exam Code: 1z0-071
Certification: Oracle Database 12c
Vendor: Oracle
Total Questions: 326
Last Updated: July 07, 2025
419 Satisfied Customers
Success is simply the result of the efforts you put into the preparation. We at Dumpsgroup wish to make that preparation a lot easier. The Oracle Database 12c SQL 1z0-071 Practice Exam we offer is solely for best results. Our IT experts put in their blood and sweat into carefully selecting and compiling these unique Practice Questions. So, you can achieve your dreams of becoming a Oracle Database 12c professional. Now is the time to press that big buy button and take the first step to a better and brighter future.
Passing the Oracle 1z0-071 exam is simpler if you have globally valid resources and Dumpsgroup provides you just that. Millions of customers come to us daily, leaving the platform happy and satisfied. Because we aim to provide you with Oracle Database 12c Practice Questions aligned with the latest patterns of the Oracle Database 12c SQL Exam. And not just that, our reliable customer services are 24 hours at your beck and call to support you in every way necessary. Order now to see the 1z0-071 Exam results you always desired.
You must have heard about candidates failing in a large quantity and perhaps tried yourself and fail to pass Oracle Database 12c SQL. It is best to try Dumpsgroup’s 1z0-071 Practice Questions this time around. Dumpsgroup not only provides an authentic, valid, and accurate resource for your preparation. They simplified the training by dividing it into two different formats for ease and comfort. Now you can get the Oracle 1z0-071 in both PDF and Online Test Engine formats. Choose whichever or both to start your Oracle Database 12c certification exam preparation.
Furthermore, Dumpsgroup gives a hefty percentage off on these Spoto 1z0-071 Practice Exam by applying a simple discount code; when the actual price is already so cheap. The updates for the first three months, from the date of your purchase, are FREE. Our esteemed customers cannot stop singing praises of our Oracle 1z0-071 Practice Questions. That is because we offer only the questions with the highest possibility of appearing in the actual exam. Download the free demo and see for yourself.
We know you have been struggling to compete with your colleagues in your workplace. That is why we provide the 1z0-071 Practice Questions to let you gain the upper hand that you always wanted. These questions and answers are a thorough guide in a simple and exam-like format! That makes understanding and excelling in your field way lot easier. Our aim is not just to help to pass the Oracle Database 12c Exam but to make a Oracle professional out of you. For that purpose, our 1z0-071 Practice Exams are the best choice.
There are many resources available online for the preparation of the Oracle Database 12c SQL Exam. But that does mean that all of them are reliable. When your future as a Oracle Database 12c certified is at risk, you have got to think twice while choosing Oracle 1z0-071 Practice Questions. Dumpsgroup is not only a verified source of training material but has been in this business for years. In those years, we researched on 1z0-071 Practice Exam and came up with the best solution. So, you can trust that we know what we are doing. Moreover, we have joined hands with Oracle experts and professionals who are exceptional in their skills. And these experts approved our 1z0-071 Practice Questions for Oracle Database 12c SQL preparation.
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?
A. It executes successfully and generates the required result.
B. It produces an error because COUNT (*) should be specified in the SELECT clause also.
C. It produces an error because COUNT (*) should be only in the HAVING clause and not in the WHERE clause.
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
ANSWER : C
In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10
years.
Evaluate the following SQL statement:
What would be the result?
A. It executes successfully but no rows updated.
B. It executes successfully and updates the records of those employees who have been working in the
company for more than 600 days.
C. It gives an error because multiple NVL functions are used in an expression.
D. It gives an error because NVL function cannot be used with UPDATE.
ANSWER : B
Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?
A. DELTE and TRUNCATE statements can have a rollback done to restore data into a table.
B. DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed.
C. DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows
of a table.
D. DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.
ANSWER : D
View the Exhibit and examine the structure of the PRODUCTS table.
You must display the category with the maximum number of items.You issue this query:
What is the result?
A. It generates an error because = is not valid and should be replaced by the IN operator.
B. It executes successfully but does not give the correct output.
C. It executes successfully and gives the correct output.
D. It generate an error because the subquery does not have a GROUP BY clause.
ANSWER : D
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?
A. Both the statements execute and give different results
B. Only the second SQL statement executes successfully
C. Both the statements execute and give the same result
D. Only the first SQL statement executes successfully
ANSWER : A
You execute this query:
What is the result?
A. It returns the date for the first Monday of the next month.
B. It returns the date for the last Monday of the current month.
C. It executes successfully but does not return any result.
D. It generates an error.
ANSWER : A
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.)
A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date <
SYSDATE-365GROUP BY MIN(salary), MAX(salary);
B. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM
employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;
C. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM
employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary));
D. SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROMemployeesWHERE hire_date < SYSDATE-365);
ANSWER : B,D
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.Which two SQL statements would create the required table? (Choose two.)
A. CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
B. CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
C. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,login_id NUMBER UNIQUE,employee_name VARCHAR2(25),hire_date DATE);
D. CREATE TABLE employees(employee_id NUMBER,login_id NUMBER,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));
E. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,login_id NUMBER CONSTRAINT login_id_nn NOT NULL,employee_name VARCHAR2(100),hire_date DATE,CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));
ANSWER : B,E
Which three statements are true about GLOBAL TEMPORARY TABLES?
A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose
user has been granted select on the table.
B. GLOBAL TEMPORARY TABLE space allocation occurs at session start.
C. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
D. A GLOBAL TEMPORARY TABLE’s definition is available to multiple sessions.
E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
F. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for
the issuing session to be deleted.
ANSWER : A,B,C
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?
A. Remove the GROUP BY clause from the subquery and place it in the main query
B. Replace = with the >ANY operator
C. Replace = with the >ALL operator
D. Replace = with the IN operator
ANSWER : D
Examine the create table statements for the stores and sales tables.SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE); SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));You executed the following statement:SQL> DELETE from stores WHERE store_id=900;The statement fails due to the integrity constraint error:ORA-02292: integrity constraint (HR.STORE_ID_FK) violatedWhich three options ensure that the statement will execute successfully? (Choose three.)
A. Disable the primary key in the STORES table.
B. Use CASCADE keyword with DELETE statement.
C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
D. Disable the FOREIGN KEY in SALES table and then delete the rows.
E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
ANSWER : C,D,E
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:
You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table.
You execute the SQL statement:
What is the outcome?
A. It fails because the join type used is incorrect.
B. It executes successfully and displays the required list.
C. It executes successfully but displays an incorrect list.
D. It fails because the ON clause condition is not valid.
ANSWER : B
Which statement is true regarding the UNION operator?
A. By default, the output is not sorted.
B. Null values are not ignored during duplicate checking.
C. Names of all columns must be identical across all select statements.
D. The number of columns selected in all select statements need not be the same.
ANSWER : B
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: SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER (cust_last_name) LIKE ‘B%’ OR UPPER (cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
A. Only the second query gives the correct result
B. Both execute successfully but do not give the required result
C. Only the first query gives the correct result
D. Both execute successfully and give the same result
ANSWER : C
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);
A. Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.
B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.
C. Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.
D. 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.
ANSWER : D
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?
A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL
(SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;
B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT
promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL
(SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);
D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY
(SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
ANSWER : C
You are designing the structure of a table in which two columns have the specifications: COMPONENT_ID – must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row EXECUTION_DATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components. Which two options define the data types that satisfy these requirements most efficiently? (Choose two.)
A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
B. The EXECUTION_DATETIME must be of TIMESTAMP data type.
C. The EXECUTION_DATETIME must be of DATE data type.
D. The COMPONENT_ID must be of ROWID data type.
E. The COMPONENT_ID must be of VARCHAR2 data type.
F. The COMPONENT_ID column must be of CHAR data type.
ANSWER : C,F
Examine the description of the BOOKS_TRANSACTIONS table:
Examine this partial SQL statement: SELECT * FROM books_transactionsWhich two WHERE conditions give the same result?
A. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’));
B. WHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’, ‘A102’);
C. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id = A101’ OR member_id = ‘A102’));
D. WHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’);
E. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’);
ANSWER : B,C
View the exhibit and examine the structure of the STORES table.
You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and
the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and
above.
Which SQL statement would get the desired output?
A. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date, property_price,
property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,'01-JAN-2000') <=36;
B. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date,property_price,
property_price*115/100FROM storesWHERE
TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
C. SELECT name, address||', '||city||', '||country AS full_address, start_date,property_price,
property_price*115/100FROM storesWHERE
MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
D. SELECT name, concat(address||', '||city||', ', country) AS full_address, start_date,property_price,
property_price*115/100FROM storesWHERE
MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
ANSWER : D
View the exhibit for the structure of the STUDENT and FACULTY tables.
You need to display the faculty name followed by the number of students handled by the faculty at the base
location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?
A. Only statement 2 executes successfully and gives the required result.
B. Only statement 1 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.
ANSWER : B
Examine the structure of the BOOKS_TRANSACTIONS table:
You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?
A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
ANSWER : C
Which two are true about the WITH GRANT OPTION clause?
A. The grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option.
B. It can be used when granting privileges to roles.
C. It cannot be used to pass on privileges to PUBLIC by the grantee.
D. It can be used for system and object privileges.
E. It can be used to pass on privileges to other users by the grantee.
F. The grantee can grant the object privilege to any user in the database, with or without including this
option.
ANSWER : D,E?
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.)
A. WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21
2003','MON DD YYYY') )
B. WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
C. WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
D. WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
ANSWER : C,D
Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS?
A. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
B. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of
fractional seconds
C. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the
database server.
D. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
E. SYSDATE can be queried only from the DUAL table.
F. CURRENT_DATE returns the current date and time as per the session time zone.
ANSWER : E,F
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
Examine this SQL statement:SELECT department_id "DEPT_ID", department_name, 'b' FROM departmentsWHERE departments_id=90 UNIONSELECT department_id, department_name DEPT_NAME, 'a' FROM departmentsWHERE department_id=10Which two ORDER BY clauses can be used to sort the output? (Choose two.)
A. ORDER BY DEPT_NAME;
B. ORDER BY DEPT_ID;
C. ORDER BY 'b';
D. ORDER BY 3;
ANSWER : B,D
These are the steps for a correlated subquery, listed in random order:The WHERE clause of the outer query is evaluated.A candidate row is fetched from the table specified in the outer query.This is repeated for the subsequent rows of the table, until all the rows are processed.Rows are returned by the inner query, after being evaluated with the value from the candidate row in theouter query.Which is the correct sequence in which the Oracle server evaluates a correlated subquery?
A. 2, 1, 4, 3
B. 4, 1, 2, 3
C. 4, 2, 1, 3
D. 2, 4, 1, 3
ANSWER : D
In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained a query?
A. PROMO_BEGIN_DATE – 5 will return a date.
B. PROMO_BEGIN_DATE – SYSDATE will return a number.
C. TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number.
D. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.
E. PROMO_BEGIN_DATE – SYSDATE will return an error.
ANSWER : A,D
Which three statements are true regarding subqueries? (Choose three.)
A. The ORDER BY Clause can be used in a subquery.
B. A subquery can be used in the FROM clause of a SELECT statement.
C. If a subquery returns NULL, the main query may still return rows.
D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
ANSWER : A,B,C
View and Exhibit and examine the structure and data in the INVOICE table.
Which two statements are true regarding data type conversion in query expressions? (Choose two.)
A. inv_date = '15-february-2008' :uses implicit conversion
B. inv_amt = '0255982' : requires explicit conversion
C. inv_date > '01-02-2008' : uses implicit conversion
D. CONCAT(inv_amt, inv_date) : requires explicit conversion
E. inv_no BETWEEN '101' AND '110' : uses implicit conversion
ANSWER : A,E
View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.
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? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.
A. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE 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_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total)VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
C. INSERT INTO ordersVALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total)VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000);
ANSWER : C
Examine the structure of the SALES table. (Choose two.)
Examine this statement:
A. It will not be created because the column-specified names in the SELECT and CREATE TABLE
clauses do not match.
B. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES
table.
C. It will not be created because of the invalid WHERE clause.
D. It is created with no rows.
E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in
the SALES table
ANSWER : B,D?
Examine the data in the CUSTOMERS table:
You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)
A. LEFT OUTER JOIN
B. JOIN
C. NATURAL JOIN
D. RIGHT OUTER JOIN
E. FULL OUTER JOIN
ANSWER : B,D
Examine the description of the SALES1 table:
SALES2 is a table with the same description as SALES1.
Some sales data is duplicated in both tables.
You want to display the rows from the SALES1 table which are not present in the SALES2 table. Which set operator generates the required output?
A. SUBTRACT
B. INTERSECT
C. UNION ALL
D. UNION
E. MINUS
ANSWER : E
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?
A. Both execute successfully and give correct results.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E.. Only the second query executes successfully and gives the correct result.
ANSWER : C
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)
A. 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.
B. During execution, the Oracle server may read data from storage if the required data is not already in
memory.
C. During row source generation, rows that satisfy the query are retrieved from the database and stored in
memory
D. During optimization, execution plans are formulated based on the statistics gathered by the database
instance, and the lowest cost plan is selected for execution
ANSWER : B
The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully?
A. SELECT NVL (cust_credit_limit * .15, ‘Not Available’) FROM customers;
B. SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
D. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM
customers;
E. SELECT NVL2 (cust_credit_limit * .15, ‘Not Available’) FROM customers;
ANSWER : C,E
View the Exhibit and examine the description of the tables.
You execute this SQL statement:
Which three statements are true?
A. The statement will execute successfully and a new row will be inserted into the SALES table.
B. A product can have a different unit price at different times.
C. The statement will fail because a subquery may not be contained in a VALUES clause.
D. The statement will fail if a row already exists in the SALES table for product 23.
E. A customer can exist in many countries.
F. The SALES table has five foreign keys.
ANSWER : A,E,F
Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)
A. The CUST_STATUS column would store exactly one character.
B. The TRANS_VALIDITY column would have a maximum size of one character.
C. The CUST_CREDIT_LIMIT column would be able to store decimal values.
D. The CUST_STATUS column would give an error.
E. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
F. The TRANS_VALIDITY column would give an error.
ANSWER : A,F
On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command: $> sqlldr hr/hr@pdb table=employees Which two statements are true regarding the command? (Choose two.)
A. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the
database.
B. It fails because no SQL *Loader data file location is specified.
C. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
D. It fails because no SQL *Loader control file location is specified.
ANSWER : A,C
Which statement is true about the INTERSECT operator used in compound queries?
A. INTERSECT is of lower precedence than UNION or UNION ALL.
B. Multiple INTERSECT operators are not possible in the same SQL statement.
C. It ignores NULLs.
D. It processes NULLs in the selected columns.
ANSWER : D
Which three statements are true regarding subqueries? (Choose three.)
A. Multiple columns or expressions can be compared between the main query and subquery.
B. Subqueries can contain ORDER BY but not the GROUP BY clause.
C. Main query and subquery can get data from different tables.
D. Subqueries can contain GROUP BY and ORDER BY clauses.
E. Main query and subquery must get data from the same tables.
F. Only one column or expression can be compared between the main query and subquery.
ANSWER : A,C,D
Which statement is true about an inner join specified in the WHERE clause of a query?
A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
B. It requires the column names to be the same in all tables used for the join conditions.
C. It is applicable for equijoin and nonequijoin conditions.
D. It is applicable for only equijoin conditions.
ANSWER : C
Examine the description of the EMPLOYEES table:
Which query is valid?
A. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
B. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
C. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
D. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
ANSWER : D
Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
ANSWER : A,D
You must create a table for a banking application.One of the columns in the table has these requirements: Which data type would you use?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
ANSWER : D
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?
A. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in
the column definition
B. 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.
C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE
command and the SELECT clause do not match.
D. 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.
ANSWER : B
Examine the structure of the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;
ANSWER : A,C
Examine this query:
Which two methods should you use to prevent prompting for a hire date value when this query is executed?
A. Replace ‘&1’ with ‘&&1’ in the query.
B. Use the DEFINE command before executing the query.
C. Use the UNDEFINE command before executing the query.
D. Execute the SET VERIFY ON command before executing the query.
E. Store the query in a script and pass the substitution value to the script when executing it.
F. Execute the SET VERIFY OFF command before executing the query.
ANSWER : D,E
Which statement is true regarding the USING clause in table joins? (Choose two.)
A. It can be used to join a maximum of three tables.
B. It can be used to access data from tables through equijoins as well as nonequijoins.
C. It can be used to join tables that have columns with the same name and compatible data types.
D. It can be used to restrict the number of columns used in a NATURAL join.
ANSWER : C,D
Dumpsgroup is a verified platform for certification preparation. Our expert professionals are working day and night to create the perfect Practice Exam so you can pass your exam with flying colors. We provide only the authentic and valid Practice Questions approved by IT Experts. Start your certification journey now!
www.dumpsgroup.com
support@dumpsgroup.com
1104 Long Street, NY, 11206, USA
Feel free to contact us, we are available 24/7.