Vendors

create table Vendors
    (Ven_id INTEGER PRIMARY KEY,
    Ven_name VARCHAR(20) NOT NULL,
    Contact NUMBER NOT NULL,
    Street VARCHAR(20),
    City VARCHAR(20),
    Salary NUMBER,
    Dept_id VARCHAR(10));
alter table Vendors add Dob DATE;
alter table Vendors add Mail_id VARCHAR(20);
insert into Vendors values(1,'Ratna',9044670000,'Rajiv Chowk','Delhi',30000,'A01','1987-06-11','ratna@gmail.com');
insert into Vendors values(2,'Anjali',9839780000,'Vijay Nagar','Kanpur',21000,'A01','1990-12-01','anjali@gmail.com');
insert into Vendors values(3,'Rahul',9532890000,'Model Town','Delhi',35000,'B01','1978-04-08','rahul@gmail.com');
insert into Vendors values(4,'Ankit',7522900000,'Rajajipuram','Lucknow',24500,'C03','1977-10-27','ankit@gmail.com');
insert into Vendors values(5,'Sumit',7271000000,'Ramadevi','Kanpur',26000,'B02','1985-02-18','sumit@gmail.com');
insert into Vendors values(6,'Kapil',7890120000,'Mall Road','Kanpur',27000,'C03','1974-10-30','kapil@gmail.com');
insert into Vendors values(7,'Abhinav',9984256311,null,'Lucknow',25000,'C03','1985-04-14','abhinav@gmail.com');
insert into Vendors values(8,'Ankur',9865237411,'Akbar Road','Delhi',20700,'B03','1977-11-05','ankur@gmail.com');

select Dept_id,count(*) from Vendors group by Dept_id;
select Dept_id,max(Salary),min(Salary) from Vendors group by Dept_id;
select Dept_id,avg(Salary) from Vendors group by Dept_id having Salary>=25000;
select Dept_id,count(*) from Vendors group by Dept_id having salary>=21000 and Street is not null;
select Dept_id,max(Salary) from Vendors group by Dept_id having count(Dept_id)>=2;
select Dept_id,avg(Salary) from Vendors group by Dept_id having count(Dept_id)>=2;
select Dept_id,sum(Salary) from Vendors group by Dept_id having Dept_id='A01';
select City,count(*) from Vendors group by City;
select Dept_id,count(Dept_id) from Vendors where Salary>26000 group by Dept_id;
select City,sum(Salary) from Vendors group by City;
select City,count(*) from Vendors group by City order by count(City) desc;
select Dept_id,max(Salary) from Vendors group by Dept_id having Dept_id between 'B03' and 'B07';
select Dept_id,Salary from Vendors group by Dept_id having Dept_id between 'B01' and 'B07' and max(Salary)>1000;