Question

Consider the following schema:

Suppliers(sid, sname, address)

Parts(pid, pname, color)

Catalog(sid, pid, cost)

In a plain text file, write the following queries in SQL:

1.Find the names of all suppliers who supply a green part.

2.Find the names of all suppliers who are from Illinois.

3.Find the names of all suppliers who sell a red part costing less than $100.

4.Find the names and colors of all parts that are green or red.

In writing these (basic) queries you may make the following assumptions:

a. Each part has only one color.
b. The cost field is a real number with two decimal places (e.g., 100.25, 93.00).
c. The sid field in Suppliers and the sid field in Catalog refer to the same field.
d. The pid field in Parts and the pid field in Catalog refer to the same field.

Answer 1

Answer:

Creating Suppliers Table:

create table suppliers(sid int primary key,sname char(20),address char(20));

Creating Parts Table:

create table parts(pid int primary key,pname char(20),color char(20));

Creating catalog table:

create table catalog (sid int,pid int,cost float);

Adding Foreign keys :

alter table catalog add foreign key (sid) references suppliers(sid);

alter table catalog add foreign key (pid) references parts(pid);

Inserting values into Tables:

insert into suppliers values (1,'John','Illinos'),(2,'Ding','Godrich'),(3,'Rom','Illinos'),(4,'Adam','YorkShire');

insert into parts values (100,'Bag','Brown'),(101,'Handbag','Green'),(102,'Laptop','Red'),(103,'Mobile','Green');

insert into catalog values (1,100,100.25),(2,101,90.50),(3,100,110.50),(2,102,80),(4,102,90),(3,103,101);

Displaying data from tables:

select * from suppliers;

select * from parts;

select * from catalog;

Query 1:

select a.sname from suppliers a,parts b ,catalog c where a.sid=c.sid and b.pid=c.pid and b.color='green';

Explanation: Here we are retrieving names of suppliers who are supplying green part for that we are checking the sid in suppliers and sid in catalog and pid in parts and pid in catalog and the color of part is green.

Query 2:

select sname from suppliers where address='illinos'

Explanation : Here we are retrieving the names of suppliers whose address is illinos by checking address

Query 3:

select a.sname from suppliers a,parts b ,catalog c where a.sid=c.sid and b.pid=c.pid and b.color='red' and c.cost<100.00;

Explanation :

In this query we are retrieving the names of suppliers who are supplying red parts and that part cost is less than 100 .for that we are joining three tables and checking sids and pids.

Query 4:

select pname,color from parts where color='green' or color='red';

Explanation:

Here by using or operator we are retrieving the part names and their color if the color of part is either red or green.

Queries Screenshots and Outputs:

Note : if you have any queries please post a comment thanks a lot..always available to help you..