1z0-071 Practice Test Questions Updated 305 Questions [Q28-Q51] : Exam for engine : http://blog.test4engine.com

NEW QUESTION 28
Which statement is true regarding the INTERSECT operator?

The names of columns in all SELECT statements must be identical.

It ignores NULLs.

Reversing the order of the intersected tables alters the result.

The number of columns and data types must be identical for all SELECT statements in the query.
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

NEW QUESTION 29
Which three statements are true about the DESCRIBE command? (Choose three.)

It can be used to display the structure of an existing view

It can be used only from SQL*Plus

It displays the PRIMARY KEY constraint for any column or columns that have that constraint

It can be used from SQL Developer

It displays all constraints that are defined for each column

It displays the NOT NULL constraint for any columns that have that constraint

NEW QUESTION 30
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.

NEW QUESTION 31
View and Exhibit and examine the structure and data in the INVOICE table.

Which two statements are true regarding data type conversion in query expressions? (Choose two.)

inv_date = ’15-february-2008′ :uses implicit conversion

inv_amt = ‘0255982’ : requires explicit conversion

inv_date > ’01-02-2008′ : uses implicit conversion

CONCAT(inv_amt, inv_date) : requires explicit conversion

inv_no BETWEEN ‘101’ AND ‘110’ : uses implicit conversion

NEW QUESTION 32
Examine this query:
SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;
What is returned upon execution?

8 rows

1 row

6 rows

3 rows

0 rows

an error

NEW QUESTION 33
Examine the data in the CUSTOMERS table:

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

LEFT OUTER JOIN

JOIN

NATURAL JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

NEW QUESTION 34
View the Exhibit and examine the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:

Which statement is true regarding the result?

The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.

The statement will fail because a subquery cannot be used in a VALUES clause.

The statement will execute and a new row will be inserted in the SALES table.

The statement will fail because the VALUES clause is not required with the subquery.

NEW QUESTION 35
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE JOB_ID = ‘SA_MAN’
————————————-
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE JOB_ID = ‘SA_MAN’;
Choose two correct SET operators which would cause the query to return the desired result.

UNION

MINUS

INTERSECT

UNION ALL

NEW QUESTION 36
Which two statements will return the names of the three employees with the lowest salaries?

SELECT last_name, salary
FROM employees
WHERE ROWNUM<=3

SELECT last_name,salary
FROM employees
ORDER BY salary
FETCH FIRST 3 ROWS ONLY;

SELECT last_name,salary
FROM employees
WHERE ROWNUM<=3
ORDER BY (SELECT salary FROM employees);

SELECT last_name,salary
FROM (SELECT * FROM employees ORDER BY salary)

SELECT last_name,salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;

NEW QUESTION 37
Examine these statements executed in a single Oracle session:

Which three statements are true? (Choose three.)

The code for penis 1.

There is no row containing pencil.

The code for fountain penis 3.

The code for penis 10.

There is no row containing fountain pen.

There is no row containing pen.

NEW QUESTION 38
Examine the structure of the CUSTOMERS table: (Choose two.)

CUSTNO is the PRIMARY KEY.
You must determine if any customers’ details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

Subquery

Self-join

Full outer-join with self-join

Left outer-join with self-join

Right outer-join with self-join

NEW QUESTION 39
View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

WHERE order_date_IN ( TO_DATE(‘OCT 21 2003′,’MON DD YYYY’), TO_CHAR(‘NOV 21 2003′,’MON DD YYYY’) )

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

WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’

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

NEW QUESTION 40
Which two statements are true about sequences created in a single instance database? (Choose two.)

When the MAXVALUE limit for the sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.

DELETE <sequencename> would remove a sequence from the database.

The numbers generated by a sequence can be used only for one table.

CURRVAL is used to refer to the last sequence number that has been generated.

When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.

NEW QUESTION 41
View the Exhibit and examine the structure of CUSTOMERStable.

Evaluate the following query:

Which statement is true regarding the above query?

It produces an error because the condition on the CUST_CITYcolumn is not valid.

It produces an error because the condition on the CUST_FIRST_NAMEcolumn is not valid.

It produces an error because conditions on the CUST_CREDIT_LIMITcolumn are not valid.

It executes successfully.

NEW QUESTION 42
In the EMPLOYEEStable there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:

What would be the result?

It executes successfully but no rows updated.

It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.

It gives an error because multiple NVLfunctions are used in an expression.

It gives an error because NVLfunction cannot be used with UPDATE.

NEW QUESTION 43
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?

SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c.department_id);

SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.department_id);

SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.department_id);

SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d.department_id);

NEW QUESTION 44
View the exhibit and examine the ORDERStable.

The ORDERStable contains data and all orders have been assigned a customer ID. Which statement would add a NOTNULLconstraint to the CUSTOMER_IDcolumn?

ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

NEW QUESTION 45
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?

DELETE orders o, order_items iWHERE o.order_id = i.order_id;

DELETEFROM ordersWHERE (SELECT order_idFROM order_items);

DELETE ordersWHERE order_total < 1000;

DELETE order_idFROM ordersWHERE order_total < 1000;

NEW QUESTION 46
View the Exhibits and examine the structure of the COSTSand PROMOTIONStables.
You want to display PROD_IDSwhose promotion cost is less than the highest cost PROD_IDin a promotion time interval.
Examine this SQL statements:

What will be the result?
Exhibit 1.

Exhibit 2.

It gives an error because the GROUP BYclause is not valid

It executes successfully but does not give the required result

It executes successfully and gives the required result

It gives an error because the ALLkeyword is not valid

NEW QUESTION 47
Evaluate the following CREATE TABLE command:

Which statement is true regarding the above SQL statement?

It would execute successfully and only ORD_ITM_IDX index would be created.

It would give an error because the USING INDEX clause cannot be used on a composite primary.

It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.

It would give an error because the USING INDEX is not permitted in the CRETAE TABLE command.

NEW QUESTION 48
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.

NEW QUESTION 49
Examine the structure of the EMPLOYEES table.

You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)

SELECT MIN(Salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary);

SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365)
GROUP BY maxsal, minsal;

SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary);

SELECT MIN(Salary), MAX(salary)
FROM (SELECT salary FROM
employees
WHERE hire_date < SYSDATE-365);

NEW QUESTION 50
You need to produce a report where each customer’s credit limit has been incremented by
$1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

SELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;

SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;

SELECT cust_last_name AS Name, cust_credit_limit + 1000″New Credit Limit”FROM customers;

SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;

NEW QUESTION 51
Which two statements are true regarding the execution of the correlated subqueries?
(Choose two.)

The nested query executes after the outer query returns the row.

The nested query executes first and then the outer query executes.

The outer query executes only once for the result returned by the inner query.

Each row returned by the outer query is evaluated for the results returned by the inner query.