SQLize Online / PHPize Online  /  SQLtest Online

/*The part details*/ CREATE TABLE Parts ( ID int PRIMARY KEY AUTO_INCREMENT, PartType char (100) NOT NULL, Model char (25) NOT NULL, Price int (7) NOT NULL ); /*The brand attached to each model in the parts table*/ CREATE TABLE Brands ( ModelID int PRIMARY KEY AUTO_INCREMENT, Brand char (25) NOT NULL, Model char (25) NOT NULL UNIQUE ); /*How many of each part are available*/ CREATE TABLE Stock( ID int PRIMARY KEY AUTO_INCREMENT, AvailableStock int (7) NOT NULL, CONSTRAINT StockID FOREIGN KEY (ID) REFERENCES Parts(ID) ); INSERT INTO Parts (PartType, Model, Price) VALUES ('Engine', 'Civic', 1500), ('Muffler', 'Civic', 120), ('Head Light', 'Civic', 50), ('Air-Filter', 'Civic', 10), ('Engine', 'Accord', 1700), ('Muffler', 'Accord', 150), ('Head Light', 'Accord', 70), ('Air-Filter', 'Accord', 20), ('Engine', 'Corolla', 2100), ('Muffler', 'Corolla', 120), ('Head Light', 'Corolla', 80), ('Air-Filter', 'Corolla', 20), ('Engine', 'Camry', 2200), ('Muffler', 'Camry', 130), ('Head Light', 'Camry', 90), ('Air-Filter', 'Camry', 30), ('Engine', 'Legacy', 3000), ('Muffler', 'Legacy', 200), ('Head Light', 'Legacy', 180), ('Air-Filter', 'Legacy', 25), ('Engine', 'Outback', 4000), ('Muffler', 'Outback', 500), ('Head Light', 'Outback', 190), ('Air-Filter', 'Outback', 30), ('Engine', 'Altima', 2500), ('Muffler', 'Altima', 200), ('Head Light', 'Altima', 80), ('Air-Filter', 'Altima', 25), ('Engine', 'Maxima', 3000), ('Muffler', 'Maxima', 500), ('Head Light', 'Maxima', 90), ('Air-Filter', 'Maxima', 35), ('Engine', 'Speed3', 2000), ('Muffler', 'Speed3', 120), ('Head Light', 'Speed3', 65), ('Air-Filter', 'Speed3', 20), ('Engine', 'CX8', 1500), ('Muffler', 'CX8', 135), ('Head Light', 'CX8', 75), ('Air-Filter', 'CX8', 50); INSERT INTO Brands (Brand, Model) VALUES ('Honda', 'Civic'), ('Honda', 'Accord'), ('Toyota', 'Corolla'), ('Toyota', 'Camry'), ('Subaru', 'Legacy'), ('Subaru', 'Outback'), ('Nissan', 'Altima'), ('Nissan', 'Maxima'), ('Mazda', 'Speed3'), ('Mazda', 'CX8'); INSERT INTO Stock (AvailableStock) VALUES (50), (9876), (2346), (2533), (2843), (100000), (23214), (12344), (32), (23131), (2421), (1), (123), (223), (5564), (4345), (3479), (3467), (7), (7777), (90), (10587), (3445), (4500), (1500), (98007), (33445), (12345), (78), (34954), (3788), (10), (983), (783), (7893), (4457), (8898), (1010), (8), (9900); /*The SELECT.... FROM.... query*/ /*Show all of the available parts and their types, models, and prices, but not the ID as that is unecessary for the customer to see*/ SELECT PartType AS "Part Type", Model, FORMAT(Price, 'C') AS "Price" FROM Parts; /*The SELECT.... FROM.... WHERE.... query*/ /*Select type, model, and price from parts where the price is less or equal to than $100*/ SELECT PartType AS "Parts Under $100", Model, FORMAT(Price, 'C') AS "Price" FROM Parts WHERE Price <= 100; /*The SELECT.... FROM.... WHERE.... ORDER BY.... query*/ /*Show all the available parts made for a car of a Civic model and their prices, and order them by price in ascending order*/ SELECT PartType AS "Civic Parts", FORMAT(Price, 'C') FROM Parts WHERE Model = "Civic" ORDER BY Price ASC; /*The SELECT.... FROM.... WHERE.... GROUP BY.... query*/ /*Calculate the cost it would take to completely replace all parts that cost less than $1000 and group by the model*/ SELECT Model, sum(Price) AS "Cost For Full Replacement Of Parts Under $1000" FROM Parts WHERE Price <= 1000 GROUP BY Model; /*The SUBQUERY*/ /*Select all types, models, and prices from parts but only for those with models under the "Honda" brand in the brands table*/ SELECT PartType AS "Part Type", Model, FORMAT(Price, 'C') AS "Price" FROM Parts WHERE Model = ( SELECT Model FROM Brands WHERE Brand = "Honda" AND Model = Parts.Model ); /*The JOIN query*/ /*Select every engine in the parts table and join the part's model and price from the Parts table with the available stock from the Stock Table and the brand from the brands table*/ SELECT Brands.Brand, Parts.Model, FORMAT(Parts.Price, 'C') AS "Price", Stock.AvailableStock AS "Available Stock" FROM Parts JOIN Stock ON Parts.ID = Stock.ID JOIN Brands ON Brands.Model = Parts.Model WHERE Parts.PartType = "Engine"