/*------------------------------------------------------------------------------------------------------------

In this script we cover Basic DML statement including SELECT, INSERT, UPDATE AND DELETE

SELECT statement returns the data from tables. Does not make any changes to the underlying data. It is the most 
commonly used SQL statement. This statement can use a sub-select (sub query). Here is the syntax
SELECT fields FROM tables WHERE condition ORDERBY fields

The INSERT statement adds one or more new rows to a table. In a simplified treatment, INSERT has this form:
INSERT [INTO] table_or_view [(column_list)] data_values

The UPDATE is used to update data in tables. Here is the syntax
UPDATE TABLE SET COLUMN WHERE CONDITION

DELETE statement removes rows from a table! Be very careful with this statement. Here is the syntax
DELETE FROM TABLE WHERE CONDITION

More information can be found in this document, SQL Addons.doc
These example utilizie SQL Server sample database ADVENTUREWORKS

------------------------------------------------------------------------------------------------------------*/




USE ADVENTUREWORKS


--SELECT 

--select all data 

SELECT *
FROM [AdventureWorks].[Person].[Address]
  
  
--select specific columns
  
SELECT 
[AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
FROM [AdventureWorks].[Person].[Address]  


--select specific rows

SELECT *
FROM [AdventureWorks].[Person].[Address]
WHERE [City]='Seattle'



--INSERT
--inserting a row into Sales.CreditCard table

INSERT INTO [AdventureWorks].[Sales].[CreditCard]
           ([CardType]
           ,[CardNumber]
           ,[ExpMonth]
           ,[ExpYear]
           ,[ModifiedDate])
     VALUES
           ('VISA'
           ,'11112222847802'
           ,12
           ,2006
           ,GETDATE())






--UPDATE
--updating TaxRate for Province with ID=57 in Sales.SalesTaxRate table

--Make sure you use a WHERE clause otherwise it will do a Global Update


UPDATE [Sales].[SalesTaxRate]
   SET TaxRate = 8
WHERE StateProvinceID=57



--DELETE
--deleting a row in Sales.Customer table

--Make sure you use a WHERE clause otherwise it will do a Global Delete


DELETE FROM [Sales].[Customer]
  WHERE [CustomerID]=4


/*

Compiled by http://www.learningsqlserver2008.com/, Kash Data Consulting LLC 

Disclaimer:

All sample code and scripts are compiled by Kash Data Consulting LLC for illustrative purposes only. Kash Data Consulting LLC, 
therefore, cannot guarantee or imply reliability, or function of this code or scripts.

All code contained herein are provided to you "AS IS" without any warranties of any kind. Please test all code and 
scripts in test environment before deployment in production systems.

*/