It is impossible to pass Oracle 1z0 051 dumps pdf exam without any help in the short term. Come to Actualtests soon and find the most advanced, correct and guaranteed Oracle oracle 1z0 051 practice questions. You will get a surprising result by our Regenerate Oracle Database: SQL Fundamentals I practice guides.

Q1. - (Topic 1) 

Exhibit contains the structure of PRODUCTS table: 

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 products whose list price is the second highest in the table. 

C. It shown the names of all products whose list price is less than the maximum list price 

D. It shows the names of all products in the table 

Answer:


Q2. - (Topic 2) 

You need to create a table named ORDERS that contain four columns: 

1. 

an ORDER_ID column of number data type 

2. 

a CUSTOMER_ID column of number data type 

3. 

an ORDER_STATUS column that contains a character data type 

4. 

a DATE_ORDERED column to contain the date the order was placed. 

When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead. 

Which statement accomplishes this? 

A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE); 

B. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE); 

C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE); 

D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE); 

E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE); 

F. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE); 

Answer:

Explanation: Requirement that Order_Status should be a character data type 

Not E: Order_status must be a character data type. There is also a syntax error. 


Q3. - (Topic 2) 

Which two statements are true about WHERE and HAVING clauses? (Choose two) 

A. A WHERE clause can be used to restrict both rows and groups. 

B. A WHERE clause can be used to restrict rows only. 

C. A HAVING clause can be used to restrict both rows and groups. 

D. A HAVING clause can be used to restrict groups only. 

E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause. 

F. A HAVING clause CANNOT be used in sub queries. 

Answer: B,D Explanation: 

B: WHERE clause cannot be use to restrict groups 

WHERE clause cannot be use when there is group functions. 

D: A HAVING clause can only e used to restrict GROUPS. 

Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause 

1. 

rows are grouped 

2. 

the group function is applied to the group 

3. 

the group that match the criteria in the Having clause are displayed. 

Incorrect Answers : 

A. Where clause cannot be use to restrict groups 

C. A HAVING clause can only e used to restrict GROUPS. 

E. WHERE clause cannot be use when there is group function, instead HAVING is to be use. 

F. There is no constraint to use HAVING clause in a sub queries. 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20 


Q4. - (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 


Q5. - (Topic 2) 

Which constraint can be defined only at the column level? 

A. UNIQUE 

B. NOT NULL 

C. CHECK 

D. PRIMARY KEY 

E. FOREIGN KEY 

Answer:

Explanation: 

The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row. 

Incorrect Answers A:The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level. C:The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column. 

D:The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL. E:The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects 


Q6. - (Topic 1) 

Which statement is true regarding the INTERSECT operator? 

A. It ignores NULL values 

B. The number of columns and data types must be identical for all SELECT statements in the query 

C. The names of columns in all SELECT statements must be identical 

D. Reversing the order of the intersected tables the result 

Answer:

Explanation: 

INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and 

removing duplicates. 

The columns in the queries that make up a compound query can have different names, but 

the output result set will use the names of the columns in the first query. 


Q7. - (Topic 2) 

Evaluate the following query: 

SELECT INTERVAL '300' MONTH, 

INTERVAL '54-2' YEAR TO MONTH, 

INTERVAL '11:12:10.1234567' HOUR TO SECOND 

FROM dual; 

What 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:

Explanation: 

Datetime Data Types You can use several datetime data types: INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days, hours, minutes, and seconds 


Q8. - (Topic 1) 

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 removes all the rows as well as the structure of the table 

B. It removes all the rows in the table and deleted rows cannot be rolled back 

C. It removes all the rows in the table and deleted rows can be rolled back 

D. It would not remove the rows if the table has a primary key 

Answer:


Q9. - (Topic 1) 

View the Exhibits and examine the structures of the PROMOTIONS and SALES tables. 

Evaluate the following SQL statements: 

Which statement is true regarding the output of the above query? 

A. It gives details of product IDs that have been sold irrespective of whether they had a 

promo or not 

B. It gives the details of promos for which there have been no sales 

C. It gives the details of promos for which there have been sales 

D. It gives details of all promos irrespective of whether they have resulted in a sale or not 

Answer:


Q10. - (Topic 1) 

Examine these statements: 

CREATE ROLE registrar; 

GRANT UPDATE ON student_grades TO registrar; 

GRANT registrar to user1, user2, user3; 

What does this set of SQL statements do? 

A. The set of statements contains an error and does not work. 

B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role. 

E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object. 

F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar. 

Answer:

Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3. 

Incorrect Answer: Athe statement does not contain error Bthere is no MODIFY privilege Dstatement does not create 3 users with the role Eprivilege is grant to role then grant to user Fprivilege is grant to role then grant to user