What is Stored Procedure
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etcReply
What is normalization?
Normalisation is a process whish is the tables in a database are optimised to remove the potential for redundancy. Two main problems may arise if this is not done:
> Repeated data makes a database bigger.
> Multiple instances of the same values make
maintaining the data more difficult and can
What are different normalization forms?
normalisation have a se veral forms that a database structure can be subject to, each with rules that constrain the database further and each creating what is called a Normal Form. These are, in order:
> First Normal Form (1NF)
> Second Normal Form (2NF)
> Third Normal Form (3NF)
> Boyce Codd Normal Form (BCNF)
> Fourth Normal Form (4NF)
> Fifth Normal Form (5NF)
> Optimal Normal Form (ONF)
> Domain-Key Normal Form (DKNF)
Theses are defined as :
> 1NF: Eliminate Repeating Groups : Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
> 2NF: Eliminate Redundant Data : If an attribute depends on only part of a multi-valued key, remove it to a separate table.
> 3NF: Eliminate Columns Not Dependent On Key : If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
> BCNF: Boyce-Codd Normal Form : If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
> 4NF: Isolate Independent Multiple Relationships : No table may contain two or more 1:n or n:m relationships that are not directly related.
> 5NF: Isolate Semantically Related Multiple : Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships.
> ONF: Optimal Normal Form : A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
> DKNF: Domain-Key Normal Form : A model free from all modification anomalies.Reply
What is RDBMS?
RDBMS stands for Relational Database Management
System. RDBMS is the basis for SQL, and for all
modern database systems like MS SQL Server, IBM
DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects
called tables. A table is a collections of
related data entries and it consists of columns
and rows.Relational Data Base Management Systems
(RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.Reply
Which one is faster DELETE/TRUNCATE?
Truncante is more faster than delete because when we delete the records from the database, database has to perform 2 actions.
> Delete from the database
> Write the deleted records into "rollback"
segments. But incase of "Truncate" the second activity is not required.It is faster than becouse truncate is a ddl command so it does not produce any rollback information and the storage space is released while the delete command is a dml command and it produces rollback information too and space is not deallocated using delete command.Reply
What is the Sql Wild card?
SQL Wild Card is the bassically use for the searchign opration similarily LIKE function.SQL wildcards can substitute for one or more characters when searching for data in a database.
the following wildcards can be used:
> % - A substitute for zero or more characters
> _ - A substitute for exactly one character
> [charlist] - Any single character in charlist
> [^charlist] or [!charlist] - Any single character not in charlist
Describe TO_DATE function?
The TO_DATE function using the values when we want to be show thw timestamp from a character string that has been interpreted using a character template.Reply
What Oracle lock modes do you know?
Oracle has two lock modes :
> shared or
Shared locks are set on database resources so that many transactions can access the resource.
Exclusive locks are set on resources that ensure
one transaction has exclusive access to the database resource
What is Oracle locking?
Oracle is the bassically a database Its have a many facility, taht facilities is onne of the name is locking which uses locking mechanisms to protect data from being destroyed by concurrent transactions.
What is query optimization?
In the database many queries generate and executed or Query optimization is the part of the query process in which the database system compares different query strategies and chooses the one with the least expected cost.