Proper study guides for Replace Oracle Oracle Database: SQL Fundamentals I certified begins with Oracle 1z0 051 dumps preparation products which designed to deliver the 100% Correct 1z0 051 practice test questions by making you pass the 1z0 051 pdf test at your first time. Try the free 1z0 051 latest dumps free download pdf demo right now.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1Z0-051 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 1Z0-051 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/1Z0-051-exam-dumps.html
Q81. - (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
Q82. - (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.
Q83. - (Topic 2)
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:
What would be the outcome?
A. Abigail PA B. Abigail Pa
C. Abigail IS
D. an error message
Answer: B
Q84. - (Topic 1)
Which three are true? (Choose three.)
A. A MERGE statement is used to merge the data of one table with data from another.
B. A MERGE statement replaces the data of one table with that of another.
C. A MERGE statement can be used to insert new rows into a table.
D. A MERGE statement can be used to update existing rows in a table.
Answer: A,C,D
Explanation: The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table
Q85. - (Topic 1)
Which three statements are true about multiple-row sub queries? (Choose three.)
A. They can contain a subquery within a sub query.
B. They can return multiple columns as well as rows.
C. They cannot contain a sub query within a sub query.
D. They can return only one column but multiple rows.
E. They can contain group functions and GROUP BY and HAVING clauses.
F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
Answer: A,B,E
Q86. - (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
promos;
E. SELECT
promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',
promo_end_date - promo_start_date)
FROM promos;
Answer: B,C,D
Q87. - (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.
Answer: A,C
Explanation:
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;
Q88. - (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: D
Q89. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:
Which two SQL statements would execute successfully? (Choose two.)
A.
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000'
B.
SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98'
C.
UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
D.
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'));
Answer: A,B
Q90. - (Topic 1)
Which two statements are true regarding the USING clause in table joins?(Choose two.)
A. It can be used to join a maximum of three tables.
B. It can be used to restrict the number of columns used in a NATURAL join.
C. It can be used to access data from tables through equijoins as well as nonequijoins.
D. It can be used to join tables that have columns with the same name and compatible data types.
Answer: B,D
Explanation:
NATURAL JOIN operation A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):
All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to
every column of that table that is not a common column.
If a common column is referenced without being qualified by a table name, the column
reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT
OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column
point to the column in the second (right) table.
Syntax
TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN {
TableViewOrFunctionExpression |
( TableExpression ) }
Examples
If the tables COUNTRIES and CITIES have two common columns named COUNTRY and
COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:
SELECT * FROM COUNTRIES NATURAL JOIN CITIES
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)
