Proper study guides for Replace Oracle Oracle Database: SQL Fundamentals I certified begins with Oracle 1z0 051 dumps pdf preparation products which designed to deliver the Printable 1z0 051 dumps questions by making you pass the oracle 1z0 051 test at your first time. Try the free 1z0 051 practice test demo right now.
2017 NEW RECOMMEND
Free VCE & PDF File for Oracle 1Z0-051 Real Exam
Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions
Q151. – (Topic 1)
Where can sub queries be used? (Choose all that apply)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following
are required clauses:
A SELECT list
A FROM clause
The following are optional clauses: WHERE GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.
Q152. – (Topic 1)
The SQL statements executed in a user session as follows: Exhibit:
Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
A. Both the DELETE statements and the UPDATE statement are rolled back
B. The rollback generates an error
C. Only the DELETE statements are rolled back
D. Only the seconds DELETE statement is rolled back
E. No SQL statements are rolled back
Q153. – (Topic 1)
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
Q154. – (Topic 2)
The CUSTOMERS table has these columns:
The CUSTOMER_ID column is the primary key for the table.
You need to determine how dispersed your customer base is.
Which expression finds the number of different countries represented in the CUSTOMERS table?
D. COUNT DISTINCT UPPER(country_address)
E. COUNT(DISTINCT (UPPER(country_address)))
Q155. – (Topic 2)
The CUSTOMERS table has these columns:
A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France?
A. WHERE lower(country_address) = "france"
B. WHERE lower(country_address) = 'france'
C. WHERE lower(country_address) IS 'france'
D. WHERE lower(country_address) = '%france%'
E. WHERE lower(country_address) LIKE %france%
Incorrect Answer: Ainvalid use of symbol ââ Cinvalid use of IS keyword Dinvalid use of % in condition Einvalid use of condition Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-12
Q156. – (Topic 1)
Evaluate the following SQL statements: Exhibit:
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Which two statements are true regarding the above view? (Choose two.)
A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
C. It allows you to insert rows into the EMPLOYEES table
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements
Q157. – (Topic 2)
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)
A. The HAVING clause can be used with aggregate functions in subqueries.
B. The WHERE clause can be used to exclude rows after dividing them into groups.
C. The WHERE clause can be used to exclude rows before dividing them into groups.
D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
Q158. – (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.
Q159. – (Topic 1)
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO
columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)
A. CREATE INDEX ord_idx1
B. CREATE INDEX ord_idx2
C. CREATE INDEX ord_idx3
D. CREATE INDEX ord_idx4
ON ord,ord_items(ord_no, ord_date,qty);
Explanation: How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a
column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index
is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create
the FOREIGN KEY column index for a join in a query to improve the speed of retrieval.
Note: You can manually create a unique index, but it is recommended that you create a
unique constraint, which implicitly creates a unique index.
Q160. – (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.
WHERE cust_last_iiame='Roberts' AND
VALUES(l.'10-mar-2007\\ 'direct'. &customer_id. 1000):