SQL

CREATE TABLE WITH CONSTRAINTS

You can set constraints to your tables during creation.

To define integrity among tables and define rules against values accept from the columns we can use table constraints.

Following are some of the most useful constraints we usually use during SQL scripting.

·         Key Constraints (Foreign Key, Unique, primary key)

·         Auto Increment columns

·         Define rules regarding values allow from the columns

·         Not null/null

There have 2 ways to define constraints during table creation

1.    Define constraints in same line of the column definition.

CREATE TABLE [dbo].[UserData]

(

NID varchar(10)PRIMARY KEY NOT NULL,

FirstName varchar(Max)NOT NULL,

LastName varchar(100)NOT NULL,

Age int NOT NULL CHECK(Age BETWEEN 6 AND 90),

)

2.    Set constraints separately after define columns.

CREATE TABLE [dbo].[UserData]

(

NID varchar(10)PRIMARY KEY NOT NULL,

FirstName varchar(Max)NOT NULL,

LastName varchar(100)NOT NULL,

Age int NOT NULL,

CONSTRAINT chk_Age CHECK(Age BETWEEN 6 AND 90)

)

Ex:-

CREATE TABLE [dbo].[UserData]

(

NID varchar(10)PRIMARY KEY NOT NULL,

FirstName varchar(Max)NOT NULL,

LastName varchar(100)NOT NULL,

Age int NOT NULL,

CONSTRAINT chk_Age CHECK(Age BETWEEN 6 AND 90)

)

CREATE TABLE [dbo].[Customer]

(

NID varchar(10) NOT NULL FOREIGN KEY REFERENCES [dbo].[UserData](NID) ,

CustomerID int IDENTITY PRIMARY KEY  NOT NULL,

CompanyName varchar(Max) NOT NULL,

CotactName varchar(100) NOT NULL,

Address varchar(Max)NULL,

City varchar(50)NULL

)

CREATE TABLE [dbo].[StudentData]

(

NID varchar(10) NOT NULL,

StudentID int IDENTITY PRIMARY KEY  NOT NULL ,

Grade int NOT NULL,

CONSTRAINT chk_frigh FOREIGN KEY(NID) REFERENCES [dbo].[UserData](NID)

)

ALTER TABLE

You can also set constraints after creating table. For that you need to use ALTER statement. ALTER statement can use when you need to do modification to a table after creating it.

·         Add columns

·         Set constraints

·         Remove columns

·         Remove Constraints

·         Change existing column

Ex:

1.    Using following query user can set new constraint to Student data table

ALTER TABLE [dbo].[StudentData]

ADD CONSTRAINT chk_Grade CHECK(Grade BETWEEN 1 AND 13)

2.    Using following query user can add new column to existing table

ALTER TABLE [dbo].[StudentData]

ADD Extra_Activities varchar(Max) NULL;

3. Add multiple columns

ALTER TABLE [dbo].[StudentData]

ADD Extra_Activities varchar(Max) NULL,Leadership varchar(Max) NULL;

4.    Drop existing column

ALTER TABLE [dbo].[StudentData]

DROP COLUMN Extra_Activities;

5.    Drop multiple column once

ALTER TABLE [dbo].[StudentData]

DROP COLUMN Extra_Activities,Leadership ;

SQL DELETIONS

DELETE

Using delete statement you can delete rows from specified table. Delete statement allows you to select required rows using ‘WHERE’ clause.

·         You can’t delete rows from a table by using delete statement if that row referenced by another table row. If you need to delete that type of row you need to delete referenced rows before

delete from Customer where NID='123456789V'; (child1)

delete from StudentData where NID='123456789V'; (child2)

delete from UserData where NID='123456789V';(parent table row)

·         If you going to delete multiple rows at once but one of the rows referenced by another table entire query may fail and none of the row may delete.

·         You can use DELETE without WHERE clause for delete all the data from your table

delete from Customer

DROP

Can used to remove one or more tables, indexers, databases, views, procedures, constraints, etc...

·         Drop statement can’t use with tables those referenced by foreign key. If you need to drop that type of tables) before you need to drop referencing tables (child tables).

DROP TABLE [dbo].[Customer] ------(Child1)

DROP TABLE [dbo].[StudentData] -----(Child2)

DROP TABLE [dbo].[UserData] ------(Parent)

TRUNCATE

Truncate statement is mostly same as to the Delete statement. You can use truncate statement to remove data from the table. Truncate statement not removing the entire table it only remove data from the table. Truncate statement can only use to remove all the data from the table.

·         You can’t use where clause with truncate statement as you can’t remove specified data from table using truncate statement.

·         You can’t remove data of a table using truncate statement if that table referenced by another table even no data still referenced by another table.

·         You can’t remove data of a table using truncate statement if that table participates for the indexed view.

If you need to delete data from a table that has above characteristics you can use Delete statement instead of Truncate.

Ex:-

--empty data inside tables--

truncate table [dbo].[Customer]

truncate table [dbo].[StudentData]

CROSS APPLY

APPLY operator use to invoke the table -valued functions during query excecution.
suppose that you need to excute a function for each rows returned by an outer query expression then you can use the CROSS APPLY.

SELECT * FROM dbo.Customer CUS

 CROSS APPLY (SELECT * FROM UDF.CalculateSalery(CustomerID,'01/01/2012'))SAL
WHERE  CompanyName='SoftCodeIT'

above query will retrive all the customers who are work with SoftCodeIT with their salery.
special thing of this function is, it take each row and invoke function within the query expression. otherwise we'll have to use a loop to excecute function for multiple records but it would be not a optimize way.