SQL Basic

Create Database DatabaseName

Alter Database DatabaseName Modify Name= NewDatabaseName
Execute sp_renameDB'OldDatabaseName','NewDatabaseName'

Drop Database DatabaseName

Alter Database DatabaseName Set Single_User With Rollback Immediate

Use [DatabaseName]
Go
Create Table TableName,
(
FieldName1 Datatype Null/Not Null Primary Key,
FieldName2 Datatype Null/Not Null,
)

Foriegn Key relation mapping
--------------------------------
Alter Table ForeignKeyTable add constraint ForeignKeyTable_ForiegnKeyColumn_FK
FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)

Altering an existing column to add a default constraint:
ALTER TABLE {TABLE_NAME}
ADD CONSTRAINT {CONSTRAINT_NAME}
DEFAULT {DEFAULT_VALUE} FOR {EXISTING_COLUMN_NAME}

Adding a new column, with default value, to an existing table:
ALTER TABLE {TABLE_NAME}
ADD {COLUMN_NAME} {DATA_TYPE} {NULL | NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} CONSTRAINT_TYPE(e.g. Default, Check) {DEFAULT_VALUE}

Dropping a constraint:
ALTER TABLE {TABLE_NAME}
DROP CONSTRAINT {CONSTRAINT_NAME}


Cascading referential integrity
Options when setting up cascading referential integrity constraint:
1. No Action- this is default behaviour. No Action specifies that if an attempt is
made to delete or upgrade a row with a key referenced by foreign keys in existing
rows in the other tables, an erroe is raised and the DELETE or UPDATE is rollback.

2. Cascade- Specifies that if an attempt is made to delete or update a row with a
 key referenced by foreign keys in existing rows in other tables, all rows containing
 thoses foriegn keys are also deleted or updated.

3. Set NULL- Specifies that if an attempt is made to delete or update a row with a key
 referenced by foreign keys in existing rows in other tables, all rows containing
those foreign keys are set to NULL.

4. Set Default- Specifed that if an attemp is made to delete or upate a row with akey referenced
by foreign keys in existing rows in other tables, all rows containg those foreign keys are set to
 default values.


Check Constraint
CHECK Constraint is used to limit the range of the values, that can be entered for a column


Identity Column
To get last generated identity column value

Now: You can use SCOPE_IDENTITY(), @@IDENTITY, IDENT_CURRENT('TableName')

SCOPE_IDENTITY() :-Same Session and the same scope
@@IDENTITY :- Same session and across any scope
IDENT_CURRENT('TableName') :- Specific table across any session and any scope.


Primary Key and Unique Key
We use UNIQUE constraint to enforce uniqueness of a column i.e. the column should not
allow any duplicate values. We can add a Unique constraint thru the designer or uning a
query.


To create the unique key using a query.
Alter Table Table_Name
Add constraint constraint_Name Unique (Column_Name)

Difference between Primary Key and Unique Key
1. A table can have only one primary key, but more than one unique key
2. Primary key does not allow nulls, where as unique key allows one one null


Group By Clause


Inner Join- returns only the matching rows between both the tables. Non matching rows are eliminated.

Select LeftTableCoumns,RightTableColumns
from LeftTable
JOIN / INNER JOIN RightTable
ON LeftTable.MatchingColumn=RightColumn.MatchingColumn

Left Join / Left Outer Join- returns all the matching row + non matching rows from the left table

Select LeftTableCoumns,RightTableColumns
from LeftTable
LEFT JOIN / LEFT OUTER JOIN RightTable
ON LeftTable.MatchingColumn=RightColumn.MatchingColumn

Right Join / Right Outer Join - returns all the matching row + non matching row from the right table

Select LeftTableCoumns,RightTableColumns
from LeftTable
RIGHT JOIN / RIGHT OUTER JOIN RightTable
ON LeftTable.MatchingColumn=RightColumn.MatchingColumn

Full Join / Full Outer Join - returns all rows from both left and right table, including the non matching rows

Select LeftTableCoumns,RightTableColumns
from LeftTable
FULL JOIN / FULL OUTER JOIN RightTable
ON LeftTable.MatchingColumn=RightColumn.MatchingColumn


Cross Join- produces Cartesian product of the 2 tables involved in the join and does not require ON keyword

Select LeftTableCoumns,RightTableColumns
from LeftTable
CROSS JOIN RightTable