The following tables define a schema to record buying transa

The following tables define a schema to record buying transactions for various customers.

create table item(

itemid varchar(20) primary key,

name varchar(20) not null,

category varchar(20) not null,

price numeric(8,2));

create table customer(

custid varchar(20) primary key,

name varchar(20),

street_addr varchar(20),

city varchar(20));

create table transaction(

transid varchar(20) primary key,

custid varchar(20) references customer, date date);

create table itemsale(

transid varchar(20) primary key references transaction, itemid varchar(20) references item,

qty integer not null);

a. (3) Write an SQL query to find the name and price of the most expensive item (if more than one item is the most expensive, list them all). The output should have two columns, item name and price.

Solution

select name,price from items where price in ( select max(price) from items );