2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 1Z0-071Q&As
Oracle Database 12c SQL
 Pass Oracle 1Z0-071 Exam with 100% Guarantee
Free Download Real Questions & Answers PDF and VCE file from:
 
100% Passing Guarantee
 100% Money Back Assurance
 Following Questions and Answers are all new published by Oracle
 Official Exam Center
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 1 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 1
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?
 A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING
 (department_id)
 B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN .department_details d ON
 (c.depatrment_id=d.department_id)
 C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON
 (c.department_id=d. department_id)
 D. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON
 (c.department_idd. department_id)
 
 QUESTION 2
View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose the best answer.)
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 2 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 PRODUCT_ID column is the primary key.
 You create an index using this command:
 SQL > CREATE INDEX upper_name_idx
 ON product_information(UPPER(product_name));
 No other indexes exist on the PRODUCT_INFORMATION table.
 Which query would use the UPPER_NAME_IDX index?
 A. SELECT product_id, UPPER(product_name) FROM product_information WHERE UPPER(product_name) =
 \\'LASERPRO\\' OR list_price > 1000;
 B. SELECT UPPER(product_name) FROM product_information;
 C. SELECT UPPER(product_name) FROM product_information WHERE product_id = 2254;
 D. SELECT product_id FROM product_information WHERE UPPER(product_name) IN (\\'LASERPRO\\', \\'CABLE\\');
 
 QUESTION 3
You must display details of all users whose username contains the string \\'ch_\\'. (Choose the best answer.)
 Which query generates the required output?
 A. SELECT * FROM users Where user_name LIKE \\'%ch_\\';
 B. SELECT * FROM users Where user_name LIKE \\'%ch_%\\'ESCAPE\\'%\\';
 C. SELECT * FROM users Where user_name LIKE \\'ch\_%\\' ESCAPE \\'_\\';
 D. SELECT * FROM users Where user_name LIKE \\'%ch\_%\\' ESCAPE \\'\\\';
 
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 3 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 4
Which statement is true regarding external tables?
 A. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an
 external table.
 B. The data and metadata for an external table are stored outside the database.
 C. The default REJECT LIMIT for external tables is UNLIMITED.
 D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external
 table.
 
 References: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm
 QUESTION 5
Which two statements about sub queries are true? (Choose two.)
 A. A sub query should retrieve only one row.
 B. A sub query can retrieve zero or more rows.
 C. A sub query can be used only in SQL query statements.
 D. Sub queries CANNOT be nested by more than two levels.
 E. A sub query CANNOT be used in an SQL query statement that uses group functions.
 F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the
 SELECT clause of the sub query should contain only one column.
 F
 sub query can retrieve zero or more rows, sub query is used with an inequality comparison operator in the outer SQL
 statement, and the column list in the SELECT clause of the sub query should contain only one column. Incorrect
 answer:
 A. sub query can retrieve zero or more rows
 C. sub query is not SQL query statement
 D. sub query can be nested
 E. group function can be use with sub query
 QUESTION 6
1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 4 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 You notice a performance change in your production Oracle 12c database. You want to know which change caused this
 performance difference.
 Which method or feature should you use?
 A. Compare Period ADDM report.
 B. AWR Compare Period report.
 C. Active Session History (ASH) report.
 D. Taking a new snapshot and comparing it with a preserved snapshot.
 
 QUESTION 7
Evaluate the following statement.
 Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
 A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN
 clause.
 B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the
 subsequent WHEN clauses.
 C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the
 subsequent WHEN clauses.
 D. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN
 clauses are true.
 
 References: http://psoug.org/definition/WHEN.htm
 QUESTION 8
1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 5 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.
 SALE_VW is a view created using the following command:
 SQL>CREATE VIEW sale_vw AS
 SELECT prod_id, SUM(quantity_sold) QTY_SOLD
 FROM sales GROUP BY prod_id;
 You issue the following command to add a row to the SALE_SUMMARY table:
 SQL>INSERT INTO sale_summary
 SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
 USING (prod_id) WHERE prod_id = 16; What is the outcome?
 A. It executes successfully.
 B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
 C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
 D. It gives an error because the number of columns to be inserted does not match with the number of columns in the
 SALE_SUMMARY table.
 
 QUESTION 9
Which four are valid Oracle constraint types? (Choose four.)
 A. CASCADE
 B. UNIQUE
 C. NONUNIQUE
 D. CHECK
 E. PRIMARY KEY
 F. CONSTANT
 G. NOT NULL
 DEG
 Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and Unique Incorrect answer:
 A. Is not Oracle constraint
 C. Is not Oracle constraint
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 6 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 F. Is not Oracle constraint Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-3
 QUESTION 10
The SQL statements executed in a user session as follows: Which two statements describe the consequence of issuing
 the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
 A. Both the DELETE statements and the UPDATE statement are rolled back
 B. The rollback generates an error
 C. Only the DELETE statements are rolled back
 D. Only the seconds DELETE statement is rolled back
 E. No SQL statements are rolled back
 E
 QUESTION 11
Which two statements are true about Data Manipulation Language (DML) statements?
 A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
 B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
 C. A DELETE FROM..... statement can remove rows based on only a single condition on a table.
 D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
 E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
 F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.
 E
 References: http://www.techonthenet.com/sql/and_or.php
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 7 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 12
Which two statements complete a transaction? (Choose two)
 A. DELETE employees;
 B. DESCRIBE employees;
 C. ROLLBACK TO SAVEPOINT C;
 D. GRANT SELECT ON employees TO SCOTT;
 E. ALTER TABLE employeesSET UNUSED COLUMN sal;
 F. Select MAX(sal)FROM employeesWHERE department_id = 20;
 E
 D: GRANT is a DML operation which will cause an implicit commit
 E: It is important to understand that an implicit COMMIT occurs on the database when a user exits SQL*Plus or issues a
 data-definition language (DDL) command such as a CREATE TABLE statement, used to create a database object, or
 an
 ALTER TABLE statement, used to alter a database object.
 Incorrect Answers
 A:. The DELETE command is data-manipulation language (DML) command and it does not complete a transaction.
 B:. The DESCRIBE command is internal SQL*Plus command and it has nothing to do with completion a transaction.
 C: ROLLBACK is not used to commit or complete a transaction, it is used to undo a transaction
 F:. SELECT command is used to retrieve data. It does not complete a transaction.
 OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282 Chapter 3: Advanced Data Selection in
 Oracle
 QUESTION 13
Which two statements are true regarding constraints? (Choose two)
 A. A constraint is enforced only for an INSERT operation on a table.
 B. A foreign key cannot contain NULL values.
 C. The column with a UNIQUE constraint can store NULLS.
 D. You can have more than one column in a table as part of a primary key.
 D
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 8 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 14
Evaluate the following query
 What is the correct output of the above query?
 A. +00-300, +00-650, +00 11:12:10.123457
 B. +25-00, +54-02, +00 11:12:10.123457
 C. +00-300, +54-02, +00 11:12:10.123457
 D. +25-00, +00-650, +00 11:12:10.123457
 
 QUESTION 15
View the Exhibit and examine the structure of CUSTOMERS table.
 Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all
 customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
 Which SQL statement would produce the required result?
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 9 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 A. SELECT NVL (TO CHAR(cust_credit_limit * .15), \\'Not Available\\') "NEW CREDIT" FROM customers;
 B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), \\'Not Available\\') "NEW CREDIT" FROM customers;
 C. SELECT NVL(cust_credit_limit * .15), \\'Not Available\\') "NEW CREDIT" FROM customers;
 D. SELECT NVL(cust_credit_limit), \\'Not Available\\') "NEW CREDIT" FROM customers;
 
 QUESTION 16
View the Exhibit and examine the data in the EMPLOYEES table. Exhibit
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 10 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 You want to generate a report showing the total compensation paid to each employee to date. You issue the following
 query:
 What is the outcome?
 A. It executes successfully but does not give the correct output.
 B. It generates an error because the concatenation operator can be used to combine only two items.
 C. It generates an error because the usage of the ROUND function in the expression is not valid.
 D. It generates an error because the alias is not valid.
 E. IT executes successfully and gives the correct output.
 
 QUESTION 17
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently
 empty.
 Which statement accomplishes this task?
 A. ALTER TABLE students ADD PRIMARY KEY student_id;
 B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
 C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
 D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
 E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
 
 ALTER TABLE table_name
 ADD [CONSTRAINT constraint] type (coloumn);
 Incorrect answer:
 A. wrong syntax
 B. wrong syntax
 C. wrong syntax
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 11 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 E. no such MODIFY keyword
 Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-17
 QUESTION 18
View the Exhibit and examine the description of the EMPLOYEES table.
 You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and
 the percentage commission earned for a year. Only a few employees earn commission.
 Which SQL statement would you execute to get the desired output?
 A. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))"Total" FROM
 EMPLOYEES;
 B. SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES;
 C. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 "Total" FROM EMPLOYEES;
 D. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, "Total" FROM EMPLOYEES;
 
 QUESTION 19
Evaluate the following ALTER TABLE statement:
 ALTER TABLE orders
 SET UNUSED (order_date);
 Which statement is true?
 A. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS
 table.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 12 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 B. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
 C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
 D. The DESCRIBE command would still display the ORDER_DATE column.
 
 QUESTION 20
Examine the structure of the MEMBERS table.
 Which query can be used to display the last names and city names only for members from the states MO and MI?
 A. SELECT last_name, city FROM members WHERE state =\\'MO\\' AND state =\\'MI\\';
 B. SELECT last_name, city FROM members WHERE state LIKE \\'M%\\';
 C. SELECT last_name, city FROM members WHERE state IN (\\'MO\\', \\'MI\\');
 D. SELECT DISTINCT last_name, city FROM members WHERE state =\\'MO\\' OR state =\\'MI\\';
 
 QUESTION 21
Which statement is true about Data Manipulation Language (DML)?
 A. DML automatically disables foreign ley constraints when modifying primary key values in the parent table.
 B. Each DML statement forms a transaction by default.
 C. A transaction can consist of one or more DML statements.
 D. 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.
 
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 13 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 22
You issue the following command to drop the PRODUCTS table:
 SQL > DROP TABLE products;
 Which three statements are true about the implication of this command? (Choose three.)
 A. All data along with the table structure is deleted.
 B. A pending transaction in the session is committed.
 C. All indexes on the table remain but they are invalidated.
 D. All views and synonyms on the table remain but they are invalidated.
 E. All data in the table is deleted but the table structure remains.
 BD
 QUESTION 23
Which SQL statement returns a numeric value?
 A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
 B. SELECT ROUND(hire_date) FROM EMP;
 C. SELECT sysdate-hire_date FROM EMP;
 D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;
 
 DATE value subtract DATE value will return numeric value. Incorrect answer:
 A. does not return numeric value
 B. does not return numeric value
 D. does not return numeric value
 Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-13
 QUESTION 24
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.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 14 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 C. It is applicable for equijoin and nonequijoin conditions.
 D. It is applicable for only equijoin conditions.
 
 QUESTION 25
You need to create a table named ORDERS that contain four columns:
 1.
 AN ORDER_ID column of number data type
 2.
 A CUSTOMER_ID column of number data type
 3.
 AN ORDER_STATUS column that contains a character data type
 4.
 A DATE_ORDERED column to contain the date the order was placed.
 When a row is inserted into the table, if no value is provided when the order was placed, today\\'s date should be used
 instead.
 Which statement accomplishes this?
 A. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10),
 date_ordered DATE = SYSDATE);
 B. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10),
 date_ordered DATE DEFAULT SYSDATE);
 C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status
 VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
 D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status
 VARCHAR2 (10), date_ordered DATE = SYSDATE);
 E. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10),
 date_ordered DATE = SYSDATE);
 F. CREATE TABLE orders (order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10),
 date_ordered DATE DEFAULT SYSDATE);
 
 Requirement that Order_Status should be a character data type
 Not E: Order_status must be a character data type. There is also a syntax error.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 15 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 26
View the Exhibit and examine the structure of the PROMOTIONS table.
 Evaluate the following SQL statement:
 Which statement is true regarding the outcome of the above query?
 A. It produces an error because subqueries cannot be used with the CASE expression.
 B. It shows COST_REMARK for all the promos in the promo category `TV\\'.
 C. It shows COST_REMARK for all the promos in the table.
 D. It produces an error because the subquery gives an error.
 
 QUESTION 27
View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 16 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 You want to see the product names and the date of expiration of warranty for all the products, if the product is
 purchased today. The products that have no warranty should be displayed at the top and the products with maximum
 warranty period should be displayed at the bottom.
 Which SQL statement would you execute to fulfill this requirement?
 A. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER
 BY SYSDATE-warranty_period
 B. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER
 BY SYSDATE+warranty_period
 C. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER
 BY SYSDATE
 D. SELECT product_name, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information WHERE
 warranty_period > SYSDATE
 
 QUESTION 28
The customers table has the following structure:
 You need to write a query that does the following tasks:
 1.
 Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 17 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 2.
 Only those customers whose income level has a value should be considered.
 3.
 Customers whose tax amount is null should not be considered.
 Which statement accomplishes all the required tasks?
 A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS
 NOT NULL AND tax_amount IS NOT NULL;
 B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS
 NOT NULL AND cust_credit_limit IS NOT NULL;
 C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level
 NULL AND tax_amount NULL;
 D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE (cust_income_level,
 tax_amount) IS NOT NULL;
 
 QUESTION 29
Evaluate the following CREATE SEQUENCE statement:
 CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
 The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
 SELECT seq1.nextval FROM dual;
 What is displayed by the SELECT statement?
 A. 100
 B. an error
 C. 10
 D. 1
 
 QUESTION 30
Evaluate the following statement.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 18 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
 A. 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.
 B. All rows are evaluated by all the three WHEN clauses.
 C. 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.
 D. The INSERT statement will return an error because the ELSE clause is missing.
 
 QUESTION 31
View the Exhibit and examine the details of the PRODUCT_INFORMATION table. Exhibit
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 19 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 You must 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 this SQL statement:
 Which statement is true regarding the execution?
 A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
 B. It would execute but would return no rows.
 C. It would not execute because the same column has been used twice with the AND logical operator.
 D. It would execute and return the desired result.
 
 QUESTION 32
View the Exhibit and examine the structure of the CUSTOMERS table.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 20 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 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
 SQL>SELECT cust_last_name, cust_credit_limit FROM customers
 WHERE UPPER (cust_last_name) BETWEEN `A\\' AND `C\\'
 AND cust_credit_limit
 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
 
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 21 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 33
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(*).
 
 QUESTION 34
View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 22 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 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 views successfully?
 A. 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;
 B. 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;
 C. 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;
 D. 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;
 
 QUESTION 35
Which two statements are true regarding constraints?
 A. A foreign key column cannot contain null values.
 B. A column with the UNIQUE constraint can contain null values.
 C. A constraint is enforced only for INSERT operation on the table.
 D. A constraint can be disabled even if the constraint column contains data.
 E. All constraints can be defined at the column level and at the table level.
 D
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 23 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 36
Which two statements are true regarding constraints? (Choose two.)
 A. All constraints can be defined at the column level and at the table level.
 B. A constraint can be disabled even if the constraint column contains data.
 C. A column with the UNIQUE constraint can contain NULLS.
 D. A foreign key column cannot contain NULLS.
 E. A constraint is enforced only for INSERT operations.
 C
 QUESTION 37
View the Exhibit and examine the data in the PRODUCTS table.
 Which statement would add a column called PRICE, which cannot contain NULL?
 A. ALTER TABLE products ADD price NUMBER(8,2) NOT NULL;
 B. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT NOT NULL;
 C. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
 D. ALTER TABLE products ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL.
 
 QUESTION 38
Examine the data in the ORD_ITEMS table:
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 24 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 Evaluate the following query: SQL>SELECT item_no, AVG(qty) FROM ord_items
 HAVING AVG(qty) > MIN(qty) * 2
 GROUP BY item_no;
 Which statement is true regarding the outcome of the above query?
 A. It gives an error because the HAVING clause should be specified after the GROUP BY clause.
 B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT
 list.
 C. It displays the item nos with their average quantity where the average quantity is more than double the minimum
 quantity of that item in the table.
 D. It displays the item nos with their average quantity where the average quantity is more than double the overall
 minimum quantity of all the items in the table.
 
 QUESTION 39
Which two statements are true regarding multiple-row subqueries? (Choose two.)
 A. They can contain group functions.
 B. They always contain a subquery within a subquery.
 C. They use the
 D. They can be used to retrieve multiple rows from a single table only.
 E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the
 subquery.
 E
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 25 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 QUESTION 40
Examine the structure of the EMP_DEPT_VU view:
 Which SQL statement produces an error?
 A. SELECT *FROM emp_dept_vu;
 B. SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department_id;
 C. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department_id, job_id;
 D. SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10, 20) GROUP BY job_id HAVING
 SUM(salary) > 20000;
 E. None of the statements produce an error; all are valid.
 Correct Answer: E
 None of the statements produce an error. Incorrect answer:
 A. Statement will not cause error
 B. Statement will not cause error
 C. Statement will not cause error
 D. Statement will not cause error
 1Z0-071 PDF Dumps 1Z0-071 VCE Dumps 1Z0-071 Exam Questions
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 26 / 27
 
2019 Latest passapply 1Z0-071 PDF and VCE dumps Download
 To Read the Whole Q&As, please purchase the Complete Version from Our website.
 Try our product !
100% Guaranteed Success
 100% Money Back Guarantee
 365 Days Free Update
 Instant Download After Purchase
 24x7 Customer Support
 Average 99.9% Success Rate
 More than 800,000 Satisfied Customers Worldwide
 Multi-Platform capabilities - Windows, Mac, Android, iPhone, iPod, iPad, Kindle
 We provide exam PDF and VCE of Cisco, Microsoft, IBM, CompTIA, Oracle and other IT Certifications.
 You can view Vendor list of All Certification Exams offered:
 https://www.passapply.com/allproducts
 Need Help
Please provide as much detail as possible so we can best assist you.
 To update a previously submitted ticket:
 Any charges made through this site will appear as Global Simulators Limited.
 All trademarks are the property of their respective owners.
 Copyright © passapply, All Rights Reserved.
 Powered by TCPDF (www.tcpdf.org)
 1Z0-071 PDF Dumps | 1Z0-071 VCE Dumps | 1Z0-071 Exam Questions 27 / 27