1Z0-051 | A Review Of Tested 1Z0-051 examcollection
Q131. - (Topic 1)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of
theDEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Answer: C,D
Explanation: the EMP_ID_SEQ sequence is not affected by modification to the
EMPLOYEES table. Any other column of NUMBER data type in your schema can use the
EMP_ID_SEQ sequence.
Incorrect Answer:
AEMP_ID_SEQ sequence can be use to populate JOB_ID
BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES
table.
FEMP_ID_SEQ sequence will be dropped automatically when you drop the
EMPLOYEE_ID column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4
Q132. - (Topic 1)
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
Answer: A
Q133. - (Topic 1)
The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?
A. GRANT select ON dept TO ALL_USERS;
B. GRANT select ON dept TO ALL;
C. GRANT QUERY ON dept TO ALL_USERS
D. GRANT select ON dept TO PUBLIC;
Answer: D
Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
Q134. - (Topic 2)
Which best describes an inline view?
A. a schema object
B. a sub query that can contain an ORDER BY clause
C. another name for a view that contains group functions
D. a sub query that is part of the FROM clause of another query
Answer: D
Explanation:
a sub query that is part of the FROM clause of another query
Incorrect Answer:
Ais not a schema object
Bsub query can contain GROUP BY clause as well.
Cdoes not necessary contains group functions
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21
Q135. - (Topic 2)
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?
A. SELECT COUNT(*) FROM employees WHERE last_name='Smith'
B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith'
C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith'
D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith'
E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith'
Answer: D
Q136. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1.
The promo name should not begin with 'T' or 'N'.
2.
The promo should cost more than $20000.
3.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?
A.
WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
B.
WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
C.
WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
D.
WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')
Answer: C
Q137. - (Topic 1)
Examine the structure of the EMPLOYEES table:
Which UPDATE statement is valid?
A.
UPDATE employees
SET first_name = ‘John’
SET last_name = ‘Smith’
WHERE employee_id = 180;
B.
UPDATE employees
SET first_name = ‘John’,
SET last_name = ‘Smoth’
WHERE employee_id = 180;
C.
UPDATE employee
SET first_name = ‘John’
AND last_name = ‘Smith’
WHERE employee_id = 180;
D.
UPDATE employee
SET first_name = ‘John’, last_name = ‘Smith’
WHERE employee_id = 180;
Answer: D
Q138. - (Topic 2)
Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?
A. SELECT &1, "&2"
FROM &3
WHERE last_name = '&4'
B. SELECT &1, '&2'
FROM &3
WHERE '&last_name = '&4' '
C. SELECT &1, &2
FROM &3
WHERE last_name = '&4'
D. SELECT &1, '&2'
FROM EMP
WHERE last_name = '&4'
Answer: C
Explanation:
In a WHERE clause, date and characters values must be enclosed within single quotation marks.
Sample of the correct syntax
SELECT EMPLOYEE_ID, &COLUMN_NAME FROM EMPLOYEES
Incorrect Answers :
A. Incorrect use of " symbol
B. Incorrect use of ' symbol
D. No input for table name as EMP has been use in the statement.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8
Q139. - (Topic 1)
The PRODUCTS table has the following structure:
Evaluate the following two SQL statements:
Which statement is true regarding the outcome?
A. Both the statements execute and give the same result
B. Both the statements execute and give different results
C. Only the second SQL statement executes successfully
D. Only the first SQL statement executes successfully
Answer: B
Explanation:
Using the NVL2 Function The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax NVL2(expr1, expr2, expr3) In the syntax: expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null
Q140. - (Topic 2)
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