This post continues from the previous post, Part 2.

Here is the sql code that creates a new database and all of the tables. You could copy it to a new query in SSMS and run it. Then you could create a new view and SELECT against it. One problem with the columns in the tables is that we have the same table name in both the Suppliers and Orders table: datetimestamp. Will this pose a problem? We’ll see.

In the code below, before you execute it in a query window, you could change the name of the database to whatever you want. Also change the USE statement to the name you chose.

CREATE DATABASE BookStore
GO
USE [Bookstore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Suppliers](
	[FullSupplierName] [varchar](20) NOT NULL,
	[SupplierCode] [varchar](7) NOT NULL,
	[datetimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED 
(
	[SupplierCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Suppliers] ADD  CONSTRAINT [DF_Suppliers_datetimestamp]  DEFAULT (getdate()) FOR [datetimestamp]
GO
CREATE TABLE [dbo].[Orders](
	[SupplierCode] [varchar](7) NOT NULL,
	[OrderNumber] [varchar](5) NOT NULL,
	[TotalUnits] [int] NOT NULL,
	[TotalValue] [money] NOT NULL,
	[CSVDatetime] [varchar](14) NOT NULL,
	[datetimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_datetimestamp]  DEFAULT (getdate()) FOR [datetimestamp]
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD FOREIGN KEY([SupplierCode])
REFERENCES [dbo].[Suppliers] ([SupplierCode])
GO
CREATE TABLE [dbo].[Books](
	[OrderNumber] [varchar](5) NULL,
	[BookTitle] [nvarchar](70) NULL,
	[ISBN] [nchar](10) NULL,
	[Authors] [nvarchar](30) NULL,
	[Quantity] [int] NULL,
	[UnitPrice] [money] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Books]  WITH CHECK ADD FOREIGN KEY([OrderNumber])
REFERENCES [dbo].[Orders] ([OrderNumber])
GO