SQL-Assignment-23-02-2023_February batch
***********************************************************************************
Q1. Write an SQL query to display the empid alongwith their hierarchy.
Bossid with value NULL is the CEO with hierarchy as 0.
create table hierarchy(empid int,fname varchar(50),lname varchar(50),bossid int);
insert into hierarchy values(1,'Raj','Kumar',null);
insert into hierarchy values(2,'Mohan','Kumar',1);
insert into hierarchy values(3,'Abdul','Khan',4);
insert into hierarchy values(4,'John','Dan',1);
Output:
Id Hierarchy_Level DESIGNATION
1 0 CEO
2 1 DIRECTOR
4 1 DIRECTOR
3 2 MANAGER
with company_hierarchy(empid,hierarchy_level) as
(
select empid,0 as hierarchy_level from hierarchy where bossid is null
union all
select a.empid,hierarchy_level + 1 as hierarchy_level from hierarchy a join company_hierarchy b on
a.bossid = b.empid
)
select empid,hierarchy_level,decode(hierarchy_level,0,'CEO',1,'Director',2,'Manager') as Designation
from company_hierarchy;
*************************************************************************************
Q2.Write an SQL query to find the missing customer IDs.
The missing IDs are ones that are not in the Customers table but are in the range between 1 and the
maximum customer_id present in the table.
Explanation:
The maximum customer_id present in the table is 5, so in the range [1,5], IDs 2 and 3 are missing from
the table.
Table Script:
create table customers(customer_id int,customer_name varchar(50));
insert into customers values(1,'Alice');
insert into customers values(4,'Bob');
insert into customers values(5,'Charlie');
Soln:
with cte(id) as
(
select 1 as id from customers
union all
select id + 1 as id from cte where id < (select max(customer_id) from customers)
Tech Nest Academy
SQL-Assignment-23-02-2023_February batch
)
select distinct id from cte
minus
select customer_id from customers
***********************************************************************************
Q3. Write an SQL query without using window functions to list the student name whose marks have
continously increased from 1st semester till 4th semester.
CREATE TABLE STUD(NAME VARCHAR2(10),SEMESTER NUMBER,MARKS NUMBER);
INSERT INTO STUD VALUES('A',1,100);
INSERT INTO STUD VALUES('A',2,110);
INSERT INTO STUD VALUES('A',3,120);
INSERT INTO STUD VALUES('A',4,130);
INSERT INTO STUD VALUES('B',1,100);
INSERT INTO STUD VALUES('B',2,150);
INSERT INTO STUD VALUES('B',3,100);
INSERT INTO STUD VALUES('B',4,250);
INSERT INTO STUD VALUES('C',1,100);
INSERT INTO STUD VALUES('C',2,150);
INSERT INTO STUD VALUES('C',3,200);
INSERT INTO STUD VALUES('C',4,250);
Output:
A
C
Solution:
with cte(n,name) as
(
select 1,name from stud
union all
select a.n+1,b.name
from cte a join stud b
on a.n+1=b.semester
join stud c
on c.semester=a.n and b.name=c.name
where b.marks>c.marks
)
select name from cte having count(distinct n) = 4 group by name
My solution:
with cte(n,name,marks) as
(
select 1,name,marks from stud where semester =1
union all
select a.n+1,b.name,b.marks
from cte a join stud b
Tech Nest Academy
SQL-Assignment-23-02-2023_February batch
on a.n+1=b.semester and a.name=b.name and a.marks<b.marks
)
select name from cte group by name having count(distinct n) = 4
***********************************************************************************
Q4. Write an SQL query to display the below output using recursive cte
output
O
R
A
C
L
E
create table input(id varchar(50));
insert into input values('ORACLE');
with cte(id) as
(
select 1 as id from input
union all
select id + 1 as id from cte where id < (select len(val) from input)
) select substring(val,id,1) output from cte,input ;
My Solution:
with cte1(str,id,len) as
( select substr(id,1,1) as str,id, 1 as len from input
union all
select substr(id,len+1,1) as str,id,len+1 as len from cte1 where len < length(id))
select str from cte1;
output
O
R
A
C
L
E
***********************************************************************************
Q5. Write an SQL query to get the below output using recursive cte
output:
ORDERID ITEM QTY
O1 A1 1
O1 A1 1
O1 A1 1
Tech Nest Academy
SQL-Assignment-23-02-2023_February batch
O1 A1 1
O1 A1 1
O2 A2 1
O3 A3 1
O3 A3 1
O3 A3 1
create table orders
(
orderid varchar(5),
item varchar(5),
qty int
);
insert into orders values ('O1','A1', '5');
insert into orders values ('O2','A2','1') ;
insert into orders values ('O3','A3', '3');
Soln: USING RECURSIVE CTE
with cte(id,orderid,item) as
(
select 1 as id,a.orderid,a.item from orders a
union all
select id + 1 as id,b.orderid,b.item from cte,orders b where id < (select max(qty) from orders c where
b.orderid = c.orderid)
)
,cte2 as
(select distinct orderid,item,id,rank()over(partition by item order by item)qty from cte order by orderid)
select orderid,item,qty from cte2
***********************************************************************************
Q6. Write an SQL query to get the below output.
FAMILYNAME FATHERSON
Family1 John Tom
Family1 Tom David
Family1 David Joe
Family2 Ibrahim Mutallib
Family2 Mutallib Abdullah
Family2 Abdullah Yusuf
create table family(familyname varchar(50),grandfather varchar(50),father varchar(50),son
varchar(50),grandson varchar(50));
insert into family values ('Family1','John','Tom','David','Joe');
insert into family values ('Family2','Ibrahim','Mutallib','Abdullah','Yusuf');
Tech Nest Academy
SQL-Assignment-23-02-2023_February batch
with cte1 (familyname,father,son,hierarchy) as
(select Familyname,grandfather,father,0 as hierarchy from family
union all
select b.familyname,
case when b.son=a.father and hierarchy <2 then a.father when b.son=a.son and hierarchy=1 then a.son
end father,
case when b.son=a.father and hierarchy <2 then a.son when b.son=a.son and hierarchy=1 then
a.grandson end son,
hierarchy+1
from family a join cte1 b on (b.son=a.father and hierarchy <2) or (b.son=a.son and hierarchy=1)
)
select familyname,father,son from cte1 order by familyname,hierarchy
********************************End**************************************************
create table shopping(deptname varchar(50),location varchar(50),month varchar(50),profit int);
insert into shopping values('clothing','delhi','jan',200);
insert into shopping values('clothing','delhi','feb',300);
insert into shopping values('clothing','delhi','mar',500);
insert into shopping values('clothing','mumbai','jan',300);
insert into shopping values('clothing','mumbai','feb',200);
insert into shopping values('clothing','mumbai','mar',150);
insert into shopping values('clothing','chennai','jan',400);
insert into shopping values('clothing','chennai','feb',400);
insert into shopping values('clothing','chennai','mar',400);
insert into shopping values('sports','delhi','jan',200);
insert into shopping values('sports','delhi','feb',150);
insert into shopping values('sports','delhi','mar',100);
insert into shopping values('sports','mumbai','jan',300);
insert into shopping values('sports','mumbai','feb',400);
insert into shopping values('sports','mumbai','mar',500);
insert into shopping values('sports','chennai','jan',400);
insert into shopping values('sports','chennai','feb',500);
insert into shopping values('sports','chennai','mar',300);
insert into shopping values('appliances','delhi','jan',200);
insert into shopping values('appliances','delhi','feb',350);
insert into shopping values('appliances','delhi','mar',100);
insert into shopping values('appliances','mumbai','jan',350);
insert into shopping values('appliances','mumbai','feb',400);
insert into shopping values('appliances','mumbai','mar',500);
insert into shopping values('appliances','chennai','jan',400);
Tech Nest Academy
SQL-Assignment-23-02-2023_February batch
insert into shopping values('appliances','chennai','feb',500);
insert into shopping values('appliances','chennai','mar',200);
Dept and location where profit has continously increased month over month
My solution:
with cte1(dept,loc,mon,profit)as
(select deptname as dept,location as loc,month as mon, profit from shopping where
upper(month)='JAN'
union all
select a.dept,a.loc,b.month as mon,b.profit from cte1 a join shopping b
on a.dept=b.deptname and a.loc=b.location
and TO_CHAR ( TO_DATE ( a.mon, 'Mon') , 'MM') +1 = TO_CHAR ( TO_DATE ( b.month, 'Mon') , 'MM')
where a.profit < b.profit
)
select dept,loc from cte1 group by dept,loc having count(*) =3
order by dept,loc
Tech Nest Academy
