Getting Smart with: 1z0 051 dumps pdf

Your success in Oracle 1z0 051 dumps pdf is our sole target and we develop all our 1z0 051 practice test braindumps in a way that facilitates the attainment of this target. Not only is our 1z0 051 dumps pdf study material the best you can find, it is also the most detailed and the most updated. 1z0 051 dumps Practice Exams for Oracle oracle 1z0 051 are written to the highest standards of technical accuracy.


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:

Q1. – (Topic 2) 

Evaluate the following SQL statement: 

SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore'); 

Which statement is true regarding the above query if one of the values generated by the subquery is NULL? 

A. It produces an error. 

B. It executes but returns no rows. 

C. It generates output for NULL as well as the other values produced by the subquery. 

D. It ignores the NULL value and generates output for the other values produced by the subquery. 


Q2. – (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. 



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. 

Q3. – (Topic 2) 

Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition? 

A. SELECT &1, "&2" 

FROM &3 

WHERE last_name = '&4'; 

B. SELECT &1, '&2' 

FROM &3 

WHERE '&last_name = '&4' '; 

C. SELECT &1, &2 

FROM &3 

WHERE last_name = '&4'; 

D. SELECT &1, '&2' 


WHERE last_name = '&4'; 



In a WHERE clause, date and characters values must be enclosed within single quotation marks. 

Sample of the correct syntax 


Incorrect Answers : 

A. Incorrect use of " symbol 

B. Incorrect use of ' symbol 

D. No input for table name as EMP has been use in the statement. 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8 

Q4. – (Topic 2) 

Examine the description of the EMPLOYEES table: 


Which statement produces the number of different departments that have employees with last name Smith? 

A. SELECT COUNT(*) FROM employees WHERE last_name='Smith'; 

B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith'; 

C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith'; 

D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith'; 

E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith'; 


Q5. – (Topic 1) 

Evaluate the following query: 

What would be the outcome of the above query? 

A. It produces an error because flower braces have been used. 

B. It produces an error because the data types are not matching. 

C. It executes successfully and introduces an 's at the end of each promo_name in the output. 

D. It executes successfully and displays the literal" {'s start date was} " for each row in the output. 



So, how are words that contain single quotation marks dealt with? There are essentially 

two mechanisms available. The most popular of these is to add an additional single 

quotation mark next to each naturally occurring single quotation mark in the character 


Oracle offers a neat way to deal with this type of character literal in the form of the 

alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote 

characters as the special pair of symbols that enclose or wrap any other character literal. 

These character-enclosing symbols could have been anything other than single quotation 


Bearing this in mind, consider the alternative quote (q) operator. The q operator enables 

you to choose from a set of possible pairs of wrapping symbols for character literals as 

alternatives to the single quote symbols. The options are any single-byte or multibyte 

character or the four brackets: (round brackets), {curly braces}, [squarebrackets], or <angle 

brackets>. Using the q operator, the character delimiter can effectively be changed from a 

single quotation mark to any other character 

The syntax of the alternative quote operator is as follows: 

q'delimiter'character literal which may include the single quotes delimiter' where delimiter 

can be any character or bracket. 

Alternative Quote (q) Operator 

Specify your own quotation mark delimiter. 

Select any delimiter. 

Increase readability and usability. 

SELECT department_name || q'[ Department's Manager Id: ]' 

|| manager_id 

AS "Department and Manager" 

FROM departments; 

Alternative Quote (q) Operator 

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and select your own quotation mark delimiter. You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [ ], { }, ( ), or < >. In the example shown, the string contains a single quotation mark, which is normally interpreted as a delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation mark delimiters. The string between the brackets delimiters is interpreted as a literal character string. 

Q6. – (Topic 2) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 

Which DELETE statement is valid? 

A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees); 

B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees); 

C. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey'); 

D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey'); 



The correct syntax for DELETE statement 

DELETE [ FROM ] table 

[ WHERE condition ]; 

Incorrect Answers : 

A. '=' is use in the statement and sub query will return more than one row. 

Error Ora-01427: single-row sub query returns more than one row. 

B. Incorrect DELETE statement 

D. Incorrect DELETE statement 

Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Manipulating Data, 

p. 8-19 

Q7. – (Topic 2) 

View the Exhibits and examine PRODUCTS and SALES tables. 

You issue the following query to display product name and the number of times the product has been sold: 

SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p 

ON i.prod_id = p.prod_id; 

What happens when the above statement is executed? 

A. The statement executes successfully and produces the required output. 

B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query. 

C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together. 

D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause. 


Q8. – (Topic 1) 

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

You need to generate a report of all promos from the PROMOTIONS table based on the following conditions: 


The promo name should not begin with 'T' or 'N'. 


The promo should cost more than $20000. 


The promo should have ended after 1st January 2001. 

Which WHERE clause would give the required result? 


WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01' 


WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01' 


WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01' 


WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01') 


Q9. – (Topic 1) 

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

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times. 

Which statement is true regarding this SQL statement? 

A. It executes successfully and generates the required result. 

B. It produces an error because COUNT(*) should be specified in the SELECT clause also. 

C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause. 

D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*). 



Restricting Group Results with the HAVING Clause 

You use the HAVING clause to specify the groups that are to be displayed, thus further 

restricting the groups on the basis of aggregate information. 

In the syntax, group_condition restricts the groups of rows returned to those groups for 

which the specified condition is true. 

The Oracle server performs the following steps when you use the HAVING clause: 


Rows are grouped. 


The group function is applied to the group. 


The groups that match the criteria in the HAVING clause are displayed. 

The HAVING clause can precede the GROUP BY clause, but it is recommended that you 

place the GROUP BY clause first because it is more logical. Groups are formed and group 

functions are calculated before the HAVING clause is applied to the groups in the SELECT 


Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups. 

Q10. – (Topic 2) 

Examine the structure of the PROMOS table: 

You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day. 

Which query gives the correct output? 

A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') 

FROM promos; 

B. SELECT promo_name, NVL(TRUNC(promo_end_date – promo_start_date), 'Same 

Day') FROM promos; 

C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), 

NULL,'Same Day') 

FROM promos; 

D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), 

NULL,'Same day') FROM promos; 



The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A – date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component