BeginnerHere BeginnerHere - 14 days ago 67
SQL Question

What are the correct select statements for the this?

In class we made a FruitTable with the fields fruitID, fruitName, price, quantity, supplier and delivery date. We're practicing select statements and I'm stuck on these question:


  1. CREATE A SELECT STATEMENT THAT WILL OUTPUT THE FRUIT WITH THE HIGHEST POTENTIAL PROFIT.



I don't know how to display the fruitName but I figured out how to display the highest potential profit of it. How do I get this to display what fruit this is?

select max(price*quantity) from FruitTable as maxprofit



  1. A SELECT STATEMENT WHICH OUTPUTS THE FRUIT AND SUPPLIER NAME THAT SAME FIRST LETTER IN THEIR NAMES.



I have no idea how to answer this one.

I'm using SQL Server Management Studio

Answer Source

You are almost there with the first one.

select * from FruitTable
WHERE price * quantity = (SELECT max(price*quantity) FROM FruitTable)

The second one (returning row having the same first letter in FruitName and Supplier name) is

SELECT * FROM FruitTable 
WHERE SUBSTRING(fruitName, 1, 1) = SUBSTRING(supplier, 1, 1)