Free 1Z0-071 Exam Dumps

Question 56

Examine the structure of the SALES table. (Choose two.)
1Z0-071 dumps exhibit
Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS
SELECT product_id, customer_id, quantity_sold, price FROM sales
WHERE 1 = 2;
Which two statements are true about the SALES1 table?

A. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
B. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
C. It will not be created because of the invalid WHERE clause.
D. It is created with no rows.
E. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Correct Answer:BD

Question 57

Which statement correctly grants a system privilege?

A. GRANT CREATE VIEWON table1 TOuser1;
B. GRANT ALTER TABLETO PUBLIC;
C. GRANT CREATE TABLETO user1, user2;
D. GRANT CREATE SESSIONTO ALL;

Correct Answer:C

Question 58

Examine the structure of the MEMBERS table: NameNull?Type
------------------ --------------- ------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?

A. It fails because the alias name specified after the column names is invalid.
B. It fails because the space specified in single quotation marks after the first two column names is invalid.
C. It executes successfully and displays the column details in a single column with only the alias column heading.
D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Correct Answer:D

Question 59

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
1Z0-071 dumps exhibit
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?

A. 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.
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 selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Correct Answer:D

Question 60

Examine the structure of the INVOICE table. NameNull?Type
-------------------------------------------------- INV_NONOT NULLNUMBER(3) INV_DATEDATE INV_AMTNUMBER(10,2)
Which two SQL statements would execute successfully?

A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;

Correct Answer:AC