Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- PRAGMA foreign_keys = ON;
- DROP TABLE IF EXISTS PurchasingOrderItems;
- DROP TABLE IF EXISTS CustomerOrderItems;
- DROP TABLE IF EXISTS SupplierItems;
- DROP TABLE IF EXISTS WarehouseItems;
- DROP TABLE IF EXISTS PurchasingOrder;
- DROP TABLE IF EXISTS Suppliers;
- DROP TABLE IF EXISTS MadeOrders;
- DROP TABLE IF EXISTS Items;
- DROP TABLE IF EXISTS WarehouseLocation;
- DROP TABLE IF EXISTS Customers;
- DROP TABLE IF EXISTS CustomerOrders;
- -- HERE CREATE TABLES DEFINITIONS -- COMPILE ENTIRE SCRIPT ON https://kripken.github.io/sql.js/GUI/
- CREATE TABLE Suppliers (SuppliersCode INTEGER PRIMARY KEY,
- SupplierName TEXT,
- Country TEXT,
- City TEXT,
- Code INTEGER,
- Email TEXT);
- CREATE TABLE Items (ItemsCode INTEGER PRIMARY KEY,
- Name TEXT,
- Details TEXT,
- ItemCode FLOAT);
- CREATE TABLE PurchasingOrder (PONumber INTEGER PRIMARY KEY,
- SupplierCode INTEGER,
- PaidDate TEXT,
- RecivedDate TEXT
- );
- CREATE TABLE WarehouseLocation (Location INTEGER PRIMARY KEY);
- CREATE TABLE CustomerOrders (CustomerOrderNumber INTEGER PRIMARY KEY,
- PaidDate TEXT,
- OutgoingDate TEXT);
- CREATE TABLE Customers(CustomerCode INTEGER PRIMARY KEY,
- CustomerName TEXT,
- Country TEXT,
- City TEXT,
- Street TEXT,
- PhoneNumber TEXT,
- Email TEXT);
- CREATE TABLE SupplierItems (SupplierCode INTEGER,
- ItemCode INTEGER,
- ItemPrice FLOAT,
- PRIMARY KEY (SupplierCode, ItemCode),
- FOREIGN KEY (SupplierCode) REFERENCES Suppliers (SuppliersCode),
- FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
- );
- CREATE TABLE PurchasingOrderItems (PurchasingOrderNumber INTEGER,
- ItemCode INTEGER,
- ItemQuantity FLOAT,
- PRIMARY KEY (PurchasingOrderNumber, ItemCode),
- FOREIGN KEY (PurchasingOrderNumber) REFERENCES PurchasingOrder (PONumber),
- FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
- );
- CREATE TABLE WarehouseItems (Location INTEGER,
- ItemCode INTEGER,
- ItemQuantity FLOAT,
- SupplierCode INTEGER,
- PRIMARY KEY (Location, ItemCode),
- FOREIGN KEY (Location) REFERENCES WarehouseLocation (Location),
- FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
- );
- CREATE TABLE CustomerOrderItems (CustomerOrderNumber INTEGER,
- ItemCode TEXT,
- ItemQuantity TEXT,
- PRIMARY KEY (CustomerOrderNumber, ItemCode)
- FOREIGN KEY (CustomerOrderNumber) REFERENCES CustomerOrders(CustomerOrderNumber)
- FOREIGN KEY (ItemCode) REFERENCES Items(ItemsCode)
- );
- CREATE TABLE MadeOrders (CustomerCode INTEGER,
- CustomerOrderNumber INTEGER,
- PRIMARY KEY (CustomerCode, CustomerOrderNumber)
- FOREIGN KEY (CustomerCode) REFERENCES Customers(CustomerCode)
- FOREIGN KEY (CustomerOrderNumber) REFERENCES CustomerOrders(CustomerOrderNumber)
- );
- -- HERE CREATE TABLES DEFINITIONS
- --INPUT DATA FROM SESSION 1
- INSERT INTO Suppliers VALUES( 1, "Sandvik", "Norway", "Sandviken", 555666, "sandvik@gazeta.pl" );
- INSERT INTO Suppliers VALUES( 2, "CocaCola", "USA", "New York", 888333, "coke@gazeta.pl" );
- INSERT INTO Suppliers VALUES( 3, "KFC", "USA", "Chicago", 999222, "kfc@gazeta.pl" );
- INSERT INTO Items VALUES( 1, "Chicken", "Tasty", 5.5 );
- INSERT INTO Items VALUES( 2, "Leg", "Delicious", 4 );
- INSERT INTO Items VALUES( 3, "Omelette", "Ordinary", 3 );
- INSERT INTO Items VALUES( 4, "Pizza", "Ugly", 12.5 );
- INSERT INTO Items VALUES( 5, "Fanta", "When Thirsty", 2.4 );
- INSERT INTO Items VALUES( 6, "Cola", "Refreshing", 3 );
- INSERT INTO Items VALUES( 7, "Screw", "Decent", 5.5 );
- INSERT INTO Items VALUES( 8, "Scissors", "Indecent", 7.5 );
- INSERT INTO Items VALUES( 9, "Nail", "Firm", 8.5 );
- INSERT INTO Items VALUES(10, "Decanter", "Spacious", 10 );
- INSERT INTO PurchasingOrder VALUES( 1, 1, NULL, "01.01.2017");
- INSERT INTO PurchasingOrder VALUES( 2, 1, "02.01.2017", "02.01.2017");
- INSERT INTO PurchasingOrder VALUES( 3, 1, "02.01.2017", "03.01.2017");
- INSERT INTO PurchasingOrder VALUES( 4, 2, "02.01.2017", "04.01.2017");
- INSERT INTO PurchasingOrder VALUES( 5, 2, "02.01.2017", "05.01.2017");
- INSERT INTO PurchasingOrder VALUES( 6, 3, "02.01.2017", "06.01.2017");
- INSERT INTO PurchasingOrder VALUES( 7, 3, "02.01.2017", "07.01.2017");
- INSERT INTO PurchasingOrder VALUES( 8, 3, "02.01.2017", "08.01.2017");
- INSERT INTO PurchasingOrder VALUES( 9, 3, "09.01.2017", NULL);
- --INPUT DATA FROM SESSION 2
- INSERT INTO SupplierItems VALUES( 1, 7, 3 );
- INSERT INTO SupplierItems VALUES( 1, 8, 5 );
- INSERT INTO SupplierItems VALUES( 1, 9, 6.5 );
- INSERT INTO SupplierItems VALUES( 1,10, 7 );
- INSERT INTO SupplierItems VALUES( 2,10, 8 );
- INSERT INTO SupplierItems VALUES( 2, 5, 2 );
- INSERT INTO SupplierItems VALUES( 2, 6, 2.5 );
- INSERT INTO SupplierItems VALUES( 3, 7, 2 );
- INSERT INTO SupplierItems VALUES( 3, 8, 4 );
- INSERT INTO SupplierItems VALUES( 3, 9, 6 );
- INSERT INTO SupplierItems VALUES( 3,10, 5 );
- INSERT INTO SupplierItems VALUES( 3, 5, 1 );
- INSERT INTO SupplierItems VALUES( 3, 6, 2 );
- INSERT INTO SupplierItems VALUES( 3, 1, 3 );
- INSERT INTO SupplierItems VALUES( 3, 2, 2 );
- INSERT INTO SupplierItems VALUES( 3, 3, 2.2 );
- INSERT INTO SupplierItems VALUES( 3, 4, 8 );
- INSERT INTO PurchasingOrderItems VALUES(1, 7, 20);
- INSERT INTO PurchasingOrderItems VALUES(1, 8, 50);
- INSERT INTO PurchasingOrderItems VALUES(1, 9, 15);
- INSERT INTO PurchasingOrderItems VALUES(1, 10, 10);
- INSERT INTO PurchasingOrderItems VALUES(2, 8, 35);
- INSERT INTO PurchasingOrderItems VALUES(3, 7, 40);
- INSERT INTO PurchasingOrderItems VALUES(3, 10, 80);
- INSERT INTO PurchasingOrderItems VALUES(4, 5, 10);
- INSERT INTO PurchasingOrderItems VALUES(4, 6, 30);
- INSERT INTO PurchasingOrderItems VALUES(5, 10, 70);
- INSERT INTO PurchasingOrderItems VALUES(6, 2, 20);
- INSERT INTO PurchasingOrderItems VALUES(6, 1, 50);
- INSERT INTO PurchasingOrderItems VALUES(6, 4, 30);
- INSERT INTO PurchasingOrderItems VALUES(7, 3, 15);
- INSERT INTO PurchasingOrderItems VALUES(7, 1, 20);
- INSERT INTO PurchasingOrderItems VALUES(8, 4, 40);
- INSERT INTO PurchasingOrderItems VALUES(9, 3, 30);
- INSERT INTO PurchasingOrderItems VALUES(9, 2, 10);
- INSERT INTO PurchasingOrderItems VALUES(9, 1, 20);
- INSERT INTO PurchasingOrderItems VALUES(9, 4, 25);
- --INPUT DATA FROM SESSION 3
- INSERT INTO WarehouseLocation VALUES( 1 );
- INSERT INTO WarehouseLocation VALUES( 2 );
- INSERT INTO WarehouseLocation VALUES( 3 );
- INSERT INTO WarehouseLocation VALUES( 4 );
- INSERT INTO WarehouseLocation VALUES( 5 );
- INSERT INTO WarehouseItems VALUES( 1, 1, 20, 1);
- INSERT INTO WarehouseItems VALUES( 1, 2, 30, 1);
- INSERT INTO WarehouseItems VALUES( 1, 3, 60, 1);
- INSERT INTO WarehouseItems VALUES( 1, 4, 20, 1);
- INSERT INTO WarehouseItems VALUES( 1, 5, 10, 1);
- INSERT INTO WarehouseItems VALUES( 1, 6, 0, 1);
- INSERT INTO WarehouseItems VALUES( 1, 7, 0, 1);
- INSERT INTO WarehouseItems VALUES( 1, 8, 40, 1);
- INSERT INTO WarehouseItems VALUES( 1, 9, 20, 1);
- INSERT INTO WarehouseItems VALUES( 1, 10, 50, 1);
- INSERT INTO WarehouseItems VALUES( 2, 1, 15, 1);
- INSERT INTO WarehouseItems VALUES( 2, 2, 30, 1);
- INSERT INTO WarehouseItems VALUES( 2, 3, 25, 1);
- INSERT INTO WarehouseItems VALUES( 2, 4, 55, 1);
- INSERT INTO WarehouseItems VALUES( 2, 5, 75, 1);
- INSERT INTO WarehouseItems VALUES( 2, 6, 10, 1);
- INSERT INTO WarehouseItems VALUES( 2, 7, 80, 1);
- INSERT INTO WarehouseItems VALUES( 2, 8, 30, 1);
- INSERT INTO WarehouseItems VALUES( 2, 9, 45, 1);
- INSERT INTO WarehouseItems VALUES( 2, 10, 25, 1);
- INSERT INTO WarehouseItems VALUES( 3, 1, 5, 1);
- INSERT INTO WarehouseItems VALUES( 3, 2, 0, 1);
- INSERT INTO WarehouseItems VALUES( 3, 3, 0, 1);
- INSERT INTO WarehouseItems VALUES( 3, 4, 0, 1);
- INSERT INTO WarehouseItems VALUES( 3, 5, 30, 1);
- INSERT INTO WarehouseItems VALUES( 3, 6, 65, 1);
- INSERT INTO WarehouseItems VALUES( 3, 7, 10, 1);
- INSERT INTO WarehouseItems VALUES( 3, 8, 45, 1);
- INSERT INTO WarehouseItems VALUES( 3, 9, 50, 1);
- INSERT INTO WarehouseItems VALUES( 3, 10, 5, 1);
- INSERT INTO WarehouseItems VALUES( 4, 1, 35, 1);
- INSERT INTO WarehouseItems VALUES( 4, 2, 20, 1);
- INSERT INTO WarehouseItems VALUES( 4, 3, 45, 1);
- INSERT INTO WarehouseItems VALUES( 4, 4, 75, 1);
- INSERT INTO WarehouseItems VALUES( 4, 5, 80, 1);
- INSERT INTO WarehouseItems VALUES( 4, 6, 0, 1);
- INSERT INTO WarehouseItems VALUES( 4, 7, 0, 1);
- INSERT INTO WarehouseItems VALUES( 4, 8, 0, 1);
- INSERT INTO WarehouseItems VALUES( 4, 9, 15, 1);
- INSERT INTO WarehouseItems VALUES( 4, 10, 20, 1);
- INSERT INTO WarehouseItems VALUES( 5, 1, 10, 1);
- INSERT INTO WarehouseItems VALUES( 5, 2, 65, 1);
- INSERT INTO WarehouseItems VALUES( 5, 3, 20, 1);
- INSERT INTO WarehouseItems VALUES( 5, 4, 65, 1);
- INSERT INTO WarehouseItems VALUES( 5, 5, 75, 1);
- INSERT INTO WarehouseItems VALUES( 5, 6, 0, 1);
- INSERT INTO WarehouseItems VALUES( 5, 7, 45, 1);
- INSERT INTO WarehouseItems VALUES( 5, 8, 35, 1);
- INSERT INTO WarehouseItems VALUES( 5, 9, 25, 1);
- INSERT INTO WarehouseItems VALUES( 5, 10, 10, 1);
- --INPUT DATA FROM SESSION 4
- INSERT INTO Customers VALUES ( 1, 'Mitch Ducanon', 'USA', 'Miami', 'Ocean Street', '+1 8376456', 'mitch@hotmail.com');
- INSERT INTO Customers VALUES ( 2, 'Deynn', 'Poland', 'Sosnowiec', 'Na Ostatnim Groszu', '+48 398723', 'deynn@onet.pl');
- INSERT INTO Customers VALUES ( 3, 'Angela Merkel', 'Germany', 'Berlin', 'Bullowstrasse', '+49 2134908', 'angie@spiegel.com');
- INSERT INTO Customers VALUES ( 4, 'Patrick Bruel', 'France', 'Pari', 'Frog Street', '+33 8376456', 'bruel@hotmail.com');
- INSERT INTO Customers VALUES ( 5, 'Gianni Versace', 'Italy', 'Rome', 'Macaroni Street', '+39 8376456', 'gianni@hotmail.com');
- INSERT INTO CustomerOrders VALUES ( 1, '4.09.2017', '12.09.2017');
- INSERT INTO CustomerOrders VALUES ( 2, '1.09.2017', '14.09.2017');
- INSERT INTO CustomerOrders VALUES ( 3, '5.09.2017', '18.09.2017');
- INSERT INTO CustomerOrders VALUES ( 4, NULL, NULL);
- INSERT INTO CustomerOrders VALUES ( 5, '10.09.2017', '15.09.2017');
- INSERT INTO CustomerOrders VALUES ( 6, '8.09.2017', NULL);
- INSERT INTO CustomerOrders VALUES ( 7, '4.09.2017', '14.09.2017');
- INSERT INTO CustomerOrders VALUES ( 8, '2.09.2017', '13.09.2017');
- --INPUT DATA FROM SESSION 5
- INSERT INTO CustomerOrderItems VALUES ( 1, 2, 80 );
- INSERT INTO CustomerOrderItems VALUES ( 1, 10, 30 );
- INSERT INTO CustomerOrderItems VALUES ( 1, 3, 50 );
- INSERT INTO CustomerOrderItems VALUES ( 2, 6, 70 );
- INSERT INTO CustomerOrderItems VALUES ( 2, 5, 40 );
- INSERT INTO CustomerOrderItems VALUES ( 3, 8, 90 );
- INSERT INTO CustomerOrderItems VALUES ( 3, 7, 120 );
- INSERT INTO CustomerOrderItems VALUES ( 4, 9, 100 );
- INSERT INTO CustomerOrderItems VALUES ( 4, 3, 30 );
- INSERT INTO CustomerOrderItems VALUES ( 5, 2, 40 );
- INSERT INTO CustomerOrderItems VALUES ( 5, 1, 35 );
- INSERT INTO CustomerOrderItems VALUES ( 5, 4, 60 );
- INSERT INTO CustomerOrderItems VALUES ( 6, 10, 70 );
- INSERT INTO CustomerOrderItems VALUES ( 7, 1, 30 );
- INSERT INTO CustomerOrderItems VALUES ( 7, 4, 65 );
- INSERT INTO CustomerOrderItems VALUES ( 7, 6, 50 );
- INSERT INTO CustomerOrderItems VALUES ( 7, 8, 25 );
- INSERT INTO CustomerOrderItems VALUES ( 8, 2, 80 );
- INSERT INTO MadeOrders VALUES (1, 8);
- INSERT INTO MadeOrders VALUES (1, 7);
- INSERT INTO MadeOrders VALUES (3, 1);
- INSERT INTO MadeOrders VALUES (3, 6);
- INSERT INTO MadeOrders VALUES (2, 3);
- INSERT INTO MadeOrders VALUES (5, 2);
- INSERT INTO MadeOrders VALUES (4, 4);
- INSERT INTO MadeOrders VALUES (1, 5);
- --DISPLAY ALL TABLES
- SELECT * FROM Suppliers;
- SELECT * FROM Items;
- SELECT * FROM PurchasingOrder;
- SELECT * FROM SupplierItems;
- SELECT * FROM PurchasingOrderItems;
- SELECT * FROM WarehouseLocation;
- SELECT * FROM WarehouseItems;
- SELECT * FROM Customers;
- SELECT * FROM CustomerOrders;
- SELECT * FROM CustomerOrderItems;
- SELECT * FROM MadeOrders;
- -- DISPLAY PURCHASE ORDERS NUMBERS, RELATED ITEM NAMES WITH PROCES AND QUANTITY
- -- DISPLAY PURCHASE ORDERS NUMBERS AND RELATED ITEM NAMES WITH THEIR VALUES
- -- WHAT IS SELLING VALUE OF EVERY ITEM IN ALL WAREHOUSE(ITEM NAME - PRICE)
- -- DISPLAY SELLING PRICE FOR EVERY CUSTOMERORDER
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement