MySQL Query Set-2

CREATION OF TABLES
Ø  Creation of table S:
mysql> create table S(SNO varchar(2) primary key, SNAME varchar(10),STATUS int(2), CITY varchar(10));
Query OK, 0 rows affected (0.29 sec)
Ø  Creation of table P:
mysql> create table P(PNO varchar(2) primary key, PNAME varchar(10), COLOR varchar(10), WEIGHT int(2), CITY varchar(10));
Query OK, 0 rows affected (0.31 sec)
Ø  Creation of table J:
mysql> create table J(JNO varchar(2) primary key, JNAME varchar(10), CITY varchar(10));
Query OK, 0 rows affected (0.27 sec)
Ø  Creation of table SPJ:
mysql> create table SPJ(SNO varchar(2) references S, PNO varchar(2) references P, JNO varchar(2) references J, QTY int(3), primary key(SNO,PNO,JNO));
Query OK, 0 rows affected (0.28 sec)

INSERTION INTO TABLES
Ø  Insertion into table S:
mysql> insert into S values("S1","Smith",20,"London");
Query OK, 1 row affected (0.04 sec)
mysql> insert into S values("S2","Jones",10,"Paris");
Query OK, 1 row affected (0.05 sec)
mysql> insert into S values("S3","Blake",30,"Paris");
Query OK, 1 row affected (0.09 sec)
mysql> insert into S values("S4","Clark",20,"London");
Query OK, 1 row affected (0.10 sec)
mysql> insert into S values("S5","Adams",30,"Athens");
Query OK, 1 row affected (0.04 sec)
Ø  Insertion into table P:
mysql> insert into P values("P1","Nut","Red",12,"London");
Query OK, 1 row affected (0.04 sec)
mysql> insert into P values("P2","Bolt","Green",17,"Paris");
Query OK, 1 row affected (0.05 sec)
mysql> insert into P values("P3","Screw","Blue",17,"Rome");
Query OK, 1 row affected (0.10 sec)
mysql> insert into P values("P4","Screw","Red",14,"London");
Query OK, 1 row affected (0.04 sec)
mysql> insert into P values("P5","Cam","Blue",12,"Paris");
Query OK, 1 row affected (0.05 sec)
mysql> insert into P values("P6","Cog","Red",19,"London");
Query OK, 1 row affected (0.04 sec)
Ø  Insertion into table J:
mysql> insert into J values("J1","Sorter","Paris");
Query OK, 1 row affected (0.08 sec)
mysql> insert into J values("J2","Display","Rome");
Query OK, 1 row affected (0.05 sec)
mysql> insert into J values("J3","OCR","Athens");
Query OK, 1 row affected (0.08 sec)
mysql> insert into J values("J4","Console","Athens");
Query OK, 1 row affected (0.07 sec)
mysql> insert into J values("J5","RAID","London");
Query OK, 1 row affected (0.03 sec)
mysql> insert into J values("J6","EDS","Oslo");
Query OK, 1 row affected (0.04 sec)
mysql> insert into J values("J7","ARP","London");
Query OK, 1 row affected (0.11 sec)
Ø  Insertion into table SPJ:
mysql> insert into SPJ values("S1","P1","J1",200);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S1","P1","J4",700);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S2","P3","J1",400);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S2","P3","J2",200);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S2","P3","J3",200);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S2","P3","J4",500);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S2","P3","J5",600);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S2","P3","J6",400);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S2","P3","J7",800);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S2","P5","J2",100);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S3","P3","J1",200);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S3","P4","J2",500);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S4","P6","J3",300);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S4","P6","J7",300);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S5","P2","J2",200);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S5","P2","J4",100);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S5","P5","J5",500);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S5","P5","J7",100);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S5","P6","J2",200);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S5","P1","J4",100);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S5","P3","J4",200);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S5","P4","J4",800);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SPJ values("S5","P5","J4",400);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SPJ values("S5","P6","J4",500);
Query OK, 1 row affected (0.11 sec)

VIEW DATA FROM TABLES
Ø  Data of table S:
mysql> select * from S;
+-----+-------+--------+--------+
| SNO | SNAME | STATUS | CITY   |
+-----+-------+--------+--------+
| S1  | Smith |     20 | London |
| S2  | Jones |     10 | Paris  |
| S3  | Blake |     30 | Paris  |
| S4  | Clark |     20 | London |
| S5  | Adams |     30 | Athens |
+-----+-------+--------+--------+
5 rows in set (0.00 sec)
Ø  Data of table P:
mysql> select * from P;
+-----+-------+-------+------+--------+
| PNO | PNAME | COLOR | WEIGHT   | CITY   |
+-----+-------+-------+------+--------+
| P1  | Nut   | Red   |   12 | London |
| P2  | Bolt  | Green |   17 | Paris  |
| P3  | Screw | Blue  |   17 | Rome   |
| P4  | Screw | Red   |   14 | London |
| P5  | Cam   | Blue  |   12 | Paris  |
| P6  | Cog   | Red   |   19 | London |
+-----+-------+-------+------+--------+
6 rows in set (0.00 sec)
Ø  Data of table J:
mysql> select * from J;
+-----+---------+--------+
| JNO | JNAME   | CITY   |
+-----+---------+--------+
| J1  | Sorter  | Paris  |
| J2  | Display | Rome   |
| J3  | OCR     | Athens |
| J4  | Console | Athens |
| J5  | RAID    | London |
| J6  | EDS     | Oslo   |
| J7  | ARP     | London |
+-----+---------+--------+
7 rows in set (0.00 sec)
Ø  Data of table SPJ:
mysql> select * from SPJ;
+-----+-----+-----+------+
| SNO | PNO | JNO | QTY  |
+-----+-----+-----+------+
| S1  | P1  | J1  |  200 |
| S1  | P1  | J4  |  700 |
| S2  | P3  | J1  |  400 |
| S2  | P3  | J2  |  200 |
| S2  | P3  | J3  |  200 |
| S2  | P3  | J4  |  500 |
| S2  | P3  | J5  |  600 |
| S2  | P3  | J6  |  400 |
| S2  | P3  | J7  |  800 |
| S2  | P5  | J2  |  100 |
| S3  | P3  | J1  |  200 |
| S3  | P4  | J2  |  500 |
| S4  | P6  | J3  |  300 |
| S4  | P6  | J7  |  300 |
| S5  | P1  | J4  |  100 |
| S5  | P2  | J2  |  200 |
| S5  | P2  | J4  |  100 |
| S5  | P3  | J4  |  200 |
| S5  | P4  | J4  |  800 |
| S5  | P5  | J4  |  400 |
| S5  | P5  | J5  |  500 |
| S5  | P5  | J7  |  100 |
| S5  | P6  | J2  |  200 |
| S5  | P6  | J4  |  500 |
+-----+-----+-----+------+
24 rows in set (0.00 sec)

QUERIES
1. Get full details of all projects.
mysql> select * from J;
+-----+---------+--------+
| JNO | JNAME   | CITY   |
+-----+---------+--------+
| J1  | Sorter  | Paris  |
| J2  | Display | Rome   |
| J3  | OCR     | Athens |
| J4  | Console | Athens |
| J5  | RAID    | London |
| J6  | EDS     | Oslo   |
| J7  | ARP     | London |
+-----+---------+--------+
7 rows in set (0.00 sec)
2. Get full details of all projects in london.
mysql> select * from J where CITY="London";
+-----+-------+--------+
| JNO | JNAME | CITY   |
+-----+-------+--------+
| J5  | RAID  | London |
| J7  | ARP   | London |
+-----+-------+--------+
2 rows in set (0.00 sec)
3. Get supplier numbers for suppliers who supply project J1.
mysql> select distinct SNO from SPJ where JNO="J1";
+-----+
| SNO |
+-----+
| S1  |
| S2  |
| S3  |
+-----+
3 rows in set (0.03 sec)
4. Get all shipments where the quantity is in the range 300 to 750 inclusive.
mysql> select * from SPJ where QTY>=300 and QTY<=750;
+-----+-----+-----+------+
| SNO | PNO | JNO | QTY  |
+-----+-----+-----+------+
| S1  | P1  | J4  |  700 |
| S2  | P3  | J1  |  400 |
| S2  | P3  | J4  |  500 |
| S2  | P3  | J5  |  600 |
| S2  | P3  | J6  |  400 |
| S3  | P4  | J2  |  500 |
| S4  | P6  | J3  |  300 |
| S4  | P6  | J7  |  300 |
| S5  | P5  | J4  |  400 |
| S5  | P5  | J5  |  500 |
| S5  | P6  | J4  |  500 |
+-----+-----+-----+------+
11 rows in set (0.00 sec)
5. Get all part-color/part-city combinations. Note: Here and subsequently, the term “all” is to be taken to mean “all currently represented in the database” not “all possible”.
mysql> SELECT DISTINCT COLOR, CITY FROM P;
+-------+--------+
| COLOR | CITY   |
+-------+--------+
| Red   | London |
| Green | Paris  |
| Blue  | Rome   |
| Blue  | Paris  |
+-------+--------+
4 rows in set (0.00 sec)
6. Get all supplier number/part number/project number triples such that the indicated supplier, part and project all are colocated (i.e. all in the same city).
mysql> select S.SNO, P.PNO, J.JNO, J.CITY from S, P, J, SPJ where S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND J.JNO=SPJ.JNO AND S.CITY=P.CITY and P.CITY=J.CITY;
+-----+-----+-----+--------+
| SNO | PNO | JNO | CITY   |
+-----+-----+-----+--------+
| S4  | P6  | J7  | London |
+-----+-----+-----+--------+
1 row in set (0.04 sec)
7.   Get all supplier number/part number/project number triples such that the indicated supplier, part and project are not colocated.
mysql> select distinct S.SNO, S.CITY, P.PNO, P.CITY, J.JNO, J.CITY from S, P, J, SPJ where S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND J.JNO=SPJ.JNO AND (S.CITY!=P.CITY OR P.CITY!=J.CITY);
+-----+--------+-----+--------+-----+--------+
| SNO | CITY   | PNO | CITY   | JNO | CITY   |
+-----+--------+-----+--------+-----+--------+
| S1  | London | P1  | London | J1  | Paris  |
| S1  | London | P1  | London | J4  | Athens |
| S2  | Paris  | P3  | Rome   | J1  | Paris  |
| S2  | Paris  | P3  | Rome   | J2  | Rome   |
| S2  | Paris  | P3  | Rome   | J3  | Athens |
| S2  | Paris  | P3  | Rome   | J4  | Athens |
| S2  | Paris  | P3  | Rome   | J5  | London |
| S2  | Paris  | P3  | Rome   | J6  | Oslo   |
| S2  | Paris  | P3  | Rome   | J7  | London |
| S2  | Paris  | P5  | Paris  | J2  | Rome   |
| S3  | Paris  | P3  | Rome   | J1  | Paris  |
| S3  | Paris  | P4  | London | J2  | Rome   |
| S4  | London | P6  | London | J3  | Athens |
| S5  | Athens | P1  | London | J4  | Athens |
| S5  | Athens | P2  | Paris  | J2  | Rome   |
| S5  | Athens | P2  | Paris  | J4  | Athens |
| S5  | Athens | P3  | Rome   | J4  | Athens |
| S5  | Athens | P4  | London | J4  | Athens |
| S5  | Athens | P5  | Paris  | J4  | Athens |
| S5  | Athens | P5  | Paris  | J5  | London |
| S5  | Athens | P5  | Paris  | J7  | London |
| S5  | Athens | P6  | London | J2  | Rome   |
| S5  | Athens | P6  | London | J4  | Athens |
+-----+--------+-----+--------+-----+--------+
23 rows in set (0.00 sec)
8.  Get all supplier number/part number/project number triples such that no two indicated supplier, part and project all are colocated.
mysql> select distinct S.SNO, S.CITY, P.PNO, P.CITY, J.JNO, J.CITY from S, P, J, SPJ where S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND J.JNO=SPJ.JNO AND (S.CITY!=P.CITY AND S.CITY!=J.CITY AND P.CITY!=J.CITY);
+-----+--------+-----+--------+-----+--------+
| SNO | CITY   | PNO | CITY   | JNO | CITY   |
+-----+--------+-----+--------+-----+--------+
| S2  | Paris  | P3  | Rome   | J3  | Athens |
| S2  | Paris  | P3  | Rome   | J4  | Athens |
| S2  | Paris  | P3  | Rome   | J5  | London |
| S2  | Paris  | P3  | Rome   | J6  | Oslo   |
| S2  | Paris  | P3  | Rome   | J7  | London |
| S3  | Paris  | P4  | London | J2  | Rome   |
| S5  | Athens | P2  | Paris  | J2  | Rome   |
| S5  | Athens | P5  | Paris  | J5  | London |
| S5  | Athens | P5  | Paris  | J7  | London |
| S5  | Athens | P6  | London | J2  | Rome   |
+-----+--------+-----+--------+-----+--------+
10 rows in set (0.00 sec)
9. Get full details for parts supplied by a supplier in London.
mysql> select DISTINCT P.PNO,PNAME,COLOR,WEIGHT,P.CITY from P,S,SPJ where S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND S.CITY="London";
+-----+-------+-------+------+--------+
| PNO | PNAME | COLOR | WEIGHT   | CITY   |
+-----+-------+-------+------+--------+
| P1  | Nut   | Red   |   12 | London |
| P6  | Cog   | Red   |   19 | London |
+-----+-------+-------+------+--------+
2 rows in set (0.00 sec)
10. Get part numbers for parts supplied by a supplier in London to a project in London.
mysql> select DISTINCT PNO from S,J,SPJ where S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY="LONDON" AND J.CITY="London";
+-----+
| PNO |
+-----+
| P6  |
+-----+
1 row in set (0.00 sec)
11. Get all pairs of city names such that a supplier in the first city supplies a project in the second city.
mysql> select distinct S.CITY, J.CITY from S, J, SPJ where SPJ.SNO=S.SNO and SPJ.JNO=J.JNO and S.CITY!=J.CITY;
+--------+--------+
| CITY   | CITY   |
+--------+--------+
| London | Paris  |
| London | Athens |
| Paris  | Rome   |
| Paris  | Athens |
| Paris  | London |
| Paris  | Oslo   |
| Athens | Rome   |
| Athens | London |
+--------+--------+
8 rows in set (0.00 sec)
12. Get part numbers for parts supplied to a project by a supplier in the same city in that project.
mysql> select DISTINCT PNO from S,J,SPJ where S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND J.CITY=S.CITY;
+-----+
| PNO |
+-----+
| P3  |
| P1  |
| P2  |
| P4  |
| P5  |
| P6  |
+-----+
6 rows in set (0.00 sec)
13. Get project numbers for projects supplied by at least one supplier not in the same city.
mysql> select DISTINCT J.JNO,J.CITY,S.SNO,S.CITY from S,J,SPJ where S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND J.CITY!=S.CITY;
+-----+--------+-----+--------+
| JNO | CITY   | SNO | CITY   |
+-----+--------+-----+--------+
| J1  | Paris  | S1  | London |
| J4  | Athens | S1  | London |
| J2  | Rome   | S2  | Paris  |
| J3  | Athens | S2  | Paris  |
| J4  | Athens | S2  | Paris  |
| J5  | London | S2  | Paris  |
| J6  | Oslo   | S2  | Paris  |
| J7  | London | S2  | Paris  |
| J2  | Rome   | S3  | Paris  |
| J3  | Athens | S4  | London |
| J2  | Rome   | S5  | Athens |
| J5  | London | S5  | Athens |
| J7  | London | S5  | Athens |
+-----+--------+-----+--------+
13 rows in set (0.01 sec)
14. Get all pairs of part numbers such that some supplier supplies a both the indicated parts.
mysql> select distinct a.PNO, b.PNO, b.SNO from SPJ a, SPJ b where a.SNO=b.SNO and a.PNO!=b.PNO;
+-----+-----+-----+
| PNO | PNO | SNO |
+-----+-----+-----+
| P3  | P5  | S2  |
| P5  | P3  | S2  |
| P3  | P4  | S3  |
| P4  | P3  | S3  |
| P1  | P2  | S5  |
| P1  | P3  | S5  |
| P1  | P4  | S5  |
| P1  | P5  | S5  |
| P1  | P6  | S5  |
| P2  | P1  | S5  |
| P2  | P3  | S5  |
| P2  | P4  | S5  |
| P2  | P5  | S5  |
| P2  | P6  | S5  |
| P3  | P1  | S5  |
| P3  | P2  | S5  |
| P3  | P4  | S5  |
| P3  | P5  | S5  |
| P3  | P6  | S5  |
| P4  | P1  | S5  |
| P4  | P2  | S5  |
| P4  | P3  | S5  |
| P4  | P5  | S5  |
| P4  | P6  | S5  |
| P5  | P1  | S5  |
| P5  | P2  | S5  |
| P5  | P3  | S5  |
| P5  | P4  | S5  |
| P5  | P6  | S5  |
| P6  | P1  | S5  |
| P6  | P2  | S5  |
| P6  | P3  | S5  |
| P6  | P4  | S5  |
| P6  | P5  | S5  |
+-----+-----+-----+
34 rows in set (0.00 sec)
15. Get total numbers of projects supplied by supplier S1.
mysql> select count(JNO) from SPJ where SNO="S1";
+------------+
| count(JNO) |
+------------+
|          2 |
+------------+
1 row in set (0.01 sec)
16. Get the total quantity of part P1 supplied by supplier S1.
mysql> select sum(QTY) from SPJ where SNO="S1" and PNO="p1";
+----------+
| sum(QTY) |
+----------+
|      900 |
+----------+
1 row in set (0.00 sec)
17. For each part being supplied to a project, get the part number, the project number and the corresponding total quantity.
mysql> select PNO, JNO, sum(QTY) "Total QTY" from SPJ group by PNO,JNO;
+-----+-----+-----------+
| PNO | JNO | Total QTY |
+-----+-----+-----------+
| P1  | J1  |       200 |
| P1  | J4  |       800 |
| P2  | J2  |       200 |
| P2  | J4  |       100 |
| P3  | J1  |       600 |
| P3  | J2  |       200 |
| P3  | J3  |       200 |
| P3  | J4  |       700 |
| P3  | J5  |       600 |
| P3  | J6  |       400 |
| P3  | J7  |       800 |
| P4  | J2  |       500 |
| P4  | J4  |       800 |
| P5  | J2  |       100 |
| P5  | J4  |       400 |
| P5  | J5  |       500 |
| P5  | J7  |       100 |
| P6  | J2  |       200 |
| P6  | J3  |       300 |
| P6  | J4  |       500 |
| P6  | J7  |       300 |
+-----+-----+-----------+
21 rows in set (0.00 sec)
18. Get part numbers for parts supplied to some project in an average quantity of more than 350.
mysql> select PNO, avg(QTY) from SPJ group by PNO having avg(QTY)>350;
+-----+----------+
| PNO | avg(QTY) |
+-----+----------+
| P3  | 388.8889 |
| P4  | 650.0000 |
+-----+----------+
2 rows in set (0.00 sec)
19. Get project names for projects supplied by supplier S1.
mysql> select JNAME from J,SPJ where J.JNO=SPJ.JNO and SNO="S1";
+---------+
| JNAME   |
+---------+
| Sorter  |
| Console |
+---------+
2 rows in set (0.00 sec)
20. Get colors of parts supplied by supplier S1.
mysql> select DISTINCT P.PNO, P.COLOR FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.SNO="S1";
+-----+-------+
| PNO | COLOR |
+-----+-------+
| P1  | Red   |
+-----+-------+
1 row in set (0.00 sec)
21. Get part numbers for parts supplied to any project in London.
mysql> SELECT DISTINCT PNO FROM SPJ WHERE JNO IN(SELECT JNO FROM J WHERE CITY="London");
+-----+
| PNO |
+-----+
| P3  |
| P6  |
| P5  |
+-----+
3 rows in set (0.00 sec)
22. Get project numbers for projects using at least one part available from supplier S1.
mysql> SELECT DISTINCT JNO FROM SPJ WHERE PNO IN(SELECT PNO FROM SPJ WHERE SNO="S1");
+-----+
| JNO |
+-----+
| J1  |
| J4  |
+-----+
2 rows in set (0.00 sec)
23. Get supplier numbers for suppliers supplying at least one part supplied by at least one supplier who supplies at least one red part.
mysql> SELECT DISTINCT SNO FROM SPJ WHERE PNO IN(SELECT PNO FROM SPJ WHERE SNO IN(SELECT SNO FROM SPJ WHERE PNO IN(SELECT PNO FROM P WHERE COLOR="Red")));
+-----+
| SNO |
+-----+
| S1  |
| S2  |
| S3  |
| S4  |
| S5  |
+-----+
5 rows in set (0.00 sec)
24. Get supplier numbers for suppliers with a status lower than that of supplier S1.
mysql> select SNO from S where STATUS<(select STATUS from S where SNO="S1");
+-----+
| SNO |
+-----+
| S2  |
+-----+
1 row in set (0.02 sec)
25. Get project numbers for projects whose city is in first alphabetic list of such cities.
mysql> select JNO, CITY from J where CITY in(select min(CITY) from J);
+-----+--------+
| JNO | CITY   |
+-----+--------+
| J3  | Athens |
| J4  | Athens |
+-----+--------+
2 rows in set (0.00 sec)
26. Get project numbers for projects supplied with part P1 in an average quantity greater than the greatest quantity in which any part is supplied to project J1.
mysql> select JNO from SPJ where PNO="P1" group by JNO having avg(QTY)>(select max(QTY) from SPJ where JNO="J1");
Empty set (0.00 sec)
27. Get supplier numbers for suppliers supplying some project with part P1 in a quantity greater than the average shipment quantity of part P1 for that project.
mysql> SELECT SNO, QTY, JNO FROM SPJ WHERE PNO="P1" AND QTY>(SELECT AVG(QTY) FROM SPJ GROUP BY PNO AND JNO IN(SELECT JNO FROM SPJ WHERE PNO="P1"));
+-----+------+-----+
| SNO | QTY  | JNO |
+-----+------+-----+
| S1  |  700 | J4  |
+-----+------+-----+
1 row in set (0.00 sec)
28. Get project numbers for projects not supplied with any red part by any London supplier.
mysql> SELECT DISTINCT JNO FROM SPJ WHERE JNO NOT IN(SELECT DISTINCT JNO FROM SPJ,P,S WHERE SPJ.PNO=P.PNO AND SPJ.SNO=S.SNO AND P.COLOR="RED" AND S.CITY="London");
+-----+
| JNO |
+-----+
| J2  |
| J5  |
| J6  |
+-----+
3 rows in set (0.00 sec)
29. Get project numbers for projects supplied entirely by supplier S1.
mysql> SELECT JNO FROM SPJ WHERE SNO="S1" AND JNO IN(SELECT JNO FROM SPJ GROUP BY JNO HAVING COUNT(DISTINCT SNO)=1);
Empty set (0.00 sec)
30. Get part numbers for parts supplied to all projects in London.
mysql> SELECT P.PNO FROM P WHERE NOT EXISTS(SELECT JNO FROM J WHERE J.CITY="London" AND JNO NOT IN (SELECT JNO FROM SPJ WHERE SPJ.PNO=P.PNO));
+-----+
| PNO |
+-----+
| P3  |
| P5  |
+-----+
2 rows in set (0.00 sec)
31. Get supplier numbers for suppliers who supply the same part to all projects.
mysql> SELECT SNO,PNO FROM SPJ GROUP BY SNO,PNO HAVING COUNT(DISTINCT JNO)=(SELECT COUNT(JNO) FROM J);
+-----+-----+
| SNO | PNO |
+-----+-----+
| S2  | P3  |
+-----+-----+
1 row in set (0.00 sec)
32. Get project numbers for projects supplied with at least one part available from suppliers.
mysql> select distinct JNO from SPJ;
+-----+
| JNO |
+-----+
| J1  |
| J4  |
| J2  |
| J3  |
| J5  |
| J6  |
| J7  |
+-----+
7 rows in set (0.00 sec)
33. Get all cities in which at least one supplier, part or project is located.
 mysql> (SELECT CITY FROM S) UNION (SELECT CITY FROM P) UNION (SELECT CITY FROM J);
+--------+
| CITY   |
+--------+
| London |
| Paris  |
| Athens |
| Rome   |
| Oslo   |
+--------+
5 rows in set (0.00 sec)
34. Get part numbers for parts that are supplied either by a London supplier or to a London project.
mysql> (SELECT SPJ.PNO FROM S, SPJ WHERE S.SNO=SPJ.SNO AND S.CITY="London") UNION (SELECT SPJ.PNO FROM J,SPJ WHERE J.JNO=SPJ.JNO AND J.CITY="LONDON");
+-----+
| PNO |
+-----+
| P1  |
| P6  |
| P3  |
| P5  |
+-----+
4 rows in set (0.00 sec)
35. Get supplier number / part number pairs such that the indicated supplier does not supply the indicated part.
mysql> select DISTINCT SNO,PNO FROM S,P WHERE NOT EXISTS(SELECT SNO,PNO FROM SPJ WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO);
+-----+-----+
| SNO | PNO |
+-----+-----+
| S2  | P1  |
| S3  | P1  |
| S4  | P1  |
| S1  | P2  |
| S2  | P2  |
| S3  | P2  |
| S4  | P2  |
| S1  | P3  |
| S4  | P3  |
| S1  | P4  |
| S2  | P4  |
| S4  | P4  |
| S1  | P5  |
| S3  | P5  |
| S4  | P5  |
| S1  | P6  |
| S2  | P6  |
| S3  | P6  |
+-----+-----+
18 rows in set (0.00 sec)
36. Get all pairs of supplier numbers, Sx and Sy say, such that Sx and Sy supply exactly the same set of parts each.
mysql> SELECT DISTINCT SPJ1.SNO, SPJ.SNO, SPJ.PNO FROM SPJ, SPJ SPJ1 WHERE SPJ.PNO=SPJ1.PNO AND SPJ.SNO!=SPJ1.SNO;
+-----+-----+-----+
| SNO | SNO | PNO |
+-----+-----+-----+
| S1  | S5  | P1  |
| S2  | S3  | P3  |
| S2  | S5  | P3  |
| S2  | S5  | P5  |
| S3  | S2  | P3  |
| S3  | S5  | P3  |
| S3  | S5  | P4  |
| S4  | S5  | P6  |
| S5  | S1  | P1  |
| S5  | S2  | P3  |
| S5  | S3  | P3  |
| S5  | S3  | P4  |
| S5  | S2  | P5  |
| S5  | S4  | P6  |
+-----+-----+-----+
14 rows in set (0.00 sec)