You will get the particular comprehensive explanation once you face difficulties on your 1Z0-051 study. Testking supply you the particular 1Z0-051 Oracle Database: SQL Fundamentals I blueprint legibly within the 1Z0-051 puts. All these are significantly saving your expenses to get familiar with the particular Oracle classes.

2016 Jun 1Z0-051 free exam

Q81. - (Topic 2) 

Examine the data in the CUSTOMERS table: 


You want to list all cities that have more than one customer along with the customer details. Evaluate the following query: 

SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname); 

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.) 

A. JOIN 

B. NATURAL JOIN 

C. LEFT OUTER JOIN 

D. FULL OUTER JOIN 

E. RIGHT OUTER JOIN 

Answer: A,E 


Q82. - (Topic 1) 

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? 


A. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000); 

B. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND redit_limit=600).order_total) VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000): 

C. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id. 

o.ordertotal FROM orders o. customers c WHERE o.customer_id = c.customerid AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600) VALUES (L'10-mar-2007\ 'direct'.( SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000); 

D. INSERT INTO orders (order_id.order_date.order_mode. 

(SELECT customer_id 

FROM customers 

WHERE cust_last_iiame='Roberts' AND 

credit_limit=600).order_total) 

VALUES(l.'10-mar-2007\ 'direct'. &customer_id. 1000): 

Answer: A 


Q83. - (Topic 1) 

See the Exhibit and examine the structure and data in the INVOICE table: Exhibit: 


Which two SQL statements would executes successfully? (Choose two.) 

A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice; 

B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice; 

C. SELECT (AVG(inv_date) FROM invoice; 

D. SELECT AVG(inv_date - SYSDATE),AVG(inv_amt) FROM invoice; 

Answer: A,D 


Q84. - (Topic 1) 

See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table: 




You issue the following query: 


Which statement is true regarding the outcome of this query? 

A. It produces an error because the NATURAL join can be used only with two tables 

B. It produces an error because a column used in the NATURAL join cannot have a qualifier 

C. It produces an error because all columns used in the NATURAL join should have a qualifier 

D. It executes successfully 

Answer: B 

Explanation: 

Creating Joins with the USING Clause 

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. 

The Natural JOIN USING Clause 

The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement. 


Q85. - (Topic 1) 

You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement: 

1) The order ID will be unique and cannot have null values. 

2) The order date cannot have null values and the default should be the current date. 

3) The order amount should not be less than 50. 

4) The order status will have values either shipped or not shipped. 

5) The order payment mode should be cheque, credit card, or cash on delivery (COD). 

Which is the valid DDL statement for creating the ORD_DETAIL table? 

A. 

CREATE TABLE ord_details 

(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL, 

ord_date DATE DEFAULT SYSDATE NOT NULL, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount > 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

B. 

CREATE TABLE ord_details 

(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL, 

ord_date DATE DEFAULT SYSDATE NOT NULL, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount > 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

C. 

CREATE TABLE ord_details 

(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY, 

ord_date DATE DEFAULT SYSDATE NOT NULL, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount >= 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

D. 

CREATE TABLE ord_details 

(ord_id NUMBER(2), 

ord_date DATE NOT NULL DEFAULT SYSDATE, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount >= 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

Answer: C 


2passeasy.com

Latest 1z0-051 dumps vce:

Q86. - (Topic 2) 

Which best describes an inline view? 

A. a schema object 

B. a sub query that can contain an ORDER BY clause 

C. another name for a view that contains group functions 

D. a sub query that is part of the FROM clause of another query 

Answer: D 

Explanation: 

a sub query that is part of the FROM clause of another query 

Incorrect Answer: 

Ais not a schema object 

Bsub query can contain GROUP BY clause as well. 

Cdoes not necessary contains group functions 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21 


Q87. - (Topic 2) 

View the Exhibit and examine the structure and data in the INVOICE table. 


Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.) 

A. inv_amt ='0255982' : requires explicit conversion 

B. inv_date > '01-02-2008' : uses implicit conversion 

C. CONCAT(inv_amt,inv_date) : requires explicit conversion 

D. inv_date = '15-february-2008' : uses implicit conversion 

E. inv_no BETWEEN '101' AND '110' : uses implicit conversion 

Answer: D,E 

Explanation: 

In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user. Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output. Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements. 


Q88. - (Topic 2) 

View the Exhibit and examine the data in the EMPLOYEES table: 

You want to display all the employee names and their corresponding manager names. 

Evaluate the following query: 

SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME" 

FROM employees e ______________ employees m 

ON e.manager_id = m.employee_id; 

Which JOIN option can be used in the blank in the above query to get the required output? 

Exhibit: 

A. only inner JOIN 

B. only FULL OUTER JOIN 

C. only LEFT OUTER JOIN 

D. only RIGHT OUTER JOIN 

Answer: C 


Q89. - (Topic 1) 

Examine the structure of the EMPLOYEES table: 

EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER\ SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of 

theDEPARTMENTS table 

You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table. 

Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.) 

A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column. 

B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column. 

C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table. 

D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence. 

E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table. 

F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column. 

Answer: C,D 

Explanation: the EMP_ID_SEQ sequence is not affected by modification to the 

EMPLOYEES table. Any other column of NUMBER data type in your schema can use the 

EMP_ID_SEQ sequence. 

Incorrect Answer: 

AEMP_ID_SEQ sequence can be use to populate JOB_ID 

BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify. 

EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES 

table. 

FEMP_ID_SEQ sequence will be dropped automatically when you drop the 

EMPLOYEE_ID column. 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4 


Q90. - (Topic 2) 

You issue the following query: 

SQL> SELECT AVG(MAX(qty)) 

FROM ord_items 

GROUP BY item_no 

HAVING AVG(MAX(qty))>50; 

Which statement is true regarding the outcome of this query? 

A. It executes successfully and gives the correct output. 

B. It gives an error because the HAVING clause is not valid. 

C. It executes successfully but does not give the correct output. 

D. It gives an error because the GROUP BY expression is not valid. 

Answer: B 

Explanation: 

The general form of the SELECT statement is further enhanced by the addition of the 

HAVING clause and becomes: 

SELECT column|expression|group_function(column|expression [alias]),…} 

FROM table 

[WHERE condition(s)] 

[GROUP BY {col(s)|expr}] 

[HAVING group_condition(s)] 

[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]]; 

An important difference between the HAVING clause and the other SELECT statement 

clauses is that it may only be specified if a GROUP BY clause is present. This dependency 

is sensible since group-level rows must exist before they can be restricted. The HAVING 

clause can occur before the GROUP BY clause in the SELECT statement. However, it is 

more common to place the HAVING clause after the GROUP BY clause. All grouping is 

performed and group functions are executed prior to evaluating the HAVING clause.