Oracle Database: SQL Fundamentals I
Question No: 131 – (Topic 1)
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
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
It executes successfully and displays rows in the descending order of PROMO_CATEGORY
It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement
It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators
Answer: A Explanation:
Using the ORDER BY Clause in Set Operations
The ORDER BY clause can appear only once at the end of the compound query. Component queries cannot have individual ORDER BY clauses.
The ORDER BY clause recognizes only the columns of the first SELECT query.
By default, the first column of the first SELECT query is used to sort the output in an ascending order.
Question No: 132 – (Topic 1)
Which statements are correct regarding indexes? (Choose all that apply.)
For each data manipulation language (DML) operation performed, the corresponding
indexes are automatically updated.
A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
When a table is dropped, the corresponding indexes are automatically dropped
Question No: 133 – (Topic 1)
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:
The promo name should not begin with #39;T#39; or #39;N#39;.
The promo should cost more than $20000.
The promo should have ended after 1st January 2001. Which WHERE clause would give the required result?
WHERE promo_name NOT LIKE #39;T%#39; OR promo_name NOT LIKE #39;N%#39; AND promo_cost
gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;
WHERE (promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39;)OR promo_cost
gt; 20000 OR promo_end_date gt; #39;1-JAN-01#39;
WHERE promo_name NOT LIKE #39;T%#39; AND promo_name NOT LIKE #39;N%#39; AND promo_cost gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;
WHERE (promo_name NOT LIKE #39;%T%#39; OR promo_name NOT LIKE #39;%N%#39;) AND(promo_cost gt; 20000 AND promo_end_date gt; #39;1-JAN-01#39;)
Question No: 134 – (Topic 1)
View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table.
Which two CREATE INDEX statements are valid? (Choose two.)
CREATE INDEX ord_idx1 ON ord(ord_no);
CREATE INDEX ord_idx2
CREATE INDEX ord_idx3 ON ord_items(item_no);
CREATE INDEX ord_idx4
ON ord,ord_items(ord_no, ord_date,qty);
Answer: B,C Explanation:
How Are Indexes Created?
You can create two types of indexes.
Unique index: The Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.
Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY column index for a join in a query to improve the speed of retrieval. Note: You can manually create a unique index, but it is recommended that you create a unique constraint, which implicitly creates a unique index.
Question No: 135 – (Topic 1)
You need to display the date ll-oct-2007 in words as quot;Eleventh of October, Two Thousand Seven#39;. Which SQL statement would give the required result?
SELECT TO_CHAR(#39;ll-oct-2007#39;. #39;miDdspth quot;of Month. Year#39;) FROM DUAL:
SELECT TO_CHAR(TO_DATE(#39;ll-oct-2007#39;X #39;miDdspth of month, year#39;) FROM DUAL;
SELECT TO_CHAR(TO_DATE(#39;ll-oct-2007#39;), #39;miDdthsp quot;of* Month. Year#39;) FROM DUAL;
SELECT TO_DATE(TO_CHAR(#39;ll-oct-20077fiiiDdspth quot;ofquot; Month. Year#39;)) FROM DUAL:
Question No: 136 – (Topic 1)
Evaluate the following SQL commands:
The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)
You cannot use SYSDATE in the condition of a CHECK constraint.
You cannot use the BETWEEN clause in the condition of a CHECK constraint.
You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KEY.
Answer: A,C Explanation: CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees (…
salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary gt; 0),
Question No: 137 – (Topic 1)
You need to perform these tasks:
->Create and assign a MANAGER role to Blake and Clark
->Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark
Which set of SQL statements achieves the desired results?
CREATE ROLE manager; GRANT create table, create view TO manager;
GRANT manager TO BLAKE,CLARK;
CREATE ROLE manager; GRANT create table, create voew TO manager;
GRANT manager ROLE TO BLAKE,CLARK;
GRANT manager ROLE TO BLAKE,CLARK; GRANT create table, create voew
TO BLAKE CLARK;
Explanation: Result of commands:
Question No: 138 – (Topic 1)
Examine the structure of the EMPLOYEES table:
Which UPDATE statement is valid?
UPDATE employees SET first_name = ‘John’ SET last_name = ‘Smith’
WHERE employee_id = 180;
UPDATE employees SET first_name = ‘John’, SET last_name = ‘Smoth’
WHERE employee_id = 180;
UPDATE employee SET first_name = ‘John’
AND last_name = ‘Smith’ WHERE employee_id = 180; D.
SET first_name = ‘John’, last_name = ‘Smith’ WHERE employee_id = 180;
Question No: 139 – (Topic 1)
The SQL statements executed in a user session as follows: Exhibit:
Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)
Both the DELETE statements and the UPDATE statement are rolled back
The rollback generates an error
Only the DELETE statements are rolled back
Only the seconds DELETE statement is rolled back
No SQL statements are rolled back
Question No: 140 – (Topic 1)
Which view should a user query to display the columns associated with the constraints on a table owned by the user?
Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Atable to view all constraints definition and names Bshow all object name belong to user
Cdoes not display column associated Eno such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|