Hi! I'm a SQL newbie and I'm working on database for an electronic store inventory and I need help Below are my schema:
-Product(sku, brand, model, type)
-Camera(sku, quantity, price)
-Cellphone(sku, quantity, price)
-Printer(sku, quantity, price)
-Laptop(sku, quantity, price)
**Note-I'm using the SKU # as the primary key to join each product type (camera, printer, laptop, desktop) table to the "Product" table.
I would like to have a table of all the inventory in the store (a listing of All the item/products available in the store), I have the following query, but I'm stuck on the Select clause and I'm not sure if my Where clause is correct. In the Select clause, I'm not sure how to call the quantity and price column for each different item correctly.
Select p.sku, p.brand, p.model, p.type, //incomplete-needs help
From Product p, Camera c, Printer pr, Laptop l, Cellphone ce
Where p.sku = c.sku AND p.sku = pr.sku AND p.sku = l.sku AND p.sku = ce.sku //needs verification
Group by p.type;
The result table that I want to have:
SKU | brand | model | type | Quantity | Price |
12345 | Canon | PowerShot | SX500 | Camera | 25 | 146.26
67890 | Nikon | Coolpix L810 | Camera | 15 | 135.00
89012 | Apple | iPhone 5S | Cellphone | 45 | 40 | 650.00
34567 | Samsung | Galaxy S4 | Cellphone | 20 | 580.00
45678 | Apple | MacBook Pro | Laptop | 10 | 1800.00
90123 | Microsoft | Surface Pro 2 | Laptop | 10 | 1000.00
78901 | Dell | B1160w | Printer | 15 | 93.00
23456 | HP | Officejet Pro 8600 | Printer | 10 | 199.99