Free Download Pass4sure and Lead2pass 1Z0-051 Exam Question with PDF & VCE(1-10)

admin

QUESTION 1
Which is the valid CREATE TABLE statement?

A.    CREATE TABLE emp9$# (emp_no NUMBER (4));
B.    CREATE TABLE 9emp$# (emp_no NUMBER(4));
C.    CREATE TABLE emp*123 (emp_no NUMBER(4));
D.    CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);

Answer: A

QUESTION 2
Which two statements are true regarding tables? (Choose two.)

A.    A table name can be of any length.
B.    A table can have any number of columns.
C.    A column that has a DEFAULT value cannot store null values.
D.    A table and a view can have the same name in the same schema.
E.    A table and a synonym can have the same name in the same schema.
F.    The same table name can be used in different schemas in the same database.

Answer: EF

QUESTION 3
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: BD

QUESTION 4
Which two statements are true regarding constraints? (Choose two.)

A.    A foreign key cannot contain NULL values.
B.    The column with a UNIQUE constraint can store NULLS .
C.    A constraint is enforced only for an INSERT operation on a table.
D.    You can have more than one column in a table as part of a primary key.

Answer: BD

QUESTION 5
Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command fails when executed. What could be the reason?

A.    SYSDATE cannot be used with the CHECK constraint.
B.    The BETWEEN clause cannot be used for the CHECK constraint.
C.    The CHECK constraint cannot be placed on columns having the DATE data type.
D.    ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also
the FOREIGN KEY.

Answer: A
Explanation:
CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees
(…
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),

QUESTION 6
Evaluate the following SQL commands:
SQL>CREATE SEQUENCE ord_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE;
SQL>CREATE TABLE ord_items
(ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL,
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)

A.    You cannot use SYSDATE in the condition of a CHECK constraint.
B.    You cannot use the BETWEEN clause in the condition of a CHECK constraint.
C.    You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
D.    You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is
also the FOREIGN KEY.

Answer: AC

QUESTION 7
Examine the structure and data in the PRICE_LIST table:
name           Null           Type
——        ———      ——-
PROD_ID       NOT NULL       NUMBER(3)
PROD_PRICE                    VARCHAR2(10)
PROD_ID         PROD_PRICE
———-     ————
100              $234.55
101              $6,509.75
102              $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?

A.    SELECT TO_CHAR(prod_price* .25,’$99,999.99′)
FROM PRICE_LIST;
B.    SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,’$99,999.00′)
FROM PRICE_LIST;
C.    SELECT TO_CHAR(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′) FROM PRICE_LIST;
D.    SELECT TO_NUMBER(TO_NUMBER(prod_price,’$99,999.99′)* .25,’$99,999.00′) FROM PRICE_LIST;

Answer: C

QUESTION 8
View the Exhibit and examine the structure of the PROMOTIONS table.
Which two SQL statements would execute successfully? (Choose two.)

image

A.    UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE TO_CHAR(promo_end_date, ‘yyyy’) > ‘2000’;
B.    SELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,’mon dd yy’)=’jul 01 98′;
C.    UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE promo_end_date > TO_DATE(SUBSTR(’01-JAN-2000′,8));
D.    SELECT TO_CHAR(promo_begin_date,’dd/month’)
FROM promotions
WHERE promo_begin_date IN (TO_DATE(‘JUN 01 98’), TO_DATE(‘JUL 01 98’));

Answer: AB

QUESTION 9
View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.

image

Which two queries give the correct result? (Choose two.)

A.    SELECT promo_name, TO_CHAR(promo_end_date,’Day’) ‘, ‘
TO_CHAR(promo_end_date,’Month’) ‘ ‘
TO_CHAR(promo_end_date,’DD, YYYY’) AS last_day
FROM promotions;
B.    SELECT promo_name,TO_CHAR (promo_end_date,’fxDay’) ‘, ‘
TO_CHAR(promo_end_date,’fxMonth’) ‘ ‘
TO_CHAR(promo_end_date,’fxDD, YYYY’) AS last_day
FROM promotions;
C.    SELECT promo_name, TRIM(TO_CHAR(promo_end_date,’Day’)) ‘, ‘
TRIM(TO_CHAR(promo_end_date,’Month’)) ‘ ‘
TRIM(TO_CHAR(promo_end_date,’DD, YYYY’)) AS last_day
FROM promotions;
D.    SELECTpromo_name,TO_CHAR(promo_end_date,’fmDay’)’,’
TO_CHAR(promo_end_date,’fmMonth’) ‘ ‘
TO_CHAR(promo_end_date,’fmDD, YYYY’) AS last_day
FROM promotions;

Answer: CD

QUESTION 10
View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, y ou need to generate a report that shows an increase in the credit limit by 15% for all customers.
Customers whose credit limit has not been entered should have the message ” Not Available” displayed.
Which SQL statement would produce the required result?

image

A.    SELECT NVL(cust_credit_limit,’Not Available’)*.15 “NEW CREDIT” FROM customers;
B.    SELECT NVL(cust_credit_limit*.15,’Not Available’) “NEW CREDIT” FROM customers;
C.    SELECT TO_CHAR(NVL(cust_credit_limit*.15,’Not Available’)) “NEW CREDIT” FROM customers;
D.    SELECT NVL(TO_CHAR(cust_credit_limit*.15),’Not Available’) “NEW CREDIT” FROM customers;

Answer: D
Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
NVL(commission_pct,0)
NVL(hire_date,’01-JAN-97′)
NVL(job_id,’No Job Yet’)

…go to http://www.lead2pass.com/1z0-051.html to download the full version Q&As.