Reliable Oracle PL/SQL Developer Certified Associate 1z0-071 Dumps PDF Jan 16, 2022 Recently Updated Questions
Pass Your Oracle 1z0-071 Exam with Correct 305 Questions and Answers
NEW QUESTION 127
Examine the data in the CUSTOMERStable:
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. FULL OUTER JOIN
- B. JOIN
- C. RIGHT OUTER JOIN
- D. NATURAL JOIN
- E. LEFT OUTER JOIN
Answer: B,C
NEW QUESTION 128
View the Exhibit and examine the description for the PRODUCTS and SALES table.
PROD_IDis a primary key in the PRODUCTStable and foreign key in the SALEStable with ON DELETE CASCADEoption. The SALEStable contains data for the last three years. You want to remove all the rows from the PRODUCTStable for which no sale was done for the last three years.
Which is the valid DELETEstatement?
- A. DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE SYSDATE - 3*365 >= time_id); - B. DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE - 3*365 ); - C. DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id - 3*365 = SYSDATE ); - D. DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE SYSDATE >= time_id - 3*365 );
Answer: A
NEW QUESTION 129
View the exhibit for the structure of the STUDENTand FACULTYtables.
STUDENT
Name Null? Type
------------------ ------------------- -------------
STUDENT_ID NOT NULL NUMBER(2)
STUDENT_NAME VARCHAR2(20)
FACULTY_ID VARCHAR2(2)
LOCATION_ID NUMBER(2)
FACULTY
Name Null? Type
- ----------------- ------------------- -------------
FACULTY_ID NOT NULL NUMBER(2)
FACULTY_NAME VARCHAR2(20)
LOCATION_ID NUMBER(2)
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. Both statements 1 and 2 execute successfully and give different results.
- B. Only statement 1 executes successfully and gives the required result.
- C. Only statement 2 executes successfully and gives the required result.
- D. Both statements 1 and 2 execute successfully and give the same required result.
Answer: B
NEW QUESTION 130
You issue the following command to drop the PRODUCTStable:
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 views and synonyms on the table remain but they are invalidated.
- D. All indexes on the table remain but they are invalidated.
- E. All data in the table is deleted but the table structure remains.
Answer: A,B,C
NEW QUESTION 131
Which statement is true regarding the UNION operator?
- A. Names of all columns must be identical across all select statements.
- B. The number of columns selected in all select statements need not be the same.
- C. Null values are not ignored during duplicate checking.
- D. By default, the output is not sorted.
Answer: C
NEW QUESTION 132
You must create a SALES table with these column specifications and data types:
SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified
Which statement would create the table? (Choose the best answer.)
- A. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = "CASH");
- B. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT 'SYSDATE',payment VARCHAR2(30) DEFAULT CASH);
- C. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT 'CASH');
- D. CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = "CASH");
Answer: C
NEW QUESTION 133
Examine the types and examples of relationship that follows: (Choose the best answer.)
1 One-to-one a) teacher to Student
2 One-to-many b) Employees to Manager
3 Many-to-one c) Person to SSN
4 Many-to-many d) Customers to Products
Which option indicates correctly matched relationships?
- A. 1-a, 2-b, 3-c, and 4-d
- B. 1-c, 2-a, 3-b, and 4-d
- C. 1-d, 2-b, 3-a, and 4-c
- D. 1-c, 2-d, 3-a, and 4-b
Answer: A
NEW QUESTION 134
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 AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
- C. SELECT member_id 'MEMBER ID', due_date '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: D
NEW QUESTION 135
Which two are true about rollbacks?
- A. The ROLLBACK statement does not release locks resulting from table updates.
- B. Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.
- C. Data consistency is not guaranteed after a rollback.
- D. A transaction interrupted by a system failure is automatically rolled back.
- E. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .
Answer: D,E
NEW QUESTION 136
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 table prefix is missing for the column names in the SELECTclause.
- B. The EMPLOYEESand DEPARTMENTStables have more than one column with the same column name and data type.
- C. The NATURAL JOINclause is missing the USINGclause.
- D. The DEPARTMENTStable is not used before the EMPLOYEEStable in the FROMclause.
Answer: B
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 137
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
- A. The outer query stops evaluating the result set of the inner query when the first value is found.
- B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
- C. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
- D. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
Answer: A,D
NEW QUESTION 138
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 table prefix is missing for the column names in the SELECTclause.
- B. The EMPLOYEESand DEPARTMENTStables have more than one column with the same column name and data type.
- C. The NATURAL JOINclause is missing the USINGclause.
- D. The DEPARTMENTStable is not used before the EMPLOYEEStable in the FROMclause.
Answer: B
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 139
Which two statements are true regarding constraints? (Choose two.)
- A. You can have more than one column in a table as part of a primary key.
- B. A constraint is enforced only for an INSERT operation on a table.
- C. A column with the UNIQUE constraint can store NULLS.
- D. A foreign key cannot contain NULL values.
Answer: A,C
NEW QUESTION 140
Which two statements are true about Oracle synonyms?
- A. A synonym can have a synonym.
- B. A synonym has an object number.
- C. All private synonym names must be unique in the database.
- D. A synonym can be created on an object in a package.
- E. Any user can create a PUBLIC synonym.
Answer: A,B
NEW QUESTION 141
Which two statements are true about the results of using the intersect operator in compound queries?
- A. Column names in each select in the compound query can be different.
- B. The number of columns in each select in the compound query can be different.
- C. intersect ignores nulls.
- D. intersect returns rows common to both sides of the compound query.
- E. Reversing the order of the intersected tables can sometimes affect the output.
Answer: A,D
NEW QUESTION 142
Examine the structure of the MEMBERStable.
Name Null? Type
- --------------- ----------------- ---------------------------
MEMBER_ID NOT NULL VARCHAR2 (6)
FIRST_NAME VARCHAR2 (50)
LAST_NAME NOT NULL VARCHAR2 (50)
ADDRESS VARCHAR2 (50)
CITY VARCHAR2 (25)
STATE NOT NULL VARCHAR2 (3)
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 LIKE 'M%';
- B. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
- 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';
Answer: C
NEW QUESTION 143
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table and the foreign key of the ORDER_ITEMS table, whose constraint is defined with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?
- A. DELETE order_idFROM ordersWHERE order_total < 1000;
- B. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
- C. DELETE ordersWHERE order_total < 1000;
- D. DELETE orders o, order_items iWHERE o.order_id = i.order_id;
Answer: C
NEW QUESTION 144
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: D
NEW QUESTION 145
View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update EMPLOYEES table as follows:
* Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
* Set department_id for these employees to the department_id corresponding to London (location_id
* 2100).
* Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
* Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
What is outcome?
- A. It executes successfully and gives the desired update.
- B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
- C. It executes successfully but does not give the desired update.
- D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
Answer: C
NEW QUESTION 146
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.
You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees:
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 EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
- B. The NATURAL JOIN clause is missing the USING clause.
- C. The table prefix is missing for the column names in the SELECT clause.
- D. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
Answer: A
Explanation:
Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)
NEW QUESTION 147
Which three statements are true about Oracle synonyms?
- A. A SEQUENCE can have a synonym.
- B. A synonym cannot be created for a PL /SQL package.
- C. A synonym created by one user can refer to an object belonging to another user.
- D. A synonym can be available to all users .
- E. Any user can drop a PUBLIC synonym.
Answer: B,C,D
NEW QUESTION 148
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
-------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC".
Which query gives the required output?
- A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
- B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
- C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
- D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
Answer: B
NEW QUESTION 149
Which three privileges can be restricted to a subset of columns in a table?
- A. REFERENCES
- B. INSERT
- C. INDEX
- D. SELECT
- E. UPDATE
- F. ALTER
- G. DELETE
Answer: A,B,E
NEW QUESTION 150
View the exhibit and examine the structure of the EMPLOYEES table.
You want to select all employees having 100 as their MANAGER_ID manages and their manager.
You want the output in two columns: the first column should have the employee's manager's LAST_NAME and the second column should have the employee's LAST_NAME.
Which SQL statement would you execute?
- A. 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;
- B. 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;
- C. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id AND 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 e.manager_id=100;
Answer: D
NEW QUESTION 151
......
Latest 2022 Realistic Verified 1z0-071 Dumps: https://www.fast2test.com/1z0-071-premium-file.html
Pass 1z0-071 Exam Updated 305 Questions: https://drive.google.com/open?id=1SzU7pl11Fvj2wnc3QgJZe9GNbj7rd-0W