ORACLE 
Assignment-A

What are the exceptions? How they declared and used in PL/SQL block. What are the main differences between user-defined exception system-defined exceptions? Write any five system-defined exceptions.

How the various SQL statements are executed within a PL/SQL block. What are the advantages of Using SQL statements within PL/SQL block rather than executing them separately?

 
             
Differentiate the procedures, Functions, and Triggers. What are the advantages of having Subprograms rather than the normal PL/SQL blocks?

4.  What do you mean by Integrity constant? Explain in brief different integrity Constraints available in SQL? SOLVE BY WWW.SOLVEZONE.IN

What is structured Query Language? Explain in brief different types of SQL Sub Language? 

Assignment – 2
1.
 
 
CREATE SALESPEOPLE, CUSTOMER AND ORDER TABLE WITH FOLLOWING 
ATTRIBUTES AND INSERT VALUES IN THEM.

SALESPEOPLE                                 CUSTOMER                           ORDER
SNUM                                                   CNUM                                       ONUM
SNAME                                                CNAME                                     AMT
CITY                                                     CITY                                         O DATE
COMM                                                  RATING                                    CNUM
                                                             SNUM                                       SNUM     
     

2. 

3. What are cursors and for what purpose these are used in PL/SQL block? Explain various cursor attributes.

Assignment – 3
QUESTION 1
"Create table employee (name varchar, id integer) What type of statement is this ?"
        
DML
DDL
View
Integrity constraint

QUESTION 2
What Oracle backup and recover file contains user and system data?
        
Control file
Datafile
OnLine ReDo file
Offline ReDo file

QUESTION 3
"To remove a relation from an SQL database, we use the ______ command."
        
Delete
Purge
Remove
Drop table

QUESTION 4

"When the correct SQL command is used to delete a table's structure, what happens to the data in the table?"

"If the deleted table was a parent table, the data is added to the appropriate rows of the child table."
"If the deleted table was a child table, the data is added to the appropriate rows of the parent table."
The data in the table is also deleted.
Nothing because there was no data in the table - only an empty table can be deleted.

QUESTION 5
Which of the following database design features is most important to SQL performance?
        
Removal of data redundancy
The introduction of data redundancy
The introduction of non-first normal form relations
The introduction of SQL*Plus

QUESTION 6
SQL*Plus will finish the statement and execute it when the user types in this:
        
A left slash ( \ ) followed by [Enter].
A colon ( : ) followed by [Enter].
A semicolon ( ; ) followed by [Enter].
A period ( . ) followed by [Enter].

QUESTION 7
"Which SQL keyword is used to change the structure, properties or constraints of a table?"
        
SET
CREATE
SELECT
ALTER

QUESTION 8
Which of the following is not true about indexes?

Indexes are created to enforce uniqueness on columns.
Indexes are created to enable fast retrieval by column values.
Columns that are frequently used with equal conditions in WHERE clauses are good candidates for indexes.
Indexes are created with the ALTER TABLE command.

QUESTION 9
What is the type of Oracle backup in which all uncommitted changes have been removed from the data files?
        
Full backup
Consistent backup
Inconsistent backup
Differential backup

QUESTION 10
"Which one of the following is used to define the structure of the relation, deleting relations and relating schemas ?"
        
DML(Data Manipulation Langauge)
DDL(Data Definition Langauge)
Query
Relational Schema

QUESTION 11
Which of the following object types below cannot be replicated?
        
Data
Trigger
View
Sequence

QUESTION 12
An Oracle ..is a set of tables and views that are used as a read-only reference about the database.
        
Database dictionary
Dictionary table
Data dictionary
Dictionary

QUESTION 13
Which of the following SQL operations demands the use of wild cards comparisons?
        
IN
BETWEEN
EXISTS
LIKE

QUESTION 14

Which of the following statements would not be valid in both a client Forms PL/SQL object and a stored PL/SQL object?
        
SELECT LAST_NAME INTO v_ last_name FROM STUDENT;        
OPEN CURSOR c_student;
FIND_ITEM(v_item);
END;

QUESTION 15

"Which has enhancements in the input/output operation of disk, scalability, locking of row, backup and recovery:"
        
Oracle V6
Oracle V5
Oracle V3
Oracle V4

QUESTION 16
"By, default, Oracle object types are”
        
INSTANTIABLE
NOT INSTANTIABLE
FINAL
OVERRIDING

QUESTION 17
Oracle database 11g was released in_________:
        
2001
2002
2006
2007

QUESTION 18

The LIKE operator is used in place of an .sign.
        
' + '
' = '
' * '
' - '

QUESTION 19
The command to remove rows from a table 'CUSTOMER' is:

DROP FROM CUSTOMER.
REMOVE FROM CUSTOMER
DELETE FROM CUSTOMER WHERE.
UPDATE FROM CUSTOMER

QUESTION 20
Which of the following is NOT an Oracle-supported trigger?
        
BEFORE
DURING
AFTER
INSTEAD OF

QUESTION 21
Regarding the interchangeability of subqueries and joins :
        
"A join can always be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join."
"A join can sometimes be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join."
"A join can always be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join."
"A join can never be used as an alternative to a subquery, and a subquery can never be used as an alternative to a join."

QUESTION 22
What is true about sequences?
        
The maximum value of the descending sequence defaults to 1
The minimum value of an ascending sequence defaults to 1.
The start value of the sequence is always 1.
A sequence always increments by 1.

QUESTION 23
"In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?"
        
After the WHERE clause
Before the FROM clause
Immediately after the SELECT clause
Before the WHERE clause

QUESTION 24
Which of the following is not true of SQL views?
        
Oracle views cannot use the ORDER BY clause in view definitions.
Oracle views are created using the standard SQL-92 CREATE VIEW command.
Oracle views can be queried.
The SQL-92 standard does not allow the use of the ORDER BY clause in view definitions.

QUESTION 25
The maximum length of all Oracle Objects in 9i:
        
30
15
12
25

QUESTION 26
Which of the following cannot be done with a WHEN-NEW-FORMS-INSTANCE trigger?
        
It cannot be created in the same form as a PRE-FORM trigger
It cannot be created at the item level
cannot use the SET_ITEM_PROPERTY in it
cannot use it to set block properties

QUESTION 27
The SQL keyword is used:

for ranges.
to limit the columns displayed.
as a wildcard.
as a query
0.75 points   

QUESTION 28
A subquery in an SQL SELECT statement:
        
can only be used with two tables.
can always be duplicated by a join.
has a distinct form that cannot be duplicated by a join.
cannot have its results sorted using ORDER BY.

QUESTION 29
What is the scope within which triggers fire?

The object they are attached to
The PL/SQL block
The user s session
The PL/SQL Editor

QUESTION 30
"A ____________________ is a structure that contains definitions, blocks of PL/SQL code, or both in a single unit."
        
Package body
Package
Procedure
trigger

QUESTION 31
Each tablespace in an Oracle database consists of one or more files called :
        
Files
namespace
datafiles
PFILE

QUESTION 32
Which are valid Oracle constraint types?
        
CASCADE
NOT NULL
CONSTANT
NONUNIQUE

QUESTION 33
Referential integrity constraints are used to limit the possible values of a ________.
        
composite key
candidate key
foreign key
surrogate key

QUESTION 34
What among the following is a type of Oracle SQL functions?
        
Multiple-row functions
Single column functions
Single value functions
Multiple columns functions

QUESTION 35
Forms triggers are written in which of the following languages ?
        
C
PL/SQL
ActiveX
C++

QUESTION 36
Which SQL keyword is used to delete a table's structure?
        
DELETE
DROP
DISPOSE
ALTER

QUESTION 37
"In an SQL query, which SQL keyword is used to determine if a column value is equal to any one of a set of values?"
        
AND
EXISTS
HAVING
IN

QUESTION 38
One advantage of using the ALTER command to define a primary is that the database designer controls the:

name of the primary key
name of the foreign key
name of the constraint
name of the unique key

QUESTION 39
"In creating a procedure, you may get a message if you have compile errors. Which of the following is true?"

The line numbers reported match the line numbers you see in your text editor.
SQL*Plus will automatically show the errors to you.
"To see the errors, enter SHOW ERRORS in SQL*Plus."
"If there are no syntax errors, you will receive the message ""NO ERRORS."""

QUESTION 40
Updates that violate __________ are disallowed.

Integrity constraints
Transaction control
Authorization
DDL constraints