Virtual of 1Z0-051 free exam questions materials and testing bible for Oracle certification for candidates, Real Success Guaranteed with Updated 1Z0-051 pdf dumps vce Materials. 100% PASS Oracle Database: SQL Fundamentals I exam Today!

2016 May 1Z0-051 Study Guide Questions:

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


Q62. - (Topic 1) 

View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.) 


A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION; 

B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION; 

C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION; 

D. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION; 

Answer: A,B 

Explanation: 

Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view – ANSWER C 


Q63. - (Topic 1) 

Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.) 

A. The ON clause can be used to join tables on columns that have different names but compatible data types 

B. A maximum of one pair of columns can be joined between two tables using the ON clause 

C. Both USING and ON clause can be used for equijoins and nonequijoins 

D. The WHERE clause can be used to apply additional conditions in SELECT statement containing the ON or the USING clause 

Answer: A,D 

Explanation: 

Creating Joins with the USING Clause If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin. Use the USING clause to match only one column when more than one column matches. The NATURAL JOIN and USING clauses are mutually exclusive Using Table Aliases with the USING clause When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it. For example, in the query mentioned in the slide, you should not alias the location_id column in the WHERE clause because the column is used in the USING clause. The columns that are referenced in the USING clause should not have a qualifier (table name oralias) anywhere in the SQL statement. Creating Joins with the ON Clause The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify arbitrary conditions or specify columns to join. – ANSWER C The join condition is separated from other search conditions. ANSWER D 

The ON clause makes code easy to understand. 


2passeasy.com

Improved testking 1z0-051:

Q64. - (Topic 2) 

Examine the following SQL commands: 


Which statement is true regarding the execution of the above SQL commands? 

A. Both commands execute successfully. 

B. The first CREATE TABLE command generates an error because the NULL constraint is not valid. 

C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid. 

D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column. 

E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints. 

Answer: B 

Explanation: 

Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create 

constraints at either the column level or table level. Constraints defined at the column level 

are included when the column is defined. Table-level constraints are defined at the end of 

the table definition and must refer to the column or columns on which the constraint 

pertains in a set of parentheses. It is mainly the syntax that differentiates the two; 

otherwise, functionally, a columnlevel constraint is the same as a table-level constraint. 

NOT NULL constraints must be defined at the column level. 

Constraints that apply to more than one column must be defined at the table level. 


Q65. - (Topic 2) 

Examine the structure of the SHIPMENTS table: 


You want to generate a report that displays the PO_ID and the penalty amount to be paid if 

the 

SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. 

Evaluate the following two queries: 


Which statement is true regarding the above commands? 

A. Both execute successfully and give correct results. 

B. Only the first query executes successfully but gives a wrong result. 

C. Only the first query executes successfully and gives the correct result. 

D. Only the second query executes successfully but gives a wrong result. 

E. Only the second query executes successfully and gives the correct result. 

Answer: C 

Explanation: 

The MONTHS_BETWEEN(date 1, date 2) function returns the number of months between two dates: months_between('01-FEB-2008','01-JAN-2008') = 1 The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements if then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. DECODE Function Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THENELSE statement: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) DECODE Function The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned. 

If the default value is omitted, a null value is returned where a search value does not match any of the result values. 


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


2passeasy.com

Refined oracle 1z0-051:

Q67. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. 


You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category. 

Which query would give you the required output? 

A. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET' 

B. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET'); 

C. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET'); 

D. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET'); 

Answer: C 


Q68. - (Topic 2) 

Which is an iSQL*Plus command? 

A. INSERT 

B. UPDATE 

C. SELECT 

D. DESCRIBE 

E. DELETE 

F. RENAME 

Answer: D 

Explanation: Explanation: The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of table name, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values. Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7 


Q69. - (Topic 1) 

The STUDENT_GRADES table has these columns: 


Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001? 

A. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

OR gpa > 3.; 

B. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

AND gpa gt 3.0; 

C. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

AND gpa > 3.0; 

D. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ 

OR gpa > 3.0; 

E. SELECT student_id, gpa 

FROM student_grades 

WHERE semester_end > ’01-JAN-2001’ OR semester_end < ’31-DEC-2001’ 

AND gpa >= 3.0; 

Answer: C 


Q70. - (Topic 1) 

You issue the following command to drop the PRODUCTS table: 

SQL>DROP TABLE products; 

What is the implication of this command? (Choose all that apply.) 

A. All data in the table are deleted but the table structure will remain 

B. All data along with the table structure is deleted 

C. All views and synonyms will remain but they are invalidated 

D. The pending transaction in the session is committed 

E. All indexes on the table will remain but they are invalidated 

Answer: B,C,D