mysql null values in arithmetic expression sql statement example

mysql null values in arithmetic expression sql statement example

mysql-null-values-arithmetic-expression-sql-example.php


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
  <title>mysql null values in arithmetic expression sql statement example</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <meta name="Author" content="Md Iqbal Hosan">
  <meta name="title" content="mysql null values in arithmetic expression sql statement example">
  <meta name="keywords" content="php, example, code, table, sql command, sql, null values">
  <meta name="description" content="mysql null values in arithmetic expression sql statement example">
  <style>
   h2, h4{background:#eee; color:#000087;}
   h2{ padding:3px; margin:3px; font-size:22px;}
   h4{ padding:2px; margin:2px; font-size:19px;}
   p{padding:2px; margin:2px; color:#0099Fd;}
   body{ background:#FFFFFb;}
  </style>
 </head>
 <body>
  <h2>mysql null values in arithmetic expression sql statement example</h2>
  <pre>
   <?php
    /*
     ---------------------------------------------------------------------
     WE HAVE A TABLE NAMED employees LIKE THIS:

     CREATE TABLE `employees` 
     (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
       `EmployeeID` int(11) NOT NULL auto_increment,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
       `FirstName` varchar(50) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
       `LastName` varchar(50) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       `JoiningDate` datetime default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       `DepartmentID` int(11) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       `Designation` varchar(50) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       `MonthlySalary` double default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       `CommissionPercentage` double default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
       `ContactNo` varchar(20) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
       `EmailAddress` varchar(255) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
       `PresentAddress` varchar(200) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
       `Gender` varchar(1) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
       `Religion` varchar(20) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
       PRIMARY KEY  (`EmployeeID`)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
     );
     
     NOW WE WANT TO GET FirstName AND MONTHLY COMMISSION OF ALL EMPLOYEES
     BY USING ARITHMETIC EXPRESSION IN MYSQL
     ---------------------------------------------------------------------
    */
    $sql = "SELECT FirstName, MonthlySalary*CommissionPercentage FROM employees";
    /*
     -------------------------------------------------------------------------------------
     LIKE ORACLE Arithmetic expressions containing a null value evaluate to NULL IN MYSQL
     -------------------------------------------------------------------------------------
    */
    /*
     -------------------------------------------------------------------------------------
     YOU CAN USE ALIAS FOR MONTHLY COMMISSION LIKE THIS:
     -------------------------------------------------------------------------------------
    */
    $sql = "SELECT FirstName, MonthlySalary*CommissionPercentage AS MonthlyCommission FROM employees";
    /*
     ----------------------------------------------------
     USING PHP YOU CAN DO THIS. 
     GET CONNECTION TO DATABASE BY USING : mysql_connect()
     SELECT DATABASE BY USING : mysql_select_db()
     THEN RUN THE QUERY
     ----------------------------------------------------
    */
    
    mysql_connect('localhost', 'root', 'root');
    mysql_select_db('hr');
    mysql_query($sql);
    
    /*
     -----------------------------------------------------------------------------
     TO RUN THE PHP CODE PROVIDE REAL DATA TO mysql_connect AND mysql_select_db()
     -----------------------------------------------------------------------------
    */
   ?>
  </pre>
 </body>
</html>

mysql null values in arithmetic expression sql statement example - output in the client

No comments:

Post a Comment

leave your comments here..