Thursday, July 30, 2009

Learn Joint Queries in sql

- A Joint query joints some of the rows in one table with some of the rows in another table.

- For each part being supplied get the part number and the names of all cities which are supplied that part

SELECT p.pno,city
FROM p,spj
WHERE p.pno=spj.pno

-Get all pairs of part name and project name such that the part and the project are located
in the same city

SELECT pname,jname
FROM p,j
WHERE p.city=j.city

Get all information about suppliers and parts where the supplier and the part concern are
located in the same city but omitting suppliers with status value less than 20

SELECT s.*,p.*
FROM s,p
WHERE s.city=p.city AND status>=20

Get all suppliers and their supplying information of those suppliers with status value less than 20 and the quantity supplied grater than 200

SELECT s.*,spj.*
FROM s,spj
WHERE s.sno=spj.sno AND status<20>200


For each shipment get the supplier number, part number, project number and the value of the
shipment

SELECT spj.sno,p.pno,spj.jno,spj.qty*p.price
FROM p,spj
WHERE spj.pno=p.pno

Get all pairs of city names such that a supply located in the first city is currently supplying a part stored in the second city

SELECT DISTINCT s.city,p.city
FROM s,spj,p
WHERE s.sno=spj.sno AND spj.pno=p.pno

Get all pairs of supplier numbers such that two suppliers concern are located in the same city

SELECT f.sno,t.sno
FROM s f, s t
WHERE f.city=t.city AND f.sno


No comments:

Post a Comment