DMBS Queries

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 date_add(doi,interval 2 month) from ticket_header;
11. select last_day(dot) from ticket_header;
12. select period_diff(dot,doi) from ticket_header;
13. SELECT DAY,CASE when (4-weekday(day))>0 then day + interval(4-weekday(day))day else day+interval(11-weekday(day))day end "Next Friday"from fleet_header;
14.
i. select day,case when(month(day)<6) then makedate(year(day),1) else makedate(year(day),12) end "Rounded date" from fleet_header;
ii. select day,case when(day(day))>15 then last_day(day)else date_add(day,interval(1-day(day)) day)end "Round of day"from fleet_header;
 iii. select day,case when(weekday(day)>3) then date_sub(day,interval(weekday(day))day) else date_add(day,interval(6-weekday(day))day) end "Round of day" from fleet_header;
iv. select day, case when (year(day)<1900) then date_\c select day, case when(year(day)<=1950)then date_add(day,interval (2000-year(day))day) else date_sub(day,interval(year(day))day) end "round of year" from fleet_header;
15. select date_format(day, '%D %m %y') from fleet_header;
16. select max(period_diff(dot,doi))"Max.Differance" from ticket_header;
17. select date_add(doi,interval 31 day) from ticket_header;
18. select date_sub(day,interval 60 day) from fleet_header;
19. select concat(upper(left(name,1)),substring(name,2)) from ticket_detail;
20. select ucase(origin)"upper of Orgin",ucase(destination)"upper of Destination" from route_header;
21. select lcase(origin),lcase(destination) from route_header;
22. select substring('bangalore',1,3) from place_header;
23. select substring('hyderabad',-3)from place_header;
24. select replace(place_name,'m','v') from place_header where place_name like 'm%';
25. select substring(name,4,3) from ticket_detail;
26. select replace(place_name,'s','e'),replace(place_name,'a','e') from place_header;
27. select round(sum(fare),2) from route_header ;
28. select round(sum(fare))"Total Fare" from route_header;
29. select date_format(curdate(), '%D %M %Y');
30. select date_format(curdate(),'%d %m %Y');
31.
32. select avg(fare)"Average Fare" from route_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 ticket_no;
37. update fleet_header set day=date_add(day,interval 3 day) where weekday(day)=6;  


Procedure
mysql> delimiter //
mysql> create procedure k1()
-> begin
-> select * from fleet_header;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call k1();


mysql> create procedure k2(k int)
-> begin
-> select * from fleet_header;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call k2(5);






Trigger
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt


mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;


mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+


mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN -> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;//
mysql> delimiter ;