3.1 Create an Index via Primary Key

On the one hand, we can use a primary key, which every table should have anyway.

ALTER TABLE example_Table ADD ID INT IDENTITY(1,1) PRIMARY KEY;

SELECT ID
    FROM example_Table
    ;

The disadvantage here is if you want to use this only for a temporary index, that you can no longer delete it, which of course also makes sense.

ALTER TABLE example_Table DROP COLUMN ID;

3.2 Create an Index via a new Column (recommended)

A better method would be to first generate a new column in the data set and then fill it with a consecutive number.

ALTER TABLE example_Table ADD Idx INT;

SELECT ID, Idx
    FROM example_Table
    ;

Now we fill the new column:

DECLARE @Idx INT 
SET @Idx = 0 
UPDATE example_Table 
SET @Idx = Idx = @Idx + 1 

SELECT ID, Idx
    FROM example_Table
    ;

We can delete these again at will.

ALTER TABLE example_Table DROP COLUMN Idx;

SELECT *
    FROM example_Table
    ;

4.1 Replace NULL Values (ISNULL)

Let’s take a look at this part of the data set:

SELECT Cat1, Cat2
    FROM example_Table
    ;

With ISNULL we have the possibility to replace NULL values from columns with other values.

SELECT Cat1, Cat2,
       ISNULL(Cat1, 'Unknown') AS non_null_Cat1,
       ISNULL(Cat2, 999) AS non_null_Cat2
    FROM example_Table
    ;

Hint. in other SQL languages the syntax is different:

• MySQL = IFNULL()
• MS Access = IsNull()
• Oracle = NVL()

ISNULL can also be used nested:

SELECT Cat1, 
       AVG(ISNULL(Cat2, 10)) AS avg_non_null_Cat2, 
       AVG(Cat2) AS avg_with_null_Cat2
    FROM example_Table
    GROUP BY Cat1
    HAVING Cat1 = 'A' OR Cat1 = 'B'
    ;

To track the calculation even better see the modified syntax here:

SELECT Cat1, 
       AVG(ISNULL(Cat2, 10)) AS avg_non_null_Cat2, 
       AVG(Cat2) AS avg_with_null_Cat2,
       Count(ISNULL(Cat2, 10)) AS num_abs_non_null_Cat2,
       Count(Cat2) AS num_abs_with_null_Cat2,
       SUM(ISNULL(Cat2, 10)) AS sum_non_null_Cat2,
       SUM(Cat2) AS sum_with_null_Cat2
    FROM example_Table
    GROUP BY Cat1
    HAVING Cat1 = 'A' OR Cat1 = 'B'
    ;

Let’s take a look at Cat1, B (non NULL): 26/5 = 5.2 = 5 Works fine!

4.2 Replace Values with NULL (NULLIF)

The same game goes around in the other direction. Let’s take a look at this part:

SELECT ID
    FROM example_Table
    ;

Now I want to specifically set some values to NULL

SELECT ID,
       NULLIF(ID, 8) AS null_if_ID
    FROM example_Table
    ;

This is of course only possible because I have preset that the column ID may also contain NULL values (which is normally not the case, especially not if it is the primary key):

Limitation: only one value can be set as NULL in this way.

If we want to set several values to NULL or replace several values with new ones we have to resort to another function: CASE.

4.3 IF Else Statemtents in SQL (CASE)

SELECT ID,
        CASE 
            WHEN ID = 7 THEN NULL
            WHEN ID = 8 THEN NULL
            WHEN ID = 9 THEN NULL
            ELSE ID
        END AS ID_with_null
    FROM example_Table
    ;

SELECT ID, Title
    FROM example_Table
    ;

SELECT ID, Title,
        CASE 
            WHEN Title = 'Mr' THEN 'Male'
            WHEN Title = 'Mrs' THEN 'Female'
            WHEN Title = 'Miss' THEN 'Female'
            WHEN Title = 'Ms' THEN 'Female'
            ELSE 'Unknown'
        END AS Gender
    FROM example_Table
    ;

SELECT ID, Title,
        CASE 
            WHEN Title IN ('Mrs', 'Miss','Ms') THEN 'Female'
            ELSE 'Male'
        END AS Gender
    FROM example_Table
    ;

SELECT First_Name, Age
    FROM example_Table
    WHERE Age IS NOT NULL
    ;

SELECT First_Name, Age,
        CASE 
            WHEN Age BETWEEN 0 AND 35 THEN 'young' 
            WHEN Age BETWEEN 36 AND 99 THEN 'old'  
        END AS Age_State
    FROM example_Table
    WHERE Age IS NOT NULL
    ;

SELECT First_Name, Age, Cat1
    FROM example_Table
    WHERE Age IS NOT NULL
    ;

SELECT First_Name, Age, Cat1,
        CASE 
            WHEN Age < 30 AND Cat1 = 'A' THEN 'Young_Professional' 
            WHEN Age < 40 OR Cat1 = 'B' THEN 'Expert'
            ELSE 'Normal_Employee'
        END AS Expertise
    FROM example_Table
    ;

4.4 IF Statemtents in SQL (IIF)

The syntax is very simple but of course somewhat limited: IIF(boolean_expression,true_value,false_value) It must be a valid boolean value, or the function will raise an error.

Here is a simple example:

SELECT IIF(500<1000, 'true', 'false') AS _500_is_less_than_1000;

Since we have only one title notation for the man in the example shown above, we can also apply the IIF in this case.

SELECT ID, Title,
       IIF(Title = 'Mr', 'M', 'F') AS Gender
    FROM example_Table
    ;

IIF also works wonderfully with local variables:

DECLARE @BankBalance INT = 150;

SELECT IIF(@BankBalance > 100, 'Rich!', 'Poor');

Even nested is no problem:

DECLARE @bankBalance INT = 120;

SELECT IIF(
    @BankBalance > 100, 
        IIF(@BankBalance > 150, 'Rich!', 'Wealthy'), 
    'Poor'
    );

4.5 COALESCE

COALESCE is a more difficult function but super helpful in some situations that I will show you in a moment:

SELECT First_Name, Age
    FROM example_Table
    ;

SELECT First_Name, Age,
       COALESCE(Age, AVG(Age) over ()) as new_Age
    FROM example_Table
    ;

SELECT First_Name, Middle_Name, Last_Name
    FROM example_Table
    ;

SELECT First_Name, Middle_Name, Last_Name,
       First_Name + ' ' + Middle_Name + ' ' + Last_Name AS Full_Name
    FROM example_Table
    ;

SELECT First_Name, Middle_Name, Last_Name,
       First_Name + ' ' + COALESCE(Middle_Name, '') + ' ' + Last_Name AS Full_Name
    FROM example_Table
    ;

SELECT First_Name, LastOrderDate, LastWebSearchDate, LastInquiryDate, LastPaymentDate 
    FROM example_Table
    ;

SELECT First_Name, 
       COALESCE(LastOrderDate, LastWebSearchDate, LastInquiryDate, LastPaymentDate) AS LastActivityDate
    FROM example_Table
    ;

SELECT First_Name,
        CASE 
            WHEN LastOrderDate IS NOT NULL THEN LastOrderDate
            WHEN LastWebSearchDate IS NOT NULL THEN LastWebSearchDate
            WHEN LastInquiryDate IS NOT NULL THEN LastInquiryDate
            WHEN LastPaymentDate IS NOT NULL THEN LastPaymentDate
        END AS LastActivityDate
    FROM example_Table
    ;

CREATE TABLE COALESCE_Table (Char_Variable VARCHAR(10))

INSERT INTO COALESCE_Table VALUES(NULL)

SELECT * FROM COALESCE_Table

SELECT Char_Variable, 
       ISNULL(Char_Variable, 'some_Text') AS filled_Char_Var
    FROM COALESCE_Table
    ;

SELECT Char_Variable, 
       ISNULL(Char_Variable, 'some_very_long_Text') AS filled_Char_Var
    FROM COALESCE_Table
    ;

SELECT Char_Variable,
       COALESCE(Char_Variable, 'some_very_long_Text') AS filled_Char_Var
    FROM COALESCE_Table
    ;

ALTER TABLE COAlESCE_Table ADD Num_Variable INT 

SELECT * FROM COALESCE_Table

SELECT Num_Variable,
       5 / ISNULL(Num_Variable, 2.00) AS _ISNULL, 
       5 / COALESCE(Num_Variable, 2.00) AS _COALESCE
    FROM COALESCE_Table
    ;

7.1 Create Example Data

CREATE TABLE customers
    (Name VARCHAR(100),
    Debts FLOAT,
    Salary INT,
    Age INT,
    Purchasing_Power FLOAT)
    ;

INSERT INTO customers (Name, Debts, Salary, Age, Purchasing_Power) VALUES ('A', 480000, 2000, 25, 150.84)
INSERT INTO customers (Name, Debts, Salary, Age, Purchasing_Power) VALUES ('B', 225000, 4000, 30, 420.31)
INSERT INTO customers (Name, Debts, Salary, Age, Purchasing_Power) VALUES ('C', 407000, 8500, 42, 85.17)
INSERT INTO customers (Name, Debts, Salary, Age, Purchasing_Power) VALUES ('D', 670000, 6000, 22, 320.78)
INSERT INTO customers (Name, Debts, Salary, Age, Purchasing_Power) VALUES ('E', 160000, 4150, 18, 240.45)

SELECT *
    FROM customers
    ;

7.2 CAST

Let’s say we want to get the salary in thousands:

SELECT Salary / 1000 AS Salary_in_thousand
    FROM customers
    ;

Since the Salary column in the customers table is stored as INT, we also receive only INT values as output.

Therefore, we need to use CAST:

SELECT CAST(Salary AS FLOAT) / 1000 AS Salary_in_thousand
    FROM customers
    ;

Now let’s divide the Debt column by the Salary column to see how long it will take me to pay this off.

SELECT Debts / Salary AS num_years
    FROM customers
    ;

Also this issue is just not advantageous for my purpose. Therefore we use CAST again.

SELECT CAST(Debts AS INT) / CAST(Salary AS INT) AS num_years
    FROM customers
    ;

Do I really need to cast both columns?

No!

SQL would not be SQL if this could not be done more simply.

SELECT CAST(Debts / Salary AS INT) AS num_years
    FROM customers
    ;

7.3 CONVERT

As already described above, CONVERT does this job for us as well, is only slightly different from the syntax notation.

Here again the original data set:

SELECT *
    FROM customers
    ;

In the following I will show the two examples from the previous chapter again, only this time with CONVERT:

SELECT CONVERT(FLOAT, Salary) / 1000 AS Salary_in_thousand
    FROM customers
    ;

SELECT CONVERT(INT, Debts / Salary) AS num_years
    FROM customers
    ;

8.1 ROUND

With Round we have the possibility to determine the number of decimal places. In our example, we round the Purchasing_Power variable to one decimal place.

SELECT Purchasing_Power, 
       ROUND(Purchasing_Power, 1) AS rounded_power_1
    FROM customers
    ;

Of course, we also have the option to round to zero decimal places. Here, the function rounds the number down to a value of .49 and up from .50.

SELECT Purchasing_Power, 
       ROUND(Purchasing_Power, 0) AS rounded_power_0
    FROM customers
    ;

8.2 CEILING

However, if we want the number to always be rounded up regardless of the amount of decimal places, then we need the CEILING function.

SELECT Purchasing_Power,
       ROUND(Purchasing_Power, 0) AS rounded_power_0,
       CEILING(Purchasing_Power) AS ceiled_power
    FROM customers
    ;

8.3 FLOOR

If we want to have the numbers always rounded off, we need FLOOR.

SELECT Purchasing_Power,
       ROUND(Purchasing_Power, 0) AS rounded_power_0,
       FLOOR(Purchasing_Power) AS floored_power
    FROM customers
    ;