SQL - DML (Data Manipulation Language)

Select

SELECT ...
FROM ...
[WHERE ...]
[GROUP BY ...
[HAVING ...] ]
[ORDER BY ... [DESC/ASC] ]

Execution order:
from
where
group by
having
select
order by

Calculated fields

YEAR(getdate()) - YEAR(BirthDate) AS Age
SELECT "Name: ", LastName FROM Employee
Functions:
math: +, -, *, /, ^, MOD
comp: =, <, >, <=, >=, <>
logical: AND, OR, NOT
date: NOW, YEAR, MONTH, DATEDIFF, WEEKDAY, DAY
text: LCASE, UCASE, LEFT, MID, RIGHT, TRIM, &
aggregate (group): COUNT (counts only not null), SUM, AVG, MAX, MIN

Examples:
Count number of customers: select count (*) from customer
Count number of customers with telephone: select count(te) from customer
Min and max price: select max(price), min(price) from article
total amount of reduction: select sum(reduction) from invoice
List of suppliers, except for Brussels:
select supplier.name, supplier.postalcode, city.name
from supplier, city
where supplier.postalcode = city.postalcode and postalcode <> "1000"
order by name

With

http://msdn.microsoft.com/en-us/library/ms175972.aspx
http://www.dba-oracle.com/t_sql99_with_clause.htm

Example:

WITH Statusses (StatusID, Name)
AS
(
  SELECT StatusID, Name
  FROM Status
)

select *
from Quote q
left outer join Statusses s on s.StatusID = q.StatusID

Over

http://technet.microsoft.com/en-us/library/ms189461.aspx

Insert

Standard insert

INSERT INTO Persons (LastName, Address, City) VALUES ('Jansen', 'Kerkstraat 11', 'Beerse')
INSERT INTO table2 SELECT * FROM table1;

Get ID of inserted record:
DECLARE @ID bigint
SET @ID = SCOPE_IDENTITY()

Create new table and insert

SELECT *
INTO [externaldatabase..]new_table_name
FROM old_tablename

Create new table and insert from openquery

select *
into dbo.lcusrep
from openquery(RETAIL_PROD_FR,'
select 
*
from lcusrep
')

Update

Standard update

UPDATE Persons SET Address='Kerkstraat 12', City='Turnhout' WHERE LastName='Jansen'

Update from other table (SQL server):

UPDATE TABLEA
SET     b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1

Update with latest record from another table (see also next point - Get latest record)

update Fee
set RulesetName = fr.rulename
from fee f, feerule fr
inner join (select max(startdate) as startdate, feeid from feerule group by feeid) frt
on fr.feeid = frt.feeid and fr.startdate = frt.startdate
where f.feeid = fr.feeid

Get latest record

Table with customer, status, date.
What is the latest status?

select customer, status, date
from CustomerStatus a
inner join (select customer, max(date) as lastdate from CustomerStatus group by customer) b
on a.customer = b.customer and a.date = b.lastdate

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

More simple:
select * from CustomerStatus where date = (select max(date) from CustomerStatus)

Working with subqueries

select count(*), sum(price)
from
( select max([Total Price]) as price
from [dbo].[PLEDEV$PFE Reporting Rental Line]
WHERE [Chassis Type] = 0 AND [Reporting Year] = 2014
group by [Document No_] ) a

Conditions in select

SELECT product, 'Status' = CASE WHEN quantity > 0 THEN 'in stock' ELSE 'out of stock' END
FROM dbo.inventory;

SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product

If you have to check for null, only the first option works.

Insert / Update

declare @Id bigint
declare @Tms timestamp
select @Id = Id, @Tms = Tms
from …
where …
if @Id is null
insert
else
update

Conversions

CAST (field AS datatype[length])
eg. select cast(intvalue as varchar(10)) from test
MSDN
Convert datetime to string:
CONVERT(varchar(30), [UpdateTime], 120) — 121 = YYYY-MM-DD hh:mm:ss:mmm
Convert string to datetime:
CONVERT(datetime, [TimeAsString], 120)

Convert null to 0
SELECT ISNULL(column, 0) FROM MyTable

Rounding

SQL server uses mathematical rounding. (lower than 5 rounds down, 5 or higher rounds up).
Unlike .NET which uses Banker's rounding (or round to the nearest even number when 5).
ROUND(field, number of digits)
http://www.mssqltips.com/sqlservertip/1589/sql-server-rounding-functions--round-ceiling-and-floor/
http://stackoverflow.com/questions/18847374/sql-server-rounding-issue-where-there-is-5

Check for null

The COALESCE() function returns the first non-null value in a list.
eg.
UPDATE Test
SET
EndDate = COALESCE(@EndDate, [EndDate]),
WHERE TestID = @TestID
This will only update the end date if the end date parameter is not null.

Insert Enter in a SQL command

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + CHAR(10) + 'This is line 2.'

Export as XML

select ApplicationSettingID, Name, Description, Type, StringValue, IntegerValue, DecimalValue, BooleanValue, DateValue
from ApplicationSetting for xml Path('ApplicationSettingDto'), Root('ArrayOfApplicationSettingDto')

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