1Z0-051 | What Download 1Z0-051 examcollection Is?
Q11. - (Topic 1)
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
Q12. - (Topic 2)
You need to create a table named ORDERS that contain four columns:
1.
an ORDER_ID column of number data type
2.
a CUSTOMER_ID column of number data type
3.
an ORDER_STATUS column that contains a character data type
4.
a DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead.
Which statement accomplishes this?
A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
B. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE);
F. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);
Answer: B
Explanation: Requirement that Order_Status should be a character data type
Not E: Order_status must be a character data type. There is also a syntax error.
Q13. - (Topic 2)
Examine the structure of the EMP_DEPT_VU view:
Which SQL statement produces an error?
A. SELECT *
FROM emp_dept_vu;
B. SELECT department_id, SUM(salary)
FROM emp_dept_vu
GROUP BY department_id;
C. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vu
GROUP BY department_id, job_id;
D. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E. None of the statements produce an error; all are valid.
Answer: E
Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error
Q14. - (Topic 2)
Which constraint can be defined only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY
Answer: B
Explanation:
The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row.
Incorrect Answers A:The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level. C:The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column.
D:The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL. E:The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects
Q15. - (Topic 2)
Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL values.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.
Answer: B,D
Explanation:
Including Constraints
.
Constraints enforce rules at the table level.
.
Constraints prevent the deletion of a table if there are dependencies.
.
The following constraint types are valid:
–
NOT NULL
–
UNIQUE
–
PRIMARY KEY
–
FOREIGN KEY
–
CHECK
Q16. - (Topic 2)
Examine the structure of the PROMOTIONS table:
The management wants to see a report of unique promotion costs in each promotion category.
Which query would achieve the required result?
A. SELECT DISTINCT promo_cost, promo_category FROM promotions;
B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Answer: D
Q17. - (Topic 2)
Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?
A.
SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",
SUM(DECODE(SUBSTR
(promo_begin_date,8),'99',1,0)) "1999"
FROM promotions;
B.
SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0
END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE
0 END) "2000"
FROM promotions;
C.
SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;
D.
SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1,
0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1,
0)) "2000"
FROM promotions;
Answer: A
Q18. - (Topic 2)
The DBA issues this SQL command:
CREATE USER scott IDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. no privileges
B. only the SELECT privilege
C. only the CONNECT privilege
D. all the privileges of a default user
Answer: A
Explanation:
when a user is created, by default no privilege is granted
Incorrect Answer:
BSELECT is not grant
CCONNECT is not grant
Ddefault profile is grant by default not privilege.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-6
Q19. - (Topic 1)
Which is a valid CREATE TABLE statement?
A. CREATE TABLE EMP9$# AS (empid number(2));
B. CREATE TABLE EMP*123 AS (empid number(2));
C. CREATE TABLE PACKAGE AS (packid number(2));
D. CREATE TABLE 1EMP_TEST AS (empid number(2));
Answer: A
Explanation: Table names and column names must begin with a letter and be 1-30
characters long. Characters A-Z,a-z, 0-9, _, $ and # (legal characters but their use is
discouraged).
Incorrect Answer:
BNon alphanumeric character such as “*” is discourage in Oracle table name.
DTable name must begin with a letter.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-4
Q20. - (Topic 1)
View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.
What would be the outcome if all the parentheses are removed from the above statement?
A. It produces a syntax error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
Answer: B