10 tips on 1z0-051 study guide

1Z0-051 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library!

https://www.2passeasy.com/dumps/1Z0-051/

Exam Code: 1Z0-051 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-051 Exam.

2016 Jun 1Z0-051 Study Guide Questions:

Q41. - (Topic 2) 

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

You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this 

condition. 

Evaluate the following SQL statement: 

SQL>SELECT prod_name,prod_list_price FROM products WHERE prod_list_price >= 2 * prod_min_price 

Which ORDER BY clauses can be added to the above SQL statement to get the correct output? 

(Choose all that apply.) 


A. ORDER BY prod_list_price DESC, prod_name; 

B. ORDER BY (2*prod_min_price)DESC, prod_name; 

C. ORDER BY prod_name, (2*prod_min_price)DESC; 

D. ORDER BY prod_name DESC, prod_list_price DESC; 

E. ORDER BY prod_list_price DESC, prod_name DESC; 

Answer: A,E 

Explanation: 

Using the ORDER BY Clause The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition. Syntax SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax: ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.) 

DESC orders the rows in descending order If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. 


Q42. - (Topic 1) 

Which two statements are true regarding the COUNT function? (Choose two.) 

A. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns 

B. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column 

C. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column 

D. A SELECT statement using COUNT function with a DISTINCT keyword cannot have a WHERE clause 

E. The COUNT function can be used only for CHAR, VARCHAR2 and NUMBER data types 

Answer: A,C 

Explanation: 

Using the COUNT Function 

The COUNT function has three formats: 

COUNT(*) 

COUNT(expr) 

COUNT(DISTINCT expr) 

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT 

statement, including duplicate rows and rows containing null values in any of the columns. 

If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of 

rows that satisfy the condition in the WHERE clause. 

In contrast, 

COUNT(expr) returns the number of non-null values that are in the column identified by 

expr. 

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the 

column identified by expr. 


Q43. - (Topic 2) 

Examine the data in the ORD_ITEMS table: 

ORD_NO ITEM_NO QTY 

1 111 10 

1 222 20 

1 333 30 

2 333 30 

2 444 40 

3 111 40 

Evaluate the following query: 

SQL>SELECT item_no, AVG(qty) 

FROM ord_items 

HAVING AVG(qty) > MIN(qty) * 2 

GROUP BY item_no; 

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

A. It gives an error because the HAVING clause should be specified after the GROUP BY clause. 

B. It gives an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list. 

C. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table. 

D. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table. 

Answer: C 


1Z0-051  exam price

Renew pdf 1z0-051:

Q44. - (Topic 1) 

What is true about sequences? 

A. The start value of the sequence is always 1. 

B. A sequence always increments by 1. 

C. The minimum value of an ascending sequence defaults to 1. 

D. The maximum value of descending sequence defaults to 1. 

Answer: C 


Q45. - (Topic 2) 

View the Exhibit and examine the structure of the SALES and PRODUCTS tables. 


In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold. 

Evaluate the following query: 

SQL>SELECT p.prod_id, COUNT(s.prod_id) 

FROM products p _____________ sales s 

ON p.prod_id = s.prod_id 

GROUP BY p.prod_id; 

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

A. JOIN 

B. FULL OUTER JOIN 

C. LEFT OUTER JOIN 

D. RIGHT OUTER JOIN 

Answer: B,C 


Q46. - (Topic 2) 

Which four are attributes of single row functions? (Choose four.) 

A. cannot be nested 

B. manipulate data items 

C. act on each row returned 

D. return one result per row 

E. accept only one argument and return only one value 

F. accept arguments which can be a column or an expression 

Answer: B,C,D,F 

Explanation: 

manipulate data items, act on each row returned, return one result per row, and accept arguments that can be a column or expression. 

Incorrect Answer: Ais not single row attributes Efunctions can accept more than one argument, e.g NVL2 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-5 


2passeasy.com

Vivid 1z0-051 oracle database 11g sql fundamentals i pdf:

Q47. - (Topic 1) 

You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.) 

A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL: 

B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL: 

C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL: 

D. SELECT TO_CHAR(SYSDATE. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL: 

E. SELECT TO_DATE(SYSDATE. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL: 

Answer: B,C 


Q48. - (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 


Q49. - (Topic 2) 

Which two statements are true regarding constraints? (Choose two.) 

A. A foreign key cannot contain NULL values. 

B. A column with the UNIQUE constraint can contain NULL values. 

C. A constraint is enforced only for the INSERT operation on a table. 

D. A constraint can be disabled even if the constraint column contains data. 

E. All constraints can be defined at the column level as well as the table level. 

Answer: B,D 

Explanation: 

Including Constraints 

Constraints enforce rules at the table level. 

Constraints prevent the deletion of a table if there are dependencies. 

The following constraint types are valid: 

– 

NOT NULL 

– 

UNIQUE 

– 

PRIMARY KEY 

– 

FOREIGN KEY 

– 

CHECK 


Q50. - (Topic 2) 

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. 

Which statement accomplishes this task? 

A. ALTER TABLE students ADD PRIMARY KEY student_id; 

B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id); 

C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id; 

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id); E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id); 

Answer: D 

Explanation: 

ALTER TABLE table_name 

ADD [CONSTRAINT constraint] type (coloumn); 

Incorrect Answer: 

Awrong syntax 

Bwrong syntax 

Cwrong syntax 

Eno such MODIFY keyword 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-17 



see more Oracle Database: SQL Fundamentals I