Create, Drop and Alter Operations in SQL Server (DDL Operations)

In this blod we will discuss how to perform Create, Drop and Alter Operations in SQL Server (DDL Operations)

Use a Database in your Project

USE DB02TEST01;

Create a table: ( Customers )

CREATE TABLE Customers_rumman_1637935(
CustomerId char(5) not null,
CompanyName varchar(40) not null,
ContactName char(30) null,
Address varchar(60) null,
City char(15) null,
Phone char(24) null,
Fax char(24) null
)

Create a table: ( Orders )

CREATE TABLE Orders_rumman_1637935(
OrderId int not null,
CustomerId char(5) not null,
OrderDate datetime null,
ShippedDate datetime null,
Freight money null,
ShipName varchar(40) null,
ShipAddress varchar(24) null,
Quantity integer null
)

Use the ALTER TABLE statement, add a new column named ShipRegion to the Orders_rumman_1637935 table the field should be nullable and integer constant.

ALTER TABLE Orders_rumman_1637935 ADD ShipRegion int null;

Use the ALTER TABLE statement, change the data type of the column ShipRegion from INTEGER to Character with length 8. The field may contain null variable.

ALTER TABLE Orders_rumman_1637935 DROP COLUMN ShipRegion;
ALTER TABLE Orders_rumman_1637935 ADD ShipRegion char(8) null;

Delete the formally created column ShipRegion

ALTER TABLE Orders_rumman_1637935 DROP COLUMN ShipRegion

Using the SQL Server Management Studio, try to insert a new row into the Orders table with the following values. (10, 'ord01', getdate(), getdate(), 100.0, 'Windstar', 'Ocean',1) Find out the reason why this is not possible.

INSERT INTO Orders_rumman_1637935 (OrderId, CustomerId, OrderDate, ShippedDate, Freight,
ShipName, ShipAddress, Quantity)
VALUES (10, 'ord01', getdate(), getdate(), 100.0, 'Windstar', 'Ocean',1)

Using the ALTER TABLE statement, add the current system date and time as the default value to the OrderDate column of the Order table.

ALTER TABLE Orders_rumman_1637935
ADD CONSTRAINT df_Orders_rumman_1637935_OrderDate
DEFAULT GETDATE() FOR OrderDate

Rename the city column of the Customers table. The new name is town

sp_RENAME 'Customers_rumman_1637935.Town' , 'City'