MySQL Lab
Work 2024-25
Name: Bhavya Rohila
Class: 12-C
Roll No: 27
Question 1)
create table graduate
( sno int(2) primary key,
name varchar(20) not null,
stipend int(3) not null,
subject varchar(20) not null,
average int(2) not null, divi
varchar(2) not null
);
insert into graduate
values(1,'KARAN',400,'PHYSICS',68,'I');
insert into graduate
values(2,'DIWAKAR',450,'COMP Sc.',68,'I');
insert into graduate
values(3,'DIVYA',300,'CHEMISTRY',62,'I');
insert into graduate
values (4,'REKHA',350,'PHYSICS',63,'I');
insert into graduate
values (5,'ARJUN',500,'MATHS',70,'I');
insert into graduate
values (6,'SABINA',400,'CHEMISTRY',55,'I');
insert into graduate
values (7,'JOHN',230,'PHYSICS',64,'II');
insert into graduate
values (8,'ROBERT',450,'MATHS',68,'I');
insert into graduate
values (9,'RUBINA',500,'CHEMISTRY',62,'I');
insert into graduate
values (10,'VIKAS',400,'MATHS',57,'II');
Answer(a)
Answer(b)
Answer(c)
e(i)
e(ii)
e(iii)
e(iv)
(f) create table guide
(
mainarea char(10) references graduate(subject),
advisor char(20) not null
);
insert into guide
values('PHYSICS','VINOD');
insert into guide
values('COMP.SC','ALOK');
insert into guide
values('CHEMISTRY','RAJAN');
insert into guide
values('MATHS','MAHESH');
select * from guide;
(g)
Question 2)
create table sports
(
studentno int(2) primary key,
class int(2) not null,
name varchar(15) not null,
game1 varchar(20) not null,
grade1 char(1)
check(grade1 in('A','B','C')),
game2 varchar(20) not null,
grade2 char (1)
check(grade2 in('A','B','C'))
);
insert into sports
values(10,7,'SAMMER','CRICKET','B','SWIMMING','A');
insert into sports
values(11,8,'SUJIT','TENNIS','A','SKATING','C');
insert into sports
values(12,7,'KAMAL','SWIMMING','B','FOOTBALL','B');
insert into sports
values(13,7,'VENNA','TENNIS','C','TENNIS','A');
insert into sports
values(14,9,'ARCHANA','BASKETBALL','A','CRICKET','A');
insert into sports
values(15,10,'ARPIT','CRICKET','A','ATHLETICS','C');
select * from sports;
(a)
(b)
(c)
(d)
(e)
(f )
(g)
Question 3)
create table employees
(
empid int(3) primary key,
firstname varchar(10) not null,
lastname varchar(10) not null,
address char(20) not null,
city varchar(20) not null
);
insert into employees
values(010, 'RAVI','KUMAR','RAJ NAGAR','GZB');
insert into employees
values(105, 'HARRY','WALTOR','GANDHI NAGAR','GZB');
insert into employees
values(152, 'SAM','TONES','33 ELM ST.','PARIS');
insert into employees
values(215, 'SARAH','ACKERMAN','440 U.S. 110','UPTON');
insert into employees
values(244, 'MANILA','SENGUPTA','24 FRIENDS STREET','NEW DELHI ');
insert into employees
values(300, 'ROBERT','SAMUEL','9 FIFTH CROSS','WASHINGTON');
insert into employees
values(335, 'RITU','TONDON','SHASTRI NAGAR','GZB');
insert into employees values(400, 'RACHEL','LEE','121 HARRISON ST.','NEW YORK');
insert into employees values(441, 'PETER','THOMPSON','11 RED ROAD','PARIS');
create table empsalary
(
empid int(3) references sports(empid),
salary int(7) not null,
benefits int(7) not null,
designation varchar(15) not null
);
insert into empsalary values(010,75000,15000,'MANAGER');
insert into empsalary values(105,65000,15000,'MANAGER');
insert into empsalary values(152,80000,25000,'DIRECTOR');
insert into empsalary values(215,75000,12500,'MANAGER');
insert into empsalary values(244,50000,12000,'CLERK');
insert into empsalary values(300,45000,10000,'CLERK');
insert into empsalary values(335,40000,10000,'CLERK');
insert into empsalary values(400,32000,7500,'SALESMAN');
insert into empsalary values(441,28000,7500,'SALESMAN')
Employee
EmpSalary
(i) Select Firstname, Lastname, Address, City from Employees where city=”Paris”;
(ii) Select * from Employees order by Firstname desc;
(iii) Select Firstname, Lastname, salary+benefits ”Total Salary” from Employees, EmpSalary
where
Employee.Empid=EmpSalary.Empid;
(iv) Select max(salary) from EmpSalary where Designation=”Manager” and Designation=”Clerk”;
Question 4)
i)Select Book_name from Books where author_name=”P. Purohit” and Publishers=”FIRST PUBL.”;
ii)Select Price from BOOKS where Publishers=’FIRST PUBL.’
iii)update BOOKS set
Price=Price-(Price*0.05)
where Publishers="EPB";
iv)select Book_name,Price from BOOKS,issued where BOOKS.book_id=ISSUED.Book_ID and
issued.Qty_Issued>3;
v)select type,sum(price*qty)'total price' from books
group by type;
vi)select * from BOOKS where Price=(select max(Price) from BOOKS)
Q5
create table PRODUCT
(
PCODE varchar(4) primary key,
PNAME varchar(10) not null,
COMPANY varchar(10) not null,
PRICE int(5) not null,
STOCK int(3) not null,
MANUFACTURE varchar(20) not null,
WARRANTY int(1) not null
);
insert into PRODUCT
values("P001","TV","BPL",10000,200,"12-JAN-2008",3);
insert into PRODUCT
values("P002","TV","SONY",12000,150,"23-MAR-2007",4);
insert into PRODUCT
values("P003","PC","LENOVO",39000,100,"09-APR-2008",2);
insert into PRODUCT
values("P004","PC","COMPAQ",38000,120,"20-JUN-2009",2); insert
into PRODUCT
values("P005","HANDYCAM","SONY",18000,250,"23-MAR-2007",3);
select * from PRODUCT;
(a)select * from PRODUCT where PNAME="PC" and STOCK>110;
(b)select COMPANY from PRODUCT where WARRANTY>2
group by COMPANY;
(c)select sum(PRICE*STOCK)'stock value' from PRODUCT
(d)select COMPANY,count(PNAME) from PRODUCT group by COMPANY;
(e) (f)
(g)
(i)
(g)(ii)
Question 6)
create table faculty
(
fid int(3) primary key,
fname varchar(15) not null,
lname varchar(15) not null,
hiredate date not null,
salary int(7) not null
);
insert into faculty values(102,'AMIT','MISHRA','1998-
10-12',12000); insert into faculty
values(103,'NITIN','VYAS','1994-12-24',8000);
insert into faculty
values(104,'RAKSHIT','SONI','2001-05-18',14000);
insert into faculty
values(105,'RASHMI','MALHOTRA','2004-09-11',11000);
insert into faculty
values(106,'SULEKHA','SRIVASTAVA','2006-06-05',10000);
create table courses
(
cid char(3) primary key, fid
int(3) references faculty(fid),
cname char(35) not null, fees
int(7) not null
);
insert into courses values('C21',102,'GRID COMPUTING',40000);
insert into courses values('C22',106,'SYSTEM DESIGN',16000); insert
into courses values('C23',104,'COMPUTER SECURITY',8000); insert
into courses values('C24',106,'HUMAN BIOLOGY',15000); insert into
courses values('C25',102,'COMPUTER NETWORK',20000); insert into
courses values('C26',105,'VISUAL BASIC',6000);
(i)select * from faculty where salary>12000;
(ii)select * from courses where fees between 15000 and 50000;
(iii)select 500+fees'fees' from courses where cname='SYSTEM DESIGN';
(iv)select * from courses,faculty
where courses.fid=faculty.fid
and faculty.fid=106 order by
cname desc;
(v)select count(distinct fid) from courses;
(vi)select min(salary) from faculty,courses where courses.fid=faculty.fid;
(vii)select max(hiredate),min(hiredate) from faculty;
(viii)select fid,sum(fees) from courses group by fid having sum(fees)>20000;
