dayalDoc

select custNo,Name,City from customer where not exists (select * from orders where customer.custNo = orders.custno)

select a.city ,count(*) as totalCity from customer a where a.city in ('colombo','kandy')and a.gender ='male' group by a.city

FUNCTIONS

use customerdata
select left(Ltrim(name),1)+'.  '+ ltrim(lname) as Name_with_Initial from customer


use customerdata
select substring(ltrim(name),1,3) from customer

use customerdata
select len(ltrim(name)) from customer

use customerdata
select reverse(ltrim(name)) from customer

use customerdata
select lower(ltrim(name)) from customer

use customerdata
select upper(ltrim(name)) from customer


Numeric Functions

use customerdata
select unitprice, round(unitprice,0)as roundPrice from orderdetails

use customerdata
select unitprice, square(unitprice)as roundPrice from orderdetails

use customerdata
select unitprice, sqrt(81)as roundPrice from orderdetails

use customerdata
select unitprice, rand() from orderdetails

DATE  FUNCTIONS

use customerdata
select Name, dob, year(dob),getdate(), year(getdate()) - year(DOB)as age  from customer

select Name, dob, year(dob),datename(month,dob),getdate(), year(getdate()) - year(DOB)as age  from customer





public SqlDataReader GetData(string queryString, string connectionString)
    {

        SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(queryString, connection);
            SqlDataReader reader = command.ExecuteReader();
            SqlDataReader sqlDataReader = reader;
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Customer Id");
            dataTable.Columns.Add("Customer Name");
            while (sqlDataReader.Read())
            {
                DataRow row = dataTable.NewRow();
                row["Customer Id"] = sqlDataReader["CustomerId"];
                row["Customer Name"] = sqlDataReader["CustomerName"];
                dataTable.Rows.Add(row);
            }
            GridView1.DataSource = dataTable;
            GridView1.DataBind();
            connection.Close();
        return reader;
    }