SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE _User ( User_Name varchar(50) NOT NULL, _Password varchar(50) NOT NULL, Type varchar(50) NOT NULL, PRIMARY KEY (User_Name) ); CREATE TABLE Managed_By ( User_Name varchar(50) NOT NULL, Store_Number INT NOT NULL, PRIMARY KEY (User_Name, Store_Number) ); CREATE TABLE Store ( Store_Number int NOT NULL, Store_type varchar(50) NOT NULL, Phone_Number varchar(15) NOT NULL, Street_Address varchar(100) NOT NULL, City_Name varchar(50) NOT NULL, _State varchar(50) NOT NULL, PRIMARY KEY (Store_Number) ); CREATE TABLE City ( City_Name varchar(50) NOT NULL, _State varchar(50) NOT NULL, Population int NOT NULL, PRIMARY KEY (City_Name, _State) ); CREATE TABLE Product ( PID int NOT NULL, Product_Name varchar(50) NOT NULL, Retail_Price decimal(12,2) NOT NULL, Manufacturer_Name varchar(100) NOT NULL, PRIMARY KEY (PID) ); CREATE TABLE Product_Category ( PID int NOT NULL, Category_Name varchar(50) NOT NULL, PRIMARY KEY (PID, Category_Name) ); CREATE TABLE Category ( Category_Name varchar(50) NOT NULL, PRIMARY KEY (Category_Name) ); CREATE TABLE Manufacturer ( Manufacturer_Name varchar(50) NOT NULL, PRIMARY KEY (Manufacturer_Name) ); CREATE TABLE Sold ( PID int NOT NULL, Quantity INT NOT NULL, Store_Number int NOT NULL, _Date Date NOT NULL, PRIMARY KEY (PID, Store_Number, _Date) ); CREATE TABLE Discount ( PID int NOT NULL, Discount_Price decimal(12,2) NOT NULL, _Date date NOT NULL, PRIMARY KEY (PID, _Date) ); CREATE TABLE Business_Day ( _Date Date NOT NULL, Percent_Discount decimal(2,2), is_savings_day boolean, PRIMARY KEY (_Date) ); CREATE TABLE Discount_Day ( _Date Date NOT NULL, PRIMARY KEY (_Date) ); ALTER TABLE Managed_By ADD FOREIGN KEY (User_Name) REFERENCES _User (User_Name), ADD FOREIGN KEY (Store_Number) REFERENCES Store (Store_Number); ALTER TABLE Product_Category ADD FOREIGN KEY (PID) REFERENCES Product (PID), ADD FOREIGN KEY (Category_Name) REFERENCES Category (Category_Name); ALTER TABLE Product ADD FOREIGN KEY (Manufacturer_Name) REFERENCES Manufacturer (Manufacturer_Name); ALTER TABLE Store ADD FOREIGN KEY (City_Name, _State) REFERENCES City (City_Name, _State); -- ADD FOREIGN KEY (State) REFERENCES City (State); ALTER TABLE Discount ADD FOREIGN KEY (PID) REFERENCES Product (PID), ADD FOREIGN KEY (_Date) REFERENCES Discount_Day (_Date); ALTER TABLE Sold ADD FOREIGN KEY (PID) REFERENCES Product (PID), ADD FOREIGN KEY (Store_Number) REFERENCES Store (Store_Number), ADD FOREIGN KEY (_Date) REFERENCES Business_Day (_Date); INSERT INTO Discount_Day VALUES ('2021-06-01'), ('2021-06-02'), ('2021-06-03'); INSERT INTO Business_Day VALUES ('2021-06-01', 0.00, FALSE), ('2021-06-02', 0.10, FALSE), ('2021-06-03', 0.00, TRUE), ('2021-06-04', 0.10, TRUE), ('2021-06-05', 0.20, TRUE), ('2021-06-06', 0.00, FALSE), ('2021-06-07', 0.00, FALSE), ('2021-06-08', 0.00, FALSE), ('2021-06-09', 0.00, FALSE), ('2021-06-10', 0.00, FALSE), ('2021-06-11', 0.00, FALSE), ('2021-06-12', 0.00, FALSE), ('2021-06-13', 0.00, FALSE), ('2021-06-14', 0.00, FALSE), ('2021-06-15', 0.00, FALSE); INSERT INTO City VALUES ("Newyork", "NY",200000000), ("Houston", "TX",500000000), ("Columbia", "DC",100000000); INSERT INTO Store VALUES (1, "grand_showcase", "1222334556", "Delta Avenue", "Houston", "TX"), (2, "not_grand_showcase", "138889922", "Systems Boulevard ", "Columbia", "DC"), (3, "not_grand_showcase", "4555667788", "Daddison Avenue ", "Newyork", "NY"); INSERT INTO _User VALUES ("mmikail3", "12345", "Sales_manager"), ("zwang3205", "000000", "Sales_manager"), ("nnaik34", "12345", "Marketer"); INSERT INTO Managed_By VALUES ("mmikail3", 1), ("mmikail3", 2), ("zwang3205", 3); INSERT INTO Manufacturer VALUES ("Kevlar"), ("Boeng"), ("Royal Dutch"), ("Tippy"); INSERT INTO Category VALUES ("Consumable"), ("Parts"), ("Luxury"), ("Other"); INSERT INTO Product VALUES (1, "Tyroid", 12.3, "Kevlar"), (2, "Ball joint", 14.6, "Royal Dutch"), (3, "Oil", 30.3, "Tippy"); INSERT INTO Discount VALUES (1, 5.0, '2021-06-01'), (2, 4.0, '2021-06-02'), (3, 5.0, '2021-06-03'), (1, 4.0, '2021-06-02'); INSERT INTO Product_Category VALUES (1, "Consumable"), (1, "Parts"), (2, "Luxury"), (2, "Consumable"), (3, "Luxury"), (3, "Parts"); INSERT INTO Sold VALUES (1, 20, 2, '2021-06-01'), (2, 200, 1, '2021-06-02'), (3, 50, 1, '2021-06-03'), (2, 500, 3, '2021-06-04'), (3, 5, 2, '2021-06-05'), (1, 20, 2, '2021-06-06'), (2, 200, 1, '2021-06-07'), (3, 80, 1, '2021-06-08'), (2, 500, 3, '2021-06-09'), (3, 5, 2, '2021-06-10'), (1, 20, 2, '2021-06-11'), (2, 200, 1, '2021-06-12'), (1, 50, 1, '2021-06-13'), (2, 500, 3, '2021-06-14'), (3, 5, 2, '2021-06-15'); -- Report 9: Main Report SQL Query WITH CAT_SC AS (SELECT C.Category_name, SUM(S.Quantity) AS Sold_at_Showcase_Store FROM Category AS C, Sold As S, Product_Category As PC, Store AS SL WHERE S.PID = PC.PID AND PC.Category_Name=C.Category_Name AND SL.Store_type="grand_showcase" AND SL.Store_Number = S.Store_Number GROUP BY C.Category_name DESC) WITH CAT_NON_SC AS (SELECT C.Category_name, SUM(S.Quantity) AS Sold_at_non_Showcase_Store FROM Category AS C, Sold As S, Product_Category As PC, Store AS SL WHERE S.PID = PC.PID AND PC.Category_Name=C.Category_Name AND SL.Store_type="not_grand_showcase" AND SL.Store_Number = S.Store_Number GROUP BY C.Category_name DESC) SELECT CAT_SC.Category_name As Category_name, Sold_at_Showcase_Store, Sold_at_non_Showcase_Store,(Sold_at_Showcase_Store - Sold_at_non_Showcase_Store) AS Diff FROM CAT_SC LEFT JOIN CAT_NON_SC ON CAT_SC.Category_name = CAT_NON_SC.Category_name ORDER bY Diff DESC, Category_name ASC; stop] SELECT CAT_SC.Category_name As Category_name, Sold_at_Showcase_Store, Sold_at_non_Showcase_Store,(Sold_at_Showcase_Store - Sold_at_non_Showcase_Store) AS Diff FROM (SELECT C.Category_name, SUM(S.Quantity) AS Sold_at_Showcase_Store FROM Category AS C, Sold As S, Product_Category As PC, Store AS SL WHERE S.PID = PC.PID AND PC.Category_Name=C.Category_Name AND SL.Store_type="grand_showcase" AND SL.Store_Number = S.Store_Number GROUP BY C.Category_name DESC) CAT_SC LEFT JOIN (SELECT C.Category_name, SUM(S.Quantity) AS Sold_at_non_Showcase_Store FROM Category AS C, Sold As S, Product_Category As PC, Store AS SL WHERE S.PID = PC.PID AND PC.Category_Name=C.Category_Name AND SL.Store_type="not_grand_showcase" AND SL.Store_Number = S.Store_Number GROUP BY C.Category_name DESC) CAT_NON_SC ON CAT_SC.Category_name = CAT_NON_SC.Category_name ORDER bY Diff DESC, Category_name ASC;