Question

Basic SQL
Use the following schema to answer the queries below using SQL

DEVICE [dno, dtype, price] PROVIDER [pno, pname, web] SERVICE [dno, pno, servicedate]

SERVICE.dno references DEVICE.dno SERVICE.pno references PROVIDER.pno

bold is underline.

a) Find the dno for the most expensive device

b) Find all providers that have the work fast in the name

c) Find the number of different device types (dtype)

d) Give all details of devices with price more than $400

Answer 1

a) Find the dno for the most expensive device

select dno from DEVICE where price=(select max(price) from DEVICE);

b) Find all providers that have the work fast in the name

select * from PROVIDER where pname like %work% or pname like %fast%;

c) Find the number of different device types (dtype)

select count(distinct dtype) from DEVICE;

d) Give all details of devices with price more than $400

select * from DEVICE where price>400;