1) What is the difference between DDL,DML AND DCL?
Ans:
DDL: These Statements are used to define the data.
Ex: Create,Alter,Drop,Truncate,Rename
Create : To create objects in the database
Alter : This command is used to alter the structure of the database
Drop : This is used to delete objects from the database
Truncate: This is used to remove all records from a table including all spaces allocated for the records are removed
Rename : This is used to Rename the objects
DML: This statements are used to manage the data or manipulating the data.
Ex: Select,Insert,update,Delete
Select: This command is used to retrieve from the database
Insert: This command is used to insert data into a table
Update: This is used to updates existing data in a table
Delete : This is used to Deletes all records from a table but the space for the records remain.
DCL: These statements are used for control the access of data.
Ex : Grant,Revoke
Grant: This command is used to gives user's access privileges to database
Revoke: This command is used to withdraw access privileges Given with the Grant command.
2) What is Commit,SavePoint and Rollback?
Ans:
Commit:
This command is used to makes changes to the current Transaction Permanent. It erases the savepoints and releases the transaction locks.
Savepoint: Savepoints allow to arbitrarily hold work at any point of time with option of later
Rollback : This statement is used to undo a work.
3) How do we Tune the Queries?
Ans : Queries can be Tuned by checking the logic like table joins and by creating indexes on Objects in the where clause. Finally use the trace utility to generate the trace file,use the TK-Prof utility to generate a statistical analysis about the query using which appropriate actions can be taken.
4) What is Trigger? How many types of Triggers are there?
Ans: Trigger is a procedure that gets implicitly executed when an insert/update/delete is issued against an associate table. Triggers can only be defined on only tables not on views. There are two types of Triggers They are
Statement Level Trigger
Row Level Trigger
5) What is anonymous block?
Ans: Anonymous block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in the database schema and it is also not compiled and saved in server storage. So it needs to be parsed and executed each time it is run.However this simple form of program can use variable,can have flow of control logic,can return query results into variables can prompt the user for input using SQL *PLUS feature as any stored procedure.
6) What is Pragma Exception_Init? Explain Its Usage?
Ans: Pragma Exception_Init is used to handle undefined Exceptions. It issues a directive to the compiler asking it to the associate an exception to the Oracle error. Thereby displaying a specific error message pertaining to the error occurred.
7) What are Exceptions? How many types of Exceptions are there?
Ans: Exceptions are conditions that cause the termination of block.
There are two types of Exceptions. They are
a)Pre defined Exception
b) User defined Exception
a) Pre defined Exception: These types of Exceptions already defined by PL/SQL and are associated with specific errors.
b) User defined Exception: These types of Exceptions are declared by user and raised on deliberate request like breaking a condition etc..
Exception handlers are used to handle exceptions that are raised.
8) What are modes for passing parameters in Oracle?
Ans: There are three modes for passing parameters to subprograms.
1. IN : An IN parameter lets you pass values to subprograms being called.In subprogram it acts like a constant and can not be assigned a value.
2. OUT: An Out parameter lets you return values to the caller of the subprogram.It is acts like an initialized variable its value can not be assigned to another variable or to itself.
3. INOUT: An IN-OUT parameter lets you pass the initial values to subprogram being called and returns updated values to the caller.
9) What is Cursor? How many types of Cursor are there?
Ans: Cursor is an identifier or name to a work area that we can interact with to access its information.A cursor points to the current row in the result set fetched.
There are Three types of cursors. They are
a) Implicit Cursor: These type of Cursor created automatically by PL/SQL for all SQL DML statement such as Insert,Update,Delete and Select
b) Explicit Cursor: These type of Cursor is created Explicitly. They create a storage area where a set of rows returned by a query a placed.
Main functionality of Explicit Cursor is Declaring the cursor,Opening the cursor,Fetching the data,Closing the cursor (that means releasing the work area )
c) Dynamic Cursor: These type of Cursor is Ref Cursors( Used for runtime modification of the select query)
10) What is difference between Truncate and Delete Statement?
Ans:
Truncate: When we used this statement data is lost permanently and can not be retrieved even by rollback.
Note: Truncate statement does not use rollback segment during its execution hence it is fast.
Delete: This statement is used Data is lost but can be retrieved back by rollback.
Note: Delete statement does not free up the table object allocated space.
11)What is PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?
Ans:
PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table.
12)What are Views and why they are used?
A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
Views are used:
To restrict data access
To make complex queries easy
To provide data Independence
Views provide groups of user to access data according to their requirement.
13) How can we find out the current date and time in Oracle?
Ans: We can find the current Date & Time using SYSDATE in Oracle.
Syntax:
SELECT SYSDATE into CURRENT_DATE from dual;
14)When do we use GROUP BY clause in a SQL Query?
Ans: GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG etc.
Syntax:
SELECT COLUMN_1, COLUMN_2
FROM TABLENAME
WHERE [condition]
GROUP BY COLUMN_1, COLUMN_2
15) What is the use of Aggregate functions in Oracle?
Ans: Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations.
Few of them are listed below:
AVG
MIN
MAX
COUNT
SUM
STDEV
Ans:
DDL: These Statements are used to define the data.
Ex: Create,Alter,Drop,Truncate,Rename
Create : To create objects in the database
Alter : This command is used to alter the structure of the database
Drop : This is used to delete objects from the database
Truncate: This is used to remove all records from a table including all spaces allocated for the records are removed
Rename : This is used to Rename the objects
DML: This statements are used to manage the data or manipulating the data.
Ex: Select,Insert,update,Delete
Select: This command is used to retrieve from the database
Insert: This command is used to insert data into a table
Update: This is used to updates existing data in a table
Delete : This is used to Deletes all records from a table but the space for the records remain.
DCL: These statements are used for control the access of data.
Ex : Grant,Revoke
Grant: This command is used to gives user's access privileges to database
Revoke: This command is used to withdraw access privileges Given with the Grant command.
2) What is Commit,SavePoint and Rollback?
Ans:
Commit:
This command is used to makes changes to the current Transaction Permanent. It erases the savepoints and releases the transaction locks.
Savepoint: Savepoints allow to arbitrarily hold work at any point of time with option of later
Rollback : This statement is used to undo a work.
3) How do we Tune the Queries?
Ans : Queries can be Tuned by checking the logic like table joins and by creating indexes on Objects in the where clause. Finally use the trace utility to generate the trace file,use the TK-Prof utility to generate a statistical analysis about the query using which appropriate actions can be taken.
4) What is Trigger? How many types of Triggers are there?
Ans: Trigger is a procedure that gets implicitly executed when an insert/update/delete is issued against an associate table. Triggers can only be defined on only tables not on views. There are two types of Triggers They are
Statement Level Trigger
Row Level Trigger
5) What is anonymous block?
Ans: Anonymous block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in the database schema and it is also not compiled and saved in server storage. So it needs to be parsed and executed each time it is run.However this simple form of program can use variable,can have flow of control logic,can return query results into variables can prompt the user for input using SQL *PLUS feature as any stored procedure.
6) What is Pragma Exception_Init? Explain Its Usage?
Ans: Pragma Exception_Init is used to handle undefined Exceptions. It issues a directive to the compiler asking it to the associate an exception to the Oracle error. Thereby displaying a specific error message pertaining to the error occurred.
7) What are Exceptions? How many types of Exceptions are there?
Ans: Exceptions are conditions that cause the termination of block.
There are two types of Exceptions. They are
a)Pre defined Exception
b) User defined Exception
a) Pre defined Exception: These types of Exceptions already defined by PL/SQL and are associated with specific errors.
b) User defined Exception: These types of Exceptions are declared by user and raised on deliberate request like breaking a condition etc..
Exception handlers are used to handle exceptions that are raised.
8) What are modes for passing parameters in Oracle?
Ans: There are three modes for passing parameters to subprograms.
1. IN : An IN parameter lets you pass values to subprograms being called.In subprogram it acts like a constant and can not be assigned a value.
2. OUT: An Out parameter lets you return values to the caller of the subprogram.It is acts like an initialized variable its value can not be assigned to another variable or to itself.
3. INOUT: An IN-OUT parameter lets you pass the initial values to subprogram being called and returns updated values to the caller.
9) What is Cursor? How many types of Cursor are there?
Ans: Cursor is an identifier or name to a work area that we can interact with to access its information.A cursor points to the current row in the result set fetched.
There are Three types of cursors. They are
a) Implicit Cursor: These type of Cursor created automatically by PL/SQL for all SQL DML statement such as Insert,Update,Delete and Select
b) Explicit Cursor: These type of Cursor is created Explicitly. They create a storage area where a set of rows returned by a query a placed.
Main functionality of Explicit Cursor is Declaring the cursor,Opening the cursor,Fetching the data,Closing the cursor (that means releasing the work area )
c) Dynamic Cursor: These type of Cursor is Ref Cursors( Used for runtime modification of the select query)
10) What is difference between Truncate and Delete Statement?
Ans:
Truncate: When we used this statement data is lost permanently and can not be retrieved even by rollback.
Note: Truncate statement does not use rollback segment during its execution hence it is fast.
Delete: This statement is used Data is lost but can be retrieved back by rollback.
Note: Delete statement does not free up the table object allocated space.
11)What is PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?
Ans:
PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table.
12)What are Views and why they are used?
A View logically represents subsets of data from one or more table. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
Views are used:
To restrict data access
To make complex queries easy
To provide data Independence
Views provide groups of user to access data according to their requirement.
13) How can we find out the current date and time in Oracle?
Ans: We can find the current Date & Time using SYSDATE in Oracle.
Syntax:
SELECT SYSDATE into CURRENT_DATE from dual;
14)When do we use GROUP BY clause in a SQL Query?
Ans: GROUP BY clause is used to identify and group the data by one or more columns in the query results. This clause is often used with aggregate functions like COUNT, MAX, MIN, SUM, AVG etc.
Syntax:
SELECT COLUMN_1, COLUMN_2
FROM TABLENAME
WHERE [condition]
GROUP BY COLUMN_1, COLUMN_2
15) What is the use of Aggregate functions in Oracle?
Ans: Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations.
Few of them are listed below:
AVG
MIN
MAX
COUNT
SUM
STDEV
No comments:
Post a Comment