OCA DUMPS-Oracle Database 11g: SQL Fundamentals I Practice Test

----------------------------------------------------------------------------------------------------
Review Before Writing Paper..
----------------------------------------------------------------------------------------------------

QUESTION NO: 1
See the Exhibit:
You need to display all promo categories that do not have 'discount' in their subcategory.
Which two SQL statements give the required result? (Choose two.)
A. SELECT promo_category
FROM promotions
MINUS
SELECT promo_category
FROM promotions
WHERE promo_subcategory = 'discount'
B. SELECT promo_category
FROM promotions
INTERSECT
SELECT promo_category
FROM promotions
WHERE promo_subcategory = 'discount'
C. SELECT promo_category
FROM promotions
MINUS
SELECT promo_category
FROM promotions
WHERE promo_subcategory <> 'discount'
D. SELECT promo_category
FROM promotions
INTERSECT
SELECT promo_category
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 2
ActualTests.com
FROM promotions
WHERE promo_subcategory <> 'discount'
Answer: A,D
QUESTION NO: 2
See the Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. UPDATE promotions
SET promo_cost = promo_cost+100
WHERE TO_CHAR(promo_end_date,'yyyy'>'2000';
B. SELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
C. UPDATE promotions
SET promo_cost = promo_cost+100
WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-200',8));
D. SELECT TO_CHAR(promo_begin_date,'dd/month')
FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98',TO_DATE('JUL 01 98'));
Answer: A,B
QUESTION NO: 3
Which two statements are true about sequences created in a single instance database? (Choose
two.)
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 3
ActualTests.com
A. The numbers generated by a sequence can be used only for one table
B. DELETE <sequencename> would remove a sequence from the database
C. CURRVAL is used to refer to the last sequence number that has been generated
D. When the MAXVALUE limit for a sequence for reached, you can increase the MAXVALUE limit
by using the ALTER SEQUENCE statement
E. When a database instance shuts down abnormally, the sequence numbers that have been
cached but not used would be available once again when the database instance is restarted
Answer: C,D
QUESTION NO: 4
The SQL statements executed in a user session as follows:
Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a
command in the session? (Choose two.)
A. The rollback generates an error
B. No SQL statements are rolled back
C. Only the DELETE statements are rolled back
D. Only the seconds DELETE statement is rolled back
E. Both the DELETE statements and the UPDATE statement are rolled back
Answer: A,B
QUESTION NO: 5
Which three statements/commands would cause a transaction to end? (Choose three.)
A. COMMIT
B. SELECT
C. CREATE
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 4
ActualTests.com
D. ROLLBACK
E. SAVEPOINT
Answer: A,C,D
QUESTION NO: 6
Evaluate the following SQL statements:
Which is the correct output of the above query?
A. +25-00, +54-02, +00 11:12:10.123457
B. +00-300, +54-02,+00 11:12:10.123457
C. +25-00,+00-650,+00 11:12:10.123457
D. +00-300,+00-650,+00 11:12:10.123457
Answer: A
QUESTION NO: 7
Which three statements are true regarding subqueries? (Choose three.)
A. Subqueries can contain GROUP BY and ORDER BY clauses
B. Main query and subquery can get data from different tables
C. Main query and subquery must get data from the same tables
D. Subqueries can contain ORDER BY but not the GROUP BY clause
E. Only one column or expression can be compared between the main query and subqeury
F. Multiple columns or expressions can be compared between the main query and subquery
Answer: A,B,F
QUESTION NO: 8
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 5
ActualTests.com
You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where
CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will
accomplish the task?
A. UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level = NULL;
B. UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL;
C. UPDATE customers
SET cust_credit_limit = TO_NUMBER(NULL)
WHERE cust_income_level = TO_NUMBER(NULL);
D. UPDATE customers
SET cust_credit_limit = TO_NUMBER(' ',9999)
WHERE cust_income_level IS NULL;
Answer: B
QUESTION NO: 9
Which two statements are true regarding working with dates? (Choose two.)
A. The default internal storage of dates is in the numeric format
B. The default internal storage of dates is in the character format
C. The RR date format automatically calculates the century from the SYSDATE function and does
not allow the user to enter the century
D. The RR date format automatically calculates the century from the SYSDATE function but allows
the user to enter the century if required
Answer: A,D
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 6
ActualTests.com
QUESTION NO: 10
Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause
B. A view is created with the subquery having the DISTINCT keyword can be updated
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot
be updated
D. A Data Manipulation Language (DML) operation can be performed on a view that is created
with the subquery having all the NOT NULL columns of a table
Answer: C,D
QUESTION NO: 11
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 7
ActualTests.comEvaluate the following SQL statements:
Which statement is true regarding the above compound query?
A. It reduces an error
B. It shows products that were sold and have a cost recorded
C. It shows products that were sold but have no cost recorded
D. It shows products that have a cost recorded irrespective of sales
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 8
ActualTests.com
QUESTION NO: 12
Examine the structure of the MARKS table:
Which two statements would execute successfully? (Choose two.)
A. SELECT student_name, subject1
FROM marks
WHERE subject1 > AVG(subject1);
B. SELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE 'R%';
C. SELECT SUM (subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL
D. SELECT SUM (DISTINCT NVL(subject1,0)),MAX(subject1)
FROM marks
WHERE subject1 > subject2;
Answer: C,D
QUESTION NO: 13
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 9
ActualTests.com
Using the PROMOTIONS table, you need to display the names of all promos done after January
1, 2001 starting with the latest promo.
Which query would give the required result? (Choose all that apply.)
A. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 2 DESC;
B. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY promo_name DESC;
C. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 1 DESC;
D. SELECT promo_name,promo_begin_date "START DATE"
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY "START DATE" DESC;
Answer: A,D
QUESTION NO: 14
When does a transaction complete? (Choose all that apply.)
A. When a DELETE statement is executed
B. When a ROLLBACK command is executed
C. When a PL/SQL anonymous block is executed
D. When a data definition language statement is executed
E. When a TRUNCATE statement is executed after the pending transaction
Answer: B,D,E
QUESTION NO: 15
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 10
ActualTests.com
Which statement would display the highest credit limit available in each income level in each city in
the CUSTOMERs table?
A. SELECT cust_city,cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city,cust_income_level,cust_credit_limit;
B. SELECT cust_city,cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city,cust_income_level;
C. SELECT cust_city,cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city;
D. SELECT cust_city,cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level,MAX(cust_credit_limit);
Answer: B
QUESTION NO: 16
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 11
ActualTests.com
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY
that have the same data types and size as the corresponding columns in the CUSTOMERS table.
Evaluate the following INSERT SQL statement:
The INSERT statement fails when executed. What could be the reason?
A. The VALUES clause cannot be used in an INSERT with a subquery
B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match
C. The WHERE clause cannot be used in a subquery embedded in an INSERT statement
D. The total number of columns in the NEW_CUSTOMERS table does not match the total number
of columns in the CUSTOMERS table
Answer: A
QUESTION NO: 17
See the Exhibit:
PROMOTIONS table
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 12
ActualTests.com
SALES table
Evaluate the following SQL statements:
Which statement is true regarding the output of the above query?
A. It gives the details of promos for which there have been sales
B. It gives the details of promos for which there have been no sales
C. It gives details of all promos irrespective of whether they have resulted in a sale or not
D. It gives details of product IDs that have been sold irrespective of whether they had a promo or
not
Answer: C
QUESTION NO: 18
You need to generate a list of all customer last names with their credit limits from the
CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list.
Which two queries would achieve the required result? (Choose two.)
A. SELECT cust_last_name,cust_credit_limit
FROM customers
ORDER BY cust_credit_limit DESC;
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 13
ActualTests.com
B. SELECT cust_last_name,cust_credit_limit
FROM customers
ORDER BY cust_credit_limit;
C. SELECT cust_last_name,cust_credit_limit
FROM customers
ORDER BY cust_credit_limit NULLS LAST;
D. SELECT cust_last_name,cust_credit_limit
FROM customers
ORDER BY cust_last_name,cust_credit_limit NULLS LAST;
Answer: B,C
QUESTION NO: 19
See the 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
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 14
ActualTests.com
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 join statements are not allowed for creating a view
D. The view will not be created because the GROUP BY clause is not allowed for creating a view
Answer: B
QUESTION NO: 20
See the Exhibit:
You issue the following SQL statement:
Which statement is true regarding the execution of the above query?
A. It produces an error because the AMT_SPENT column contains a null value
B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT
C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT or
AMT_SPENT is null
D. It produces an error because the TO_NUMBER function must be used to convert the result of
the NULLIF function before it can be used by the NVL2 function
Answer: C
QUESTION NO: 21
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 15
ActualTests.comThere is only one customer with the CUST_LAST_NAME column having value Roberts. Which
INSERT statement should be used to add a row into the ORDERS table for the customer whose
CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
A. INSERT INTO orders
VALUES(1,'10-mar-2007','direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),1000);
B. INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES (1,'10-mar-2007','direct',&&customer_id,1000);
C. INSERT INTO(SELECT o.order_id,o.order,o.order_mode,c.customer_id,o.order_total
FROM orders o, customers c
WHERE o.customer_id=c.customer_id
AND c.cust_last_name='Roberts' and c.credit_limit=600)
VALUES (1,'10-mar-2007','direct',&&customer_id,1000);
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),1000);
D. INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 16
ActualTests.com
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES (1,'10-mar-2007','direct',&customer_id,1000);
Answer: A
QUESTION NO: 22
Evaluate the following SQL query;
What would be the outcome?
A. 16
B. 100
C. 160
D. 200
E. 150
Answer: C
QUESTION NO: 23
See the Exhibit:
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?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 17
ActualTests.com
A. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date)
FROM promotions) AND
promo_category = 'INTERNET';
B. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions)
WHERE promo_category = 'INTERNET';
C. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET';
D. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET';
Answer: C
QUESTION NO: 24
Evaluate the following SQL statements:
The above command fails when executed. What could be the reason?
A. SYSDATE cannot be used with the CHECK constraint
B. The BETWEEN clause cannot be used for the CHECK constraint
C. The CHECK constraint cannot be placed on columns having the DATE data type
D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also
the FOREIGN KEY
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 18
ActualTests.com
Answer: A
QUESTION NO: 25
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY
B. It produces an error because positional notation cannot be used in the ORDER BY clause with
SET operators
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end
of the compound statement
D. It produces an error because the ORDER BY clause should appear only at the end of a
compound query-that is, with the last SELECT statement
Answer: D
QUESTION NO: 26
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 19
ActualTests.comThe CUSTOMERS table contains the current location of all currently active customers. The
CUST_HISTORY table stores historical details relating to any changes in the location of all current
as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?
A. MINUS
B. UNION
C. INTERSECT
D. UNION ALL
Answer: A
QUESTION NO: 27
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 20
ActualTests.com
you issue the following SQL statement on the CUSTOMERS table to display the customers who
are in the same country as customers with the last name 'king' and whose credit limit is less than
the maximum credit limit in countries that have customers with the last name 'king'.
Which statement is true regarding the outcome of the above query?
A. It executes and shows the required result
B. It produces an error and the < operator should be replaced by < ALL to get the required output
C. It produces an error and the < operator should be replaced by < ANY to get the required output
D. It produces an error and the IN operator should be replaced by = in the WHERE clause of the
main query to get the required output
Answer: A
QUESTION NO: 28
Which two statements are true regarding working with dates? (Choose two.)
A. The default internal storage of dates is in the numeric format
B. The default internal storage of dates is in the character format
C. The RR date format automatically calculates the century from the SYSDATE function and does
not allow the user to enter the century
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 21
ActualTests.com
D. The RR date format automatically calculates the century from the SYSDATE function but allows
the user to enter the century if required
Answer: A,D
QUESTION NO: 29
Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values
B. A columns with the UNIQUE constraint can contain NULL values
C. A constraint is enforced only for the INSERT operation on a table
D. A constraint can be disabled even if the constraint column contains data
E. All constraints can be defined at the column level as well as the table level
Answer: B,D
QUESTION NO: 30
Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause
B. A view is created with the subquery having the DISTINCT keyword can be updated
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot
be updated
D. A Data Manipulation Language (DML) operation can be performed on a view that is created
with the subquery having all the NOT NULL columns of a table
Answer: C,D
QUESTION NO: 31
Evaluate the following SQL statements:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 22
ActualTests.com
Which ORDER BY clauses are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2.cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
Answer: A,C,E
QUESTION NO: 32
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?
A. It would not remove the rows if the table has a primary key
B. It removes all the rows as well as the structure of the table
C. It removes all the rows in the table and deleted rows can be rolled back
D. It removes all the rows in the table and deleted rows cannot be rolled back
Answer: C
QUESTION NO: 33
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument
B. They can be nested only to two levels
C. Arguments can only be column values or constant
D. They always return a single result row for every row of a queried table
E. They can return a data type value different from the one that is reference
Answer: D,E
QUESTION NO: 34
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 23
ActualTests.com
Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped
B. A FOREIGN KEY constraint on a column in a table automatically creates a nonunique key
C. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a
unique index
D. For each data manipulation language operation performed, the corresponding indexes are
automatically updated
Answer: A,C,D
QUESTION NO: 35
See the Exhibit:
You need to generate a report that displays the IDs or all products in the COSTS table whose unit
price is at least 25% more than the unit cost. The details should be displayed in the descending
order of 25% of the unit cost.
You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result
B. It produces an error because an expression cannot be used in the ORDER BY clause
C. It produces an error because the DESC option cannot be used with an expression in the
ORDER BY clause
D. It produces an error because the expression in the ORDER BY clause should also be specified
in the SELECT clause
Answer: A
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 24
ActualTests.com
QUESTION NO: 36
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose
two.)
A. Only One LONG column can be used per table
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. The value for a CHAR data type column is blanked-padded to the maximum defined column
width
Answer: A,D,E
QUESTION NO: 37
See the Exhibit:
Examine the structure of CUSTOMERS table:
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. It executes successfully
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 25
ActualTests.com
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause
C. It returns an error because WHERE and HAVING clause cannot be used in the same SELECT
statement
D. It returns an error because WHERE and HAVING clause cannot be used to apply conditions on
the same column
Answer: A
QUESTION NO: 38
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)
A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007) FROM DUAL;
D. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
E. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
Answer: B,C
QUESTION NO: 39
You are currently located in Singapore and have connected to a remote database in Chicago. You
issue the following command:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. An error because the ROUND function specified is invalid
B. An error because the WHERE condition specified is invalid
C. Number of days since the promo started based on the current Chicago data and time
D. Number of days since the promo started based on the current Singapore data and time.
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 26
ActualTests.com
QUESTION NO: 40
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument
B. They can be nested only to two levels
C. Arguments can only be column values or constant
D. They always return a single result row for every row of a queried table
E. They can return a data type value different from the one that is reference
Answer: D,E
QUESTION NO: 41
See the structure of the PROGRAMS table:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)
FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))
FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')
FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')
FROM programs;
Answer: A,D
QUESTION NO: 42
Which statement is true regarding the COALESCE function?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 27
ActualTests.com
A. It can have a maximum of five expressions in a list
B. It returns the highest NOT NULL value in the list for all rows
C. It requires that all expressions in the list must be of the same data type
D. It requires that at least one of the expressions in the list must have a NOT NULL value
Answer: C
QUESTION NO: 43
See the exhibit:
Evaluate the following query:
The above query produces an error on execution. What is the reason for the error?
A. An alias cannot be used in an expression
B. The alias NAME should not be enclosed within double quotation marks
C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL
values
D. The alias MIDPOINT should be enclosed within double quotation marks for the
CUST_CREDIT_LIMIT/2 expression
Answer: A
QUESTION NO: 44
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 28
ActualTests.comYou need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
B. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;
C. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;
D. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
Answer: B,C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 29
ActualTests.com
QUESTION NO: 45
See the Exhibit and examine the structure of the SALES table:
The following query is written to retrieve all those product IDs from the SALES table that have
more than 55000 sold and have been ordered more than 10 times:
Which statement is true regarding this SQL statement?
A. It executes successfully and generates the required result
B. It produces an error because COUNT (*) should be specified the SELECT clause also
C. It produces an error because COUNT (*) should be only the HAVING clause and not in the
WHERE clause
D. It executes successfully but produces no result because COUNT(prod_id) should be used
instead of COUNT(*)
Answer: C
QUESTION NO: 46
Which three statements are true regarding subqueries? (Choose three.)
A. Subqueries can contain GROUP BY and ORDER BY clauses
B. Main query and subquery can get data from different tables
C. Main query and subquery must get data from the same tables
D. Subqueries can contain ORDER BY but not the GROUP BY clause
E. Only one column or expression can be compared between the main query and subqeury
F. Multiple columns or expressions can be compared between the main query and subquery
Answer: A,B,F
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 30
ActualTests.com
QUESTION NO: 47
The CUSTOMERS table has the following structure:
You need to write a query that does the following task:
* Display the first name and tax amount of the customers. Tax is 5% of their credit limit
* Only those customers whose income level has a value should be considered
* Customers whose tax amount is null should not be considered
Which statement accomplishes all the required tasks?
A. SELECT cust_first_name,cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
tax_amount IS NOT NULL
B. SELECT cust_first_name,cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
cust_credit_limit IS NOT NULL
C. SELECT cust_first_name,cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level <> NULL AND
tax_amount <> NULL
D. SELECT cust_first_name,cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE (cust_income_level,tax_amount) IS NOT NULL
Answer: B
QUESTION NO: 48
See the Exhibit and examine the structure and data in the INVOICE table:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 31
ActualTests.comWhich two SQL statements would executes successfully? (Choose two.)
A. SELECT (AVG(inv_date) FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
C. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
D. SELECT AVG(inv_date - SYSDATE),AVG(inv)amt) FROM invoice;
Answer: B,D
QUESTION NO: 49
You are currently located in Singapore and have connected to a remote database in Chicago.
You issue the following command:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. An error because the ROUND function specified is invalid
B. An error because the WHERE condition specified is invalid
C. Number of days since the promo started based on the current Chicago data and time
D. Number of days since the promo started based on the current Singapore data and time.
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 32
ActualTests.com
QUESTION NO: 50
See the Exhibit:
and examine the structure of CUSTOMRS AND SALES tables:
Evaluate the following SQL statement:
Which statement is true regarding the execution of the above UPDATE statement?
A. It would not execute because two tables cannot be used in a single UPDATE statement
B. It would not execute because the SELECT statement cannot be used in place of the table name
C. It would execute and restrict modifications to only the column specified in the SELECT
statement
D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE
statement
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 33
ActualTests.com
QUESTION NO: 51
See the Exhibit and examine the structure of the PROMOTIONS table:
Using the PROMOTIONS table, you need to find out the average cost for all promos in the range
$0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
What would be the outcome?
A. It executes successfully and gives the required result
B. It generates an error because NULL cannot be specified as a return value
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because multiple conditions cannot be specified for the WHEN clause
Answer: A
QUESTION NO: 52
Evaluate the following two queries:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 34
ActualTests.com
Which statement is true regarding the above two queries?
A. Performance would improve in query 2
B. Performance would degrade in query 2
C. There would be no change in performance
D. Performance would improve in query 2 only if there are null values in the
CUST_CREDIT_LIMIT column
Answer: C
QUESTION NO: 53
Which statement is true regarding the COALESCE function?
A. It can have a maximum of five expressions in a list
B. It returns the highest NOT NULL value in the list for all rows
C. It requires that all expressions in the list must be of the same data type
D. It requires that at least one of the expressions in the list must have a NOT NULL value
Answer: C
QUESTION NO: 54
Evaluate the following SQL statements:
You issue the following command to create a view that displays the IDs and last names of the
sales staff in the organization.
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 35
ActualTests.com
Which two statements are true regarding the above view? (Choose two.)
A. It allows you to insert rows into the EMPLOYEES table
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES
table
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in
multitable INSERT statements
Answer: B,D
QUESTION NO: 55
Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause
B. A view is created with the subquery having the DISTINCT keyword can be updated
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot
be updated
D. A Data Manipulation Language (DML) operation can be performed on a view that is created
with the subquery having all the NOT NULL columns of a table
Answer: C,D
QUESTION NO: 56
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 36
ActualTests.com
you issue the following SQL statement on the CUSTOMERS table to display the customers who
are in the same country as customers with the last name 'king' and whose credit limit is less than
the maximum credit limit in countries that have customers with the last name 'king'.
Which statement is true regarding the outcome of the above query?
A. It executes and shows the required result
B. It produces an error and the < operator should be replaced by < ALL to get the required output
C. It produces an error and the < operator should be replaced by < ANY to get the required output
D. It produces an error and the IN operator should be replaced by = in the WHERE clause of the
main query to get the required output
Answer: A
QUESTION NO: 57
Which statement is true regarding the default behavior of the ORDER BY clause?
A. In a character sort, the values are case-sensitive
B. NULL values are not considered at all by the sort operation
C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause
D. Numeric values are displayed from the maximum to the minimum value if they have decimal
positions
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 37
ActualTests.com
Answer: A
QUESTION NO: 58
Which three task can be performed using SQL functions built into Oracle Database? (Choose
three.)
A. Displaying a date in a nondefault format
B. Finding the number of characters in an expression
C. Substituting a character string in a text expression with a specified string
D. Combining more than two columns or expressions into a single column in the output
Answer: A,B,C
QUESTION NO: 59
See the exhibit:
Exhibit 1:
Exhibit 2:
Exhibit 3:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 38
ActualTests.com
Examine the structures of the PRODUCTS, SALES AND CUSTOMERS table.
You need to generate a report that gives details of the customer's last name, name of the product
and the quantity sold for all customers in 'Tokyo'.
Which two queries give the required result? (Choose two.)
A. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM sales s JOIN products p
USING (prod_id)
JOIN customers c
USING (cust_id)
WHERE c.cust_city='Tokyo';
B. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM products p JOIN sales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHERE c.cust_city='Tokyo';
C. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM products p JOIN sales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
WHERE c.cust_city='Tokyo';
D. SELECT c.cust_last_name,p.prod_name,s.quantity_sold
FROM products p JOIN sales s
USING (prod_id)
ON(p.prod_id=s.prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city='Tokyo';
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 39
ActualTests.com
Answer: A,C
QUESTION NO: 60
Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date DATE NOT NULL);
B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL),
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL),
CONSTRAINT ord_pk PRIMARY KEY (ord_no,item_no));
Answer: D
QUESTION NO: 61
Examine the structure of the INVOICE table:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')
FROM invoice;
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 40
ActualTests.com
B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')
FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv,sysdate)
FROM invoice;
D. SELECT inv_no,NVL2(inv_date,inv_amt*.25,'Not Available')
FROM invoice;
Answer: A,C
QUESTION NO: 62
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values
B. Reversing the order of the intersected tables the result
C. The names of columns in all SELECT statements must be identical
D. The number of columns and data types must be identical for all SELECT statements in the
query
Answer: D
QUESTION NO: 63
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?
A. It would not remove the rows if the table has a primary key
B. It removes all the rows as well as the structure of the table
C. It removes all the rows in the table and deleted rows can be rolled back
D. It removes all the rows in the table and deleted rows cannot be rolled back
Answer: C
QUESTION NO: 64
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the
ORDERS table to the user HR.
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 41
ActualTests.com
Which statement would create a synonym ORD so that HR can execute the following query
successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database
administrator
D. CREATE PUBLIC SYNONY ord FOR oe.orders; This command is issued by the database
administrator
Answer: D
QUESTION NO: 65
Evaluate the following SQL statements:
Which ORDER BY clauses are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
Answer: A,C,E
QUESTION NO: 66
Exhibit contains the structure of PRODUCTS table:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 42
ActualTests.com
Evaluate the following query:
What would be the outcome of executing the above SQL statement?
A. It produces an error
B. It shows the names of all products in the table
C. It shown the names of products whose list price is the second highest in the table
D. It shown the names of all products whose list price is less than the maximum list price
Answer: C
QUESTION NO: 67
Evaluate the following SQL statements:
You issue the following command to create a view that displays the IDs and last names of the
sales staff in the organization.
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 43
ActualTests.com
Which two statements are true regarding the above view? (Choose two.)
A. It allows you to insert rows into the EMPLOYEES table
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES
table
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in
multitable INSERT statements
Answer: B,D
QUESTION NO: 68
Which statement is true regarding the default behavior of the ORDER BY clause?
A. In a character sort, the values are case-sensitive
B. NULL values are not considered at all by the sort operation
C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause
D. Numeric values are displayed from the maximum to the minimum value if they have decimal
positions
Answer: A
QUESTION NO: 69
The PRODUCTS table has the following structure:
Evaluate the following two SQL statements:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 44
ActualTests.com
Which statement is true regarding the outcome?
A. Both the statements execute and give different results
B. Both the statements execute and give the same result
C. Only the first SQL statement executes successfully
D. Only the second SQL statement executes successfully
Answer: A
QUESTION NO: 70
You want to create an ORD_DETAIL table to store details for an order placed having the following
business requirement:
The order ID will be unique and cannot have null values
The order date cannot have null values and the default should be the current date
The order amount should not be less than 50
The order status will have values either shipped or not shipped
The order payment mode should be cheque, credit card or cash on delivery (COD)
Which is the valid DDL statement for creating the ORD_DETAIL table?
A. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5,2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shopped','Not Shipped')
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque','Credit Card',
'Cash On Delivery')));
B. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5,2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shopped','Not Shipped')
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque','Credit Card',
'Cash On Delivery')));
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 45
ActualTests.com
C. CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5,2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shopped','Not Shipped')
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque','Credit Card',
'Cash On Delivery')));
D. CREATE TABLE ord_details
(ord_id NUMBER(2) ,
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5,2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN ('Shopped','Not Shipped')
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque','Credit Card',
'Cash On Delivery')));
Answer: C
QUESTION NO: 71
Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clause can be used for equijoins and nonequijoins
B. A maximum of one pair of columns can be joined between two tables using the ON clause
C. The ON clause can be used to join tables on columns that have different names but compatible
data types
D. The WHERE clause can be used to apply additional conditions in SELECT statement
containing the ON or the USING clause
Answer: C,D
QUESTION NO: 72
Which statement is true regarding the UNION operator?
A. By default, the output is not sorted
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 46
ActualTests.com
B. NULL values are not ignored during duplicate checking
C. Names of all columns must be identical across all SELECT statements
D. The number of columns selected in all SELECT statements need to be the same
Answer: B
QUESTION NO: 73
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)
A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007) FROM DUAL;
D. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
E. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
Answer: B,C
QUESTION NO: 74
You created an ORDERS table with the following description:
You inserted some rows in the table. After some time, you want to alter the table by creating the
PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?
A. You cannot have two constraints on one column
B. You cannot add a primary key constraint if data exists in the column
C. The primary key constraint can be created only a the time of table creation
D. You can add the primary key constraint even if data exists, provided that there are no duplicate
values
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 47
ActualTests.com
Answer: D
QUESTION NO: 75
You need to create a table for a banking application. One of the columns in the table has he
following requirements:
You want a column in the table to store the duration of the credit period. The data in the column
should be stored in a format such that it can be easily added and subtracted with DATE data type
without using conversion The maximum period of the credit provision in the application is 30 days
the interest has to be calculated for the number of days an individual has taken a credit for
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D
QUESTION NO: 76
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
Exhibit1:
Exhibit 2:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 48
ActualTests.com
Exhibit 3:
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 a column used in the NATURAL join cannot have a qualifier
D. It produces an error because all columns used in the NATURAL join should have a qualifier
Answer: C
QUESTION NO: 77
Which statement is true regarding the UNION operator?
A. By default, the output is not sorted
B. NULL values are not ignored during duplicate checking
C. Names of all columns must be identical across all SELECT statements
D. The number of columns selected in all SELECT statements need to be the same
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 49
ActualTests.com
Answer: B
QUESTION NO: 78
Examine the description of the EMP_DETAILS table given below:
Which two statements are true regarding SQL statements that can be executed on the
EMP_DETAIL table? (Choose two.)
A. An EMP_IMAGE column can be included in the GROUP BY clause
B. An EMP_IMAGE column cannot be included in the ORDER BY clause
C. You cannot add a new column to the table with LONG as the data type
D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column
Answer: B,C
QUESTION NO: 79
See the Exhibit:
You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the
customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the
same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to
have the same value as that of the customer with CUST_ID 2566.
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 50
ActualTests.com
Which UPDATE statement will accomplish the task?
A. UPDATE customers
SET cust_income_level = (SELECT cust_income_level
FROM customers
WHERE cust_id = 2560),
cust_credit_limit = (SELECT cust_credit_limit
FROM customers
WHERE cust_id = 2566)
WHERE cust_id=2360;
B. UPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id = 2560 OR cust_id = 2566)
WHERE cust_id=2360;
C. UPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id IN (2560, 2566)
WHERE cust_id=2360;
D. UPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id = 2560 AND cust_id = 2566)
WHERE cust_id=2360;
Answer: A
QUESTION NO: 80
You created an ORDERS table with the following description:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 51
ActualTests.com
You inserted some rows in the table. After some time, you want to alter the table by creating the
PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?
A. You cannot have two constraints on one column
B. You cannot add a primary key constraint if data exists in the column
C. The primary key constraint can be created only a the time of table creation
D. You can add the primary key constraint even if data exists, provided that there are no duplicate
values
Answer: D
QUESTION NO: 81
See the Exhibit and examine the structure of ORD table:
Evaluate the following SQL statements that are executed in a user session in the specified order:
What would be the outcome of the above statements?
A. All the statements would execute successfully and the ORD_NO column would contain the
value 2 for the CUST_ID 101
B. The CREATE SEQUENCE command would not execute because the minimum value and
maximum value for the sequence have not been specified
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 52
ActualTests.com
C. The CREATE SEQUENCE command would not execute because the starting value of the
sequence and the increment value have not been specified
D. All the statements would execute successfully and the ORD_NO column would have the value
20 for the CUST_ID 101 because the default CACHE value is 20
Answer: A
QUESTION NO: 82
See the Exhibit and examine the structure of the PROMOSTIONS table:
Which SQL statements are valid? (Choose all that apply.)
A. SELECT promo_id, DECODE(NVL(promo_cost,0),promo_cost,
promo_cost*0.25,100) "Discount"
FROM promotions;
B. SELECT promo_id, DECODE(promo_cost,10000,
DECODE(promo_category,'G1',promo_cost*.25,NULL),
NULL) "Catcost"
FROM promotions;
C. SELECT promo_id, DECODE(NULLIF(promo_cost,10000),
NULL,promo_cost*.25,'N/A') "Catcost",
FROM promotions;
D. SELECT promo_id, DECODE(promo_cost,>10000,'High',
<10000,'Low') "Range"
FROM promotions;
Answer: A,B
QUESTION NO: 83
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 53
ActualTests.com
You are currently located in Singapore and have connected to a remote database in Chicago. You
issue the following command:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. An error because the ROUND function specified is invalid
B. An error because the WHERE condition specified is invalid
C. Number of days since the promo started based on the current Chicago data and time
D. Number of days since the promo started based on the current Singapore data and time.
Answer: C
QUESTION NO: 84
See the Exhibit and examine the structure and data in the INVOICE table:
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT AVG(inv_date)
FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id)
FROM invoice;
C. SELECT MAX(AVG(SYSDATE-inv_date))
FROM invoice;
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 54
ActualTests.com
D. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt)
FROM invoice;
Answer: B,D
QUESTION NO: 85
See the Exhibit and examine the structure of the CUSTOMERS table:
Using the CUSTOMERS table, you need to generate a report that shown the average credit limit
for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?
A. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit,cust_city;
B. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;
C. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;
D. SELECT cust_city,AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 55
ActualTests.com
QUESTION NO: 86
Which two statements are true regarding the COUNT function?(Choose two.)
A. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types
B. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL
value in any of the columns
C. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and
NULL value in the CUST_ID column
D. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates
and NULL values in the INV_AMT column
E. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE
clause
Answer: B,D
QUESTION NO: 87
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:
You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A. SELECT custname, grade
FROM customers, grades
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 56
ActualTests.com
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
B. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;
C. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;
D. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
Answer: B,C
QUESTION NO: 88
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
Exhibit1:
Exhibit 2:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 57
ActualTests.com
Exhibit 3:
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 a column used in the NATURAL join cannot have a qualifier
D. It produces an error because all columns used in the NATURAL join should have a qualifier
Answer: C
QUESTION NO: 89
See the Exhibit and examine the structure of the PROMOTiONS table:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 58
ActualTests.com
Using the PROMITIONS table you need to find out the names and cost of all the promos done on
TV and internet that ended in the time interval 15th March '00 to 15th October' 00.
Which two queries would give the required result?(Choose two.)
A. SELECT promo_name, promo_cost
FROM promotions
WHERE promo_category IN ('TV','internet') AND
promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
B. SELECT promo_name, promo_cost
FROM promotions
WHERE promo_category = 'TV' OR promo_category ='internet'AND
promo_end_date >= '15-MAR-00' OR promo_end_date <='15-OCT-00';
C. SELECT promo_name, promo_cost
FROM promotions WHERE (promo_category BETWEEN 'TV' AND'internet') AND
(promo_end_date IN ('15-MAR-00' AND '15-OCT-00');
D. SELECT promo_name, promo_cost
FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND
(promo_end_date >= '15-MAR-00' AND promo_end_date <='15-OCT-00');
Answer: A,D
QUESTION NO: 90
See the Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A. UPDATE promotions
SET promo_cost = promo_cost+100
WHERE TO_CHAR(promo_end_date,'yyyy') > '2000';
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 59
ActualTests.com
B. SELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
C. UPDATE promotions
SET promo_cost = promo_cost+100
WHERE promo_end_date, > TO_DATE(SUBSTR('01-JAN-2000',8));
D. SELECT TO_CHAR(promo_begin_date,'dd/month')
FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
Answer: A,B
QUESTION NO: 91
See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS
tables:
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS
table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table
referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following the CREATE TABLE command:
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be
specified in the column definition
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the
specified columns would be passed to the new table
C. The NEW_SALES table would not get created because the column names in the CREATE
TABLE command and the SELECT clause do not match
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on
the specified columns would be passed to the new table
Answer: B
QUESTION NO: 92
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 60
ActualTests.com
See the Exhibit:
Which statement would display the highest credit limit available in each income level in each city in
the CUSTOMERs table?
A. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level,cust_credit_limit;
B. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level;
C. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_credit_limit , cust_income_level, cust_city
D. SELECT cust_city, cust_income_level,MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city , , cust_income_level ,MAX(cust_credit_limit);
Answer: B
QUESTION NO: 93
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 61
ActualTests.com
you issue the following SQL statement on the CUSTOMERS table to display the customers who
are in the same country as customers with the last name 'king' and whose credit limit is less than
the maximum credit limit in countries that have customers with the last name 'king'.
Which statement is true regarding the outcome of the above query?
A. It executes and shows the required result
B. It produces an error and the < operator should be replaced by < ALL to get the required output
C. It produces an error and the < operator should be replaced by < ANY to get the required output
D. It produces an error and the IN operator should be replaced by = in the WHERE clause of the
main query to get the required output
Answer: A
QUESTION NO: 94
Here is the structure and data of the CUST_TRANS table:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 62
ActualTests.com
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate='01-01-07';
C. SELECT transamt FROM cust_trans WHERE custno> '11';
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
E. SELECT custno + 'A' FROM cust_trans WHERE transmit > 2000;
Answer: A,C,D
QUESTION NO: 95
See the Exhibit and Examine the structure of SALES and PROMOTIONS tables:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 63
ActualTests.comYou want to delete rows from the SALES table, where the PROMO_NAME column in the
PROMOTIONS table has either blowout sale or everyday low price as values. Which DELETE
statements are valid? (Choose all that apply.)
A. DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'blowout sale')
AND promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'everyday low price');
B. DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'blowout sale')
OR promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = 'everyday low price');
C. DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name = 'blowout sale')
OR promo_name = 'everyday low price');
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 64
ActualTests.com
D. DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name IN ('blowout sale','everyday low price'));
Answer: B,C,D
QUESTION NO: 96
Which three statements/commands would cause a transaction to end? (Choose three.)
A. COMMIT
B. SELECT
C. CREATE
D. ROLLBACK
E. SAVEPOINT
Answer: A,C,D
QUESTION NO: 97
See the Exhibit:
Examine the structure of CUSTOMERS table:
Evaluate the following SQL statement:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 65
ActualTests.com
Which statement is true regarding the outcome of the above query?
A. It executes successfully
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause
C. It returns an error because WHERE and HAVING clause cannot be used in the same SELECT
statement
D. It returns an error because WHERE and HAVING clause cannot be used to apply conditions on
the same column
Answer: A
QUESTION NO: 98
Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values
B. Reversing the order of the intersected tables the result
C. The names of columns in all SELECT statements must be identical
D. The number of columns and data types must be identical for all SELECT statements in the
query
Answer: D
QUESTION NO: 99
Which statement is true regarding synonyms?
A. Synonyms can be created only for a table
B. Synonyms are used to reference only those tables that are owned by another user
C. A public synonym and a private synonym can exist with the same name for the same table
D. The DROP SYNONYM statement removes the synonym and the table on which the synonym
has been created becomes invalid
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 66
ActualTests.com
QUESTION NO: 100
See the Exhibit and Examine the structure of the CUSTOMERS table:
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit
limit by 15% for all customers. Customers whose credit limit has not been entered should have the
message "Not Available" displayed.
Which SQL statement would produce the required result?
A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT"
FROM customers;
B. SELECT NVL(cust_credit_limit*.15,'Not Available')"NEW CREDIT"
FROM customers;
C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT"
FROM customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT"
FROM customers;
Answer: D
QUESTION NO: 101
See the Exhibit and examine the structure of the CUSTOMERS table:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 67
ActualTests.com
Using the CUSTOMERS table, you need to generate a report that shown the average credit limit
for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?
A. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit,cust_city;
B. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;
C. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;
D. SELECT cust_city,AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
Answer: C
QUESTION NO: 102
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 68
ActualTests.comand examine the structure of CUSTOMRS AND SALES tables:
Evaluate the following SQL statement:
Which statement is true regarding the execution of the above UPDATE statement?
A. It would not execute because two tables cannot be used in a single UPDATE statement
B. It would not execute because the SELECT statement cannot be used in place of the table name
C. It would execute and restrict modifications to only the column specified in the SELECT
statement
D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE
statement
Answer: C
QUESTION NO: 103
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 69
ActualTests.com
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55,'$0G000D00')
FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99')
FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$9,999D99')
FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00')
FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
Answer: A,D,E
QUESTION NO: 104
Where can subqueries be used? (Choose all that apply)
A. Field names in the SELECT statement
B. The FROM clause in the SELECT statement
C. The HAVING clause in the SELECT statement
D. The GROUP BY clause in the SELECT statement
E. The WHERE clause in only the SELECT statement
F. The WHERE clause in the SELECT as well as all DML statements
Answer: A,B,C,F
QUESTION NO: 105
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the
ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query
successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database
administrator
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 70
ActualTests.com
D. CREATE PUBLIC SYNONY ord FOR oe.orders; This command is issued by the database
administrator
Answer: D
QUESTION NO: 106
You need to display the date 11-Oct-2007 in words as 'Eleventh of October, Two Thousand
Seven. Which SQL statement would give the required result?
A. SELECT TO_CHAR('11-oct-2007','fmDspth "of" Month, Year')
FROM DUAL;
B. SELECT TO_CHAR(TO_DATE('11-oct-2007'),'fmDspth of Month, Year')
FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('11-oct-2007'),'fmDspth "of" Month, Year')
FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDspth "of" Month, Year'))
FROM DUAL;
Answer: C
QUESTION NO: 107
You issue the following command to drop the PRODUCTS table:
SQL>DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data along with the table structure is deleted
B. The pending transaction in the session is committed
C. All indexes on the table will remain but they are invalidated
D. All view and synonyms will remain but they are invalidated
E. All data in the table are deleted but the table structure will remain
Answer: A,B,D
QUESTION NO: 108
See the Exhibit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 71
ActualTests.com
Examine the structure of PRODUCTS table.
Using the PRODUCTS table, you issue the following query to generate the names, current list
price and discounted list price for all those products whose list price fails below $10 after a
discount of 25% is applied on it.
The query generates an error.
What is the reason of generating error?
A. The parenthesis should be added to enclose the entire expression
B. The double quotation marks should be removed from the column alias
C. The column alias should be replaced with the expression in the WHERE clause
D. The column alias should be put in uppercase and enclosed within double quotation marks in the
WHERE clause
Answer: D
QUESTION NO: 109
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 72
ActualTests.comYou need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
B. SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval and endval;
C. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit= (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval and endval;
D. SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval and endval;
Answer: B,C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 73
ActualTests.com
QUESTION NO: 110
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
Exhibit1:
Exhibit 2:
Exhibit 3:
You issue the following query:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 74
ActualTests.com
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 a column used in the NATURAL join cannot have a qualifier
D. It produces an error because all columns used in the NATURAL join should have a qualifier
Answer: C
QUESTION NO: 111
Evaluate the following SQL statements:
Which ORDER BY clauses are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
Answer: A,C,E
Explanation:
__________________________________________________________________
QUESTION NO: 112
View the Exhibit and examine the description for the PRODUCTS and SALES table. PROD_ID is
a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all
the rows from the PRODUCTS table for which no sale was done for the last three years. Which is
the valid DELETE statement?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 75
ActualTests.com
A. DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id - 3*365 = SYSDATE );
B. DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE SYSDATE >= time_id - 3*365 );
C. DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE SYSDATE - 3*365 >= time_id);
D. DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE - 3*365 );
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 76
ActualTests.com
QUESTION NO: 113
You need to display the first names of all customers from the CUSTOMERS table that contain the
character 'e' and have the character 'a' in the second last position. Which query would give the
required output?
A. SELECT custfirstname
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name. -2, 1)=a;
B. SELECT custfirstname FROM customers
WHERE INSTR(cust_first_name. 'e')oM AND SUBSTR(cust_first_name. -2, 1'a';
C. SELECT custfirstname FROM customers
WHERE INSTR(cust_first_name. 'e')IS NOT NULL AND SUBSTR(cust_first_name. 1 .-2)='a';
D. SELECT custfirstname FROM customers
WHERE DSTSTR(cust_first_name, 'e')^0 AND SUBSTR(cust_fu-st_name. LENGTHCcus^fiisMiame).-
2)='a';
Answer: A
QUESTION NO: 114
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query
statement:
SQL> SELECT cust_last_name. cust_city3 cust_credit_liinit
FROM customers
WHERE cust_last_name BETWEEN 'A' AND 'C AND cust_credit_limit BETWEEN
1000 AND 3000:
What would be the outcome of the above statement?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 77
ActualTests.com
A. It executes successfully.
B. It produces an error because the condition on CUST_LAST_NAME is invalid.
C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null
values.
D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN
clauses.
Answer: A
QUESTION NO: 115
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit
amount in each income level. The report should NOT show any repeated credit amounts in each
income level. Which query would give the required result?
A. SELECT cust_income_level. DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
B. SELECT DISTINCT cust_income_level. DISTINCT cust_credit_limit * 0.50 AS "50% Credit
Limit"
FROM customers:
C. SELECT DISTINCT cust_income_level || " || cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers:
D. SELECT cust_income_level ||' '|| cust_credit_limit * 0.50 AS "50% Credit Limit" FROM
customers:
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 78
ActualTests.com
QUESTION NO: 116
View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements
are valid? (Choose all that apply.)
A. SELECT promo_id. DECODE(NYL(promo_cost.O). promo_cost. promo_cost * 0.25. 100)
"Discount"
FROM promotions;
B. SELECT promo id. DECODE(promo cost. 10000.
DECODE(promo_category. 'Gl\ promo_cosr * 25. NULL). NULL) "Catcost" FROM promotions;
C. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25, *N/A')
"Catcost"
FROM promotions;
D. SELECT promo_id. DECODE(promo_cost. >10000. 'High'. <10000. 'Low') "Range"FROM
promotions;
Answer: A,B
QUESTION NO: 117
Evaluate the following SQL statement:
SQL> SELECT cust_id. cust_last_name FROM customers
WHERE cust_credit_limit IN
(select cust_credit_limit
FROM customers
WHERE cust_city='Srngapore'):
W^iich statement is true regarding the above query if one of the values generated by the
subquery is NULL
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 79
ActualTests.com
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the subquery.
Answer: C
QUESTION NO: 118
View the Exliibit and examine the structure of the PRODUCTS table. You need to generate a
report in the following format:
CATEGORIES
5MP Digital Photo Camera's category is Photo
Y Box's categoiy is Electronics
Envoy Ambassador's categoiy is Hardware
Which two queries would give the required output? (Choose two.)
A. SELECT prod_name || q'"s categoiy is' "| prod_categoiy CATEGORIES FROM products:
B. SELECT prodrome | q'['s ]'category is ' || prod_category CATEGORIES FROM products:
C. SELECT prod_name || qVsV ||' categoiy is' |, prod_categoiy CATEGORIES FROM products:
D. SELECT prod_name || q'<*s >' || 'categoiy is ' || prod_categoiy CATEGORIES FROM products:
Answer: C,D
QUESTION NO: 119
The PART_CODE column in the SPARES table contains the following list of values:
PART CODE
A%_WQ123 A%BWQ123 AB_WQ123
E\*aluate the following query:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 80
ActualTests.com
SQL> SELECT part_code FROM spares
WHERE pait_code LIKE '%\%_WQ12%' ESCAPE V;
Which statement is true regarding the outcome of the above query?
A. It produces an error.
B. It displays all values.
C. It displays only the values A%_WQ123 and AB_WQ123.
D. It displays only the values A%_WQ123 and A%BWQ123.
E. It displays only the values A96BWQ123 and AB_WQ123.
Answer: D
QUESTION NO: 120
View the Exliibit and examine the structure of the PROMOTIONS table. Evaluate the following
SQL statement:
SQL>SELECT promo_categoiy. AVG(promo_cost) Avg_Cost. AVG(promo_cost)*.25
Ave Overhead
FROM promotions
WHERE UPPER(promo_categoiy) IN ("TV*. 'INTERNET/POST') GROUP BY
Avg_Cost ORDER BY
Avg_Overhead: The above query generates an error on execution. Which clause in the above
SQL statement causes the error?
A. WHERE
B. SELECT
C. GROUP BY
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 81
ActualTests.com
D. ORDER BY
Answer: C
QUESTION NO: 121
Which statement is true regarding subqueries?
A. The LIKE operator cannot be used with single-row subqueries.
B. The NOT IN operator is equivalent to IS NULL with single-row subqueries.
C. =ANY and =ALL operators have the same functionality in multiple-row subqueries.
D. The NOT operator can be used with IN, ANY, and ALL operators in multiple-row subqueries.
Answer: D
QUESTION NO: 122
View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS
tables. You need to generate a report that gives details of the customer's last name, name of the
product, and the quantity sold for all customers in Tokyo'. Which two queries give the required
result? (Choose two.)
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 82
ActualTests.com
A. SELECT c.cust_last_name.p.prod_naine, s.quanrity_sold FROM sales s JOIN products p
USING(prod_id)
JOIN customers c
USEs'G(cust_id)
WHERE cxust_city=Tokyo';
B. SELECT c.cust_last_name. p.prod_name. s.quantity_sold FROM products p JOIN sales s JOIN
customers c ON(p.prod_id=s .prod_id)
ON(s. cust_id=c .cust_id) WHERE cxu$t_city=Tokyo';
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 83
ActualTests.com
C. SELECT c.cust_last_name. p.prodname, s.quantity_sold FROM products p JOIN sales s
ON(p.prod_id=s .prod_id) JOIN customers c ON(s. cust_id=c .cust_id) AND c.cust_city=Tokyo':
D. SELECT cxust_id.c.cust_last_name.p.prod_id. p.prod_name. s.quantity_sold FROM products p
JOIN sales s
USEs'G(prod_id)
JOIN customers c
USEs'G(cust_id)
WHERE cxust_city=Tokyo';
Answer: A,C
QUESTION NO: 123
View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would
display the highest credit limit available in each income level in each city in the CUSTOMERS
table?
A. SELECT cust_city. cust_income_level. MAX(cust_credit_limit) FROM customers GROUP BY
cust_city, cust_income_level. cusr_credit_liiiiit:
B. SELECT cust_city. cust_iicome_level. MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level;
C. SELECT cust_city. cust_income_level. MAX(cust_credit_limit) FROM customers GROUP BY
cust_credit_limit. cust_income_level. cusr_city:
D. SELECT cust_ciry. cust_income_level. MAX(cust_credit_limit) FROM customers GROUP BY
cust_city, cust_income_level, MAX (cust_credir_limit):
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 84
ActualTests.com
Answer: B
QUESTION NO: 124
Evaluate the following SQL statement:
SQL> SELECT cust_id. cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO. cust_last_name
FROM customers
WHERE country id = 30:
Which ORDER BY clause are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUSTNO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"
Answer: A,C,E
QUESTION NO: 125
Which is the valid CREATE [TABLE statement?
A. CREATE TABLE emp9S# (emp_no NUMBER(4)):
B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
C. CREATE TABLE emp*123 (emp_no NUMBER(4)):
D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);
Answer: A
QUESTION NO: 126
View the Exhibit to examine the description for the SALES table.Which views can have all DML
operations performed on it? (Choose all that apply.)
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 85
ActualTests.com
A. CREATE MEW v3
AS SELECT * FROM SALES "WHERE cust_id = 2034 WITH CHECK OPTION:
B. CREATE MEW vl
AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION:
C. CREATE MEW v2
AS SELECT prod_id. cust_icL time_id FROM SALES WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION:
D. CREATE MEWv4
AS SELECT prod_id. cust_icL SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE
- 2*365 GROUP BYprod_id. cust_id WITH CHECK OPTION:
Answer: A,B
QUESTION NO: 127
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only
one customer with the cus_last_name column having value Roberts. Which INSERT statement
should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME
is Roberts and CREDIT_LIMIT is 600?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 86
ActualTests.comA. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customerid
FROM customers
WHERE cust_last_iiame='Roberts' AND
credit_limit=600). 1000);
B. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id
FROM customers
WHERE cust_last_iiame='Roberts' AND
credit_limit=600).order_total)
VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000):
C. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id. o.ordertotal
FROM orders o. customers c
WHERE o.customer_id = c.customerid
AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600)
VALUES (L'10-mar-2007\ 'direct'.( SELECT customer_id
FROM customers
WHERE cust_last_iiame='Roberts' AND
credit_limit=600). 1000);
D. INSERT INTO orders (order_id.order_date.order_mode.
(SELECT customer_id
FROM customers
WHERE cust_last_iiame='Roberts' AND
credit_limit=600).order_total)
VALUES(l.'10-mar-2007\ 'direct'. &customer_id. 1000):
Answer: A
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 87
ActualTests.com
QUESTION NO: 128
View the Exhibit and examine the structure of the SALES. CUSTOMERS.
PRODUCTS, and TIMES tables. The PROD_ID column is the foreign key in the SALES table,
which references the PRODUCTS table. Similarly, the CUST ID and
TIME ID columns are also foreign keys in the SALES table referencing the
CUSTOMERS and TIMES tables. respectively.Evaluate the following CREATE
TABLE command:
CREATE TABLE new_sales(prod_id. cust_id. order_date DEFAULT SYSDATE)
AS SELECT prod_id. custid. time_id
FROM sales;
Winch statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be
specified in the column definition.
B. The NEW SALES table would set created and all the NOT NULL constraints defined on the
specified columns would be passed to the new table.
C. The NEW_SALES table would not set created because the column names in the CREATE
TABLE command and the SELECT clause do not match.
D. The NEW SALES table would set created and all the FOREIGN KEY constraints defined on the
specified columns would be passed to the new table.
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 88
ActualTests.com
Answer: B
QUESTION NO: 129
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL
statement:
SQL> SELECT cust_city. COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000:
Which statement is true regarding the outcome of the above query?
A. It executes successfully.
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT
statement.
D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions
on the same column.
Answer: A
QUESTION NO: 130
The following data exists in the PRODUCTS table:
PROD ID PROD LIST PRICE
123456 152525.99
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 89
ActualTests.com
You issue the following query:
SQL> SELECT RPAD((ROUND(prod_list_price)). lO,'*1)
FROM products
WHERE prod_id = 123456:
What would be the outcome?
A. 152526****
B. **152525.99
C. 152525**
D. an error message
Answer: A
QUESTION NO: 131
View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two
SQL statements:
Statement 1
SQL>SELECT promo_categoiy.SUM(promo_cost) FROM promotions
WHERE promo_end_date-promo_begin_date > 30
GROUP BY promo_category: Statement 2
SQL>SELECT promo_categoiy.siun(promo_cost) FROM promotions
GROUP BY promo_categpry
HA\TNGMTN(promo_end_date-promo_begin_date)>30:
Which statement is true regarding the above two SQL statements?
A. statement 1 gives an error, statement 2 executes successfully
B. statement 2 gives an error, statement 1 executes successfully
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 90
ActualTests.com
C. statement 1 and statement 2 execute successfully and give the same output
D. statement 1 and statement 2 execute successfully and give a different output
Answer: D
QUESTION NO: 132
Examine the structure and data of the CUSTJTRANS table:
CUSTJRANS
Name Null? Type
CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO
TRANSDATE TRANSAMT
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three
SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM custjrans;
B. SELECT * FROM custjrans WHERE transdate = '01-01-07':
C. SELECT transamt FROM custjrans WHERE custno > '11':
D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07':
E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000:
Answer: A,C,D
QUESTION NO: 133
View the Exhibit and examine the data in the COSTS table. You need to generate a report that
displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the
unit cost. The details should be displayed in the descending order of 25% of the unit cost. You
issue the following query:
SQL>SELECT prod_id
FROM costs
WHERE unit_price >= unit_cost * 1.25
ORDER BY unit_cost * 0.25 DESC:
Which statement is true regarding the above query?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 91
ActualTests.com
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the
ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified
in the SELECT clause.
Answer: A
QUESTION NO: 134
Which two statements are true regarding subqueries? (Choose two.)
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appeal* on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT
statement.
Answer: A,D
QUESTION NO: 135
View the Exhibit and examine the data in the PRODUCTS table.You need to display product
names from the PRODUCTS table that belong to the 'Software/Other1 category with minimum
prices as either $2000 or $4000 and no unit of measure. You issue thej
following query:
SQL>SELECT prod_name. prod_category. prod_min_price
FROM products
WHERE prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR
prod_min_price = 4000) AND prod_unit_of_measure o";
Which statement is true regarding the above query?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 92
ActualTests.com
A. It executes successfully but returns no result.
B. It executes successfully and returns the required result.
C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not
valid.
D. It generates an error because the condition specified for the PROD_CATEGORY column is not
valid.
Answer: A
QUESTION NO: 136
You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the
default format of dd-mon-rr.Which two SQL statements would give the required output? (Choose
two.)
A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:
B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:
D. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:
E. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:
Answer: B,C
QUESTION NO: 137
You need to generate a list of all customer last names with their credit limits from the
CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list.
Winch two queries would achieve the required result? (Choose two.)
A. SELECT cust_last_name. cust_credit_limit FROM customers
ORDER BY cust_credit_limit DESC:
B. SELECT cust_last_name. cust_credit_limit FROM customers
ORDER BY cust_credit_limit:
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 93
ActualTests.com
C. SELECT cust_last_name. cust_credit_limit FROM customers
ORDER BY cust_credit_limit NULLS LAST:
D. SELECT cust_last_name. cust_credit_limit FROM customers
ORDER BY cust_last_name. cust_credit_limit NULLS LAST:
Answer: B,C
QUESTION NO: 138
Examine the structure and data in the PRIC E_LIST table: Name Null? Type
------------ ------------- -------------------
PRODJD NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD ID PROD PRICE
----------- ------------------------
100 $234.55
101 $6,509.75
102 $1,234
in the same format as the PROD_PRICE. Which SQL statement would give the required result?
A. SELECT TO_CHAR(prod_price* .25.'$99.999.99') FROM PRICEJLIST:
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.999.99')* .25.'$99.999.00') FROM
PRICE_LIST:
D. SELECT TO_NUMBER(TO_NUMBER(prod_price.,$99.999.99')* .25/$99.999.00') FROM
PRICE_LIST:
Answer: C
QUESTION NO: 139
View the Exhibit for the structure of the STUDENT and FACULTY tables .You need to display the
faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
5QL>SELECTfaculty_name.COUNT(smdent_id)
FROM student JOIN faculty USING (faculty_id. location_id)
3ROUP BY faculty_name:
Statement 2
5QL>SELECTfaculty_name.COUNT(smdent_id)
FROM student NATURAL JOIN faculty
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 94
ActualTests.com
3ROUP BY faculty_name:
Which statement is true regarding the outcome?
A. Only statement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.
Answer: D
QUESTION NO: 140
Which statement is true regarding the COALESCE function?
A. It can have a maximum of five expressions in a list.
B. It returns the highest NOT NULL value in the list for all rows.
C. It requires that all expressions in the list must be of the same data type.
D. It requires that at least one of the expressions in the list must have a NOT NULL value.
Answer: C
QUESTION NO: 141
View the Exhibit and examine the structure of ORD and ORDJTEMS tables. The ORD_NO column
is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite
PRIMARY KEY in the ORDJTEMS table. Which two CREATE INDEX statements are valid?
(Choose two.)
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 95
ActualTests.com
A. CREATE INDEX ordjdxl ON ord(ord_no):
B. CREATE INDEX ord_idx2 ON ord_irems(ord_no);
C. CREATE INDEX ord_idx3 ON ord_items(item no);
D. CREATE INDEX ord_idx4
ON ord.ord_itenis(ord_DD, ord_date.qry):
Answer: B,C
QUESTION NO: 142
View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS
table, you need to find out the names and cost of all the promos done on 'TV1 and 'internet' that
ended in the time interval 15th March '00 to 15th October '00.Which two queries would give the
required result? (Choose two.)
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 96
ActualTests.com
A. SELECT promoname, promocosl
FROM promotions
WHERE promo_category IN ('TV7', 'internet') AND
promo_end_date BETWEEN '15-MAR-OO' AND '15-OCT-00':
B. SELECT promo_name. promo_cost FROM promotions
WHERE promo_categoiy = TV OR promo_category ='inteniet' AND promo_end_date >='15-MAR-
00' OR promo_end_date <='15-OCT-00':
C. SELECT promo_name. promo_cost FROM promotions
WHERE (promo_categoiy BETWEEN TV AND 'internet') AND (promo_end_date IN ('15-MARO0'.'
15-OCT-O0'));
D. SELECT promo_name. promo_cost
FROM promotions WHERE (promo_category = TV OR promo_category ='inteniet')
AND (promo_end_date >='l 5-MAR-00' AKD promo_end_date <='l 5-OCT-00'):
Answer: A,D
QUESTION NO: 143
You need to display the date ll-oct-2007 in words as "Eleventh of October, Two Thousand Seven'.
Which SQL statement would give the required result?
A. SELECT TO_CHAR('ll-oct-2007'. 'miDdspth "of Month. Year') FROM DUAL:
B. SELECT TO_CHAR(TO_DATE('ll-oct-2007'X 'miDdspth of month, year') FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('ll-oct-2007'). 'miDdthsp "of* Month. Year') FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('ll-oct-20077fiiiDdspth "of" Month. Year')) FROM DUAL:
Answer: C
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 97
ActualTests.com
QUESTION NO: 144
View the Exhibit and examine the structure of the PRODUCTS table. All products have a list price.
You issue the following command to display the total price of each product after a discount of 25%
and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.
SQL>SELECT prod_name. prod_list_price -
(prod_list_jmce*(25/100)^
AS "TOTAL PRICE"
FROM products:
What would be the outcome if all the parentheses are removed from the above statement?
A. It produces a syntax error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
Answer: B
QUESTION NO: 145
View the Exhibit and examine the structure of the CUSTOMERS table. Using the CUSTOMERS
table, you need to generate a report that shows an increase in the credit limit by 15% for all
customers. Customers whose credit limit has not been entered should have the message "Not
Available" displayed. Which SQL statement would produce the required result?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 98
ActualTests.comA. SELECT N\T,(cusr_credir_liniir.'Nor Available*)*. 15 "NEW CREDIT" FROM customers;
B. SELECT NVL(cust_credit_limit*. 15."Not Available') "NEW CREDIT" FROM customers:
C. SELECT TO_CHAR(NVL(cust_credit_liniit*. 15."Not Available')) "NEW CREDIT" FROM
customers:
D. SELECT N\"L(TO_CHAR(cust_credit_limit*. 15).*Not Available') "NEW CREDIT" FROM
customers:
Answer: D
QUESTION NO: 146
Evaluate the following query:
SQL> SELECT promo_name |l q'{'s start date was }' || promo_begin_date
AS "Promotion Launches" FROM promotions;
What would be the outcome of the above query?
A. It produces an error because flower braces have been used.
B. It produces an error because the data types are not matching.
C. It executes successfully and introduces an 's at the end of each promo_name in the output.
D. It executes successfully and displays the literal" {'s start date was} " for each row in the output.
Answer: C
QUESTION NO: 147
View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS
table, you need to display the names of all promos done after January 1, 2001, starting with the
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 99
ActualTests.com
latest promo. Which query would give the required result? (Choose all that apply.)
A. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
B. SELECT promo_name. promo_begiii_date FROM promotions
WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:
C. SELECT promo_name. promo_begin_date FROM promotions
WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:
D. SELECT promo_name, promo_begin_date "START DATE" FROM promotions
WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
Answer: A,D
QUESTION NO: 148
Evaluate the following SQL commands:
SQL>CREATE SEQUENC E ord_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE:
SQL>CREATE TABLE ord_items
(ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL.
item_no NUMBER(3). qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200).
expiry_date date CHECK (expiry_date > SYSDATE).
CONSTRAINT it_pk PRIMARY KEY (ord_no.item_no).
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no)):
The command to create a table fails. Identify the reason for the SQL statement failure?
(Choose all that apply.)
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 100
ActualTests.com
A. You cannot use SYSDATE in the condition of a CHECK constraint.
B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD
NO is also the FOREIGN KEY.
Answer: A,C
QUESTION NO: 149
Which arithmetic operations can be performed on a column by using a SQL function that is built
into Oracle database? (Choose three.)
A. addition
B. subtraction
C. raising to a power
D. finding the quotient
E. finding the lowest value
Answer: A,C,E
QUESTION NO: 150
View the Exhibit and examine the structure of the PROMOTIONS table. You need to generate a
report of all promos from the PROMOTIONS table based on the following conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 101
ActualTests.comA. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND
promo_cost > 20000 AND promo_end_date > '1-JAN-01'
B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR
promo_cost > 20000 OR promo_end_date > '1-JAN-01'
C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost >
20000 AND promo_end_date > '1-JAN-01'
D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%')
AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')
Answer: C
QUESTION NO: 151
View the Exhibit and examine the structure of the SALES table.The following query is written to
retrieve all those product IDs from the SALES table that have more than
55000 sold and have been ordered more than 10 times.
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>10
GROUP BY prod_id HAVING COUNT(*)>10;
Which statement is true regarding this SQL statement?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 102
ActualTests.com
A. It executes successfully and generates the required result.
B. It produces an error because COUNT(*) should be specified in the SELECT clause also.
C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the
WHERE clause.
D. It executes successfully but produces no result because COUNT(prod_id) should be used
instead of COUNT(*).
Answer: C
QUESTION NO: 152
You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value 'ACTIVE' if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the
employee
Which is the correct syntax to create this table?
A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB(200));
B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB);
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 103
ActualTests.com
C. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT "ACTIVE",
resume CLOB);
D. CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB);
Answer: D
QUESTION NO: 153
You need to extract details of those products in the SALES table where the PROD_ID column
contains the string '_D123'. Which WHERE clause could be used in the
SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
Answer: B
QUESTION NO: 154
Which three statements are true about multiple-row subqueries? (Choose three.)
A. They can contain a subquery within a subquery.
B. They can return multiple columns as well as rows.
C. They cannot contain a subquery within a subquery.
D. They can return only one column but multiple rows.
E. They can contain group functions and GROUP BY and HAVING clauses.
F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
Answer: A,B,E
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 104
ActualTests.com
QUESTION NO: 155
Which two statements are true regarding the ORDER BY clause? (Choose two.)
A. It is executed first in the query execution.
B. It must be the last clause in the SELECT statement.
C. It cannot be used in a SELECT statement containing a HAVING clause.
D. You cannot specify a column name followed by an expression in this clause.
E. You can specify a combination of numeric positions and column names in this clause.
Answer: B,E
QUESTION NO: 156
Which two statements are true regarding the USING clause in table joins?(Choose two.)
A. It can be used to join a maximum of three tables.
B. It can be used to restrict the number of columns used in a NATURAL join.
C. It can be used to access data from tables through equijoins as well as nonequijoins.
D. It can be used to join tables that have columns with the same name and compatible data types.
Answer: B,D
QUESTION NO: 157
View the Exhibit and examine the structure of the CUSTOMERS table. You have been asked to
produce a report on the CUSTOMERS table showing the customers details sorted in descending
order of the city and in the descending order of their income level in each city. Which query would
accomplish this task?
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 105
ActualTests.comA. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city desc, cust_income_level DESC;
B. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_income_level desc, cust_city DESC;
C. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY (cust_city, cust_income_level) DESC;
D. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city, cust_income_level DESC;
Answer: A
QUESTION NO: 158
View the Exhibit and examine the structure of the PROMOTIONS, SALES, and
CUSTOMER tables.You need to generate a report showing the promo name along with the
customer name for all products that were sold during their promo campaign and before 30th
October 2007.You issue the following query:
SQL> SELECT promo_name,cust_name
FROM promotions p JOIN sales s
ON(time_id BETWEEN promo_begin_date AND promo_end_date)
JOIN customer c
ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';
Oracle 1z0-051: Practice Exam
"Pass Any Exam. Any Time." - www.actualtests.com 106
ActualTests.com
Which statement is true regarding the above query?
A. It executes successfully and gives the required result.
B. It executes successfully but does not give the required result.
C. It produces an error because the join order of the tables is incorrect.
D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same
SELECT statement.
Answer: B