Name: Ankush Halder
Student ID:181001102061
Subject: DBMS lab assignment
BCA2A
ASSIGNMENT 1
create table student(Roll int not null auto_increment,Name varchar(20),city varchar(20),pin
char(7),primary key(Roll));
insert into student(Name,city,pin) values
('Monika','Naihati','700101'),
('Sudipta','Asansol','700231'),
('Surbhi','Mednipur','7023411'),
('Avi','LakeTown','700201'),
('Shrobona','Baguiati','7003411'),
('Snigdha','Baguiati','7003410'),
('Rohan','Howrah','7002410'),
('Rounak','Durgapur','7001410'),
('Kritika','Saltlake','7001410'),
('Amit','Nabadwip','700501');
select * from student;
select * from student where city='DURGAPUR';
select * from student order by name asc;
alter table student modify Name varchar(25);
update student set city='KALYANI' where name='MONIKA';
select * from student where name='MONIKA';
delete from student where name='Amit';
select * from student where name='Amit';
select * from student where city='Asansol';
drop table student;
describe student;
ASSIGNMENT 2
create table st_marks(Roll bigint not null,Stream varchar(40),Marks int);
alter table st_marks add primary key(Roll);
describe st_marks;
alter table st_marks modify Stream char(40);
describe st_marks;
insert into st_marks(Roll,Stream,Marks)Values
(181001102021,'BCA',480),
(181001102025,'BCA',465),
(18100112286,D_PHARM,477),
(181001112067,'BTECH-CSE',480),
(181001122087,'BTECH-M',482),
(181001222087,'B-ARCH',460),
(181001522087,'B-PHARM',469),
(181001522047,'B-PHARM',467),
(181001524040,'BTECH-CSE',490);
create table student(Roll bigint not null ,Name varchar(20),city varchar(20),pin char(7),primary
key(Roll));
describe student;
insert into student(Roll,Name,city,pin) values
(181001102021,'Monika','Naihati','700101'),
(181001102025,'Sudipta','Asansol','700231'),
(181001102067,'Surbhi','Mednipur','7023411'),
(181001122087,'Avi','LakeTown','700201'),
(181001524040,'Shrobona','Kolkata','700103'),
(181001232087,'Snigdha','Baguiati','7003410'),
(181001522047,'Rohan','Howrah','7002410'),
(181001722747,'Rounak','Durgapur','7001410'),
(182001522047,'Kritika','Saltlake','7001410'),
(18100112286,'Amit','Nabadwip','700501');
select name,marks,city from student,st_marks where st_marks.Roll=18100112286 and
student.Roll=18100112286;
select name from student where Roll in (select Roll from st_marks where marks >480);
ASSIGNMENT 3
create table Employee(ename varchar(20) NOT NULL,city varchar(20));
create table Works(cname varchar(20)NOT NULL,ename varchar(20)NOT
NULL, salary int);
create table Managers(ename varchar(20) NOT NULL,bossname
varchar(20) NOT NULL);
create table Company(cname varchar(20) not null,city varchar(20) );
insert into Employee(ename,city)values
('SHROBONA','KOLKATA'),
('SUDIPTA','HYDERABAD'),
('AREET','MUMBAI'),
('JOHN','GOA'),
('RAHUL','PUNE'),
('RUMA','CHENNAI'),
('SUBRATA','KOLKATA'),
('SUBHAM','DELHI'),
('RUDRA','JAIPUR'),
('KIRTI','PUNE'),
('SRIRUPA','KOLKATA'),
('RAVIDRA','HYDERABAD'),
('AVINAVA','MUMBAI'),
('SANYA','HYDERABAD'),
('RUMI','PUNE');
insert into Works(cname,ename,salary)values
('TCS','KIRTI',25000),
('ACCENTURE','RUDRA',28000),
('IBM','SHROBONA',32000),
('ACCENTURE','SUBRATA',40000),
('CAPGEMINI','JOHN',15000),
('GLU_MOBILE','RUMA',18000),
('TCS','RAHUL',21000),
('L&T','SUDIPTA',29000),
('CTS','AREET',35000),
('WIPRO','SUBHAM',20000),
('ACCENTURE','SRIRUPA',25000),
('TCS','RAVINDRA',32000),
('IBM','AVINAVA',42000),
('L&T','SANYA',22000),
('WIPRO','RUMI',26000);
insert into Managers(ename,bossname)values
('RAHUL','KIRTI'),
('RUDRA','SUBRATA'),
('AVINAVA','SHROBONA');
insert into Company(cname,city)values
('TCS','MUMBAI'),
('ACCENTURE','DELHI'),
('CAPGEMINI','HYDERABAD'),
('L&t','HYDERABAD'),
('WIPRO','KOLKATA'),
('CTS','BANGALORE'),
('GLU_MOBILE','HYDERABAD'),
('IBM','MUMBAI');
select ename,cname from Works where cname='TCS';
select ename,city from Employee where ename in (select ename from Works where cname='IBM');
select ename,city from Employee where ename in (select ename from Works where cname='IBM' and
salary>20000);
select e.ename from Company c,(select w.ename,w.cname from Works w join
Employee e on e.ename=w.ename) this ,Employee e where
e.ename=this.ename and c.cname=this.cname and e.city=c.city;
select e.ename from Employee e,(select e.city,m.bossname,m.ename from
Managers m,Employee e where e.ename=m.bossname)this where
e.ename=this.ename and e.city=this.city;
select ename from Works where cname!='TCS';
select ename,salary from Works where cname='TCS' order by(salary) DESC
limit 1;
select cname,avg(salary) as salary from Works group by cname;
select cname,count(*) from Works group by cname limit 1;
select cname,min(salary) from Works;
select * from works where cname='TCS';
ASSIGNMENT 4
create table DEPT(Dno int not null auto_increment, Dname varchar(64),primary key(Dno));
create table EMP(Eno int not null auto_increment,Ename varchar(64),City char(3),Salary int,Dno
int,join_date date,primary key(Eno),foreign key (Dno) references DEPT(Dno));
insert into DEPT(Dname)values
('TCS'),
('INFOTECH'),
('ACCENTURE'),
('EA-SPORTS'),
('GUL'),
('CAPGEMINI'),
('WIPRO'),
('CTS');
select * from DEPT;
insert into EMP(Ename,City,Salary,Dno,join_date)values
('SUDIPTA','KOL',15000,1,'2019-05-17'),
('ARINDAM','BIH',18000,2,'2019-07-27'),
('SHROBONA','MUM',12000,3,'2018-06-12'),
('KIRTI','HYD',22000,1,'2020-09-19'),
('RAMA','CHE',28000,4,'2016-01-01'),
('ROBIN','BEN',26000,5,'2018-12-11'),
('SIDDHI','PAT',12000,2,'2019-11-10'),
('SRESHTHA','KOL',14000,6,'2019-03-17'),
('ARITRA','BIH',14000,8,'2020-01-27'),
('SAMPARNA','MUM',12000,7,'2020-04-12');
Select * from EMP;
select Ename from EMP order by Ename asc;
select avg(Salary) AS AVG_SALARY_EMP from EMP ;
select Ename AS 'JOIN IN JULY' from EMP where month(join_date)=6;
select Ename,Dname from EMP,DEPT WHERE EMP.City='CHE' OR EMP.City='MUM' GROUP BY
Ename;
select * from EMP where salary between 7000 and 18000;
select * from Emp where Dno=2;
select dname,count(dname) from DEPT,EMP where EMP.dno=DEPT.dno group by dept.dno;
select max(salary)AS MAX,min(salary) AS MIN ,(max(salary)-min(salary))AS 'DIFF SALARY' from
EMP;
ASSIGNMENT 5
CREATE TABLE IF NOT EXISTS `Student` (
`Roll` int(6) NOT NULL AUTO_INCREMENT,
`Subject` varchar(20) NOT NULL,
`Marks` int(3) NOT NULL,
PRIMARY KEY (`Roll`)
) DEFAULT CHARSET=utf8;
INSERT INTO `Student` (`Roll`, `Subject`,`Marks`) VALUES
(1, 'English',89),
(2, 'Math',98),
(3, 'Bengali',78),
(4, 'Geography',86),
(5, 'English',78);
select count(*) from Student;
select avg(Marks) from Student;
select max(Marks) from Student;
select Roll from Student where Marks=(select max(Marks) from Student);
select min(Marks) from Student;
select Roll from Student where Marks=(select min(Marks) from Student);
select concat((100/100)*(Marks),'%') from Student;
ASSIGNMENT 6
create table SALESPEOPLE(snum int not null auto_increment, sname varchar(64) not null ,city char(3)
not null,commission int,primary key(snum));
create table CUSTOMERS(cnum int not null auto_increment,cname varchar(64),city char(3) not null,
rating int not null, snum int,primary key(cnum),foreign key(snum) references SALESPEOPLE(snum));
create table ORDERS(onum int not null auto_increment,amt int,odate date,cnum int,snum int,foreign
key(cnum) references CUSTOMERS(cnum),primary key (onum),foreign key(snum) references
SALESPEOPLE(snum));
insert into SALESPEOPLE(sname,city,commission)values
('SUBHAM','HYD',1300),
('SNIGDHA','MUM',1200),
('BIPASHA','KOL',1600),
('MITHI','KOL',1700),
('ANIRUDH','CHE',1800),
('KAUSTAV','LEH',1550),
('RUBY','AGR',1680);
insert into SALESPEOPLE(sname,city,commission)values
('SHROBONA','KOL',1000),
('SIDDHI','MUM',1100);
insert into CUSTOMERS(cname,city,rating,snum)values puffy
('SUDIPTA','KOL',7,102),
('ARINDAM','BIH',8.5,101),
('SHROBONA','MUM',6,102),
('KIRTI','HYD',9,100),
('RAMA','CHE',7,104),
('ROBIN','BEN',8,107),
('SIDDHI','PAT',9,106),
('SRESHTHA','KOL',6.5,103),
('ARITRA','BIH',5,105),
('SAMPARNA','MUM',9,103),
('SUSHANT','HYD',7.5,105),
('RIDDHI','MUM',8,102),
('SNEHA','KOL',9,107),
('MAYAVATI','BIH',5.5,101),
('ALLYSA','CHE',7,103);
insert into ORDERS(amt,odate,snum,cnum)values
(12000,'2020-07-19',103,11),
(22000,'2019-12-19',104,5),
(25000,'2019-12-19',103,5),
(18000,'2019-11-08',107,3),
(32000,'2020-03-11',102,9),
(5000,'2019-10-07',106,15),
(2300,'2018-03-10',104,14),
(29000,'2020-02-08',107,7),
(20000,'2020-05-18',101,6),
(22000,'2019-12-19',105,10),
(1900,'2018-12-19',105,4),
(28000,'2020-01-01',106,12);
select cname,sname from CUSTOMERS JOIN SALESPEOPLE ON
CUSTOMERS.cname=SALESPEOPLE.sname;
select customers.city,cname,sname from CUSTOMERS JOIN SALESPEOPLE ON
CUSTOMERS.city=SALESPEOPLE.CITY;
select cname,sname from CUSTOMERS JOIN SALESPEOPLE ON
CUSTOMERS.city=SALESPEOPLE.city WHERE (CUSTOMERS.city in('MUM','KOL') and
SALESPEOPLE.city in('MUM','KOL'));
select s.snum,s.sname ,onum,amt,odate from orders o join salespeople s on s.snum=o.snum order by
s.sname;
select onum,cname,sname from CUSTOMERS c,SALESPEOPLE s,ORDERS o where o.cnum=c.cnum
and s.snum=o.snum order by s.sname;
select cname,rating from CUSTOMERS ORDER BY rating DESC;
select cname from CUSTOMERS c where c.city in (select city from ORDERS o,SALESPEOPLE s where
s.sname='SNIGDHA' and c.snum=o.snum);
select c.rating,s.sname from SALESPEOPLE s join CUSTOMERS c on c.snum=s.snum where
c.rating>=8;
select c.cnum,c.cname,o.onum,o.amt,o.odate from Orders o join CUSTOMERS c on c.cnum=o.cnum
where c.city='MUM';
select sname,commission from salespeople where snum in (select snum from orders where amt>20000);
ASSIGNMENT 7
use testing;
CREATE TABLE IF NOT EXISTS Supplier
Sno int NOT NULL AUTO_INCREMENT,
Sname varchar(20) NOT NULL,
Status varchar(20) NOT NULL,
City varchar(10) NOT NULL,
PRIMARY KEY (Sno)
);
CREATE TABLE IF NOT EXISTS Parts
Pno int NOT NULL AUTO_INCREMENT,
Pname varchar(20) NOT NULL,
Color varchar(20) NOT NULL,
Weight float NOT NULL,
City varchar(20) NOT NULL,
PRIMARY KEY (Pno)
)auto_increment=100;
CREATE TABLE IF NOT EXISTS Projects (
Jno int NOT NULL AUTO_INCREMENT,
Jname varchar(20) NOT NULL,
City varchar(20) NOT NULL,
PRIMARY KEY (Jno)
)auto_increment=1000;
CREATE TABLE IF NOT EXISTS SPJ (
Sno int,
Pno int,
Jno int,
Qty int NOT NULL,
FOREIGN KEY (Sno) REFERENCES Supplier(Sno),
FOREIGN KEY (Pno) REFERENCES Parts(Pno),
FOREIGN KEY (Jno) REFERENCES Projects(Jno)
);
INSERT INTO SUPPLIER (SNAME,STATUS,CITY) VALUES
('KRUNAL',10,'LONDON'),
('RAMESH',5,'INDIA'),
('VIVEK',4,'LONDON'),
('VIMAL',3,'JAPAN'),
('HEMAL',10,'KORIA'),
('RAJU',2,'CHINA'),
('VINU',3,'CHINA');
INSERT INTO PARTS(PNAME,COLOR,WEIGHT,CITY) VALUES
( 'MOUSE','RED',45,'NEW YORK'),
( 'MONITOR','WHITE',4,'LONDON'),
( 'MOTHER_BOARD','GREY',24,'CHINA'),
( 'CPU','CYAN',16,'CHINA'),
( 'ROUTER','BLACK',58,'NEW YORK'),
( 'CABINET','GREY',45,'NEW YORK'),
( 'VIDEO_CARD','WHITE',4,'LONDON'),
( 'ADAPTER','GREY',24,'CHINA'),
( 'MOUSE_PAD','RED',16,'CHINA'),
( 'UPS','BLACK',58,'NEW YORK');
INSERT INTO PROJECTS (JNAME,CITY) VALUES
('PRJ1','LONDON'),
('PRJ2','CHINA'),
('PRJ3','CHINA'),
('PRJ4','INDIA'),
('PRJ5','LONDON'),
('PRJ6','CHINA'),
('PRJ7','CHINA'),
('PRJ8','INDIA'),
('PRJ9','LONDON'),
('PRJ10','CHINA'),
('PRJ11','CHINA'),
('PRJ12','INDIA');
INSERT INTO SPJ (SNO,PNO,JNO,QTY) VALUES
(6,102,1005,300),
(7,101,1011,800),
(6,109,1001,115),
(4,106,1004,130),
(1,106,1005,200),
(1,109,1011,300),
(3,106,1002,800),
(2,104,1003,115),
(4,102,1001,130),
(1,103,1004,75),
(7,102,1001,300),
(5,105,1002,800),
(7,104,1003,115),
(6,102,1011,130),
(3,103,1010,75);
select * from Supplier;
select * from Parts;
select * from Projects;
select * from SPJ;
select sp.sno,sp.pno,p.jname from Projects p JOIN SPJ sp on sp.jno=p.jno where p.jname='PRJ2';
SELECT SUPPLIER.SNO,PARTS.PNO,PROJECTS.JNO
FROM SUPPLIER,PARTS,PROJECTS,SPJ
WHERE SUPPLIER.SNO = SPJ.SNO AND PARTS.PNO = SPJ.PNO AND
PROJECTS.JNO = SPJ.JNO AND SUPPLIER.CITY = PARTS.CITY AND
SUPPLIER.CITY = PROJECTS.CITY;
SELECT SUPPLIER.SNO,PARTS.PNO,PROJECTS.JNO
FROM SUPPLIER,PARTS,PROJECTS,SPJ
WHERE SUPPLIER.SNO = SPJ.SNO AND PARTS.PNO = SPJ.PNO AND
PROJECTS.JNO = SPJ.JNO AND SUPPLIER.CITY <> PARTS.CITY AND
SUPPLIER.CITY <> PROJECTS.CITY;
select distinct pno AS 'PART NUM' from Spj where sno in (select distinct s.sno from Supplier s,Parts
p,SPJ sp where s.sno=sp.sno and s.city='LONDON');
ASSIGNMENT 8
create table SALESPEOPLE(snum int not null auto_increment, sname varchar(64) not null ,city char(3)
not null,commission int,primary key(snum));
create table CUSTOMERS(cnum int not null auto_increment,cname varchar(64),city char(3) not null,
rating int not null, snum int,primary key(cnum),foreign key(snum) references SALESPEOPLE(snum));
insert into SALESPEOPLE(snum,sname,city,commission)values
(100,'SREE’,'MUM',1000);
insert into SALESPEOPLE(sname,city,commission)values
('SUBHAM','HYD',1300),
('SNIGDHA','MUM',1200),
('BIPASHA','KOL',1600),
('MITHI','KOL',1700),
('ANIRUDH','CHE',1800),
('KAUSTAV','LEH',1550),
('RUBY','AGR',1680),
('SHROBONA','KOL',1000),
('SIDDHI','MUM',1100);
insert into CUSTOMERS(cname,city,rating,snum)values
('SUDIPTA','KOL',7,102),
('ARINDAM','BIH',8.5,101),
('SHROBONA','MUM',6,102),
('KIRTI','HYD',9,100),
('RAMA','CHE',7,104),
('ROBIN','BEN',8,107),
('SIDDHI','PAT',9,106),
('SRESHTHA','KOL',6.5,103),
('ARITRA','BIH',5,105),
('SAMPARNA','MUM',9,103),
('SUSHANT','HYD',7.5,105),
('RIDDHI','MUM',8,102),
('SNEHA','KOL',9,107),
('MAYAVATI','BIH',5.5,101),
('ALLYSA','CHE',7,103);
create view HIGHEST_RATING AS select cname,city,rating from CUSTOMERS where rating>7;
create view NO_OF_SALESPERSON AS select city,count(*) from SALESPEOPLE group by city;
create view SALESPEOPLE_FROM_KOLKATA AS select * from SALESPEOPLE where city='KOL';
create view SALESOWN AS select snum,sname,city from SAlESPEOPLE;
update SALESOWN SET city='RAJ’ where snum=104;
select * from salesown where city!='KOL';
DROP VIEW salesown;
ASSIGNMENT 6
create table SALESPEOPLE(snum int not null auto_increment, sname varchar(64) not null ,city char(3)
not null,commission int,primary key(snum));
create table CUSTOMERS(cnum int not null auto_increment,cname varchar(64),city char(3) not null,
rating int not null, snum int,primary key(cnum),foreign key(snum) references SALESPEOPLE(snum));
create table ORDERS(onum int not null auto_increment,amt int,odate date,cnum int,snum int,foreign
key(cnum) references CUSTOMERS(cnum),primary key (onum),foreign key(snum) references
SALESPEOPLE(snum));
insert into ORDERS(amt,odate,snum,cnum)values
(12000,'2020-07-19',103,11),
(22000,'2019-12-19',104,5),
(25000,'2019-12-19',103,5),
(18000,'2019-11-08',107,3),
(32000,'2020-03-11',102,9),
(5000,'2019-10-07',106,15),
(2300,'2018-03-10',104,14),
(29000,'2020-02-08',107,7),
(20000,'2020-05-18',101,6),
(22000,'2019-12-19',105,10),
(1900,'2018-12-19',105,4),
(28000,'2020-01-01',106,12);
insert into SALESPEOPLE(snum,sname,city,commission)values
(100,'SREE’,'MUM',1000);
insert into SALESPEOPLE(sname,city,commission)values
('SUBHAM','HYD',1300),
('SNIGDHA','MUM',1200),
('BIPASHA','KOL',1600),
('MITHI','KOL',1700),
('ANIRUDH','CHE',1800),
('KAUSTAV','LEH',1550),
('RUBY','AGR',1680),
('SHROBONA','KOL',1000),
('SIDDHI','MUM',1100);
insert into CUSTOMERS(cname,city,rating,snum)values
('SUDIPTA','KOL',7,102),
('ARINDAM','BIH',8.5,101),
('SHROBONA','MUM',6,102),
('KIRTI','HYD',9,100),
('RAMA','CHE',7,104),
('ROBIN','BEN',8,107),
('SIDDHI','PAT',9,106),
('SRESHTHA','KOL',6.5,103),
('ARITRA','BIH',5,105),
('SAMPARNA','MUM',9,103),
('SUSHANT','HYD',7.5,105),
('RIDDHI','MUM',8,102),
('SNEHA','KOL',9,107),
('MAYAVATI','BIH',5.5,101),
('ALLYSA','CHE',7,103);
