The Secret of 1z0 061 dumps
The article at Testaimer.com going over http://www.testaimer.com/1Z0-061-test is very comprehensive.
Q31. Which two statements are true regarding constraints?
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: B,D
Q32. CORRECT TEXT
View the Exhibit and examine the structure of the promotions table.
You need to generate a report of all promos from the promotions table based on the following conditions:
1. The promo name should not begin with 'T' or 'N'.
2. The promo should cost more than $20000.
3. The promo should have ended after 1st January 2001.
Which where clause would give the required result?
Answer: WHERE promo_name NOT LIKE ‘T%’ AND promo_name NOT LIKE ‘N%’ AND promo_cost > 20000 AND promo_end_date > ‘1-JAN-01'
Q33. Which statement adds a column called salary to the employees table having 100 rows, which cannot contain null?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Reference: http://www.comp.nus.edu.sg/~ooibc/courses/sql/ddl_table.htm (see changing table structures)
Q34. Which create table statement is valid?
A. Option A
B. Option B
C. Option C
D. Option D
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.
Q35. View the Exhibit and examine the structure of the promotions table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. It shows COST_REMARK for all the promos in the table.
B. It produces an error because the SUBQUERY gives an error.
C. It shows COST_REMARK for all the promos in the promo category 'TV'
D. It produces an error because SUBQUERIES cannot be used with the case expression.
Answer: A
Q36. Using the customers table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Explanation: Duplicate Rows Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows.
To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword.
You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.
Q37. Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1)
FROM DUAL;
What would be the outcome?
A. 16
B. 100
C. 160
D. 200
E. 150
Answer: C
Explanation:
Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero
Q38. Which statement is true regarding the UNION operator?
A. By default, the output is not sorted.
B. Null values are not ignored during duplicate checking.
C. Names of all columns must be identical across all select statements.
D. The number of columns selected in all select statements need not be the same.
Answer: D
Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.
Q39. Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement:
SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?
A. Disable the primary key in the STORES table.
B. Use CASCADE keyword with DELETE statement.
C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
D. Disable the FOREIGN KEY in SALES table and then delete the rows.
E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
Answer: A,C,D
Q40. You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with date data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D