1Z0-051 | All About Best Quality 1Z0-051 braindumps

Q121. - (Topic 1) 

Which two statements are true regarding constraints? (Choose two.) 

A. A constraint can be disabled even if the constraint column contains data 

B. A constraint is enforced only for the INSERT operation on a table 

C. A foreign key cannot contain NULL values 

D. All constraints can be defined at the column level as well as the table level 

E. A columns with the UNIQUE constraint can contain NULL values 

Answer: A,E 

Q122. - (Topic 2) 

View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.) 

A. 

SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales; 

B. 

SELECT prod_id FROM products MINUS SELECT prod_id FROM sales; 

C. 

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id; 

D. 

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id; 

Answer: A,C 

Q123. - (Topic 2) 

A data manipulation language statement _____. 

A. completes a transaction on a table 

B. modifies the structure and data in a table 

C. modifies the data but not the structure of a table 

D. modifies the structure but not the data of a table 

Answer: C 

Explanation: 

modifies the data but not the structure of a table 

Incorrect Answer: 

ADML does not complete a transaction 

BDDL modifies the structure and data in the table 

DDML does not modified table structure. 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-3 

Q124. - (Topic 2) 

View the Exhibit and examine the data in the EMPLOYEES table. 

You want to generate a report showing the total compensation paid to each employee to date. 

You issue the following query: 

What is the outcome? 

A. It generates an error because the alias is not valid. 

B. It executes successfully and gives the correct output. 

C. It executes successfully but does not give the correct output. 

D. It generates an error because the usage of the ROUND function in the expression is not valid. 

E. It generates an error because the concatenation operator can be used to combine only two items. 

Answer: C 

Explanation: 

ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) 

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

Q126. - (Topic 2) 

Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table: 

Which two expressions give the same output? (Choose two.) 

A. NVL(NULLIF(list_price, min_price), 0) 

B. NVL(COALESCE(list_price, min_price), 0) 

C. NVL2(COALESCE(list_price, min_price), min_price, 0) 

D. COALESCE(NVL2(list_price, list_price, min_price), 0) 

Answer: B,D 

Explanation: 

Using the COALESCE Function 

. 

The advantage of the COALESCE function over the NVL function is that the COALESCE 

function can take multiple alternate values. 

. 

If the first expression is not null, the COALESCE function returns that expression; 

otherwise, it does a COALESCE of the remaining expressions. 

Using the COALESCE Function 

The COALESCE function returns the first non-null expression in the list. 

Syntax 

COALESCE (expr1, expr2, ... exprn) In the syntax: 

. 

expr1 returns this expression if it is not null 

. 

expr2 returns this expression if the first expression is null and this expression is not null 

. 

exprn returns this expression if the preceding expressions are null Note that all expressions must be of the same data type. 

Q127. - (Topic 2) 

Examine the description of the CUSTOMERS table: 

The CUSTOMER_ID column is the primary key for the table. 

Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco? 

A. SELECT city_address, COUNT(*) FROM customers 

WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’); 

B. SELECT city_address, COUNT (*) 

FROMcustomers 

WHERE city address IN ( ‘Los Angeles’, ‘San Fransisco’) 

GROUP BY city_address; 

C. SELECT city_address, COUNT(customer_id) 

FROMcustomers 

WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’) 

GROUP BYcity_address, customer_id; 

D. SELECT city_address, COUNT (customer_id) 

FROM customers 

GROUP BY city_address IN ( ‘Los Angeles’, ‘San Fransisco’); 

Answer: B 

Explanation: 

Not C: The customer ID in the GROUP BY clause is wrong 

Q128. - (Topic 1) 

The PART_CODE column in the SPARES table contains the following list of values: 

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

A. It produces an error. 

B. It displays all values. 

C. It displays only the values A%_WQ123 and AB_WQ123 . 

D. It displays only the values A%_WQ123 and A%BWQ123 . 

E. It displays only the values A%BWQ123 and AB_WQ123. 

Answer: D 

Explanation: 

Combining Wildcard Characters 

The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter “o” as the second character. 

ESCAPE Identifier 

When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement: SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_%' ESCAPE '' 

Q129. - (Topic 2) 

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

Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table: 

ENAME HIREDATE 

SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81 

You want to generate a list of user IDs as follows: USERID 

Smi17DEC80 All20FEB81 War22FEB81 

You issue the following query: 

SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-')) 

"USERID" 

FROM employees; 

What is the outcome? 

A. It executes successfully and gives the correct output. 

B. It executes successfully but does not give the correct output. 

C. It generates an error because the REPLACE function is not valid. 

D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function. 

Answer: A 

Explanation: 

REPLACE(text, search_string,replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3,'9','85') from dual Query 2: select replace(sysdate, 'DEC','NOV') from dual 

Q130. - (Topic 2) 

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. 

Which statement accomplishes this task? 

A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id; 

B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal; 

C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id); 

D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal; 

Answer: B 

Explanation: function MAX(column_name) 

Incorrect Answer: 

Ainvalid statement 

Cinner query return more than one line 

Dcolumn maxsal does not exists. 

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