Proper study guides for Update Oracle Oracle Database: SQL Fundamentals I certified begins with Oracle 1z0 051 practice test preparation products which designed to deliver the Actual 1z0 051 practice test questions by making you pass the 1z0 051 practice test test at your first time. Try the free 1z0 051 practice test demo right now.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/1Z0-051-exam-dumps.html
Q111. - (Topic 1)
You need to create a table for a banking application. One of the columns in the table has the 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. INTERVAL YEAR TO MONTH
B. NUMBER
C. TIMESTAMP
D. DATE
E. INTERVAL DAY TO SECOND
Answer: E
Q112. - (Topic 2)
The DBA issues this SQL command:
CREATE USER Scott
IDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. No privileges.
B. Only the SELECT privilege.
C. Only the CONNECT privilege.
D. All the privileges of a default user.
Answer: A
Explanation:
There are no privileges for the user Scott at this point. They are not added themselves to
the user immediately after creation. The DBA needs to grant all privileges explicitly.
Incorrect Answers
B:There are no privileges for the user Scott at this point. SELECT privilege needs to be
added to the user Scott.
C:There are no privileges for the user Scott at this point. CONNECT privilege needs to be
added to the user Scott.
D:There is no default user in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351
Chapter 8: User Access in Oracle
Q113. - (Topic 1)
A SELECT statement can be used to perform these three functions:
Choose rows from a table.
Choose columns from a table
Bring together data that is stored in different tables by creating a link between
them.
Which set of keywords describes these capabilities?
A. difference, projection, join
B. selection, projection, join
C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product
Answer: B
Explanation: Explanation: choose rows from a table is SELECTION,
Choose column from a table is PROJECTION
Bring together data in different table by creating a link between them is JOIN.
Incorrect Answer:
Aanswer should have SELECTION, PROJECTION and JOIN.
Canswer should have SELECTION, PROJECTION and JOIN.
Danswer should have SELECTION, PROJECTION and JOIN.
Eanswer should have SELECTION, PROJECTION and JOIN.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6
Q114. - (Topic 1)
You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) 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 ('Shipped', '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 ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
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 ('Shipped', '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 ('Shipped', 'Not Shipped')),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN ('Cheque', 'Credit Card',
'Cash On Delivery')));
Answer: C
Q115. - (Topic 1)
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:
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
Q116. - (Topic 1)
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. 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
B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY
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 positional notation cannot be used in the ORDER BY clause with SET operators
Answer: A
Explanation:
Using the ORDER BY Clause in Set Operations
The ORDER BY clause can appear only once at the end of the compound query.
Component queries cannot have individual ORDER BY clauses.
The ORDER BY clause recognizes only the columns of the first SELECT query.
By default, the first column of the first SELECT query is used to sort the output in an
ascending order.
Q117. - (Topic 2)
The user Sue issues this SQL statement:
GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;
The user Alice issues this SQL statement:
GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;
The user Reena issues this SQL statement:
GRANT SELECT ON sue.EMP TO timber;
The user Sue issues this SQL statement:
REVOKE select on sue.EMP FROM alice;
For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?
A. Alice only
B. Alice and Reena
C. Alice, Reena, and Timber
D. Sue, Alice, Reena, and Timber
Answer: C
Explanation: use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked. Alice, Reena and Timber will be revoke.
Incorrect Answer: Athe correct answer should be Alice, Reena and Timber Bthe correct answer should be Alice, Reena and Timber Dthe correct answer should be Alice, Reena and Timber
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-17
Q118. - (Topic 1)
Examine the structure of the MARKS table:
Exhibit:
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
Q119. - (Topic 1)
The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE
123456 152525.99
You issue the following query:
SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*')
FROM products
WHERE prod_id = 123456;
What would be the outcome?
A. 152526****
B. **152525.99
C. 152525**
D. an error message
Answer: A
Explanation:
The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.
Q120. - (Topic 2)
Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
Answer: B,D
Explanation:
B: The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually
any magnitude can be stored and are guaranteed portable among different systems
operating Oracle, up to 38 digits of precision.
The following numbers can be stored in a NUMBER column:
Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits Zero Positive and negative infinity (generated only by importing from an Oracle Version 5 database)
D: The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. An interval literal specifies a period of time, and Oracle supports two types of interval literals: YEAR_TO_MONTH and DAY TO SECOND. For DAY TO SECOND, you can specify these differences in terms in terms of days, hours, minutes, and seconds. DAY TO SECOND contains a leading field and may contain an optional trailing field. If trailing field is specified it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid.
A DAY TO MINUTE interval considers an interval of days to the nearest minute. Reference: Oracle Database Concepts 10g, Native Datatypes
