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 May 1Z0-051 Study Guide Questions:
Q191. - (Topic 1)
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?
A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50%
C. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit"
D. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM
Explanation: Duplicate Rows Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.
Q192. - (Topic 1)
Here is the structure and data of the CUST_TRANS table: Exhibit:
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07'
C. SELECT transamt FROM cust_trans WHERE custno > '11'
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07'
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
Q193. - (Topic 1)
You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list. Winch two queries would achieve the required result? (Choose two.)
A. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit DESC:
B. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit:
C. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_credit_limit NULLS LAST:
D. SELECT cust_last_name. cust_credit_limit FROM customers ORDER BY cust_last_name. cust_credit_limit NULLS LAST:
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. ANSWER C Sorting The default sort order is ascending:
Numeric values are displayed with the lowest values first (for example, 1 to 999).
Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95).
Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).
Null values are displayed last for ascending sequences and first for descending sequences.
. You can also sort by a column that is not in the SELECT list.
Replace oracle 1z0-051 certification:
Q194. - (Topic 1)
Evaluate the following two queries: Exhibit:
Which statement is true regarding the above two queries?
A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column
B. Performance would degrade in query 2
C. There would be no change in performance
D. Performance would improve in query 2
Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator
has no performance benefits and is used only for logical simplicity.
Q195. - (Topic 1)
See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit:
Which SQL statements are valid? (Choose all that apply.)
A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,
promo_cost * 0.25, 100) "Discount"
B. SELECT promo_id, DECODE(promo_cost, 10000,
DECODE(promo_category, 'G1', promo_cost *.25, NULL),
C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000),
NULL, promo_cost*.25, 'N/A') "Catcost"
D. SELECT promo_id, DECODE(promo_cost, >10000, 'High',
<10000, 'Low') "Range"
The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1, [comp2,iftrue2...[ compN,iftrueN]], [iffalse]).
Q196. - (Topic 2)
View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another. Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements: select avg(salary) from employees; select last_name from employees where salary < result_of_previous_query ;
Alternatively, this example uses one statement with a subquery:
select last_name from employees where salary < (select avg(salary)from employees);
In this example, the subquery is used to substitute a value into the WHERE clause of the
parent query: it is returning a single value, used for comparison with the rows retrieved by
the parent query.
The subquery could return a set of rows. For example, you could use the following to find
all departments that do actually have one or more employees assigned to them:
select department_name from departments where department_id in
(select distinct(department_id) from employees);
Vivid 1z0-047 vs 1z0-051:
Q197. - (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;
Q198. - (Topic 2)
The PRODUCTS table has these columns:
Evaluate this SQL statement:
ORDER BY price, product_name;
What is true about the SQL statement?
A. The results are not sorted.
B. The results are sorted numerically.
C. The results are sorted alphabetically.
D. The results are sorted numerically and then alphabetically.
the result is sort by price which is numeric and follow by product_name which is alphabetically.
Incorrect Answer: Athe results are sorted Bthe results are sorted with alphabetically as well Cthe results are sorted with numerically as well
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-2
Q199. - (Topic 2)
Which constraint can be defined only at the column level?
B. NOT NULL
D. PRIMARY KEY
E. FOREIGN KEY
the NOT NULL constraint can be specified only at the column level, not at the table level.
Incorrect Answer: AUNIQUE can be define at table level CCHECK can be define at table level DPRIMARY KEY can be define at table level EFOREIGN KEY can be define at table level
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-8
Q200. - (Topic 2)
View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.
Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)
A. It locks all the rows that satisfy the condition in the statement.
B. It locks only the columns that satisfy the condition in both the tables.
C. The locks are released only when a COMMIT or ROLLBACK is issued.
D. The locks are released after a DML statement is executed on the locked rows.
FOR UPDATE Clause in a SELECT Statement
Locks the rows in the EMPLOYEES table where job_id is SA_REP.
Lock is released only when you issue a ROLLBACK or a COMMIT.
If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECTstatement SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id;
see more 1Z0-051 dumps