For each piece, find the most expensive offering of that piece and include the piece name, provider name, and price (note that there could be two providers who supply the same piece at the most expensive price).
| SELECT piece, price FROM provides
| GROUP piece, price MAX BY piece
| SELECT piece, price, provider FROM provides
| JOIN piece
| WHERE price $= price-max
| SELECT code, name FROM providers
| RENAME code provider
| JOIN provider
| SELECT code, name FROM pieces
| RENAME code piece
| RENAME name piecename
| JOIN piece
| PROJECT piecename, name, price
4 rows 0 msec
| GROUP piece, price MAX BY piece
| SELECT piece, price, provider FROM provides
| JOIN piece
| WHERE price $= price-max
| SELECT code, name FROM providers
| RENAME code provider
| JOIN provider
| SELECT code, name FROM pieces
| RENAME code piece
| RENAME name piecename
| JOIN piece
| PROJECT piecename, name, price
4 rows 0 msec
| piecename | name | price |
|---|---|---|
| Sprocket | Susan Calvin Corp. | 15 |
| Screw | Clarke Entreprises | 20 |
| Nut | Susan Calvin Corp. | 50 |
| Bolt | Susan Calvin Corp. | 7 |