I have the following tables:
Customers (Cust_ID, Cust_name, Cust_address, Cust_email, Type)
Suppliers (Sup_ID, Sup_name, Sup_address, Sup_email)
Equipment (Equip_ID, Equip_name, Equip_brand, Sup_ID, weekedays_rate, weekend_rate, Delivery_time, Category, Qty_in_stock)/ *Sup_ID referencing Supplier.Sup_ID*/
Complain (Comp_ID, hire_ID,Equip_ID, Equip_name, Cust_ID, comp_date,Comp_action) /*hire_ID refrencing Hire.hire_ID* Equip_ID referencing Equipmnet.Equip_ID* Equip_name referencing Equipmnet.Equip_name *Cus_ID referencing Customers.Cus_ID*/
Rent (Rent_ID, Cust_ID, Equip_ID, Equip_name, Rent_start, exp_return_date, act_return_date, weekday_num, weekend_num,hire_ income)/ *Cus_ID referencing Customers.Cus_ID* Equip_ID referencing Equipmnet.Equip_ID* Equip_name referencing Equipmnet.Equip_name */
and i need to produce the following queries:
- A list of names and addresses of all suppliers along with the total number of equipments from all categories they currently supply.
Code:
SELECT S.Sup_name, Sup_address AS address, (SELECT SUM(E.Qty_in_stock) FROM Equipment E, Suppliers S GROUP BY Sup_ID) AS quantity
FROM Suppliers S, Equipment E
WHERE S.Sup_ID = E.Sup_ID
ORDER BY S.Sup_name;
- Produce, for a given category, the total number of equipments under that category available in stock and the number currently on hire to customers.
Code:
SELECT E.category, SUM(E.Qty_in_stock) AS quantity, (SELECT COUNT(*.R) FROM Rent R, Equipment E WHERE E.Equip_ID=R.Equip_ID AND E.Category = ‘Access Equipment’) AS rent
FROM Equipment E, Rent R
WHERE E.Category = ‘Access Equipment’
AND R.exp_return_date > getdate()
AND E.Equip_ID =R.Equip_ID
GROUP BY E.Category;