SQL query examples

Get all Customers

Read all customers    [ Read more... ]

SELECT * FROM Customers

Get a Customer

Read a customer    [ Read more... ]

SELECT * FROM Customers
WHERE Id = 'abc'

Get Customers (Filter by Date)

Using date time requires yyyy-MM-dd format usage    [ Read more... ]

SELECT * FROM Customers
WHERE Created > '2010-01-01'

Insert a Customer

Insert a customer    [ Read more... ]

INSERT INTO Customers
([Name]
	, [Email]
	, [Description]
	, [Phone]
	, [Balance]
	, [AddressLine1]
	, [AddressLine2]
	, [AddressCity]
	, [AddressState]
	, [AddressCountry]
	, [AddressPostalCode]
	, [InvoicePrefix]
	)
VALUES('Cust-1'
	, 'email@abc.com'
	, 'Some desc'
	, '+1 222-333-4444'
	, 0
	, '55 Main St.'
	, 'Suite 100'
	, 'New York'
	, 'NY'
	, 'USA'
	, '07204' --JSON fragment 
	, 'INVC'
)

Update a Customer

Update a customer    [ Read more... ]

UPDATE Customers
SET   Name='Cust1-Updated'
	, Email='a-updated@b.com'
	, Phone='+1 800-123-2345'
	, Description='Desc-updated'
	, AddressLine1='Line-1-upd'
	, AddressLine2='Line-2-upd'
	, AddressCity='SomeCity'
	, AddressState='NY'
	, AddressCountry='USA'
	, AddressPostalCode='112233'
	
	, ShippingPhone='+1 800-123-2345'
	, ShippingName='SHName-upd'
	, ShippingAddressLine1='Line-1-upd'
	, ShippingAddressLine2='Line-2-upd'
	, ShippingAddressCity='SomeCity'
	, ShippingAddressState='NY'
	, ShippingAddressCountry='USA'
	, ShippingAddressPostalCode='112233'
	
	, Balance=100	
WHERE Id='cus_IcUG2lD69ZHuol'

Delete a Customer

Delete a customer by Id    [ Read more... ]

DELETE FROM Customers
WHERE Id = 'abc'

Get all Products

Read all products    [ Read more... ]

SELECT * FROM Products

Get a Product

Read a product    [ Read more... ]

SELECT * FROM Products
WHERE Id = 'abc'

Update a Product

Read a product    [ Read more... ]

UPDATE Products
SET [name] = 'new name'
WHERE Id = 'abc'

Inserts a Product

Inserts a product    [ Read more... ]

INSERT INTO Products
(     [Name]
	, [Caption]
	, [Description]
	, [Type]
	--, [UnitLabel] --only when product type=service
	, [Active]
	, [PackageDimensionsHeight]
	, [PackageDimensionsWidth]
	, [PackageDimensionsLength]
	, [PackageDimensionsWeight]
	, [URL]
	, [Image1]
	, [Image2]
	, [Image3]
	)
VALUES('SSIS PowerPack 3'
	, 'SSIS Caption 3'
	, 'SSIS PowerPack description long ....'
	, 'good' --or service
	--, 'Unit label' --only when product type=service
	, 'True' --active ?
	, '12'
	, '13'
	, '14'
	, '1000'
	, 'https://zappysys.com/products/ssis-powerpack/'
	, 'https://zappysys.com/images/tech/web-api-logo.png'
	, 'https://zappysys.com/images/tech/xml-logo.png'
	, 'https://zappysys.com/images/tech/salesforce-logo.png'
)

Update a Product

Product Update example. When supply Image1,Image2... it will reset previous images.    [ Read more... ]

UPDATE Products
SET   [Name]='SSIS PowerPack 3 - Updated'
	, [Caption]='Caption-updated'
	, [Description]='Desc-updated'
	--, [UnitLabel] --only when product type=service
	, [Active]='true'
	, [PackageDimensionsHeight]=12
	, [PackageDimensionsWidth]=13
	, [PackageDimensionsLength]=14
	, [PackageDimensionsWeight]=1122
	, [URL]='https://zappysys.com/products/ssis-powerpack/?updated=1'
	, [Image1]='https://zappysys.com/images/tech/web-api-logo.png?updated=1'
	, [Image2]='https://zappysys.com/images/tech/xml-logo.png?updated=1'
WHERE Id='prod_MiSJzGZ8PDM9uh'

Delete a Product

Delete a product    [ Read more... ]

DELETE FROM Products
WHERE Id = 'abc'

Get all Invoices

Read all invoices    [ Read more... ]

SELECT * FROM Invoices

Get an Invoice

Read an invoice    [ Read more... ]

SELECT * FROM Invoices
WHERE Id = 'abc'

Get all Invoice Line Items

Read all Invoice Line Items    [ Read more... ]

SELECT * FROM InvoiceItems

Get an Invoice Line Item

Read an invoice line item by Id    [ Read more... ]

SELECT * FROM InvoiceItems
WHERE Id = 'abc'

Insert an Invoice

Insert an invoice    [ Read more... ]

INSERT INTO Invoices([Customer],[AmountRemaining])
VALUES ('cus_LqWX1s0E32JJeZL', 12345)

Update an Invoice

Update an invoice    [ Read more... ]

UPDATE Invoices
SET [DaysUntilDue] = 20
WHERE Id = 'abc'

Delete an invoice

Delete an invoice    [ Read more... ]

DELETE FROM Invoices
WHERE Id = 'abc'