Question

Write a SQL statement for the following query:

Relational Schema:

Part( P #, PName, Producer, Year, Price) (P# is the primary key)

Customer( C#, CName, City) (C# is the primary key)

Supply(S#, P#, C#, Quantity, Amount, Date) (S# is the primary key)

Query: For each Apple product supplied to more than 10 different customers in Chicago in 2020, list product number, product name, total supply quantity, and total supply amount. Sort the result by product name.

Answer 1

select p.P# ,p.PName, sum(s.Quantity), sum(s.Amount)
from Supply s,
    Customer c,
    Part p
where s.C# = c.C#
and   s.P# = p.P#
and p.Producer = 'Apple'
and c.City = 'Chicago'
and extract(YEAR from s.Date) = 2020
group by (p.P#, p.PName,s.Quantity, s.Amount)
having count(c.C#)>10;

I doubt you can create a column with name date in Supply table. For testing I renamed it as Dates since date is reserved keyword in sql.

If you also get error for same then please use some other name than date.