SQL InterviewQuestions Part2

11) what is a constraint?
a) A constraint is a property which is assigned to a column or set of columns in a table. There are 5 types of constraints
Primary key, Unique key, Foreign Key, Check, Not Null

12) What is an entity integrity?
a) This ensures that there can not be duplicate rows in a table

13) What is a Trigger?
a) A trigger is a special of stored procedure which will be executed
automatically in place of or after data modifications. There are 2 types of triggers
After triggers: This is the trigger which will be executed automatically after the statement which triggered it completes
Instead of Triggers This is the trigger which will be executed automatically in place of triggering statement

14) How many after triggers we can crate for a table?
a) We can create any number of after triggers for each Insert, Update, Delete operations on a table

15) How many Instead of triggers can be created for a table?
a) Only one for each Insert, Delete, Update operations

16) Can a trigger be encrypted?
a) Yes.

17) what is the stored procedure used to set the order of after triggers?
a) sp_settriggerorder

18) can we create after triggers on a view?
a) No

19) what is a view?
a) A view is a virtual table which contains columns from one or more tables.

20) what are the advantages of views?
a) 1. It provides security. means if a view is created on a table then the users can access only those columns which are there in view. they cannot access all the columns
2. It simplifies query execution means a complex query can be stored in the form of a view and view can be accessed using a simple query.

SQL InterviewQuestions Part1

1) Differences between Having and Where
Having:
1) It applies to a group as a whole
2) It selects rows afetr grouping
3) It can contain aggrigate functions
4) It is used only in select clause
Where:
1) It applies to individual rows
2) It selects rows before grouping
3) It can't contain aggrigate functions
4) It can be used in select, delete, insert etc

2) What is a primary key?
a) 1)A primary key is used to uniquely identify a row in a table.
2) A table can have only one primary key.
3) It does not allows nulls
4) It creats a clusterd index on a column

3) What is a clustered index?
a) In a clustered index the logical order of index matches the physical
stored order of the rows in a disk. A table can have only one clustered
index. The leaf nodes of clustered index contains the data pages(actual data)

4) What is a transaction and what are its properties?
a) A transaction is a logical unit of work in which all steps must be performed or none. It has 4 main properties
a) Atomicity
b) Consistency
c) Isolation
d) Durability

5) How many non clustered indexes we can create on a table
a) More than one

6) Differences between Union and UnionAll
a) Union:
1) This is used to eliminate duplicate rows
2) This selects only distinct rows
3) It can be used to combine any number of queries
4) It can't contain aggrigate functions
UnionAll:
1) It will not eliminate duplicate rows
2) It selects all the values
3) It can be used to combine maximum of 2 quesries
4) It can contain aggrigate functions

7) What is a composite key?
a) A key formed by combining 2 or more columns is called composite key

8) how many nulls a unique key allows?
a) It allows only one null

9) Differences between Delete and Truncate?
a) Delete:
1) It is a DML statement
2) It can activate a trigger
3) It can include a Where clause
Truncate:
1) It is a DDL statement
2) It cann't activate a trigger
3) It can't include Where clause

10) what is Atomicity?
a) It states that database modifications must follow all or none,
means if a part of a transaction fails then the entire transaction fails.