Excellent 1z1-071 Updated 2021 Dumps With 100% Exam Passing Guarantee
Best way to practice test for Oracle 1z1-071
NEW QUESTION 114
Evaluate the following SELECTstatement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS';
Which two statements are true about the output? (Choose two.)
- A. The column DELETE_RULEdecides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
- B. The STATUScolumn indicates whether the table is currently in use.
- C. The R_CONSTRAINT_NAMEcolumn gives the alternative name for the constraint.
- D. In the second column, 'c'indicates a check constraint.
Answer: A,D
NEW QUESTION 115
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result?
- A. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- B. The values in the all columns would be returned in descending order.
- C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- D. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
- E. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
Answer: D,E
NEW QUESTION 116
Which two statements are true about Data Manipulation Language (DML) statements?
- A. An UPDATE... SET...statement can modify multiple rows based on multiple conditions on a table.
- B. A DELETE FROM.....statement can remove rows based on only a single condition on a table.
- C. A DELETE FROM.....statement can remove multiple rows based on multiple conditions on a table.
- D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
- E. An INSERTINTO...VALUES..statement can add multiple rows per execution to a table.
- F. An UPDATE....SET....statement can modify multiple rows based on only a single condition on a table.
Answer: A,C
Explanation:
Explanation/Reference:
References:
http://www.techonthenet.com/sql/and_or.php
NEW QUESTION 117
Which two statements are true regarding roles? (Choose two.)
- A. Roles are named groups of related privileges that can be granted to users or other roles.
- B. A role can be granted to PUBLIC.
- C. The REVOKEcommand can be used to remove privileges but not roles from other users.
- D. A role can be granted to itself.
- E. A user can be granted only one role at any point of time.
Answer: A,B
Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28
NEW QUESTION 118
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> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_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> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= 'INTERNET';
Answer: C
NEW QUESTION 119
View the exhibit and examine the description of the PRODUCT_INFORMATION table.
Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?
- A. SELECT COUNT (NVL(list_price, 0))FROM product_informationWHERE list_price is NULL
- B. SELECT COUNT (DISTINCT list_price)FROM product_informationWHERE list_price is NULL
- C. SELECT COUNT (list_price)FROM product_informationWHERE list_price is NULL
- D. SELECT COUNT (list_price)FROM product_informationWHERE list_price i= NULL
Answer: A
NEW QUESTION 120
Examine the types and examples of relationship that follow:
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-c, 2-a, 3-b, and 4-d
- B. 1-d, 2-b, 3-a, and 4-c
- C. 1-a, 2-b, 3-c, and 4-d
- D. 1-c, 2-d, 3-a, and 4-b
Answer: C
NEW QUESTION 121
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
- A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
- B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
Answer: D
NEW QUESTION 122
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)
- A. ORDER BY "Last name"
- B. ORDER BY 2, cust_id
- C. ORDER BY 2,1
- D. ORDER BY "CUST_NO"
- E. ORDER BY CUST_NO
Answer: A,B,C
Explanation:
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
NEW QUESTION 123
Examine the description of the ORDERStable:
Examine the description of the INVOICEStable:
Examine this query:
Which three rows will it return? (Choose three.)
5 01-MAR-2019
- A. 4 01-FEB-2019
- B.
- C. 3 <null>
- D. 2 <null>
- E. 3 01-JAN-2019
- F. 5 <null>
- G. 1 <null>
Answer: B,C,F
NEW QUESTION 124
Examine the command to create the BOOKS table.
SQL> create table books(book id CHAR(6) PRIMARY KEY,
title VARCHAR2(100) NOT NULL,
publisher_id VARCHAR2(4),
author_id VARCHAR2 (50));
The BOOK ID value 101 does not exist in the table.
Examine the SQL statement.
insert into books (book id title, author_id values
('101','LEARNING SQL','Tim Jones')
- A. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.
- B. It executes successfully only if NULL is explicitly specified in the INSERT statement.
- C. It executes successfully and the row is inserted with a null PLBLISHER_ID.
- D. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.
Answer: C
NEW QUESTION 125
Which two statements are true regarding a SAVEPOINT? (Choose two.)
- A. Rolling back to a SAVEPOINT can undo a TRUNCATE statement
- B. A SAVEPOINT does not issue a COMMIT
- C. Only one SAVEPOINT may be issued in a transaction
- D. Rolling back to a SAVEPOINT can undo a DELETE statement
- E. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
Answer: B,D
Explanation:
Explanation/Reference:
NEW QUESTION 126
Examine the structure of the EMPLOYEES table. (Choose two.)
You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output?
- A. SELECT MIN(Salary), MAX(salary)
FROM (SELECT salary FROM
employees
WHERE hire_date < SYSDATE-365); - B. SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary); - C. SELECT MIN(Salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary); - D. SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365)
GROUP BY maxsal, minsal;
Answer: A,D
NEW QUESTION 127
You issued this command:
Which three statements are true?
- A. All constraints defined on HR.EMPLOYEES are dropped.
- B. Synonyms for HR.EMPLOYEES are dropped.
- C. The HR.EMPLOYEES table may be moved to the recycle bin.
- D. All indexes defined on HR.EMPLOYEES are dropped.
- E. Sequences used to populate columns in the HR.EMPLOYEES table are dropped.
- F. Views referencing HR.EMPLOYEES are dropped.
Answer: A,D,F
NEW QUESTION 128
Which three statements are true about Oracle synonyms?
- A. A synonym cannot be created for a PL /SQL package.
- B. Any user can drop a PUBLIC synonym.
- C. A SEQUENCE can have a synonym.
- D. A synonym created by one user can refer to an object belonging to another user.
- E. A synonym can be available to all users .
Answer: A,D,E
NEW QUESTION 129
Which two statements will return the names of the three employees with the lowest salaries?
- A. SELECT last_ name, salary
FBOM employees
WEERE
ORDER BY SELECT
ROINUM <= 3
salary FROM
employees); - B. SELECT last name, salary
FROM employees
ORDER BY salary
FETCE FIRST 3 RONS ONLY; - C. SELECT last_ name, salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary; - D. SELECT last_ name, salary
FROM
(SELECT" FROM employees ORDER BY salary)
WHERE ROWNUM <=3 - E. SELECT last_ name, salary
FROM employees
WHERE ROWNUM <=3
ORDER BY salary
Answer: B,D
NEW QUESTION 130
View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)
The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.
The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?
- A. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p FULL OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id); - B. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p JOIN proj_task_details d
ON (p.task_id = d.task_id); - C. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p LEFT OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id); - D. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p JOIN proj_task_details d
ON (p.based_on = d.task_id);
Answer: C
NEW QUESTION 131
Which three are true about multitable INSERTstatements? (Choose three.)
- A. They can be performed on views.
- B. They can insert each computed row into more than one table.
- C. They can be performed on relational tables.
- D. They can be performed on external tables using SQL* Loader.
- E. They can be performed only by using a subquery.
- F. They can be performed on remote tables.
Answer: B,C,D
NEW QUESTION 132
Which two statements are true about an Oracle database? (Choose two.)
- A. A VARCHAR2column without data has a NULLvalue.
- B. A NUMBERcolumn without data has a zero value.
- C. A table can have multiple primary keys.
- D. A column definition can specify multiple data types.
- E. A table can have multiple foreign keys.
Answer: B,E
NEW QUESTION 133
Which three statements are true about the ALTER TABLE....DROP COLUMN....
command?
- A. A dropped column can be rolled back.
- B. A column can be dropped only if it does not contain any data.
- C. A column can be dropped only if another column exists in the table.
- D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
- E. A parent key column in the table cannot be dropped.
Answer: C,D,E
NEW QUESTION 134
Examine the structure of the SALES table.
Examine this statement:
Which two statements are true about the SALES1 table? (Choose two.)
- A. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- B. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- C. It will not be created because of the invalid WHERE clause.
- D. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- E. It is created with no rows.
Answer: A,E
NEW QUESTION 135
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
INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = 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
SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE 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 136
......
Oracle Database SQL Certification Sample Questions and Practice Exam: https://www.preppdf.com/Oracle/1z1-071-prepaway-exam-dumps.html
Real Exam Questions & Answers - Oracle 1z1-071 Dump is Ready: https://drive.google.com/open?id=1sPU4j4DA-YdI4CfUQG3nmbOy72NJ9pdQ