Few Tips / Useful Statements And Functions in SQL

Whenever i try to write some sql to retreive a report to display to a business user there comes a Question of Formatting. You can do it in 2 ways either by retreiving the data and then start formatting it respective language before creating report or use Database functions and Constructs to achieve this.

Of the above 2 options i prefer logic to be embeeded in SQL to the maximum extent as possible

  1. Because it increases the response time.
  2. It decreases dependency on Programming langugage.

Ofcourse there is a negative side of it . You are not much database agnostic. But i think we can live with it. As changing few hundred lines of SQL isn’t too hard than writing whole logic in new framework.

Listed are Few SQL Constructs We Would Love to Use in our Day to Day Life

  1. COALESCE, NULLIF
  2. CASE …. THEN ….ELSE …. END
  3. FORMAT
  4. DATE_FORMAT
  5. STR_TO_DATE
  6. CONCAT, SUBSTR

I Would explain each of them with an Example.

COALESCE

The wiki definition of the above construct is “COALESCE function accepts a list of parameters, returning the first non-Null value from the list”

In RealWorld when we generate reports we wouldn’t like to show business users values like null ..etc. There come the use of this. Lets case scenario where we store Unit_Price of each and every product we sell in a table and our business head requires a report of all products and their corresponding unit prices. But our database might be also containing products which are not yet launched or for which price i not yet determined. Here comes the use of COALESCE.

Here is sample Query SELECT PRODUCT_NAME,COALESCE(UNIT_PRICE,0) FROM PRODUCTS_TABLE;

This displays report where unit price is shown as ‘0’ for all those products whose price tag is not yet decided.

NULLIF :- This does the same function as above statement. But COALESCE is prefered as this is ubiqutious across DB Server

To Be Continued…

Advertisements

One thought on “Few Tips / Useful Statements And Functions in SQL

  1. Морозным зимним утречком юной и одинокий мужчина Селигман обнаруживает на улице избитую и замерзшую женщину Нимфоманка смотреть фильм в полубессознательном пребывании.
    Он приводит ее домой, отогревает,
    рассматривает раны и узнает, что ее имя – Джо.

    Она повествует ему увлекательную и длинную историю о том,
    как когда-то Нимфоманка в молодости поставила себе диагноз «нимфомания» и с
    тех пор ее жизнь находится из бесконечных эротических происшествий.
    То выставляя себя чудовищем, то, наоборот, защищаясь,смотреть онлайн Джо объясняет, что объявила войну окружению, одержимому любовью.
    Отсюда и ее беспорядочные связи: девчонка
    принимала до 8 любовников за ночь бесплатно,
    многие из которых были женаты.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s