Question

Please write the correct query for the following questions:

1. Select the name of each manufacturer along with the name and price of its most expensive product.

2. Computer products are expensive. Find the maximum priced product. Which products are within $30 of the maximum priced product.

3. Find the maker of the most expensive ‘drive’. Note, there are variety of drives in the data. Regular expressions help.

Using the following table:

CREATE TABLE manufacturers (
code number PRIMARY KEY NOT NULL,
name varchar(50) NOT NULL
);

CREATE TABLE products (
code NUMBER PRIMARY KEY NOT NULL,
name varchar(50) NOT NULL ,
price real NOT NULL ,
manufacturer number NOT NULL
CONSTRAINT fk_manufacturers_code REFERENCES manufacturers(code)
);

INSERT INTO manufacturers(code,name) VALUES(1,'Sony');
INSERT INTO manufacturers(code,name) VALUES(2,'Creative Labs');
INSERT INTO manufacturers(code,name) VALUES(3,'Hewlett-Packard');
INSERT INTO manufacturers(code,name) VALUES(4,'Iomega');
INSERT INTO manufacturers(code,name) VALUES(5,'Fujitsu');
INSERT INTO manufacturers(code,name) VALUES(6,'Winchester');

INSERT INTO products(code,name,price,manufacturer) VALUES(1,'Hard drive',240,5);
INSERT INTO products(code,name,price,manufacturer) VALUES(2,'Memory',120,6);
INSERT INTO products(code,name,price,manufacturer) VALUES(3,'ZIP drive',150,4);
INSERT INTO products(code,name,price,manufacturer) VALUES(4,'Floppy disk',5,6);
INSERT INTO products(code,name,price,manufacturer) VALUES(5,'Monitor',240,1);
INSERT INTO products(code,name,price,manufacturer) VALUES(6,'DVD drive',180,2);
INSERT INTO products(code,name,price,manufacturer) VALUES(7,'CD drive',90,2);
INSERT INTO products(code,name,price,manufacturer) VALUES(8,'Printer',270,3);
INSERT INTO products(code,name,price,manufacturer) VALUES(9,'Toner cartridge',66,3);
INSERT INTO products(code,name,price,manufacturer) VALUES(10,'DVD burner',180,2);

Answer 1

Select the name of each manufacturer along with the name and price of its most expensive product.

select m.name, p.name, max(p.price)
from manufacturers as m, products as p
where m.code=p.code;

Explanation: This query will fetch records from manufaturers and products tables where manufacturers code matches with product code.

Find the maximum priced product.

select name, max(price)
from products;

Explanation: we will use max operator in sql to fetch maximum priced product from products table.

Which products are within $30 of the maximum priced product.

select name, price
from products
where price between 30 and 270;

Explanation : Between operator will specify the products whose price lies between 30 and 270.

Find the maker of the most expensive ‘drive’.

select m.name ,p.name, max(p.price)
from manufacturers as m, products as p
where m.code=p.code and p.name LIKE '%drive';

Explanation: Firstly we need to match manhufacture and product code to fetch the details of maker. for getting maximum priced item use max operator and to match the drive name use LIKE operator.