Verified 1z0-071 dumps Q&As – 100% Pass from It-Tests [Q44-Q66] – IT Certification Exam Questions & Answers

Rate this post

Verified 1z0-071 dumps Q&As – 100% Pass from It-Tests

Pass 1z0-071 Exam in First Attempt Guaranteed 2025 Dumps!

Oracle Corporation, the developer of the Oracle Database SQL certification exam, provides various resources to help candidates prepare for the exam. These resources include training courses, study guides, practice tests, and online forums. Oracle Corporation also offers a certification program that allows candidates to showcase their skills and knowledge in Oracle databases and SQL.

Oracle 1z1-071 exam, also known as the Oracle Database SQL exam, is a certification test that validates the knowledge and skills of individuals in using SQL to interact with Oracle Database. Passing 1z0-071 exam demonstrates that an individual has a strong foundation in SQL and is capable of performing basic database operations.

QUESTION 44
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

Only the primary key can be defined at the column and table level.

The foreign key columns and parent table primary key columns must have the same names.

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.

A table can have only one primary key but multiple foreign keys.

Primary key and foreign key constraints can be defined at both the column and table level.

A table can have only one primary key and one foreign key.

It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

QUESTION 45
Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?

After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.

The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.

ROLLBACK can be used to restore the ORDER_DATE column.

The DESCRIBE command would still display the ORDER_DATE column.

QUESTION 46
Examine the description of the CUSTOMERStable:

You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters.
Which query can be used?

SELECT * FROM customers WHERE city = ‘D_%’;

SELECT * FROM customers WHERE city LIKE ‘D_’;

SELECT * FROM customers WHERE city LIKE ‘D_%’;

SELECT * FROM customers WHERE city = ‘%D_’;

QUESTION 47
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION?

Each SELECT statement in the compound query must have its own ORDER BY clause.

Each SELECT statement in the compound query can have its own ORDER BY clause.

Column positions must be used in the ORDER BY clause.

The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.

Only column names from the first SELECT statement in the compound query are recognized.

QUESTION 48
View the exhibit and examine the descriptions of the DEPTand LOCATIONStables.

You want to update the CITYcolumn of the DEPTtable for all the rows with the corresponding value in the CITYcolumn of the LOCATIONStable for each department.
Which SQL statement would you execute to accomplish the task?

UPDATE dept d
SET city = ALL (SELECT city
FROM locations l
WHERE d.location_id = l.location_id);

UPDATE dept d
SET city = (SELECT city
FROM locations l)
WHERE d.location_id = l.location_id;

UPDATE dept d
SET city = ANY (SELECT city
FROM locations l)

UPDATE dept d
SET city = (SELECT city
FROM locations l
WHERE d.location_id = l.location_id);

QUESTION 49
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 referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this 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?

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

QUESTION 50
Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = ‘ORDERS’; Which two statements are true about the output?

The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.

The R_CONSTRAINT_NAME column contains an alternative name for the constraint.

In the second column, ‘c’ indicates a check constraint.

The STATUS column indicates whether the table is currently in use.

QUESTION 51
Which two statements are true?

AIl conditions evaluated using DECODE can also be evaluated using CASE.

All conditions evaluated using CASE can also be evaluated using DECODE.

CASE is a function and DECODE is not.

DECODE is a function and CASE is not.

Neither CASE nor DECODE is a function.

Both CASE and DECODE are functions.

QUESTION 52
Examine these two queries and their output:
SELECT deptno, dname FROM dept;

SELECT ename, job, deptno FROM emp ORDER BY deptno;

Now examine this query:
SELECT ename, dname
FROM emp CROSS JOIN dept WHERE job = 'MANAGER'
AND dept.deptno IN (10, 20) ;

QUESTION 53
View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables.

You are asked to retrieve the ORDER_ID, product_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECT prder_id, product_id, unit_price*quantity “Total Price”
FROM order_items
WHERE unit_price*quantity > 50000
NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?

The statement would execute and provide the desired result.

The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.

The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.

The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.

QUESTION 54
Which two queries execute successfully?

SELECT NULLIF(100, 100) FROM DUAL;

SELECT COALESCE(100, NULL, 200) FROM DUAL;

SELECT NULLIF(100, ‘A’) FROM DUAL;

SELECT NULLIF(NULL, 100) FROM DUAL;

SELECT CO ALESCE(100, ‘A’ ) FROM DUAL;

QUESTION 55
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null. (Choose two.) Which two SQL statements would create the required table?

CREATE TABLE employees
(employee_id NUMBER,
Login_id NUMBER,
Employee_name VARCHAR2(100),
Hire_date DATE,
CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));

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));

CREATE TABLE employees
(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
Login_id NUMBER UNIQUE,
Employee_name VARCHAR2(25),
Hire_date DATE);

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));

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));

QUESTION 56
The first DROPoperation is performed on PRODUCTStable using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACKoperation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACKcommand?

It recovers only the table structure.

It recovers the table structure, data, and the indexes.

It recovers the table structure and data but not the related indexes.

It is not possible to recover the table structure, data, or the related indexes.

QUESTION 57
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

SELECT TO_CHAR (1890.55, ‘$99G999D00’)
FROM DUAL

SELECT TO_CHAR (1890.55, ‘$9,999V99’)
FROM DUAL;

SELECT TO_CHAR (1890.55, ‘$0G000D00’)
FROM DUAL;

SELECT TO_CHAR (1890.55, ‘$99,999D99’)
FROM DUAL;

SELECT TO_CHAR (1890.55, ‘$99G999D99’)
FROM DUAL

QUESTION 58
The ORDERStable has a column ORDER_DATE of data type DATE.
The default display format for a date is DD-MON-RR.
Which two WHEREconditions demonstrate the correct usage of conversion functions? (Choose two.) WHERE TO_CHAR(order_date, ‘MON DD YYYY’) = ‘JAN 20 2019’

WHERE order_date > TO_DATE(‘JUL 10 2018’, ‘MON DD YYYY’)

WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6), ‘MON DD YYYY’)

WHERE order_date > TO_DATE(ADD_MONTHS(SYSDATE,6), ‘MON DD YYYY’)

WHERE order_date IN (TO_DATE(‘OCT 21 2018’, ‘Mon DD YYYY’), TO_CHAR(‘Nov 21

2018′, ‘Mon DD YYYY’))

QUESTION 59
Examine this SELECT statement and view the Exhibit to see its output:

SELECT constraints_name, constraints_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.)

The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.

The R_CONSTRAINT_NAME column contains an alternative name for the constraint.

In the second column, ‘c’ indicates a check constraint.

The STATUS column indicates whether the table is currently in use.

QUESTION 60
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?

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;

SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.manager_id=100;

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;

SELECT m.last_name “Manager”, e.last_name “Employee”
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.manager_id=100;

QUESTION 61
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or
cust_credit_limit = 3000
Which statement is true regarding the above two queries?

Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.

There would be no change in performance.

Performance would degrade in query 2.

Performance would improve in query 2.

QUESTION 62
Which three statements are true about an ORDER BY clause?

An ORDER BY clause always sorts NULL values last.

An ORDER BY clause can perform a binary sort

An ORDER BY clause can perform a linguistic sort

By default an ORDERBY clause sorts rows in ascending order

An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

QUESTION 63
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:
SELECT department_id “DEPT_ID”, department_name, ‘b’ FROM
departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, ‘a’ FROM
departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort output?

ORDER BY DEPT_NAME;

ORDER BY DEPT_ID;

ORDER BY ‘b’;

ORDER BY 3;

QUESTION 64
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

QUESTION 65
Which two statements are true regarding the GROUP BY clause in a SQL statement?
(Choose two.)

You can use column alias in the GROUP BY clause.

Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.

The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.

Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.

If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.

QUESTION 66
Examine the structure of the BOOKS_TRANSACTIONStable:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;

SELECT member_id ‘MEMBER ID’, due_date ‘DUE DATE’, ‘$2 AS LATE FEE’ FROM BOOKS_TRANSACTIONS;

SELECT member_id AS “MEMBER ID”, due_date AS “DUE DATE”, ‘$2’ AS “LATE FEE” FROM BOOKS_TRANSACTIONS;

SELECT member_id AS “MEMBER ID”, due_date AS “DUE DATE”, $2 AS “LATE FEE” FROM BOOKS_TRANSACTIONS;

1z0-071 Dumps Full Questions – Exam Study Guide: https://www.it-tests.com/1z0-071.html

Tags: 1z0-071 clearer explanation, 1z0-071 exam tutorial, 1z0-071 latest test braindumps, 1z0-071 latest test tips, 1z0-071 new exam cram sheet file

IT Certification Exam Questions & Answers

Leave a Reply Cancel reply