1Z0-051

Oracle Database: SQL Fundamentals

Exam: 1Z0-051
Demo Edition

1 http://www.hotcerts.com
 1Z0-051

QUESTION: 1
Which statement is true regarding the INTERSECT operator?

A. It ignores NULL values.

B. Reversing the order of the intersected tables alters 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: 2
Which three statements are true regarding the data types in Oracle Database 0g/11g?
(Choose three.)

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 blank-padded to the maximum defined
column width.

Answer: A, D, E

QUESTION: 3
Examine the structure of the PROGRAMS table:
Name Null? Type
---------- ------------- ---------------
PROG_ID NOT NULL NUMBER(3)
PROG_COST NUMBER(8,2)
START_DATE NOT NULL DATE
END_DATE DATE
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;

2 http://www.hotcerts.com
 1Z0-051

D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')
FROM programs;

Answer: A, D

QUESTION: 4
View the Exhibit and examine the structure of the PROMOTIONS table. 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-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: 5
View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS
tables. Evaluate the following SQL statement:
SQL>SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales
MINUS

3 http://www.hotcerts.com
 1Z0-051

SELECT prod_id FROM costs;

Which statement is true regarding the above compound query?

A. It produces 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

QUESTION: 6
View the Exhibit and examine the structure of CUSTOMERS and SALES tables.
Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id
FROM sales)
SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id

4 http://www.hotcerts.com
 1Z0-051

FROM customers
WHERE cust_last_name = 'Roberts' AND
credit_limit = 600);
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 columns 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

5 http://www.hotcerts.com
 1Z0-051

QUESTION: 7
You are currently located in Singapore and have connected to a remote database in
Chicago. You issue the following command:
SQL> SELECT ROUND(SYSDATE-promo_begin_date,0)
FROM promotions
WHERE (SYSDATE-promo_begin_date)/365 > 2;
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 date and time
D. number of days since the promo started based on the current Singapore date and time

Answer: C

QUESTION: 8
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 cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, -2, 1)='a';
B. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' AND
SUBSTR(cust_first_name, -2, 1)='a';
C. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
SUBSTR(cust_first_name, 1,-2)='a';
D. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a';

Answer: A

QUESTION: 9

6 http://www.hotcerts.com
 1Z0-051

Evaluate the following query:

SQL> SELECT TRUNC(ROUND(156.00,-1),-1) FROM DUAL;
What would be the outcome?

A. 16
B. 100
C. 160
D. 200
E. 150

Answer: C

QUESTION: 10
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 the COUNT function with a DISTINCT keyword cannot
have a WHERE clause.

Answer: B, D

QUESTION: 11
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.
Evaluate the following SQL statement:
SQL>SELECT p.promo_id, p.promo_name, s.prod_id
FROM sales s RIGHT OUTER JOIN promotions p
ON (s.promo_id = p.promo_id);
Which statement is true regarding the output of the above query?

7 http://www.hotcerts.com
 1Z0-051

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: 12
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the
query statement:
SQL> SELECT cust_last_name, cust_city, cust_credit_limit
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?

8 http://www.hotcerts.com
 1Z0-051

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: 13
Which two statements are true regarding the USING and ON clauses in table joins?
(Choose two.)

A. Both USING and ON clauses 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
statements containing the ON or the USING clause.

Answer: C, D

QUESTION: 14
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

9 http://www.hotcerts.com
 1Z0-051

D. the GROUP BY clause in the SELECT statement

E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements

Answer: A, B, C, F

QUESTION: 15
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

QUESTION: 16
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 not be the same.

Answer: B

QUESTION: 17
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.

10 http://www.hotcerts.com
 1Z0-051

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

QUESTION: 18
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.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the
database administrator.

Answer: D

QUESTION: 19
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(NVL(promo_cost,0), promo_cost,

promo_cost * 0.25, 100) "Discount"
FROM promotions;
B. SELECT promo_id, DECODE(promo_cost, 10000,

11 http://www.hotcerts.com
 1Z0-051

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: 20
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 ='Singapore');
Which statement is true regarding the above query if one of the values generated by the
subquery is NULL?

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

12 http://www.hotcerts.com