create table bus(
 bus_no int primary key,
 b_capacity int not null,
 depot_name varchar(20)
);

\d bus

create table route(
 route_no int primary key,
 source char(20),
 destination char(20),
 no_of_stations int
);

\d route

create table driver(
 driver_no int primary key,
 driver_name char(20),
 license_no int unique,
 address char(20),
 d_age int,
 salary float
);

\d driver

create table busdriver(
 bus_no int,
 driver_no int,
 date_of_duty_alloted date,
 shift int check(shift in(1,2)),
 primary key(bus_no,driver_no,date_of_duty_alloted),
 foreign key(bus_no) references bus(bus_no),
 foreign key(driver_no) references driver(driver_no)
);

\d busdriver

insert into bus(bus_no,b_capacity,depot_name) values
(101,50,'north depot'),
(102,40,'south depot'),
(103,55,'north depot'),
(104,45,'east depot');

select * from bus;

insert into route(route_no,source,destination,no_of_stations) values
(1,'Station a','Station b',10),
(2,'Station c','Station d',15),
(3,'Station a','Station d',20);

select * from route;

insert into driver(driver_no,driver_name,license_no,address,d_age,salary) values
(1,'john doe','12345','123 main st',55,50000),
(2,'jane smith','67890','456 elm st',40,60000),
(3,'emily davis','13579','789 oak st',60,70000),
(4,'michael brown','24680','321 pine st',30,55000);

select * from driver;

insert into busdriver(bus_no,driver_no,date_of_duty_alloted,shift) values
(101,1,'2023-08-15',1),
(101,2,'2024-01-10',2),
(102,3,'2023-06-01',1),
(103,4,'2024-02-25',2),
(104,1,'2024-03-30',1);

select * from busdriver;

create or replace function 
get_buses_by_route(route_no int)
returns table(
 bus_no int,
 b_capacity int,
 depot_name varchar
)as $$
begin
 return query
 select b.bus_no,b.b_capacity,b.depot_name
 from bus b
 join route r on r.route_no=r.route_no
 join busdriver bd on b.bus_no=bd.bus_no;
end;
$$ language plpgsql;

select * from get_buses_by_route(1);

create or replace function
get_drivers_by_shift_and_depot(shift int,depot_name varchar)
returns table(
 driver_no int,
 driver_name char(20),
 license_no int,
 address char(20),
 d_age int,
 salary float
)as $$
begin
 return query
 select d.driver_no,d.driver_name,d.license_no,d.address,d.d_age,d.salary
 from driver d
 join busdriver bd on d.driver_no=bd.driver_no
 join bus b on bd.bus_no=b.bus_no
 where bd.shift=bd.shift
 and b.depot_name=b.depot_name
 and bd.date_of_duty_alloted>'2020-07-01';
end;
$$ language plpgsql;

select * from get_drivers_by_shift_and_depot(1,'north depot');

create or replace function 
count_buses_by_source(source char(20))
returns int as $$
declare
 bus_count int;
begin
 select count(distinct b.bus_no) into bus_count
 from bus b
 join route r on r.route_no=b.bus_no
 where r.source=r.source;
 return bus_count;
end;
$$ language plpgsql;

select * from count_buses_by_source('Station c');

create or replace function
get_drivers_age_above_50(depot_name varchar)
returns table(
 driver_no int,
 driver_name char(20),
 license_no int,
 address char(20),
 d_age int,
 salary float
)as $$
begin 
 return query
 select d.driver_no,d.driver_name,d.license_no,d.address,d.d_age,d.salary
 from driver d
 join busdriver bd on d.driver_no=bd.driver_no
 join bus b on bd.bus_no=b.bus_no
 where d.d_age>50
 and b.depot_name=b.depot_name;
end;
$$ language plpgsql;

select * from get_drivers_age_above_50('north depot');

create table branch(
 br_id serial primary key,
 br_name varchar(30),
 br_city varchar(10)
);

\d branch

create table customer(
 cno serial primary key,
 c_name varchar(20),
 caddr varchar(35),
 city varchar(20)
);

\d customer

create table loan_application(
 lno serial primary key,
 l_amt_required numeric,
 l_amt_approved numeric,
 l_date date,
 check(l_amt_required>0)
);

\d loan_application

create table ternary(
 br_id integer references branch(br_id),
 cno integer references customer(cno),
 lno integer references loan_application(lno),
 primary key(br_id,cno,lno)
);

\d ternary

insert into branch(br_name,br_city) values
('branch a','pune'),
('branch b','mumbai');

select * from branch;

insert into customer(cno,c_name,caddr,city) values
('101','john doe','address 1','pune'),
('102','jane smith','address 2','mumbai');

select * from customer;

insert into loan_application(l_amt_required,l_amt_approved,l_date) values
(10000,8000,'2023-05-15'),
(5000,5000,'2024-07-01');

select * from loan_application;

insert into ternary(br_id,cno,lno) values
(1,101,1),
(2,102,2),
(1,102,2);

select * from ternary;

create function 
customerswithhigherrequiredamount(br_name varchar(30))
returns table(c_name
 varchar(20),caddr varchar(35),
 city varchar(20))
language sql 
as $$
 select c.c_name,c.caddr,c.city
 from customer c
 join ternary t on c.cno=t.cno
 join loan_application l on t.lno=l.lno
 join branch b on t.br_id=b.br_id
 where b.br_name=br_name
 and l.l_amt_required>l.l_amt_approved;
 $$;
 
 select * from customerswithhigherrequiredamount('branch a');
 
create function
customerswithloansafterdate(br_name varchar(30),loan_date date)
returns table(c_name varchar(20),
lno integer,l_amt_approved numeric)
language sql
as $$
 select c.c_name,l.lno,l.l_amt_approved
 from customer c
 join ternary t on c.cno=t.cno
 join loan_application l on t.lno=l.lno
 join branch b on t.br_id=b.br_id
 where b.br_name=br_name
 and l.l_date>=loan_date;
 $$;
 
 select * from customerswithloansafterdate('branch b','2023-06-01');
 
 create function
 totalloanamountapproved(loan_date date)
 returns numeric
 language sql
 as $$
 select sum(l.l_amt_approved)
 from loan_application l
 join ternary t on l.lno=t.lno
 where l.l_date>loan_date;
 $$;
 
 select * from totalloanamountapproved('2023-05-31');
 
 create function 
 customerswithmultiplebranches()
 returns table(c_name varchar(20))
 language sql
 as $$
 select c.c_name
 from customer c
 join ternary t on c.cno=t.cno
 group by c.cno
 having count(distinct t.br_id)>1;
 $$;
 
 select * from customerswithmultiplebranches();
 
 
 
create table project(
 pno int primary key,
 pname char(30) not null,
 ptype char(20),
 duration int check(duration>0)
);
 
\d project

create table employee(
 eno int primary key,
 ename char(20),
 qualification char(15),
 joining_date date
);

\d employee

create table projectemployee(
 pno int,
 eno int,
 start_date date,
 no_of_hours_worked int,
 primary key(pno,eno),
 foreign key(pno) references project(pno),
 foreign key(eno) references employee(eno)
);

\d employee

insert into project values
(1,'project alpha','software development',12),
(2,'project beta','hardware development',8),
(3,'project gamma','research',15);

select * from project;

insert into employee values
(101,'john doe','engineer','2005-05-10'),
(102,'jane smith','scientist','2008-02-20'),
(103,'milke johnson','programmer','2012-09-01');

select * from employee;

insert into projectemployee values
(1,101,'2006-01-01',100),
(1,102,'2006-02-15',80),
(2,101,'2007-03-20',120),
(2,103,'2007-04-05',90),
(3,102,'2009-05-12',150);

select * from projectemployee;

create or replace function
countemployeebeforedate(target_date date)
returns integer as $$
declare 
 emp_count integer;
begin
 select count(*)
 into emp_count
 from employee
 where joining_date<target_date;
 return emp_count;
end;
$$ language plpgsql;

select * from countemployeebeforedate('2007-01-01');

create or replace function
countprojectsbyemployee(eno int)
returns integer as $$
declare
 project_count integer;
begin
 select count(distinct pno)
 into project_count
 from projectemployee as pe
 where pe.eno=$1;
 return project_count;
end;
$$ language plpgsql;

select countprojectsbyemployee(103);

--ASSIGNMENT-2 SET-A 
--QUESTION-1


--ASSIGNMENT-1
--SET-A
--QUESTION-1

create table student(
 s_no serial primary key,
 s_name varchar(20),
 s_class varchar(10) not null,
 s_addr varchar(30) 
);

\d student

create table teacher(
 t_no serial primary key,
 t_name varchar(20) not null,
 qualification varchar(15),
 experience int
);

\d teacher

create table studentteacher(
 s_no int,
 t_no int,
 subject varchar(20),
 primary key(s_no,t_no,subject),
 foreign key(s_no) references student(s_no),
 foreign key(t_no) references teacher(t_no)
);

\d studentteacher

insert into student(s_name,s_class,s_addr) values
('tina','FY','sangvi'),
('aman','SY','pune'),
('shruti','FY','pimpri');

select * from student;

insert into teacher(t_name,qualification,experience) values
('siddhi','MCA',10),
('harshit','PHD',5),
('sonali','MCA',15);

select * from teacher;

insert into studentteacher(s_no,t_no,subject) values
(1,1,'DBMS'),
(2,2,'STATISTICS'),
(3,3,'DBMS'),
(2,1,'DSA');

select * from studentteacher;

select distinct t_name 
from teacher t
join studentteacher st on t.t_no=st.t_no
join student s on st.s_no=s.s_no
where s.s_name='aman';

select distinct s_name 
from student s
join studentteacher st on s.s_no=st.s_no
join teacher t on st.t_no=t.t_no
where t.t_name='siddhi';

select *
from teacher
where t_name like 's%';

select distinct t_name
from teacher t
join studentteacher st on t.t_no=st.t_no
where st.subject='STATISTICS';

select count(*)
from teacher 
where qualification='MCA';



create table area(
 aname varchar(20)  primary key,
 area_type varchar(5) 
 check(area_type in('urban','rural'))
);

\d  area

create table person(
 pno serial primary key,
 pname varchar(20),
 birthdate date,
 income money,
 aname varchar(20),
 foreign key(aname) references area(aname)
);

\d person

insert into area(aname,area_type) values
('pune','urban'),
('mumbai','urban'),
('nashik','rural');

select * from area;


insert into person(pname,birthdate,income,aname) values
('tina','1980-01-15','50000','pune'),
('shruti','1992-08-05','60000','mumbai'),
('gauri','1985-05-20','70000','nashik'),
('sanjana','1975-04-10','55000','pune');

select * from person;

select pname
from person
where aname='pune';

select *
from person
where pname like 'g%';

select aname,count(*)
from person
where income>'55000'
group by aname;

select pname 
from person
where income between '55000' and '70000';

select pname
from person
where extract(month from birthdate)=8;


create table movie(
 m_name varchar(25) primary key,
 release_year int,
 budget money
);

\d movie

create table actor(
 a_name varchar(20) primary key,
 role char(20) not null,
 charge money,
 a_address varchar(20)
);

\d actor

create table producer(
 producer_id serial primary key,
 p_name char(30) not null,
 p_address varchar(20)
);

\d producer

create table movieactor(
 m_name varchar(25),
 a_name varchar(20),
 primary key(m_name,a_name),
 foreign key(m_name) references movie(m_name),
 foreign key(a_name) references actor(a_name)
);

\d movieactor

create table movieproducer(
 m_name varchar(25),
 producer_id serial,
 primary key(m_name,producer_id),
 foreign key(m_name) references movie(m_name),
 foreign key(producer_id) references producer(producer_id)
);

\d movieproducer

insert into actor(a_name,role,charge,a_address) values
('salman khan','lead','5000000','galaxy apt'),
('sharukh khan','support','3500000','bandra road'),
('ranbir kapoor','lead','6000000','mumbai');

select * from actor;

insert into movie(m_name,release_year,budget) values
('Jawan','2016','5000000'),
('tiger','2017','7500000'),
('tamasha','2018','6800000');

select * from movie;

insert into producer(p_name,p_address) values
('sanjay leela bhansali','address1'),
('karan johar','address2'),
('imtiaz ali','address3');

select * from producer;

insert into movieactor(m_name,a_name) values
('Jawan','salman khan'),
('tiger','salman khan'),
('tiger','sharukh khan'),
('tamasha','ranbir kapoor');

select * from movieactor;

insert into movieproducer(m_name,producer_id) values
('Jawan',1),
('tiger',2),
('tamasha',3);

select a.a_name,m.m_name
from actor a
join movieactor ma on a.a_name=ma.a_name
join movie m on ma.m_name=m.m_name;

select m.m_name
from movie m
join movieproducer mp on m.m_name=mp.m_name
join producer p on mp.producer_id=p.producer_id
where p.p_name='karan johar';

select * from movie
where budget=(select min(budget) from movie);

select m_name
from movie
where release_year>'2015';

select count(*) as total_count,sum(budget) as total_budget
from movie
where release_year='2017';


create table branch1(
 bid serial primary key,
 brname char(20),
 brcity char(10)
);

\d branch

create table customer1(
 cno serial primary key,
 cname char(20),
 caddr char(35),
 city char(20)
);

\d customer1

create table loan_application1
(
 lno serial primary key,
 l_amt_require money,
 l_amt_approve money,
 l_date date
);

\d loan_application1

create table ternary1(
 bid integer,
 cno integer,
 lno integer,
 primary key (bid,cno,lno),
 foreign key(bid) references branch1(bid),
 foreign key(cno) references customer1(cno),
 foreign key(lno) references loan_application1(lno)
);

\d ternary1

insert into branch1(brname,brcity) values
('HDFC','pune'),
('SBI','mumbai');

select * from branch1;

insert into customer1(cname,caddr,city) values
('Tina','sangvi','pune'),
('Shruti','sangvii','mumbai'),
('sanjana','pimpri','pune'),
('Gauri','jagtap dairy','mumbai');

select * from customer1;

insert into loan_application1(l_amt_require,l_amt_approve,l_date) values
('5000000','4000000','2024-07-15'),
('3000000','3000000','2024-06-10'),
('2000000','1500000','2024-07-20'),
('1000000','1000000','2024-08-05');

select * from loan_application1;

insert into ternary1(bid,cno,lno) values
(1,1,1),
(2,2,2),
(1,3,3),
(2,4,4);

select * from ternary1;

select sum(l_amt_approve) as total_approved
from loan_application1 l
join ternary1 t on l.lno=t.lno
join branch1 b on t.bid=b.bid
where b.brname='pune';

select distinct c.cname
from customer1 c
join ternary1 t on c.cno=t.cno
join branch1 b on t.bid=b.bid
where b.brname='SBI';

select distinct c.cname
from customer1 c
join ternary1 t on c.cno=t.cno
join branch1 b on t.bid=b.bid
where c.city=b.brcity;

select distinct c.cname
from customer1 c
join ternary1 t on c.cno=t.cno
join loan_application1 l on t.lno=l.lno
where l.l_amt_approve<l.l_amt_require;

select distinct c.cname,b.brname
from customer1 c
join ternary1 t on c.cno=t.cno
join branch1 b on t.bid=b.bid
join loan_application1 l on t.lno=l.lno
where to_char(l_date,'YYYY-MM')='2024-08';


create table cities(
 city char(20) primary key,
 state char(20)
);

\d cities

create table warehouses(
 wid serial primary key,
 wname char(30),
 location char(20),
 city char(20),
 foreign key(city) references cities(city)
);

\d warehouses

create table stores(
 sid serial primary key,
 store_name char(20),
 location_city char(20)
);

\d stores

create table items(
 itemno serial primary key,
 description text,
 weight decimal(5,2),
 cost decimal(5,2)
);

\d items

create table customer2(
 cno serial primary key,
 cname char(50),
 addr varchar(50),
 cu_city char(20)
);

\d customer2

create table orders(
 ono serial primary key,
 odate date,
 cno int,
 foreign key(cno) references customer2(cno)
);

\d orders

create table ordersitems(
 ono integer,
 itemno integer,
 ordered_quantity integer,
 primary key(ono,itemno),
 foreign key(ono) references orders(ono),
 foreign key(itemno) references items(itemno)
);

\d ordersitems

create table storesitems(
 sid integer,
 itemno integer,
 quantity integer,
 primary key(sid,itemno),
 foreign key(sid) references stores(sid),
 foreign key(itemno) references items(itemno)
);

\d storesitems

insert into cities(city,state) values
('pune','gujarat'),
('mumbai','maharahtra'),
('jaipur','rajasthan');

select * from cities;

insert into warehouses(wname,location,city) values
('warehouse1','location1','pune'),
('warehouse2','location2','mumbai'),
('warehouse3','location3','jaipur');

select * from warehouses;

insert into stores(store_name,location_city) values
('store1','pune'),
('store2','mumbai');

select * from stores;

insert into items(description,weight,cost) values
('item1',10.00,100.00),
('item2',5.00,50.00),
('item3',15.00,150.00);

select * from items;

insert into customer2(cname,addr,cu_city) values
('tina','address1','pune'),
('shruti','address2','mumbai');

select * from customer2;

insert into orders(odate,cno) values
('2024-07-15',1),
('2024-08-10',2);

select * from orders;

insert into ordersitems(ono,itemno,ordered_quantity) values
(1,1,10),
(1,2,5),
(2,3,2);

select * from ordersitems;

insert into storesitems(sid,itemno,quantity) values
(1,1,100),
(1,2,50),
(2,3,30);

select * from storesitems;

select wname 
from warehouses
where city='pune';

select * from items
where weight=(select min(weight) from items);

select c.cname,o.ono,o.odate
from customer2 c
join orders o on c.cno=o.cno;

with customerid as(
 select cno
 from customer2
 where cname='shruti'
)
select i.* from items i
join ordersitems oi on i.itemno=oi.itemno
join orders o on oi.ono=o.ono
where o.cno in(select cno from customer2);

with ordercounts as(
 select itemno,sum(ordered_quantity) as total_ordered
 from ordersitems
 group by itemno
)
select i.* from items i
join ordercounts oc on i.itemno=oc.itemno
where total_ordered=(select min(total_ordered) from ordercounts);


create table project1(
 pno integer primary key,
 pname char(30) not null,
 ptype char(20),
 duration integer 
 check(duration>0)
);

\d project1

insert into project1(pno,pname,ptype,duration) values
(1,'erp','software',12),
(2,'crm','software',6),
(3,'AI development','research',8);

select * from project1;

create table employee1(
 eno integer primary key,
 ename char(20),
 qualification char(15),
 joining_date date
);

\d employee1

insert into employee1(eno,ename,qualification,joining_date) values
(1,'alice','mca','2023-01-15'),
(2,'bob','btech','2022-01-10'),
(3,'charlie','mca','2021-07-22');

select * from employee1;

create table project1employee1(
 pno integer,
 eno integer,
 start_date date,
 no_of_hours_worked integer,
 primary key(pno,eno),
 foreign key(pno) references project1(pno),
 foreign key(eno) references employee1(eno)
);

\d project1employee1

insert into project1employee1(pno,eno,start_date,no_of_hours_worked) values 
(1,1,'2023-01-20',350),
(2,2,'2022-03-15',200),
(3,3,'2021-08-01',400),
(1,3,'2023-02-10',150);

select * from project1employee1;

create view EmpWorkOnERP as
select e.eno,e.ename,e.qualification,e.joining_date
from employee1 e
join project1employee1 pe on e.eno=pe.eno
join project1 p on pe.pno=p.pno
where pname='erp';

select * from EmpWorkOnERP;

create view ProjectDetails as
select p.pno,p.pname,p.ptype,p.duration,pe.start_date
from project1 p
join project1employee1 pe on p.pno=pe.pno
where p.duration>6
order by pe.start_date;

select * from ProjectDetails;

create view MCAEmp as
select e.eno,e.ename,e.qualification,e.joining_date
from employee1 e
where e.qualification='mca';

select * from MCAEmp;

create view EmpWorkedMoreThan300Hours as 
select e.ename,p.pname
from employee1 e
join project1employee1 pe on e.eno=pe.eno
join project1 p on pe.pno=p.pno
where pe.no_of_hours_worked>300;

select * from EmpWorkedMoreThan300Hours;

create table area1(
 aname varchar(20)  primary key,
 area_type varchar(5) 
 check(area_type in('urban','rural'))
);

\d  area1

create table person1(
 pno serial primary key,
 pname varchar(20),
 birthdate date,
 income money,
 aname varchar(20),
 foreign key(aname) references area1(aname)
);

\d person1

insert into area1(aname,area_type) values
('hadapsar','urban'),
('wagholi','rural'),
('kothrud','urban');

select * from area1;


insert into person1(pname,birthdate,income,aname) values
('tina','1980-01-15','50000','hadapsar'),
('shruti','1992-08-05','9000','wagholi'),
('gauri','1985-05-20','7000','kothrud'),
('sanjana','1975-04-10','55000','hadapsar');

select * from person1;

create view PersonName as
select pname 
from person1
where pname like 't%' and aname='hadapsar';

select * from PersonName;

create view PersonsInRuralArea as  
select count(*) as person_count
from person1 p
join area a on p.aname=a.aname
where a.area_type='rural' and p.income>'7000';

select * from PersonsInRuralArea;

create view AreaCount as  
select pname,count(*) as area_count
from person1 p
join area1 a on p.aname=a.aname
where p.birthdate>'1992-08-05'
group by pname;

select * from AreaCount;

create view AreaNamePerson as
select a.aname,p.pname,p.income
from area1 a
join person1 p on a.aname=p.aname
where (p.aname,p.income) in(
 select p.aname,max(p.income)
 from person1 p
 group by p.aname
)
order by a.aname;

select * from AreaNamePerson;


create table bus1(
 bus_no int primary key,
 b_capacity int not null,
 depot_name varchar(20)
);

\d bus1

create table route1(
 route_no int primary key,
 source char(20),
 destination char(20),
 no_of_stations int
);

\d route1

create table driver1(
 driver_no int primary key,
 driver_name char(20),
 license_no int unique,
 address char(20),
 d_age int,
 salary float
);

\d driver1

create table bus1driver1(
 bus_no int,
 driver_no int,
 date_of_duty_alloted date,
 shift int check(shift in(1,2)),
 primary key(bus_no,driver_no,date_of_duty_alloted),
 foreign key(bus_no) references bus1(bus_no),
 foreign key(driver_no) references driver1(driver_no)
);

\d bus1driver1


insert into bus1(bus_no,b_capacity,depot_name) values
(101,50,'north depot'),
(102,40,'south depot'),
(103,55,'north depot'),
(104,45,'east depot');

select * from bus1;

insert into route1(route_no,source,destination,no_of_stations) values
(1,'Station a','Station b',10),
(2,'Station c','Station d',15),
(3,'Station a','Station d',20);

select * from route1;

insert into driver1(driver_no,driver_name,license_no,address,d_age,salary) values
(1,'john doe','12345','123 main st',55,50000),
(2,'jane smith','67890','456 elm st',40,60000),
(3,'emily davis','13579','789 oak st',60,70000),
(4,'michael brown','24680','321 pine st',30,55000);

select * from driver1;

insert into bus1driver1(bus_no,driver_no,date_of_duty_alloted,shift) values
(101,1,'2023-08-15',1),
(101,2,'2024-01-10',2),
(102,3,'2023-06-01',1),
(103,4,'2024-02-25',2),
(104,1,'2024-03-30',1);

select * from bus1driver1;