2016 June Oracle Official: 1Z0-051: Oracle Database 11g: SQL Fundamentals I Exam Questions New Updated Today! Braindump2go.com Offers 1Z0-051 PDF and VCE Dumps 303q for Free Downloading!
NEW QUESTION 21 – NEW QUESTION 30:
QUESTION 21
View the Exhibit for the structure of the STUDENT and FACULTY tables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Which statement is true regarding the outcome?
A. Only s tatement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.
Answer: D
QUESTION 22
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: BD
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)
QUESTION 23
Examine the structure of the CUSTOMERS table:
CUSTNO is the PRIMARY KEY in the table.
You want to find out if any customers’ details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result? (Choose two.)
A. self-join
B. subquery
C. full outer-join with self-join
D. left outer-join with self-join
E. right outer-join with self-join
Answer: AB
QUESTION 24
View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.
You issue the following query:
Which statement is true regarding the outcome of this query?
A. It executes successfully.
B. It produces an error because the NATURAL join can be used only with two tables.
C. It produces an error because a column used in the NATURAL join cannot have a qualifier.
D. It produces an error because all columns used in the NATURAL join should have a qualifier.
Answer: C
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.
QUESTION 25
View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.
You need to generate a report that gives details of the customer’s last name, name of the product, and the quantity sold for all customers in ‘Tokyo’ .
Which two queries give the required result? (Choose two.)
A. SELECT c.cust_last_name,p.prod_name, s.quantity_sold
FROM sales s JOIN products p
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city=’Tokyo’;
B. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHERE c.cust_city=’Tokyo’;
C. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOIN sales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
AND c.cust_city=’Tokyo’;
D. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN
sales s
USING(prod_id)
JOIN customers c
USING(cust_id)
WHERE c.cust_city=’Tokyo’;
Answer: AC
QUESTION 26
View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.
You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:
Which statement is true regarding the above query?
A. It executes successfully and gives the required result.
B. It executes successfully but does not give the required result.
C. It produces an error because the join order of the tables is incorrect.
D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same
SELECT statement.
Answer: B
QUESTION 27
View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.
The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.
Which query would give the required result?
A. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p JOIN proj_task_details d
ON (p.based_on = d.task_id);
B. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
C. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);
D. SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p JOIN proj_task_details d
ON (p.task_id = d.task_id);
Answer: B
QUESTION 28
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: AE
QUESTION 29
View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables.
You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers.
Which FROM clause gives the required result?
A. FROM sales JOIN customers USING (cust_id)
FULL OUTER JOIN countries USING (country_id);
B. FROM sales JOIN customers USING (cust_id)
RIGHT OUTER JOIN countries USING (country_id);
C. FROM customers LEFT OUTER JOIN sales USING (cust_id)
RIGHT OUTER JOIN countries USING (country_id);
D. FROM customers LEFT OUTER JOIN sales USING (cust_id)
LEFT OUTER JOIN countries USING (country_id);
Answer: C
QUESTION 30
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.
Evaluate the following SQL statement:
Which statement is true regarding the output of the above query?
A. It gives the details of promos for which there have been sales.
B. It gives the details of promos for which there have been no sales.
C. It gives details of all promos irrespective of whether they have resulted in a sale or not.
D. It gives details of product ID s that have been sold irrespective of whether they had a promo or not.
Answer: C
2016 Valid Oracle 1Z0-051 Exam Study Materials:
1.| Latest 1Z0-051 PDF and VCE Dumps 303Q&As from Braindump2go: http://www.braindump2go.com/1z0-051.html [100% Exam Pass Guaranteed!]
2.| NEW 1Z0-051 Exam Questions and Answers: https://drive.google.com/folderview?id=0B75b5xYLjSSNVGxLT202clFMbjA&usp=sharing
MORE Practice is the Most Important IF You want to PASS 1Z0-051 Exam 100%!
————— Braindump2go.com
————— Pass All IT Exams at the first Try!