Given the schema item(itemid, name, category, price) itemsale(transid, itemid, qty) transaction(tran_sid, custid, date) customer(custid, name, street-addr, city), to find the name and price of the most expensive item (if more than one item is the most expensive, print them all) is given below:
SET p 1 : = ( SELECT MAX ( price ) FROM items);
SELECT * FROM items WHERE price = p 1 ;
Using variables, p1 stores the maximum price from the table items and then uses the variable p1 in the following query to return all records which have that maximum price without limiting the number of records as you desired.
Other ways of getting the same result are given below:
2. SELECT * FROM items
WHERE itemID IN (
SELECT itemID FROM items
WHERE price IN (
SELECT MAX(price) FROM items
)
);
3. SELECT * FROM items
WHERE price IN (
SELECT MAX ( price ) FROM items
) ;
Read more about SQL here:
https://brainly.com/question/27851066
#SPJ1
