Tactics to oracle 1z0 051

Proper study guides for Far out Oracle Oracle Database: SQL Fundamentals I certified begins with Oracle 1z0 051 latest dumps free download pdf preparation products which designed to deliver the Accurate 1z0 051 practice test questions by making you pass the 1z0 051 dumps pdf test at your first time. Try the free 1z0 051 pdf demo right now.

Q11. - (Topic 1) 

You need to calculate the number of days from 1st Jan 2007 till date: 

Dates are stored in the default format of dd-mm-rr. 

Which two SQL statements would give the required output? (Choose two.) 

A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL; 

B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL; 

C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL 

D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL 

E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL; 

Answer: A,C 


Q12. - (Topic 2) 

Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three.) 

A. 

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL; 

B. 

SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL; 

C. 

SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL; 

D. 

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL; 

E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL; 

Answer: A,D,E 


Q13. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization. 

Exhibit: 

Which two statements are true regarding the above view? (Choose two.) 

A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table 

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table 

C. It allows you to insert rows into the EMPLOYEES table 

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements 

Answer: B,D 


Q14. - (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:

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. 


Q15. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables. 

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company. 

You need to find those customers who have never changed their address. 

Which SET operator would you use to get the required output? 

A. INTERSECT 

B. UNION ALL 

C. MINUS 

D. UNION 

Answer:


Q16. - (Topic 1) 

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

You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task? 

A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC; 

B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC; 

C. SELECT cust_city, cust_income_level, cust_last_name 

FROM customers 

ORDER BY (cust_city, cust_income_level) DESC; 

D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC; 

Answer:


Q17. - (Topic 1) 

What is true regarding sub queries? 

A. The inner query always sorts the results of the outer query 

B. The outer query always sorts the results of the inner query 

C. The outer query must return a value to the outer query 

D. The inner query returns a value to the outer query 

E. The inner query must always return a value or the outer query will give an error 

Answer:

Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result 


Q18. - (Topic 1) 

Which view should a user query to display the columns associated with the constraints on a table owned by the user? 

A. USER_CONSTRAINTS 

B. USER_OBJECTS 

C. ALL_CONSTRAINTS 

D. USER_CONS_COLUMNS 

E. USER_COLUMNS 

Answer:

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view. Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25 


Q19. - (Topic 1) 

See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit: 

Which SQL statements are valid? (Choose all that apply.) 

A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, 

promo_cost * 0.25, 100) "Discount" 

FROM promotions; 

B. SELECT promo_id, DECODE(promo_cost, 10000, 

DECODE(promo_category, 'G1', promo_cost *.25, NULL), 

NULL) "Catcost" 

FROM promotions; 

C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), 

NULL, promo_cost*.25, 'N/A') "Catcost" 

FROM promotions; 

D. SELECT promo_id, DECODE(promo_cost, >10000, 'High', 

<10000, 'Low') "Range" 

FROM promotions; 

Answer: A,B 

Explanation: 

The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-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. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1, [comp2,iftrue2...[ compN,iftrueN]], [iffalse]). 


Q20. - (Topic 2) 

Which statements are true regarding single row functions? (Choose all that apply.) 

A. MOD : returns the quotient of a division 

B. TRUNC : can be used with NUMBER and DATE values 

C. CONCAT : can be used to combine any number of values 

D. SYSDATE : returns the database server current date and time 

E. INSTR : can be used to find only the first occurrence of a character in a string 

F. TRIM : can be used to remove all the occurrences of a character from a string 

Answer: B,D 

Explanation: 

ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division SYSDATE is a date function that returns the current database server date and time. 

Date-Manipulation Functions 

Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day. 

The CONCAT Function 

The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The INSTR(source string, search item, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com','.',1,2) = 18 The TRIM function literally trims off leading or trailing (or both) character strings from a given source string: