Otus, HomeWork, 25.07.01

Otus, HomeWork, 25.07.01

Create Tables Create_Shop2.sql

D:\VC25\Otus\CS\Projects\Shop\Scripts\

---------------------------------------------------------------------------------------------------

USE Shop3

-- Create Tables

-- Таблица Products

CREATE TABLE Products (

    ProductID INT IDENTITY(1,1) PRIMARY KEY,

    ProductName NVARCHAR(255) NOT NULL,

    Description NVARCHAR(MAX),

    Price NUMERIC(10, 2) NOT NULL CONSTRAINT CK_Product_Price CHECK (Price > 0),

    QuantityInStock INT NOT NULL CONSTRAINT CK_Product_Qty CHECK (QuantityInStock >= 0)

);

Go

-- Таблица Users

CREATE TABLE Users (

    UserID INT IDENTITY(1,1) PRIMARY KEY,

    UserName NVARCHAR(255) NOT NULL,

    Email NVARCHAR(255) UNIQUE NOT NULL,

    RegistrationDate DATETIME DEFAULT GETDATE()

);

Go

-- Таблица Orders

CREATE TABLE Orders (

    OrderID INT IDENTITY(1,1) PRIMARY KEY,

    UserID INT FOREIGN KEY REFERENCES Users(UserID),

    OrderDate DATETIME DEFAULT GETDATE(),

    Status NVARCHAR(50) NOT NULL

);

Go

-- Таблица OrderDetails

CREATE TABLE OrderDetails (

    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,

    OrderID INT FOREIGN KEY REFERENCES Orders(OrderID),

    ProductID INT FOREIGN KEY REFERENCES Products(ProductID),

    Quantity INT NOT NULL CONSTRAINT CK_OrderDetails_Quantity CHECK (Quantity > 0),

    TotalCost NUMERIC(10, 2) NOT NULL CONSTRAINT CK_OrderDetails_TotalCost CHECK (TotalCost > 0)

);

GO

-- ------------------------------------------------------

--USE Shop3

DELETE FROM Products

INSERT INTO Products (ProductName, Description, Price, QuantityInStock)

VALUES

('jackson usa', 'USA Signature guitars', 1500.00, 20),

('Gibson LP', 'Gibson Les Paul Tribute', 2000.00, 15),

('Schecter Plat', 'Schecter Platinium', 1000.00, 30),

('Fender Am', 'Fender American Professional', 2500.00, 10),

('Ibanez 550', 'Ibanez RG550', 2600.00, 42),

('Gibson SG', 'Gibson SG Standard', 800.00, 40),

('Schecter BJ', 'Schecter Black Jack', 1200.00, 26),

('Schecter Reaper', 'Schecter Reaper usa', 1400.00, 42);

GO

DELETE FROM Users

GO

INSERT INTO Users (UserName, Email)

VALUES

('Rony James Dio', 'rony@jamesdio.com'),

('Janis Joplin', 'janis@joplin.com');

GO

-----------------------------------------------------------

--USE Shop3

DELETE FROM Orders

INSERT INTO Orders (UserID, Status)

VALUES

(1, 'pending'), 

(1, 'completed'), 

(1, 'completed');

go

INSERT INTO Orders (UserID, Status)

VALUES

(2, 'pending'), 

(2, 'completed');

go

DELETE FROM OrderDetails

INSERT INTO OrderDetails (OrderID, ProductID, Quantity, TotalCost)

VALUES

(1, 1, 2, 10), 

(1, 2, 1, 10), 

(2, 3, 3, 10),

(2, 4, 5, 10),

(3, 5, 1,10),

(4, 6, 3,10),

(5, 7, 5, 10),

(5, 8, 1, 10);

go

--USE Shop3

SELECT TOP (1000)

    O.[OrderID],

    U.[UserID],

    U.[UserName],

    U.[Email],

    U.[RegistrationDate],

    O.[OrderDate],

    O.[Status],

    OD.[OrderDetailID],

    OD.[ProductID],

    OD.[Quantity],

    OD.[TotalCost],

    P.[ProductName],

    P.[Description],

    P.[Price],

    P.[QuantityInStock]

FROM 

    [dbo].[Orders] AS O

INNER JOIN 

    [dbo].[Users] AS U ON O.[UserID] = U.[UserID]

INNER JOIN 

    [dbo].[OrderDetails] AS OD ON O.[OrderID] = OD.[OrderID]

INNER JOIN 

    [dbo].[Products] AS P ON OD.[ProductID] = P.[ProductID];

GO