Pass4sure 1z0 051 dumps Questions are updated and all 1z0 051 pdf answers are verified by experts. Once you have completely prepared with our 1z0 051 latest dumps free download pdf exam prep kits you will be ready for the real 1z0 051 pdf exam without a problem. We have Up to date Oracle 1z0 051 practice test dumps study guide. PASSED 1z0 051 dumps pdf First attempt! Here What I Did.
Q11. - (Topic 2)
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
Evaluate the following query:
What would be the outcome?
A. Abigail PA B. Abigail Pa
C. Abigail IS
D. an error message
Q12. - (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
Q13. - (Topic 1)
Examine the structure and data of the CUSTJTRANS table:
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:
Q14. - (Topic 1)
Which statement is true regarding sub queries?
A. The LIKE operator cannot be used with single- row subqueries.
B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.
C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.
D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.
Using the ANY Operator in Multiple-Row Subqueries
The ANY operator (and its synonym, the SOME operator) compares a value to each value
returned by a subquery.
<ANY means less than the maximum.
>ANY means more than the minimum.
=ANY is equivalent to IN
Using the ALL Operator in Multiple-Row Subqueries
The ALL operator compares a value to every value returned by a subquery.
>ALL means more than the maximum and
<ALL means less than the minimum.
The NOT operator can be used with IN, ANY, and ALL operators.
Q15. - (Topic 2)
View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.
Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)
A. It locks all the rows that satisfy the condition in the statement.
B. It locks only the columns that satisfy the condition in both the tables.
C. The locks are released only when a COMMIT or ROLLBACK is issued.
D. The locks are released after a DML statement is executed on the locked rows.
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 SELECTstatement SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id;
Q16. - (Topic 2)
Which two statements are true regarding subqueries? (Choose two.)
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
Using a Subquery to Solve a Problem Suppose you want to write a query to find out who earns a salary greater than Abel’s salary. To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. Subquery Syntax A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause In the syntax: operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query. Guidelines for Using Subqueries Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
Q17. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully.
Which two SQL statements would execute successfully? (Choose two.)
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000'
SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98'
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
Q18. - (Topic 2)
Which is an iSQL*Plus command?
Explanation: Explanation: The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of table name, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values. Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7
Q19. - (Topic 2)
You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL;
What is the outcome?
A. It executes successfully and returns the correct result.
B. It executes successfully but does not return the correct result.
C. It generates an error because TO_CHAR should be replaced with TO_DATE.
D. It generates an error because rrrr should be replaced by rr in the format string.
E. It generates an error because fm and double quotation marks should not be used in the format string.
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.
Q20. - (Topic 2)
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
A. DROP emp_dept_vu;
B. DELETE emp_dept_vu;
C. REMOVE emp_dept_vu;
D. DROP VIEW emp_dept_vu;
E. DELETE VIEW emp_dept_vu;
F. REMOVE VIEW emp_dept_vu;
DROP VIEW viewname;
Incorrect Answer: ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20