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

admin

QUESTION 81
You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?

A.    It executes successfully and gives the correct output.
B.    It gives an error because the HAVING clause is not valid.
C.    It executes successfully but does not give the correct output.
D.    It gives an error because the GROUP BY expression is not valid.

Answer: B

QUESTION 82
View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the following SQL statement:
SQL> SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;
Which statement is true regarding the outcome of the above query?

image

A.    It executes successfully.
B.    It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
C.    It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
D.    It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the
same column.

Answer: A

QUESTION 83
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)

A.    The HAVING clause can be used with aggregate functions in subqueries.
B.    The WHERE clause can be used to exclude rows after dividing them into groups.
C.    The WHERE clause can be used to exclude rows before dividing them into groups.
D.    The aggregate functions and columns used in the HAVING clause must be specified in the SELECT
list of the query.
E.    The WHERE and HAVING clauses can be used in the same statement only if they are applied to
different columns in the table.

Answer: AC

QUESTION 84
View the Exhibit and examine the structure of the PROMOTIONS table.
Examine the following two SQL statements:
Statement 1
SQL>SELECT promo_category,SUM(promo_cost)
FROM promotions
WHERE promo_end_date-promo_begin_date > 30
GROUP BY promo_category;
Statement 2
SQL>SELECT promo_category,sum(promo_cost)
FROM promotions
GROUP BY promo_category
HAVING MIN(promo_end_date-promo_begin_date)>30;
Which statement is true regarding the above two SQL statements?

image

A.    statement 1 gives an error, statement 2 executes successfully
B.    statement 2 gives an error, statement 1 executes successfully
C.    statement 1 and statement 2 execute successfully and give the same output
D.    statement 1 and statement 2 execute successfully and give a different output

Answer: D

QUESTION 85
Examine the data in the ORD_ITEMS table:
ORD_NO      ITEM_NO     QTY
——–   ——–    ———
1            111          10
1            222          20
1            333          30
2            333          30
2            444          40
3            111          40
Evaluate the following query:
SQL>SELECT item_no, AVG(qty)
FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2
GROUP BY item_no;
Which statement is true regarding the outcome of the above query?

A.    It gives an error because the HAVING clause should be specified after the GROUP BY clause.
B.    It gives an error because all the aggregate functions used in the HAVING clause must be specified
in the SELECT list.
C.    It displays the item nos with their average quantity where the average quantity is more than double
the minimum quantity of that item in the table.
D.    It displays the item nos with their average quantity where the average quantity is more than double
the overall minimum quantity of all the items in the table.

Answer: C

QUESTION 86
Which CREATE TABLE statement is valid?

A.    CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date DATE NOT NULL);
B.    CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);
C.    CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D.    CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D
Explanation:
PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.
Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

QUESTION 87
You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
1) The order ID will be unique and cannot have null values.
2) The order date cannot have null values and the default should be the current date.
3) The order amount should not be less than 50.
4) The order status will have values either shipped or not shipped.
5) The order payment mode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDL statement for creating the ORD_DETAIL table?

A.    CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));
B.    CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount > 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));
C.    CREATE TABLE ord_details
(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULT SYSDATE NOT NULL,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));
D.    CREATE TABLE ord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULL DEFAULT SYSDATE,
ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min
CHECK (ord_amount >= 50),
ord_status VARCHAR2(15) CONSTRAINT ord_status_chk
CHECK (ord_status IN (‘Shipped’, ‘Not Shipped’)),
ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN (‘Cheque’, ‘Credit Card’,
‘Cash On Delivery’)));

Answer: C

QUESTION 88
You created an ORDERS table with the following description:
name Null Type
ORD_ID NOT NULL NUMBER(2)
CUST_ID NOT NULL NUMBER(3)
ORD_DATE NOT NULL DATE
ORD_AMOUNT NOT NULL NUMBER (10,2)
You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?

A.    You cannot have two constraints on one column.
B.    You cannot add a primary key constraint if data exists in the column.
C.    The primary key constraint can be created only at the time of table creation .
D.    You can add the primary key constraint even if data exists, provided that there are no duplicate values.

Answer: D

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

A.    A table can have only one primary key and one foreign key.
B.    A table can have only one primary key but multiple foreign keys.
C.    Only the primary key can be defined at the column and table levels.
D.    The foreign key and parent table primary key must have the same name.
E.    Both primary key and foreign key constraints can be defined at both column and table levels.

Answer: BE

QUESTION 90
Examine the following SQL commands:
SQL>CREATE TABLE products (
prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0),
prod_name CHAR(30),
prod_qty NUMBER(6),
CONSTRAINT p_name NOT NULL,
CONSTRAINT prod_pk PRIMARY KEY (prod_id));
SQL>CREATE TABLE warehouse (
warehouse_id NUMBER(4),
roomno NUMBER(10) CONSTRAINT r_id CHECK(roomno BETWEEN 101 AND 200),
location VARCHAR2(25),
prod_id NUMBER(3),
CONSTRAINT wr_pr_pk PRIMARY KEY (warehouse_id,prod_id),
CONSTRAINT prod_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id));
Which statement is true regarding the execution of the above SQL commands?

A.    Both commands execute successfully.
B.    The first CREATE TABLE command generates an error because the NULL constraint is not valid.
C.    The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
D.    The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY
constraints cannot be used for the same column.
E.    The first CREATE TABLE command generates an error because the column PROD_ID cannot
be used in the PRIMARY KEY and FOREIGN KEY constraints.

Answer: B

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