PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE
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
Explanation:
The general form of the SELECT statement is further enhanced by the addition of the HAVING clause and becomes:
SELECT column|expression|group_function(column|expression [alias]),…}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
An important difference between the HAVING clause and the other SELECT statement clauses is that it may only be specified if a GROUP BY clause is present. This dependency is sensible since group-level rows must exist before they can be restricted. The HAVING clause can occur before the GROUP BY clause in the SELECT statement. However, it is more common to place the HAVING clause after the GROUP BY clause. All grouping is performed and group functions are executed prior to evaluating the HAVING clause.
QUESTION 82
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
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:
Which statement is true regarding the above two SQL statements?
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:
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:
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:
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
Explanation:
Defining Constraints
The slide gives the syntax for defining constraints when creating a table. You can create constraints at either the column level or table level. Constraints defined at the column level are included when the column is defined. Table-level constraints are defined at the end of the table definition and must refer to the column or columns on which the constraint pertains in a set of parentheses. It is mainly the syntax that differentiates the two; otherwise, functionally, a columnlevel constraint is the same as a table-level constraint. NOT NULL constraints must be defined at the column level. Constraints that apply to more than one column must be defined at the table level.
QUESTION 91
You issued the following command to drop the PRODUCTS table:
SQL> DROP TABLE products;
What is the implication of this command? (Choose all that apply.)
A. All data along with the table structure is deleted.
B. The pending transaction in the session is committed.
C. All indexes on the table will remain but they are invalidated.
D. All views and synonyms will remain but they are invalidated.
E. All data in the table are deleted but the table structure will remain.
Answer: ABD
QUESTION 92
Which two statements are true regarding views? (Choose two.)
A. A simple view in which column aliases have been used cannot be updated.
B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
C. Rows added through a view are deleted from the table automatically when the view is dropped.
D. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
Answer: BD
QUESTION 93
Evaluate the following command:
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:
Which two statements are true regarding the above view? (Choose two.)
A. It allows you to insert rows into the EMPLOYEES table .
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table.
C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table.
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements.
Answer: BD
QUESTION 94
View the Exhibit to examine the description for the SALES and PRODUCTS tables. You want to create a SALE_PROD view by executing the following SQL statement:
Which statement is true regarding the execution of the above statement?
A. The view will be created and you can perform DML operations on the view.
B. The view will be created but no DML operations will be allowed on the view.
C. The view will not be created because the join statements are not allowed for creating a view.
D. The view will not be created because the GROUP BY clause is not allowed for creating a view.
Answer: B
Explanation:
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudocolumn ROWNUM keyword
Columns defined by expressions
NOT NULL columns in the base tables that are not selected by the view
QUESTION 95
Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause.
B. A view that is created with the subquery having the DISTINCT keyword can be updated.
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be updated.
D. A data manipulation language ( DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table.
Answer: CD
Explanation:
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudocolumn ROWNUM keyword
Columns defined by expressions
NOT NULL columns in the base tables that are not selected by the view
QUESTION 96
Which three statements are true regarding views? (Choose three.)
A. Views can be created only from tables.
B. Views can be created from tables or other views.
C. Only simple views can use indexes existing on the underlying tables.
D. Both simple and complex views can use indexes existing on the underlying tables.
E. Complex views can be created only on multiple tables that exist in the same schema.
F. Complex views can be created on multiple tables that exist in the same or different schemas.
Answer: BDF
Explanation:
Creating a Sequence (continued)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory
(By default, the Oracle server caches 20 values.)
QUESTION 97
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
Answer: A
QUESTION 98
View the Exhibit and examine the structure of the ORD table. Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, ’25-jan-2007′,101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
Answer: A
QUESTION 99
When executing a SQL workload, you choose to generate execution plans only, without collecting execution statistics. Which two statements describe the implications of this? (Choose two.)
A. It produces less accurate results of the comparison analysis.
B. It automatically calls the SQL Tuning Advisor for recommendations.
C. It shortens the time of execution and reduces the impact on system resources.
D. Only the changes in the execution plan, and not performance regression, are detected.
Answer: AC
QUESTION 100
Which statements are correct regarding indexes? (Choose all that apply.)
A. When a table is dropped, the corresponding indexes are automatically dropped.
B. A FOREIGN KEY constraint on a column in a table automatically creates a nonunique index.
C. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
D. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
Answer: ACD
PassLeader now are offering 100% pass ensure 1Z0-051 dumps! All 1Z0-051 exam questions have been updated with correct answers, welcome to download the newest PassLeader 1Z0-051 VCE dumps and PDF dumps: http://www.passleader.com/1z0-051.html (303 Q&As)
BTW: Download PassLeader 1Z0-051 dumps from Google Drive for free: https://drive.google.com/open?id=0B-ob6L_QjGLpWXpBUEhyRnVXVlE