Interview Question and answer - SQL Server Day 1


Question: What is a Stored Procedure?

Answer: Stored Procedure is the collection of Structured Query Language (SQL) statements with an assigned name stored in the database in compiled form. Stored Procedure can be used over the network by several clients using different input data because the stored procedure can accept input parameters. The stored procedure improved performance, reducing network traffic.

Question: What is Trigger in SQL Server?

Answer: Trigger is the same as a procedure that is executed on the action when an event (INSERT, DELETE, UPDATE, etc.) occurs. DBMS manage and store to trigger. Triggers are commonly used to perform auditing action, to maintain the table integrity in place of native constraints such as foreign key and check constraints, and perform other DML processing. When a trigger is at work, the process can’t be completed until trigger completion because the trigger operates under the scope of a transaction.

Question: Type of trigger?

Answer: There are two types of trigger DML and DDL.
1-  DML Trigger: DML trigger is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. There are two types of DML trigger.

A)  Instead of Trigger: Instead of trigger override, the standards actions of the triggering means fire in the place of the triggering action such as insert, delete, or update. For example:  when the value is updated in an hourly wage column in a payroll table exceeds a specific value.

B)  After Trigger: This triggers fire after the execution of an action query. After trigger fire for both DML and DDL statements.

2-  DDL Trigger: This type of trigger fired when an action occurs like Drop, Create, and Alter Table DDL triggers are always after a trigger.



PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...