Table and Column



Table (entity) is a collection of related data entries and it consists of columns and rows.
Example: Employee, Department, ..,

 

CREATE Table

CREATE Table statement is used to create a new table. Here is the Syntax for creating a new Table.


CREATE  TABLE "TableName"
(
            "Column1" "Data Type" [Constraint],
            "Column2" "Data Type" [Constraint],
            "Column3" "Data Type" [Constraint]
);

EXAMPLE 1:

-- Create Department table with necessary constraints
CREATE TABLE [dbo].[Department](
[DepartmentID] [int] NOT NULL IDENTITY(0,1),
       [DepartmentName] [varchar](50) NOT NULL,
       [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)
       CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
       ( [DepartmentID] ASC) ON [PRIMARY]
 ) ON [PRIMARY]


-- Create Employee table with necessary constraints
CREATE TABLE [dbo].[Employee](
   [EmployeeID] int NOT NULL IDENTITY(1,1),
   [DepartmentID] int NOT NULL CONSTRAINT FK_Employee_DepartmentID
                              REFERENCES [Department](DepartmentID),
   [EmployeeName] varchar(50) NOT NULL,
   [Gender] char(1) NOT NULL CONSTRAINT CH_Employee_Gender
                            CHECK ([Gender] in('M','F','O')),
   [BirthDate] date NOT NULL,
   [Salary] decimal(10,2) NULL,
   [Detail] varchar(max) NULL,
   [IsActive] bit NOT NULL CONSTRAINT DF_Employee_IsActive DEFAULT (1),
   CONSTRAINT [UQ_Employee_EmployeeName] UNIQUE(EmployeeName),
   CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
   ([EmployeeID] ASC) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


GO

-- Create Department table if not exists
EXAMPLE2:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
-- Create Department table with necessary constraints
CREATE TABLE [dbo].[Department](
[DepartmentID] [int] NOT NULL IDENTITY(0,1),
       [DepartmentName] [varchar](50) NOT NULL,
       [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)
       CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
       ( [DepartmentID] ASC) ON [PRIMARY]
 ) ON [PRIMARY]
GO

DROP Table 

Example for deleting the existing table using the DROP TABLE command is given below.


EXAMPLE1:
DROP TABLE [Department]

EXAMPLE2:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
DROP TABLE [dbo].[Department]
GO

RENAME Table 


Example for renaming the existing Table syntax is given below.
EXAMPLE1:
EXEC sp_rename 'Department', 'Department_Test'
Another way of renaming the existing Table is, CREATE a new table, populate the data from existing table data and then DROP the existing Table and as given below
EXAMPLE1:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department_Test]') AND type in (N'U'))
-- Create Department table with necessary constraints
CREATE TABLE [dbo].[Department_Test](
[DepartmentID] [int] NOT NULL IDENTITY(0,1),
       [DepartmentName] [varchar](50) NOT NULL,
       [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)
       CONSTRAINT [PK_Department_Test_DepartmentID] PRIMARY KEY CLUSTERED
       ( [DepartmentID] ASC) ON [PRIMARY]
 ) ON [PRIMARY]
GO


SET IDENTITY_INSERT [dbo].[Department_Test] ON
INSERT INTO [dbo].[Department_Test] (
[DepartmentID] , [DepartmentName] , [IsActive])
SELECT [DepartmentID] ,[DepartmentName] ,[IsActive]
FROM Department
SET IDENTITY_INSERT [dbo].[Department_Test] OFF

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
DROP TABLE [dbo].[Department]
GO

SELECT Table


Retrieving the existing Table data using the SELECT command is given below. This command retrieves the data from Department table.
Selecting the Table consists of, in order

Query Execution Evaluation Order

The Optimizer will try to find the most efficient way/plan to execute the query based on available information such as table Index and scanning mechanism.

The Query execution default order is:
1.     FROM  clause
2.     WHERE  clause
3.     GROUP BY clause
4.     HAVING  clause
5.     SELECT  clause
6.     ORDER BY  clause
EXAMPLE1:

SELECT * FROM [dbo].[Department]

EXAMPLE2:

SELECT d.DepartmentID, d.DepartmentName, COUNT(e.EmployeeID) AS NumberOfEmployees
FROM Employee e
INNER JOIN Department d ON d.DepartmentID = e.DepartmentID
WHERE Gender ='M'
GROUP BY d.DepartmentID, d.DepartmentName
HAVING COUNT(EmployeeID)>=2
ORDER BY DepartmentName

INSERT INTO Table 


Example for inserting the record of a table syntax is given below.

EXAMPLE1:
-- Inserting the data to Department table with specified columns except primary key
INSERT [dbo].[Department] ([DepartmentName], [IsActive]) VALUES ('(None)', 1)
INSERT [dbo].[Department] ([DepartmentName], [IsActive]) VALUES ('Computer Science', 1)
INSERT [dbo].[Department] ([DepartmentName], [IsActive]) VALUES ('Maths', 1)
INSERT [dbo].[Department] ([DepartmentName], [IsActive]) VALUES ('Chemistry', 1)

EXAMPLE2:

-- Inserting the data to Department_Test table from Department table with all columns including primary key

SET IDENTITY_INSERT [dbo].[Department_Test] ON
INSERT INTO [dbo].[Department_Test] (
[DepartmentID] , [DepartmentName] , [IsActive])
SELECT [DepartmentID] ,[DepartmentName] ,[IsActive]
FROM Department
SET IDENTITY_INSERT [dbo].[Department_Test] OFF

EXAMPLE3:

-- Inserting the data to Department_Test table with all columns including primary key
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (0, N'(None)', 1)
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (1, N'Computer Science', 1)
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (2, N'Maths', 1)
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (3, N'Chemistry', 1)
SET IDENTITY_INSERT [dbo].[Department_Test] OFF

UPDATE Table 


Example for updating the record of a table syntax is given below.

EXAMPLE:

UPDATE Department SET DepartmentName = 'Computer Science'
WHERE DepartmentName = 'Computer'

DELETE Table 


Example for deleting the record of a table syntax is given below.

EXAMPLE:

DELETE FROM Department WHERE DepartmentName = 'Computer'

ADD Column


Example for adding a new column to the table syntax is given below.

EXAMPLE1:

ALTER TABLE [dbo].[Department] ADD [DepartmentAbbr] [varchar](10) NULL

EXAMPLE2:

IF NOT EXISTS( SELECT 1 FROM sys.columns WHERE Name = N'DepartmentAbbr'
      AND Object_ID = Object_ID(N'Department'))
ALTER TABLE [dbo].[Department] ADD [DepartmentAbbr] [varchar](10) NULL

ALTER Column


Example for altering an existing table column length (from 10 to 15) syntax is given below.

EXAMPLE1:

ALTER TABLE [dbo].[Department] ALTER COLUMN [DepartmentAbbr] [varchar](15) NULL

EXAMPLE2:

Example for altering an existing table column’s data type (varchar to nvarchar) syntax is given below.
ALTER TABLE [dbo].[Department] ALTER COLUMN [DepartmentAbbr] [nvarchar](15) NULL

DROP Column


Example for dropping an existing column of table syntax is given below.

EXAMPLE1:

ALTER TABLE [dbo].[Department] DROP COLUMN [DepartmentAbbr]

EXAMPLE2:

IF EXISTS( SELECT 1 FROM sys.columns WHERE Name = N'DepartmentAbbr'
      AND Object_ID = Object_ID(N'Department'))
ALTER TABLE [dbo].[Department] DROP COLUMN [DepartmentAbbr]

RENAME Column


Example for renaming the existing table column (from DepartmentName' to 'DeptName') syntax is given below.

EXAMPLE1:

EXEC sp_rename 'Department.DepartmentName', 'DeptName', 'COLUMN'

EXAMPLE2:

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'DeptName'
      AND Object_ID = Object_ID(N'Department'))
   AND EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'DepartmentName'
      AND Object_ID = Object_ID(N'Department'))
EXEC sp_rename 'Department_Test.DepartmentName', 'DeptName', 'COLUMN'

Check the existence of Table 


EXAMPLE:

if OBJECT_ID(N'Department') IS NULL
      print 'object does not exist'     
else
      print 'object exists'     

Check the existence of Column of a Table


EXAMPLE:

IF NOT EXISTS( SELECT 1 FROM sys.columns WHERE Name = N'DepartmentName'
      AND Object_ID = Object_ID(N'Department'))
      PRINT 'column does not exist'     
ELSE
      PRINT 'column exists' 

No comments:

Post a Comment