microsoft t-sql

correlated subquery

join and union

commonly used functions

DATE

while loop

Case

SELECT ProductAlternateKey, Category = CASE ProductLine WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END, EnglishProductName FROM dbo.DimProduct ORDER BY ProductKey;

CREATE TABLE Emp_Table ( Emp_No int primary key, Name varchar (50) NULL, DOB varchar (50) NULL, Gender char (10) NULL, Salary int NULL, City varchar (20) NULL, departmentid int);
create table Dept_Table(departmentid int,departmentname varchar(30))

INSERT INTO Emp_Table(Emp_No, Name, DOB, Gender, Salary, City,departmentid)VALUES (100,'Mahesh','1965-12-01','Male',30000,'Delhi',1),(101,'Suresh','1963-11-14','Male',34000,'Delhi',2),(102,'Rajat','1969-12-21','Male',23000,'Shimla',1),(103,'Kalpana','1961-12-01','Female',40000,'Goa',2),(104,'Neha','1971-12-01','Female',19000,'Goa',1),(105,'Sunita','1958-12-01','Female',50000,'Delhi',2)
insert into Dept_Table values (1,'IT'),(2,'Sales')

COALESCE

CREATE TABLE dbo.wages ( emp_id TINYINT NULL, hourly_wage DECIMAL NULL, salary DECIMAL NULL, commission DECIMAL NULL, num_sales TINYINT NULL ); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (1, 10.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (2, 20.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (3, 30.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (4, 40.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (5, NULL, 10000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (6, NULL, 20000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (7, NULL, 30000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (8, NULL, 40000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (9, NULL, NULL, 15000, 3); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (10,NULL, NULL, 25000, 2); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (11, NULL, NULL, 20000, 6); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (12, NULL, NULL, 14000, 4);
select * from wages;
SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary FROM dbo.wages ORDER BY TotalSalary;
TotalSalary10000.0020000.0020800.0030000.0040000.0041600.0045000.0050000.0056000.0062400.0083200.00120000.00

analytics functions - over (partition by <> order by <>)

create table employee( emp_ID int, emp_NAME varchar(50), DEPT_NAME varchar(50), SALARY int);
insert into employee values(101, 'Mohan', 'Admin', 4000);insert into employee values(102, 'Rajkumar', 'HR', 3000);insert into employee values(103, 'Akbar', 'IT', 4000);insert into employee values(104, 'Dorvin', 'Finance', 6500);insert into employee values(105, 'Rohit', 'HR', 3000);insert into employee values(106, 'Rajesh', 'Finance', 5000);insert into employee values(107, 'Preet', 'HR', 7000);insert into employee values(108, 'Maryam', 'Admin', 4000);insert into employee values(109, 'Sanjay', 'IT', 6500);insert into employee values(110, 'Vasudha', 'IT', 7000);insert into employee values(111, 'Melinda', 'IT', 8000);insert into employee values(112, 'Komal', 'IT', 10000);insert into employee values(113, 'Gautham', 'Admin', 2000);insert into employee values(114, 'Manisha', 'HR', 3000);insert into employee values(115, 'Chandni', 'IT', 4500);insert into employee values(116, 'Satya', 'Finance', 6500);insert into employee values(117, 'Adarsh', 'HR', 3500);insert into employee values(118, 'Tejaswi', 'Finance', 5500);insert into employee values(119, 'Cory', 'HR', 8000);insert into employee values(120, 'Monica', 'Admin', 5000);insert into employee values(121, 'Rosalin', 'IT', 6000);insert into employee values(122, 'Ibrahim', 'IT', 8000);insert into employee values(123, 'Vikram', 'IT', 8000);insert into employee values(124, 'Dheeraj', 'IT', 11000);COMMIT;
SELECT E.*,SUM(SALARY) OVER(partition by dept_name) SUMM,AVG(SALARY) OVER(partition by dept_name) AVGG,MIN(SALARY) OVER(partition by dept_name) MINN,MAX(SALARY) OVER(partition by dept_name) MAXX,rank() over(partition by dept_name order by salary desc) as rnk,dense_rank() over(partition by dept_name order by salary desc) as dense_rnk,row_number() over(partition by dept_name order by emp_id ASC) as rn,lag(salary) over(partition by dept_name order by emp_id) as prev_empl_sal,lead(salary) over(partition by dept_name order by emp_id) as next_empl_sal
FROM EMPLOYEE EORDER BY DEPT_NAME

CREATE TABLE [dbo].[table1]( [EmpID] [int] NULL, [EmpName] [varchar](50) NULL)

insert into table1(empid,empname) values (1,'vikas'),(2,'ashish'),(3,'manoj')
CREATE TABLE [dbo].[table2]( [EmpID] [int] NULL, [EmpName] [varchar](50) NULL)

insert into table2(empid,empname) values (4,'alok'),(2,'ashish'),(5,'rajat')
select * from table1select * from table2

select * from table1unionselect * from table2
select * from table1union allselect * from table2
select * from table1intersectselect * from table2

select * from table1exceptselect * from table2

select * from table2exceptselect * from table1

select ED.Emp_No,ED.Name,ED.Gender,ED.City,ED.DOB,ED.Salary,DT.departmentname from Emp_Table EDinner join( select e.departmentid,d.departmentname,max(Salary) as HighestSalary from Emp_Table e inner join Dept_Table d on e.departmentid=d.departmentid group by e.departmentid,d.departmentname) DTon ED.departmentid=DT.departmentid and ED.Salary=DT.HighestSalary

select top(1) * from (select top(4) * from [dbo].[EmployeeDetails] order by salary desc) as Torder by T.Salary asc

select * from [dbo].[EmployeeDetails] where salary =(select MIN(salary) from (select top(4) * from [dbo].[EmployeeDetails] order by salary desc) as T

Page updated

Google Sites

Report abuse