Collecttion SQL

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What is database replication? What are the different types of replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

    * Snapshot replication
    * Transactional replication (with immediate updating subscribers, with queued updating subscribers)

    * Merge replication

GET EMPLOYEE SALARY DETAILS

select *from EMPTB e where 2=(select count(distinct salary) 
from EMPTB m where m.salary>e.salary)

What is the use of CASCADE CONSTRAINTS?   we cant able to delete a record from the parent table if there is some corresponding record exists in the child table. for this we are using cascading constraint. with the help of cascading constraint we can able to delete a record from the parent table as well as all the corresponding records from the child table.even we can set some null values.