PRACTICAL_NO:02
1)Create database and 3 tables
create database if not exists orders;
use orders;
create table suppliers (
sid varchar(10) not null ,
sname varchar(50),
Address varchar(100) not null );
insert into suppliers (sid,sname,Address) values
("s1","abc","pune"),
("s2","pqr","mumbai"),
("s3","xyz","nashik"),
("s4","vft","nagar"),
("s5","adsrr","dhule");
select *from suppliers;
create table parts (
pid varchar(10) not null ,
pname varchar(50) not null ,
color varchar(100) not null );
insert into parts (pid,pname,color) values
("p1","prod_1","red"),
("p2","prod_2","green"),
("p3","prod_3","blue"),
("p4","prod_4","green"),
("p5","prod_5","orange");
select * from parts;
create table catalog (
sid varchar(10) not null ,
pid varchar(10) not null ,
cost int not null );
insert into catalog (sid,pid,cost) values
("s1","p1",30),
("s2","p2",10),
("s3","p3",40),
("s4","p4",56),
("s5","p5",34);
select * from catalog;
2) Name of all parts whose color is green .
select * from parts
where color="green";
output:
3)Inner join
select s.sname,p.color
from suppliers s
inner join catalog c on s.sid=c.sid
inner join parts p on p.pid=c.pid
where color="red";
output:
4)Name of supplier ,color of parts where cost>=25
select s.sname,p.color,c.cost
from suppliers s
inner join catalog c on s.sid=c.sid
inner join parts p on p.pid=c.pid
where cost>=25;
output:
5)Left join
INSERT INTO parts (pid, pname, color)
VALUES ('p6', 'prod_6', 'black');
INSERT INTO suppliers (sid, sname, Address)
VALUES ('s6', 'new_supplier', 'kolhapur');
SELECT s.sid, s.sname, c.pid, c.cost
FROM suppliers s
LEFT JOIN catalog c ON s.sid = c.sid;
output:
6)Right join
select s.sid,s.sname
from suppliers s right join catalog c on s.sid=c.sid;
output:
7)full join
SELECT s.sid, s.sname, c.pid
FROM suppliers s
LEFT JOIN catalog c ON s.sid = c.sid
UNION
SELECT s.sid, s.sname, c.pid
FROM suppliers s
RIGHT JOIN catalog c ON s.sid = c.sid;
output:
8)Subquery( supplier name having expensive part)
select s.sname ,c.cost from suppliers s
join catalog c on s.sid=c.sid
where c.cost=(select max(cost) from catalog);
output:
9) SUBQUERY IN IN – Suppliers who supply parts with cost > 30
SELECT s.sname
FROM suppliers s
WHERE s.sid IN (
SELECT sid FROM catalog WHERE cost > 30);
output:
10)View
create view supply_details as select s.sname,p.pname,p.color,c.cost
from suppliers s
join catalog c on s.sid=c.sid
join parts p on p.pid=c.pid;
select * from supply_details;
output:
