1z0-071: Oracle Datbase 12c SQL Table Practice Questions

Evaluate this ALTER TABLE statement: (Choose the best answer.) ALTER TABLE orders SET UNUSED (order_date); Which statement is true?

• AA. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
• BB. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
• CC. ROLLBACK can be used to restore the ORDER_DATE column.
• DD. The DESCRIBE command would still display the ORDER_DATE column.

View the exhibit and examine the structure of the CUSTOMERS table. Which two tasks would require subqueries or joins to be executed in a single statement?

Select 2 answers.

• AA. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
• BB. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
• CC. listing of customers who do not have a credit limit and were born before 1980
• DD. finding the number of customers, in each city, who’s marital status is 'married'.
• EE. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

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?

• AA. 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");
• BB. 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);
• CC. 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");
• DD. 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');

You issued the following command: SQL> DROP TABLE employees; Which three statements are true?

Select 3 answers.

• AA. All uncommitted transactions are committed.
• BB. All indexes and constraints defined on the table being dropped are also dropped.
• CC. Sequences used in the employees table become invalid.
• DD. The space used by the employees table is reclaimed immediately.
• EE. The employees table can be recovered using the rollback command.
• FF. The employees table is moved to the recycle bin