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 ;

What's New in Visual C++ 6.0

What's New in Visual C++ 6.0

Visual C++ offers many new features and improvements over its predecessor, Visual C++ 5.0. The new features covered in this book follow:

• The compiler offers improved support for the ANSI C++ standard. Boolean types are supported, and template support is improved.
• The development system includes new enhancements to MFC, the Microsoft Foundation Class Library. These enhancements include classes for Internet programming and support for new common controls introduced in Internet Explorer 4.0 and Windows 98.
• The Developer Studio Editor is much improved, and it takes advantage of IntelliSense features originally released as part of Visual Basic. These features include statement completion, which greatly improves your efficiency.
• The debugger included with Visual C++ includes a new feature called Debug and Continue, which enables you to make small changes while debugging and then immediately continue debugging without restarting the application.
• An improved online help system puts the MSDN in easy reach, just a mouse click away. The online help system automatically uses the latest version of the MSDN Library if it's installed on your computer.

MySQL Cursors

       Cursors are supported inside stored procedures and functions and triggers. The syntax is as in embedded SQL. Cursors in MySQL have these properties:

    *    Asensitive: The server may or may not make a copy of its result table
    *    Read only: Not updatable
    *    Nonscrollable: Can be traversed only in one direction and cannot skip rows

Cursors must be declared before declaring handlers. Variables and conditions must be declared before declaring either cursors or handlers.


DECLARE cursor_name CURSOR FOR select_statement

This statement declares a cursor. Multiple cursors may be declared in a stored program, but each cursor in a given block must have a unique name.

The SELECT statement cannot have an INTO clause.


OPEN cursor_name

This statement opens a previously declared cursor.



FETCH cursor_name INTO var_name [, var_name] ...

This statement fetches the next row (if a row exists) using the specified open cursor, and advances the cursor pointer.

If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition).



CLOSE cursor_name

This statement closes a previously opened cursor.
If not closed explicitly, a cursor is closed at the end of the compound statement in which it was declared. 





 Example:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;