MySQL Query Set-1

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), WT int(2), CITY varchar(10));
Query OK, 0 rows affected (0.31 sec)
   
 

Ø  Creation of table SP:
mysql> create table SP(SNO varchar(2) references S, PNO varchar(2) references P,QTY int(3), primary key(SNO,PNO));
Query OK, 0 rows affected (0.25 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 SP:
mysql> insert into SP values("S1","P1",300);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SP values("S1","P2",200);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SP values("S1","P3",400);
Query OK, 1 row affected (0.07 sec)
mysql> insert into SP values("S1","P4",200);
Query OK, 1 row affected (0.07 sec)
mysql> insert into SP values("S1","P5",100);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SP values("S1","P6",100);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SP values("S2","P1",300);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SP values("S2","P2",400);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SP values("S3","P2",200);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SP values("S4","P2",200);
Query OK, 1 row affected (0.03 sec)
mysql> insert into SP values("S4","P4",300);
Query OK, 1 row affected (0.04 sec)
mysql> insert into SP values("S4","P5",400);
Query OK, 1 row affected (0.04 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 | WT   | 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 SP:
mysql> select * from SP;
+-----+-----+------+
| SNO | PNO | QTY  |
+-----+-----+------+
| S1  | P1  |  300 |
| S1  | P2  |  200 |
| S1  | P3  |  400 |
| S1  | P4  |  200 |
| S1  | P5  |  100 |
| S1  | P6  |  100 |
| S2  | P1  |  300 |
| S2  | P2  |  400 |
| S3  | P2  |  200 |
| S4  | P2  |  200 |
| S4  | P4  |  300 |
| S4  | P5  |  400 |
+-----+-----+------+
12 rows in set (0.00 sec)

QUERIES
1. Get full details of all parts in London.
mysql> select * from P where CITY="London";
+-----+-------+-------+------+--------+
| PNO | PNAME | COLOR | WT   | CITY   |
+-----+-------+-------+------+--------+
| P1  | Nut   | Red   |   12 | London |
| P4  | Screw | Red   |   14 | London |
| P6  | Cog   | Red   |   19 | London |
+-----+-------+-------+------+--------+
3 rows in set (0.00 sec)
2. Get SNO for suppliers who supply the part P1.
mysql> select SNO from SP where PNO="P1";
+-----+
| SNO |
+-----+
| S1  |
| S2  |
+-----+
2 rows in set (0.00 sec)
3. Get all part-color/part-city combinations.
mysql> select distinct PNO, COLOR, PNO, CITY from P;
+-----+-------+-----+--------+
| PNO | COLOR | PNO | CITY   |
+-----+-------+-----+--------+
| P1  | Red   | P1  | London |
| P2  | Green | P2  | Paris  |
| P3  | Blue  | P3  | Rome   |
| P4  | Red   | P4  | London |
| P5  | Blue  | P5  | Paris  |
| P6  | Red   | P6  | London |
+-----+-------+-----+--------+
6 rows in set (0.00 sec)
4. Get all SNO/PNO doubles such that all are co-located.
mysql> select distinct SNO, PNO, S.CITY from S, P where S.CITY=P.CITY; 
+-----+-----+--------+
| SNO | PNO | CITY   |
+-----+-----+--------+
| S1  | P1  | London |
| S4  | P1  | London |
| S2  | P2  | Paris  |
| S3  | P2  | Paris  |
| S1  | P4  | London |
| S4  | P4  | London |
| S2  | P5  | Paris  |
| S3  | P5  | Paris  |
| S1  | P6  | London |
| S4  | P6  | London |
+-----+-----+--------+
10 rows in set (0.02 sec)
5. Get all SNO, PNO doubles such that they are not all co-located.
mysql> select distinct SNO, S.CITY, PNO, P.CITY from S, P where NOT S.CITY=P.CITY; 
+-----+--------+-----+--------+
| SNO | CITY   | PNO | CITY   |
+-----+--------+-----+--------+
| S2  | Paris  | P1  | London |
| S3  | Paris  | P1  | London |
| S5  | Athens | P1  | London |
| S1  | London | P2  | Paris  |
| S4  | London | P2  | Paris  |
| S5  | Athens | P2  | Paris  |
| S1  | London | P3  | Rome   |
| S2  | Paris  | P3  | Rome   |
| S3  | Paris  | P3  | Rome   |
| S4  | London | P3  | Rome   |
| S5  | Athens | P3  | Rome   |
| S2  | Paris  | P4  | London |
| S3  | Paris  | P4  | London |
| S5  | Athens | P4  | London |
| S1  | London | P5  | Paris  |
| S4  | London | P5  | Paris  |
| S5  | Athens | P5  | Paris  |
| S2  | Paris  | P6  | London |
| S3  | Paris  | P6  | London |
| S5  | Athens | P6  | London |
+-----+--------+-----+--------+
20 rows in set (0.00 sec)
6. Get PNO for parts supplied by a supplies in London.
mysql> select distinct(PNO) from SP, S where S.CITY="London";
+-----+
| PNO |
+-----+
| P1  |
| P2  |
| P3  |
| P4  |
| P5  |
| P6  |
+-----+
6 rows in set (0.00 sec)
7. Get all pairs of cities such that a supplier in the first city supplies a part in the second city.
mysql> select distinct S.CITY, P.CITY from S, P, SP where SP.SNO=S.SNO and SP.PNO=P.PNO and S.CITY!=P.CITY;
+--------+--------+
| CITY   | CITY   |
+--------+--------+
| London | Paris  |
| London | Rome   |
| Paris  | London |
+--------+--------+
3 rows in set (0.01 sec)