DeletorWall, Sql Server

DeletorWall

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

--select @@version as 'sql server version'

CREATE TABLE CATEGORIES (
  CategoryID int NOT NULL PRIMARY KEY,
  CategoryName nvarchar(255),
  RefID int,
  Detail nvarchar(255)
);
GO

CREATE TABLE EMPLOYEES (
  EmployeeID int NOT NULL PRIMARY KEY,
  Name nvarchar(255),
  SurName nvarchar(255),
  Detail nvarchar(255)
);
GO

CREATE TABLE OrderDetails (
  OrderID int,
  ProductID int,
  Quantity int,
  Price int,
  CategoryID int
);
  
CREATE TABLE Orders (
  OrderID int,
  EmpID int
);
GO

ALTER TABLE CATEGORIES
ADD FOREIGN KEY (RefID) REFERENCES CATEGORIES(CategoryID);
GO

INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (0, 'Root', 0); 
INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (1, 'Electronics', 0); 
INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (2, 'Drink', 0); 
INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (3, 'Computer', 1); 
INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (4, 'Mouse', 3); 
INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (5, 'Keyboard', 3); 
INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (6, 'Juice', 2); 
INSERT INTO CATEGORIES (CategoryID,CategoryName, RefID) VALUES (7, 'Mouse Cable', 4); 
GO

INSERT INTO EMPLOYEES (EmployeeID, Name, SurName) VALUES (100, 'Michael', 'Jackson'); 
INSERT INTO EMPLOYEES (EmployeeID, Name, SurName) VALUES (101, 'Dr.', 'X'); 
GO

INSERT INTO Orders VALUES (5000, 100); 
INSERT INTO Orders VALUES (5001, 101); 
INSERT INTO Orders VALUES (5002, 100); 
INSERT INTO Orders VALUES (5003, 101); 
GO

INSERT INTO OrderDetails VALUES (5000, 3001, 10, 25, 4); 
INSERT INTO OrderDetails VALUES (5000, 3002, 10, 123, 7); 
INSERT INTO OrderDetails VALUES (5000, 3003, 10, 123, 7); 
INSERT INTO OrderDetails VALUES (5000, 3003, 10, 123, 7); 
INSERT INTO OrderDetails VALUES (5001, 3004, 10, 33, 3); 
INSERT INTO OrderDetails VALUES (5001, 3005, 10, 244, 2); 
INSERT INTO OrderDetails VALUES (5002, 3006, 10, 215, 1); 
INSERT INTO OrderDetails VALUES (5000, 3002, 10, 215, 1);
INSERT INTO OrderDetails VALUES (5000, 3003, 10, 215, 1);
INSERT INTO OrderDetails VALUES (5002, 3007, 10, 33, 3); 
INSERT INTO OrderDetails VALUES (5003, 3008, 10, 244, 2); 
INSERT INTO OrderDetails VALUES (5003, 3008, 10, 244, 2); 
INSERT INTO OrderDetails VALUES (5003, 3002, 10, 123, 7); 
INSERT INTO OrderDetails VALUES (5003, 3003, 10, 123, 7); 
INSERT INTO OrderDetails VALUES (5002, 3010, 10, 215, 1); 
GO

/*
DECLARE @rowResult TABLE (
    EmployeeID int,
    CategoryID int,
    Total int);
DECLARE @EmployeeID int;
DECLARE @CategoryID int;
DECLARE @RefID int;

SET @EmployeeID = 100;
SET @CategoryID = 7;

SELECT @RefID = RefID FROM CATEGORIES WHERE CategoryID = @CategoryID;

WHILE @RefID > 0
BEGIN

INSERT INTO @rowResult
    SELECT e.EmployeeID, d.CategoryID, SUM(d.Quantity * d.Price) AS 'Total'
    FROM Employees e 
      INNER JOIN Orders o ON o.EmpID = e.EmployeeID
      INNER JOIN OrderDetails d ON d.OrderID = o.OrderID
    WHERE e.EmployeeID = @EmployeeID AND d.CategoryID = @CategoryID
    GROUP BY e.EmployeeID, d.CategoryID ;
    
    SET  @CategoryID = @RefID;
SELECT @RefID = RefID FROM CATEGORIES WHERE CategoryID = @CategoryID;
END

SELECT * FROM @rowResult
GO
*/


run \| edit \| history \| help	0