How to Retrieve Records that Matches a Pattern: SQL Programming

When retrieving data in sql programming, you can view selected rows that match a specific pattern. For example, to create a report that displays all the product names of Adventure Works beginning with the letter P. This task can be done through the LIKE keyword.

The LIKE keyword is used to search a string by using wildcards. Wildcards are special characters, such as * and %. These characters are used to match patterns and some of them are described with example, mostly used by sql server:

• %    Represents any string of zero or more character(s)
• _    Represents a single character
• []    Represents any single character within the special range
• [^]    Represents any single character not within the specified range

The LIKE keyword matches the given character string with the specified pattern. The pattern can include combination of wildcard characters and regular characters. While performing a pattern match, regular characters must match the characters specified in the character string. However, wildcard characters are matched with fragments of the character string.

The following SQL query retrieves records from the Department table where the values of Name column begin with ‘Pro’. You need to use the ‘%’ wildcard character for this query.

SELECT * FROM HumanResources.Department WHERE Name LIKE 'Pro%'

Output: Shows all the records satisfy the given condition i.e. starting with Pro

The following SQL query retrieves the rows from the Department table in which the department name is five characters long and begins with ‘Sale’, whereas the fifth character can be anything. For this, you need to use the '_wildcard character.

SELECT * FROM HumanResources.Department WHERE Name LIKE 'Sale_'

Output: Shows all the records satisfy the given condition i.e. starting with having sale with single character.

While Loop with Example in C language

A set of statements may have to be repeatedly executed till a certain condition is reached, in every computer programming. In such situations (C language), we do not know exactly how many times a set of statements have to be repeated. So, naturally we need to have a condition controlled loop like in looping statements. In C language, the condition controlling is done generally by a while statement or while loop. It is also called test and do structure.

Syntax
while (exp)
{
  statement1;
  statement2;
  .
  .
  .
statementn;
}
Where

• while -  is a reserve word or keyword
• exp – is the expression which is evaluated to TRUE or FALSE.

Working of the while loop: 

The following sequences are carried out after executing the statements that appear just before the while loop

• If the expression exp is evaluated to FALSE at the time of entry into the loop, the control comes out of the loop without executing the body of the loop. Later, the statements after the while loop are executed.
• If the expression exp is evaluated to TRUE, the body of the loop is executed. After executing the body of the loop, control goes back to the beginning of the while loop and exp is again checked for TRUE or FALSE.
• Thus, the body of the while loop is repeatedly executed as long as exp is evaluated TRUE. Once the expression exp is evaluated FALSE, the control comes out of the while loop and the statements which after the while loops are executed.

As the expression exp is evaluated to TRUE or FALSE in the beginning of the while loop, the loop is also called entry controlled or top testing loop.

If a relational expression is evaluated to TRUE, the result is 1. If a relational expression is evaluated to FALSE, the result is 0. If an arithmetic/logical/bitwise or any other expression is evaluated to non-zero value (for example 10, 999, etc) then the condition is TRUE. After evaluation, if the result is zero (0), then the condition is FALSE.

Some special forms of while loop are shown in table below:

Algorithm: to reverse a given number

Step1:    [Input the number]
    Read : N
Step2:    [Initialize reverse number rev to 0]
    Rev = 0
Step3:    [Separate and reverse the number]
    While N !=0
        digit = N%10
        N = N/10
        Rev = rev*10+digit
    [End of while]
Step4:    [Output the reversed number]
    Write: rev
Step5:    Exit

Let N is 123, which is the give number to be reversed. In step1, N will be 12. In step2, N will be 1 and finally in step3, N is zero after separating a digit each time. So, the above three statements should be executed as long as N is not zero. Also, in step1, note that initial value of reverse is 0 and at the end of final step, reverse is 321.

Step by step Analysis
Step1:   
    Obtain 1st digit     =    123%10     = 3
    Reverse         =    0*10+3      = 3
    Separate 1st digit    =    123/10      = 12
Step2:   
    Obtain 2nd digit     =    12%10         = 2
    Reverse         =    3*10+2      = 32
    Separate 1st digit    =    12/10          = 1
Step3:   
    Obtain 3rd digit     =    1%10         = 0
    Reverse         =    32*10+1      = 321
    Separate 3rd digit    =    1/10          = 0

C Program
main()
{
int n,revnum=0,digit;
printf("Enter any integer number here:\n");
scanf("%d",&n);
while(n!=0)
{
digit=n%10;
revnum=revnum*10+digit;
   n=n/10;
}
printf("The reverse number of %d is %d",n,revnum);
}

The output is exactly the reverse no of input by the user.

Do-While loop in C

Computer Programming : Nullable types, Null coalescing operator in c# Programming

In Computer Programming, C# Data types are divided into two broad categories such as Value types and Reference type. In Value types, some inbuilt types such as int, float, double, structs, enum etc. These all are non-nullable types, it means these types don't hold null value. In other hand Reference types such as string, Interface, Class, delegates, arrays etc. can hold null value.

By default value types are non nullable. To make them nullable use, programmer have to use ?

• int a=0   a is non nullable, so a cannot be set to null, a=null will generate compiler error
• int ?b=0  b is nullable int, so b=null is legal

Operator Need

If programmer want to assign null value to non-nullable data types then there are some operators explained with an example. 

Lets take an simple example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            int a;
            int? b = 100;
            a = b ?? 0;
            System.Console.WriteLine(a);
            Console.ReadKey();

        }
    }
}

Output show that if b hold some value like 100 then Null coalescing operator assign value to variable (a). If b hold null value then Null coalescing operator assign default value, which is zero to variable a. 

Replace the declaration of variable b with the following line:

            int? b = Null;

This will outputs zero (default value).

How to Retrieve Records Containing any value from Set of Values: SQL Programming

List operators, in context of SQL programming, are used to compare a value to a list of literal values that have been specified. When field name must contain one of the values returned by an expression for inclusion in the query result, list operators comes in to existence.

Sometimes, you might want to retrieve data either within a given range or after specifying a set of values to check whether the specified value matches any data of the table. This type of operation is performed by using the IN and NOT IN keywords. The syntax of using the IN and NOT IN operators in the SELECT statement is:

SELECT column_list
FROM table_name
WHERE expression list_operator ('value_list')

Where

• column_list: list of fields to be shown in output.
• table_name: from the records are to be retrieved.
• expression is any valid combination of constants, variables, functions, or column-based expressions.
• list_operator is any valid list operator, IN or NOT IN.
• value_list is the list of values to be included or excluded in the condition.

IN keyword selects values that match any one of the values in a list. The following SQL query retrieves records of employees who are Recruiters or Stockers from the Employee table:

SELECT BusinessEntityID, JobTitle, LoginID FROM HumanResources.Employee WHERE JobTitle IN ('Recruiter', 'Stocker')

NOT IN keyword restricts the selection of values that match any one of the values in a list. The following SQL query retrieves records of employees whose designation is not Recruiter or Stocker:

SELECT BusinessEntityID, JobTitle, LoginID FROM HumanResources.Employee WHERE JobTitle Not IN ('Recruiter', 'Stocker')

Retrieve records matches condition
Retrieve records within given range

How to Retrieve Records that contain values in Given Range: SQL Programming

Criteria is similar to a formula, a string that may consist of field references, operators and constants. We can tagged it an expression in sql programming. Sometimes this criteria have to be in range of values to let the programmer can select records within a range.

The article describes about these range operator that can access records within a given range e.g. list of students between ages 18yr to 25yr, list of employee having salary 10k to 20k. The inputted value may be a number, text or even dates. Mostly these range operator allows you to define a predicate in the form of a scope, if a column value falls in the specified scope, it got selected.

The Range operator retrieves data based on a range. The syntax for using the range operator in the SELECT statement is:

SELECT column_list
FROM table_name
WHERE expression1 range_operator expression2 AND expression3

Where

• Column_list: list of fields to be shown in output.
• Table_name: from the records are to be retrieved.
• expression1, expression2, and expression3 are any valid combination of constants, variables, functions, or column-based expressions.
• Range_operator is any valid range operator.

Here’re the range operators used in Sql programming

BETWEEN: Is used to specify a test range. It gives records specifying the given condition via sql query.
The following SQL query retrieves records from the Employee table when the vacation hour is between 20 and 50:

SELECT BusinessEntityID, VacationHours FROM HumanResources.Employee WHERE VacationHours BETWEEN 20 AND 50

NOT BETWEEN: Is used to specify the test range to which he values in the search result do not belong.
The following SQL query retrieves records from the Employee table when the vacation hour is not between 40 and 50:

SELECT BusinessEntityID, VacationHours FROM HumanResources.Employee WHERE VacationHours Not BETWEEN 20 AND 50

Retrieve records matches condition

Role of Basic Graphical Controls of Programming: Introduction to Java

The Palette tab of a Graphical controls offered by Java Swing contains the tools that you can use to draw controls on your forms/windows/ frames while programming. The area on the frame where GUI components are placed is called content pane.

In earlier article, we have discussed about what are components. You have seen many controls listed on the Swing control palette. Here’s a list with briefly description about role and purpose of some commonly used controls.

• JFrame provides the basic attributes and behavior of a window. A frame is displayed as a separate window with its own tittle bar.
• JLable allows un-editable text (i.e., that user cannot change) or icons to be displayed.
• JTextField allows user input, can also be used to be display text. As it can be edited you can also call it edit field.
• JButton provides button capabilities. An action Event is generated when button is pushed.
• JCheckBox provides check box. Checkboxes are used to allow a user select multiple choices, e.g., a student can select 5 subject out of 17 subject-choices. A check boxes shows an X in box in front of it when selected.
• JList is a list of items from which a selection can be made. From a list, multiple elements can be selected.
• JComboBox provides a drop-down list of items from which a selection can be made or new items can be added. It is a combination of text field and list.
  Note: The area on the frame where GUI components are placed is called content pane.
• JPanel is a supporting container that cannot be displayed on its own but must be added to another container. Panels can be added to the content pane of a frame. They help to organize the components in a GUI.
• JRadioButton provides radio buttons. Radio buttons are option buttons that can be turned on or off. The radio buttons provide mutually exclusive options.

Note: Though radio buttons and checkboxes appear to function similarly, yet there is an important difference-when a radio button is selected, all other radio buttons in its same group are automatically unselected. On the other hand, any no. of checkboxes can be selected.

Computer Programming: Operators in c# console application

Introduction

It is a symbol, which is performed operation on operands, operation can be mathematical or logical. In c#, operators are categories in three basic parts first one is unary operator and second one is binary operator and last one is ternary operator. In unary operator we use single operand like.
++a // here a is a "operand"
In Binary operator we use two operand. Like
a+b;
In ternary operator we use three operand like
a>b?a:b

Assignment Operator (=)

if we create a variable of type integer also we want to initialize it with some value then we should use assignment operator like.

int a=10;

Here, variable a is initialized with value 10. It means you can say the right hand side value is copy to left hand side variable using assignment operator.

Arithmetic Operators ( +,-,*,/,%)

Basically arithmetic operators are used for doing mathematical calculation like addition of two or more numbers , multiplication, division, subtraction, and remainder. lets take a simple example to understand arithmetic operators. If the total collection by five students is five thousand then find the average value?.

int collected_money=5000;

int total_student =5;

int avg=collected_money/total_student;

Ternary operator (?, : )

In ternary operator we have three operands, in which first operand is used for check the condition and other two operands is used for giving results. Let’s take a simple example to understand the ternary operator.

Int firstnumber=10;
Bool result;
Result = firstnumber>10? True: false;

In the preceding example, if condition becomes true or you can say if firstnumber is greater than to 10 then ternary operator gives  ‘true’ in results otherwise gives ‘false’.

 

Comparison Operators (< , > ,<=,>=,!=,==)

using comparison operator you can perform Boolean operation between operands known as comparison operator. Lets take an simple to understand comparison operator

 

 class Program
    {
        static void Main(string[] args)
        {
            int a = 10;
            if (a==10)
            {
                Console.WriteLine(string.Format("The value of variable ={0}", a));
  
            }
            Console .ReadKey (false);
        }
    }
 

Here, condition if check equality operation between operands, a and constant value 10. If condition becomes true, output comes "The value of variable=10".


 

  

  Logical operator (&&,|| )

Perform logical operation between Boolean operands known as logical operator. Lets take a simple example

 

  class Program
    {
        static void Main(string[] args)
        {
            bool a = true;
            bool b = true;

            if (a&&b)
            {
                Console.WriteLine("condition becomes true");
  
            }
            Console .ReadKey (false);
        }
    }