Online Access Free 1Z0-007 Practice Test

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?

• A.ALTER TABLE students ADD PRIMARY KEY student_id;
• B.ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
• C.ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
• D.ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
• E.ALTER TABLE studentsMODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

Exhibit:

Examine the data in the EMPLOYEES table.
Examine the subquery:
SELECT last_name FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id);
Which statement is true?

• A.The SELECT statement does not work because there is no HAVING clause.
• B.The SELECT statement is syntactically accurate.
• C.The SELECT statement does not work because the column specified in the GROUP BY clause is not in the SELECT list.
• D.The SELECT statement does not work because the GROUP BY clause should be in the
  main query and not in the subquery.

Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?

• A.SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM') NEW_DATE FROM dual;
• B.SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
• C.SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
• D.SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY), 'fmDdspth "of" Month YYYYfmtHH:HI:SS AM') NEW_DATE FROM dual;

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?

• A.MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
• B.MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
• C.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);
• D.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
• E.MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
• F.MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
• G.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
• H.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);

Evaluate this SQL statement:
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40)
ORDER BY dept_name;
The statement fails when executed. Which change fixes the error?

• A.remove the ORDER BY clause
• B.replace the condition
  "d.department_id NOT IN (10,40)"
  in the WHERE clause with
  "d.department_id <> 10 AND d.department_id <> 40"
• C.remove the table alias from the SELECT clause
• D.prefix the column in the USING clause with the table alias
• E.prefix the column in the ORDER BY clause with the table alias
• F.remove the table alias prefix from the WHERE clause