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.