1. WHAT
IS DATA OR INFORMATION?
Ans: The Matter that we feed into the Computer is called Data or
Information.
Ans: The Matter that we feed into the Computer is called Data or
Information.
2. WHAT
IS DATABASE?
Ans: The Collection of Interrelated Data is called Data Base.
Ans: The Collection of Interrelated Data is called Data Base.
3. WHAT IS A DATABASE MANAGEMENT SYSTEM (DBMS) PACKAGE?
Ans: The Collection of Interrelated Data and some Programs to access
the Data is Called Data Base Management
System (DBMS).
Ans: The Collection of Interrelated Data and some Programs to access
the Data is Called Data Base Management
System (DBMS).
4. WHEN CAN WE SAY A DBMS PACKAGE AS RDBMS?
Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system,
it must use its
RELATIONAL facilities to MANAGE the DATABASE.
Ans: For a system to Qualify as RELATIONAL DATABASE MANAGEMENT system,
it must use its
RELATIONAL facilities to MANAGE the DATABASE.
5. WHAT IS ORDBMS?
Ans: Object (oriented) Relational Data Base Management System is one
that can store data, the relationship of the
data, and the behavior of the data (i.e., the way it interacts with
other data).
Ans: Object (oriented) Relational Data Base Management System is one
that can store data, the relationship of the
data, and the behavior of the data (i.e., the way it interacts with
other data).
6. NAME SOME CODD'S RULES.
Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it
is to be considered truly relational. Out those,
some are as follows
a) The rules stem from a single rule- the ‘zero rule’: For a system to
Qualify as RELATIONAL
DATABASE MANAGEMENT system, it must use its RELATIONAL facilities
to MANAGE the DATABASE.
b) Information Rule: Tabular Representation of Information.
c) Guaranteed Access Rule: Uniqueness of tuples for guaranteed
accessibility.
d) Missing Information Rule: Systematic representation of missing
information as NULL values.
e) Comprehensive Data Sub-Language Rule: QL to support Data definition,
View definition,
Data manipulation, Integrity, Authorization and
Security.
Ans: Dr. E.F. Codd presented 12 rules that a database must obey if it
is to be considered truly relational. Out those,
some are as follows
a) The rules stem from a single rule- the ‘zero rule’: For a system to
Qualify as RELATIONAL
DATABASE MANAGEMENT system, it must use its RELATIONAL facilities
to MANAGE the DATABASE.
b) Information Rule: Tabular Representation of Information.
c) Guaranteed Access Rule: Uniqueness of tuples for guaranteed
accessibility.
d) Missing Information Rule: Systematic representation of missing
information as NULL values.
e) Comprehensive Data Sub-Language Rule: QL to support Data definition,
View definition,
Data manipulation, Integrity, Authorization and
Security.
7. WHAT ARE HIERARCHICAL, NETWORK, AND RELATIONAL DATABASE
MODELS?
Ans: a) Hierarchical Model: The Hierarchical Model was introduced in
the Information Management System (IMS)
developed by IBM in 1968. In this data is organized as a tree
structure. Each tree is made of nodes and branches.
The nodes of the tree represent the record types and it is a collection
of data attributes entity at that point. The
topmost node in the structure is called the root. Nodes succeeding
lower levels are called children.
Ans: a) Hierarchical Model: The Hierarchical Model was introduced in
the Information Management System (IMS)
developed by IBM in 1968. In this data is organized as a tree
structure. Each tree is made of nodes and branches.
The nodes of the tree represent the record types and it is a collection
of data attributes entity at that point. The
topmost node in the structure is called the root. Nodes succeeding
lower levels are called children.
b) Network Model: The Network Model, also called as the CODSYL
database
structure, is an improvement over the
Hierarchical mode, in this model concept of parent and child is
expanded to have multiple parent-child
relationships, i.e. any child can be subordinate to many different
parents (or nodes). Data is represented by
collection of records, and relationships among data are represented by
links. A link is an association between
precisely two records. Many-to-many relationships can exists between
the parent and child.
structure, is an improvement over the
Hierarchical mode, in this model concept of parent and child is
expanded to have multiple parent-child
relationships, i.e. any child can be subordinate to many different
parents (or nodes). Data is represented by
collection of records, and relationships among data are represented by
links. A link is an association between
precisely two records. Many-to-many relationships can exists between
the parent and child.
c) Relational Model: The Relational Database Model eliminates the
need
for explicit parent-child relationships.
In RDBMS, data is organized in two-dimensional tables consisting of
relational, i.e. no pointers are maintained
between tables.
for explicit parent-child relationships.
In RDBMS, data is organized in two-dimensional tables consisting of
relational, i.e. no pointers are maintained
between tables.
8. WHAT IS DATA MODELING?
Ans: Data Modeling describes relationship between the data objects. The
relationships between the collections of
data in a system may be graphically represented using data modeling.
Ans: Data Modeling describes relationship between the data objects. The
relationships between the collections of
data in a system may be graphically represented using data modeling.
9. DEFINE ENTITY, ATTRIBUTE AND RELATIONSHIP.
Ans: Entity: An Entity is a thing, which can be easily identified. An
entity is any object, place, person, concept or
activity about which an enterprise records data.
Attribute: An attribute is the property of a given entity.
Relationship: Relationship is an association among entities.
Ans: Entity: An Entity is a thing, which can be easily identified. An
entity is any object, place, person, concept or
activity about which an enterprise records data.
Attribute: An attribute is the property of a given entity.
Relationship: Relationship is an association among entities.
10. WHAT IS ER-MODELING?
Ans: The E-R modeling technique is the Top Down Approach. Entity
relationship is technique for analysis and
logical modeling of a system’s data requirements. It is the most widely
used and has gained acceptance as the
ideal database design. It uses three basic units: entities, their
attributes and the relationship that exists between
the entities. It uses a graphical notation for representing these.
Ans: The E-R modeling technique is the Top Down Approach. Entity
relationship is technique for analysis and
logical modeling of a system’s data requirements. It is the most widely
used and has gained acceptance as the
ideal database design. It uses three basic units: entities, their
attributes and the relationship that exists between
the entities. It uses a graphical notation for representing these.
11. WHAT IS NORMALIZATION?
Ans: Normalization is a step-by-step decomposition of complex records
into simple records.
Ans: Normalization is a step-by-step decomposition of complex records
into simple records.
12. WHAT ARE VARIOUS NORMAL FORMS OF DATA?
Ans: The First Normal Form 1NF,
The Second Normal Form 2NF,
The Third Normal Form 3NF,
The Boyce and Codd Normal Form BC NF.
Ans: The First Normal Form 1NF,
The Second Normal Form 2NF,
The Third Normal Form 3NF,
The Boyce and Codd Normal Form BC NF.
13. WHAT IS DENORMALIZATION?
Ans: The intentional introduction of redundancy to a table to improve
performance is called
DENORMALIZATION.
Ans: The intentional introduction of redundancy to a table to improve
performance is called
DENORMALIZATION.
14. WHAT ARE 1-TIER, 2-TIER, 3-TIER OR N-TIER DATABASE
ARCHITECTURES?
Ans: 1-Tier Database Architecture is based on single system, which acts
as both server and client.
2-Tier Architecture is based on one server and client.
3-Tier Architecture is based on one server and client out that on
client act as a remote system.
N-Tier Architecture is based on N no. Of servers and N no. Of clients.
Ans: 1-Tier Database Architecture is based on single system, which acts
as both server and client.
2-Tier Architecture is based on one server and client.
3-Tier Architecture is based on one server and client out that on
client act as a remote system.
N-Tier Architecture is based on N no. Of servers and N no. Of clients.
15. WHAT ARE A TABLE, COLUMN, AND RECORD?
Ans: Table: A Table is a database object that holds your data. It is
made up of many columns. Each of these
columns has a data type associated with it.
Ans: Table: A Table is a database object that holds your data. It is
made up of many columns. Each of these
columns has a data type associated with it.
Column: A column, referred to as an attribute, is similar to a
field in
the file system.
the file system.
Record: A row, usually referred to as tuple, is similar to record
in
the file system.
the file system.
16. WHAT IS DIFFERENCE BETWEEN A PROCEDURAL LANGUAGE AND A
NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language NON-Procedural Language
A program in this implements a step-by-step algorithm to solve the
problem. It contains what to do but not how to do
NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language NON-Procedural Language
A program in this implements a step-by-step algorithm to solve the
problem. It contains what to do but not how to do
17.WHAT TYPE OF LANGUAGE "SQL" IS?
Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts
what to do rather than how to do any
process.
Ans: SQL is a Non-procedural, 4th generation Language,/ which concerts
what to do rather than how to do any
process.
18. CLASSIFICATION OF SQL COMMANDS?
Ans:
DDL (Data Definition Language) DML (Data Manipulating Language) DCL
(Data Control
Language) DTL(Data Transaction Language)
Create Alter Drop Select Insert Update Delete Rollback Commit Grant
Revoke
19. WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly whereas For DML
commands autocommit is to be turned
ON explicitly.
Ans:
DDL (Data Definition Language) DML (Data Manipulating Language) DCL
(Data Control
Language) DTL(Data Transaction Language)
Create Alter Drop Select Insert Update Delete Rollback Commit Grant
Revoke
19. WHAT IS DIFFERENCE BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly whereas For DML
commands autocommit is to be turned
ON explicitly.
20. WHAT IS DIFFERENCE BETWEEN A TRANSACTION AND A QUERY?
Ans: A Transaction is unit of some commands where as Query is a single
line request for the information from the
database.
Ans: A Transaction is unit of some commands where as Query is a single
line request for the information from the
database.
21. WHAT IS DIFFERENCE BETWEEN TRUNCATE AND DELETE COMMANDS?
Ans: Truncate Command will delete all the records where as Delete
Command will delete specified or all the
records depending only on the condition given.
Ans: Truncate Command will delete all the records where as Delete
Command will delete specified or all the
records depending only on the condition given.
22. WHAT IS DIFFERENCE BETWEEN UPDATE AND ALTER COMMANDS?
Ans: Alter command is used to modify the database objects where as the
Update command is used to modify the
values of a data base objects.
Ans: Alter command is used to modify the database objects where as the
Update command is used to modify the
values of a data base objects.
23. WHAT ARE COMMANDS OF TCL CATEGORY?
Ans: Grant and Revoke are the two commands belong to the TCL Category.
24. WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using this command we can
delete only those records that are not
really required.
Ans: Grant and Revoke are the two commands belong to the TCL Category.
24. WHICH IS AN EFFICIENT COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using this command we can
delete only those records that are not
really required.
25. WHAT ARE RULES FOR NAMING A TABLE OR COLUMN?
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character from your database
character set and the characters $ and #.
But these characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace. Objects in the name
space must have different names.
7) A name can be enclosed in double quotes.
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character from your database
character set and the characters $ and #.
But these characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace. Objects in the name
space must have different names.
7) A name can be enclosed in double quotes.
26. HOW MANY COLUMNS CAN A TABLE HAVE?
Ans: A Table can have 1000 columns.
Ans: A Table can have 1000 columns.
27. WHAT ARE DIFFERENT DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data
types for character values,
Number (precision, scale), Number, Number (n), Float, Float (binary
precision) data types for numerical values,
Date data type for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.
Ans: Char (size), Nchar (size), Varchar2 (size), Nvarchar2 (size) data
types for character values,
Number (precision, scale), Number, Number (n), Float, Float (binary
precision) data types for numerical values,
Date data type for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob, Bfile for large objects.
28. WHAT IS DIFFERENCE BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB LONG
1) The maximum size is 4GB.
2) LOBs (except NCLOB) can be attributes of an object type.
3) LOBs support random access to data.
4) Multiple LOB columns per table or LOB attributes in an object type.
1) The maximum size is 2GB. 2) LONGs cannot. 3) LONGs support only
sequential access.
4) Only one LONG column was allowed in a table
Ans:
LOB LONG
1) The maximum size is 4GB.
2) LOBs (except NCLOB) can be attributes of an object type.
3) LOBs support random access to data.
4) Multiple LOB columns per table or LOB attributes in an object type.
1) The maximum size is 2GB. 2) LONGs cannot. 3) LONGs support only
sequential access.
4) Only one LONG column was allowed in a table
29. WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store variable no. Of
characters and while querying the table varchar2
trims the extra spaces from the column and fetches the rows that
exactly match the criteria.
Ans: Varchar2 is similar to Char but can store variable no. Of
characters and while querying the table varchar2
trims the extra spaces from the column and fetches the rows that
exactly match the criteria.
30. HOW MUCH MEMORY IS ALLOCATED FOR DATE DATATYPE? WHAT IS
DEFAULT
DATE
FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes Memory.
Default Date Format is: DD-MON-YY.
DATE
FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes Memory.
Default Date Format is: DD-MON-YY.
31. WHAT IS RANGE FOR EACH DATATYPE OF SQL?
Ans:
Datatype Range
Char Varchar2 Number Float LONG, RAW, LONGRAW Large Objects
(LOB’s) 2000 bytes 4000 bytes
Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary
precision 2 GB 4GB
Ans:
Datatype Range
Char Varchar2 Number Float LONG, RAW, LONGRAW Large Objects
(LOB’s) 2000 bytes 4000 bytes
Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary
precision 2 GB 4GB
32. HOW TO RENAME A COLUMN?
Ans: We can’t rename a Column of a table directly. So we follow the
following steps.
To Rename a Column:
a) Alter the table specifying new column name to be given and data
type.
b) Then copy the values in the column to be renamed into new column.
c) drop the old column.
Ans: We can’t rename a Column of a table directly. So we follow the
following steps.
To Rename a Column:
a) Alter the table specifying new column name to be given and data
type.
b) Then copy the values in the column to be renamed into new column.
c) drop the old column.
33. HOW TO DECREASE SIZE OR CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a column
i. Truncate the table first.
ii. Alter the table column whose size is to be decreased using the same
name and data type but new size.
Ans: To Decrease the size of a Data type of a column
i. Truncate the table first.
ii. Alter the table column whose size is to be decreased using the same
name and data type but new size.
34. WHAT IS A CONSTRAINT? WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of the column to
enforce data entity and consistency.There r two
levels
1)Column-level constraints 2)Table-level constraints.
35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or
Referential Integrity.
Ans: Constraint: Constraints are representators of the column to
enforce data entity and consistency.There r two
levels
1)Column-level constraints 2)Table-level constraints.
35. LIST OUT ALL THE CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and Foreign Key or
Referential Integrity.
36. WHAT IS DIFFERENCE BETWEEN UNIQUE+NOT NULL AND PRIMARY
KEY?
Ans: Unique and Not Null is a combination of two Constraints that can
be present any number of times in a table
and can’t be a referential key to any column of an another table where
as Primary Key is single Constraint that
can be only once for table and can be a referential key to a column of
another table becoming a referential integrity.
Ans: Unique and Not Null is a combination of two Constraints that can
be present any number of times in a table
and can’t be a referential key to any column of an another table where
as Primary Key is single Constraint that
can be only once for table and can be a referential key to a column of
another table becoming a referential integrity.
37. WHAT IS A COMPOSITE PRIMARY KEY?
Ans: A Primary key created on combination of columns is called
Composite Primary Key.
Ans: A Primary key created on combination of columns is called
Composite Primary Key.
38. WHAT IS A CANDIDATE COLUMN? HOW MANY CANDIDATE COLUMNS
CAN BE
POSSIBLE
PER COMPOSITE PRIMARY KEY?
Ans:
39. HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero
nor a space and any mathematical
calculation with NULL is always NULL.
POSSIBLE
PER COMPOSITE PRIMARY KEY?
Ans:
39. HOW TO DEFINE A NULL VALUE?
Ans: A NULL value is something which is unavailable, it is neither zero
nor a space and any mathematical
calculation with NULL is always NULL.
40. WHAT IS NULL? A CONSTRAINT OR DEFAULT VALUE?
Ans: It is a default value.
Ans: It is a default value.
41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY
COLUMNS?
Ans: Index.
COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.
Ans: In this we can't specify Pseudo Columns like sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY
CONSTRAINT?
Ans: References is used as column level key word where as foreign key
is used as table level constraint.
Ans: References is used as column level key word where as foreign key
is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table
then whenever the records from the parent
table is deleted automatically the respective values in the child table
will be deleted.
Ans: when this key word is included in the definition of a child table
then whenever the records from the parent
table is deleted automatically the respective values in the child table
will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using
References)is called as a child table(detail table) and a table which
is being referred is called Parent (Master)
Table .
Ans: A table which references a column of another table(using
References)is called as a child table(detail table) and a table which
is being referred is called Parent (Master)
Table .
47. HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be
used explicitly in queries are called
Pseudo-Columns.
Ex:currval,nextval,sysdate….
Ans: Columns that are not created explicitly by the user and can be
used explicitly in queries are called
Pseudo-Columns.
Ex:currval,nextval,sysdate….
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified
order(ascending,descending) by default it takes ascending order.
Ans: To arrange the query result in a specified
order(ascending,descending) by default it takes ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column whereas count (*) will
count total no. of rows in a table.
Ans: Count () will count the specified column whereas count (*) will
count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of a column.
Ans: To get subtotals and grand total of values of a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of
inner query will be used by the outer query.
Ans: A query within a query is called a sub query where the result of
inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator.
Ans: Value (), Ref () is SQL operator.
58. EXPLAIN
"ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest
value from the set and compares a
value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query
produces any result then it evaluates it to
TRUE else it evaluates it to FALSE.
Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest
value from the set and compares a
value to each returned by a sub query.
All: ALL compares a value to every value returned by SQL.
Exists: This operator produces a BOOLWAN results. If a sub query
produces any result then it evaluates it to
TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A
NORMAL
SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once
for each ‘Candidate row’ by the main
query, which on execution uses a value from a column in the outer
query. In normal sub query the result of inner
query is dynamically substituted in the condition of the outer query
where as in a correlated subquery, the column
value used in inner query refers to the column value present in the
outer query forming a correlated subquery.
SUB QUERY?
Ans: A correlated subquery is a nested subquery, which is executed once
for each ‘Candidate row’ by the main
query, which on execution uses a value from a column in the outer
query. In normal sub query the result of inner
query is dynamically substituted in the condition of the outer query
where as in a correlated subquery, the column
value used in inner query refers to the column value present in the
outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF JOINS?
Ans: A join is used to combine two or more tables logically to get
query results.
Ans: A join is used to combine two or more tables logically to get
query results.
There are four types of Joins namely
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
EQUI Join
NON-EQUI Join
SELF Join
OUTER Join.
61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column between the joining
tables.
Ans: There shold be atleast one common column between the joining
tables.
62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not have corresponding
values in the other,in an equi join that row
will not be selected.Such rows can be forcefully selected by using
outer join symbol(+) on either of the sides(left
or right) based on the requirement.
Ans:If there r any values in one table that do not have corresponding
values in the other,in an equi join that row
will not be selected.Such rows can be forcefully selected by using
outer join symbol(+) on either of the sides(left
or right) based on the requirement.
63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas
EQUI JOIN is made between different tables having common column.
Ans: SELF JOIN is made within the table whereas
EQUI JOIN is made between different tables having common column.
64. WHAT ARE "SET" OPERATORS?
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
Ans: UNION, INTERSECT or MINUS is called SET OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN "UNION" AND
"UNION ALL"
OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will
return even duplicate values.
OPERATORS?
Ans: UNION will return the values distinctly whereas UNION ALL will
return even duplicate values.
66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER
FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
FUNCTIONS.
Ans: Number Functions:
Round (m, [n]),
Trunc (m, [n]),
Power (m, n),
Sqrt,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (string1, string2)
Lower (string)
Upper (string)
Substr (string, from_str, to_str)
ASCII (string)
Length (string)
Initcap (string).
Date Functions:
sysdate
Months between (d1, d2)
To_char (d, format)
Last day (d)
Next_day (d, day).
Conversion Functions:
To_char
To_date
To_number
67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST ()
FUNCTIONS?
Ans: MAX is an aggregate function which takes only one column name of a
table as parameter whereas Greatest is
a general function which can take any number of values and column
names from dual and table respectively.
Ans: MAX is an aggregate function which takes only one column name of a
table as parameter whereas Greatest is
a general function which can take any number of values and column
names from dual and table respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in place of a NULL.
Ans: NVL Function helps in substituting a value in place of a NULL.
69. WHAT FOR DECODE () FUNCTION IS?
Ans: It is substitutes value basis and it actually does an
'if-then-else' test.
Ans: It is substitutes value basis and it actually does an
'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE ()
FUNCTIONS?
Ans: Translate() is a superset of functionality provided by
Replace().
FUNCTIONS?
Ans: Translate() is a superset of functionality provided by
Replace().
71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR ()
FUNCTIONS?
Ans: Substr() will return the specified part of a string whereas
Instr() return the position of the specified part of the string.
Ans: Substr() will return the specified part of a string whereas
Instr() return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days between January 1, 4712 BC
and the given date.
Ans: It will return count of the no. Of days between January 1, 4712 BC
and the given date.
73. HOW TO DISPLAY TIME FROM A DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.
74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date()
function.
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date()
function.
75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR ()
CONVERSION
FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
FUNCTIONS?
Ans: To_date converts character date to date format whereas
To_char function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no
physical data and manipulates the base table.
View is saved as a select statement in the database and contains no
physical data whereas Table exists physically.
Ans: View is database object, which exists logically but contains no
physical data and manipulates the base table.
View is saved as a select statement in the database and contains no
physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based
on more than one table) cannot be
modified.
Ans: Simple views can be modified whereas Complex views(created based
on more than one table) cannot be
modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use
like a view inside a SQL statement. It is not a
schema object like SQL-object.
Ans: Inline view is basically a subquery with an alias that u can use
like a view inside a SQL statement. It is not a
schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be Updated.
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r
performed through the view r not
allowed to create rows which the view cannot select and therefore
allows integrity constraints and data validation
checks to be enforced on data being inserted or updated.
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r
performed through the view r not
allowed to create rows which the view cannot select and therefore
allows integrity constraints and data validation
checks to be enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick
access to rows in a table. An Index increases the
performance of the database.
Ans: An Index is a database object used n Oracle to provide quick
access to rows in a table. An Index increases the
performance of the database.
82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH
SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers
to use as primary keys. Nextval, Currval are the Pseudo Columns
associated with the sequence.
Ans: Sequence is a Database Object used to generate unique integers
to use as primary keys. Nextval, Currval are the Pseudo Columns
associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A
CLUSTER
WHEN
CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a
method of storing tables that are intimately
related and are often joined together into the same area on the disk.
When cluster table exists then to drop
cluster we have to drop the table first then only cluster is to be
dropped.
WHEN
CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a
method of storing tables that are intimately
related and are often joined together into the same area on the disk.
When cluster table exists then to drop
cluster we have to drop the table first then only cluster is to be
dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data
was replicated through CREATE
SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym
for CREATE
SNAPSHOT. Query performance is improved using the materialized view as
these views pre calculate expensive
joins and aggregate operations on the table.
Ans: Materialized views can be used to replicate data. Earlier the data
was replicated through CREATE
SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym
for CREATE
SNAPSHOT. Query performance is improved using the materialized view as
these views pre calculate expensive
joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create
alternate names for Oracle tables and views. It is an
alias for a table, view, snapshot, sequence, procedure, function or
package.
Ans: A Synonym is a database object that allows you to create
alternate names for Oracle tables and views. It is an
alias for a table, view, snapshot, sequence, procedure, function or
package.
86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas
any user can reference the Public
synonym.
Ans: Only the user or table owner can reference Private synonym whereas
any user can reference the Public
synonym.
87. WHAT IS DIFFERENCE BETWEEN "SQL" AND
"SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTE <any
aggregate function>
OF <column name> [break] ON <column name> etc are SQL*PLUS REPORTING
COMMANDS.
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTE <any
aggregate function>
OF <column name> [break] ON <column name> etc are SQL*PLUS REPORTING
COMMANDS.
90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges.
Create <object>, Select, Insert, Alter etc are Object Privileges.
91. WHAT FOR DCL COMMANDS ARE?
Ans: Commit, Rollback are DCL commands.
Ans: Connect and Resource etc are System Privileges.
Create <object>, Select, Insert, Alter etc are Object Privileges.
91. WHAT FOR DCL COMMANDS ARE?
Ans: Commit, Rollback are DCL commands.
92. WHAT FOR GRANT COMMAND WITH "WITH GRANT
OPTION"?
Ans: “With Grant Option” with Grant Command gives privileges to the
user to grant privileges to other user(s)
among the privileges he/she has.
Ans: “With Grant Option” with Grant Command gives privileges to the
user to grant privileges to other user(s)
among the privileges he/she has.
93. HOW TO CHANGE PASSWORD OF A USER?
Ans: Using Password command or
Using ALTER USER <user name> IDENTIFIED BY <new password> COMAND.
Ans: Using Password command or
Using ALTER USER <user name> IDENTIFIED BY <new password> COMAND.
94. WHAT IS A SCHEMA AND SCHEMA OBJECTS?
Ans: A schema is a collection of logical structures of data, or schema
objects. A schema is owned by the database
user and has the same name as that of user. Each user owns a single
schema. Schema objects include following
type of objects Clusters, Database Links, Functions, Indexes, Packages,
Procedures, Sequences, Synonyms, Tables,
Database Triggers, Views.
Ans: A schema is a collection of logical structures of data, or schema
objects. A schema is owned by the database
user and has the same name as that of user. Each user owns a single
schema. Schema objects include following
type of objects Clusters, Database Links, Functions, Indexes, Packages,
Procedures, Sequences, Synonyms, Tables,
Database Triggers, Views.
**95. HOW TO STARTUP AND SHUTDOWN ORACLE DATABASE?
Ans: Startup and Shutdown Oracle database can be done by only the
administator. Startup is done by using
STARTUP command and Shutdown is done by SHUTDOWN command
Ans: Startup and Shutdown Oracle database can be done by only the
administator. Startup is done by using
STARTUP command and Shutdown is done by SHUTDOWN command
96. WHAT IS A SESSION?
Ans: The period between Login and Logoff on schema.
Ans: The period between Login and Logoff on schema.
97. WHAT IS A CLIENT PROCESS? WHAT IS A SERVER PROCESS?
Ans: ref: 172 Q & A.
Ans: ref: 172 Q & A.
98. HOW TO MAKE EVERY DML OPERATION AS AUTO COMMIT?
Ans: By using SET AUTOCOMMIT ON command.
Ans: By using SET AUTOCOMMIT ON command.
99. HOW TO DISPLAY DATA PAGE WISE IN SQL?
Ans: By using SET PAUSE ON command.
Ans: By using SET PAUSE ON command.
100. HOW TO CHANGE LINE SIZE, PAGE SIZE AND SQL PROMPT?
Ans: By using SET LINESIZE <value>, SET PAGESIZE <value>,
SET SQLPROMPT <new prompt>.
Ans: By using SET LINESIZE <value>, SET PAGESIZE <value>,
SET SQLPROMPT <new prompt>.
101. HOW PL/SQL IS DIFFERENT FROM SQL?
Ans: SQL is non-procedural language whereas PL/SQL is procedural
language that includes features and design
of programming language.
Ans: SQL is non-procedural language whereas PL/SQL is procedural
language that includes features and design
of programming language.
102. WHAT IS ARCHITECTURE OF PL/SQL?
Ans:
Ans:
103. WHAT IS A PL/SQL BLOCK?
Ans: DECLARE
<declarations>
BEGIN
<Exececutable Statements>
EXCEPTION
<Exception Handler(s)>
END;
Ans: DECLARE
<declarations>
BEGIN
<Exececutable Statements>
EXCEPTION
<Exception Handler(s)>
END;
104. WHAT ARE DIFFERENT TYPES OF PL/SQL BLOCKS?
Ans: DECLARE BLOCK: In this block all the declarations of the variable
used in the program is made. If no
variables are used this block will become optional.
BEGIN BLOCK: In this block all the executable statements are
placed.
This block is Mandatory.
EXCEPTION BLOCK: In this block all the exceptions are handled.
This block is also very optional.
END: Every begin must be ended with this END; statement.
Ans: DECLARE BLOCK: In this block all the declarations of the variable
used in the program is made. If no
variables are used this block will become optional.
BEGIN BLOCK: In this block all the executable statements are
placed.
This block is Mandatory.
EXCEPTION BLOCK: In this block all the exceptions are handled.
This block is also very optional.
END: Every begin must be ended with this END; statement.
105. WHAT ARE COMPOSITE DATA TYPES?
Ans: Records, Tables are two Composite data types.
Ans: Records, Tables are two Composite data types.
106. WHAT IS SCOPE OF A VARIABLE IN PL/SQL BLOCK?
Ans: The visuability and accessibility of a variable within the
block(s) is called scope of a variable.
Ans: The visuability and accessibility of a variable within the
block(s) is called scope of a variable.
107. WHAT IS A NESTED BLOCK?
Ans: A block within a block is called Nested Block.
Ans: A block within a block is called Nested Block.
108. WHAT IS A PL/SQL ENGINE?
Ans: The PL/SQL engine accepts any valid PL/SQL block as input,
executes the procedural part of the statements
and sends the SQL statements to the SQL statement executor in the
Oracle server.
Ans: The PL/SQL engine accepts any valid PL/SQL block as input,
executes the procedural part of the statements
and sends the SQL statements to the SQL statement executor in the
Oracle server.
109. WHAT IS DEFAULT VALUE FOR A NUMERIC PL/SQL VARIABLE?
Ans: NULL
Ans: NULL
110. WHAT IS DIFFERENCE BETWEEN SIMPLE LOOP AND A FOR LOOP?
Ans: Simple requires declaration of variables used in it and exit
condition but For Loop doesn’t require this.
Ans: Simple requires declaration of variables used in it and exit
condition but For Loop doesn’t require this.
111. WHAT IS A CURSOR? STEPS TO USE A CURSOR?
Ans: Cursor is Private SQL area in PL/SQL.
Declare the Cursor,
Open the Cursor,
Fetch values from SQL into the local Variables,
Close the Cursor.
Ans: Cursor is Private SQL area in PL/SQL.
Declare the Cursor,
Open the Cursor,
Fetch values from SQL into the local Variables,
Close the Cursor.
112. HOW MANY TYPES OF CURSORS ARE SUPPORTED BY ORACLE?
Ans: There are two types of cursors namely Implicit Cursor, Explicit
Cursor.
Ans: There are two types of cursors namely Implicit Cursor, Explicit
Cursor.
113. WHAT IS A CURSOR FOR LOOP?
Ans: Cursor For Loop is shortcut process for Explicit Cursors because
the Cursor is Open, Rows are fetched once
for each iteration and the cursor is closed automatically when all the
rows have been processed.
Ans: Cursor For Loop is shortcut process for Explicit Cursors because
the Cursor is Open, Rows are fetched once
for each iteration and the cursor is closed automatically when all the
rows have been processed.
114. WHAT ARE CURSOR ATTRIBUTES?
Ans: %Found
%NotFound
%IsOpen
%RowCount are the cursor attributes.
Ans: %Found
%NotFound
%IsOpen
%RowCount are the cursor attributes.
115. WHAT IS USE OF CURSOR WITH "FOR UPDATE OF" CLAUSE?
Ans: This Clause stop accessing of other users on the particular
columns used by the cursor until the COMMIT is
issued.
Ans: This Clause stop accessing of other users on the particular
columns used by the cursor until the COMMIT is
issued.
116. WHAT IS AN EXCEPTION? HOW IT IS DIFFERENT FROM ERROR?
Ans: Whenever an error occurs Exception raises.
Error is a bug whereas the Exception is a warning or error
condition.
Ans: Whenever an error occurs Exception raises.
Error is a bug whereas the Exception is a warning or error
condition.
117. NAME SOME BUILT-IN EXCEPTIONS.
Ans: Too_Many_Rows
No_Data_Found
Zero_Divide
Not_Logged_On
Storage_Error
Value_Error etc.
Ans: Too_Many_Rows
No_Data_Found
Zero_Divide
Not_Logged_On
Storage_Error
Value_Error etc.
118. HOW TO CREATE A USER-DEFINED EXCEPTION?
Ans: User-Defined Exception is created as follows:
DECLARE
<exception name> EXCEPTION;
- - - - - - - - - ;
- - - - - - - - -;
BEGIN
- - - - - - - - -;
- - - - - - - - -;
RAISE <exception name>;
EXCEPTION
WHEN <exception name> THEN
- - - - - - - - -;
- - - - - - - - -;
END;
Ans: User-Defined Exception is created as follows:
DECLARE
<exception name> EXCEPTION;
- - - - - - - - - ;
- - - - - - - - -;
BEGIN
- - - - - - - - -;
- - - - - - - - -;
RAISE <exception name>;
EXCEPTION
WHEN <exception name> THEN
- - - - - - - - -;
- - - - - - - - -;
END;
119. WHAT IS "OTHERS" EXCEPTION?
Ans: It is used to along with one or more exception handlers.
This will handle all the errors not already handled in the block.
Ans: It is used to along with one or more exception handlers.
This will handle all the errors not already handled in the block.
120. WHAT IS SCOPE OF EXCEPTION HANDLING IN NESTED BLOCKS?
Ans: Exception scope will be with in that block in which exception
handler is written.
Ans: Exception scope will be with in that block in which exception
handler is written.
121. WHAT IS A SUB-PROGRAM?
Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE INVOKED BY TAKING
PARAMATERS.
Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE INVOKED BY TAKING
PARAMATERS.
122. WHAT ARE DIFFERENT TYPES OF SUB-PROGRAMS?
Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.
Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.
123. HOW A PROCEDURE IS DIFFERENT FROM A FUNCTION?
Ans: Function has return key word and returns a value whereas a
Procedure doesn’t return any value.
124. WHAT ARE TYPES OF PARAMETERS THAT CAN BE PASSED TO FUNCTION
OR
PROCEDURE?
Ans: IN, IN OUT, OUT.
PROCEDURE?
Ans: IN, IN OUT, OUT.
125. WHAT IS "IN OUT" PARAMETER?
Ans: A parameter, which gets value into the Procedure or Function and
takes the value out of the Procedure or
Function area, is called IN OUT parameter.
Ans: A parameter, which gets value into the Procedure or Function and
takes the value out of the Procedure or
Function area, is called IN OUT parameter.
126. DOES ORACLE SUPPORTS PROCEDURE OVERLOADING?
Ans: NO.
Ans: NO.
127. WHAT IS A PACKAGE AND PACKAGE BODY?
Ans: Package is declarative part of the functions and procedures stored
in that package and package body is
the definition part of the functions and procedures of that package.
Ans: Package is declarative part of the functions and procedures stored
in that package and package body is
the definition part of the functions and procedures of that package.
128. WHAT IS ADVANTAGE OF PACKAGE OVER PROCEDURE OR FUNCTION?
Ans: Packages provides Functions or Procedures Overloading facility and
security to those Functions or
Procedures.
Ans: Packages provides Functions or Procedures Overloading facility and
security to those Functions or
Procedures.
129. IS IT POSSIBLE TO HAVE A PROCEDURE AND A FUNCTION WITH THE
SAME
NAME?
Ans: NO if it is out side a Package, YES if it is within a Package.
NAME?
Ans: NO if it is out side a Package, YES if it is within a Package.
130. DOES ORACLE SUPPORTS RECURSIVE FUNCTION CALLS?
Ans: YES.
Ans: YES.
131. WHAT IS A TRIGGER? HOW IT IS DIFFERENT FROM A PROCEDURE?
Ans: Trigger: A Trigger is a stored PL/SQL program unit associated
with a specific database table.
Procedure: A Procedure is to be explicitly called by the user
whereas Triggers are automatically called implicitly
by Oracle itself whenever event Occurs.
Ans: Trigger: A Trigger is a stored PL/SQL program unit associated
with a specific database table.
Procedure: A Procedure is to be explicitly called by the user
whereas Triggers are automatically called implicitly
by Oracle itself whenever event Occurs.
132. WHAT IS DIFFERENCE BETWEEN A TRIGGER AND A CONSTRAINT?
Ans: Constraints are always TRUE whereas Triggers are NOT always TRUE
and Constraints has some limitations
whereas Trigger has no limitations.
Ans: Constraints are always TRUE whereas Triggers are NOT always TRUE
and Constraints has some limitations
whereas Trigger has no limitations.
133. WHAT ARE DIFFERENT EVENTS FOR A TRIGGER AND THEIR SCOPES?
Ans: Insert, Update or Delete.
Ans: Insert, Update or Delete.
134. WHAT IS DIFFERENCE BETWEEN TABLE LEVEL AND ROW LEVEL
TRIGGERS?
Ans: Table level Triggers execute once for each table based transaction
whereas Row level Triggers will execute
once FOR EACH ROW.
Ans: Table level Triggers execute once for each table based transaction
whereas Row level Triggers will execute
once FOR EACH ROW.
** 135. WHAT ARE AUTONOMOUS TRIGGERS?
Ans:
Ans:
136. WHAT IS AN "INSTEAD OF" TRIGGER?
Ans: These Triggers are used with the Complex Views only to make
possible of Insert, Update and Delete on those
Views.
Ans: These Triggers are used with the Complex Views only to make
possible of Insert, Update and Delete on those
Views.
** 137. HOW MANY TRIGGERS CAN BE CONFIGURED ON A TABLE AND VIEW?
Ans:
Ans:
138. WHAT IS "TABLE MUTATING" ERROR? HOW TO SOLVE IT?
Ans: ORA-04091: Table name is mutating, trigger/function may not see it
Cause : A trigger or a user-defined PL/SQL function that is referenced
in the statement attempted to query or
modify a table that was in the middle of being modified by the
statement that fired the trigger.
Action : Rewrite the trigger or function so it does not read the table.
Ans: ORA-04091: Table name is mutating, trigger/function may not see it
Cause : A trigger or a user-defined PL/SQL function that is referenced
in the statement attempted to query or
modify a table that was in the middle of being modified by the
statement that fired the trigger.
Action : Rewrite the trigger or function so it does not read the table.
139. WHEN TO USE ":NEW" AND ":OLD" SPECIFIERS?
Ans: The prefix :old is used to refer to values already present in the
table. The prefix :new is a correlation name
that refers to the new value that is inserted / updated.
Ans: The prefix :old is used to refer to values already present in the
table. The prefix :new is a correlation name
that refers to the new value that is inserted / updated.
** 140. WHAT IS A CONDITIONAL TRIGGER?
Ans:
Ans:
** 141. HOW TO CREATE A USER-DEFINED VARIABLE IN PL/SQL?
Ans:
Ans:
142. HOW TO CREATE AN ARRAY VARIABLE IN PL/SQL?
Ans: Using CREATE [OR REPLACE] TYPE <type name>
AS VARRAY (size) OF ELEMENT_TYPE (NOT NULL) Command;
Ans: Using CREATE [OR REPLACE] TYPE <type name>
AS VARRAY (size) OF ELEMENT_TYPE (NOT NULL) Command;
**143. HOW TO MAKE A USER-DEFINED DATA TYPE GLOBAL IN PL/SQL?
Ans:
Ans:
144. HOW TO CREATE AN OBJECT IN ORACLE?
Ans: Using CREATE [OR REPLACE] TYPE <type name> AS OBJECT (ATTRIBUTE
NAME DATA TYPE,..)
Command
Ans: Using CREATE [OR REPLACE] TYPE <type name> AS OBJECT (ATTRIBUTE
NAME DATA TYPE,..)
Command
145. WHAT IS A TRANSIENT AND PERSISTENT OBJECT?
Ans: The Object created in a table is called Persistent Object.
Object created on execution of PL/SQL block is called Transient
Object.
Ans: The Object created in a table is called Persistent Object.
Object created on execution of PL/SQL block is called Transient
Object.
**146. WHAT IS A COLUMN OBJECT AND TABLE OBJECT?
Ans: A Column Object is only a Column of a table.
147. HOW TO GRANT PERMISSION ON AN OBJECT TO OTHER USER?
Ans: GRANT <permission> ON <object name> TO <user name>.
Ans: A Column Object is only a Column of a table.
147. HOW TO GRANT PERMISSION ON AN OBJECT TO OTHER USER?
Ans: GRANT <permission> ON <object name> TO <user name>.
148. WHAT IS A COLLECTION OF ORACLE?
Ans: Varray, Nested Table is a collection of Oracle.
Ans: Varray, Nested Table is a collection of Oracle.
149. WHAT IS DIFFERENCE BETWEEN VARRAY AND NESTED TABLE?
Ans: Varray has a fixed size.
Nested tables can carry any number of values.
Ans: Varray has a fixed size.
Nested tables can carry any number of values.
150. HOW TO MODIFY CONTENTS OF A VARRAY IN ORACLE?
Ans: To modify a stored VARRAY it has to selected into a
PL/SQL variable and then inserted back into the table.
Ans: To modify a stored VARRAY it has to selected into a
PL/SQL variable and then inserted back into the table.
151. WHAT IS USE OF "THE" OPERATOR FOR NESTED TABLE?
Ans: THE operator allows nested tables to be manipulated using DML when
it is stored in a Table.
Ans: THE operator allows nested tables to be manipulated using DML when
it is stored in a Table.
152. WHICH PACKAGE IS USED FOR FILE INPUT/OUTPUT IN ORACLE?
Ans: UTL_FILE Package is used for File input/output in Oracle.
Ans: UTL_FILE Package is used for File input/output in Oracle.
153. NAME SOME METHODS AND PROCEDURES OF FILE I/O PACKAGE?
Ans: FOPEN
FCLOSE
FFLUSH
IS_OPEN
GET_LINE
PUT_LINE
PUTF
NEW_LINE
**154. WHAT IS SQLJ? HOW IT IS DIFFERENT FROM JDBC CONNECTIVITY?
Ans: SQLJ is basically a Java program containing embedded static SQL
statements that are compatible with
Java design philosophy.
Ans: FOPEN
FCLOSE
FFLUSH
IS_OPEN
GET_LINE
PUT_LINE
PUTF
NEW_LINE
**154. WHAT IS SQLJ? HOW IT IS DIFFERENT FROM JDBC CONNECTIVITY?
Ans: SQLJ is basically a Java program containing embedded static SQL
statements that are compatible with
Java design philosophy.
155. WHAT IS AN ITERATOR? Name some TYPES OF ITERATORS?
Ans: SQLJ Iterators are basically record groups generated during
transaction, which requires manipulation of
more than one records from one or more tables. There are two types
Iterators namely Named Iterator and
Positional Iterator.
Ans: SQLJ Iterators are basically record groups generated during
transaction, which requires manipulation of
more than one records from one or more tables. There are two types
Iterators namely Named Iterator and
Positional Iterator.
** 156. WHAT ARE DIFFERENT STEPS TO WRITE A DYNAMIC SQL PROGRAM?
Ans:
Eg: char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
EXEC SQL EXECUTE readytogo;
Ans:
Eg: char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
EXEC SQL EXECUTE readytogo;
157. WHAT IS TABLE PARTITIONING AND INDEX PARTITIONING?
Ans: Oracle8 allows tables and Indexes to be partitioned or broken up
into smaller parts based on range of key
values. Partitioning is a “divide and conquer” strategy that improves
administration and performance in data
warehouse and OLTP systems.
Ans: Oracle8 allows tables and Indexes to be partitioned or broken up
into smaller parts based on range of key
values. Partitioning is a “divide and conquer” strategy that improves
administration and performance in data
warehouse and OLTP systems.
158. WHAT IS PARALLEL PROCESSING?
Ans:
Ans:
159. WHAT IS PHYSICAL MEMORY STRUCTURE OF ORACLE?
Ans: The basic oracle memory structure associated with Oracle includes:
Software Code Areas
The System Global Area (SGA)
The Database Buffer Cache
The shared Pool
The Program Global Areas (PGA)
Stack Areas
Data Areas
Sort Areas
Ans: The basic oracle memory structure associated with Oracle includes:
Software Code Areas
The System Global Area (SGA)
The Database Buffer Cache
The shared Pool
The Program Global Areas (PGA)
Stack Areas
Data Areas
Sort Areas
160. WHAT IS LOGICAL MEMORY STRUCTURE OF ORACLE?
DB_STG
STUDENT SYSTEM
EMP DEPT EMP_IND ….. ..
DATA DATA INDEX
Ans: Database
Tablespace
DB Object
Segment
Extends
DB_STG
STUDENT SYSTEM
EMP DEPT EMP_IND ….. ..
DATA DATA INDEX
Ans: Database
Tablespace
DB Object
Segment
Extends
161. WHAT IS SGA?
Ans: A System Global Area is a group of shared memory allocated by
Oracle that contains data and control
information for one Oracle database instance. IF the multiple users are
concurrently connected to the same
instance, the data in the instance’s SGA is “shared” among the users.
Consequently, the SGA is
often referred to as either the “system Global Area” or the “Shared
Global Area”.
162. WHAT IS PGA?
Ans: The Program Global Area is a memory buffer that contains data and
control information for
a server process. A PGA is created by Oracle when a server process is
started. The information in a PGA
depends on the configuration of Oracle.
Ans: The Program Global Area is a memory buffer that contains data and
control information for
a server process. A PGA is created by Oracle when a server process is
started. The information in a PGA
depends on the configuration of Oracle.
163. WHAT IS AN ORACLE INSTANCE?
Ans: Every time a database is started, an SGA is allocated and Oracle
background processes are started.
The combination of these processes and memory buffers is called an
Oracle instance.
Ans: Every time a database is started, an SGA is allocated and Oracle
background processes are started.
The combination of these processes and memory buffers is called an
Oracle instance.
164. WHAT ARE DIFFERENT ORACLE PROCESSES?
Ans: A process is a “thread of control” or a mechanism in an operating
system that can be execute a series
of steps. Some operating systems use terms jobs or task. A process
normally has its own private memory area
in which it runs. An Oracle database system has general types of
process: User Processes and Oracle Processes.
Ans: A process is a “thread of control” or a mechanism in an operating
system that can be execute a series
of steps. Some operating systems use terms jobs or task. A process
normally has its own private memory area
in which it runs. An Oracle database system has general types of
process: User Processes and Oracle Processes.
**165. WHAT IS DIFFERENCE BETWEEN PMON AND SMON?
Ans: SMON (System Monitor) performs instance recovery at instance of
startup. In a multiple instance system
(one that uses the parallel server), SMON of one instance can also
perform instance recovery
other instance that have failed whereas The PMON (Process Monitor)
performs process recovery when
a user process fails.
Ans: SMON (System Monitor) performs instance recovery at instance of
startup. In a multiple instance system
(one that uses the parallel server), SMON of one instance can also
perform instance recovery
other instance that have failed whereas The PMON (Process Monitor)
performs process recovery when
a user process fails.
**166. WHAT IS DIFFERENCE BETWEEN DATABASE AND TABLESPACE?
Ans:
Ans:
167. WHAT IS JOB OF DATABASE WRITER (DBWR) PROCESS?
Ans: The Data Base Writer writes modified blocks from the database
buffer cache to the data files.
Ans: The Data Base Writer writes modified blocks from the database
buffer cache to the data files.
168. WHAT IS JOB OF LOG WRITER (LGWR) PROC*SS?
Ans: The Log Writer writes redo log files to disk. Redo log data is
generated in the redo log buffer of the SGA.
As transactions commit and log buffer fills, LGWR writes redo entries
into an online redo log file.
Ans: The Log Writer writes redo log files to disk. Redo log data is
generated in the redo log buffer of the SGA.
As transactions commit and log buffer fills, LGWR writes redo entries
into an online redo log file.
169. WHAT IS RECOVERER?
Ans: The Recover (RECO) is used to resolve distributed transactions
that are pending due to network or
system failure in a distributed database. At timed intervals, the local
RECO attempts to concept to remote
database and automatically complete the commit or rollback of the local
portion of any pending distributed
transactions.
Ans: The Recover (RECO) is used to resolve distributed transactions
that are pending due to network or
system failure in a distributed database. At timed intervals, the local
RECO attempts to concept to remote
database and automatically complete the commit or rollback of the local
portion of any pending distributed
transactions.
170. WHAT IS ARCHIVER?
Ans: The Archiver (ARCH) copies the online redo log files to archival
storage when they are full.
ARCH is active only when a database’s redo log is used ARCHILOG mode.
Ans: The Archiver (ARCH) copies the online redo log files to archival
storage when they are full.
ARCH is active only when a database’s redo log is used ARCHILOG mode.
** 171. WHAT IS A STORED QUERY?
Ans: VIEW
172. WHAT IS USER PROCESS AND SERVER PROCESS?
Ans: A User process is created and maintained to execute the software
code of an application program (such as
PRO * Program) or an ORACLE tool (such as SQL * DBA). The User process
also manages the communication
with server processes. User processes communication with the server
processes through the program interface.
Ans: A User process is created and maintained to execute the software
code of an application program (such as
PRO * Program) or an ORACLE tool (such as SQL * DBA). The User process
also manages the communication
with server processes. User processes communication with the server
processes through the program interface.
Other processes call ORACLE processes. In a dedicated
server
configuration, a server
Process handles requests for a single user process. A multithread
server configuration allows many user processes
to share a small number of server processes, minimizing the utilization
of available system resources.
configuration, a server
Process handles requests for a single user process. A multithread
server configuration allows many user processes
to share a small number of server processes, minimizing the utilization
of available system resources.
**173. WHAT IS A SELF REFERENTIAL INTEGRITY?
Ans: Table related to itself .Foreign key of the table links to primary
key of the same table.
Ans: Table related to itself .Foreign key of the table links to primary
key of the same table.
174. WHAT IS A "RAISE" STATEMENT?
Ans: It is used to Raise Exceptions.
Ans: It is used to Raise Exceptions.
175. WHAT IS ROWID? HOW IT IS DIFFERENT FROM ROWNUM?
Ans: Rowid is the address of the row at where it is stored in the
database. Rownum is count of records whereas
Rowid is identification of the each row.
11111111111111111111111111111111111111111111111111111111111111111111111111111111111111
SQL> INSERT INTO t VALUES (1);
SQL> INSERT INTO t VALUES (-5);
SQL> INSERT INTO t VALUES (6);
SQL> INSERT INTO t VALUES (7);
SQL> INSERT INTO t VALUES (-8);
SQL> SELECT MAX(DECODE(a.sign_val,+1,a.posneg)) pos
2 , MAX(DECODE(a.sign_val, 0,a.posneg)) zero
3 , MAX(DECODE(a.sign_val,-1,a.posneg)) neg
4 FROM (SELECT t.posneg
5 , SIGN(t.posneg) sign_val
6 , ROW_NUMBER()
7 OVER (PARTITION BY SIGN(t.posneg)
8 ORDER BY ABS(t.posneg)) r
9 FROM t) a
10 GROUP BY a.r
11 /
POS ZERO NEG
---------- ---------- ----------
1 -5
6 -8
7
Ans: Rowid is the address of the row at where it is stored in the
database. Rownum is count of records whereas
Rowid is identification of the each row.
11111111111111111111111111111111111111111111111111111111111111111111111111111111111111
SQL> INSERT INTO t VALUES (1);
SQL> INSERT INTO t VALUES (-5);
SQL> INSERT INTO t VALUES (6);
SQL> INSERT INTO t VALUES (7);
SQL> INSERT INTO t VALUES (-8);
SQL> SELECT MAX(DECODE(a.sign_val,+1,a.posneg)) pos
2 , MAX(DECODE(a.sign_val, 0,a.posneg)) zero
3 , MAX(DECODE(a.sign_val,-1,a.posneg)) neg
4 FROM (SELECT t.posneg
5 , SIGN(t.posneg) sign_val
6 , ROW_NUMBER()
7 OVER (PARTITION BY SIGN(t.posneg)
8 ORDER BY ABS(t.posneg)) r
9 FROM t) a
10 GROUP BY a.r
11 /
POS ZERO NEG
---------- ---------- ----------
1 -5
6 -8
7
or
SELECT posneg AS pos, to_number(null) AS neg
FROM t
WHERE posneg >= 0
UNION ALL
SELECT to_number(null) AS pos, posneg AS neg
FROM t
WHERE posneg < 0;
No comments:
Post a Comment