-->

Friday, January 31, 2014

How to Querying Data using Joins in Sql Programming: Part 4

Equi Join

In SQL programming, an Equi join is the same as an inner join and joins tables with the help of a foreign key. However, an equi join is used to display all the columns from both the tables. The common column from all the joining tables is displayed.

Consider an example where you apply an equi join between the EmployeeDepartmentHistory, Employee, and Department tables by using a common column, BusinessEntityID. To perform this task, you can use the following query:

SELECT * FROM HumanResources.EmployeeDepartmentHistory d
JOIN HumanResources.Employee e ON d.BusinessEntityID = e.BusinessEntityID
JOIN HumanResources.Department p ON p.DepartmentID = d.DepartmentID

The output of this query displays the EmployeeID column from all the tables, as shown in the following figure.

Equi join sql programming

Self Join

In a self-join, a table is joined with itself. As a result, one row in a table correlates with other rows in the same table. In a self-join, a table name is used twice in the query. Therefore, to differentiate the two instances of a single table, the table is given two alias names.

The following example performs a self-join of the Sales.SalesPerson table to produce a list of all the territories and the sales people working in them.

SELECT st.Name AS TerritoryName, sp.BusinessEntityID, sp.SalesQuota, sp.SalesYTD
FROM Sales.SalesPerson AS sp JOIN Sales.SalesTerritory AS st
ON sp.TerritoryID = st.TerritoryID
ORDER BY st.Name, sp.BusinessEntityID

The output of the self-join is shown in the following figure.

Self join sql programming


How to Querying Data using Joins in Sql Programming: Part 3

A cross join, also known as Cartesian Product in SQL programming, between two tables joins each row from one table with each row of the other table. The number of rows in the result set is the number of rows in the first table multiplied by the number of rows in the second table.

This implies that if Table A has 10 rows and Table B has 5 rows, then all 10 rows of Table A are joined with all 5 rows of Table B. therefore, the result set will contain 50 rows.

Consider following query in which all the BusinessEntityID is retrieved from SalesPerson table and SalesTerritory with their cross join. The where condition will filter the result according to the territoryID, and at the last the result will sort by the resulting BusinessEntityID.

SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID

The preceding query combines the records of both the tables to display the result with all the possible combinations, as shown in the following figure.

How to Querying Data using Cross Joins in Sql Programming


Item selection required for ListBox in ASP.NET

If you want to do it in asp.net that your website visitor select any one option in given ListBox item. Use Required Field Validator for this type of problem, also set initial value. If your initial value is match with your selected item, error message generated on client machine. lets do it in asp.net with sort code example

Selection is required 

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <strong>Selection Required<br />
        </strong>
    </div>
        <asp:ListBox ID="ListBox1" runat="server" Height="132px" Width="147px">
            <asp:ListItem>Select Control</asp:ListItem>
            <asp:ListItem>Button Control</asp:ListItem>
            <asp:ListItem>Calendar Control</asp:ListItem>
            <asp:ListItem>CheckBox Control</asp:ListItem>
            <asp:ListItem>FileUpload Control</asp:ListItem>
    </asp:ListBox>
    &nbsp;<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
        ControlToValidate="ListBox1" ForeColor="Red" InitialValue="Select Control">Please Select Control</asp:RequiredFieldValidator>
    <br />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Submit" />
    </form>
</body>
</html>

Code Generate the following output

Item selection required for ListBox in ASP.NET

Bind DetailsView with insert, edit and delete in ASP.NET

You can easily bind your DetailsView data control in asp.net using SqlDataSource, SqlDataSource provides connectivity between front end to back-end with all insert, edit and delete features. This example covers that how to bind your DetailsView with database. These are following steps

Step-1 : Create a SQL Table with some fields like
sno int (primaryKey, Isidentity=true)
name nvarchar(50)
address nvarchar(250)


Step-2 : Add SqlDataSource Control to Design window from toolbox
Step-3 : Select 'Configure Data Source' link using show Smart tag.

'Configure Data Source'



Step-4 : Select Database or ConnectionString from Dropdown menu.
ConnectionString

Step-5 : Select Table-name from Dropdown menu also select Advanced tab.
Table-name from Dropdown menuAdvanced tab

Step-6 : Select Insert, Update and delete checkbox option.
Step-7 : Click to Test Query and  Finish button.

Step-8 : Add DetailsView Control to the page, check all insert, update, and delete features using show smart teg.
Bind DetailsView with insert, edit and delete in ASP.NET

Now generate source code in page file

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default5.aspx.cs" Inherits="Default5" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            DeleteCommand="DELETE FROM [emp] WHERE [sno] = @sno" 
            InsertCommand="INSERT INTO [emp] ([name], [address]) VALUES (@name, @address)" 
            SelectCommand="SELECT * FROM [emp]" 
            UpdateCommand="UPDATE [emp] SET [name] = @name, [address] = @address WHERE [sno] = @sno">
            <DeleteParameters>
                <asp:Parameter Name="sno" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="name" Type="String" />
                <asp:Parameter Name="address" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="name" Type="String" />
                <asp:Parameter Name="address" Type="String" />
                <asp:Parameter Name="sno" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
    
    </div>
    <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" 
        AutoGenerateRows="False" DataKeyNames="sno" DataSourceID="SqlDataSource1" 
        Height="50px" Width="125px">
        <Fields>
            <asp:BoundField DataField="sno" HeaderText="sno" InsertVisible="False" 
                ReadOnly="True" SortExpression="sno" />
            <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
            <asp:BoundField DataField="address" HeaderText="address" 
                SortExpression="address" />
            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" 
                ShowInsertButton="True" />
        </Fields>
    </asp:DetailsView>
    </form>
</body>
</html>

Code Generate the following output

Bind DetailsView with insert, edit and delete in ASP.NET

Accept only numbers by the TextBox in ASP.NET

If you want to do it in asp.net that your TextBox accept only number when user input in it. Use CompareField Validator for this type of problem, Normally we use CompareField Validator for password recheck. Today we use it for another purpose, now, take an example.

Accept Only integer type number by TextBox

Step-1 : Add webform into the project.
Step-2 : Take one TextBox and Button control onto the webform.
Step-3 : Also take one Required field and one compare field validator onto the web form.
Step-4 : Set Property of required field validator, these are

ControlToValidate="TextBox1"
Display="Dynamic"
ForeColor="#CC0000"
Text= "Required"

Step-5 : Set Property of CompareField Validator, these are

ControlToValidate="TextBox1"
 Display="Dynamic"
  ForeColor="#CC0000"
  Operator="DataTypeCheck"
   Type="Integer"
   Text= "Enter Only numbers"

Complete code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        Enter age :
        <asp:TextBox ID="TextBox1" runat="server" Width="172px"></asp:TextBox>
&nbsp;<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
            ControlToValidate="TextBox1" Display="Dynamic" ForeColor="#CC0000">Required</asp:RequiredFieldValidator>

        <asp:CompareValidator ID="CompareValidator1" runat="server" 
            ControlToValidate="TextBox1" Display="Dynamic" ForeColor="#CC0000" 
            Operator="DataTypeCheck" Type="Integer">Enter Only numbers</asp:CompareValidator>
        <br />
        <asp:Button ID="Button1" runat="server" Text="Submit" />
    
    </div>
    </form>
</body>
</html>

Code Generate the following Output

Accept only numbers by the TextBox in ASP.NET

Examples of some common Algorithms of DataStructure in C programming

1.Algorithm to find the average of  a subject marks of ‘N’ number of students.
             AVG_OF_MARKS (LIST, N)
            [LIST is an array containing marks, N is the size of array]
            SUM 0
            Repeat For I=1,2,3, …N
               SUM  SUM  + LIST[i]
            [End of For I]
            AVG SUM/N
            Write: ‘The average is’,AVG
            Exit.

2.Algorithm to find average of SALARY in an array of EMP struct containing information EMPNO, EMPNAME, and SALARY.
        AVG_SALARY (LIST, SIZE)
        [LIST is an array of EMP, SIZE is size of array]
        SUM 0
        Repeat For I=1,2,3, …SIZE
          SUM SUM  + LIST[I].SALARY
       [End of For I]
       AVG  SUM / SIZE
       Write: ‘The average Salary is’,AVG
       Exit.
An algorithm, that performs sub task, may be called in another algorithm. It is better practice to divide the given problem into sub problems and write the individual algorithms to solve such sub problems and collectively write main algorithm to call the sub algorithms in order to solve the main problem.

3.Algorithm to find maximum of two unequal numbers and use the same to find maximum of four unequal numbers.
          MAX_OF_2(NUM1, NUM2)
          [NUM1 and NUM2 are two numbers]
          If NUM1 > NUM2 Then:
                       Return NUM1
          Else:
                        Return NUM2
          [End of If]
          Exit:
          MAX_OF_4(NUM1, NUM2, NUM3, NUM4)
          [NUM1, NUM2, NUM3, and NUM4 are numbers]
          TEMP1 MAX_OF_2(NUM1, NUM2)
           TEMP2  MAX_OF_2(NUM3, NUM4)
            MAX MAX_OF_2(TEMP1, TEMP2)
            Return MAX
            Exit.

4.Algorithm to find minimum of two distinct numbers and using the same to find minimum of three distinct numbers.
            MIN_OF_2(NUM1, NUM2)
            [NUM1 and NUM2 are two number]
             If NUM1<NUM2 Then:
                 Return NUM1
             Else:
                Return NUM2
               [End of If]
               Exit.
               MIN_OF_3(NUM1, NUM2, NUM3)
               [NUM1, NUM2, and NUM3 are distinct number]
                   Return MIN_OF_2(MIN_OF_2(NUM1, NUM2), NUM3)
                Exit.

5.Algorithm to find the GCD (Greatest Common Divisor)
of two positive numbers and use the same to find LCM of two positive numbers.
GCD(NUM1, NUM2)
Rem    NUM1%NUM2 [%Modulus Operator]
Repeat While Rem<>0
  NUM1 <--- NUM2
   NUM2<--- Rem
   Rem <-- NUM1% NUM2
 [End of While]
Return NUM2
Exit. 
LCM(NUM1,NUM2)
  Return(NUM1*NUM2) / GCD (NUM1,NUM2)
Eixt.
Suppose that the LCM algorithm is called using 12 and 16.
The algorithm calls GCD with 12 and 16.

Working of GCD algorithm:
Rem12%16       i.e.  Rem =12
Rem is not equal to Zero. So, NUM1=16 and NUM2=12
Rem16%12       i.e. Rem=4
Rem is not equal to Zero. So, NUM1=12 and NUM2=4
Rem12%4          i.e. Rem=0
Rem is equal to Zero. So, GCD algorithm returns NUM2 that is 4.
When the algorithm LCM gets the result from GCD algorithm it finds the expression value (NUM1 * NUM2) /GCD(NUM1,NUM2).i.e.(12*16) / 4. Which is equal to 48.48 is the LCM of 12 and 16.
Here in this example algorithm, finding LCM is the main problem that can be divided into sub problem like finding the GCD first and using the same to solve the main problem. So, GCD algorithm is written first and in the order algorithm that can be treated as a main algorithm. In this way a given problem can be divided into small problems and algorithms can be written to solve such small problems.

6. Algorithm to find sum of ‘N’ natural numbers between 
N1 and N2.
SUM1(N1,N2)
[N1 and N2 are two different natural numbers]
If N1> N2 Then:
   MAXN1
    MINN2
Else:
    MAXN2
    MINN1
[End of ]
MIN  MIN – 1[to include MIN in sum]
SUM1 (MIN*(MIN +1))/2
SUM2(MAX*(MAX +1))/2
Return SUM2-SUM1
Exit.
In the above algorithm to find the sum of N natural the formula N(N+1)/2 is used. Instead of formula a repetitive statement can also be used that run from MIN to MAX with setp 1. The algorithm can be re-written as:

SUM2(N1,N2)
[N1 and N2 are two different natural numbers]
If N1>N2 Then:
   MAX N2
   MIN N2
Else:
   MAX N2
   MINN1
[End of If]
SUM 0
Repeat For I= MIN, MIN+1, MIN+2 …MAX
    SUMSUM +1 
[End of For]
Return SUM
Exit.

Dry Run:
Suppose that the above algorithm SUM 1 is called with two number 23 and 11. In the If statement 23>11 condition is TRUE, therefore MAX becomes 23 and MIN becomes 11. Now the loop repeats for 11, 12, 13, 14 up to 23 (I values).Every time when the loop is repeated  I  is added to SUM. So, finally the value of SUM is returned from the algorithm. 

Insert element in Array in PHP Programming

Insert element in PHP array:- 
                                                                             If we wish to insert a new item or element in PHP array then we have three choices first is insert the element  from fist position , second is insert the element  at last position and third is insert the element at any desire position in an array. Here following code is showing that how to implement above three operations.



Insert the element from starting of array: - We can do this we use PHP array_unshift() function. Syntax of this function is follows:-
              Int  array_unshift(array array, mixed variable [, mixed variable...])

So we can use any element to the array which is passed in this function  this can be understand with the help of following code.
:
$names = array("jack”,“jon");
array_unshift($states,"weilems","reacher");
after this statement the array name  becomes
$names = array("jon","jacob","rhett","weilems");

So names jack and jon added to the $names array from its starting index and value of index replace by 2 because two new element we added. The same function can be applied with associative array but the key name does not changed.


Insert the element  at last position:- This can be possible in PHP array by using array_push() method. The syntax of this array is
int array_push(array array, mixed variable [, mixed variable...])
So the element is added to the given array and return true if success and return false if failed. This can be understood with the help of following code.

$names = array("weilems"," reacher");
array_push($states," jon "," jack ");
after this statement the array name  becomes
$names = array("jon","jacob","rhett","weilems");

So names weilems and reacher added to the $names array from its end index and value of index increased by 2 because two new element we added. The same function can be applied with associative array.


Insert the element at any desire position:- This is possible if we have sufficient index is available. This can be possible by using direct assignment for example if we want to add a value at fourth index of $name array the we use following statements.

$name [4] =” weilems”;

If the 4th index is available and free then name “weilems” will be add to this index. Or it replaces the value which is already stored at this index.
© Copyright 2013 Computer Programming | All Right Reserved