SQL Server Database Interview Questions and Answers

1) You have two columns in source table in which the col1 may contain duplicate values, All the duplicate values in col2 will be transformed as comma separated in the column col2 of target table Answer: --Select Query in table
Query: select * from tbl1

col1	col2
a	x
b	y
c	z
a	m
b	n

Solution:

Select col1, STRING_AGG(col2,',') Result

from tbl1 group by col1 --OR

select col1, STRING_AGG (col2,',') WITHIN GROUP (ORDER BY col2 ) Result from tbl1 group by col1

Output:

col1	Result
a	x ,m
b	n ,y
c	z

2) Reading a source file with salary prefix $, in the target the Sal column must store in number.
Answer:
select * from source;

empno	ename	job	managerid	firedate	salary	deptno
7369	Smith	Clerk	7902	12/17/1980	$800	20
7499	Allen	Salesman	7698	2/12/1981	$1,300	30

select *, replace (salary,'$','') salary_without_$ from source

Output:

empno	ename	job	managerid	firedate	salary	deptno	salary_without_$
7369	Smith	Clerk	7902	12/17/1980	$800	20	800
7499	Allen	Salesman	7698	2/12/1981	$1,300	30	1300

Date: - 20-02-2022

3) How to produce rows in target table with every row as sum of all previous rows in source table?

Answer:

select * from prerowsum;

id	sal
1	200
2	300
3	500
4	560

Solution:
select t1.id, sum(t2.sal) Total_Sum--, t1.id,t1.sal,t2.sal

from prerowsum t1

inner join prerowsum t2 on t1.id>=t2.id

group by t1.id

--OR

select id,sal,sum(sal) over(order by id) Next_Sal

from prerowsum;

--OR

with t1 as (

select id ,sal,ISNULL(lag(sal,1) over (order by sal),0) Next_Sal from prerowsum)

select id,sal,cast(next_sal as int)Next_Salary,sal+cast(next_sal as int) from t1

Output:

id	Total_Sum
1	200
2	500
3	1000
4	1560

Query: --4) How do you load unique records into one target table and duplicate records into different target table? */

Answer:

--For duplicate row:-

select col1, count(*) Duplicate_Rows from tbl1 group by col1 having count(*)>1

output:

col1	Duplicate_Rows
a	2
b	2

--For Unique Records:-

select col1, count(*) Unique_Records from tbl1 group by col1 having count(*)=1

--/*output:

col1	Unique_Records
c	1

Query: --5) How do you load first and last records into target table

Answer:

use Interview

Select * from FirstLastRow

id	name	prize	company
1	Alex	10000	C1
2	Mary	13000	C1
3	Noor	4000	C2
4	Kunal	2000	C2
5	Constance	9000	C3
6	Nicole	12000	C3
7	Fred	10000	C1

--first record union

Select top 1 * from FirstLastRow

--last record

Select top 1 * from FirstLastRow order by id desc

id	name	prize	company
1	Alex	10000	C1

id	name	prize	company
7	Fred	10000	C1

----- About Subquery create the employee table: -

--Subquery: -

It is an SQL query which is placed inside another SQL query.

• Scalar Subquery: - Which have one row and one column

• MultiRow Subquery: -

a) Which have one row and multiple column

b) Which have one column and multiple column

• Correlated Subquery: - where inner subquery is dependent on outer query

Example:

use Interview --database name is Interview

--Create table query:

create table emp_subquery ( emp_id int primary key identity (101,1) NOT NULL, emp_name nvarchar(50), dept_name nvarchar(50), salary decimal (18,2) )

--Select Query on table:-

select * from emp_subquery

emp_id	emp_name	dept_name	salary	GETDATE
101	Mohan	Admin	5788.13	5/17/2022
102	Rajkumar	HR	8103.38	5/17/2022
103	Akbar	IT	4630.5	5/17/2022
104	Dorvin	Finance	7524.56	5/17/2022
105	Rohit	HR	5788.13	5/17/2022
106	Rajesh	Finance	5788.13	5/17/2022
107	Mohit	Admin	6366.94	5/17/2022
108	Krishna	HR	6945.75	5/17/2022
109	Sunita	HR	10418.63	5/17/2022
110	Radhe	IT	7524.56	5/17/2022
111	Sita	Finance	2894.06	5/17/2022
112	Ram	Finance	92610	5/17/2022
114	Shiva	HR	15750	6/5/2022

Query --6) Find the employees who's salary is more than the average salary earned by all employees

Answer:

--a) Find average salary earned by all the employees

select avg(salary) from emp_subquery

Output:

avg_salary

13856.36692

use Interview

--b) Employee who's salary is more than the avg salary: -

--First way: - with Subquery select * from emp_subquery

--outer query / main query where salary > (select avg(salary) from emp_subquery);

--(query) Called Inner query / Subquery/ Standalone SQL query (because it's independent query)

--Second way: - with subquery as table

Query: select a.* from emp_subquery a, (select avg(salary) salary from emp_subquery)b where a.salary>b.salary

Output:

--OR

--Third way: - by using join operator

select a.* from emp_subquery a join (select avg(salary) salary from emp_subquery)b on a.salary>b.salary

--Output:

emp_id	emp_name	dept_name	salary	GETDATE
112	Ram	Finance	92610	5/17/2022
114	Shiva	HR	15750	6/5/2022

Query: --7) Find the employee who earn the highest salary in each department

Answer:

select * from emp_subquery order by dept_name

Answer:

emp_id	emp_name	dept_name	salary	GETDATE
101	Mohan	Admin	5788.13	5/17/2022
107	Mohit	Admin	6366.94	5/17/2022
104	Dorvin	Finance	7524.56	5/17/2022
111	Sita	Finance	2894.06	5/17/2022
112	Ram	Finance	92610	5/17/2022
106	Rajesh	Finance	5788.13	5/17/2022
114	Shiva	HR	15750	6/5/2022
105	Rohit	HR	5788.13	5/17/2022
102	Rajkumar	HR	8103.38	5/17/2022
108	Krishna	HR	6945.75	5/17/2022
109	Sunita	HR	10418.63	5/17/2022
110	Radhe	IT	7524.56	5/17/2022
103	Akbar	IT	4630.5	5/17/2022

--a) first find max salary in each department:-

select dept_name,max(salary) Max_Sal from emp_subquery group by dept_name

/*Output:-

select * from emp_subquery where (salary) in (select dept_name,max(salary) sal from emp_subquery group by dept_name )

dept_name	Max_Sal
Admin	6366.94
Finance	92610
HR	15750
IT	7524.56

/*If we use = in place of (in) then It'll through an error messagbelow:low :-

Error Message: - Msg 512, Level 16, State 1, Line 154 Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=, <, <=, >, >= or when the subquery is used as an expression.

select distinct a.* from emp_subquery a, (select max(salary) sal from emp_subquery group by dept_name) b where a.salary=b.sal

--OR

select distinct a.* from emp_subquery a

join (select max(salary) sal from emp_subquery group by dept_name) b on a.salary=b.sal

Output:

emp_id	emp_name	dept_name	salary	GETDATE
104	Dorvin	Finance	7524.56	5/17/2022
107	Mohit	Admin	6366.94	5/17/2022
110	Radhe	IT	7524.56	5/17/2022
112	Ram	Finance	92610	5/17/2022
114	Shiva	HR	15750	6/5/2022

--Multiple Row:- 1 Column and Multiple Row

Query: --8) Find department who don't have any employee?

create table department_subquery ( dept_id int primary key identity (1,1), dept_name nvarchar(50), location nvarchar(100) )

--Multiple Row Subquery:-

Query: --9) Find department who don't have any employee?

Select * from emp_subquery

emp_id	emp_name	dept_name	salary	GETDATE
101	Mohan	Admin	5788.13	5/17/2022
102	Rajkumar	HR	8103.38	5/17/2022
103	Akbar	IT	4630.5	5/17/2022
104	Dorvin	Finance	7524.56	5/17/2022
105	Rohit	HR	5788.13	5/17/2022
106	Rajesh	Finance	5788.13	5/17/2022
107	Mohit	Admin	6366.94	5/17/2022
108	Krishna	HR	6945.75	5/17/2022
109	Sunita	HR	10418.63	5/17/2022
110	Radhe	IT	7524.56	5/17/2022
111	Sita	Finance	2894.06	5/17/2022
112	Ram	Finance	92610	5/17/2022
114	Shiva	HR	15750	6/5/2022

Select * from department_subquery

dept_id	dept_names	location
1	HR	Bangalore
2	IT	Bangalore
3	Finance	Mumbai
4	Marketing	Banglore
5	Sales	Mumbai
6	Admin	Hydrabad
7	Quantity	Karnataka
8	Sales	Ahemdabad

Answer:

use Interview

select distinct dept_names

from department_subquery

where dept_names not in (select distinct dept_name from emp_subquery)

dept_names

Marketing

Quantity

Sales

--OR

--Find the department who have not any employee

select d.* from department_subquery d

where not exists (select * from emp_subquery e where d.dept_names = e.dept_name)

dept_id	dept_names	location
4	Marketing	Banglore
5	Sales	Mumbai
7	Quantity	Karnataka
8	Sales	Ahemdabad

--Correlated Subquery: - --

Query10) Find employees in each department who earn more than the average salary in that department?

use Interview

Answer:

select a.*,b.salary

from emp_subquery a, (select dept_name,avg(salary) salary from emp_subquery group by dept_name) b

where a.dept_name=b.dept_name and a.salary>b.salary

emp_id	emp_name	dept_name	salary	GETDATE	salary
107	Mohit	Admin	6366.94	5/17/2022	6077.535
112	Ram	Finance	92610	5/17/2022	27204.1875
114	Shiva	HR	15750	6/5/2022	9401.178
109	Sunita	HR	10418.63	5/17/2022	9401.178
110	Radhe	IT	7524.56	5/17/2022	6077.53

--Correlated Subquery where inner query related to outer query: -

select * from emp_subquery a where a.salary> (select avg(Salary) from emp_subquery b where a.dept_name=b.dept_name)

emp_id	emp_name	dept_name	salary	GETDATE
107	Mohit	Admin	6366.94	5/17/2022
112	Ram	Finance	92610	5/17/2022
114	Shiva	HR	15750	6/5/2022
109	Sunita	HR	10418.63	5/17/2022
110	Radhe	IT	7524.56	5/17/2022

--Note :-

--1) We cannot correlate subquery standalone/independently

--2) For every single record which is processed by outer query the correlated subquery is executed.

--Date: - 06-03-2022

--Nested Sub Query: - query inside have another subquery have another subquery called nested subquery.

Example:

--Table create

create table sales_Nested_subquery

(

store_id int,

store_name nvarchar(50),

product_name nvarchar(50),

quantity int,

price int

)

select * from sales_Nested_subquery;

store_id	store_name	product_name	quantity	price
1	Apple Store 1	iPhone 13 Pro	1	1000
1	Apple Store 1	MacBook pro 14	3	6000
1	Apple Store 1	AirPods Pro	2	500
2	Apple Store 1	iPhone 13 pro	2	2000
3	Apple Store 1	iPhone 12 Pro	1	750
3	Apple Store 1	MacBook pro 14	1	2000
3	Apple Store 3	MacBook pro 14	1	2000
3	Apple Store 3	MacBook Air	4	4400
3	Apple Store 3	iPhone 13	2	1800
3	Apple Store 3	AirPods Pro	3	750
4	Apple Store 4	iPhone 12 Pro	2	1500
4	Apple Store 4	MacBook pro 16	1	3500

--Q 11) Find Stores who's sales where better than the average sales accros all stores.

--a) Find the total sales for each store.

Select store_name,sum(price) total_Sales from sales_Nested_subquery

group by store_name

store_name	total_Sales
Apple Store 1	12250
Apple Store 3	8950
Apple Store 4	5000

--b) find avg sales for all the stores.

Select store_name,avg(price*quantity) avg_Sales

from sales_Nested_subquery

group by store_name

store_name	avg_Sales
Apple Store 1	4458
Apple Store 3	6362
Apple Store 4	3250

--Q12) Find Stores who's sales where better than the average sales accross all stores.

--a) Find the total sales for each store.

Select store_name,sum(price) total_Sales from sales_Nested_subquery

group by store_name

store_name	total_Sales
Apple Store 1	12250
Apple Store 3	8950
Apple Store 4	5000

--b) find avg sales for all total sales.

Select avg(total_sales) avg_sales

from (Select store_name,sum(price) total_Sales from sales_Nested_subquery

group by store_name)x

avg_sales

8733

--c) compare a) & b)

select *

from (Select store_name,sum(price) total_Sales from sales_Nested_subquery

group by store_name)total_sales

join

(Select avg(total_sales) avg_sales

from (Select store_name,sum(price) total_Sales from sales_Nested_subquery

group by store_name)x)average_Sales

on total_sales.total_Sales>average_Sales.avg_sales

--OR

--Implementation by using with clause: -

with Sales as

(Select store_name,sum(price) total_Sales from sales_Nested_subquery group by store_name)

select *

from Sales t1

join

(Select avg(total_sales) avg_sales

from Sales)t2

on t1.total_Sales>t2.avg_sales

store_name	total_Sales	avg_sales
Apple Store 1	12250	8733
Apple Store 3	8950	8733

--d) Subquery with Having Clause :-

--Q 13) Find the stores who have sold more units than the average units sold by all stores.

select * from sales

store_id	store_name	product_name	quantity	price
1	Apple Store 1	iPhone 13 Pro	1	1000
1	Apple Store 1	MacBook pro 14	3	6000
1	Apple Store 1	AirPods Pro	2	500
2	Apple Store 2	iPhone 13 pro	2	2000
3	Apple Store 1	iPhone 12 Pro	1	750
3	Apple Store 1	MacBook pro 14	1	2000
3	Apple Store 3	MacBook pro 14	1	2000
3	Apple Store 3	MacBook Air	4	4400
3	Apple Store 3	iPhone 13	2	1800
3	Apple Store 3	AirPods Pro	3	750
4	Apple Store 4	iPhone 12 Pro	2	1500
4	Apple Store 4	MacBook pro 16	1	3500
3	Apple Store 3	MacBook pro 14	1	2000
3	Apple Store 3	MacBook Air	4	4400
3	Apple Store 3	iPhone 13	2	1800
3	Apple Store 3	AirPods Pro	3	750
4	Apple Store 4	iPhone 12 Pro	2	1500
4	Apple Store 4	MacBook pro 16	1	3500

Answer:

select store_name, sum(quantity) Total_Qty

from sales

group by store_name

having sum(quantity)>(select avg(quantity) avg_unit from Sales)

store_name	Total_Qty
Apple Store 1	8
Apple Store 3	20
Apple Store 4	6

Date: - 11-03-2022

---We can use subquery in below command such as: -

i) Insert

ii) Update

iii) Delete

--Insert

select * into Insert_table_check from (select store_name, sum(quantity) Total_Qty

from sales

group by store_name

having sum(quantity)> (select avg(quantity) avg_unit from Sales)) a

Answer:

select * from Insert_table_check

store_name	Total_Qty
Apple Store 1	8
Apple Store 3	20
Apple Store 4	6

--Q.14) Insert data to employee history table. Make sure not insert duplicate records.

--created table to insert data into history table by using subquery: -

create table employee_history_Insert_subquery

(

emp_id int,

emp_name nvarchar(50),

dept_name nvarchar(50),

salary decimal (18,2),

location nvarchar(50)

)

Select * from emp_subquery

emp_id	emp_name	dept_name	salary	GETDATE
101	Mohan	Admin	5788.13	5/17/2022
102	Rajkumar	HR	8103.38	5/17/2022
103	Akbar	IT	4630.5	5/17/2022
104	Dorvin	Finance	7524.56	5/17/2022
105	Rohit	HR	5788.13	5/17/2022
106	Rajesh	Finance	5788.13	5/17/2022
107	Mohit	Admin	6366.94	5/17/2022
108	Krishna	HR	6945.75	5/17/2022
109	Sunita	HR	10418.63	5/17/2022
110	Radhe	IT	7524.56	5/17/2022
111	Sita	Finance	2894.06	5/17/2022
112	Ram	Finance	92610	5/17/2022
114	Shiva	HR	15750	6/5/2022

select * from department_subquery

dept_id	dept_names	location
1	HR	Bangalore
2	IT	Bangalore
3	Finance	Mumbai
4	Marketing	Banglore
5	Sales	Mumbai
6	Admin	Hydrabad
7	Quantity	Karnataka
8	Sales	Ahemdabad

insert into employee_history_Insert_subquery

Select e.emp_id,e.emp_name,d.dept_names,e.salary,d.location

from emp_subquery e join department_subquery d

on e.dept_name=d.dept_names

where not exists (select * from employee_history_Insert_subquery eht --To avoid duplicate record insert

where eht.emp_id=e.emp_id)

order by e.emp_id

emp_id	emp_name	dept_name	salary	location	hire_date_yyyy_mm_dd
101	Mohan	Admin	4000	Hydrabad	10/25/2010
107	Mohit	Admin	5500	Hydrabad	8/15/2009
104	Dorvin	Finance	6500	Mumbai	8/28/2009
106	Rajesh	Finance	5000	Mumbai	5/20/2012
111	Sita	Finance	2500	Mumbai	11/16/2017
105	Rohit	HR	3000	Bangalore	3/31/2006
102	Rajkumar	HR	3000	Bangalore	8/15/2018
108	Krishna	HR	4000	Bangalore	9/15/2022
109	Sunita	HR	7000	Bangalore	2/28/2021
110	Reena	IT	6500	Bangalore	8/19/2019
103	Akbar	IT	4000	Bangalore	1/26/2022

--Note:-

--1) First time query execution It'll show message "(24 rows affected)"

--2) Second and more than one time query execute than it'll show message "(0 rows affected)".

--3) If you try to update identity field than It'll show message:-

--update department_subquery set dept_id=8, dept_names='Quantity', location='Karnataka' where dept_id=7

----Message:-

----Msg 8102, Level 16, State 1, Line 400

----Cannot update identity column 'dept_id'.

--ii) Update with Subquery: - check link to know how to write subquery with update statement as below link :- Click Here

/* Perform an update with a correlated subquery

USE AdventureWorks;

UPDATE d

SET Name =

(

SELECT Name FROM

BackupOfAdventureWorks.HumanResources.Department

WHERE DepartmentID = d.DepartmentID

)

FROM HumanResources.Department d;

--Perform an update using JOIN

USE AdventureWorks;

UPDATE d

SET d.Name = bd.Name

FROM HumanResources.Department d

JOIN BackupOfAdventureWorks.HumanResources.Department bd

ON bd.DepartmentID = d.DepartmentID;

--Q 15) Give 10% increment to all employees in Bangalore location based on the maximum salary earned by an employee in each department. Only consider employees in employee_history table. */

Answer: -

--select * from emp_subquery where dept_name ='Admin' order by dept_name

--select * from employee_history_Insert_subquery where location='Hyderabad'

--select * from department_subquery where location='Hyderabad'

emp_id	emp_name	dept_name	salary	GETDATE
101	Mohan	Admin	5788.13	5/17/2022
107	Mohit	Admin	6366.94	5/17/2022

-----------------------1st way try

begin tran

Update emp_subquery

set salary = (select max(salary)+(max(salary)*0.1)

from employee_history_Insert_subquery eh

where eh.dept_name=dept_name

)

where dept_name in (select dept_names from department_subquery where location='Hydrabad')

and emp_id in (select emp_id from employee_history_Insert_subquery)

Output:

(2 rows affected)

Completion time: 2023-07-16T16:47:08.1111763+05:30

rollback;

Commands completed successfully.

Completion time: 2023-07-16T16:48:23.9702571+05:30

---------------2nd Way tries

select * from emp_subquery

begin tran

update emp_subquery

set salary = (select salary*0.1+salary from (

select dept_name,salary,ROW_NUMBER() over(partition by dept_name order by salary desc) rn

from employee_history_Insert_subquery

where location='Hydrabad' and emp_id=emp_id and dept_name=dept_name)a

where a.rn=1

)

where dept_name=(select eh.dept_name from employee_history_Insert_subquery eh where eh.emp_id=emp_id and location='Bangalore')

/* Output:-

(13 rows affected)

Msg 512, Level 16, State 1, Line 403

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Completion time: 2023-07-16T16:49:21.0005520+05:30

--Delete with Subquery:-

select * from emp_subquery

select * from department_subquery

--Q. 16) Delete all departments who don't have any employees

begin tran

delete from department_subquery

where dept_names in (

select distinct d.dept_names

from department_subquery d

where not exists (select *

from emp_subquery e

where d.dept_names=e.dept_name

)

(4 rows affected)

Completion time: 2023-07-16T16:50:38.6090850+05:30

select * from department_subquery

rollback;

Commands completed successfully.

Completion time: 2023-07-16T16:51:23.5804246+05:30

-- ******* Different SQL Clause where subquery is allow:-

a) Select Clause

b) Form Clause: - above example

c) Where Clause:- check Q3) example

d) Having Clause :- check Q 13)

--a) Subquery with Select Clause:- We'll understand it by the below example.

--Q17) Fetch all employees details and add remark to those employees who earn more than the average pay.

use Interview

select * from emp_subquery

emp_id	emp_name	dept_name	salary	GETDATE
101	Mohan	Admin	5788.13	5/17/2022
102	Rajkumar	HR	8103.38	5/17/2022
103	Akbar	IT	4630.5	5/17/2022
104	Dorvin	Finance	7524.56	5/17/2022
105	Rohit	HR	5788.13	5/17/2022
106	Rajesh	Finance	5788.13	5/17/2022
107	Mohit	Admin	6366.94	5/17/2022
108	Krishna	HR	6945.75	5/17/2022
109	Sunita	HR	10418.63	5/17/2022
110	Radhe	IT	7524.56	5/17/2022
111	Sita	Finance	2894.06	5/17/2022
112	Ram	Finance	92610	5/17/2022
114	Shiva	HR	15750	6/5/2022

select *,

(select avg(salary) from emp_subquery) as 'AVG_SALES',

case when salary>(select avg(salary) from emp_subquery) then 'Higer_Than_Average_Sales' End as Remarks

from emp_subquery

emp_id	emp_name	dept_name	salary	GETDATE	AVG_SALES	Remarks
101	Mohan	Admin	5788.13	5/17/2022	13856.36692	NULL
102	Rajkumar	HR	8103.38	5/17/2022	13856.36692	NULL
103	Akbar	IT	4630.5	5/17/2022	13856.36692	NULL
104	Dorvin	Finance	7524.56	5/17/2022	13856.36692	NULL
105	Rohit	HR	5788.13	5/17/2022	13856.36692	NULL
106	Rajesh	Finance	5788.13	5/17/2022	13856.36692	NULL
107	Mohit	Admin	6366.94	5/17/2022	13856.36692	NULL
108	Krishna	HR	6945.75	5/17/2022	13856.36692	NULL
109	Sunita	HR	10418.63	5/17/2022	13856.36692	NULL
110	Radhe	IT	7524.56	5/17/2022	13856.36692	NULL
111	Sita	Finance	2894.06	5/17/2022	13856.36692	NULL
112	Ram	Finance	92610	5/17/2022	13856.36692	Higer_Than_Average_Sales
114	Shiva	HR	15750	6/5/2022	13856.36692	Higer_Than_Average_Sales

--Date:- 16-03-2022

--Merge Statement: -

create table mergeA

(

e_id int,

e_name nvarchar(50),

e_salary decimal(18,2),

e_age int,

e_gender nvarchar(10),

e_dept nvarchar(20)

)

create table mergeB

(

e_id int,

e_name nvarchar(50),

e_salary decimal(18,2),

e_age int,

e_gender nvarchar(10),

e_dept nvarchar(20)

)

Select * from mergeSrc

e_id	e_name	e_salary	e_age	e_gender	e_dept
1	Sam	93000	40	Male	Operations
2	Bob	80000	21	Male	Support
3	Amme	13000	25	Female	Analytics
4	Joff	11200	27	Female	Contact
7	Adam	10000	28	Male	Content
8	Jeff	85000	37	Male	Tech

Select * from mergeTgt

e_id	e_name	e_salary	e_age	e_gender	e_dept
9	Ram	100000	25	Male	IT
9	Ram	100000	25	Male	IT
9	Ram	100000	25	Male	IT
9	Ram	100000	25	Male	IT
5	Matt	159000	33	Male	Sales
9	Ram	100000	25	Male	IT
6	Joff	11200	27	Female	Contact
9	Ram	100000	25	Male	IT
1	Sam	93000	40	Male	Operations
2	Bob	80000	21	Male	Support
3	Amme	13000	25	Female	Analytics
4	Joff	11200	27	Female	Contact
7	Adam	10000	28	Male	Content
8	Jeff	85000	37	Male	Tech

--Merge command using :-

merge mergeTgt as T

using mergeSrc as S

on T.e_id=S.e_id

when matched

then update set t.e_salary=s.e_salary, t.e_age=s.e_age

when not matched by target

then insert (e_id,e_name,e_salary,e_age,e_gender,e_dept)

values(s.e_id,s.e_name,s.e_salary,s.e_age,s.e_gender,s.e_dept);

(6 rows affected)

Completion time: 2023-08-12T17:09:46.4191890+05:30

select * from mergeTgt_bkp_16_03_2022

Select * from mergeTgt

e_id	e_name	e_salary	e_age	e_gender	e_dept
1	Sam	95000	40	Male	Operations
2	Bob	80000	21	Male	Support
3	Amme	125000	25	Female	Analytics
4	Julia	112000	30	Male	Analytics
5	Matt	159000	33	Male	Sales
8	Jeff	112000	27	Male	Operations

e_id	e_name	e_salary	e_age	e_gender	e_dept
9	Ram	100000	25	Male	IT
9	Ram	100000	25	Male	IT
9	Ram	100000	25	Male	IT
9	Ram	100000	25	Male	IT
5	Matt	159000	33	Male	Sales
9	Ram	100000	25	Male	IT
6	Joff	11200	27	Female	Contact
9	Ram	100000	25	Male	IT
1	Sam	93000	40	Male	Operations
2	Bob	80000	21	Male	Support
3	Amme	13000	25	Female	Analytics
4	Joff	11200	27	Female	Contact
7	Adam	10000	28	Male	Content
8	Jeff	85000	37	Male	Tech

--SavePoint:-

create table savepoint

(

id int,

name nvarchar(20)

)

select * from savepoint

id	name
1	Ram
2	Shyam
3	Krishna
4	Mohan
5	Kanha

begin transaction

insert into savepoint values(3,'Krishna')

select * from savepoint

----id name

----1 Ram

----2 Shyam

----3 Krishna

rollback--Not commited then does the rollback

select * from savepoint

id name

1 Ram

2 Shyam

-----Commit

begin transaction

insert into savepoint values(3,'Krishna')

commit

--Can not rollback after commit.

select * from savepoint

rollback

Msg 3903, Level 16, State 1, Line 633

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

select * from savepoint

--delete from savepoint where id=4

----------------Savepoint

begin transaction

insert into savepoint values(5,'Mukesh')

save tran save1

--id name

--1 Ram

--2 Shyam

--3 Krishna

--4 Mohan

________________________________________

---Date :- 22-03-2022

Select * from savepoint

begin transaction

insert into savepoint values(5,'Kanha');

save tran sp1;

commit

begin transaction

insert into savepoint values(6,'vishnu');

save tran sp2;

--not comminted

begin transaction

insert into savepoint values(7,'Vinit');

save tran sp3;

begin transaction

insert into savepoint values(8,'Vinit');

save tran sp4;

select * from savepoint;

--not commited

--Without savepoint did the transaction:-

insert into savepoint values(9,'Harsh');

--Now if we rollback then last row which is inserted without under transaction then it'll be roll back only

select * from savepoint;

/*output:-

id name

1 Ram

2 Shyam

3 Krishna

4 Mohan

5 Kanha

6 vishnu

7 Kranti

--After rollback without using savepoint name;

rollback; --It'll rollback all the tranaction with is not commited whatever is savepoint or without transaction.

select * from savepoint;

Result :-

id name

1 Ram

2 Shyam

3 Krishna

4 Mohan

5 Kanha

Query: --18) WAQ an SQL query to report the different between number of apples and oranges sold each day.

Note: Return the result table ordered by sale_date in format('YYYY-MM-DD')

--Expected Result:

sale_date diff

2020-05-01 2

2020-05-02 0

2020-05-03 20

2020-05-04 -1

Solution:

Create table Sale

(

sale_date date,

--fruit varchar(10) --default 'apples','oranges'

fruit varchar(10) constraint fruit_chk check (fruit in ('apples','oranges')),

sold_num int,

primary key(sale_date, fruit)

)

Select * from Sale;

sale_date	fruit	sold_num
5/1/2020	apples	10
5/1/2020	oranges	8
5/2/2020	apples	15
5/2/2020	oranges	15
5/3/2020	apples	20
5/3/2020	oranges	0
5/4/2020	apples	15
5/4/2020	oranges	16

Select sale_date, diff from(

Select *,sold_num-lead(sold_num,1) over(partition by sale_date order by sale_date) diff

from Sale)a where diff is not null

--OR

select a.sale_date, a.sold_num-b.sold_num as diff

from

(Select sale_date,fruit,sold_num

from Sale where fruit='apples')a inner join

(Select sale_date,fruit,sold_num

from Sale where fruit='oranges')b on a.sale_date=b.sale_date

sale_date	diff
5/1/2020	2
5/2/2020	0
5/3/2020	20
5/4/2020	-1

Exist Operator and Joins Operator Difference:-

Select * from customers;

customer_id	customer_name	customer_email
1	Shashank	abc@gmail.com
2	Rahul	aaa@gmail.com
3	Ajay	klm@gmail.com
4	Nitin	poc@gmail.com
5	Naveen	mnc@gmail.com

Select * from orders;

order_id	customer_id	amount	status
101	1	550	Delivered
102	2	350	Delivered
103	1	220	Cancelled
104	3	660	Delivered
105	3	300	Delivered

--Is the Shashank placed any order or not

Select c.customer_id, c.customer_name, c.customer_email, o.order_id,o.amount,o.status

from customers c

Left join orders o on c.customer_id=o.customer_id

where c.customer_name='Shashank' and o.order_id is not null

customer_id	customer_name	customer_email	order_id	amount	status
1	Shashank	abc@gmail.com	101	550	Delivered
1	Shashank	abc@gmail.com	103	220	Cancelled

--- OR

Select distinct c.*

from customers c

Left join orders o on c.customer_id=o.customer_id

where c.customer_name='Shashank' --and o.order_id is not null

--using left or right join if you remove the right and left table column from the select statement then appeared NULL will not display becasue the column which don't have value appear NULL by deafult but if you remove those column then null will be not appear becasue NULL will appear only for those column which contain the NULL value.

customer_id	customer_name	customer_email	order_id	amount	status
1	Shashank	abc@gmail.com	101	550	Delivered

--OR

--Query using Exist operator in SQL :-

Select *

from customers c

where exists (Select order_id from orders o where c.customer_id=o.customer_id and c.customer_name='Shashank'

)

customer_id	customer_name	customer_email
1	Shashank	abc@gmail.com

--Explaination about Exist Operator: -

--Exists operator checks if any record matches with another table, then it''ll show thosrecordsrd one time only not a multiple time like as join do and to fix it we can use the distinct keyword using joins operator.

--Exists work like as switch because after got one match then if is it again match the matching breaks.

Q. )

Tables Description: - (user_id, time_stamp) is the primary key for this table.

Each row contains information about the login time for the user with ID user_id.

Question: WAQ to report the latest login for all users in the year 2020.

Do not include the users who did not login in 2020.

The query result is in the following example:

Select * from Logins

+-----------+------------------------+

|user_id | time_stamp |

+-----------+------------------------|

|2 |2019-08-25 07:59:08.000 |

|2 |2020-01-16 02:49:50.000 |

|6 |2019-03-07 00:18:15.000 |

|6 |2020-06-30 15:06:07.000 |

|6 |2021-04-21 14:06:06.000 |

|8 |2020-02-01 05:10:53.000 |

|8 |2020-12-30 00:46:50.000 |

|14 |2019-07-14 09:00:00.000 |

|14 |2021-01-06 11:59:59.000 |

+------------+-----------------------+

Query Solution:

with t as (

Select user_id,time_stamp, row_number() over (partition by user_id order by time_stamp desc)rn

from Logins

where year(time_stamp)='2020')

Select user_id, time_stamp last_stamp

from t

where rn=1 order by time_stamp desc;

Select user_id,max(time_stamp)

from Logins

where year(time_stamp)='2020'

group by user_id;

Output:

+-----------+------------------------+

|user_id | last_stamp |

+-----------+------------------------|

| 8 |2020-12-30 00:46:50.000 |

| 6 |2020-06-30 15:06:07.000 |

| 2 |2020-01-16 02:49:50.000 |

+------------+-----------------------+

--Problem Statment:-

Remove all reversed number pairs from given table, keep only one (random if somthing exists)

Select * from number_pairs

|A | B |

-----------

|1 | 2 |

|3 | 2 |

|2 | 4 |

|2 | 1 |

|5 | 6 |

|4 | 2 |

-----------

Solution :-

Select t1A A, t1B B from (

Select t1.A t1A,t1.B t1B

from number_pairs t1

inner join number_pairs t2 on t2.B=t1.A and t2.A=t1.B)t

where t1A < t1B

union

select * from number_pairs t1

where not exists

(Select *

from number_pairs t2 where t2.B=t1.A and t2.A=t1.B)

Select t1.A as A , t1.B as B

from number_pairs t1

left join number_pairs t2 on t2.B=t1.A and t2.A=t1.B

where t1.A < t2.A

union

Select t1.A as A , t1.B as B

from number_pairs t1

left join number_pairs t2 on t2.B=t1.A and t2.A=t1.B

where t2.A IS NULL

A B

1 2

2 4

3 2

5 6

----------------------------------------------- Exit -----------------------------------------------

Search This Blog

Welcome