It is impossible to pass Oracle 1Z0-051 exam without any help in the short term. Come to Pass4sure soon and find the most advanced, correct and guaranteed Oracle 1Z0-051 practice questions. You will get a surprising result by our Update Oracle Database: SQL Fundamentals I practice guides.
2016 Jun exam 1z0-051:
Q1. - (Topic 2)
Examine the structure of the PROMOS table:
You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)
A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos;
B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
D. SELECT promo_name, DECODE(promo_end_date
-promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM
promo_end_date - promo_start_date)
Q2. - (Topic 1)
You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position.
Which query would give the required output?
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, -2, 1)='a'
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>'' AND SUBSTR(cust_first_name, -2, 1)='a'
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND SUBSTR(cust_first_name, 1,-2)='a'
SELECT cust_first_name FROM customers WHERE INSTR(cust_first_name, 'e')<>0 AND SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a'
The SUBSTR(string, start position, number of characters) function accepts three
parameters and returns a string consisting of the number of characters extracted from the
source string, beginning at the specified start position:
substr('http://www.domain.com',12,6) = domain
The position at which the first character of the returned string begins.
When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the
When position is negative, then the function counts backward from the end of string.
The length of the returned string. SUBSTR calculates lengths using characters as defined
by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses
Unicode complete characters.
SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
When you do not specify a value for this argument, then the function
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
Q3. - (Topic 2)
Examine the structure of the PROMOTIONS table:
The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?
A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Q4. - (Topic 2)
The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2 (25)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_' ESCAPE '\'
B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'
C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";
D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_'
Explanation: ESCAPE identifier to search for the _ symbol
Incorrect Answer: BESCAPE identifier must be use Cwrong syntax Dwrong syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-13
Q5. - (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')
Up to the minute oracle database 11g sql fundamentals 1 1z0-051 pdf:
Q6. - (Topic 2)
Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)
A. It locks only the columns specified in the SELECT list.
B. It locks the rows that satisfy the condition in the SELECT statement.
C. It can be used only in SELECT statements that are based on a single table.
D. It can be used in SELECT statements that are based on a single or multiple tables.
E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.
FOR UPDATE Clause in a SELECT Statement Locks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement. FOR UPDATE Clause in a SELECT Statement When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data). There are times, however, when you may want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT...FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records “for your changes only.” No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT. You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command.
Q7. - (Topic 1)
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. Number of days since the promo started based on the current Chicago data and time
B. Number of days since the promo started based on the current Singapore data and time.
C. An error because the WHERE condition specified is invalid
D. An error because the ROUND function specified is invalid
Q8. - (Topic 1)
Which two statements are true regarding sub queries? (Choose two.)
A. A sub query can retrieve zero or more rows.
B. Only two sub queries can be placed at one level.
C. A sub query can be used only in SQL query statements.
D. A sub query can appeal* on either side of a comparison operator.
E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.
Q9. - (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;
Q10. - (Topic 2)
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers
whose last names start with A, B, or C, and credit limit is below 10, 000.
Evaluate the following two queries:
Which statement is true regarding the execution of the above queries?
A. Only the first query gives the correct result.
B. Only the second query gives the correct result.
C. Both execute successfully and give the same result.
D. Both execute successfully but do not give the required result.