Author Archives: admin

Mysql Queries

Posted on by | Leave a reply

1.How To Create an SNo In MySql – Similar to row_num in MSSQL

SELECT @rownum:= @rownum+1 AS Sno
  FROM Personal_details p,(SELECT @rownum:=0) r;

How to Subtract 7 Day from Now

SELECT DATE_SUB(NOW(),INTERVAL 7 DAY)</pre>

How to View Active Connections & Process

SHOW PROCESSLIST;

How to Get Balance
Input Table

Query 1

    SELECT date,
          SUM(CASE
               WHEN TYPE = 'cash_in' THEN amount
               END) AS cash_in,
          SUM(CASE
                 WHEN TYPE = 'cash_out' THEN amount
               END) AS cash_out,
          SUM(CASE
                WHEN TYPE = 'cash_in' THEN amount
                ELSE -amount
               END) AS balance
    FROM tbSample4
   WHERE type IN ('cash_in', 'cash_out') 
   GROUP BY date(date)

Query 2

 SELECT a.currency_id, cash_in, cash_out, (cash_in - cash_out) balance
   FROM (SELECT currency_id, SUM(AMOUNT) cash_in
           FROM tbSample4
          WHERE type = 'cash_in'
          GROUP BY currency_id) a,
        (SELECT currency_id, SUM(AMOUNT) cash_out
           FROM tbSample4
          WHERE type = 'cash_out'
          GROUP BY currency_id) b
  WHERE a.currency_id = b.currency_id

Output Table

2.How to Make Selected Names at the Top of List

 SELECT Names
   FROM tbNames</pre>

<strong>Input Table</strong>
<img src="http://codeatelier.files.wordpress.com/2012/07/6.jpg" alt="" width="70" height="235" />

<strong>Output</strong>

<strong>Query1</strong>
[code lang="sql"]
SELECT Names
  FROM tbNames
 ORDER BY CASE WHEN names IN ('Chandiran', 'Suryan') 
               THEN 0
               ELSE 1
               END</pre>

Query2

 SELECT * 
   FROM tbNames
  ORDER BY  Names IN ('Heman','superman') DESC;</pre>
<img src="http://codeatelier.files.wordpress.com/2012/07/7.jpg" alt="" width="75" height="241" />

3.How To Return Row in Column as Comma Separated Value CSV

 
   SELECT GROUP_CONCAT(Name)
     FROM tblNames
    WHERE Id IN(15,16,17,18);

4.How Add OR Condition in Mysql case

CREATE TABLE tblConfirmationStatus (Confirm_Status TINYINT);

INSERT INTO tblConfirmationStatus 
Confirm_Status
VALUES
(1),
(0),
(1),
(null),
(0),
(1),
(null);

Required Output
ConfirmStatus

   Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed

Query 1

 SELECT CASE 
        WHEN Confirm_Status IS NULL OR Confirm_Status = 0 THEN 'Not Confirmed' 
        ELSE  'Confirmed' 
       END AS ConfirmStatus
  FROM tblConfirmationStatus;

Query 2

 SELECT CASE 
        WHEN Confirm_Status &gt; 0 THEN 'Confirmed' 
        ELSE  'Not Confirmed' 
       END AS ConfirmStatus
  FROM tblConfirmationStatus;

MySQL Query To Skip Days – Sunday as Below

CREATE TABLE Patients_Appointment_List(Patent_Id INT  UNSIGNED PRIMARY KEY AUTO_INCREMENT,Patient_Name VARCHAR(255),appointment_Date DATE);

INSERT INTO Patients_Appointment_List
 (Patient_Name, appointment_Date)
  VALUES
 ('Mugil', 20120730),
 ('Vinu',  20120730),
 ('Madhavan',  20120729),
 ('Gopal',  20120730),
 ('Shivaji',  20120731);

SELECT Patient_Name
  FROM Patients_Appointment_List
 WHERE CASE WHEN WEEKDAY(CURDATE() + INTERVAL 1 DAY) = 6 THEN 
       appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 2 DAY 
            ELSE appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
       END

Output as on 20120728
Patient_Name

Mugil
Vinu
Madhavan
Gopal

MySQL Procedure Part1

Posted on by | Leave a reply

A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.

Now let us see how to Create stored procedure in mysql
Code for Simple Procedure

DELIMITER //
CREATE PROCEDURE Sample()
BEGIN
  SELECT 'Hello World !';
END//

Calling Procedure

CALL Sample()

<strong>Deleting Procedure if Already exists</strong>
[code lang="sql"]
DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages()
BEGIN
  SELECT *
    FROM Pages;
END

Supplying a Argument to Procedure

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(IN Var1 VARCHAR(30))
BEGIN
  SELECT *
    FROM Pages
   WHERE PageName = Var1;
END

Working with Supplied Argument

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(IN Var1 INT)
BEGIN
   SELECT Var1 + 10;
END

Calling Procedure

CALL ShowPages(10)

O/P:
20

Getting output from Procedure

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(OUT Var1 VARCHAR(30))
BEGIN
  SET Var1 = 'Mugilvannan';
  SELECT Var1;
END//

Calling Procedure

CALL ShowPages(@parameter_var1);

O/P:
Mugilvannan

Returns The Last Inserted ID

DELIMITER //
DROP PROCEDURE  IF EXISTS  InsertSubjects;
CREATE PROCEDURE InsertSubjects(OUT Id INT, IN strMenuName VARCHAR(30),IN strPosition INT, IN strVisible TINYINT)
BEGIN
  INSERT INTO Subjects(MenuName, Position, Visible)
VALUES(strMenuName, strPosition, strVisible);

 SELECT LAST_INSERT_ID()
   FROM Subjects
  LIMIT 1;
END//

CALLING ONE PROCEDURE WITHIN ANOTHER PROCEDURE
Procedure1 mp_proc1 Will return value returned by procedure 2

//PROCEDURE 1
DROP PROCEDURE IF EXISTS mp_proc1;
CREATE PROCEDURE mp_proc1(OUT AmenityName1 VARCHAR(100))
BEGIN
 CALL mp_proc2(@amnName);
 SET AmenityName1 = (SELECT @amnName); 
END

//PROCEDURE 2
DROP PROCEDURE IF EXISTS mp_proc2;
CREATE PROCEDURE mp_proc2(OUT AmenityName VARCHAR(100))
BEGIN
 SET AmenityName =(SELECT amenity_name 
                     FROM mp_amenities
                    WHERE id = 2);
END