Q~: What is Library Cache Locks
When a database object (such as a table, view, procedure, function, package, package body, trigger, index, cluster, or synonym) is referenced during parsing or compiling of a structured query language (SQL), (Data Manipulation Language (DML) or Data Definition Language (DDL), PL/SQL, or Java statement, the process parsing or compiling the statement acquires the library cache lock in the correct mode. In Oracle9i, the lock is held only until the parse or compilation completes (for the duration of the parse call).
Q~:.How can I get an environmental variable into SQL*Plus variables?
a. Create a sequence accessed in glogin.sql (e.g. my_seq)
This permits you to have a "unique" number to build
a file that will not conflict with another process.
Column x old_value tmp_num noprint;
Set heading off
Set pause off
Select my_seq.newvalue x from dual;
b. Create a getenv.sql script like the following
host echo "define &1=\"$&1\"" $HOME/s&&tmp_num..sql
start $HOME/s&&tmp_num..sql
host rm $HOME/s&&tmp_num..sql
Q~:. What is RDBMS? What are different database models?
RDBMS: Relational Database Management System.
In RDBMS the data are stored in the form of tables
i.e. rows & columns.
The different database models
are 1. HDBMS = Hierarchical Database Management system.
2. NDBMs = Network Database Management System.
3. RDBMS = Relational Database Management System.
Q~:. What is SQL?
SQL stands for Structured Query Language. SQL was derived from the
Greek word called "SEQUEL". SQL is a non- procedural language that
is written in simple English.
Q~:. What is a transaction?
Transaction is a piece of logical unit of work done
between two successive commits or commit and rollback.
Q~:. What is a commit?
Commit is transaction statements that make the changes permanent
into the database.
Q~:. What is a Rollback?
Rollback is a transaction statement that undoes all changes to a savepoint or since the beginning of the transaction.
Q~:. What is DDL?
DDL - Data Definition Language.
It is a set of statements that is used to define or alter the
User_defined objects like tables, views, procedures, functions etc.,
present in a tablespace.
Q~:. What is DML?
DML - Data Manipulation Language.
It is a set of statements that is used for manipulation of data.
E.g. inserting a row into a table, delete a row from a table etc.
Q~:. What is locking?
The mechanism followed by the SQL to control concurrent operations
On a table is called locking.
Q~:. What is a Dead lock?
When two users attempt to perform actions that interfere with one
another,this situation is defined as Deadlock.
E.g.: - If two users try to change both a foreign and its parent key
value at the same time.
Q~:. What is a Shared Lock?
The type of lock that permits other users to perform a query, but
could not manipulate it, i.e. it cannot perform any modification
or insert or delete a data.
Q~:. What is Exclusive Lock?
The type of lock that permits users to query data but not change
it and does not permits another user to any type of lock
on the same data. They are in effect until the end of the transaction.
Q~:. What is Share Row-Exclusive lock?
Share Row Exclusive locks are used to look at a whole table and to
allow others to look at rows in the table but to prohibit others
from locking the table in Share mode or updating rows.
Q~:. What is Group - Functions?
The Functions that are used to get summary information’s about group
Or set of rows in a table.
The group functions are also termed as aggregate functions.
Following are the examples of aggregate functions:
1. AVG () - To find the average value
2. MIN () - To find the minimum value of the set of rows.
3. MAX () - To find the maximum value of the set of rows.
4. COUNT () - To find the total no of rows that has values.
5. SUM () - To find the summation of the data of a given column.
Q~:. What is indexing?
An index is an ordered list of the contents of a column or a group of
columns of a table.
By indexing a table, it reduces the time in performing queries,
especially if the table is large.
Q~:. What are clusters?
A Cluster is a schema object that contains one or more tables that have
one or more columns in common. Rows of one or more tables that share
the same value in these common columns are physically stored together
within the database.
Q~:. What is a View?
View is like a window through which you can view or change the information in table. A view is also termed as a 'virtual table'.
Q~:. What is a Rowid?
For each row in the database, The ROWID pseudo column returns a row's
address. ROWID values contain information necessary to locate a row:
* Which datablock in the data file
* Which row in the datablock (first row is 0)
* Which data file (first file is 1)
Values of the Rowid pseudocolum have the datatype ROWID.
Q~:. What is a PRIMARY KEY?
PRIMARY KEY CONSTRAINT:
Q~:. Identified the columns or set of columns, which uniquely identify each row of a table and ensure that no duplicate rows exist in the table.
2. Implicitly creates a unique index for the column (S) and
specifies the column(s) as being NOT NULL.
3. The name of the index is the same as the constraint name.
4. Limited to one per table.
Example:
CREATE TABLE loans (account NUMBER (6),
loan_number NUMBER(6),
...
CONSTRAINT loan_pk PRIMARY KEY
(account, loan_number));
Q~:. What is a Unique constraint?
UNIQUE Constraint:
1. Ensures that no two rows of a table have duplicate values
in the specified columns(s).
2. Implicitly creates a unique index on the specified columns.
3. Index name is the given constraint name.
Example:
CREATE TABLE loans (
Loan_number NUMBER (6) NOT NULL UNIQUE,
...
);
Q~:. What is the difference between a unique and primary key?
The Primarykey constraint is a constraint that takes care maintaining
the uniqueness of the data, enforcing the not null characteristic,
creates a self-index.
The Unique key constraint maintains only the uniqueness of the
data and does not enforce the not null characteristic to the
data column.
Q~:. What is a foreign key?
FOREIGN KEY Constraint:
Q~:. Enforces referential integrity constraint, which requires that for each row of a table, the value in the foreign key matches a value in the primary key or is null.
2. No limit to the number of foreign keys.
3. Can be in the same table as referenced primary key.
4. Can not reference a remote table or synonym.
Examples:
1. Explicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10) ,
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer (account),
...);
2. Implicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10),
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer,
...);
Q~:. What is data integrity? What are the types of integrity?
1. A mechanism used by the RDBMS to prevent invalid data entry
into the base tables of the database.
2. Defined on tables so conditions remain true regardless of
method of data entry or type of transactions.
The following are the type of integrity
* Entity integrity
* Referential Integrity
* General Business rules
Q~:. What is a Referential Integrity?
1. Enforces master/detail relationship between tables based on keys.
* Foreign key
* Update Delete restricts action
* Delete Cascade action
Q~:. What are different data types?
The following are the different data types available in Oracle
Q~:. Internal Data types
Q~:. Composite Data types
Internal Data types
Q~:. Character Datatype
Q~:. Date Datatype
Q~:. Row and long row data types
Q~:. Rowid Datatype
Composite Data types
Q~:. Table Data type
Q~:. Record Data type
Q~:. What is VARCHAR2? How is it different CHAR?
The Varchar2 datatype specifies a variable length character string. When you create a varchar2 column, you can supply the maximum number of bytes of data that it can hold. Oracle Subsequently stores each value in the column exactly as you specify. If you try to insert a value that exceeds this length, Oracle returns an error.
The Char datatype length is 1byte. The maximum size of the Char datatype is 255. Oracle compares Char values using the blank-padded comparison semantics. If you insert a value that is shorter than the column length, Oracle blank-pads the value to the column length.
Q~:. What is datatype mixing?
There are two data types %TYPE and %ROWTYPE. The first one declares
a variable to be of the data type of the column of the table to which it is referring.
For example, if you declare a variable like this:
my_empno emp.empno%TYPE
then, my_empno will have the data type of the empno column of the table emp.
Similarly if you declare the variable like this:
my_emprec emp%ROWTYPE
then, my_emprec will have the data type of all the fields of the emp table.
For example. Suppose employee table is like this:
empno number(2),
empname varchar2(10),
Sal number (10,2).
Then my_emprec will be of a data type whose first 2 positions will be of number data type, the next 10 will be of varchar2 data type while the last 10 will be of data type number.
One can refer to the individual fields of this record variable as
my_emprec.empno, my_emprec.empname, my_emprec.sal.
Q~:. What is NULL?
A data field without any value in it is called a null value.
A Null can arise in the following situation
* Where a value is unknown.
* Where a value is not meaningful (i.e.) in column representing
commission for a row that does not represent salesman.
Q~:. What is a sequence?
A sequence is a database object from which multiple users may generate
unique integers.
Q~:. What are pseudo-columns in ORACLE?
The columns that are not part of the table are called as pseudo columns.
Q~:. What is like operator? How is it different from IN operator?
The type of operator that is used in character string comparisons
with pattern matching.
Like operator is used to match a portion of the one character string
to another whereas IN operator performs equality condition between
two strings.
Q~:. What is Single Row numbers Functions?
The type of function that will return value after every row is being
processed.
Following are some of the row number functions.
Function Name Purpose
1. ABS (n) returns the absolute value of a number
2. Floor (n) returns the largest integer value
equal or less than n.
3. Mod (m, n) returns the remainder of m divided by n.
4. Power (m, n) returns m raised to the n power.
5. Round (n) returns n rounded to m places
right of a decimal point.
6. Sqrt (x) returns the sqrt value of x.
7. Trunk (n, m) Returns n truncated to m decimal
places.
Q~:. What are single row character functions?
The function that processes at value of data, which is of character datatype, and returns a character datatype after every row is being processed are termed as single row character functions.
Function Name Purpose.
Q~:. Char (n) returns the character having
an ASCII value.
Q~:. Initcap (n) returns character with first
letter of each argument in
UPPERCASE.
Q~:. Lower (n) returns characters with
all the letters forced to
lower case.
Q~:. ltrim(n) Removes the spaces towards
the left of the string.
Q~:. upper(n) Returns characters with
all the letters forced to
upper case.
Q~:. What are Conversion Functions?
The functions used to convert a value from one datatype to another
are being termed as conversion functions.
Function Name Purpose
To_char (n, (fmt)) converts a value of number datatype
to a value of character datatype.
To_number (n) converts a character value into a number.
Rowidtochar (n) converts rowid values to character datatype.
the result of this conversion is always
18 character long.
Q~:. What are Date functions?
Functions that operate on Oracle Dates are termed as Date functions.
All date functions return a value of date datatype except the \
function months_between returns a numeric value.
Function Purpose
ADD_MONTHS (d, n) returns the date 'd' plus n months.
n must be an integer.
n can be positive or negative.
LAST_DAY (d) returns the date of the last day
of the month containing the date 'd'.
NEXT_DAY (d, char) returns date of first day of week
named after char that is later than
d, char must be a valid day of the
week.
MONTHS_BETWEEN (d, e) returns no of months between dates
d & e.
Q~:. What is NEW_TIME function?
SYNTAX: New_time (d, a, b)
New_time function returns date and time in a time zone b and time in time zone. a and b are character expressions.
Following are the some of the character expressions:
Character expression Description
AST Atlantic Stand or daylight time
BST, BDT Burning stand or daylight time
GMT Greenwich Mean Time.
PST, PDT Pacific Standard Time.
YST, YDT Yukon standard or daylight time.
Q~:. What is Convert function?
Convert function converts two different implementations of the
same character set .
For instance: from DEC 8 bit multi-lingual characters to HP 8 bit
Multi-lingual character set.
Following are the character sets
US7ASCII - US7bit ASCII character set
WE8DEC - Western European 8 bit ASCII set
WE8HP - HP's Western European 8 bit ASCII set
F7DEC - DEC's French 7-bit ASCII set
Convert (char [destination], [source])
Q~:. What is a translate function?
The function that returns a character after replacing all occurrences
of the character specified with the corresponding character is called
as translate function.
E.g. TRANSLATE ('Hello','l','L') gives you HeLLo
Q~:. What is a soundex function?
Soundex is a function that returns a character string representing
the sound of the words in char. This function returns a phonetic
representation of each word and allows you to compare words
that are spelled differently but sound alike.
Soundex (char);
Q~:. What is a replace function?
Replace function returns character with every occurrence of the
search string replaced with the replacement string. If the replacement
string is not supplied, all occurrences of search_string are being
removed. Replace allows you to substitute one string from another.
Q~:. What is a Floor function?
Floor Function returns the largest integer equal to or than n
Syntax: floor (n);
Q~:. What is INITCAP Function?
The initcap function returns char,with first letter of each word in uppercase, all other letters in lowercase. A word is delimited by white space
Q~:. What is ASCII Function?
The ASCII function returns the collating sequence of the first character of lchar. There is no corresponding EBCDIC function.
On EBCDIC systems, the ASCII function will return EBCDIC collating
sequence values.
Q~:. What is a Decode Function?
The Decode function is used to compare an expression to each search
value and returns the result if expr equals the search value.
E.g.: Decode (expr, search1, result1, [search2, result2], [default]);
Q~:. What is Greatest Function?
The Greatest function returns the greatest of a list of values. All expr after the first are converted to the datatype of the first before
comparison is done.
Q~:. What are Format models?
Format models are used to affect how column values are displayed when
a format retrieved with a select command. Format models
do not affect the actual internal representation of the column.
Q~:. Give 5 examples for DATE, Number function?
Examples for Number Function:
Q~:. Select abs (-15) "Absolute:" from dual
Q~:. Select mod (7,5) "Modula" from dual
Q~:. Select round (1235.85,1) from dual
Q~:. Select power (2,3) from dual
Q~:. Select floor (7.5) "Floor" from dual
Examples for Date Function
Q~:. Select sysdate from dual
Q~:. Select sysdate-to_date (23-Sep-93) from dual
Q~:. Select sysdate + 90 from dual
Q~:. Select sysdate -90 from dual
Q~:. Select next_day (sysdate,"Friday") from dual
Q~:. What is an expression?
An expression is a group of value and operators which may be evaluated a single values.
Q~:. What are the types of expression?
The different types of expressions are
Q~:. Logical expression
Q~:. Compound expression
Q~:. Arithmetic expression
Q~:. Negating expression.
Q~:. What is a synonym?
The synonym is a user-defined object that is used to define an alias name for the user defined objects like table view etc.
Q~:. What is a condition?
A Condition could be said to be of the logical datatype that evaluates
the expression to a True or False value.
Q~:. What are the 7 forms of condition?
There are totally 7 forms of condition
Q~:. A comparison with expression or subquery results.
Q~:. A comparison with any or all members in a list or a subquery
Q~:. A test for membership in a list or a subquery
Q~:. A test for inclusion in a range
Q~:. A test for nulls.
Q~:. A test for existence of rows in a subquery
Q~:. A test involving pattern matching
Q~:. A combination of other conditions
Q~:. What are cursors?
Oracle uses work areas called private SQL areas to execute SQL statements and store processing information. This private SQL work area is known as cursors.
Q~:. What are explicit cursors?
Cursors that are defined for performing a multiple row select are known
as explicit cursors.
Implicit cursors are the type of cursors that is implicitly opened
by the Oracle itself whenever you perform any DML statements like
Update, delete, insert or select into statements.
Q~:. What is a PL/SQL?
PL/SQL is a transaction processing language that offers procedural
solutions.
Q~:.What is an embedded SQL?
All the SQL statements written in a Host language are known
as Embedded SQL statements.
Q~:. What are the different conditional constructs of PL/SQL?
The statements that are useful to have a control over the set
of the statements being executed as a single unit are called as
conditional constructs.
When a database object (such as a table, view, procedure, function, package, package body, trigger, index, cluster, or synonym) is referenced during parsing or compiling of a structured query language (SQL), (Data Manipulation Language (DML) or Data Definition Language (DDL), PL/SQL, or Java statement, the process parsing or compiling the statement acquires the library cache lock in the correct mode. In Oracle9i, the lock is held only until the parse or compilation completes (for the duration of the parse call).
Q~:.How can I get an environmental variable into SQL*Plus variables?
a. Create a sequence accessed in glogin.sql (e.g. my_seq)
This permits you to have a "unique" number to build
a file that will not conflict with another process.
Column x old_value tmp_num noprint;
Set heading off
Set pause off
Select my_seq.newvalue x from dual;
b. Create a getenv.sql script like the following
host echo "define &1=\"$&1\"" $HOME/s&&tmp_num..sql
start $HOME/s&&tmp_num..sql
host rm $HOME/s&&tmp_num..sql
Q~:. What is RDBMS? What are different database models?
RDBMS: Relational Database Management System.
In RDBMS the data are stored in the form of tables
i.e. rows & columns.
The different database models
are 1. HDBMS = Hierarchical Database Management system.
2. NDBMs = Network Database Management System.
3. RDBMS = Relational Database Management System.
Q~:. What is SQL?
SQL stands for Structured Query Language. SQL was derived from the
Greek word called "SEQUEL". SQL is a non- procedural language that
is written in simple English.
Q~:. What is a transaction?
Transaction is a piece of logical unit of work done
between two successive commits or commit and rollback.
Q~:. What is a commit?
Commit is transaction statements that make the changes permanent
into the database.
Q~:. What is a Rollback?
Rollback is a transaction statement that undoes all changes to a savepoint or since the beginning of the transaction.
Q~:. What is DDL?
DDL - Data Definition Language.
It is a set of statements that is used to define or alter the
User_defined objects like tables, views, procedures, functions etc.,
present in a tablespace.
Q~:. What is DML?
DML - Data Manipulation Language.
It is a set of statements that is used for manipulation of data.
E.g. inserting a row into a table, delete a row from a table etc.
Q~:. What is locking?
The mechanism followed by the SQL to control concurrent operations
On a table is called locking.
Q~:. What is a Dead lock?
When two users attempt to perform actions that interfere with one
another,this situation is defined as Deadlock.
E.g.: - If two users try to change both a foreign and its parent key
value at the same time.
Q~:. What is a Shared Lock?
The type of lock that permits other users to perform a query, but
could not manipulate it, i.e. it cannot perform any modification
or insert or delete a data.
Q~:. What is Exclusive Lock?
The type of lock that permits users to query data but not change
it and does not permits another user to any type of lock
on the same data. They are in effect until the end of the transaction.
Q~:. What is Share Row-Exclusive lock?
Share Row Exclusive locks are used to look at a whole table and to
allow others to look at rows in the table but to prohibit others
from locking the table in Share mode or updating rows.
Q~:. What is Group - Functions?
The Functions that are used to get summary information’s about group
Or set of rows in a table.
The group functions are also termed as aggregate functions.
Following are the examples of aggregate functions:
1. AVG () - To find the average value
2. MIN () - To find the minimum value of the set of rows.
3. MAX () - To find the maximum value of the set of rows.
4. COUNT () - To find the total no of rows that has values.
5. SUM () - To find the summation of the data of a given column.
Q~:. What is indexing?
An index is an ordered list of the contents of a column or a group of
columns of a table.
By indexing a table, it reduces the time in performing queries,
especially if the table is large.
Q~:. What are clusters?
A Cluster is a schema object that contains one or more tables that have
one or more columns in common. Rows of one or more tables that share
the same value in these common columns are physically stored together
within the database.
Q~:. What is a View?
View is like a window through which you can view or change the information in table. A view is also termed as a 'virtual table'.
Q~:. What is a Rowid?
For each row in the database, The ROWID pseudo column returns a row's
address. ROWID values contain information necessary to locate a row:
* Which datablock in the data file
* Which row in the datablock (first row is 0)
* Which data file (first file is 1)
Values of the Rowid pseudocolum have the datatype ROWID.
Q~:. What is a PRIMARY KEY?
PRIMARY KEY CONSTRAINT:
Q~:. Identified the columns or set of columns, which uniquely identify each row of a table and ensure that no duplicate rows exist in the table.
2. Implicitly creates a unique index for the column (S) and
specifies the column(s) as being NOT NULL.
3. The name of the index is the same as the constraint name.
4. Limited to one per table.
Example:
CREATE TABLE loans (account NUMBER (6),
loan_number NUMBER(6),
...
CONSTRAINT loan_pk PRIMARY KEY
(account, loan_number));
Q~:. What is a Unique constraint?
UNIQUE Constraint:
1. Ensures that no two rows of a table have duplicate values
in the specified columns(s).
2. Implicitly creates a unique index on the specified columns.
3. Index name is the given constraint name.
Example:
CREATE TABLE loans (
Loan_number NUMBER (6) NOT NULL UNIQUE,
...
);
Q~:. What is the difference between a unique and primary key?
The Primarykey constraint is a constraint that takes care maintaining
the uniqueness of the data, enforcing the not null characteristic,
creates a self-index.
The Unique key constraint maintains only the uniqueness of the
data and does not enforce the not null characteristic to the
data column.
Q~:. What is a foreign key?
FOREIGN KEY Constraint:
Q~:. Enforces referential integrity constraint, which requires that for each row of a table, the value in the foreign key matches a value in the primary key or is null.
2. No limit to the number of foreign keys.
3. Can be in the same table as referenced primary key.
4. Can not reference a remote table or synonym.
Examples:
1. Explicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10) ,
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer (account),
...);
2. Implicit reference to a PRIMARY KEY column
CREATE TABLE accounts (
account NUMBER(10),
CONSTRAINT borrower FOREIGN KEY (account)
REFERENCES customer,
...);
Q~:. What is data integrity? What are the types of integrity?
1. A mechanism used by the RDBMS to prevent invalid data entry
into the base tables of the database.
2. Defined on tables so conditions remain true regardless of
method of data entry or type of transactions.
The following are the type of integrity
* Entity integrity
* Referential Integrity
* General Business rules
Q~:. What is a Referential Integrity?
1. Enforces master/detail relationship between tables based on keys.
* Foreign key
* Update Delete restricts action
* Delete Cascade action
Q~:. What are different data types?
The following are the different data types available in Oracle
Q~:. Internal Data types
Q~:. Composite Data types
Internal Data types
Q~:. Character Datatype
Q~:. Date Datatype
Q~:. Row and long row data types
Q~:. Rowid Datatype
Composite Data types
Q~:. Table Data type
Q~:. Record Data type
Q~:. What is VARCHAR2? How is it different CHAR?
The Varchar2 datatype specifies a variable length character string. When you create a varchar2 column, you can supply the maximum number of bytes of data that it can hold. Oracle Subsequently stores each value in the column exactly as you specify. If you try to insert a value that exceeds this length, Oracle returns an error.
The Char datatype length is 1byte. The maximum size of the Char datatype is 255. Oracle compares Char values using the blank-padded comparison semantics. If you insert a value that is shorter than the column length, Oracle blank-pads the value to the column length.
Q~:. What is datatype mixing?
There are two data types %TYPE and %ROWTYPE. The first one declares
a variable to be of the data type of the column of the table to which it is referring.
For example, if you declare a variable like this:
my_empno emp.empno%TYPE
then, my_empno will have the data type of the empno column of the table emp.
Similarly if you declare the variable like this:
my_emprec emp%ROWTYPE
then, my_emprec will have the data type of all the fields of the emp table.
For example. Suppose employee table is like this:
empno number(2),
empname varchar2(10),
Sal number (10,2).
Then my_emprec will be of a data type whose first 2 positions will be of number data type, the next 10 will be of varchar2 data type while the last 10 will be of data type number.
One can refer to the individual fields of this record variable as
my_emprec.empno, my_emprec.empname, my_emprec.sal.
Q~:. What is NULL?
A data field without any value in it is called a null value.
A Null can arise in the following situation
* Where a value is unknown.
* Where a value is not meaningful (i.e.) in column representing
commission for a row that does not represent salesman.
Q~:. What is a sequence?
A sequence is a database object from which multiple users may generate
unique integers.
Q~:. What are pseudo-columns in ORACLE?
The columns that are not part of the table are called as pseudo columns.
Q~:. What is like operator? How is it different from IN operator?
The type of operator that is used in character string comparisons
with pattern matching.
Like operator is used to match a portion of the one character string
to another whereas IN operator performs equality condition between
two strings.
Q~:. What is Single Row numbers Functions?
The type of function that will return value after every row is being
processed.
Following are some of the row number functions.
Function Name Purpose
1. ABS (n) returns the absolute value of a number
2. Floor (n) returns the largest integer value
equal or less than n.
3. Mod (m, n) returns the remainder of m divided by n.
4. Power (m, n) returns m raised to the n power.
5. Round (n) returns n rounded to m places
right of a decimal point.
6. Sqrt (x) returns the sqrt value of x.
7. Trunk (n, m) Returns n truncated to m decimal
places.
Q~:. What are single row character functions?
The function that processes at value of data, which is of character datatype, and returns a character datatype after every row is being processed are termed as single row character functions.
Function Name Purpose.
Q~:. Char (n) returns the character having
an ASCII value.
Q~:. Initcap (n) returns character with first
letter of each argument in
UPPERCASE.
Q~:. Lower (n) returns characters with
all the letters forced to
lower case.
Q~:. ltrim(n) Removes the spaces towards
the left of the string.
Q~:. upper(n) Returns characters with
all the letters forced to
upper case.
Q~:. What are Conversion Functions?
The functions used to convert a value from one datatype to another
are being termed as conversion functions.
Function Name Purpose
To_char (n, (fmt)) converts a value of number datatype
to a value of character datatype.
To_number (n) converts a character value into a number.
Rowidtochar (n) converts rowid values to character datatype.
the result of this conversion is always
18 character long.
Q~:. What are Date functions?
Functions that operate on Oracle Dates are termed as Date functions.
All date functions return a value of date datatype except the \
function months_between returns a numeric value.
Function Purpose
ADD_MONTHS (d, n) returns the date 'd' plus n months.
n must be an integer.
n can be positive or negative.
LAST_DAY (d) returns the date of the last day
of the month containing the date 'd'.
NEXT_DAY (d, char) returns date of first day of week
named after char that is later than
d, char must be a valid day of the
week.
MONTHS_BETWEEN (d, e) returns no of months between dates
d & e.
Q~:. What is NEW_TIME function?
SYNTAX: New_time (d, a, b)
New_time function returns date and time in a time zone b and time in time zone. a and b are character expressions.
Following are the some of the character expressions:
Character expression Description
AST Atlantic Stand or daylight time
BST, BDT Burning stand or daylight time
GMT Greenwich Mean Time.
PST, PDT Pacific Standard Time.
YST, YDT Yukon standard or daylight time.
Q~:. What is Convert function?
Convert function converts two different implementations of the
same character set .
For instance: from DEC 8 bit multi-lingual characters to HP 8 bit
Multi-lingual character set.
Following are the character sets
US7ASCII - US7bit ASCII character set
WE8DEC - Western European 8 bit ASCII set
WE8HP - HP's Western European 8 bit ASCII set
F7DEC - DEC's French 7-bit ASCII set
Convert (char [destination], [source])
Q~:. What is a translate function?
The function that returns a character after replacing all occurrences
of the character specified with the corresponding character is called
as translate function.
E.g. TRANSLATE ('Hello','l','L') gives you HeLLo
Q~:. What is a soundex function?
Soundex is a function that returns a character string representing
the sound of the words in char. This function returns a phonetic
representation of each word and allows you to compare words
that are spelled differently but sound alike.
Soundex (char);
Q~:. What is a replace function?
Replace function returns character with every occurrence of the
search string replaced with the replacement string. If the replacement
string is not supplied, all occurrences of search_string are being
removed. Replace allows you to substitute one string from another.
Q~:. What is a Floor function?
Floor Function returns the largest integer equal to or than n
Syntax: floor (n);
Q~:. What is INITCAP Function?
The initcap function returns char,with first letter of each word in uppercase, all other letters in lowercase. A word is delimited by white space
Q~:. What is ASCII Function?
The ASCII function returns the collating sequence of the first character of lchar. There is no corresponding EBCDIC function.
On EBCDIC systems, the ASCII function will return EBCDIC collating
sequence values.
Q~:. What is a Decode Function?
The Decode function is used to compare an expression to each search
value and returns the result if expr equals the search value.
E.g.: Decode (expr, search1, result1, [search2, result2], [default]);
Q~:. What is Greatest Function?
The Greatest function returns the greatest of a list of values. All expr after the first are converted to the datatype of the first before
comparison is done.
Q~:. What are Format models?
Format models are used to affect how column values are displayed when
a format retrieved with a select command. Format models
do not affect the actual internal representation of the column.
Q~:. Give 5 examples for DATE, Number function?
Examples for Number Function:
Q~:. Select abs (-15) "Absolute:" from dual
Q~:. Select mod (7,5) "Modula" from dual
Q~:. Select round (1235.85,1) from dual
Q~:. Select power (2,3) from dual
Q~:. Select floor (7.5) "Floor" from dual
Examples for Date Function
Q~:. Select sysdate from dual
Q~:. Select sysdate-to_date (23-Sep-93) from dual
Q~:. Select sysdate + 90 from dual
Q~:. Select sysdate -90 from dual
Q~:. Select next_day (sysdate,"Friday") from dual
Q~:. What is an expression?
An expression is a group of value and operators which may be evaluated a single values.
Q~:. What are the types of expression?
The different types of expressions are
Q~:. Logical expression
Q~:. Compound expression
Q~:. Arithmetic expression
Q~:. Negating expression.
Q~:. What is a synonym?
The synonym is a user-defined object that is used to define an alias name for the user defined objects like table view etc.
Q~:. What is a condition?
A Condition could be said to be of the logical datatype that evaluates
the expression to a True or False value.
Q~:. What are the 7 forms of condition?
There are totally 7 forms of condition
Q~:. A comparison with expression or subquery results.
Q~:. A comparison with any or all members in a list or a subquery
Q~:. A test for membership in a list or a subquery
Q~:. A test for inclusion in a range
Q~:. A test for nulls.
Q~:. A test for existence of rows in a subquery
Q~:. A test involving pattern matching
Q~:. A combination of other conditions
Q~:. What are cursors?
Oracle uses work areas called private SQL areas to execute SQL statements and store processing information. This private SQL work area is known as cursors.
Q~:. What are explicit cursors?
Cursors that are defined for performing a multiple row select are known
as explicit cursors.
Implicit cursors are the type of cursors that is implicitly opened
by the Oracle itself whenever you perform any DML statements like
Update, delete, insert or select into statements.
Q~:. What is a PL/SQL?
PL/SQL is a transaction processing language that offers procedural
solutions.
Q~:.What is an embedded SQL?
All the SQL statements written in a Host language are known
as Embedded SQL statements.
Q~:. What are the different conditional constructs of PL/SQL?
The statements that are useful to have a control over the set
of the statements being executed as a single unit are called as
conditional constructs.
No comments:
Post a Comment