PDF (New 2025) Actual Oracle 1z1-071 Exam Questions
Dumps Moneyack Guarantee - 1z1-071 Dumps UpTo 90% Off
NEW QUESTION # 138
You issued this command:
Which three statements are true? (Choose three.)
- A. Sequences used to populate columns in the HR.EMPLOYEEStable are dropped.
- B. Views referencing HR.EMPLOYEESare dropped.
- C. All constraints defined on HR.EMPLOYEESare dropped.
- D. Synonyms for HR.EMPLOYEESare dropped.
- E. The HR.EMPLOYEEStable may be moved to the recycle bin.
- F. All indexes defined on HR.EMPLOYEESare dropped.
Answer: B,C,F
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables010.htm#ADMIN01505
NEW QUESTION # 139
View the Exhibit and examine the structure of ORDERSand ORDER_ITEMStables.
ORDER_IDis the primary key in the ORDERStable. It is also the foreign key in the ORDER_ITEMStable wherein it is created with the ON DELETE CASCADEoption.
Which DELETEstatement would execute successfully?
DELETE orders o, order_items i
- A. FROM orders
WHERE order_total < 1000; - B. WHERE order_total < 1000;
DELETE order_id - C. FROM orders
WHERE (SELECT order_id
FROM order_items);
DELETE orders - D. WHERE o.order_id = i.order_id;
DELETE
Answer: B
NEW QUESTION # 140
Examine this partial command:
CREATE TABLE cust(
cust_id NUMBER(2),
credit_limit NUMBER(10)
ORGANIZATION EXTERNAL
Which two clauses are required for this command to execute successfully?
- A. the ACCESS PARAMETERS clause
- B. the REJECT LIMIT clause
- C. the DEFAULT DIRECTORY clause
- D. the access driver TYPE clause
- E. the LOCATION clause
Answer: A,E
Explanation:
When creating an external table, which allows you to access data in a flat file as though it is a table inside the database, certain clauses are required:
A . the ACCESS PARAMETERS clause: This clause specifies the parameters required by the access driver to read the data files.
D . the LOCATION clause: This clause specifies the location of the data files that make up the external table.
Reference:
Oracle Database SQL Language Reference 12c, particularly the sections detailing the creation and management of external tables.
NEW QUESTION # 141
You execute these commands successfully:
CREATE GLOBAL TEMPORARY TABLE invoices _ gtt
( customer id INTEGER,
invoice_ total NUMBER (10, 2)
) ON COMMIT PRESERVE ROWS;
INSERT INTO invoices_ gtt VALUES (1, 100);
COMMIT;
Which two are true?
- A. Other sessions can view the committed row.
- B. You can add a column to the table in this session.
- C. You can add a foreign key to the table.
- D. When you terminate your session, the row will be deleted.
- E. To drop the table in this session, you must first truncate it.
Answer: D,E
NEW QUESTION # 142
Which three actions can you perform on an existing table containing data? (Choose three.)
- A. Add a new NOT NULL column with a DEFAULT value
- B. Define a default value that is automatically inserted into a column containing nulls
- C. Change a DATE column containing data to a NUMBER data type
- D. Add a new column as the table's first column
- E. Change the default value of a column
- F. Increase the width of a numeric column
Answer: D,E
NEW QUESTION # 143
Examine the structure of the ORDERS table:
You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders
GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result? (Choose the best answer.)
- A. It executes successfully and gives the correct output.
- B. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
- C. It executes successfully but does not give the correct output.
- D. It returns an error because the TO_CHAR function is not valid.
Answer: B
NEW QUESTION # 144
Examine this data in the EMPLOYERS table:
Which statement will execute successfully?
- A. SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id
- B. SELECT dept_id, INSTR (last_name,'A'), SUM (salary) FROM employees GROUP BY dept_id
- C. SELECT dept_id, MAX (Last_name), SUM (salary) FROM employees GROUP BY dept_id
- D. SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id
Answer: C
Explanation:
In SQL, the GROUP BY clause is used in conjunction with aggregate functions to group the result-set by one or more columns.
A . This statement will execute successfully. MAX() is an aggregate function that can be used to return the highest value of the selected column, and SUM() is an aggregate function used to sum the values. Both are valid in the SELECT statement with a GROUP BY clause grouping the results by dept_id.
B . This statement will not execute successfully when used with GROUP BY because LENGTH(last_name) is not an aggregate function and it doesn't appear in the GROUP BY clause. All selected columns that are not under an aggregate function must be included in the GROUP BY clause.
C . This statement will not execute successfully because STDDEV() is an aggregate function that calculates the standard deviation of a set of numbers, and it can only be used on numeric data types. last_name is not a numeric column.
D . This statement will not execute successfully for the same reason as B: INSTR(last_name, 'A') is not an aggregate function and must appear in the GROUP BY clause if it's in the SELECT clause.
Reference:
Oracle Documentation on GROUP BY: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702 Oracle Documentation on Aggregate Functions: https://docs.oracle.com/database/121/SQLRF/functions004.htm#SQLRF51148
NEW QUESTION # 145
In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default date format is DD-MON-RR Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?
- A. PROMO_ BEGIN_DATE - 5 will return a date
- B. TONUMBER (PROMO BEGIN_DATE) - 5 will return a number
- C. TODATE(PROMO BEGIN_DATE *5) will return a date
- D. PROMO_ BEGIN_DATE - SYSDATE will return an error
- E. PROMO_ BEGIN_DATE - SYSDATE will return a number
Answer: A,E
NEW QUESTION # 146
Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER
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 two statements are true?
- A. Both statements will return NULL if either UNIT PRICE or QUANTITY contains NULL,
- B. Statement 2 may return multiple rows of output.
- C. Statement 2 returns only one row of output.
- D. Statement 1 returns only one row of output.
- E. Both the statement given the same output.
Answer: B,D
NEW QUESTION # 147
Which two statements will return the names of the three employees with the lowest salaries?
- A. SELECT last_name,salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary; - B. SELECT last_name,salary
FROM employees
ORDER BY salary
FETCH FIRST 3 ROWS ONLY; - C. SELECT last_name,salary
FROM employees
WHERE ROWNUM<=3
ORDER BY (SELECT salary FROM employees); - D. SELECT last_name, salary
FROM employees
WHERE ROWNUM<=3 - E. SELECT last_name,salary
FROM (SELECT * FROM employees ORDER BY salary)
Answer: B
Explanation:
To retrieve the names of the three employees with the lowest salaries, the correct SQL syntax and logic are crucial:
Option B:
SELECT last_name, salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY; This query correctly sorts employees by their salary in ascending order and fetches the first three rows only. The FETCH FIRST n ROWS ONLY syntax is a standard way to limit the result set in SQL.
Options A, C, D, and E do not correctly implement the logic for fetching the lowest three salaries due to misuse of ROWNUM or incorrect placement of ORDER BY and FETCH clauses.
NEW QUESTION # 148
Examine this statement:
What is returned upon execution?
- A. an error
- B. 0 rows
- C. 1 row
- D. 2 rows
Answer: D
NEW QUESTION # 149
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
Exhibit
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 same column has been used twice with the AND logical operator.
- B. It would execute but would return no rows.
- C. It would not execute because the entire WHERE clause is not enclosed within parentheses.
- D. It would execute and return the desired result.
Answer: B
NEW QUESTION # 150
Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?
- A. The first column in the last SELECT of the compound query
- B. The first VARCHAR2 column in the first SELECT of the compound query
- C. The first NUMBER or VARCHAR2 column in the last SELECTof the compound query
- D. The first NUMBER column in the first SELECT of the compound query
- E. The first column in the first SELECT of the compound query
Answer: E
NEW QUESTION # 151
Examine this SQL statement:
Which two are true?
- A. All existing rows in the ORDERS table are updated
- B. The UPDATE statement executes successfully even if the subquery selects multiple rows
- C. The subquery is executed before the UPDATE statement is executed
- D. The subquery is executed for every updated row in the ORDERS table
- E. The subquery is not a correlated subquery
Answer: E
NEW QUESTION # 152
Which three statements are true?
- A. A product can have a different unit price at different times.
- B. A customer can exist in many countries.
- C. The statement will fail because subquery may not be I contained in a values clause.
- D. The SALES table has five foreign keys.
- E. The statement will execute successfully and a new row will be inserted into the SALES table.
- F. The statement will fail if a row already exists in the SALES table for product 23.
Answer: A,B,C
Explanation:
* A. A customer can exist in many countries. This is true as customers can have multiple addresses or operations in different countries, and a database design can reflect this by allowing multiple country entries for a single customer1.
* C. The statement will fail because subquery may not be I contained in a values clause. In Oracle Database 12c, a subquery cannot be used within the VALUES clause of an INSERT statement. The correct approach would be to use the subquery in conjunction with the INSERT INTO ... SELECT syntax if multiple rows are derived from a subquery2.
* F. A product can have a different unit price at different times. It is common for products to have different unit prices at different times due to various factors such as promotions, discounts, or changes in cost price. This can be represented in a database by having a price history table or a similar mechanism to track the changes in price over time1.
Note: The other options are incorrect because:
* B. The statement about the SALES table failing if a row already exists for product 23 is not necessarily true. Oracle allows for multiple rows with the same product ID if the table is designed to handle such cases, like having a composite primary key or no constraints preventing duplicates.
* D. Without specific information about the SALES table's design, we cannot verify the number of foreign keys it has.
* E. The statement about the successful execution and insertion of a new row into the SALES table is too vague without the context of the actual SQL statement being referred to.
NEW QUESTION # 153
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table? (Choose two.)
- A. CREATE TABLE employees
(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
Login_id NUMBER UNIQUE,
Employee_name VARCHAR2(25),
Hire_date DATE); - B. CREATE TABLE employees
(employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(25),
hire_date DATE,
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id)); - C. CREATE TABLE employees
(employee_id NUMBER,
Login_id NUMBER,
Employee_name VARCHAR2(100),
Hire_date DATE,
CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id)); - D. CREATE TABLE employees
(employee_id NUMBER,
Login_id NUMBER,
Employee_name VARCHAR2(100),
Hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);
CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id)); - E. CREATE TABLE employees
(employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,
Login_id NUMBER CONSTRAINT login_id_nn NOT NULL,
Employee_name VARCHAR2(100),
Hire_date DATE,
CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));
Answer: B,E
NEW QUESTION # 154
Which two are true about granting privileges on objects?
- A. An object privilege can be granted to a role only by the owner of that object.
- B. An object privilege can be granted to other users only by the owner of that object.
- C. The owner of an object acquires all object privileges on that object by default.
- D. The WITH GRANT OPTIONclause can be used only by DBA users.
- E. A table owner must grant the REFERENCESprivilege to allow other users to create FOREIGN KEY constraints using that table.
Answer: C,E
Explanation:
Explanation/Reference: https://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#i1008214
NEW QUESTION # 155
......
Updated Mar-2025 Pass 1z1-071 Exam - Real Practice Test Questions: https://www.exam-killer.com/1z1-071-valid-questions.html
Pass Your Exam With 100% Verified 1z1-071 Exam Questions: https://drive.google.com/open?id=1wVx5AECVKwswmMCFjRKSUt_2sv41eXNn
