Program 14
My SQL 1
Consider the following table CLUB
Coach_Id C_Name Age Sports DOAPP Pay Gender
1 KUREJA 35 KARATE 27/03/2008 31000 M
2 RAVINA 34 KARATE 20/0102015 22000 F
3 KARAN 34 SQUASH 25/02/2009 34000 M
4 TARUN 36 KARATE 06/09/2012 11000 M
5 ZUBIN 39 SWIMMING 04/06/2009 18000 M
6 ZAREEN 37 BASKETBALL 10/20/2019 12000 F
7 AKSHAY 34 KARATE NULL 29000 M
8 KAREENA 32 CRICKET 20/05/2019 30000 F
9 HAMSINI 33 BASKETBALL 01/03/2019 28000 F
10 GAUTHAM 29 KARATE 30/05/2015 28000 M
Write MySQL queries to
1) Create a table structure as shown above
Ans:-
create table club
(coach_id int primary key,
c_name varchar(25),
age int,
sports varchar(20),
doapp date,
pay int,
gender char(1));
insert into club values(1,'KHUREJA',35,'KARATE','2008/03/27'
,31000,'M');
insert into club
values(2,'RAVINA',34,'KARATE','2015/01/20',22000,'F');
insert into club values(3,'Karan',34,'SQUASH','2009/02/30'
,34000,'M');
insert into club values(4,'Tarun',36,'KARATE','2012/09/06'
,11000,'M');
insert into club values(5,'Zubin',39,'SWIMMING','2009/06/04'
,18000,'M');
insert into club
values(6,'ZAREEN',37,'BASKETBALL','2019/10/10' ,12000,'F');
insert into club
values(7,'AKSHAY',34,'KARATE',NULL,29000,'M');
insert into club values(8,'KAREENA',32,'CRICKET','2019/05/20'
,30000,'F');
insert into club
values(9,'HAMSINI',33,'BASKETBALL','2019/03/01' ,28000,'F');
insert into club values(10,'GAUTHAM',29,'KARATE','2015/05/30'
,28000,'M');
2) Display the details of female coaches in Karate
3) Display the details of coaches who joined in the year 2019
4) Display the entire table in the ascending order of sports, the coaches in same
sports in descending order of names
5) Display the different sports events available with each even appearing only once
under the heading “Sports in offer”
6) Display the names of coaches for Karate and cricket. Use the ‘in’ clause.
7) Display the details of coaches earning at least 25000, but less than 30000. USe
the between clause.
8) Display the names of coaches whose date of appointment is not mentioned.
9) Display all 5 lettered names.
10) Display the names of coaches whose name does not end with the letter ‘a’
11) Increase the salary of all cricket coaches by 10%
12) Change the data type of the column ‘Pay’ as decimal
13) Delete the record for “Gautham”
14) Display the number of coaches for each Sports item
15) Display the minimum and maximum payment of coaches
16) Display the sports events which have more than one coach
17) Add a new column “Incentive” with data type decimal (6,2)
Program 15
My SQL -2
Consider the tables
Bus
BusNo Make Type Capacity Seat_Type
V001 Volvo AC 35 Semi Sleeper
V002 Volvo NonAC 20 Sitting
T025 TATA AC 21 Full Sleeper
M012 Mahindra AC 35 Semi Sleeper
M014 Mahindra Non AC 32 Semi Sleeper
Passengers
TicketNo BusNo P_Name Age Source Destination
P_1903_001 V001 Kanishka 23 Mumbai Pune
P_1803_002 V002 Meghnadh 31 Thane Mumbai
P_1803_005 T025 Arjun 45 Agra Delhi
P_1803_025 M012 Dharani 24 Agra Delhi
P_1804_066 M014 Jagan 28 Mathura Delhi
P_1804_071 M014 Lakshmi 29 Delhi Mathura
Write My SQL queries to
1) Create table structures as shown above
mysql> create table bus
-> (BusNo varchar(5) primary key,
-> Make varchar(20),
-> Type varchar(20),
-> Capacity int,
-> Seat_Type varchar(20));
mysql> insert into bus
values("V001","Volvo","AC",35,"Semi Sleeper");
mysql> insert into bus values("V002","Volvo","Non
AC",20,"Sitting");
mysql> insert into bus values("T025","Tata","AC",21,"Full
Sleeper");
mysql> insert into bus
values("M012","Mahindra","AC",35,"Semi Sleeper");
mysql> insert into bus values("M014","Mahindra","Non
AC",32,"Semi Sleeper");
mysql> create table passengers
-> (Ticket_No varchar(12) primary key,
-> BusNo varchar(5),
-> P_Name varchar(20),
-> Age int,
-> Source varchar(20),
-> Destination varchar(20),
-> foreign key (BusNo) references bus(BusNo));
mysql> insert into passengers values('P_1903_001','V001',
'Kanishka', 23, 'Mumbai','Pune');
mysql> insert into passengers values('P_1803_002','V002',
'Meghnadh', 31, 'Thane','Mumbai');
mysql> insert into passengers values('P_1803_005','T025',
'Arjun', 45, 'Agra','Delhi');
mysql> insert into passengers values('P_1803_025','M012',
'Dharani', 24, 'Agra','Delhi');
mysql> insert into passengers values('P_1804_066','M014',
'Jagan', 28, 'Mathura','Delhi');
mysql> insert into passengers values('P_1804_071','M014',
'Lakshmi', 29, 'Delhi','Mathura');
2) Display the name and age of passengers travelling in Volvo buses
3) Display the the details of passengers travelling from Agra to Delhi
4) Display the names of passengers travelling to or from Delhi
5) Display the name and age of passengers travelling by AC Full Sleeper buses
6) Count and display the makes for which more than one bus is plying
7) Display the makes and total capacity for which the total seating capacity is more
than 50
8) Display the details of passengers who are not travelling to Delhi or Mumbai. Use
the ‘in’ clause
9) Display the average age of passengers travelling to Delhi
10) Display the equi join of both the tables