1Z0-071 Free Practice Test

View the exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?

1. A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;
2. B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
3. C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);
4. D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

Correct Answer: C

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?

1. A. CREATE TABLE employees(employee_id NUMBER,Login_id NUMBER,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_ukUNIQUE (employee_id, login_id));
2. B. 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));
3. C. CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY, Login_id NUMBER UNIQUE, Employee_name VARCHAR2(25),Hire_date DATE);
4. D. 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));
5. E. 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));

Correct Answer: BE

Which two statements are true regarding constraints? (Choose two.)

1. A. A constraint is enforced only for an INSERT operation on a table.
2. B. A foreign key cannot contain NULL values.
3. C. The column with a UNIQUE constraint can store NULLS.
4. D. You can have more than one column in a table as part of a primary key.

Correct Answer: CD

You want to display the date for the first Monday of the next month and issue the following command: SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr') FROM DUAL;
What is the outcome?

1. A. In generates an error because rrrr should be replaced by rr in the format string.
2. B. It executes successfully but does not return the correct result.
3. C. It executes successfully and returns the correct result.
4. D. In generates an error because TO_CHAR should be replaced with TO_DATE.
5. E. In generates an error because fm and double quotation marks should not be used in the format string.

Correct Answer: C

View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion functions?

1. A. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003', 'MON DD YYYY') )
2. B. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
3. C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
4. D. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Correct Answer: CD