API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 22nd, 2019
116
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 10.44 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE Clothes (
  2.     ClothingID INT NOT NULL,
  3.     Brand VARCHAR(20) NOT NULL,
  4.     Model VARCHAR(20) NOT NULL,
  5.     Country VARCHAR(20) NOT NULL,
  6.     ArticleNr INT NOT NULL,
  7.     Weight INT NOT NULL, # Grams
  8.     CSize VARCHAR(20) NOT NULL,
  9.     SalesPrice INT NOT NULL,
  10.     SupplierPrice INT NOT NULL,
  11.     Color VARCHAR(20) NOT NULL,
  12.     AmmountLeft INT NOT NULL,
  13.     Category VARCHAR(20) NOT NULL,
  14.     AmmountSold INT NOT NULL,
  15.  
  16.     PRIMARY KEY(ClothingID)
  17. );
  18. # Det är ju egentligen ingen skillnad på ClothingID och ArticleNr, behöver vi båda?
  19. INSERT INTO Clothes VALUES
  20.                             (0, 'Ralph Lauren', 'Oxford Shirt', 'United States', 0, 300, 'L', 900, 500, 'White', 10, 'Shirts Men', 0),
  21.                            (1,'Gant', 'Collage','United Kingdom',1, 600, 'L', 1100, 750, 'Red', 15, 'Shits Women', 0),
  22.                            (2, 'Gant', 'Oxford Shirt', 'United Kingdom', 2, 400, 'M', 999 , 600, 'Blue', 15, 'Shirts Men', 0),
  23.                            (3, 'Sail Racing', 'T-shirt', 'United Kingdom', 3, 500, 'S', 500, 250, 'Black', 10, 'Shirts Women', 0),
  24.                            (4, 'Ralph Lauren', 'T-Shirt', 'United States', 4, 600, 'XL', 800, 500, 'White', 10, 'Shirts Women', 0),
  25.                            (5, 'Sail Racing', 'Oxford Shirt', 'United Kingdom', 5, 900, 'M', 900, 500, 'White', 10, 'Shirts Men', 0),
  26.                            (6, 'Ralph Lauren', 'Sweater', 'United States', 6, 1010, 'L', 1200, 700, 'Blue', 5, 'Sweater Men', 0),
  27.                            (7,'Gant', 'Sweater','United Kingdom',7, 1200, 'XL', 1300, 700, 'Green', 5, 'Sweater Women', 0),
  28.                            (8, 'Gant', 'Jacket', 'United Kingdom', 8, 1400, 'M', 3700 , 2500, 'Black', 15, 'Jacket Men', 0),
  29.                            (9, 'Sail Racing', 'T-shirt', 'United States', 9, 500, 'S', 700, 300, 'Yellow', 15, 'Shirts Women', 0),
  30.                            (10, 'Ralph Lauren', 'Collage' , 'United States', 10, 1100, 'XL', 1500, 900, 'White', 10, 'Sweater Men', 0);
  31.  
  32. SELECT * FROM Clothes;
  33.  
  34. CREATE TABLE Orders (
  35.     OrderNr INT NOT NULL,
  36.     PNr VARCHAR(20) NOT NULL, # Social security number
  37.     TotalSum INT NOT NULL,
  38.     DeliveryHomeAdress VARCHAR(30) NOT NULL,
  39.     DeliveryPostAdress VARCHAR(20) NOT NULL,
  40.     Country VARCHAR(20) NOT NULL,
  41.     TotalWeight FLOAT NOT NULL,
  42.     OrderDate DATE NOT NULL,
  43.     OrderTime TIME NOT NULL,
  44.     DiscountID INT NOT NULL,
  45.     ClothingID INT NOT NULL,
  46.  
  47.     PRIMARY KEY(OrderNr, PNr),
  48.     FOREIGN KEY (OrderNr) REFERENCES Revenue(OrderNr),
  49.     FOREIGN KEY (PNr) REFERENCES Customer(PNr),
  50.     FOREIGN KEY (DiscountID) REFERENCES Discount(DiscountID)
  51. );
  52.  
  53. INSERT INTO Orders VALUES
  54.                           (0, '9603074833', 900, 'Gamla Infartsvägen 3b', '371 41 Karlskrona', 'Sweden', 300.0, NOW(), NOW(), 0, 0),
  55.                           (1, '9804208172', 2000, 'Valhallavägen 2', '371 40 Karlskrona', 'Sweden', 1500.0, NOW(),NOW(), 0, 1),
  56.                           (2, '9402208182', 500, 'Infartsvägen 3b', '371 41 Karlskrona', 'Sweden', 400.0, NOW(), NOW(), 0, 2),
  57.                           (3, '9803074823', 5000, 'Lotsvägen 7', '541 55 Alingsas', 'Sweden', 2000.0, '2019-04-18','12:04:18', 0, 3),
  58.                           (4, '9209234823', 1100, 'Arklimästaregatan 25b' , '371 42 Karlskrona', 'Sweden', 1100.0, NOW(), NOW(), 0, 4),
  59.                           (5, '9012075431', 900, 'Gamla Djuret 31b', '371 45 Karlskrona', 'Sweden', 500.0, NOW(), NOW(), 0, 5),
  60.                           (6, '0503071234',15000, 'Andralang 2b', '343 41 Stockholm', 'Sweden', 5000.0, NOW(), NOW(), 0, 6),
  61.                           (7, '0211278723', 2200, 'Seglaregatan 3', '441 51 Alingsas', 'Sweden', 1500.0, NOW(), NOW(), 0, 7);
  62.  
  63. SELECT * FROM Orders;
  64.  
  65. CREATE TABLE Revenue (
  66.     OrderNr INT NOT NULL,
  67.     TotalSum INT NOT NULL,
  68.     TransortationCost INT NOT NULL,
  69.     SupplierPrice INT NOT NULL,
  70.     LastReturnDate DATE NOT NULL,
  71.  
  72.     PRIMARY KEY(OrderNr)
  73. );
  74.  
  75. INSERT INTO Revenue VALUES
  76.                            (0, 900, 50, 550, DATE_ADD(NOW(), INTERVAL 14 DAY)),
  77.                            (1, 2000, 70, 1500, DATE_ADD(NOW(), INTERVAL 14 DAY)),
  78.                            (2, 500, 50, 550, DATE_ADD(NOW(), INTERVAL 14 DAY)),
  79.                            (3, 5000, 90, 550, DATE_ADD(NOW(), INTERVAL 14 DAY)),
  80.                            (4, 1100, 70, 550, DATE_ADD(NOW(), INTERVAL 14 DAY)),
  81.                            (5, 900, 50, 550, DATE_ADD(NOW(), INTERVAL 14 DAY)),
  82.                            (6, 15000, 150, 550, DATE_ADD(NOW(), INTERVAL 14 DAY)),
  83.                            (7, 2200, 70, 550, DATE_ADD(NOW(), INTERVAL 14 DAY));
  84.  
  85. SELECT * FROM Revenue;
  86.  
  87. CREATE TABLE Customer (
  88.     PNr VARCHAR(20) NOT NULL,
  89.     FName VARCHAR(20) NOT NULL,
  90.     LName VARCHAR(20) NOT NULL,
  91.     MobileNr VARCHAR(20) NOT NULL,
  92.     EMail VARCHAR(30) NOT NULL,
  93.     Gender VARCHAR(20) NOT NULL,
  94.     HomeAdress VARCHAR(30) NOT NULL,
  95.     PostAdress VARCHAR(20) NOT NULL,
  96.     ShoppedSum FLOAT NOT NULL,
  97.  
  98.     CONSTRAINT PK
  99.     PRIMARY KEY (PNr)
  100. );
  101.  
  102. INSERT INTO Customer VALUES
  103.                             ('9603074833', 'Anton', 'Karlsson', '0723535592', 'antonamkarlsson@hotmail.com', 'Male', 'Gamla Infartsvägen 3b', '371 41 Karlskrona', 900),
  104.                             ('9804208172', 'Emil', 'Einerskog', '0707453881', 'emil@einerskog.se', 'Male', 'Valhallavägen 2', '371 40 Karlskrona', 1200),
  105.                             ('9402208182', 'Lars', 'Svensson', '0708233212', 'larssvensson@hotmail.com', 'Male', 'Infartsvägen 3b', '371 41 Karlskrona', 500),
  106.                             ('9803074823', 'Markus', 'Nilsson', '0707083421', 'markusnilsson@hotmail.com', 'Male', 'Lotsvägen 7', '541 55 Alingsas', 5000),
  107.                             ('9209234823', 'Sven', 'Svensson', '0745621212', 'svensvensson@hotmail.com', 'Male', 'Arklimästaregatan 25b' , '371 42 Karlskrona', 1100),
  108.                             ('9012075431', 'John', 'Ren', '070732142122', 'johnren@hotmail.com', 'Male', 'Gamla Djuret 31b', '371 45 Karlskrona', 900),
  109.                             ('0503071234', 'Lisa', 'Axelsson', '07235421592', 'lisaaxelsson@hotmail.com', 'Female', 'Andralang 2b', '343 41 Stockholm', 15000),
  110.                             ('0211278723', 'Felicia', 'Abrahamsson', '0723532123', 'FeliciaAbrahamsson@hotmail.com', 'Female', 'Seglaregatan 3', '441 51 Alingsas', 900);
  111.  
  112. SELECT * FROM Customer;
  113.  
  114. CREATE TABLE Discount (
  115.     DiscountID INT NOT NULL,
  116.     CodeName VARCHAR(20) NOT NULL,
  117.     AmountDiscount FLOAT NOT NULL,
  118.     StartDate DATE NOT NULL,
  119.     EndDate DATE NOT NULL,
  120.  
  121.     PRIMARY KEY (DiscountID)
  122. );
  123.  
  124. INSERT INTO Discount VALUES
  125.                             (0, 'Easter Deal', 0.8, "2019-04-18", "2019-04-22"),
  126.                             (1, 'Summer Deal', 0.6, "2019-06-10", "2019-08-12"),
  127.                             (2, 'Spring Deal', 0.7, "2019-03-18", "2019-04-14"),
  128.                             (3, 'New Customer Deal', 0.9, NOW(),DATE_ADD(NOW(), INTERVAL 1 DAY ));
  129.  
  130. SELECT * FROM Discount;
  131.  
  132. # Remade with BoughtClothesID as primary key
  133. CREATE TABLE BoughtClothes (
  134.     BoughtClothesID INT NOT NULL,
  135.     OrderNr INT NOT NULL,
  136.     ClothingID INT NOT NULL,
  137.  
  138.     PRIMARY KEY (BoughtClothesID),
  139.     FOREIGN KEY (ClothingID) REFERENCES Clothes(ClothingID),
  140.     FOREIGN KEY (OrderNr) REFERENCES Orders(OrderNr)
  141. );
  142.  
  143. DELIMITER //
  144. CREATE TRIGGER AddSale AFTER INSERT ON BoughtClothes FOR EACH ROW
  145.     BEGIN
  146.         UPDATE Clothes SET AmmountSold = AmmountSold + 1 WHERE ClothingID = NEW.ClothingID;
  147.     END //
  148. DELIMITER ;
  149.  
  150. DELIMITER //
  151. CREATE TRIGGER RemoveStock AFTER INSERT ON BoughtClothes FOR EACH ROW
  152.     BEGIN
  153.         UPDATE Clothes SET AmmountLeft = AmmountLeft - 1 WHERE ClothingID = NEW.ClothingID;
  154.     END //
  155. DELIMITER ;
  156.  
  157. DELIMITER //
  158. CREATE TRIGGER CombinedTrigger AFTER INSERT ON BoughtClothes FOR EACH ROW
  159.     BEGIN
  160.         UPDATE Clothes SET AmmountLeft = AmmountLeft - 1, AmmountSold = AmmountSold + 1 WHERE ClothingID = NEW.ClothingID;
  161.     END //
  162. DELIMITER ;
  163.  
  164. DROP TRIGGER AddSale;
  165. DROP TRIGGER RemoveStock;
  166. DROP TRIGGER CombinedTrigger;
  167.  
  168. INSERT INTO BoughtClothes VALUES(0, 0, 0),(1, 1, 0),(2, 1, 1),(3, 2, 3),(4, 3, 7),(5, 3, 8),(6, 4, 1),(7, 5, 0),(8, 6, 7),(9, 6, 7),(10, 6, 7),(11, 6, 8),(12, 6, 8),(13, 6, 8),(14, 7, 9),(15, 7, 10);
  169.  
  170. SELECT * FROM BoughtClothes;
  171. SELECT * FROM Clothes;
  172.  
  173. #VIEWS
  174. CREATE VIEW CheapClothes AS
  175.     SELECT * FROM Clothes WHERE SalesPrice < 700;
  176.  
  177. SELECT * FROM CheapClothes;
  178.  
  179. DROP VIEW CheapClothes;
  180.  
  181. CREATE VIEW BestSeller AS
  182.     SELECT * FROM Clothes ORDER BY AmmountSold DESC LIMIT 10;
  183.  
  184. SELECT * FROM BestSeller;
  185.  
  186. DROP VIEW BestSeller;
  187.  
  188. #CustomerView
  189. CREATE VIEW CustomerView AS
  190.   SELECT DISTINCT Brand, Model, ArticleNr, SalesPrice, Color, AmmountLeft, Weight, CSize, Category, FName, LName, Orders.PNr, MobileNr, EMail,Gender, HomeAdress, PostAdress, TotalSum, TotalWeight, OrderDate, OrderTime, DiscountID FROM Clothes INNER JOIN (Orders,Customer);
  191.  
  192. SELECT * FROM CustomerView;
  193. DROP VIEW CustomerView;
  194.  
  195. #BuyerView, måste kontrollera lagerstatus också! (<5 varor)
  196. CREATE VIEW BuyerView AS
  197.     SELECT DISTINCT CONCAT(Brand, ", ", Model, ", ", ArticleNr, ", ", Weight, ", ", SupplierPrice, ", ", AmmountLeft) AS Garment,
  198.            CONCAT(c.FName, " ", c.LName, ", ", c.PNr, ", ", c.MobileNr, ", ", c.EMail, ", ", c.Gender, ", ", c.HomeAdress, ", ", c.PostAdress, ", ", c.ShoppedSum) AS Customer,
  199.            CONCAT(o.OrderNr, ", ", o.TotalSum, ", ", o.DeliveryHomeAdress, ", ", o.TotalWeight, ", ", o.OrderDate, ", ", o.DiscountID) AS OrderBill
  200.            FROM Clothes, Customer c, Orders o WHERE Clothes.ClothingID = o.ClothingID; # Vi måste hitta rätt begränsing för vyerna :)
  201.  
  202. DROP VIEW BuyerView;
  203. SELECT * FROM BuyerView;
  204.  
  205. #Procedur för mellandagsrea, genom att skicka in t.ex 0.8 ges 20% rabatt.
  206. DELIMITER //
  207. CREATE PROCEDURE AddSale
  208. (IN Sale FLOAT)
  209. BEGIN
  210.     UPDATE Clothes SET SalesPrice = SalesPrice * Sale;
  211. END //
  212. DELIMITER ;
  213.  
  214. CALL AddSale(0.8);
  215. SELECT * FROM Clothes;
  216. CALL AddSale(1.25);
  217. SELECT * FROM Clothes;
  218.  
  219. #CorporateView ska göras
  220. CREATE VIEW Corporateview AS
  221.   SELECT DISTINCT ClothingID, AmmountSold, SalesPrice, Revenue.TotalSum FROM Clothes, Revenue;
  222.  
  223. SELECT * FROM Corporateview;
  224. DROP VIEW Corporateview;
  225.  
  226. DELIMITER //
  227. CREATE TRIGGER Revenueadder AFTER INSERT ON BoughtClothes FOR EACH ROW
  228.     BEGIN
  229.         UPDATE Revenue SET TotalSum = 0;
  230.     END //
  231. DELIMITER ;
  232.  
  233. DROP TRIGGER Revenueadder;
  234.  
  235. DROP TABLE BoughtClothes;
  236. DROP TABLE Clothes;
  237. DROP TABLE Orders;
  238. DROP TABLE Revenue;
  239. DROP TABLE Customer;
  240. DROP TABLE Discount;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!