Check the Free demo of our 1z0-071 Exam Dumps with 308 Questions
Clear your concepts with 1z0-071 Questions Before Attempting Real exam
NEW QUESTION # 79
Examine the data in the INVOICES table:
Examine the data in the CURRENCIES table:
CURRENCY_CODE
-------------
JPY
GPB
CAD
EUR
USD
Which query returns the currencies in CURRENCIES that are not present in INVOICES?
- A. SELECT * FROM currencies
WHERE NOT EXISTS (
SELECT NULL FROM invoices WHERE currency_ code = currency_ code); - B. SELECT currency_ code FROM currencies
INTERSECT
SELECT currency_ code FROM invoices; - C. SELECT currency_ code FROM currencies
MINUS
SELECT currency_ code FROM invoices; - D. SELECT * FROM currencies
MINUS
SELECT * FROM invoices;
Answer: C
NEW QUESTION # 80
Which three are true about privileges?
- A. Only users with the DBA role can create roles .
- B. A combination of object and system privileges can be granted to a role.
- C. Schema owners can grant object privileges on objects in their schema to any other user or role.
- D. Object privileges granted on a table automatically apply to all synonyms for that table.
- E. All types of schema objects have associated object privileges .
- F. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users.
Answer: B,C,E
NEW QUESTION # 81
Examine the command to create the BOOKS table.
The BOOK_IDvalue 101does not exist in the table.
Examine the SQL statement:
Which statement is true?
- A. It executes successfully only if the PUBLISHER_IDcolumn name is added to the columns list in the INSERTstatement.
- B. It executes successfully and the row is inserted with a rule PUBLISHER_ID.
- C. It executes successfully only if the PUBLISHER_IDcolumn name is added to the columns list and NULL is explicitly specified in the INSERTstatement.
- D. It executes successfully only if NULLis explicitly specified in the INSERTstatement.
Answer: B
NEW QUESTION # 82
Which two statements are true regarding the USING and ON clauses in table joins?
- A. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
- B. A maximum of one pair of columns can be joined between two tables using the ON clause.
- C. Both USING and ON clauses can be used for equijoins and nonequijoins.
- D. The ON clause can be used to join tables on columns that have different names but compatible data types.
Answer: A,D
NEW QUESTION # 83
Which two statements are true regarding constraints?
- A. A column with the UNIQUE constraint can contain null values.
- B. A constraint is enforced only for INSERT operation on the table.
- C. A constraint can be disabled even if the constraint column contains data.
- D. A foreign key column cannot contain null values.
- E. All constraints can be defined at the column level and at the table level.
Answer: A,C
NEW QUESTION # 84
View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)
- A. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;
- B. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;
- C. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iUSING (product_id) AND quantity_on_hand < 5;
- D. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
Answer: B,D
NEW QUESTION # 85
The SALES table has columns PROD_IDand QUANTITY_SOLDof data type NUMBER.
Which two queries execute successfully?
- A. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP
BY COUNT(*) >10; - B. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP
BY prod_id HAVING COUNT(*) >10; - C. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING
COUNT(*) >10; - D. SELECT COUNT (prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY
prod_id; - E. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;
Answer: C,D
NEW QUESTION # 86
Examine this query:
SELECT SUBSTR (SYSDATE,1,5) 'Result' FROM DUAL
Which statement is true?
- A. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)
- B. It executes successfully with an implicit data type conversion
- C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)
- D. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)
Answer: B
NEW QUESTION # 87
Examine the data in the COLORStable:
Examine the data in the BRICKStable:
Which two queries return all the rows from COLORS? (Choose two.)
- A.
- B.
- C.
- D.
- E.
Answer: A,E
NEW QUESTION # 88
Examine the structure of the PROGRAMS table:
Which two SQL statements would execute successfully?
- A. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM
programs; - B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
- C. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
- D. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs
Answer: C,D
NEW QUESTION # 89
In which three situations does a transaction complete?
- A. when a PL/SQL anonymous block is executed
- B. when a TRUNCATE statement is executed after the pending transaction
- C. when a ROLLBACK command is executed
- D. when a DELETE statement is executed
- E. when a data definition language (DDL) statement is executed
Answer: B,C,E
Explanation:
Explanation
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
NEW QUESTION # 90
Table ORDER_ITEMS contains columns ORDER_TO, UNIT_PRICE and QUANTITY, of data type NUMBER.
Examine these SQL statements:
Statement 1:
Statement 2:
Which two statements are true? (Choose two.)
- A. Statement 2 returns only one row of output.
- B. Statement 1 returns only one row of output.
- C. Both statements will return NULLif either UNIT_PRICEor QUANTITYcontains NULL.
- D. Both the statements give the same output.
- E. Statement 2 may return multiple rows of output.
Answer: B,E
Explanation:
Explanation/Reference: https://stackoverflow.com/questions/12366390/how-to-select-product-that-have-the-maximum- price-of-each-category
NEW QUESTION # 91
Examine the structure of the employees table.
There is a parent/child relationship betweenEMPLOYEE_IDandMANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager asthe employee whoseEMPLOYEE_ID123.
Which query provides the correct output? A)
B)
C)
D)
- A. Option A
- B. Option D
- C. Option C
- D. Option B
Answer: D
NEW QUESTION # 92
Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? (Choose three.)
- A. Insert statements can always be done on a table through a view.
- B. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.
- C. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
- D. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCTkeyword.
- E. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEYand the PRIMARY KEYcolumns are not referenced in the defining query of the view.
- F. The WITH CHECKclause has no effect when deleting rows from the underlying table through the view.
Answer: B,C,E
NEW QUESTION # 93
Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)
- A. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
- B. DELETE <sequencename> would remove a sequence from the database.
- C. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.
- D. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
- E. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
Answer: A,D
Explanation:
Explanation
References:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm
NEW QUESTION # 94
Examine this description of the EMP table:
You execute this query:
SELECT deptno AS "departments", SUM (sal) AS "salary"
FROM emp
GROUP | BY 1
HAVING SUM (sal)> 3 000;
What is the result?
- A. only departments where the total salary is greater than 3000, ordered by department
- B. all departments and a sum of the salaries of employees with a salary greater than 3000
- C. an error
- D. only departments where the total salary is greater than 3000, returned in no particular order
Answer: C
NEW QUESTION # 95
Examine this description of the PRODUCTS table:
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS?
- A. CREATE INDEX price_idx ON products (price);
- B. ALTER TABLE products DROP COLUMN expiry_date;
- C. ALTER TABLE products DROP UNUSED COLUMNS;
- D. DROP TABLE products;
- E. ALTER TABLE products SET UNUSED (expiry_date);
- F. TRUNCATE TABLE products;
Answer: A,D,F
NEW QUESTION # 96
Which two statements are true regarding the SQL GROUP BY clause?
- A. The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
- B. You can use a column alias in the GROUP BY clause.
- C. 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.
- D. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
- E. Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
Answer: C,D
NEW QUESTION # 97
Which statement is true about TRUNCATE and DELETE?
- A. For large tables TRUNCATE is faster than DELETE.
- B. You can never tows from a table if foreign key constraints will be violated.
- C. You can never TRUNCATE a table if foreign key constraints will be violated.
- D. For tables with multiple indexes and triggers is faster than TRUNCATE.
Answer: A
NEW QUESTION # 98
Which two statements are true regarding savepoints? (Choose two.)
- A. Savepoints may be used to ROLLBACK.
- B. Savepoints are effective only for COMMIT.
- C. Savepoints are effective for both COMMITand ROLLBACK.
- D. Savepoints can be used for both DML and DDL statements.
- E. Savepoints can be used for only DML statements.
Answer: A,E
Explanation:
Explanation/Reference:
Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/
statements_10001.htm#SQLRF01701
NEW QUESTION # 99
Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario? (Choose two.)
- A. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
- B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.
- C. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
- D. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
- E. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
Answer: B,C
Explanation:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html
NEW QUESTION # 100
View the Exhibit and examine the details of the ORDER_ITEMS table.
Evaluate the following SQL statements:
Statement 1:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items;
Statement 2:
SELECT MAX(unit_price*quantity) "Maximum Order"
FROM order_items
GROUP BY order_id;
Which statements are true regarding the output of these SQL statements? (Choose all that apply.)
- A. Statement 1 would not return give the same output.
- B. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.
- C. Statement 2 would return multiple rows of output.
- D. Both the statements would give the same output.
- E. Statement 1 would return only one row of output.
Answer: B,C,E
NEW QUESTION # 101
Which three queries execute successfully?
- A. SELECT SYSDATE - 1 - DATE'2019-01-01' EROM DUAL;
- B. SELECT (SYSDATE-DATE '2019-01-01') / 1 FROM DUAL;
- C. SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL
- D. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
- E. SELECT 1 - SYSDATE- DATE '2019-01-01' FROM DUAL;
- F. SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;
Answer: A,B,D
NEW QUESTION # 102
......
Get professional help from our 1z0-071 Dumps PDF: https://pass4sure.prep4cram.com/1z0-071-exam-cram.html
