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
<span class="pln"> USE DB02TEST01</span><span class="pun">;</span><span class="pln"> </span>
Create a table: ( Customers )
<span class="pln"> CREATE TABLE </span><span class="typ">Customers_rumman_1637935</span><span class="pun">(</span><span class="pln"> </span><span class="typ">CustomerId</span><span class="pln"> </span><span class="kwd">char</span><span class="pun">(</span><span class="lit">5</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">CompanyName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">40</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ContactName</span><span class="pln"> </span><span class="kwd">char</span><span class="pun">(</span><span class="lit">30</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Address</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">60</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">City</span><span class="pln"> </span><span class="kwd">char</span><span class="pun">(</span><span class="lit">15</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Phone</span><span class="pln"> </span><span class="kwd">char</span><span class="pun">(</span><span class="lit">24</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Fax</span><span class="pln"> </span><span class="kwd">char</span><span class="pun">(</span><span class="lit">24</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span>
Create a table: ( Orders )
<span class="pln"> CREATE TABLE </span><span class="typ">Orders_rumman_1637935</span><span class="pun">(</span><span class="pln"> </span><span class="typ">OrderId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">CustomerId</span><span class="pln"> </span><span class="kwd">char</span><span class="pun">(</span><span class="lit">5</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">OrderDate</span><span class="pln"> datetime </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ShippedDate</span><span class="pln"> datetime </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Freight</span><span class="pln"> money </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ShipName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">40</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ShipAddress</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">24</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Quantity</span><span class="pln"> integer </span><span class="kwd">null</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span>
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.
<span class="pln"> ALTER TABLE </span><span class="typ">Orders_rumman_1637935</span><span class="pln"> ADD </span><span class="typ">ShipRegion</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span><span class="pln"> </span>
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.
<span class="pln"> ALTER TABLE </span><span class="typ">Orders_rumman_1637935</span><span class="pln"> DROP COLUMN </span><span class="typ">ShipRegion</span><span class="pun">;</span><span class="pln"> ALTER TABLE </span><span class="typ">Orders_rumman_1637935</span><span class="pln"> ADD </span><span class="typ">ShipRegion</span><span class="pln"> </span><span class="kwd">char</span><span class="pun">(</span><span class="lit">8</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">;</span><span class="pln"> </span>
Delete the formally created column ShipRegion
<span class="pln"> ALTER TABLE </span><span class="typ">Orders_rumman_1637935</span><span class="pln"> DROP COLUMN </span><span class="typ">ShipRegion</span><span class="pln"> </span>
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.
<span class="pln"> INSERT INTO </span><span class="typ">Orders_rumman_1637935</span><span class="pln"> </span><span class="pun">(</span><span class="typ">OrderId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">CustomerId</span><span class="pun">,</span><span class="pln"> </span><span class="typ">OrderDate</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ShippedDate</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Freight</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ShipName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ShipAddress</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Quantity</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">10</span><span class="pun">,</span><span class="pln"> </span><span class="str">'ord01'</span><span class="pun">,</span><span class="pln"> getdate</span><span class="pun">(),</span><span class="pln"> getdate</span><span class="pun">(),</span><span class="pln"> </span><span class="lit">100.0</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Windstar'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ocean'</span><span class="pun">,</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span>
Using the ALTER TABLE statement, add the current system date and time as the
default value to the OrderDate column of the Order table.
<span class="pln"> ALTER TABLE </span><span class="typ">Orders_rumman_1637935</span><span class="pln"> ADD CONSTRAINT df_Orders_rumman_1637935_OrderDate DEFAULT GETDATE</span><span class="pun">()</span><span class="pln"> FOR </span><span class="typ">OrderDate</span><span class="pln"> </span>
Rename the city column of the Customers table. The new name is town
<span class="pln"> sp_RENAME </span><span class="str">'Customers_rumman_1637935.Town'</span><span class="pln"> </span><span class="pun">,</span><span class="pln"> </span><span class="str">'City'</span><span class="pln"> </span>
