Sql

Sql Queries

Basic Statements

Get

SELECT     [Id]
        ,[Name]
        ,[Number]
FROM        [MyTable]        
WHERE    [Id] > 10
ORDER BY    [Id] DESC

Insert

INSERT INTO [MyTable]
           ([Name])
     VALUES
           ('Value')

Update

UPDATE [MyTable]

SET
    [Name] = 'Value'
    ,[Number] = 0
WHERE Id = 1

Determining Field Length

select datalength(MyField), count(*) as Frequency
from MyTable (nolock)
group by datalength(MyField)

Remove Line Breaks from a column

SELECT     [Id]
        , Replace(Replace(Convert(nvarchar(max), isnull([MyField], '')), char(10), ''), char(13), '') AS [MyField]
FROM [MyTable]

Use RowCount to limit entries

SET ROWCOUNT = 10

SELECT Id
. Name
FROM
MyTable

SET ROWCOUNT = 0

Sub Query with Count

Get count of products with more than 50 deliveries on one day

    select distinct convert(varchar(12), ProductDelivery.DeliveredDate) as [date], count(ProductDelivery.id) as Products 
    from ProductDelivery
    group by convert(varchar(12), Product)
    having count(ProductDelivery.id) > 50

Basic Query

USE AdventureWorks;
GO

SELECT    p.ProductID
FROM    Production.Product p
WHERE    ProductID
NOT IN
(
    SELECT ProductID
    FROM Production.WorkOrder
);
GO

SELECT    p.ProductID
FROM    Production.Product p
LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID
WHERE    w.ProductID IS NULL;

GO
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License