UNION,UNION ALL, EXCEPT and INTERSECT in sql

--First create two table  :-

CREATE TABLE [dbo].[t1](
[id] [int] NULL,
[name] [varchar](50) NULL
) 

CREATE TABLE [dbo].[t2](
[id] [int] NULL,
[name] [varchar](50) NULL
)

--Insert record in first table

insert into t1 values(1,'sk');
insert into t1 values(2,'jitu');
insert into t1 values(3,'Gita');
insert into t1 values(4,'Ram');
insert into t1 values(5,'Sita');

--Insert record in second table

insert into t2 values(1,'sk');
insert into t2 values(2,'ramu');
insert into t2 values(6,'Rajeev');

--INTERSECT


SELECT * FROM t1;






SELECT * FROM t2






<<< SELECT t1.id FROM t1 INTERSECT SELECT t2.id FROM t2

--UNION
SELECT t1.id FROM t1  UNION  SELECT t2.id FROM t2

--UNION ALL

SELECT t1.id FROM t1  UNION ALL  SELECT t2.id FROM t2

--EXCEPT

SELECT t1.id FROM t1  EXCEPT SELECT t2.id FROM t2

How to call action method in mvc without controller name

Create a specific route in your RouteConfig.cs file

routes.MapRoute(
  name: "About",
  url: "About",
  defaults: new { controller = "Home", action = "About" }
);




Get column name and column name count

declare @colindex int
declare @colcount int
declare @tableid int
declare @colname varchar(100)

set @tableid = ( select id from sysobjects where name='tblUsrShrProp' )
set @colcount = ( select COUNT(*) from syscolumns where id = @tableid )

set @colindex = 1
while @colindex <= @colcount
begin
  set @colname = ( select name from syscolumns where id = @tableid and colorder = @colindex )
  print @colname
  set @colindex = @colindex + 1
end

select @colcount as columncountname

How to get nth(1 or 2 or 3) highest salary in sql

--create table with Name Employee
CREATE TABLE Employee
(
id INT,
name VARCHAR(50),
salary INT
)

--Insert records in Employee table
INSERT INTO Employee VALUES(1,'ram',3000)
INSERT INTO Employee VALUES(2,'sita',3000)
INSERT INTO Employee VALUES(3,'Jit',600)
INSERT INTO Employee VALUES(4,'gita',30600)
INSERT INTO Employee VALUES(5,'Jai',3000)
INSERT INTO Employee VALUES(6,'ramu',30500)
INSERT INTO Employee VALUES(7,'ramu',4000)
INSERT INTO Employee VALUES(8,'ramu',5000)

--2nd highest salary using MAX
SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )
SELECT MAX(salary) As Salary From Employee Where Salary<(Select MAX(Salary) FROM Employee)

--3rd highest salary using MAX
SELECT MAX(salary) As Salary From Employee Where Salary<(Select MAX(Salary) FROM Employee
where Salary <(SELECT MAX(Salary) FROM Employee))

--3rd highest salary using sub query
SELECT TOP 1 Salary AS '3rd Highest Salary'
FROM (SELECT DISTINCT TOP 3 Salary FROM Employee ORDER BY Salary DESC) Result
ORDER BY Salary ASC

--3rd lowest salary using sub query
SELECT  TOP 1 Salary AS '3rd Lowest Salary'
FROM (SELECT DISTINCT TOP 3 Salary FROM Employee ORDER BY Salary ASC) Result
ORDER BY Salary DESC

--Nth highest salary using CTE with ROW_NUMBER function
--Disadvantage: It will not work with duplicate record
WITH CTE AS
(SELECT *,RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM Employee)
SELECT * FROM CTE WHERE RN = 3--(It can be nth)

--Nth highest salary using CTE with DENSE_RANK function
WITH EmployeeCTE AS
(SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DN  FROM  Employee)
SELECT TOP 1 salary FROM EmployeeCTE WHERE DN = 5 --(It can be nth)

--Nth highest salary 0 -max,1-2nd using sub query
SELECT * FROM Employee Emp1
WHERE (1) = (SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

--Nth highest salary and dense rank using with DENSE_RANK function
SELECT * FROM (SELECT *,DENSE_RANK() OVER(ORDER BY Salary)
As RowNum  FROM Employee) As Record WHERE Record.RowNum IN (3,3)
               

Can we write delete update and insert statement on view in sql server

Yes we can write the delete,insert and update query on view.
if we are creating a view using single table then we can write all delete,insert and update query.but when we are using multiple table in view we can not write select,update and delete query.