Database

WHERE NumColumn BETWEEN 5 AND 10

WHERE NumColumn >= 5 
AND NumColumn <= 10

the number of columns appears in the corresponding SELECT statements must be equal.
The columns appear in  the corresponding positions of each SELECT statement must have the same data type or at least convertible data type.

Select T1CL1,T1CL2 from T1 where T1CL3 = 'Something'
union all
Select T2CL1,T2CL3 from T2 where T1CL2 = 'prm1'
union all
Select T3CL3,T3CL4 from T3 where T3CL2 = 'prm2'
union all
Select T4CL1,T4CL3 from T4 where T4CL2 = 'prm3'
union all
Select T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'

// This needs MARS:
using (var connection = new SqlConnection("..."))
using (var outerCommand = new SqlCommand("...", connection))
{
    connection.Open();
    using (IDataReader outerReader = outerCommand.ExecuteReader())
    {
        while (outerReader.Read())
        {
            using (var innerCommand = new SqlCommand("...", connection))
            {
                using (IDataReader innerReader = innerCommand.ExecuteReader())
                {
                    while (innerReader.Read())
                    {
                        ...
                    }
                }
            }
        }
    }
}

// This doesn't:
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    connection.Open();
    using (IDataReader reader = command.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                ...
            }
        } 
        while (reader.NextResult())
    }
}

// Neither does this:
var dataSet = new DataSet();

using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    var adapter = new SqlDataAdapter(command);
    adapter.Fill(dataSet);
}

create table tqw1(t1c1 varchar2(10),t1c2 varchar2(10),t1c3 varchar2(10),t1c4 varchar2(10));
create table tqw2(t2c1 varchar2(10),t2c2 varchar2(10),t2c3 varchar2(10),t2c4 varchar2(10));
create table tqw3(t3c1 varchar2(10),t3c2 varchar2(10),t3c3 varchar2(10),t3c4 varchar2(10));
create table tqw4(t4c1 varchar2(10),t4c2 varchar2(10),t4c3 varchar2(10),t4c4 varchar2(10));

insert into tqw1(t1c1,t1c2,t1c3,t1c4) values (7,2,3,4);
insert into tqw2(t2c1,t2c2,t2c3,t2c4) values (8,3,4,5);
insert into tqw3(t3c1,t3c2,t3c3,t3c4) values (9,4,5,6);
insert into tqw4(t4c1,t4c2,t4c3,t4c4) values (10,5,6,7);

select Q.T1C1,W.T2C2,E.T3C3,R.T4C4 from tqw1 q,TQW2 w,TQW3 e, TQW4 r ;

KipkoechE wrote:

i wish to know how i can loop through the records of each student and insert into TotalScore column

UPDATE tablename SET TotalScore = (unit1 + unit2 + unit3 + unit4) // and so on?

KipkoechE wrote:

the number of columns can be changed at runtime