Act now and download your Oracle 1z0 051 dumps pdf test today! Do not waste time for the worthless Oracle 1z0 051 latest dumps free download pdf tutorials. Download Most recent Oracle Oracle Database: SQL Fundamentals I exam with real questions and answers and begin to learn Oracle 1z0 051 dumps with a classic professional.

Q161. - (Topic 2) 

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

Evaluate the following SQL statement: 

SQL>SELECT promo_name,CASE 

WHEN promo_cost >=(SELECT AVG(promo_cost) 

FROM promotions 

WHERE promo_category='TV') 

then 'HIGH' 

else 'LOW' 

END COST_REMARK 

FROM promotions; 

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

A. It shows COST_REMARK for all the promos in the table. 

B. It produces an error because the subquery gives an error. 

C. It shows COST_REMARK for all the promos in the promo category 'TV'. 

D. It produces an error because subqueries cannot be used with the CASE expression. 

Answer:


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


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


Q164. - (Topic 2) 

Which statement describes the ROWID data type? 

A. Binary data up to 4 gigabytes. 

B. Character data up to 4 gigabytes. 

C. Raw binary data of variable length up to 2 gigabytes. 

D. Binary data stored in an external file, up to 4 gigabytes. 

E. A hexadecimal string representing the unique address of a row in its table. 

Answer:

Explanation: 

The ROWID datatype stores information related to the disk location of table rows. They 

also uniquely identify the rows in your table. The ROWID datatype is stored as a 

hexadecimal string. 

Incorrect Answers 

A:It is not a binary data. The ROWID datatype is a hexadecimal string. 

B:It is not a character data. The ROWID datatype is a hexadecimal string. 

C:It is not a raw binary data. The ROWID datatype is a hexadecimal string. 

D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal 

string. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216 

Chapter 5: Creating Oracle Database Objects 


Q165. - (Topic 2) 

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

Evaluate the following SQL statement: 

SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_ begin_date))); 

What would be the outcome of the above SQL statement? 

A. It displays prod IDs in the promo with the lowest cost. 

B. It displays prod IDs in the promos with the lowest cost in the same time interval. 

C. It displays prod IDs in the promos with the highest cost in the same time interval. 

D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval. 

Answer:


Q166. - (Topic 2) 

Which three statements are true regarding subqueries? (Choose three.) 

A. Subqueries can contain GROUP BY and ORDER BY clauses. 

B. Main query and subquery can get data from different tables. 

C. Main query and subquery must get data from the same tables. 

D. Subqueries can contain ORDER BY but not the GROUP BY clause. 

E. Only one column or expression can be compared between the main query and subquery. 

F. Multiple columns or expressions can be compared between the main query and subquery. 

Answer: A,B,F 

Explanation: 

SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING 

clauses of a query. 

A subquery can have any of the usual clauses for selection and projection. The following 

are required clauses: 

A SELECT list 

A FROM clause 

The following are optional clauses: WHERE GROUP BY HAVING 

The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent. 


Q167. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements: 

Which statement is true regarding the above two SQL statements? 

A. statement 1 gives an error, statement 2 executes successfully 

B. statement 2 gives an error, statement 1 executes successfully 

C. statement 1 and statement 2 execute successfully and give the same output 

D. statement 1 and statement 2 execute successfully and give a different output 

Answer:


Q168. - (Topic 2) 

The EMPLOYEES table contains these columns: 

EMPLOYEE_ID NUMBER(4) 

ENAME VARCHAR2 (25) 

JOB_ID VARCHAR2(10) 

Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"? 

A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n' 

B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n' 

C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n' 

D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n' 

Answer:

Explanation: 

INSTR is a character function return the numeric position of a named string. 

INSTR(NAMED,’a’) 

Incorrect Answer: 

BDid not return a numeric position for ‘a’. 

CDid not return a numeric position for ‘a’. 

DDid not return a numeric position for ‘a’. 

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


Q169. - (Topic 2) 

You own a table called EMPLOYEES with this table structure: 

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement? 

DELETE employees; 

A. You get an error because of a primary key violation. 

B. The data and structure of the EMPLOYEES table are deleted. 

C. The data in the EMPLOYEES table is deleted but not the structure. 

D. You get an error because the statement is not syntactically correct. 

Answer:

Explanation: Explanation: You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table [WHEREcondition]; Incorrect Answer: AStatement will not cause error BDelete statement will not delete the table structure DStatement will not cause error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19 


Q170. - (Topic 1) 

The SQL statements executed in a user session as follows: Exhibit: 

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.) 

A. Both the DELETE statements and the UPDATE statement are rolled back 

B. The rollback generates an error 

C. Only the DELETE statements are rolled back 

D. Only the seconds DELETE statement is rolled back 

E. No SQL statements are rolled back 

Answer: B,E