web analytics

Easily Pass 1Z0-051 Exam With New Passleader 1Z0-051 Braindumps

Vendor: Oracle
Exam Code: 1Z0-051
Exam Name: Oracle Database 11g: SQL Fundamentals I

QUESTION 1
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

QUESTION 2
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?
21

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

QUESTION 3
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 4
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 5
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

QUESTION 6
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

QUESTION 7
Which two statements about sub queries are true? (Choose two.)

A.    A sub query should retrieve only one row.
B.    A sub query can retrieve zero or more rows.
C.    A sub query can be used only in SQL query statements.
D.    Sub queries CANNOT be nested by more than two levels.
E.    A sub query CANNOT be used in an SQL query statement that uses group functions.
F.    When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column.

Answer: BF

QUESTION 8
Which three statements are true regarding subqueries? (Choose three.)

A.    Subqueries can contain GROUP BY and ORDER BY clauses.
B.    Main query and subquery can get data from different tables.
C.    Main query and subquery must get data from the same tables.
D.    Subqueries can contain ORDER BY but not the GROUP BY clause.
E.    Only one column or expression can be compared between the main query and subquery.
F.    Multiple columns or expressions can be compared between the main query and subquery.

Answer: ABF

QUESTION 9
Which statement is true regarding the UNION operator?

A.    The number of columns selected in all SELECT statements need to be the same
B.    Names of all columns must be identical across all SELECT statements
C.    By default, the output is not sorted
D.    NULL values are not ignored during duplicate checking

Answer: A

QUESTION 10
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 11
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 12
Evaluate the following SQL statement:
SQL> SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = ‘Internet’ ORDER BY 2 DESC
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = ‘TV’
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category =’Radio’;
Which statement is true regarding the outcome of the above query?

A.    It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
B.    It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.
C.    It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
D.    It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

Answer: D


Easily Pass 1Z0-051 Exam With New Passleader 1Z0-051 Braindumps

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