SQL Server Database Interview Questions and Answers
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
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)
(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;
OR
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)
OR
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 -----------------------------------------------
Nice Job 👍
ReplyDeleteThank you for visiting my first blog.
Delete