Case statement in SQL Server

The SQL Server CASE statement is a conditional statement that returns a single value based on the evaluation of a statement. CASE expressions can be used in SQL anywhere an expression can be used like SELECT list, WHERE clause, HAVING clause, IN list, DELETE and UPDATE statements. CASE statement can also be nested. This provides a lot of pliability for evaluating multiple expressions.

SQL Case statement can be used in 2 forms:

  1. SQL CASE statement with simple expression to compare and get results.
  2. SQL CASE statement with search or comparison expression to get results.
  1. SQL CASE statement with simple expression to compare and get results

    A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency.

    Syntax

    CASE expression
        WHEN expression1 THEN expression1
        [[WHEN expression2 THEN expression2] [...]]
        [ELSE expression N]
    END
    

    Example

    SELECT CASE [Section]
        WHEN 1 THEN 'One'
        WHEN 2 THEN 'Two'
        WHEN 3 THEN 'Three'
        WHEN 4 THEN 'Four'
        WHEN 5 THEN 'Five'
        ELSE 'Greater than Five'
    END as [Sec_Word] FROM [Table_Test]
    
  2. SQL CASE statement with search or comparison expression to get results

    A search or comparison CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression.

    Syntax

    CASE
        WHEN Boolean_expression1 THEN expression1
        [[WHEN Boolean_expression2 THEN expression2] [...]]
        [ELSE expression N]
    END
    

    Example

    SELECT CASE
        WHEN [Section] < 5 THEN 'Less than Five'
        WHEN [Section] = 5 THEN 'Equal to Five'
        WHEN [Section] > 5 THEN 'Greater than Five'
    END as [Sec_Word] FROM [Table_Test]
    

Hope you will find this post helpful. For any suggestion or query please feel free to post your valuable comments. Thanks.

Introduction to JOINs in SQL Server

The nature of relational database design shows that we will have related data that is stored in different tables. To retrieve data from two or more tables we use JOINS.

The JOIN clause tells the database how the data in the two tables is related so that it can return a correct representation of the related data.

Before entering into JOINs concept in SQL Server first design two tables in database, which I will be using to give you a better understanding of JOINs.

Table1Table2

In this blog I am going to discuss about two JOIN type:

  1. INNER JOIN
  2. OUTER JOIN

INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

inner-join

Example of Inner Join in SQL Server

SELECT * FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Inner Join in SQL Server
Inner-Join

OUTER JOIN

There are three types of OUTER JOIN’s in SQL Server

  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

LEFT OUTER JOIN

LEFT OUTER JOIN in SQL Server returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

left-join

Example of Left Outer Join in SQL Server

SELECT * FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Left Outer Join in SQL Server
Left-Outer-Join

RIGHT OUTER JOIN

RIGHT OUTER JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

right-join

Example of Right Outer Join in SQL Server

SELECT * FROM Table1 t1
RIGHT OUTER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Right Outer Join in SQL Server
RIGHT-OUTER-JOIN

FULL OUTER JOIN

FULL OUTER JOIN returns rows from either table when the conditions are met and returns null value when there is no match.

outer-join

Example of Full Outer Join in SQL Server

SELECT * FROM Table1 t1
FULL OUTER JOIN Table2 t2
ON t1.ID = t2.ID

Output of Full Outer Join in SQL Server
FULL-OUTER-JOIN

Find child control of a control in WPF

Sometime we want to get reference of child control of container control of specified type. Example we want to find CheckBox control inside ListViewItem or we want to find another container Panel inside a Grid.

Here is a method through which we can find child control inside a parent control. The method below will return the first matching control of the specified type (T), if control of specified type is not found in the provided parent control it will return null.

/// <summary>
/// Method to get child control of specified type
/// </summary>
/// <typeparam name="T">Type of child control queried</typeparam>
/// <param name="parent">Reference of parent control in which child control resides</param>
/// <returns>Returns reference of child control of specified type (T) if found, otherwise it will return null.</returns>

private static T FindVisualChild<T>(DependencyObject parent)
where T : DependencyObject
{
     for (int i = 0; i < VisualTreeHelper.GetChildrenCount(parent); i++)
     {
          DependencyObject child = VisualTreeHelper.GetChild(parent, i);

          if (child != null && child is T)
               return (T)child;
          else
          {
               T childOfChild = FindVisualChild<T>(child);
               if (childOfChild != null)
                    return childOfChild;
          }
     }
return null;
}

To find parent control of a control in WPF, refer to Getting Parent Control of a Contol in WPF through Code or at Run-time.

Hope you will find it helpful, if you have any doubt please feel free to ask.

Getting parent control of a control in WPF through code or at runtime

I was working on a WPF project where I came across a problem where I need to refer to the parent container/control of specific type of control/item in WPF. So I did some RnD and search on the net for the problem and finally I came across a solution which I want to share.

Suppose we want to get ListViewItem on which a Button control is placed or we want to get the reference of that ListView in WPF.

Here is a method through which we can find parent of a control.

/// <summary>
/// Finds a parent of a given control/item on the visual tree.
/// </summary>
/// <typeparam name="T">Type of Parent</typeparam>
/// <param name="child">Child whose parent is queried</param>
/// <returns>Returns the first parent item that matched the type (T), if no match found then it will return null</returns>

public static T TryFindParent<T>(this DependencyObject child)
where T : DependencyObject
{
     DependencyObject parentObject = VisualTreeHelper.GetParent(child);
     if (parentObject == null) return null;

     T parent = parentObject as T;
     if (parent != null)
          return parent;
     else
          return TryFindParent<T>(parentObject);
}

To find child control of a control, refer to Find Child Control of a Control in WPF.

Hope you will find this blog helpful.

How to create Windows 7 Bootable USB Flash Drive (Pen Drive)

There are situations when we want to have a bootable Windows 7 USB Flash Drive/ Pen Drive. The reason could be any, may be the system on which you have to install Windows 7 does not have a DVD Rom or you don’t want to burn ISO of Windows 7 to a DVD Rom but the main point is how you can create a bootable Windows 7 USB Flash Drive, so that you can install Windows 7 through that USB Drive.

As I also had the need of bootable Windows 7 USB Flash Drive / Pen Drive I searched on the net and found many solutions. There are many tools available in the market with which we create Bootable Windows 7 USB Flash Drive but I preferred to use the tool provided by Microsoft to create the bootable flash drive.

As I don’t want others wasting time on internet and searching for the best tool to create a Windows 7 USB Flash Drive, I am going to share the method of how to create a bootable flash drive and the link from where you can download the Microsoft tool to create a bootable USB Flash Drive.
Windows 7 USB/DVD Download Tool

When you choose to download most software from the Microsoft Store, you have the option of using Download Manager or downloading from your browser. However, when you purchase Microsoft Windows 7 from Microsoft Store, you have an additional choice to make: whether to download a collection of compressed files or to download an ISO file. An ISO file combines all the Windows 7 installation files into a single uncompressed file.

If you choose to download an ISO file (so that you can create a bootable file from a DVD or USB flash drive), copy your Windows 7 ISO file onto your media and, run the Windows 7 USB/DVD Download Tool. After you do this, you can install Windows 7 onto your computer directly from the USB flash drive or DVD.

Here is the link to download Windows 7 USB/DVD Download Tool

After downloading the tool install it on your system and execute it. After executing the software you will see the following following screen.

Untitled

Browse the ISO of Windows 7 from your Hard Drive or from any media connected to your system, containing the ISO, then click on next to move to next step. In next step you will be asked to choose the media where you want to burn the ISO, choose USB Drive.

Choose-media

After choosing USB Drive you will be forwarded to step 3  where you have point to the USB Drive which you have to make bootable.

USB

In this step all you have to do is make sure that you are choosing the correct USB device. If you have other data on the device, move it to your hard drive, another USB device, or somewhere else before proceeding otherwise all your data will be lost. Now click on Begin Copying, after clicking you will be prompted to format the USB Drive, if it founds data on your drive, in order to create a bootable Windows 7 USB Flash Drive.

Erase

Choose to Erase USB Drive, it will again prompt you to ask for your confirmation to erase all your data. If you have backed up all your data then give it a positive reply to erase the data.

Format

The format will be very quick, while the copying of the files will take a little bit more time (about 10 to 15 minutes). Once the process is complete, you will get the confirmation message “Backup Completed”. At this point you can close the tool and use the USB drive to install Windows 7.

Finish

Note: You will have to choose USB as your first boot device in you bios setting in order to install Windows 7 from Bootable Windows 7 USB Flash Drive / Pen Drive created above.

Hope this will help you, if you find and mistake and if you have any suggestion please post your comments. Your comments are valuable.