SQL Queries

In this section I have discussed about more queries which all useful to design a database. Structured Query Language is the expansion for SQL, a Microsoft Technology.

Join Queries and its Methods

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

INNER JOIN	SELECT column_name(s) FROM table_name1 INNER JOIN table_name2  ON table_name1.column_name=table_name2.column_name
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2  ON table_name1.column_name=table_name2.column_name
RIGHT JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2  ON table_name1.column_name=table_name2.column_name
FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2  ON table_name1.column_name=table_name2.column_name

Below are two types of queries to retrieve the count of columns in a table.

 Note: here bluesky is database name and reports1 is tablename, change it accordingly.

Example-1: 

SELECT COUNT(*) FROM bluesky.sys.columns WHERE object_id = OBJECT_ID('bluesky.dbo.reports1')

Example-2:

select count(*) from bluesky.information_schema.columns where table_name = 'reports1'

Simple Stored Procedure to insert

ALTER PROCEDURE dbo.spCustomerEntry
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@cid VARCHAR(255),
@cname varchar(255),
@cfname varchar(255),
@caddr varchar(255),
@cpost nchar(10),
@cdist varchar(255),
@cpno nchar(15),
@cproof varchar(255)

AS
/* SET NOCOUNT ON */

begin
insert into CustomerEntry
(
cusid,cusname,cfname,caddr,cpost,cdist,cpno,cproof

)
values(
@cid,@cname,@cfname,@caddr,@cpost,@cdist,@cpno,@cproof

)

end
RETURN

Simple Stored Procedure to Update

ALTER PROCEDURE dbo.spUpdCustomerEntry
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/

@cid VARCHAR(255),
@cname varchar(255),
@cfname varchar(255),
@caddr varchar(255),
@cpost nchar(10),
@cdist varchar(255),
@cpno nchar(15),
@cproof varchar(255)

AS
/* SET NOCOUNT ON */
begin

update CustomerEntry set cusname=@cname, cfname=@cfname,
caddr=@caddr,cpost=@cpost,cdist=@cdist,cpno=@cpno,cproof=@cproof where cusid=@cid
end
RETURN

Simple Stored Procedure to Delete

ALTER PROCEDURE dbo.spDelCustomerEntry
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
@cid VARCHAR(255)
AS
/* SET NOCOUNT ON */

begin
delete from CustomerEntry where cusid=@cid
end
RETURN


Query for fetching Distinct values from Two Tables

• 
  

  
  

  
  

  select filename from sat_test where filename not in

(select filename from sat_test group by filename having count(filename)>1);

• 
  

  
  

  SELECT * FROM com WHERE NOT EXISTS (SELECT * FROM com1 WHERE com.FileName = com1.FileName

change the table name according to your project here com,com1 are tables. Filename is a column


Example tried in window application, it should come in a button_click
{

string qry2 = "select distinct(FileName) from com where date1 between '" + dateTimePicker1.Text + "' and '" + dateTimePicker2.Text + "'";
           {
            //dtime();
            SqlDataAdapter ad = new SqlDataAdapter(qry2, cn);
            DataTable tb1 = new DataTable();
            cn.Open();
            ad.Fill(tb1);
            dataGridView2.DataSource = tb1;
            cn.Close();
          }
}