Free 1z0-071 Exam Braindumps certification guide Q&A
1z0-071 Certification Overview Latest 1z0-071 PDF Dumps
NEW QUESTION # 105
Which three statements are true about defining relations between tables in a relational database?
- A. Primary key columns allow null values.
- B. Every foreign key value must refer to a matching primary or unique key value.
- C. Foreign key columns allow null values.
- D. Every primary or unique key value must refer to a matching foreign key value.
- E. Unique key columns allow null values.
Answer: B,C
NEW QUESTION # 106
View the exhibit and examine the description of the DEPARTMENTSand EMPLOYEEStables.
The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?
- A. The EMPLOYEESand DEPARTMENTStables have more than one column with the same column name and data type.
- B. The NATURAL JOINclause is missing the USINGclause.
- C. The table prefix is missing for the column names in the SELECTclause.
- D. The DEPARTMENTStable is not used before the EMPLOYEEStable in the FROMclause.
Answer: A
Explanation:
Explanation/Reference:
Explanation:
Natural join needs only one column to be the same in each table. The EMPLOYEESand DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)
NEW QUESTION # 107
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries? (Choose two.)
- A. displaying all supplier IDs whose average list price is more than 500
- B. displaying the number of products whose list prices are more than the average list price
- C. displaying the minimum list price for each product status
- D. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
- E. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
Answer: B,E
NEW QUESTION # 108
Which two statements are true about single-row functions? (Choose two.)
- A. CONCAT: can be used to combine any number of values
- B. FLOOR: returns the smallest integer greater than or equal to a specified number
- C. CEIL: can be used for positive and negative numbers
- D. TRUNC: can be used with NUMBER and DATE values
- E. MOD: returns the quotient of a division operation
Answer: D,E
Explanation:
Explanation/Reference: https://www.folkstalk.com/2012/01/oracle-single-row-functions-examples.html
NEW QUESTION # 109
Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
- A. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
- B. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
- C. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
- D. IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
- E. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
Answer: B,C
NEW QUESTION # 110
Evaluate the following two queries:
Which statement is true regarding the above two queries?
- A. Performance would improve in query 2.
- B. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
- C. Performance would degrade in query 2.
- D. There would be no change in performance.
Answer: D
Explanation:
Explanation
NEW QUESTION # 111
Which two are true about the MERGE statement?
- A. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.
- B. The WHEN NOT MATCHED clause can be used to specify the updates to be performed.
- C. The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.
- D. The WHEN MATCHED clause can be used to specify the inserts to be performed.
- E. The WHEN MATCHED clause can be used to specify the updates to be performed.
Answer: A,E
NEW QUESTION # 112
Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
- A. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
- B. The HAVING clause can be used with aggregate functions in subqueries.
- C. The WHERE clause can be used to exclude rows before dividing them into groups.
- D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- E. The WHERE clause can be used to exclude rows after dividing them into groups.
Answer: B,C
NEW QUESTION # 113
Which two tasks can be performed by using Oracle SQL statements?
- A. changing the password for an existing database user
- B. querying data from tables across databases
- C. starting up a database instance
- D. executing operating system (OS) commands in a session
- E. connecting to a database instance
Answer: A,B
Explanation:
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm
NEW QUESTION # 114
You must find the number of employees whose salary is lower than employee 110.
Which statement fails to do this?
- A. SELECT COUNT (* )
FROM employees
WHERE salary < (SELECT salary FROM employees WHERE employee.id =
110) ; - B. SELECT COUNT (*)
FROM employees e
JOIN (SELECT salary FROM employees WHERE employee_ id= 110) a
ON e. salary< a. salary; - C. SELECT COUNT (*)
FROM employees
JOIN employees a
ON e. salary< a. salary
WHERE a. employee_ id= 110; - D. SELECT COUNT (* )
FROM employees e
WHERE e. salary < (SELECT a. salary FROM employees a WHERE e. employee_ id = 110);
Answer: D
NEW QUESTION # 115
You need to calculate the number of days from 1st January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;
- A. SELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;
- B. SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;
- C. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;
- D. SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;
- E.
Answer: C,D
NEW QUESTION # 116
Which statement is true about TRUNCATEand DELETE?
- A. For large tables TRUNCATEis faster than DELETE.
- B. You can never TRUNCATEa table if foreign key constraints will be violated.
- C. You can never DELETE rows from a table if foreign key constraints will be violated.
- D. For tables with multiple indexes and triggers DELETEis faster than TRUNCATE.
Answer: A
Explanation:
Explanation/Reference: https://www.sqlservercentral.com/articles/difference-between-truncate-and-delete
NEW QUESTION # 117
View the Exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
- A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100; - B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.managerjd=100; - C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.managerjd=100; - D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id=100;
Answer: C
NEW QUESTION # 118
The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
- A. It is not possible to recover the table structure, data, or the related indexes.
- B. It recovers the table structure and data but not the related indexes.
- C. It recovers the table structure, data, and the indexes.
- D. It recovers only the table structure.
Answer: A
Explanation:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm
NEW QUESTION # 119
View the exhibit and examine the structure of the EMPLOYEES table.
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
- A. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id and AND e.manager_id = 100
- B. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON e.employee_id = m.manager_idWHERE m.manager_id = 100;
- C. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE e.manager_id = 100;
- D. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE m.manager_id = 100;
Answer: C
NEW QUESTION # 120
Examine the description of the EMP_DETAILS table given below:
Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?
- A. You cannot add a new column to the table with LONG as the data type.
- B. An EMP_IMAGE column can be included in the GROUP BY clause.
- C. An EMP_IMAGE column cannot be included in the ORDER BY clause.
- D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
Answer: A,C
Explanation:
Explanation
NEW QUESTION # 121
Which two tasks can be performed by using Oracle SQL statements?
- A. changing the password for an existing database user
- B. querying data from tables across databases
- C. starting up a database instance
- D. executing operating system (OS) commands in a session
- E. connecting to a database instance
Answer: A,B
Explanation:
Explanation/Reference:
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm
NEW QUESTION # 122
Examine the data in the PRODUCTStable:
Examine these queries:
Which queries generate the same output?
- A. 1 and 3
- B. 1 and 2
- C. 1, 2, and 3
- D. 2 and 3
Answer: A
Explanation:
Explanation/Reference: https://www.dofactory.com/sql/where-any-all (statement 2 syntax in wrong)
NEW QUESTION # 123
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 * FROM currencies
MINUS
SELECT * FROM invoices; - C. SELECT currency_ code FROM currencies
MINUS
SELECT currency_ code FROM invoices; - D. SELECT currency_ code FROM currencies
INTERSECT
SELECT currency_ code FROM invoices;
Answer: C
NEW QUESTION # 124
Which two statements are true about the rules of precedence for operators? (Choose two.)
- A. NULLS influence the precedence of operators in an expression
- B. The concatenation operator | | is always evaluated before addition and subtraction in an expression
- C. Arithmetic operators with equal precedence area evaluated from left to right within an expression
- D. Multiple parentheses can be used to override the default precedence of operators in an expression
- E. The + binary operator has the highest precedence in an expression in a SQL statement
Answer: C,D
NEW QUESTION # 125
Which two statements are true about Data Manipulation Language (DML) statements?
- A. AH INSERT INTO. . .VALUES. . statement can add multiple rows per execution to a table.
- B. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
- C. An UPDATE...SET... statement can modify multiple rows based on multiple conditions on a table.
- D. A DELETE FROM ..... statement can remove rows based on only a single condition on a table.
- E. An INSERT INTO...VALUES..... statement can add a single row 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.
Answer: A,D
NEW QUESTION # 126
Examine the command:
What does ON DELETE CASCADE imply?
- A. When a value in the BOOKS.BOOK_IDcolumn is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_IDcolumn.
- B. When a row in the BOOKStable is deleted, the rows in the BOOK_TRANSACTIONStable whose BOOK_ID matches that of the deleted row in the BOOKStable are also deleted.
- C. When the BOOKStable is dropped, the BOOK_TRANSACTIONStable is dropped.
- D. When the BOOKStable is dropped, all the rows in the BOOK_TRANSACTIONStable are deleted but the table structure is retained.
Answer: B
Explanation:
Explanation
NEW QUESTION # 127
Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?
- A. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
- B. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
- C. The CURRENT_TIMESTAMP function returns data without time zone information
- D. A TIMESTAMP data type column contains information about year, month, and day
- E. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
Answer: A,B,E
NEW QUESTION # 128
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 D
- B. Option C
- C. Option A
- D. Option B
Answer: D
NEW QUESTION # 129
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS table contains the current location of all currently active customers.
The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?
- A. MINUS
- B. UNION ALL
- C. UNION
- D. INTERSECT
Answer: A
NEW QUESTION # 130
......
Oracle 1z1-071 (Oracle Database SQL) Certification Exam is a multiple-choice exam that consists of 73 questions. Candidates are given 100 minutes to complete the exam and must score at least 63% to pass. 1z0-071 exam is available in English and Japanese and can be taken at authorized testing centers worldwide. 1z0-071 exam fee varies depending on the country and region.
The Best Oracle 1z0-071 Study Guides and Dumps of 2023: https://www.braindumpsit.com/1z0-071_real-exam.html
Top Oracle 1z0-071 Exam Audio Study Guide! Practice Questions Edition: https://drive.google.com/open?id=1vw1L6drU0ABZwwJGwehYLI3MMo7uVrEA