FAQ-- SQL(Query Only)

BACK

1.Query :get minimum salary along with name.
         Main Table: [test1]
    Name Marks
        A   50
        B   40
        C   30
        D   20
        E   20

        Output:
        name marks
        D   20
        E   20
        SqlDataAdapter adp = new SqlDataAdapter("SELECT name,marks FROM test1 where marks = (select min(marks) from test1) ", con);

        2. Query: Sum of the marks.output=160
        SqlDataAdapter adp = new SqlDataAdapter("select sum(marks) as SUM from test1 ", con);

        3. Query: Highest marks from table test1.output=50
        SqlDataAdapter adp = new SqlDataAdapter("select MAX(marks) from test1 ", con);

        4.Query: 2nd Highest marks from table test1.output=40
        SqlDataAdapter adp = new SqlDataAdapter("select MAX(marks) from test1 where marks <(select MAX(marks) from test1) ", con);
    Query: 3rd Highest marks from table test1.output=30
        SqlDataAdapter adp = new SqlDataAdapter("select MAX(marks) from test1 where marks <( select MAX(marks) from test1 where marks<(select MAX(marks) from test1) ) ", con);
    SqlDataAdapter adp = new SqlDataAdapter("select marks from test1 as t1 where 3=(select count(*) from test1 as t2 where t1.marks<=t2.marks) ", con);


        5.Query: Display Nth Raw data from table: suppose 7th row data i need(i.e 77 output)
        Table Name[test1]
        INV_no
          11      (1st)
          22      (2nd)
          33
          44
          55
          66
          77      (7th)
          88
          99

        SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM (SELECT row_number() OVER (ORDER BY INV_no ASC) AS row, * FROM test1) AS foo WHERE row = 7 ", con);
    Or,,
        SqlDataAdapter adp = new SqlDataAdapter("WITH Temp AS(SELECT ROW_NUMBER() OVER(ORDER BY INV_no ASC) AS RowNo, INV_no FROM test1 ) SELECT test1.* FROM test1 INNER JOIN Temp ON Temp.INV_no = test1.INV_no where RowNo='7'", con);

        6.Query:{Single Query}
Fetch data from a table-A and inserted into another table-B.Both table structure & type should be same.
SqlCommand cmd456 = new SqlCommand("insert into [xyz] select * from [abc] where ian_no= '335/20-21' ", con302);

        7.Query: TOP 5 Datas to be shown when data are in Assending order. (Output:     11,22,33,44,55)
        //SqlDataAdapter adp = new SqlDataAdapter("select Top 5 * From (Select Top (5) * from test1 Order By INV_no  ASC) AS foo Order By INV_no  ASC", con);

        8.A) Query: How to find ALL Non-Matching records from two tables in sql
        SqlDataAdapter adp = new SqlDataAdapter(" (select * from test1 except select * from test2)  union  (select * from test2 except select * from test1) ", con);
        8.B) Query:How to find ALL LeftTable(test1) Non-Matching records from two tables(test1, test2) in sql
         SqlDataAdapter adp = new SqlDataAdapter("select * from test1 where INV_no not in (select INV_no from test2) ", con);
        8.C) Query:How to find ALL RightTable(test2) Non-Matching records from two tables(test1, test2) in sql
         SqlDataAdapter adp = new SqlDataAdapter("select * from test2 where INV_no not in (select INV_no from test1) ", con); SQL Server-2016:

 

9.) Between Use:
  //SqlDataAdapter adp = new SqlDataAdapter("select a.number from [test_2] as a where a.number BETWEEN 100 and 400", con); 

10) All datas fetching from [i_pending_rl] and inserting into table [deleted_i_pending_rl]  where [condition].
Both table structure must be same.

SqlCommand cmd5121 = new SqlCommand("insert into [deleted_i_pending_rl] select * from i_pending_rl where order_no = '" + DropDownList1.Text + "'", con);
                                cmd5121.ExecuteNonQuery();