Skip to content

All About Realistic 1Z0-051 exam dumps

Want to know Examcollection 1z0 051 dumps pdf Exam practice test features? Want to lear more about Oracle Oracle Database: SQL Fundamentals I certification experience? Study Highest Quality Oracle 1z0 051 dumps answers to Leading 1z0 051 dumps questions at Examcollection. Gat a success with an absolute guarantee to pass Oracle 1z0 051 dumps (Oracle Database: SQL Fundamentals I) test on your first attempt.

2017 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.certleader.com/1Z0-051-dumps.html

Q11. – (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 

Q12. – (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:

Q13. – (Topic 1) 

Examine the statement: 

Create synonym emp for hr.employees; 

What happens when you issue the statement? 

A. An error is generated. 

B. You will have two identical tables in the HR schema with different names. 

C. You create a table called employees in the HR schema based on you EMP table. 

D. You create an alternative name for the employees table in the HR schema in your own schema. 

Answer:

Q14. – (Topic 1) 

Which three statements/commands would cause a transaction to end? (Choose three.) 

A. COMMIT 

B. SELECT 

C. CREATE 

D. ROLLBACK 

E. SAVEPOINT 

Answer: A,C,D 

Q15. – (Topic 1) 

Evaluate the following SQL statement: 

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

A. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement 

B. It executes successfully and displays rows in the descending order of PROMO_CATEGORY 

C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement 

D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators 

Answer:

Explanation: 

Using the ORDER BY Clause in Set Operations 

The ORDER BY clause can appear only once at the end of the compound query. 

Component queries cannot have individual ORDER BY clauses. 

The ORDER BY clause recognizes only the columns of the first SELECT query. 

By default, the first column of the first SELECT query is used to sort the output in an 

ascending order. 

Q16. – (Topic 1) 

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

All products have a list price. 

You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products. 

What would be the outcome if all the parentheses are removed from the above statement? 

A. It produces a syntax error. 

B. The result remains unchanged. 

C. The total price value would be lower than the correct value. 

D. The total price value would be higher than the correct value. 

Answer:

Q17. – (Topic 2) 

Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.) 

A. finding the remainder of a division 

B. adding a number to a date for a resultant date value 

C. comparing two expressions to check whether they are equal 

D. checking whether a specified character exists in a given string 

E. removing trailing, leading, and embedded characters from a character string 

Answer: A,C,D 

Q18. – (Topic 1) 

See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables: 

The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively. 

Evaluate the following the CREATE TABLE command: 

Exhibit: 

Which statement is true regarding the above command? 

A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match 

B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table 

C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition 

D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table 

Answer:

Explanation: 

Creating a Table Using a Subquery 

Create a table and insert rows by combining the CREATE 

TABLE statement and the AS subquery option. 

CREATE TABLE table 

[(column, column…)] 

AS subquery; 

Match the number of specified columns to the number of subquery columns. 

Define columns with column names and default values. 

Guidelines 

The table is created with the specified column names, and the rows retrieved by the 

SELECT statement are inserted into the table. 

The column definition can contain only the column name and default value. 

If column specifications are given, the number of columns must equal the number of 

columns in the subquery SELECT list. 

If no column specifications are given, the column names of the table are the same as the 

column names in the subquery. 

The column data type definitions and the NOT NULL constraint are passed to the new 

table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY 

column will not pass the NOT NULL feature to the new column. Any other constraint rules 

are not passed to the new table. However, you can add constraints in the column definition. 

Q19. – (Topic 2) 

What is true about updates through a view? 

A. You cannot update a view with group functions. 

B. When you update a view group functions are automatically computed. 

C. When you update a view only the constraints on the underlying table will be in effect. 

D. When you update a view the constraints on the views always override the constraints on the underlying tables. 

Answer:

Q20. – (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)