Exam Name Oracle Database SQL Fundamentals(114 questions)

Exam Name Oracle Database SQL Fundamentals

Preview document (4 of 59 pages)

Exam Name:
Oracle Database: SQL Fundamentals
Exam Type:
Oracle
Exam Code:
1Z0-051
Total Questions
114
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;
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.)
Page 1 of 59
Exam Name:
Oracle Database: SQL Fundamentals
Exam Type:
Oracle
Exam Code:
1Z0-051
Total Questions
114
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
SELECT prod_id FROM costs;
Which statement is true regarding the above compound query?
Page 2 of 59
Exam Name:
Oracle Database: SQL Fundamentals
Exam Type:
Oracle
Exam Code:
1Z0-051
Total Questions
114
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
FROM customers
WHERE cust_last_name = 'Roberts' AND
credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?
Page 3 of 59
Exam Name:
Oracle Database: SQL Fundamentals
Exam Type:
Oracle
Exam Code:
1Z0-051
Total Questions
114

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
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
Page 4 of 59