Cycle 02

Lab Cycle 2 — SQL Operators, Aggregate Functions, Grouping & Joins

3 Questions
Question 02 of 03

Aggregate Functions, GROUP BY, HAVING, and Sorting

Problem Statement

Write SQL queries to: 1. Find minimum, maximum, average, and sum of costs of parts. 2. Count total number of parts present. 3. Retrieve average cost of all parts supplied by 'Mike'. 4. Display total price of parts of each color. 5. Find branch and number of suppliers for branches having more than 2 suppliers. 6. Find branch and number of suppliers in each branch. 7. Find all parts sorted by pname in ascending order and cost in descending order.

C++ 
-- 1. Min, Max, Avg, and Sum of part costs
SELECT 
    MIN(price) AS min_cost,
    MAX(price) AS max_cost,
    AVG(price) AS avg_cost,
    SUM(price) AS total_cost
FROM Part;

-- 2. Count total number of parts
SELECT COUNT(*) AS total_parts FROM Part;

-- 3. Retrieve average cost of all parts supplied by 'Mike'
SELECT AVG(p.price) AS mike_avg_part_cost
FROM Part p
JOIN Supplies sp ON p.PID = sp.PID
JOIN Supplier s ON sp.SID = s.SID
WHERE s.Sname = 'Mike';

-- 4. Display total price of parts of each color
SELECT color, SUM(price) AS total_price, COUNT(*) AS count_of_parts
FROM Part
GROUP BY color;

-- 5. Find branch and count of suppliers for branches with > 2 suppliers
SELECT branch, COUNT(SID) AS supplier_count
FROM Supplier
GROUP BY branch
HAVING COUNT(SID) > 2;

-- 6. Find branch and number of suppliers in that branch
SELECT branch, COUNT(SID) AS supplier_count
FROM Supplier
GROUP BY branch;

-- 7. Find all parts sorted by pname ascending and cost descending
SELECT PID, Pname, color, price
FROM Part
ORDER BY Pname ASC, price DESC;
Question 2 of 3