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

---

http://www.passleader.com/1z0-051.html

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
Examine the structure of the PROGRAMS table:
name              Null            Type
   ——           ———      ——-
PROG_ID          NOT NULL       NUMBER(3)
PROG_COST                        NUMBER(8,2)
START_DATE      NOT NULL       DATE
END_DATE DATE
Which two SQL statements would execute successfully? (Choose two.)

A.    SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
B.    SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs;
C.    SELECT NVL(MONTHS_BETWEEN(start_date,end_date),’Ongoing’) FROM programs;
D.    SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),’Ongoing’) FROM programs;

Answer: AD

QUESTION 9
The PRODUCTS table has the following structure:
name                 Null         Type
   ——             ———    ——-
PROD_ID             NOT NULL     NUMBER(4)
PROD_NAME                         VARCHAR2(25)
PROD_EXPIRY_DATE                 DATE
Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2(prod_expiry_date, prod_expiry_date + 15,”)
FROM products;
SQL>SELECT prod_id, NVL(prod_expiry_date, prod_expiry_date + 15)
FROM products;
Which statement is true regarding the outcome?

A.    Both the statements execute and give different results.
B.    Both the statements execute and give the same result.
C.    Only the first SQL statement executes successfully.
D.    Only the second SQL statement executes successfully.

Answer: A

QUESTION 10
Examine the structure of the INVOICE table.
Name           Null           Type
   ——        ———      ——-
INV_NO        NOT NULL       NUMBER(3)
INV_DATE                       DATE
INV_AMT                        NUMBER(10,2)
Which two SQL statements would execute successfully? (Choose two.)

A.    SELECT inv_no,NVL2(inv_date,’Pending’,’Incomplete’) FROM invoice;
B.    SELECT inv_no,NVL2(inv_amt,inv_date,’Not Available’) FROM invoice;
C.    SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate) FROM invoice;
D.    SELECT inv_no,NVL2(inv_amt,inv_amt*.25,’Not Available’) FROM invoice;

Answer: AC

---

Latest Update Real PassLeader Oracle 1Z0-051 Exam Brian Dumps For Free Download

http://www.passleader.com/1z0-051.html