Oracle 1Z0-051 Certification Sample Questions and Answers

To make you familiar with Oracle Database 11g - SQL Fundamentals I (1Z0-051) certification exam structure, we have prepared this sample question set. We suggest you to try our Demo Oracle OCA 1Z0-051 Certification Practice Exam to test your understanding of Oracle 1Z0-051 concepts with real Oracle certification exam environment.

If you are planning to write the Oracle OCA 1Z0-051 certification exam (Oracle Database 11g - SQL Fundamentals I). We strongly recommend you to use our Premium Oracle 1Z0-051 Certification Practice Exam to clear your certification exam with good score.

1Z0-051 Sample Questions:

01) Examine the structures of the PATIENT, PHYSICIAN, and ADMISSION tables.
Which SQL statement will produce a list of all patients who have more than one physician?
a) SELECT p.patient_id
FROM patient p
WHERE p.patient_id IN (SELECT patient_id
FROM admission
GROUP BY patient_id
HAVING COUNT(*) > 1);
b) SELECT DISTINCT a.patient_id
FROM admission a, admission a2
WHERE a.patient_id = a2.patient_id
AND a.physician_id <> a2.physician_id;
c) SELECT patient_id
FROM admission
WHERE COUNT(physician_id) > 1;
d) SELECT patient_id
FROM patient FULL OUTER JOIN physician;
 
02) The transaction table contains these columns:
SQL> DESCRIBE transaction
TRANSACTION_ID NUMBER(9)
TRANS_CODE VARCHAR2(5)
CUST_ACCOUNT VARCHAR2(12)
A new standard was adopted in your department affecting reports produced by querying the transaction table. When creating reports, a dash (-) followed by the three characters 'ANI' must be appended to all transaction codes that contain only three characters. Any leading 'W' in a transaction code must be removed from the resulting data display.
Which query will return the desired results?
a) SELECT TRIM('W' (RPAD(trans_code, 7, '-ANI')))FROM transaction WHERE LENGTH(trans_code) = 3;
b) SELECT TRIM('W' FROM (RPAD(trans_code, 3, '-ANI')))FROM transaction WHERE LENGTH(trans_code) = 3;
c) SELECT TRIM(LEADING 'W' FROM (RPAD(trans_code, 7, '-ANI')))FROM transaction WHERE LENGTH(trans_code) = 3;
d) SELECT TRIM(LEADING 'W' FROM (RPAD(trans_code, 3, '-ANI')))FROM transaction WHERE LENGTH(trans_code) = 3;
 
03) The current date is January 1, 2009. You need to store this date value:
19-OCT-99
Which statement about the date format for this value is TRUE?
a) Both the YY and RR date formats will interpret the year as 1999.
b) Both the YY and RR date formats will interpret the year as 2099.
c) The RR date format will interpret the year as 2099, and the YY date format will interpret the year as 1999.
d) The RR date format will interpret the year as 1999, and the YY date format will interpret the year as 2099.
 
04) Which two statements would cause an implicit COMMIT to occur? (Choose two.)
a) GRANT
b) SELECT
c) RENAME
d) COMMIT
e) UPDATE
f) ROLLBACK
 
05) The EMPLOYEE table contains these columns:
EMP_ID NUMBER(9)
FNAME VARCHAR2(25)
LNAME VARCHAR(30)
SALARY NUMBER(7,2)
BONUS NUMBER(5,2)
DEPT_ID NUMBER(9)
You need to calculate the average bonus for all the employees in each department. The average should be calculated based on all the rows in the table, even if some employees do not receive a bonus.
Which group function should you use to calculate this value?
a) AVG
b) SUM
c) MAX
d) MEAN
e) COUNT
f) AVERAGE
 
06) Which statement concerning SQL functions is TRUE?
a) All date functions return DATE datatype values.
b) Character functions can return character or number values.
c) Single-row functions can only be used in SELECT and WHERE clauses.
d) Conversion functions convert a column definition from one datatype to another datatype.
 
07) Examine the data from the DONATION table.
This statement fails when executed:
SELECT amount_pledged, amount_paid
FROM donation
WHERE donor_id =
(SELECT donor_id
FROM donation
WHERE amount_pledged = 1000.00
OR pledge_dt = '05-JAN-2002');
Which two changes could correct the problem? (Choose two. Each correct answer is a separate solution.)
a) Remove the subquery WHERE clause.
b) Change the outer query WHERE clause to WHERE donor_id IN.
c) Change the outer query WHERE clause to WHERE donor_id LIKE.
d) Include the DONOR_ID column in the select list of the outer query.
e) Remove the single quotes around the date value in the inner query WHERE clause.
f) Change the subquery WHERE clause to WHERE amount_pledged = 1000.00 AND pledge_dt = '05-JAN-2002'.
 
08) Which statements concerning the creation of a view are TRUE? (Choose all that apply.)
a) View columns that are the result of derived values must be given a column alias.
b) A constraint name must be provided when using the WITH CHECK OPTION clause or the statement will fail.
c) A view may have column names that are different from the actual base table(s) column names by using column aliases.
d) When the view already exists, using the OR REPLACE option requires regranting the object privileges previously granted on the view.
 
09) You want to query employee information and display the results sorted by the employee's department, then by their salaries from highest to lowest. When multiple employees within the same department share a last name, they must be displayed in alphabetical order by first name.
Which ORDER BY clause should you use in your query?
a) ORDER BY department_id, salary, last_name, first_name
b) ORDER BY department_id, salary ASC, last_name, first_name
c) ORDER BY department_id, salary DESC, last_name, first_name
d) ORDER BY department_id, salary DESC, first_name ||' '|| last_name ASC
 
10) Examine the data in the LINE_ITEM table.
You query the database and return the value 23.
Which SELECT statement did you use?
a) SELECT SUBSTR(product_id, 3)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
b) SELECT SUBSTR(product_id, 3, -2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
c) SELECT SUBSTR(product_id, -3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;
d) SELECT SUBSTR(product_id, 3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;

Solutions:

QUESTION: 01

Answer: b

QUESTION: 02

Answer: c

QUESTION: 03

Answer: d

QUESTION: 04

Answer: a, c

QUESTION: 05

Answer: a

QUESTION: 06

Answer: b

QUESTION: 07

Answer: b, f

QUESTION: 08

Answer: a, c

QUESTION: 09

Answer: c

QUESTION: 10

Answer: d

Note: If you find any typo or data entry error in these sample questions, we request you to update us by commenting on this page or write an email on feedback@oraclestudy.com

 

Rating: 3 / 5 (4 votes)