SQL - Add column to a table

ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Add Default Value to Existing Column

-- Add default to existing column DateOfHire:

ALTER TABLE [dbo].[Employees] ADD  DEFAULT (getdate()) FOR [DateOfHire]

-- Add default value to existing column IsTerminated

ALTER TABLE [dbo].[Employees] ADD  DEFAULT ((0)) FOR [IsTerminated]

Add New Column with Default Value

-- Add new column DateOfHire with default

ALTER TABLE Employees ADD DateOfHire datetime DEFAULT (GETDATE())

-- Add new column IsTerminated with default

ALTER TABLE Employees ADD IsTerminated datetime DEFAULT (0)

Add Default Value with Create Table

CREATE TABLE [dbo].[Employees]

(

[EmployeeID] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](50) NULL,

[LastName] [varchar](50) NULL,

[SSN] [varchar](9) NULL,

-- Add default of zero

[IsTerminated] [bit] NOT NULL DEFAULT ((0)) ,

-- Add default of getdate()

[DateAdded] [datetime] NULL DEFAULT (getdate()),

[Comments] [varchar](255) NULL,

[DateOfHire] [datetime] NULL

)

ACRONYMS

SOA - Service Oriented Architecture
SLA - Service Level Agreement

One of the core concepts in SOA is the idea that a service should have a Service Level that it agrees to meet (the SLA), this might be technical in terms of up times, response times, amount of information to be handled

SQL JOINS examples

FULL JOIN :

• include a WHERE clause with a full outer join to return only the rows where there is no matching data between the tables.
• query returns only those products that have no matching sales orders, as well as those sales orders that are not matched to a product

Ex.

USE AdventureWorks2008R2;

GO

-- The OUTER keyword following the FULL keyword is optional.

SELECT p.Name, sod.SalesOrderID

FROM Production.Product p

FULL OUTER JOIN Sales.SalesOrderDetail sod

ON p.ProductID = sod.ProductID

WHERE p.ProductID IS NULL

OR sod.ProductID IS NULL

ORDER BY p.Name ;

LEFT OUTER JOIN :

• include all products, regardless of whether a review has been written for one, use an ISO left outer join

eg.

USE AdventureWorks2008R2;

GO

SELECT p.Name, pr.ProductReviewID

FROM Production.Product p

LEFT OUTER JOIN Production.ProductReview pr

ON p.ProductID = pr.ProductID

RIGHT OUTER JOIN :

• include all sales persons in the results, regardless of whether they are assigned a territory, use an ISO right outer join

eg.

USE AdventureWorks2008R2;

GO

SELECT st.Name AS Territory, sp.BusinessEntityID

FROM Sales.SalesTerritory st

RIGHT OUTER JOIN Sales.SalesPerson sp

ON st.TerritoryID = sp.TerritoryID ;

SELF JOIN :

• a self-join to find the products that are supplied by more than one vendor.

eg.

USE AdventureWorks2008R2;

GO

SELECT DISTINCT pv1.ProductID, pv1.VendorID

FROM Purchasing.ProductVendor pv1

INNER JOIN Purchasing.ProductVendor pv2

ON pv1.ProductID = pv2.ProductID

AND pv1.VendorID <> pv2.VendorID

ORDER BY pv1.ProductID

CROSS JOIN :

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

USE AdventureWorks2008R2;

GO

SELECT p.BusinessEntityID, t.Name AS Territory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

ORDER BY p.BusinessEntityID;

• However, if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set.

eg.

USE AdventureWorks2008R2;

GO

SELECT p.BusinessEntityID, t.Name AS Territory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

WHERE p.TerritoryID = t.TerritoryID

ORDER BY p.BusinessEntityID;

-- Or

USE AdventureWorks2008R2;

GO

SELECT p.BusinessEntityID, t.Name AS Territory

FROM Sales.SalesPerson p

INNER JOIN Sales.SalesTerritory t

ON p.TerritoryID = t.TerritoryID

ORDER BY p.BusinessEntityID;