Your First SQL Server 2000 Database

We've just dropped our original MyDatabase1 database and recreated both its structure and its widgets table. The one thing we're missing is the records of the actual widgets. Clear the code in the query analyzer window and enter the following code there:

USE MYDATABASE1      
GO      
     
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Red Widget', 9.95)      
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Blue Widget', 14.50)      
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Green Widget', 24.95)      
INSERT INTO Widgets(widgetName)      
VALUES('Orange Widget')      
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Purple Widget', 1.95)      
INSERT INTO Widgets(widgetName)      
VALUES('Yellow Widget')      
GO

Execute the code once again by pressing F5 or clicking on the green play button. For each new record that's added to the widgets table, SQL Server 2000 should respond with "(1 row(s) affected)" in the bottom pane of the query analyzer window.

Lastly, to view the records in the widgets table, clear the code in Query Analyzer and enter and execute the following TSQL code:

USE MYDATABASE1      
GO      
     
SELECT * FROM Widgets      
ORDER BY widgetName ASC

You should see a list of records from the widgets table in the lower pane of Query Analyzer, just like this:

And there you have it, one fully functional database that you now know how to create with both Enterprise Manager and Query Analyzer. Let's look at using some TSQL statements to manipulate our database on the next page.

Manipulating our Database

Now let's play around with a couple of TSQL commands and modify our database. The details of the commands you're about to see are available in the SQL Server 2000 help file, which you can access by pressing F1 in Query Analyzer.

Adding a column to our widgets table

Let's pretend that the boss of our widgets corporation wants to be able to set whether or not a particular widget is in stock. Most widgets are available; however, a select few have to be imported from Widgetville first. We could add a new column called widgetAvailable with this TSQL code:

USE MYDATABASE1      
GO      
     
ALTER TABLE widgets      
ADD widgetAvailable BIT NOT NULL DEFAULT 1      
GO

The code above creates the new widgetAvailable column and also sets the value of that column to 1 for every record that currently resides in the widgets table.