PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE
QUESTION 61
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust_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 (1,’10-mar-2007′, ‘direct’,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600), 1000);
B. INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600),order_total)
VALUES(1,’10-mar-2007′, ‘direct’, &&customer_id, 1000);
C. INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND c.cust_last_name=’Roberts’ ANDc.credit_limit=600 )
VALUES (1,’10-mar-2007′, ‘direct’,(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600), 1000);
D. INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name=’Roberts’ AND
credit_limit=600),order_total)
VALUES(1,’10-mar-2007′, ‘direct’, &customer_id, 1000);
Answer: A
QUESTION 62
View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables. SALE_VW is a view created using the following command:
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table :
SQL>INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
USING (prod_id) WHERE prod_id = 16;
What is the outcome?
A. It executes successfully.
B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.
Answer: D
QUESTION 63
View the exhibit and examine the description for the SALES and CHANNELS tables. You issued the following SQL statement to insert a row in the SALES table:
INSERT INTO sales VALUES
(23, 2300, SYSDATE, (SELECT channel_id
FROM channels
WHERE channel_desc=’Direct Sales’), 12, 1, 500);
Which statement is true regarding the execution of the above statement?
A. The statement will execute and the new row will be inserted in the SALES table.
B. The statement will fail because subquery cannot be used in the VALUES clause.
C. The statement will fail because the VALUES clause is not required with subquery.
D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks.
Answer: A
QUESTION 64
View the Exhibit and examine the description for the CUSTOMERS table. You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?
A. UPDATE customers
SET cust_credit_limit = NULL
WHERE CUST_INCOME_LEVEL = NULL;
B. UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL;
C. UPDATE customers
SET cust_credit_limit = TO_NUMBER(NULL)
WHERE cust_income_level = TO_NUMBER(NULL);
D. UPDATE customers
SET cust_credit_limit = TO_NUMBER(‘ ‘,9999)
WHERE cust_income_level IS NULL;
Answer: B
QUESTION 65
View the Exhibit and examine the description for the CUSTOMERS table. You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566. Which UPDATE statement will accomplish the task?
A. UPDATE customers
SET cust_income_level = (SELECT cust_income_level
FROM customers
WHERE cust_id = 2560),
cust_credit_limit = (SELECT cust_credit_limit
FROM customers
WHERE cust_id = 2566)
WHERE cust_id=2360;
B. UPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id=2560 OR cust_id=2566)
WHERE cust_id=2360;
C. UPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id IN(2560, 2566)
WHERE cust_id=2360;
D. UPDATE customers
SET (cust_income_level,cust_credit_limit) = (SELECT
cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id=2560 AND cust_id=2566)
WHERE cust_id=2360;
Answer: A
Explanation:
Updating Two Columns with a Subquery
You can update multiple columns in the SET clause of an UPDATE statement by writing multiple subqueries. The syntax is as follows:
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[ ,
column =
(SELECT column
FROM table
WHERE condition)]
[WHERE condition ] ;
QUESTION 66
View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. You want to update the EMPLOYEES table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id 2100).
-Set the employees’ salary in location_id 2100 to 1.1 times the average salary of their department.
-Set the employees’ commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
SQL>UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = 2100),
(salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900,2700,2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700)
What is the outcome?
A. It executes successfully and gives the correct result.
B. It executes successfully but does not give the correct result.
C. It generates an error because a subquery cannot have a join condition in an UPDATE statement.
D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.
Answer: B
QUESTION 67
Evaluate the following DELETE statement:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?
A. It would not remove the rows if the table has a primary key.
B. It removes all the rows as well as the structure of the table.
C. It removes all the rows in the table and deleted rows can be rolled back.
D. It removes all the rows in the table and deleted rows cannot be rolled back.
Answer: C
QUESTION 68
Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)
A. DELETE can be used to remove only rows from only one table at a time.
B. DELETE can be used to remove only rows from multiple tables at a time.
C. DELETE can be used only on a table that is a parent of a referential integrity constraint.
D. DELETE can be used to remove data from specific columns as well as complete rows.
E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.
Answer: AE
QUESTION 69
View the Exhibit and examine the structure of CUSTOMERS and SALES tables. Evaluate the following SQL statement:
Which statement is true regarding the execution of the above UPDATE statement?
A. It would not execute because two tables cannot be used in a single UPDATE statement.
B. It would not execute because the SELECT statement cannot be used in place of the table name.
C. It would execute and restrict modifications to only the columns specified in the SELECT statement.
D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
Answer: C
Explanation:
One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.
QUESTION 70
Which three statements/commands would cause a transaction to end? (Choose three.)
A. COMMIT
B. SELECT
C. CREATE
D. ROLLBACK
E. SAVEPOINT
Answer: ACD
QUESTION 71
View the Exhibit and examine the description of SALES and PROMOTIONS tables. You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values. Which DELETE statements are valid? (Choose all that apply.)
A. DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
AND promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’);
B. DELETE
FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
OR promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’);
C. DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’
OR promo_name = ‘everyday low price’);
D. D DELETE
FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name IN (‘blowout sale’,’everyday low price’));
Answer: BCD
QUESTION 72
View the Exhibit and examine the description for the PRODUCTS and SALES table. PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?
A. DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE time_id – 3*365 = SYSDATE );
B. DELETE
FROM products
WHERE prod_id = (SELECT prod_id
FROM sales
WHERE SYSDATE >= time_id – 3*365 );
C. DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE SYSDATE – 3*365 >= time_id);
D. DELETE
FROM products
WHERE prod_id IN (SELECT prod_id
FROM sales
WHERE time_id >= SYSDATE – 3*365 );
Answer: C
QUESTION 73
View the Exhibit and examine the structure and data in the INVOICE table. Which two SQL statements would execute successfully? (Choose two.)
A. SELECT AVG(inv_date )
FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id)
FROM invoice;
C. SELECT MAX(AVG(SYSDATE – inv_date))
FROM invoice;
D. SELECT AVG( inv_date – SYSDATE), AVG(inv_amt)
FROM invoice;
Answer: BD
Explanation:
Using the AVG and SUM Functions
You can use the AVG, SUM, MIN, and MAX functions against the columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.
Using the MIN and MAX Functions
You can use the MAX and MIN functions for numeric, character, and date data types.
The example in the slide displays the most junior and most senior employees.
QUESTION 74
Which two statements are true regarding the COUNT function? (Choose two.)
A. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
C. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
D. COUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
E. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
Answer: BD
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.
QUESTION 75
View the Exhibit and examine the structure of the CUSTOMERS table. Using the CUSTOMERS table, you need to generate a report that shows the average credit limit for customers in WASHINGTON and NEW YORK. Which SQL statement would produce the required result?
A. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_credit_limit, cust_city;
B. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city,cust_credit_limit;
C. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’)
GROUP BY cust_city;
D. SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN (‘WASHINGTON’,’NEW YORK’);
Answer: C
Explanation:
Creating Groups of Data: GROUP BY Clause Syntax
You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.
In the syntax:
group_by_expression Specifies the columns whose values determine the basis for grouping rows
Guidelines
– If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.
– Using a WHERE clause, you can exclude rows before dividing them into groups.
– You must include the columns in the GROUP BY clause.
– You cannot use a column alias in the GROUP BY clause.
QUESTION 76
Examine the structure of the MARKS table:
Which two statements would execute successfully? (Choose two.)
A. SELECT student_name,subject1
FROM marks
WHERE subject1 > AVG(subject1);
B. SELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE ‘R%’;
C. SELECT SUM(subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL;
D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 > subject2;
Answer: CD
QUESTION 77
View the Exhibit and examine the structure of the CUSTOMERS table. Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?
A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit ) FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit;
B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level;
C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city ;
D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);
Answer: B
QUESTION 78
View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:
The above query generates an error on execution. Which clause in the above SQL statement causes the error?
A. WHERE
B. SELECT
C. GROUP BY
D. ORDER BY
Answer: C
QUESTION 79
Examine the structure of the ORDERS table:
You want to find the total value of all the orders for each year and issue the following command:
SQL>SELECT TO_CHAR(order_date,’rr’), SUM(order_total)
FROM orders
GROUP BY TO_CHAR(order_date,’yyyy’);
Which statement is true regarding the outcome?
A. It executes successfully and gives the correct output.
B. It gives an error because the TO_CHAR function is not valid.
C. It executes successfully but does not give the correct output.
D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
Answer: D
QUESTION 80
View the Exhibit and examine the structure of the SALES table. The following query is written to retrieve all those product ID s from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
Which statement is true regarding this SQL statement?
A. It executes successfully and generates the required result.
B. It produces an error because COUNT(*) should be specified in the SELECT clause also.
C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
Answer: C
Explanation:
Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. In the syntax, group_condition restricts the groups of rows returned to those groups for which the specified condition is true. The Oracle server performs the following steps when you use the HAVING clause:
1. Rows are grouped.
2. The group function is applied to the group.
3. The groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.
PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE