List the names of all products from supplier ACME that use a

List the names of all products from supplier ACME that use a USB-port (in any amount) List the names of products that use USB-ports or memory cards (in any amount) List the names of products that use USB-ports and memory cards (in any amount) List the names of products that do not use any USB-ports List the names of components that are used in two or more products (in any amount) List the names of products such that no component used in the product is more than $10 (per unit) List the names of components that are used in only one product. List the names of products that use USB-ports and memory-cards (in any amount) List the names of products that do not use any USB-ports. List the names of components that are used in two or more products (in any amount) List the name of the cheapest compound(s). Each compound is used (one or more) in a certain number of products. List the names of components that are used in the largest number of products, if they used at least twice (i. e. with an amount of two or more). Calculate the total weight of each product (note that unit-weight) per unit, and amount tells you how many of a component a product needs). Identify the product by name. List the names of products such that they do not include any of the cheapest components.

Solution

Since the answer is required for any of the 2 questions, the answer is being provided for:

Q2. Answer the following questions in SQL. Note: you can use sub queries in WITH, SELECT, FROM, WHERE, but cannot use constructs not see in class (this includes OUTER JOINS, RANK, WINDOWS):

Ans: We will use sub queries to answer these questions. Comments have been mentioned appropriately to understand the queries. Please read comments from inner-most sub query to the outer most to completely understand the solution.

1. List the names of products that use USB-ports and memory-cards (in any amount):

SELECT name -- select names of retrieved product ids

FROM product

WHERE pid IN

(SELECT pid -- select product ids associated with retrieved component ids

FROM uses

WHERE cid IN

    (SELECT compid FROM component WHERE name IN (\'USB-port\',\'memory-card\') -- select component ids of memory-cards and USB-ports

    )

);

2. List the names of products that do not use any USB-ports:

SELECT name -- select names of retrieved product ids

FROM product

WHERE pid IN

(SELECT pid -- select products NOT associated with retrieved component ids

FROM uses

WHERE cid NOT IN

    (SELECT compid FROM component WHERE name IN (\'USB-port\') -- select component ids of USB-ports

    )

);

3. List the names of components that are used in two or more products (in any amount):

SELECT -- select names of retrieved components

name

FROM

components

WHERE

compid IN

(

    SELECT -- select component ids having more than 1 row in uses table

      cid

   FROM

      uses

    GROUP BY

      cid

    HAVING

      COUNT > 1

);

4. List the name of the cheapest component(s):

SELECT -- select components associated with the retrieved price

name

FROM

components

WHERE

price =

(

    SELECT -- find the minimum price

      MIN(price)

    FROM

      components

);