Question

Using MYSQL Workbench:

** Cannot use intersect only allowed to use inner join or in**

Suppliers(sid: int, sname: VARCHAR(30), address: VARCHAR(50))

Parts(pid: int, pname: VARCHAR(30), color:VARCHAR(10))

Catalog(sid: int, pid:int, cost: double)

(Bolded names are primary keys)

1. Find distinct sids and snames of suppliers who supply a red part and a green part.

Answer 1

Table 1: Suppliers

CREATE TABLE `suppliers` (
`Sid` int(11) PRIMARY KEY,
`Sname` varchar(30) NOT NULL,
`Address` varchar(50) NOT NULL
);

INSERT INTO `suppliers` (`Sid`, `Sname`, `Address`) VALUES
(1, 'harry', 'Mumbai'),
(2, 'peter', 'Delhi'),
(3, 'Alex', 'Bengluru'),
(4, 'Aron', 'Goa');

Sid	Sname	Address
1	harry	Mumbai
2	peter	Delhi
3	Alex	Bengluru
4	Aron	Goa

Table 2: Parts

CREATE TABLE `parts` (
`pid` int(11) PRIMARY KEY,
`pname` varchar(30) NOT NULL,
`color` varchar(10) NOT NULL
);

IN22SERT INTO `parts` (`pid`, `pname`, `color`) VALUES
(1, 'gear', 'red'),
(2, 'seats', 'black'),
(3, 'mirror', 'green'),
(4, 'tyre', 'black');

pid	pname	color
1	gear	red
2	seats	black
3	mirror	green
4	tyre	black

Table 3: Catalog

CREATE TABLE `catalog` (
`Sid` int(11) NOT NULL,
`Pid` int(11) NOT NULL,
`Cost` double NOT NULL
);

INSERT INTO `catalog` (`Sid`, `Pid`, `Cost`) VALUES
(1, 3, 300),
(2, 4, 500),
(3, 1, 800),
(4, 2, 600);

ALTER TABLE `catalog`
ADD FOREIGN KEY (`Pid`) REFERENCES `parts` (`pid`),
ADD FOREIGN KEY (`Sid`) REFERENCES `suppliers` (`Sid`);

Sid	Pid	Cost
1	3	300
2	4	500
3	1	800
4	2	600

Question:Find distinct sids and snames of suppliers who supply a red part and a green part.

Query:

SELECT DISTINCT suppliers.Sid,suppliers.Sname from catalog

INNER JOIN parts on parts.pid=catalog.pid

INNER JOIN suppliers on suppliers.Sid=catalog.Sid

WHERE parts.color="red" or parts.color="green";

Sid	Sname
1	harry
3	Alex