The database scheme consists of four tables:
- Product (maker, model, type)
- PC (code, model, speed, ram, hd, cd, price)
- Laptop (code, model, speed, ram, hd, screen, price)
- Printer (code, model, color, type, price)
The Product table contains data on the maker, model number, and type of product (
Printer). It is assumed that model numbers in the Product table are unique for all makers and product types.
Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed column, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g,
4x) - cd, and its price.
The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen.
For each printer model in the Printer table, its output type (
y for color and
n for monochrome) – color column, printing technology (
Matrix) – type, and price are specified.
Now the query I came up with:
SELECT PC.model, price FROM PC INNER JOIN Product ON PC.model = Product.model UNION SELECT Laptop.model, price FROM Laptop INNER JOIN Product ON Laptop.model = Product.model UNION SELECT printer.model, price FROM printer INNER JOIN Product ON printer.model = Product.model WHERE Product.maker='B'
The result should be:
model price 1121 850.0000 1750 1200.0000
I am doing SQL exercise and not able to get the same result.