Sql Qustion
1-What is RDBM?
Ans-RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields
2-What is Trigger?
Ans-A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.
There are two classes of triggers, they are either "row triggers" or "statement triggers". With row triggers you can define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. Triggers cannot be used to audit data retrieval.
Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which identifies the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is a conditional trigger that will fire instead of the triggering statement. However, "INSTEAD OF trigger" are available only for views.
There are typically three triggering EVENTS that cause trigger to 'fire':
INSERT event (as a new record is being inserted into the database).
UPDATE event (as a record is being changed).
DELETE event (as a record is being deleted).
1. Stored Procedure : A SP is a named group of SQL statements that have been previously created and stored in the server database.
a) SPs reduce network traffic and improve performance.
2. Trigger : A trigger is a SQL procedure that initiates an action when an event (INSERT,DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.
Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger can not be called or executed. DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to SPs in that both consist of procedural logic that is stored at the database level.
SPs are implicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
3. Nested Trigger : Nested trigger can also contain INSERT, DELETE and UPDATE logic within itself. A trigger that contains data modification logic within itself is called a nested trigger.
4. View : A simple view can be thought of a subset of a table. It can be used for retrieving data, as well as deleting or updating rows. Rows updated or deleted in the view are updated or deleted in the table the view table created with.
Note : Data in the original table changes, data in the view changes automatically bcoz views always will look at original table. Advantage of using Views are not permanently stored in database. The data accessed through a view is actually constructed using standard select command and can come from one to many different base tables or even other views.
5. Index : An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users can not see the indexes. They are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query.
6. Sub Query :
a) What's the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
Ans-RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields
2-What is Trigger?
Ans-A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.
There are two classes of triggers, they are either "row triggers" or "statement triggers". With row triggers you can define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. Triggers cannot be used to audit data retrieval.
Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which identifies the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is a conditional trigger that will fire instead of the triggering statement. However, "INSTEAD OF trigger" are available only for views.
There are typically three triggering EVENTS that cause trigger to 'fire':
INSERT event (as a new record is being inserted into the database).
UPDATE event (as a record is being changed).
DELETE event (as a record is being deleted).
1. Stored Procedure : A SP is a named group of SQL statements that have been previously created and stored in the server database.
a) SPs reduce network traffic and improve performance.
2. Trigger : A trigger is a SQL procedure that initiates an action when an event (INSERT,DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.
Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger can not be called or executed. DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to SPs in that both consist of procedural logic that is stored at the database level.
SPs are implicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
3. Nested Trigger : Nested trigger can also contain INSERT, DELETE and UPDATE logic within itself. A trigger that contains data modification logic within itself is called a nested trigger.
4. View : A simple view can be thought of a subset of a table. It can be used for retrieving data, as well as deleting or updating rows. Rows updated or deleted in the view are updated or deleted in the table the view table created with.
Note : Data in the original table changes, data in the view changes automatically bcoz views always will look at original table. Advantage of using Views are not permanently stored in database. The data accessed through a view is actually constructed using standard select command and can come from one to many different base tables or even other views.
5. Index : An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users can not see the indexes. They are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query.
6. Sub Query :
a) What's the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.