Creating Database Tables

Creating Database Tables

The online retail application uses the Java Server Pages Extensible Markup Language (JSPXML) database, which contains the User, Item, and PItem tables. Listing 1-1 shows the database.sql file, which contains the Transact-SQL (T-SQL) script to create the tables of the JSPXML database and insert sample data:

Listing 1-1: The database.sql File

Use JSPXML
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Item]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Item]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PItem]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PItem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[User]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[User]
GO
CREATE TABLE [dbo].[Item] 
(
   [ItemCD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [ItemName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Price] [int] NULL,
   [Qty] [int] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PItem] 
(
   [ItemCD] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [UserID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Qty] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[User] 
(
   [UserID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Role] [int] NULL 
) ON [PRIMARY]
GO
Insert Into [User] Values('admin','admin',1)
Insert Into [User] Values('Peter','peter',2)
Insert Into [User] Values('Sara','sara',2)
GO

Download this listing.

The above code creates the User, Item, and PItem tables of the JSPXML database. The code also inserts sample data in the User table.