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')
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 <>)
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
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
select * from [dbo].[EmployeeDetails] where salary =(select MIN(salary) from (select top(4) * from [dbo].[EmployeeDetails] order by salary desc) as T
