The sql script for Commerce db

Jun 25, 2011 at 7:40 AM

If someone needs the sql script to create the data base is here:

 

GO
CREATE TABLE [Categories](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED([CategoryID]))
GO

/***************************************************************************************/

GO
CREATE TABLE [Orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerName] [nvarchar](256) NOT NULL,
	[OrderDate] [datetime] NOT NULL,
	[ShipDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED([OrderID]))
GO

/***************************************************************************************/

GO
CREATE TABLE [Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryID] [int] NOT NULL,
	[ModelNumber] [nvarchar](50) NULL,
	[ModelName] [nvarchar](50) NULL,
	[ProductImage] [nvarchar](50) NULL,
	[UnitCost] [money] NOT NULL,
	[Description] [nvarchar](3800) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED([ProductID]))
GO

/***************************************************************************************/

GO
CREATE TABLE [OrderDetails](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderID] [int] NULL,
	[ProductID] [int] NULL,
	[Quantity] [int] NULL,
	[UnitCost] [money] NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED([Id]))
GO

/***************************************************************************************/

GO
CREATE TABLE [ShoppingCart](
	[RecordID] [int] IDENTITY(1,1) NOT NULL,
	[CartID] [nvarchar](50) NULL,
	[Quantity] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_ShoppingCart] PRIMARY KEY CLUSTERED([RecordID]))
GO

/***************************************************************************************/

GO
CREATE TABLE [Reviews](
	[ReviewID] [int] IDENTITY(1,1) NOT NULL,
	[ProductID] [int] NOT NULL,
	[CustomerName] [nvarchar](50) NULL,
	[CustomerEmail] [nvarchar](50) NULL,
	[Rating] [int] NOT NULL,
	[Comments] [nvarchar](3850) NULL,
 CONSTRAINT [PK_Reviews] PRIMARY KEY CLUSTERED([ReviewID]))
GO


/***************************************************************************************/
ALTER TABLE [Orders] ADD  CONSTRAINT [DF_Orders_OrderDate]  DEFAULT (getdate()) FOR [OrderDate]
GO
/***************************************************************************************/
ALTER TABLE [Orders] ADD  CONSTRAINT [DF_Orders_ShipDate]  DEFAULT (getdate()) FOR [ShipDate]
GO
/***************************************************************************************/
ALTER TABLE [ShoppingCart] ADD  CONSTRAINT [DF_ShoppingCart_Quantity]  DEFAULT ((1)) FOR [Quantity]
GO
/***************************************************************************************/
ALTER TABLE [ShoppingCart] ADD  CONSTRAINT [DF_ShoppingCart_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO
/***************************************************************************************/
ALTER TABLE [OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_Order_OrderDetails] FOREIGN KEY([OrderID])
REFERENCES [Orders] ([OrderID])
GO
ALTER TABLE [OrderDetails] CHECK CONSTRAINT [FK_Order_OrderDetails]
GO
/***************************************************************************************/
ALTER TABLE [Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID])
REFERENCES [Categories] ([CategoryID])
GO
ALTER TABLE [Products] CHECK CONSTRAINT [FK_Products_Categories]
GO
/***************************************************************************************/
ALTER TABLE [Reviews]  WITH NOCHECK ADD  CONSTRAINT [FK_Reviews_Products] FOREIGN KEY([ProductID])
REFERENCES [Products] ([ProductID])
NOT FOR REPLICATION
GO
ALTER TABLE [Reviews] CHECK CONSTRAINT [FK_Reviews_Products]
GO
/***************************************************************************************/
ALTER TABLE [ShoppingCart]  WITH CHECK ADD  CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY([ProductID])
REFERENCES [Products] ([ProductID])
GO
ALTER TABLE [ShoppingCart] CHECK CONSTRAINT [FK_ShoppingCart_Products]
GO

/***************************************************************************************/

GO
CREATE VIEW [ViewAlsoPurchased]
AS
SELECT        TOP (5) OrderDetails.ProductID, Products.ModelName, SUM(OrderDetails.Quantity) AS TotalNumPurchased, OrderDetails.OrderID, 
                         Products.ProductID AS Products_ProductID
FROM            OrderDetails INNER JOIN
                         Products ON OrderDetails.ProductID = Products.ProductID
WHERE        (OrderDetails.OrderID IN
                             (SELECT DISTINCT OrderID
                               FROM            OrderDetails AS OrderDetailsSelected))
GROUP BY OrderDetails.ProductID, Products.ModelName, OrderDetails.OrderID, Products.ProductID
ORDER BY TotalNumPurchased DESC
GO

/***************************************************************************************/

GO
CREATE VIEW [VewOrderDetails]
AS
SELECT        Products.ProductID, Products.ModelNumber, Products.ModelName, OrderDetails.Quantity, OrderDetails.UnitCost, 
                         OrderDetails.OrderID
FROM            OrderDetails INNER JOIN
                         Products ON OrderDetails.ProductID = Products.ProductID
GO

/***************************************************************************************/

GO
CREATE VIEW [ViewCart]
AS
SELECT        TOP (100) PERCENT Products.ProductID, Products.ModelNumber, Products.ModelName, Products.UnitCost, ShoppingCart.Quantity, 
                         ShoppingCart.CartID
FROM            Products INNER JOIN
                         ShoppingCart ON Products.ProductID = ShoppingCart.ProductID AND Products.ProductID = ShoppingCart.ProductID
ORDER BY Products.ModelName, Products.ModelNumber
GO

/***************************************************************************************/

GO
CREATE PROCEDURE [SelectPurchasedWithProducts]
 @ProductID int
AS
	SELECT  TOP 5 
    OrderDetails.ProductID,
    Products.ModelName,
    SUM(OrderDetails.Quantity) as TotalNum

FROM    
    OrderDetails
  INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID

WHERE   OrderID IN 
(
    /* This inner query should retrieve all orders that have contained the productID */
    SELECT DISTINCT OrderID 
    FROM OrderDetails
    WHERE ProductID = @ProductID
)
AND OrderDetails.ProductID != @ProductID 

GROUP BY OrderDetails.ProductID, Products.ModelName 

ORDER BY TotalNum DESC
RETURN
GO
Best regards

May 24, 2012 at 2:16 AM

Dear Primillo,

This very helpful to me, thank you!

Have a good day!

Shun.

May 24, 2012 at 3:31 AM

You are welcome