Tuesday, 24 November 2015

How to use Time in SQL, 5 fantastic ideas

Most databases live and work in one time zone. If you have a database that covers more than one time zone, you might consider storing time in UTC and adding a numeric column to hold the local time-zone offset.The time zones start at UTC, which has an offset of zero. This is how the system-level time-zone table in Standard SQL is defined.

  • There are also ISO-standard three-letter codes for the time zones of the world, such as EST, for Eastern Standard Time, in the United States. 
  • The offset is usually a positive or negative number of hours, but there were some odd zones that differed by 15 minutes from the expected pattern; these were removed in 1998. 
  • Now you have to factor in daylight saving time on top of that to get what is called “lawful time,” which is the basis for legal agreements.
The U.S. government uses DST on federal lands inside states that do not use DST. If the hardware clock in the computer in which the database resides is the source of the timestamps, you can get a mix of gaps and duplicate times over a year. This is why Standard SQL uses UTC internally. You should use a 24-hour time format. 24-hour time is less prone to errors than 12-hour (A.M./P.M.) time, since it is less likely to be misread or miswritten. This format can be manually sorted more easily, and is less prone to computational errors. Americans use a colon as a field separator between hours, minutes and seconds; Europeans use a period.

(This is not a problem for them, since they also use a comma for a decimal point.) Most databases give you these display options.

One of the major problems with time is that there are three kinds— fixed events (“He arrives at 13:00”), durations (“The trip takes three hours”), and intervals (“The train leaves at 10:00 and arrives at 13:00”)—which are all interrelated. 
  • Standard SQL introduces an INTERVAL data type that does not explicitly exist in most current implementations (Rdb, from Oracle Corporation, is an exception). 
  • An INTERVAL is a unit of duration of time, rather than a fixed point in time—days, hours, minutes, seconds. There are two classes of intervals. One class, called year-month intervals, has an express or implied precision that includes no fields other than YEAR and MONTH, though it is not necessary to use both. 
  • The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH—that is, DAY, HOUR, MINUTE, and SECOND (with decimal places).

Friday, 20 November 2015

Top myths for NULL value in RDBMS database -Handling NULLs with SQL Query

The below are some of the key points about nulls, and how to compare them. Just to make sure that you should know about what is NULL value.
  • Interpretation is not exactly missing value.
  • There could be many reasons why no value is present, e.g., value inappropriate.
How to comapare NULL to values?

In the below example the where condition just checks for TRUE condition. If not matches you will get UNKNOWN.

Example:

Table Name is: Sells

bar          beer    price
================
Joe's bar  Bud   NULL


SELECT bar
FROM Sells
WHERE price < 2.00 OR price >= 2.00;
------------ -------------
UNKNOWN

Joe's Bar is not produced, even though the WHERE condition is correctly given. The reason is the price value is NULL. So, we should not compare NULL to values.

Thursday, 15 October 2015

Sample SQL PL in DB2 -Simple Steps to Write a Stored Procedure

#Sample PL in DB2 and steps how to write it
#Sample PL in DB2 and steps how to write it
The below are the steps in writing sample procedure in SQL.

CREATE PROCEDURE update_sal (IN empnum    CHAR(6), 
                             INOUT rating SMALLINT) 
  LANGUAGE SQL 
  BEGIN 
    IF rating = 1 THEN 
      UPDATE employee 
      SET    salary = salary * 1.10, 
             bonus = 1000 
      WHERE  empno = empnum; 
    ELSEIF rating = 2 THEN 
      UPDATE employee 
      SET    salary = salary * 1.05, 
             bonus = 500 
      WHERE  empno = empnum; 
    ELSE 
      UPDATE employee 
      SET    salary = salary * 1.03, 
             bonus = 0 
      WHERE  empno = empnum; 
    END IF; 
  END;