Free Oracle 1Z1-051 Exam Dumps Questions & Answers

Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:
SQL>SELECT c1.custname, c1.city
FROM Customers c1 __________________ Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

• A.  FULL OUTER JOIN
• B.  NATURAL JOIN
• C.  RIGHT OUTER JOIN
• D.  JOIN
• E.  LEFT OUTER JOIN

In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:

What would be the outcome?

• A.  Abigail Pa
• B.  an error message
• C.  Abigail PA
• D.  Abigail IS

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

• A.  The value for a CHAR data type column is blank-padded to the maximum defined columnwidth
• B.  A TIMESTAMP data type column stores only time values with fractional seconds
• C.  The BLOB data type column is used to store binary data in an operating system file
• D.  The minimum column width that can be specified for a VARCHAR2 data type column is one
• E.  Only One LONG column can be used per table

You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:

You want to create a SALE_PROD view by executing the following SQL statements:

Which statement is true regarding the execution of the above statement?

• A.  The view will be created and you can perform DLM operations on the view
• B.  The view will be created but no DML operations will be allowed on the view
• C.  The view will not be created because the GROUP BY clause is not allowed for creating a view
• D.  The view will not be created because the join statements are not allowed for creating a view

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update the EMPLOYEES table as follows:4 ? 4;
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London
(location_id 2100).
-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in location_id 2100 to 1.5 times the average commission of their
department.
You issue the following command:
SQL>UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = 2100),
(salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900,2700,2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700)
What is the outcome?

• A.  It executes successfully and gives the correct result.
• B.  It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.
• C.  It generates an error because a subquery cannot have a join condition in an UPDATE statement.
• D.  It executes successfully but does not give the correct result.