List the names of all products from supplier ACME that use a USB-port (in any am
ID: 3927256 • Letter: L
Question
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.Explanation / Answer
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
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.