Programming

Friday, October 29, 2010

Procedure Language in MySQL

Thursday, October 28, 2010

Queries to create tables & insert values into them

create table category_header(cat_code int,cat_desc varchar(20));

create table route_header(route_id int,route_no int,cat_code int,origin varchar(20),destination varchar(20),fare float,distance int,capacity int);

create table place_header(place_id int,place_name varchar(20),place_address varchar(20),bus_station varchar(20));

create table fleet_header(fleet_id int,day date,route_id int,cat_code int);

create table ticket_header(fleet_id int,ticket_no int,doi date,dot date,time_travel varchar(12),board_place varchar(20),origin varchar(20),destination varchar(20),adults int,children int,total_fare float,route_id int);

create table ticket_detail(ticket_no int,name varchar(20),sex char(1),age int,fare float);

insert into category_header values(01,'superdeluxe');
insert into category_header values(02,'deluxe');
insert into category_header values(03,'superfast');
insert into category_header values(04,'normal');

insert into route_header values(101,33,01,'Madurai','Madras',35,250,50);
insert into route_header values(102,25,02,'Trichy','Madurai',40,159,50);
insert into route_header values(103,15,03,'Thanjavur','Madurai',59,140,50);
insert into route_header values(104,36,04,'Madras','Bangalore',79,375,50);
insert into route_header values(105,40,01,'Bangalore','Madras',80,375,50);
insert into route_header values(106,38,02,'Madras','Madurai',39,250,50);
insert into route_header values(107,39,03,'Hydrabad','Madras',50,430,50);
insert into route_header values(108,41,04,'Madras','Cochin',47,576,50);

insert into place_header values(01,'Madras','10 PTC Road','Parrys');
insert into place_header values(02,'Madurai','21 Canalbank Road','KK nagar');
insert into place_header values(03,'Trichy','11 First Cross Road','Bheltown');
insert into place_header values(04,'Bangalore','15 First Main Road','Cubbonpark');
insert into place_header values(05,'Hydrabad','115 Lake view Road','Charminar');
insert into place_header values(06,'Thanjavur','12 Temple Road','Railway Jn');

insert into fleet_header values(01,'2010-08-04',101,01);
insert into fleet_header values(02,'2010-08-04',101,01);
insert into fleet_header values(03,'2010-08-04',101,01);
insert into fleet_header values(04,'2010-08-04',101,02);
insert into fleet_header values(05,'2010-08-04',101,03);
insert into fleet_header values(06,'2010-08-04',101,04);

insert into ticket_header values(01,01,'2010-08-04','2010-09-04','15:00:00','parrys','Madras','Madurai',1,1,60,101);
insert into ticket_header values(02,02,'2010-08-04','2010-09-04','09:00:00','KK Nagar','Madurai','Madras',2,1,60,102);
insert into ticket_header values(03,03,'2010-08-04','2010-09-04','21:00:00','Cubbonpark','Bangalore','Madras',4,2,400,101);
insert into ticket_header values(04,04,'2010-08-04','2010-09-04','10:00:00','Charminar','Hydrabad','Madras',10,0,500,103);
insert into ticket_header values(05,250,'2010-08-04','2010-09-04','15:00:00','parrys','Madras','Cochin',2,2,141,103);

insert into ticket_detail values(01,'Charu','f',24,'14.00');
insert into ticket_detail values(01,'Latha','f',10,'15.55');
insert into ticket_detail values(02,'anand','M',28,'17.80');
insert into ticket_detail values(02,'gautham','M',28,'16.00');
insert into ticket_detail values(02,'bala','M',09,'17.65');
insert into ticket_detail values(05,'sandeep','M',30,'18.00');

alter table route_header add comments long;

desc rout_header;

select distinct cat_code from route_header order by cat_code desc;

alter table route_header modify(distance int);

create table route_detail(route_id int,place_id int,nonstop char);

insert into route_detail values(105,01,'n');
insert into route_detail values(102,02,'s');
insert into route_detail values(106,01,'s');
insert into route_detail values(108,05,'n');

MYSQL Lab List Solutions

1. select * from route_header where origin='madras' and destination='cochin';

2. update route_header set distance=300 where origin in('madras','madurai') and destination in('madras','madurai');

3. delete from route_header where origin = 'madras' and destination = 'bangalore';

4. select * from route_header where origin like 'm%';

5. select * from route_header where fare between 30 and 50;

6. select fare,origin from route_header where route_no>15;

7. select * from place_header where place_name like 'm%';

8. select * from place_header where place_name like 'a%';

9. create table pseudo as (select * from category_header);

10. select add_months(doi,2) from ticket_header;

11. select last_day(dot) from ticket_header;

12. select months_between(dot,doi) from ticket_header;

13. select next_day(dot,'friday') from ticket_header;

14.

I. select round(to_date(day,'dd-mon-yy'),'year') from fleet_header;

II. select round(to_date(day,'dd-mon-yy'),'month') from fleet_header;

III. select round(to_date(day,'dd-mon-yy'),'day') from fleet_header;

IV. select round(to_date(day,'dd-mon-yy')) from fleet_header;

15.

I. select trunc(to_date(day,'dd-mon-yy'),'year') from fleet_header;

II. select trunc(to_date(day,'dd-mon-yy'),'month') from fleet_header;

III. select trunc(to_date(day,'dd-mon-yy'),'day') from fleet_header;

IV. select trunc(to_date(day,'dd-mon-yy')) from fleet_header;

16. select greatest(to_date(doi),to_date(dot)) from ticket_header;

17. select day+31 from fleet_header;

18. select day-60 from fleet_header;

19. select concat(upper(left(name,1)),substring(name,2)) from ticket_detail;

20. select upper(name) from ticket_detail;

21. select lower(place_name) from place_header;

22. select rtrim(place_name,'galore') from place_header where place_name='bangalore';

23. select ltrim(place_name,'hydera') from place_header where place_name='hyderabad';

24. select translate(place_name,'m','v') from place_header where place_name like 'm%';

25. select substr(name,3,3) from ticket_detail;

26. select replace(place_name,'a','e') from place_header;

27. select round(total_fare,2) from ticket_header;

28. select floor(total_fare) from ticket_header;

29. select to_char(sysdate,'ddth "of" fmmonth yyyy')) from dual;

30. select to_char(september 20 1996,'month-dd-yyyy')) from dual;

31. insert into category_header(cat_code) values(80);select nvl(cat_desc,0) from category_header;

32. select avg(total_fare) from ticket_header;

33. select name from ticket_detail where fare=(select max(fare) from ticket_detail);

34. select * from route_header where distance=(select min(distance) from route_header);

35. select sum(total_fare) from ticket_header;

36. select name,count(name) from ticket_detail group by name;

37. update (tablename) set day=’wednesday’ where day=’sunday’