Saturday, January 24, 2015

Getting Started with Object Oriented Design - The Core Concepts

Object Oriented Approach consist of three basis steps :
  • Object Oriented Analysis (understand) What to do?
  • Object Oriented Design (plan) How to do?
  • Object Oriented Programming (build) Do it!

We are here to focus on the Basic Terminologies involved in Object Oriented Design.

What is an Object?

An Object is something that has its own characteristics and behavior. Object can be visible or non-visible, can be physical or not.

What is class?

A class is a blueprint of an object. For example : A House , we create a class for the House which defines the basic information of the house and include various attributes and behavioral description of the House. When we create an object we assign the values to the blueprints in the class and create our own different house with different colors , no of houses etc. Like this we can create even thousand different object for a class.

ABSTRACTION : When we say Table. We did not refer to a wooden table or a steel table. We just mentioned a table. This is called Abstraction, when we focus in the essentials and ignore the irrelevant or unimportant detail. Abstraction means that we wont create 2 different classes for Me and You. We will create only one class Person and create 2 different objects of that class for me and you with different attributes.

ENCAPSULATION : Encapsulation is surrounding something , not just to keep the contents together but also to protect it. In OOD Encapsulation refers to taking the attributes and the behavior, bundling them together in a same class. We also Encapsulate to hide the information inside a class that we do not want some other object to directly modify. For example we do not want that someone should be able to change the balance of bank account without going through the functions deposit() or withdraw(). We hide the balance attribute and only show the deposit and withdraw behavior of the object. This is also referred to the idea of black boxing where we do not care about the inner working of the box, but we just want to use only some of the functionality of the box. For example a telephone , we do not want to know how the call is connected to the number we enter , we just want to enter the number and talk.

INHERITANCE : Inheritance is extending support from a previously build class to create a new one. For example we had talked about the class Person. Suppose now we need one more class called Student and Employee. When we go to write the class Student we notice that we are having the same information as it is in Person class plus some more detail like the registration number or the course. So what we can do is instead of writing the same information for the Student or the Employee we can inherit the person class in both the Student and Employee to have all the attributes and the behavior of Person in them instead of writing them again for each class.


POLYMORPHISM (many + forms) : Polymorphism is doing the right thing at the right time knowing that we can do many different things for the same expression but we do what is correct for that expression. For example, we have and abstract class for a Bank Account extending its functionality to more specific sub classes Saving account or Investment Account(Inheritance). Now suppose we want that we can not withdraw more than 20,000 from a savings account. So what we do is we create a new function in Savings Account class and add this functionality to it. But the Bank Account class also had the same named behavior as Withdraw which simply withdraws the money from the account. This behavior is inherited by the Savings Account class. Now the saving account class has two behaviors for withdraw , one is inherited from Bank Account and another is defined in the Saving class itself. This is the class we call Overriding because the behavior we defined in the Saving class will override the behavior inherited from the Bank Account class. Now we can create thousands of objects of bank accounts using any of the class (Bank Account , Savings Account, Investment Account) we can use the same behavior withdraw on the all of these objects all together and we know that polymorphism will make sure the correct behavior is done automatically for the Saving Account when a withdraw is done.

Thursday, January 1, 2015

String Functions in Transact SQL



       

  •       ASCII


This Function is used to get the ASCII code of the input char. It returns an int value corresponding to the char. For Example :

              SELECT ASCII('c') AS 'ASCII Code' , 'c' AS 'Character';
OUTPUT:
               ASCII Code  Character
      ----------- ---------
      99          c

  •      CHAR


This function is used to get the character corresponding to an integer ASCII code. For Example:

              SELECT CHAR( ASCII('c') ) AS 'Character' , ASCII('c') 'ASCII Code'
OUTPUT:
               Character ASCII Code
      --------- -----------
      c         99

  •      CONCAT


As the name suggests this function is used to join together multiple string. For Example:

             SELECT CONCAT('First Name',’ ’,'Last Name');
OUTPUT:

       First Name Last Name

  •      LEFT


This functions gives us the specified number of character from the left side of a string. For Example:

             SELECT LEFT('First Name',5);
OUTPUT:

       First


  •      RIGHT


This functions gives us the specified number of character from the right side of a string. For Example:

              SELECT RIGHT('First Name',4);
OUTPUT:
       Name


  •       LEN


This function is used to get the length of a given String. For Example:

              SELECT LEN('NAME');
OUTPUT:

       4

  •      LOWER


This functions converts the given string to all LOWER CASE. For Example:

              SELECT LOWER('First Name');
OUTPUT:

       first name

  •       UPPER


This functions converts the given string to all UPPER CASE. For  Example:

               SELECT UPPER('First Name');
OUTPUT:

       FIRST NAME


  •       LTRIM


This functions removes all the extra spaces from a string at the LEFTMOST position. For Example:

       SELECT LTRIM('    First Name');
OUTPUT:

       First Name


       NOTE: This function is only concerned with the LEFTMOST spaces, it will not temper   
             with the middle or rightmost spaces.


  •       RTRIM


This functions removes all the extra spaces from a string at the RIGHTMOST position. For Example:

       SELECT RTRIM('First Name     ');
OUTPUT:

       First Name


       NOTE: This function is only concerned with the RIGHTMOST spaces, it will not temper   


             with the middle or leftmost spaces.


  •       REPLACE


This functions is used to a replace a given pattern in a string to a another pattern. For  Example:

               SELECT REPLACE('First Name','NAME','XXX');
OUTPUT:
       First XXX

'First Name' is the given String.
'NAME' is the pattern to be replaced within the String 'First Name'
'XXX' is the pattern that is added to the the String 'First Name' in place of 'NAME'

       NOTE : If the pattern in not found in the given string then the string is returned as it is with no placement.
                   By default the function searches of the given pattern in the string case-insensitively. To search for the pattern case-sensitively use COLLATION.

  •       REVERSE


This functions is used to REVERSE the order of Character in a String. For  Example:

                SELECT REVERSE ('FIRST NAME');
OUTPUT:
       EMAN TSRIF
       
       SELECT REVERSE(REVERSE ('FIRST NAME'))
OUTPUT:

       FIRST NAME

  •        STR


This functions is used convert a float expression to String. For  Example:

                SELECT STR(5.09,4,3);
OUTPUT:

        5.09

5.09 is the float expression that we want to convert.
4 is the length of total number of characters displayed in the resulting string. If the resulting string exceeds the given length then the result is trimmed from right to match the given length. 

3 is the length of the number of decimal places to be displayed. In Our case we got only 2 numbers after the decimal because the maximum provided length(4) was exceeded.






                  


TOP in T-SQL




Using the TOP clause lets you select n rows from the beginning of the selected output. 
You can use TOP with the following syntax in T-SQL :

        SELECT TOP n
        *
        FROM table_name;

TOP is mostly used to fetch the TOP n rows according to any particular ORDER . For example selecting the TOP employees taking the maximum salary in an Organization. The query would look like this:

        SELECT TOP 5
        *
        FROM emp
        ORDER BY salary;

We can use the TOP to get random outputs also without using ORDER BY according to our requirements. We may use TOP to select randomly TOP 5 lucky draw winner.

TOP can be used with two other options PERCENT and WITH TIES. These are optional and can be skipped while using the TOP. We will look into each in detail one by one:

  • PERCENT : This option is used if we want to use the TOP to select a certain percentage of all the data in the table, such as getting TOP 25% of the entries. The syntax for using TOP with PERCENT is :

        SELECT TOP 25 PERCENT
        *
        FROM emp;

The above query will select TOP 25% of the rows from a table.

  • WITH TIES : This option can only be used with ORDER BY. WITH TIES is used to encourage the DUPLICATE values for the last row in the result. When used with WITH TIES option, TOP will return TOP n rows and if the last row is 5th then if the 6th row has the same value as the 5th row then using the WITH TIES option the 6th row will also be returned . The syntax for using WITH TIES with TOP is :

        SELECT TOP 25 PERCENT
        *
        FROM emp;


emp is the table containing the following data :

Empno
ename
job
mgr
hiredate
sal
comm
deptno
7369
SMITH
CLERK
7902
1980-12-17
800.00
NULL
20
7499
ALLEN
SALESMAN
7698
1981-02-20
1600.00
300.00
30
7521
WARD
SALESMAN
7698
1981-02-22
1250.00
500.00
30
7566
JONES
MANAGER
7839
1981-02-04
2975.00
NULL
20
7654
MARTIN
SALESMAN
7698
1981-09-29
1250.00
1400.00
30
7698
BLAKE
MANAGER
7839
1981-01-05
2850.00
NULL
30
7782
CLARK
MANAGER
7839
1981-06-09
2450.00
NULL
10
7788
SCOTT
ANALYST
7566
1987-07-13
3000.00
NULL
20
7839
KING
PRESIDENT
NULL
1981-11-17
5000.00
NULL
10
7844
TURNER
SALESMAN
7698
1981-08-09
1500.00
0.00
30
7876
ADAMS
CLERK
7788
1987-07-13
1100.00
NULL
20
7900
JAMES
CLERK
7698
1981-12-03
950.00
NULL
30
7902
FORD
ANALYST
7566
1981-12-03
3000.00
NULL
20
7934
MILLER
CLERK
7782
1982-01-23
1300.00
NULL
10










The output of this query will be:

Empno
ename
Job
mgr
hiredate
sal
comm
deptno
7369
SMITH
CLERK
7902
1980-12-17
800.00
NULL
20
7499
ALLEN
SALESMAN
7698
1981-02-20
1600.00
300.00
30
7521
WARD
SALESMAN
7698
1981-02-22
1250.00
500.00
30
7566
JONES
MANAGER
7839
1981-02-04
2975.00
NULL
20
7654
MARTIN
SALESMAN
7698
1981-09-29
1250.00
1400.00
30

Now WITH TIES won’t make any difference in our case. The result seems similar to the query without using the WITH TIES. But if we had 2 entry in our table as :

7654
MARTIN
SALESMAN
7698
1981-09-29
1250.00
1400.00
3
7654
MARTIN
SALESMAN
7698
1981-09-29
1250.00
1400.00
30

Then using TOP without WITH TIES will not return both the rows because on of the above duplicate rows will not be returned as only one comes in TOP 5 and the other one is at 6th position so only one is returned.

But using TOP with WITH TIES will return BOTH 5th and 6th row.

Empno
ename
Job
mgr
Hiredate
sal
comm
deptno
7369
SMITH
CLERK
7902
1980-12-17
800.00
NULL
20
7499
ALLEN
SALESMAN
7698
1981-02-20
1600.00
300.00
30
7521
WARD
SALESMAN
7698
1981-02-22
1250.00
500.00
30
7566
JONES
MANAGER
7839
1981-02-04
2975.00
NULL
20
7654
MARTIN
SALESMAN
7698
1981-09-29
1250.00
1400.00
30
7654
MARTIN
SALESMAN
7698
1981-09-29
1250.00
1400.00
30

NOTE : the duplicate values are only displayed id they are the last rows in our output. This will not work with any row appearing at the position less than our n (TOP n) because for any row at position less than `n` the duplicate values are returned even without using WITH TIES and the duplicates are counted in the no. of rows returned.


TOP is executed at last while executing a query because the user expects the final output to be shortened according to the number n , not the random output. For example when using the TOP clause with ORDER BY statement , you will expect it to result the TOP n rows of the ORDERED LIST not the TOP n rows of the random data in the TABLE and then ORDER it. Here is an example to make you understand better:

Consider the following query which selects the TOP 5 salary takers in a company

       SELECT TOP 5
       *
       FROM emp;
       ORDER BY sal;

Our query will return the following rows (TOP 5 salary takers):

Empno
ename
job
mgr
hiredate
sal
comm
deptno
7839
KING
PRESIDENT
NULL
1981-11-17
5000.00
NULL
10
7902
FORD
ANALYST
7566
1981-12-03
3000.00
NULL
20
7788
SCOTT
ANALYST
7566
1987-07-13
3000.00
NULL
20
7566
JONES
MANAGER
7839
1981-02-04
2975.00
NULL
20
7698
BLAKE
MANAGER
7839
1981-01-05
2850.00
NULL
30

But imagine if the TOP clause were to run before the ORDER BY clause what would have been the output:

First the Database will SELECT the TOP 5 rows FROM the table which stores data randomly without any ORDER.
Then the database would execute the ORDER BY clause on the TOP 5 rows selected from the randomly ordered data. The output will look like this:

Empno
ename
job
mgr
hiredate
sal
comm
deptno
7566
JONES
MANAGER
7839
1981-02-04
2975.00
NULL
20
7499
ALLEN
SALESMAN
7698
1981-02-20
1600.00
300.00
30
7521
WARD
SALESMAN
7698
1981-02-22
1250.00
500.00
30
7369
SMITH
CLERK
7902
1980-12-17
800.00
NULL
20
1001
Varun
NULL
NULL
NULL
100.00
NULL
NULL

So if the TOP clause would have been executes before the ORDER BY clause we would get unexpected results.