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
