- Get part IDs of parts supplied by a supplier in London to a project in London.
ID
-----
j1
j15
j4
3 rows selected.
- Get part IDs of parts supplied to some project by a supplier in the same city as that project.
ID
-----
j1
j11
j15
j2
j3
j4
j5
j7
j8
j9
10 rows selected.
- Get part IDs for parts supplied to some project in Paris.
PARTID
------
p1
p2
p3
p4
p5
p6
6 rows selected.
- Get part IDs for parts that are not supplied to any project in Paris.
ID
-----
p7
p8
2 rows selected.
- Get project IDs for projects that use at least one part that is available from supplier s4.
Note: get the availability information from the SPJ relation.
Note: the project need not be supplied by 's4' as long as it uses some part that `s4' supplies.
PROJI
-----
j1
j14
j2
j3
j4
j5
j6
j7
j8
j9
10 rows selected.
- Find name of suppliers who do not supply any part heavier than 18.
ID
-----
s2
s3
s9
3 rows selected.
- Find name of suppliers that supplies a `Nut'.
NAME
------------
Adams
Bond
Brown
Green
Jackson
Jones
Klein
Smith
8 rows selected.
- Find name of suppliers who do not supply any `Nut's.
NAME
------------
Blake
Clark
Johnson
Allan
4 rows selected.
- Find project IDs of projects that do not use any locally made parts (i.e., if the project takes place in city x,
then it does not use any part made in city x).
ID
-----
j6
j10
j12
j13
j14
5 rows selected.
- Find project ID and name of projects that do not use any red parts.
ID NAME
----- ------------
j6 Terminal
j10 Scanner
2 rows selected.
- Find project ID and name of projects that only uses red parts.
ID
-----
j15
1 row selected.
- Get supplier ID and name for suppliers that supply to all projects in "Rome".
ID NAME
----- ------------
s11 Bond
s9 Johnson
2 rows selected.
- Get supplier ID and name for suppliers that do not supply to all projects in "Rome".
ID NAME
----- ------------
s1 Smith
s10 Jackson
s12 Allan
s2 Jones
s3 Blake
s4 Clark
s5 Adams
s6 Brown
s7 Klein
s8 Green
10 rows selected.
- Get supplier ID and name for suppliers that supply to some projects in "Rome" but not to all projects in "Rome".
ID NAME
----- ------------
s1 Smith
s10 Jackson
s2 Jones
s6 Brown
s8 Green
5 rows selected.
- Find project ID and name of projects that use all parts that are used in project 'j3'.
ID NAME
----- ------------
j1 Sorter
j2 Punch
j3 Reader
j4 Console
j5 Collator
j7 Tape
6 rows selected.
- Find project ID and name of projects that only use parts that are manufactured in the same city where the project
takes place.
ID NAME
----- ------------
j15 Disk
1 row selected.
- Find supplier ID and name of suppliers who supply all blue parts but do not supply any green part.
ID NAME
----- ------------
s2 Jones
s7 Klein
2 rows selected.
- Find supplier ID and name of suppliers who supply some part of every possible color.
(More clarification: suppose there are only 3 parts p1, p2 and p3. Part p1 is blue, p2 is green and p3 is blue. Then the suppliers that qualify are those that supply (p1 and p2) or (p2 and p3). In these cases, the supplier supplies parts of every color.)
ID NAME
----- ------------
s1 Smith
s5 Adams
s8 Green
3 rows selected.
- Find supplier ID and name of suppliers who supply all the "Nut" parts.
(More clarification: suppose p1 and p2 are "Nut" parts and there are no more "Nut" parts other than p1 and p2. Then you must find suppliers that supply both p1 and p2)
ID NAME
----- ------------
s1 Smith
s5 Adams
s8 Green
3 rows selected.
- Find supplier ID and name of suppliers who only supply to projects in "Rome".
ID NAME
----- ------------
s10 Jackson
s11 Bond
s9 Johnson
3 rows selected.
- Get snum and sname of suppliers who supply at least 3 parts to project 'j4'.
ID NAME
----- ------------
s1 Smith
s5 Adams
s7 Klein
3 rows selected.
- Get snum and sname of suppliers who supply to project `j4' with at least
three parts that are also available from supplier 's6'.
ID NAME
----- ------------
s5 Adams
s7 Klein
2 rows selected.