42 lines
1.4 KiB
Plaintext
42 lines
1.4 KiB
Plaintext
/* 1. list the snumber, sname, and pnumber for suppliers who supply parts but the condition of the joins is in the where clause */
|
|
select supplier.snumber, sname, pnumber, jnumber
|
|
from supplier, spj
|
|
where spj.snumber = supplier.snumber
|
|
|
|
/* 2. list the snumber, sname, and pnumber for suppliers USING SUBQUERIES aka nested selects */
|
|
select snumber, (select a.sname from supplier as a where a.snumber = spj.snumber)
|
|
pnumber, jnumber
|
|
from spj
|
|
where spj.snumber in
|
|
(select supplier.snumber from supplier);
|
|
|
|
/* 3. list the snumber, sname, and pnumber for suppliers USING SUBQUERIES aka nested selects */
|
|
select snumber, (select a.sname from supplier as a
|
|
where a.snumber = spj.snumber)
|
|
(select b.jname from project as b
|
|
where b.jnumber = spj.jnumber),
|
|
pnumber, jnumber
|
|
from spj
|
|
where spj.snumber in
|
|
(select supplier.snumber
|
|
from supplier);
|
|
|
|
|
|
/*4. List the snumber, sname, pnumber, jnumber and jname for suppliers who supply parts but not the condition for all joins in there WHERE clause */
|
|
|
|
select supplier.snumber, sname, spj.jnumber, jname
|
|
from supplier, spj, project
|
|
where supplier.snumber = spj.snumber AND
|
|
spj.jnumber = project.jnumber;
|
|
|
|
|
|
select * /* for i 0 to number of suppliers */
|
|
from supplier
|
|
where snumber in
|
|
(select snumber /* for j 0 to number of tuples in spj */
|
|
from spj
|
|
where jnumber IN
|
|
(select jnumber /* for K 0 to number of tuples in project */
|
|
from project)
|
|
);
|