SQLize | PHPize | SQLtest

CREATE TABLE Bill_of_Materials ( SupplierMaterialsID int NOT NULL, Materials_MaterialsID int NOT NULL, Supplier_SupplierID int NOT NULL, Quantity int NOT NULL, UnitPrice decimal(7,2) NOT NULL, CONSTRAINT Bill_of_Materials_pk PRIMARY KEY (SupplierMaterialsID) ); -- Table: Customer CREATE TABLE Customer ( CustomerID int AUTO_INCREMENT NOT NULL, CustomerFirstName varchar(30) NOT NULL, CustomerLastName varchar(30) NOT NULL, CustomerStreet varchar(40) NOT NULL, CustomerCity varchar(30) NOT NULL, CustomerState varchar(30) NOT NULL, CustomerZIP varchar(15) NULL, CustomerPhoneNumber varchar(30) NULL, CustomerEmailAddress varchar(40) NULL, CONSTRAINT Customer_pk PRIMARY KEY (CustomerID) ); -- Table: Customer_Order CREATE TABLE Customer_Order ( OrderID int AUTO_INCREMENT NOT NULL, OrderDate date NOT NULL, CustomerID int NOT NULL, Payment_Status varchar(20) NOT NULL, PaymentMethod varchar(20) NOT NULL, Amount decimal(7,2) NULL, CONSTRAINT Customer_Order_pk PRIMARY KEY (OrderID) ); -- Table: Manufacturer CREATE TABLE Manufacturer ( ManufacturerID int AUTO_INCREMENT NOT NULL, ManufacturerName varchar(30) NOT NULL, ManufacturerStreet varchar(50) NOT NULL, ManufacturerCity varchar(30) NOT NULL, ManufacturerState varchar(30) NOT NULL, ManufacturerZIP int NOT NULL, CONSTRAINT Manufacturer_pk PRIMARY KEY (ManufacturerID) ); -- Table: Manufacturer_Materials CREATE TABLE Manufacturer_Materials ( ManMatID int NOT NULL, Manufacturer_ManufacturerID int NOT NULL, Materials_MaterialsID int NOT NULL, Quantity int NOT NULL, Price int NOT NULL, CONSTRAINT Manufacturer_Materials_pk PRIMARY KEY (ManMatID) ); -- Table: Materials CREATE TABLE Materials ( MaterialsID int NOT NULL, MaterialType varchar(30) NOT NULL, Desc_Origin varchar(50) NOT NULL, Desc_Quality varchar(30) NOT NULL, MaterialName varchar(30) NOT NULL, CONSTRAINT Materials_pk PRIMARY KEY (MaterialsID) ); -- Table: Order_Line CREATE TABLE Order_Line ( Order_LineID int NOT NULL, Product_ProductID int NOT NULL, Order_OrderID int NOT NULL, Quantity int NOT NULL, CONSTRAINT Order_Line_pk PRIMARY KEY (Order_LineID) ); -- Table: Product CREATE TABLE Product ( ProductID int AUTO_INCREMENT NOT NULL, ProductName varchar(50) NOT NULL, ProductType varchar(30) NOT NULL, Desc_Quality varchar(30) NOT NULL, Desc_Sustainabililyt varchar(60) NOT NULL, Desc_Origin varchar(40) NOT NULL, UnitPrice decimal (7,2) NOT NULL, ProductReview int NULL, CONSTRAINT Product_pk PRIMARY KEY (ProductID) ); -- Table: Product_Materials CREATE TABLE Product_Materials ( Product_MaterialsID int NOT NULL, Product_ProductID int NOT NULL, Materials_MaterialsID int NOT NULL, CONSTRAINT Product_Materials_pk PRIMARY KEY (Product_MaterialsID) ); -- Table: Product_line CREATE TABLE Product_Line ( ProductLineID int NOT NULL, Manufacturer_ManufacturerID int NOT NULL, Product_ProductID int NOT NULL, Quantity int NOT NULL, Order_Type varchar(30) NOT NULL, Price decimal (7,2) NOT NULL, PickupDate DATE NOT NULL, CONSTRAINT Product_line_pk PRIMARY KEY (ProductLineID) ); -- Table: Supplier CREATE TABLE Supplier ( SupplierID int AUTO_INCREMENT NOT NULL, SupplierName varchar(30) NOT NULL, SupplierStreet varchar(50) NOT NULL, SupplierCity varchar(30) NOT NULL, SupplierState varchar(30) NOT NULL, SupplierZIP varchar(15) NOT NULL, CONSTRAINT Supplier_pk PRIMARY KEY (SupplierID) ); -- foreign keys -- Reference: Bill_of_Materials_Materials (table: Bill_of_Materials) ALTER TABLE Bill_of_Materials ADD CONSTRAINT Bill_of_Materials_Materials FOREIGN KEY (Materials_MaterialsID) REFERENCES Materials (MaterialsID) ; -- Reference: Bill_of_Materials_Supplier (table: Bill_of_Materials) ALTER TABLE Bill_of_Materials ADD CONSTRAINT Bill_of_Materials_Supplier FOREIGN KEY (Supplier_SupplierID) REFERENCES Supplier (SupplierID) ; -- Reference: Order_Customer (table: Customer_Order) ALTER TABLE Customer_Order ADD CONSTRAINT Order_Customer FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) ; -- Reference: Order_Line_Order (table: Order_Line) ALTER TABLE Order_Line ADD CONSTRAINT Order_Line_Order FOREIGN KEY (Order_OrderID) REFERENCES Customer_Order (OrderID) ; -- Reference: Order_Line_Product (table: Order_Line) ALTER TABLE Order_Line ADD CONSTRAINT Order_Line_Product FOREIGN KEY (Product_ProductID) REFERENCES Product (ProductID) ; -- Reference: Product_Materials_Materials (table: Product_Materials) ALTER TABLE Product_Materials ADD CONSTRAINT Product_Materials_Materials FOREIGN KEY (Materials_MaterialsID) REFERENCES Materials (MaterialsID) ; -- Reference: Product_Materials_Product (table: Product_Materials) ALTER TABLE Product_Materials ADD CONSTRAINT Product_Materials_Product FOREIGN KEY (Product_ProductID) REFERENCES Product (ProductID) ; -- Reference: Product_line_Manufacturer (table: Product_line) ALTER TABLE Product_Line ADD CONSTRAINT Product_line_Manufacturer FOREIGN KEY (Manufacturer_ManufacturerID) REFERENCES Manufacturer (ManufacturerID) ; -- Reference: Product_line_Product (table: Product_line) ALTER TABLE Product_Line ADD CONSTRAINT Product_line_Product FOREIGN KEY (Product_ProductID) REFERENCES Product (ProductID) ; -- Reference: Table_10_Manufacturer (table: Manufacturer_Materials) ALTER TABLE Manufacturer_Materials ADD CONSTRAINT Table_10_Manufacturer FOREIGN KEY (Manufacturer_ManufacturerID) REFERENCES Manufacturer (ManufacturerID) ; -- Reference: Table_10_Materials (table: Manufacturer_Materials) ALTER TABLE Manufacturer_Materials ADD CONSTRAINT Table_10_Materials FOREIGN KEY (Materials_MaterialsID) REFERENCES Materials (MaterialsID) ; INSERT INTO Customer(CustomerFirstName,CustomerLastName,CustomerStreet,CustomerCity,CustomerState,CustomerZIP,CustomerPhoneNumber,CustomerEmailAddress) VALUES ('Enrico', 'Cirieco', 'Wolkammersdreef 67C', 'Maastricht', 'Netherlands', '6216RN', '0633586528', 'enrico.cirieco4@gmail.com'); INSERT INTO Customer(CustomerFirstName,CustomerLastName,CustomerStreet,CustomerCity,CustomerState,CustomerZIP,CustomerPhoneNumber,CustomerEmailAddress) VALUES ('Michele', 'Cirieco', 'Wolkammersdreef 67C', 'Maastricht', 'Netherlands', '6216RN', '06335356528', 'enrico.cirieco4@gmail.com'); INSERT INTO Customer(CustomerFirstName,CustomerLastName,CustomerStreet,CustomerCity,CustomerState,CustomerZIP,CustomerPhoneNumber,CustomerEmailAddress) VALUES ('Alan', 'Smith', 'Elton Road 12', 'London', 'UK', '327921', '06335356528', 'alan.smith@gmail.com'); INSERT INTO Customer(CustomerFirstName,CustomerLastName,CustomerStreet,CustomerCity,CustomerState,CustomerZIP,CustomerPhoneNumber,CustomerEmailAddress) VALUES ('Trent', 'Hopkins', 'Heumerstreef 20A', 'Rotterdam', 'Netherlands', '6227BP', '+4150383356325', 'gregory.hopkins1@gmail.com'); INSERT INTO Customer_Order(OrderDate,CustomerID,Payment_Status,PaymentMethod, Amount) VALUES ('2021-10-09',1,'Paid','Credit card', NULL); INSERT INTO Customer_Order(OrderDate,CustomerID,Payment_Status,PaymentMethod, Amount) VALUES ('2021-11-09',2, 'Paid','Credit card',NULL); INSERT INTO Customer_Order(OrderDate,CustomerID,Payment_Status,PaymentMethod, Amount) VALUES ('2021-10-07',3,'Paid','Direct debit',NULL); INSERT INTO Customer_Order(OrderDate,CustomerID,Payment_Status,PaymentMethod, Amount) VALUES ('2021-10-14',4, 'Paid','Direct debit', NULL); INSERT INTO Supplier (SupplierName, SupplierStreet, SupplierCity, SupplierState, SupplierZIP) VALUES ('Woody Industries', 'Cramer van Brienenstraat','Rotterdam','The Netherlands','6225 BB'); INSERT INTO Supplier (SupplierName, SupplierStreet, SupplierCity, SupplierState, SupplierZIP) VALUES ('Steel Ltd', 'Luce de Jonge','Rotterdam','The Netherlands','3726 TR'); INSERT INTO Supplier (SupplierName, SupplierStreet, SupplierCity, SupplierState, SupplierZIP) VALUES ('Cotton Eye', 'Maastricht University Street','Maastricht','The Netherlands','2932 AL'); INSERT INTO Supplier (SupplierName, SupplierStreet, SupplierCity, SupplierState, SupplierZIP) VALUES ('PolyCare', 'Sphinxcour 12B','Maastricht','The Netherlands','6211XB'); INSERT INTO Manufacturer (ManufacturerID,ManufacturerName,ManufacturerStreet, ManufacturerCity,ManufacturerState,ManufacturerZIP) VALUES (22,'Woods Best','Sint Analaan 12','Rotterdam','The Netherlands',3819); INSERT INTO Manufacturer (ManufacturerID,ManufacturerName,ManufacturerStreet, ManufacturerCity,ManufacturerState,ManufacturerZIP) VALUES (23,'Steels Best','Tongersetraat 133','Breda','The Netherlands',3929); INSERT INTO Manufacturer (ManufacturerID,ManufacturerName,ManufacturerStreet, ManufacturerCity,ManufacturerState,ManufacturerZIP) VALUES (24,'Cottons Best','Perstraat 3C','Nijmegen','The Netherlands',23451); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (39,'Wood','Delft','Almost New','Oak Wood'); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (42,'Steel','Arnhem','Good','Stainless Steel'); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (44,'Cotton','Den Haag','Almost New','European Cotton'); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (22,'Polyester','Maastricht','Perfect Condition','Dark Protector'); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (16,'Wood','Maastricht','Almost New','Oak Wood'); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (5,'Wood','Rotterdam','Completely New','Woodoss'); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (6,'Nails','Rotterdam','Very good','Naily'); INSERT INTO Materials (MaterialsID,MaterialType,Desc_Origin,Desc_Quality,MaterialName) VALUES (2,'Polyester','Maastricht','Completely New','Polypol'); INSERT INTO Bill_of_Materials (SupplierMaterialsID,Quantity,UnitPrice,Materials_MaterialsID,Supplier_SupplierID) VALUES (1,12,5.43,16,1); INSERT INTO Bill_of_Materials (SupplierMaterialsID,Quantity,UnitPrice,Materials_MaterialsID,Supplier_SupplierID) VALUES (2,40,3.5,6,2); INSERT INTO Bill_of_Materials (SupplierMaterialsID,Quantity,UnitPrice,Materials_MaterialsID,Supplier_SupplierID) VALUES (3,23,0.23,5,3); INSERT INTO Bill_of_Materials (SupplierMaterialsID,Quantity,UnitPrice,Materials_MaterialsID,Supplier_SupplierID) VALUES (4,20,0.75,2,4); INSERT INTO Product (ProductID,ProductName, ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (87,'Terje','Folding Chair','Almost New','Carbon Neutral','Breda',24.99,5); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (63,'Sandared','Table','Almost New','Minimal Emmission','Delft',39.99,4); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (24,'Marius','Bench','Vintage','Carbon Neutral','Amsterdam',3.99,4.5); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (12,'Skogsta','Bench','Refurbished','Natural wood at end of its lifespan','Limburg',39.99,4.9); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (11,'Fixa','Stick-on-Floor Protectors','Brand New','Recycled Material','Cologne',0.99,3.7); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (13,'Poang','Armchair','Brand New','Recycled Wood','Dusseldorf',59,5); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (17,'Stefan','Chair','Solid pin','No finite materials','Amsterdam',19.99,4.5); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (18,'Fanbyn','Chair','Refurbished','Old Wood','Amsterdam',54.95, NULL); INSERT INTO Product (ProductID,productName,ProductType,Desc_Quality, Desc_Sustainabililyt,Desc_Origin,UnitPrice,ProductReview) VALUES (19,'Nilsove','Chair with armrests','Armrests','Vintage','Maastricht',79.99,5); INSERT INTO Order_Line (Order_LineID,Product_ProductID, Order_OrderID,Quantity) VALUES (1,87,1,2); INSERT INTO Order_Line (Order_LineID,Product_ProductID, Order_OrderID,Quantity) VALUES (2,63,2,3); INSERT INTO Order_Line (Order_LineID,Product_ProductID, Order_OrderID,Quantity) VALUES (3,24,3,4); INSERT INTO Order_Line (Order_LineID,Product_ProductID, Order_OrderID,Quantity) VALUES (4,12,4,1); INSERT INTO Order_Line (Order_LineID,Product_ProductID, Order_OrderID,Quantity) VALUES (5,11,4,1); INSERT INTO Order_Line (Order_LineID,Product_ProductID, Order_OrderID,Quantity) VALUES (6,17,3,3); INSERT INTO Order_Line (Order_LineID,Product_ProductID, Order_OrderID,Quantity) VALUES (7,18,2,4); INSERT INTO Manufacturer_Materials (ManMatID,Manufacturer_ManufacturerID,Materials_MaterialsID,Quantity,Price) VALUES (202,22,39,43,550); INSERT INTO Manufacturer_Materials (ManMatID,Manufacturer_ManufacturerID,Materials_MaterialsID,Quantity,Price) VALUES (192,23,42,21,832); INSERT INTO Manufacturer_Materials (ManMatID,Manufacturer_ManufacturerID,Materials_MaterialsID,Quantity,Price) VALUES (113,24,44,12,495); INSERT INTO Product_Line (ProductLineID,Manufacturer_ManufacturerID,Product_ProductID,Quantity,Order_Type,Price,PickupDate) VALUES (12,22,87,17,'Delivery',14.99,'2021-10-16'); INSERT INTO Product_Line (ProductLineID,Manufacturer_ManufacturerID,Product_ProductID,Quantity,Order_Type,Price,PickupDate) VALUES (9,23,63,18,'Delivery',20.99,'2021-09-22'); INSERT INTO Product_Line (ProductLineID,Manufacturer_ManufacturerID,Product_ProductID,Quantity,Order_Type,Price,PickupDate) VALUES (10,24,24,19,'Warehouse Pickup',1.99,'2021-06-09'); INSERT INTO Product_Line (ProductLineID,Manufacturer_ManufacturerID,Product_ProductID,Quantity,Order_Type,Price,PickupDate) VALUES (14,24,11,5,'Warehouse Pickup',0.59,'2021-10-11'); INSERT INTO Product_Line (ProductLineID,Manufacturer_ManufacturerID,Product_ProductID,Quantity,Order_Type,Price,PickupDate) VALUES (15,24,13,3,'Warehouse Pickup',37.89,'2021-10-11'); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (10,87,39); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (11,63,42); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (12,24,44); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (14,11,39); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (15,13,16); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (16,17,6); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (17,18,5); INSERT INTO Product_Materials (Product_MaterialsID, Product_ProductID,Materials_MaterialsID) VALUES (18,19,2); SELECT Customer.CustomerID, Customer.CustomerFirstName, Customer.CustomerLastName, Customer_Order.OrderID,Customer_Order.OrderDate FROM Customer LEFT JOIN Customer_Order ON Customer.CustomerID = Customer_Order.CustomerID; SELECT * FROM Supplier WHERE SupplierCity = 'Rotterdam'; SELECT Materials.MaterialsID, Materials.MaterialType,Materials.MaterialName, Bill_of_Materials.SupplierMaterialsID, Bill_of_Materials.Quantity, Bill_of_Materials.Supplier_SupplierID,Supplier.SupplierName FROM ((Materials LEFT JOIN Bill_of_Materials ON Materials.MaterialsID=Bill_of_Materials.Materials_MaterialsID) INNER JOIN Supplier ON Bill_of_Materials.Supplier_SupplierID = Supplier.SupplierID); SELECT Quantity,UnitPrice FROM Bill_of_Materials WHERE UnitPrice > 10 AND Quantity > 50; SELECT * FROM Product WHERE ProductName = 'Terje'; SELECT Product.ProductID, Product.ProductName,Product.ProductType,Product.UnitPrice,Product_Line.Manufacturer_ManufacturerID,Product_Line.Quantity,Product_Line.Order_Type,Manufacturer.ManufacturerName,Manufacturer.ManufacturerCity,Product_Line.Price FROM Product INNER JOIN Product_Line ON Product.ProductID = Product_Line.Product_ProductID INNER JOIN Manufacturer ON Product_Line.Manufacturer_ManufacturerID = Manufacturer.ManufacturerID WHERE ProductID = 87; SELECT * FROM Product WHERE ProductName = 'Skogsta'; SELECT Customer.CustomerID, Customer.CustomerFirstName,Customer.CustomerZIP, Customer_Order.OrderID, Customer_Order.OrderDate,Order_Line.Order_LineID,Order_Line.Product_ProductID,Order_Line.Quantity, Product.ProductName,Product.UnitPrice FROM Customer INNER JOIN Customer_Order ON Customer.CustomerID = Customer_Order.CustomerID INNER JOIN Order_Line ON Customer_Order.OrderID = Order_Line.Order_OrderID INNER JOIN Product ON Order_Line.Product_ProductID = Product.ProductID WHERE OrderID = 4; SELECT Manufacturer.ManufacturerID, Manufacturer.ManufacturerName,Manufacturer.ManufacturerCity, Product_Line.Product_ProductID,Product.ProductName, Product_Line.Quantity,Product_Line.Price, Product_Line.Quantity*Product_Line.Price AS Total_Cost, Product_Line.PickupDate FROM Manufacturer,Product_Line,Product WHERE Manufacturer.ManufacturerID = Product_Line.Manufacturer_ManufacturerID AND Product_Line.Product_ProductID = Product.ProductID AND Manufacturer.ManufacturerName= 'Woods Best'; SELECT Manufacturer.ManufacturerID, Manufacturer.ManufacturerName,Manufacturer.ManufacturerCity, Product_Line.Product_ProductID,Product.ProductName, Product_Line.Quantity,Product_Line.Price, Product_Line.Quantity*Product_Line.Price AS Total_Cost, Product_Line.PickupDate FROM Manufacturer,Product_Line,Product WHERE Manufacturer.ManufacturerID = Product_Line.Manufacturer_ManufacturerID AND Product_Line.Product_ProductID = Product.ProductID AND Product_Line.Quantity*Product_Line.Price > 100; SELECT Product.ProductName, Product.Desc_Quality, Product.UnitPrice,Product_Line.Quantity,Product_Line.Price, Manufacturer.ManufacturerID,Manufacturer.ManufacturerName,Manufacturer.ManufacturerCity FROM Product, Product_Line, Manufacturer WHERE Product.ProductID = Product_Line.Product_ProductID AND Product_Line.Manufacturer_ManufacturerID = Manufacturer.ManufacturerID AND Product.Desc_Quality LIKE '%New%' ORDER BY Manufacturer.ManufacturerID; SELECT Supplier.SupplierID, Supplier.SupplierName,Supplier.SupplierCity,Bill_of_Materials.Materials_MaterialsID,Bill_of_Materials.Quantity,Bill_of_Materials.UnitPrice,Bill_of_Materials.Quantity*Bill_of_Materials.UnitPrice AS Total_Cost,Materials.MaterialType,Materials.Desc_Quality,Materials.MaterialName FROM Supplier, Bill_of_Materials, Materials WHERE Supplier.SupplierID = Bill_of_Materials.Supplier_SupplierID AND Bill_of_Materials.Materials_MaterialsID = Materials.MaterialsID AND Supplier.SupplierName = 'Woody Industries'; SELECT Product.ProductID,Product.ProductName,Product.ProductType, Product.Desc_Quality,Product.Desc_Origin,Product.UnitPrice,Supplier.SupplierID,Supplier.SupplierName,Supplier.SupplierCity,Bill_of_Materials.Quantity,Bill_of_Materials.UnitPrice, Bill_of_Materials.Quantity * Bill_of_Materials.UnitPrice AS Total_Cost FROM Product,Product_Materials,Bill_of_Materials,Supplier,Materials WHERE Product.ProductID = Product_Materials.Product_ProductID AND Product_Materials.Product_ProductID = Product.ProductID AND Product_Materials.Materials_MaterialsID = Materials.MaterialsID AND Bill_of_Materials.Materials_MaterialsID = Materials.MaterialsID AND Bill_of_Materials.Supplier_SupplierID = Supplier.SupplierID;