Online Access Free 1Z0-051 Practice Test

Evaluate the following SQL statements: Exhibit:

Exhibit:

The above command fails when executed. What could be the reason?

• A.The CHECK constraint cannot be placed on columns having the DATE data type
• B.SYSDATE cannot be used with the CHECK constraint
• C.The BETWEEN clause cannot be used for the CHECK constraint
• D.ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY

In which two cases would you use an outer join? (Choose two.)

• A.The tables being joined have only unmatched data.
• B.The tables being joined have both matched and unmatched data.
• C.Only when the tables have a primary key/foreign key relationship.
• D.The columns being joined have NULL values.
• E.The tables being joined have NOT NULL columns.
• F.The tables being joined have only matched data.

See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:


You issue the following query:

Which statement is true regarding the outcome of this query?

• A.It executes successfully
• B.It produces an error because the NATURAL join can be used only with two tables
• C.It produces an error because all columns used in the NATURAL join should have a qualifier
• D.It produces an error because a column used in the NATURAL join cannot have a qualifier

You need to create a table named ORDERS that contain four columns:
1.an ORDER_ID column of number data type
2.a CUSTOMER_ID column of number data type
3.an ORDER_STATUS column that contains a character data type
4.a DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed,
today's date should be used instead.
Which statement accomplishes this?

• A.CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);
• B.CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
• C.CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
• D.CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
• E.CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE);
• F.CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

• A.SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
• B.SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
• C.SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
• D.SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;