TechSapphire Sunday, 2019-01-20, 9:57 AM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Main » Database
    This lecture is on complex query SQL server, topic covered are:
    • OUTPUT Clause
    • RANK(), ROW_NUMBER() and DENSE_RANK() Function
    • Query Optimization and Use of execution plan.



    Click Here to see full article and download sql script
    Category: Database | Views: 11620 | Added by: Admin | Date: 2013-11-18 | Comments (0)

    Read complete article



    ----Basic but important functions
    --ROW_NUMBER()
    --RANK()
    --DENSE_RANK()
    --NTILE()

    --ROW_NUMBER() --Returns a sequential row number with in partition of result set
    use Northwind;
    SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products;

    ----Getting alternate rows
    ------Odd Row
    select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=1;
    ------Even Row
    select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=0;
    ------Row Between
    select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]
    Between 5 and ... Read more »
    Category: Database | Views: 16451 | Added by: Admin | Date: 2012-10-26 | Comments (1)

    Union is to select all records from two table but common record only once.

    Select * from tbemp1
    UNION
    Select * from tbemp2


    Inersect is to select common records from two tables.

    Select * from tbemp1
    INTERSECT
    Select * from tbemp2

    Except(Minus) is equivalent to oracle minus clause. Select records of table 1 which are not in table 2.

    Select * from tbemp1
    EXCEPT
    Select * from tbemp2
    Category: Database | Views: 18417 | Added by: Admin | Date: 2012-09-04 | Comments (0)

    Get duplicate records from table
    Select ename from tbemp a where a.eno!=(select Max(eno) from tbemp b where a.ename=b.ename)

    Here eno denotes to employee number and ename denotes to employee name.
    Category: Database | Views: 1461 | Added by: Admin | Date: 2012-09-03 | Comments (0)

    Without Data
    Select * INTO tbem1 from tbemp where 1=2

    Why 1=2?
    Because none of the record could satisfy this condition i.e. 1=2. So no data would be copied into newly created table.

    With Data
    Select * INTO tbemp2 from tbemp

    With Condition
    Select * INTO tbemp3 from tbemp where depcod=102

    Here depcod denotes to department code.


    Category: Database | Views: 1521 | Added by: Admin | Date: 2012-09-03 | Comments (0)

    Select Alternate Row SQL Server

    Their are many approaches to get alternate record from SQL server database table. Here i have discussed 2 approaches
    1. Complex Query
    2. Using Views

    Complex Query: SELECT * from (SELECT ROW_NUMBER() OVER(ORDER BY Employee_id) as RowNumber, * from employees) T where t.RowNumber%2=0

    Using Views:
    CREATE VIEW with_rownumber
    as
    SELECT ROW_NUMBER() OVER(ORDER BY Employee_id) as RowNumber,* from employees;

    SELECT * from with_rownumber where RowNumber%2=0

    The approach preferred here is using VIEW because:
    1. Easy to understand.
    2. In future if you need to perform any operation related to RowNumber then you don't need to create another complex query. You could use view having rownumber column in it.


    Category: Database | Views: 2598 | Added by: Admin | Date: 2012-09-03 | Comments (0)

    « 1 2
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2019