1Z0-071 Free Practice Test

Evaluate the following CREATE TABLE command:

Which statement is true regarding the above SQL statement?

1. A. It would execute successfully and only ORD_ITM_IDX index would be created.
2. B. It would give an error because the USING INDEX clause cannot be used on a composite primary.
3. C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.
4. D. It would give an error because the USING INDEX is not permitted in the CRETAE TABLE command.

Correct Answer: A

View the Exhibit and examine the data in the employees table.

You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:

What is the outcome?

1. A. It executes successfully but does not give the correct output.
2. B. It generates an error because the concatenation operator can be used to combine only two items.
3. C. It generates an error because the usage of the round function in the expression is not valid
4. D. It generates an error because the alias is not valid.
5. E. It executes successfully and gives the correct output.

Correct Answer: A

View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

1. A. The table prefix is missing for the column names in the SELECT clause.
2. B. The NATURAL JOIN clause is missing the USING clause.
3. C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
4. D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Correct Answer: D
Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)

You must create a SALES table with these column specifications and data types: (Choose the best answer.) SALESID: Number
STOREID: Number ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table?

1. A. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");
2. B. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),QTY NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT 'SYSDATE',PAYMENT VARCHAR2(30) DEFAULT CASH);
3. C. CREATE TABLE Sales(SALESID NUMBER (4),STOREID NUMBER (4),ITEMID NUMBER (4),qty NUMBER DEFAULT = 1,SLSDATE DATE DEFAULT SYSDATE,PAYMENT VARCHAR2(30) DEFAULT = "CASH");
4. D. Create Table sales(salesid NUMBER (4),Storeid NUMBER (4),Itemid NUMBER (4),QTY NUMBER DEFAULT 1,Slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT 'CASH');

Correct Answer: D

Examine the structure of the BOOKS_TRANSACTIONS table:

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

1. A. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS
2. B. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS
3. C. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
4. D. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS

Correct Answer: B