Sql-ex Solutions

Exercise: 27

Find out the average hard disk drive capacity of PCs produced by makers who also manufacture printers.

Result set: maker, average HDD capacity.

Solution

1. solution 1

    WITH list AS (
            SELECT maker, hd
            FROM Product
                RIGHT JOIN PC ON Product.model = PC.model
            WHERE maker IN (
                SELECT maker
                FROM Product
                WHERE type = 'printer'
            )
        )
    SELECT maker, AVG(hd)
    FROM list
    GROUP BY maker
   

2. solution 2

    SELECT product.maker, AVG(pc.hd)
    FROM product
        RIGHT JOIN pc ON product.model = pc.model
    WHERE product.maker IN (
        SELECT maker
        FROM product
        WHERE type = 'printer'
    )
    GROUP BY product.maker