MY SQL:-
create table Prepaid(CustoID varchar(5),Custname
varchar(10),Model varchar(8),Connection varchar(10),Plan int(3));
insert into Prepaid
values("C0001","Sagar","Xiaomi","Vodafone",399);
insert into Prepaid values("C0002","Priya","Oppo","Jio",149);
insert into Prepaid
values("C0003","Milan","Vivo","Vodafone",699);
insert into Prepaid
values("C0004","Kaushik","Redmi","Airtel",250);
insert into Prepaid values("C0005","Karthik","Vivo","Jio",79);
select * from Prepaid where Connection like "Jio" or Connection
like "Vodafone";
select DISTINCT Connection from Prepaid;
select max(Plan) from Prepaid group by Connection;
select count(Model) from Prepaid group by Model;
select * from Prepaid where Custname like '%k';
22:
create table Stock(ITEMNO varchar(5),ITEM
varchar(10),DCODE int(3),QTY int(3),UNITPRICE
int(3),STOCKDATE date);
insert into Stock
values("S005","Ballpen",102,100,10,'2018/04/22');
insert into Stock values("S003","Gel
pen",101,150,15,'2018/03/18');
insert into Stock values("S002","Pencil",102,125,5,'2018/02/25');
insert into Stock values("S006","Eraser",101,200,3,'2018/01/12');
insert into Stock
values("S001","Sharpner",103,210,5,'2018/06/11');
select * from Stock order by STOCKDATE;
select * from Stock order by ITEM DESC;
select avg(UNITPRICE*QTY) from Stock group by DCODE
having avg(UNITPRICE)>5 ;
select sum(QTY) from Stock group by DCODE;
select max(UNITPRICE*QTY) from Stock group by DCODE;
23:
create database Sports;
use Sports;
create table Club(Coach_ID int primary key, Coach_Name
varchar(12) not null,Age int(2),Sports varchar(10), Pay int(7)
check(Pay>30000));
insert into Club values(1,"TARUN",35,"BASKETBALL",50000);
insert into Club
values(2,"ANIRUDDH",34,"SWIMMING",60000);
insert into Club values(3,"ANIMESH",32,"SQUASH",90000);
insert into Club values(4,"ANIL",35,"SWIMMING",50000);
insert into Club values(5,"SUMIT",33,"KARATE",70000);
select Coach_Name from Club where Coach_Name like "A%H";
select sum(Pay) from Club where Sports like "SWIMMING";
select Coach_Name,Age from Club order by Age desc;
update Club set Pay=1.1*Pay;
alter table Club drop primary key;
alter table Club add primary key(Coach_Name);
delete from Club;
alter table Club add Gender varchar(10);
24:
create database Stock;
use Stock;
create table Product(PID varchar(3) primary key, PRODUCT
varchar(25),NUMBER_OF_ITEMS int(3),TOTAL_PRICE int(7)
check(TOTAL_PRICE<100000));
insert into Product values('P01',"CURTAIN",43,52200);
insert into Product values('P02',"BEDSHEET",92,32700);
insert into Product values('P03',"REFRIDGERATOR
COVER",100,21500);
insert into Product values('P04',"PILLOW PACK",20,2430);
insert into Product values('P05',"WASHING MACHINE
COVER",34,2210);
select PRODUCT from Product where PRODUCT like "%R";
select PRODUCT from Product where
NUMBER_OF_ITEMS<50;
select PRODUCT,TOTAL_PRICE from Product order by
TOTAL_PRICE desc;
alter table Product drop primary key;
alter table Product add primary key(Product);
update Product set
NUMBER_OF_ITEMS=1.1*NUMBER_OF_ITEMS;
delete from Product where TOTAL_PRICE<5000;
alter table Product add DATE_OF_MANUFACTURE date;
