The Secret of Oracle 1Z0-051 practice test

Pass4sure offers free demo for 1z0 051 dumps pdf exam. "Oracle Database: SQL Fundamentals I", also known as 1z0 051 dumps exam, is a Oracle Certification. This set of posts, Passing the Oracle 1z0 051 practice test exam, will help you answer those questions. The oracle 1z0 051 Questions & Answers covers all the knowledge points of the real exam. 100% real Oracle 1z0 051 practice test exams and revised by experts!


2024 Oracle Official New Released 1Z0-051 ♥♥
https://www.certleader.com/1Z0-051-dumps.html


Q1. - (Topic 2) 

Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table: 

PROMO_BEGIN _DATE 

04-jan-00 

10-jan-00 

15-dec-99 

18-oct-98 

22-aug-99 

You want to display the number of promotions started in 1999 and 2000. 

Which query gives the correct output? 

A. 

SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000", 

SUM(DECODE(SUBSTR 

(promo_begin_date,8),'99',1,0)) "1999" 

FROM promotions; 

B. 

SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 

END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 

0 END) "2000" 

FROM promotions; 

C. 

SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions; 

D. 

SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 

0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1, 

0)) "2000" 

FROM promotions; 

Answer:

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

Q3. - (Topic 2) 

View the Exhibit and examine the structure of CUSTOMERS table. Evaluate the following query: 

Which statement is true regarding the above query? 

A. It executes successfully. 

B. It produces an error because the condition on the CUST_CITY column is not valid. 

C. It produces an error because the condition on the CUST_FIRST_NAME column is not valid. 

D. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid. 

Answer:

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

Q5. - (Topic 2) 

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

Evaluate the following SQL statement: 

UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id 

FROM sales) 

SET time_id = '22-MAR-2007' 

WHERE cust_id = (SELECT cust_id 

FROM customers 

WHERE cust_last_name = 'Roberts' AND 

credit_limit = 600); 

Which statement is true regarding the execution of the above UPDATE statement? 

A. It would not execute because two tables cannot be used in a single UPDATE statement. 

B. It would not execute because the SELECT statement cannot be used in place of the table name. 

C. It would execute and restrict modifications to only the columns specified in the SELECT statement. 

D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement. 

Answer:

Explanation: 

One UPDATE statement can change rows in only one table, but it can change any number of rows in that table. 

Q6. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement: 

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

A. It executes successfully. 

B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause. 

C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. 

D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. 

Answer:

Q7. - (Topic 1) 

Examine the structure and data of the CUSTJTRANS table: 

CUSTJRANS 

Name Null? Type 

CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT 

11 01-JAN-07 1000 

22 01-FEB-07 2000 

33 01-MAR-07 3000 

Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.) 

A. SELECT transdate + '10' FROM custjrans; 

B. SELECT * FROM custjrans WHERE transdate = '01-01-07': 

C. SELECT transamt FROM custjrans WHERE custno > '11': 

D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07': 

E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000: 

Answer: A,C,D 

Q8. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.) 

A. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) "Discount" 

FROM promotions; 

B. SELECT promo id. DECODE(promo_cost. 10000. 

DECODE(promo_category. 'Gl 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: 

Note: there are some syntax issues in this question. 

Q9. - (Topic 2) 

Which four are valid Oracle constraint types? (Choose four.) 

A. CASCADE 

B. UNIQUE 

C. NONUNIQUE 

D. CHECK 

E. PRIMARY KEY 

F. CONSTANT 

G. NOT NULL 

Answer: B,D,E,G 

Explanation: 

Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and Unique Incorrect Answer: AIs not Oracle constraint CIs not Oracle constraint FIs not Oracle constraint Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-3 

Q10. - (Topic 2) 

Examine this statement: 

SELECT student_id, gpa FROM student_grades WHERE gpa > &&value; 

You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time? 

A. An error is returned. 

B. You are prompted to enter a new value. 

C. A report is produced that matches the first report produced. 

D. You are asked whether you want a new value or if you want to run the report based on the previous value. 

Answer:

Explanation: 

use the double-ampersand if you want to reuse the variable value without prompting the user each time. 

Incorrect Answer: Ais not an error 

B&& will not prompt user for second time D&& will not ask the user for new value 

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