Tuesday, October 15, 2013

A Dirty Introduction to MySQL

In course of metamorphosis from a Physicist into a Computer scientist, I am currently taking a course on Database design and management. An important component of DBMS is DQL (Data Querey Language), which help us to generate views (virtual tables) using data from inter-related base (physical) tables. Following summary is based on the notes taken inside one of the lectures taken recently: Consider a small relational database consisting of three relations: S, P and SP
The relation S
S# SNAME STATUS CITY
S1 Rick 30 Geneve
S2 Jeffrey 20 Paris
S3 Vitaliano 30 Roma
S4 Alexandre 10 Moscow
S5 Mehmet 10 Ankara
The relation P:
P# PNAME COLOR WEIGHT
P1 BOLT BLUE 12
P2 BOLT RED 17
P3 NUT GREEN 10
P4 SCREW GREEN 15
P5 CAM BLUE 12
P6 COG RED 10
The relation SP:
S# P# QTY
S1 P2 300
S2 P2 200
S2 P6 100
S3 P4 400
S3 P5 300
S3 P6 200
S4 P1 100
S4 P2 100
S1 P6 300
S5 P2 100
S5 P3 300
S5 P1 200



INTERACTIVE SQL QUERIES

Select one of the databases for use:
using database example;
Summary of all the tables in SP:
mysql> show tables; 
+-------------------+
| Tables_in_example |
+-------------------+
| P                 |
| S                 |
| SP                |
+-------------------+
3 rows in set (0.00 sec)
Now let us try generating some views from data stored in database:
  1. Get Supplier Ids and Supplier Status for the ones in Paris 
    mysql> SELECT * FROM S WHERE CITY="PARIS";
+-----+-----------+--------+-------+
| SID | SNAME     | STATUS | CITY  |
+-----+-----------+--------+-------+
| S2  | Jefferey  |     20 | Paris |
| S4  | Alexandre |     10 | Paris |
+-----+-----------+--------+-------+
2 rows in set (0.01 sec)
  2. Get ParID for all the parts supplied: 
    mysql> select PID from SP;
+-----+
| PID |
+-----+
| P2  |
| P6  |
| P2  |
| P6  |
| P4  |
| P5  |
| P6  |
| P1  |
| P2  |
| P1  |
| P2  |
| P3  |
+-----+
12 rows in set (0.00 sec)
  3. Which parts have even been supplied? 
    SELECT DISTINCT PID FROM SP;
+-----+
| PID |
+-----+
| P2  |
| P6  |
| P4  |
| P5  |
| P1  |
| P3  |
+-----+
6 rows in set (0.00 sec)
  4. List details for all the suppliers: 
    mysql> SELECT * FROM S;
+-----+-----------+--------+--------+
| SID | SNAME     | STATUS | CITY   |
+-----+-----------+--------+--------+
| S1  | Rick      |     30 | Geneve |
| S2  | Jefferey  |     20 | Paris  |
| S3  | Vitaliano |     30 | Roma   |
| S4  | Alexandre |     10 | Paris  |
| S5  | Tylan     |     10 | Ankara |
+-----+-----------+--------+--------+
5 rows in set (0.00 sec)
  5. Select supplier details from Paris whose status is greater than 10 
    mysql> SELECT * FROM S WHERE STATUS>10 AND CITY="PARIS";
+-----+----------+--------+-------+
| SID | SNAME    | STATUS | CITY  |
+-----+----------+--------+-------+
| S2  | Jefferey |     20 | Paris |
+-----+----------+--------+-------+
1 row in set (0.00 sec)
  6. List all the suppliers who are not from Paris, 
    mysql> select * from S where CITY<>"PARIS";
+-----+-----------+--------+--------+
| SID | SNAME     | STATUS | CITY   |
+-----+-----------+--------+--------+
| S1  | Rick      |     30 | Geneve |
| S3  | Vitaliano |     30 | Roma   |
| S5  | Tylan     |     10 | Ankara |
+-----+-----------+--------+--------+
3 rows in set (0.00 sec)
  7. List all suppliers not from Paris, in descending order 
    mysql> SELECT * FROM S WHERE STATUS>10 
    AND CITY<>"PARIS" ORDER BY STATUS DESC;
+-----+-----------+--------+--------+
| SID | SNAME     | STATUS | CITY   |
+-----+-----------+--------+--------+
| S1  | Rick      |     30 | Geneve |
| S3  | Vitaliano |     30 | Roma   |
+-----+-----------+--------+--------+
2 rows in set (0.00 sec)
  8. For each part get PartID and Cities supplying it 
    mysql> select DISTINCT PID, CITY from SP,S 
    where SP.SID=S.SID order by PID;
+-----+--------+
| PID | CITY   |
+-----+--------+
| P1  | Paris  |
| P1  | Ankara |
| P2  | Paris  |
| P2  | Geneve |
| P2  | Ankara |
| P3  | Ankara |
| P4  | Roma   |
| P5  | Roma   |
| P6  | Paris  |
| P6  | Roma   |
| P6  | Geneve |
+-----+--------+
11 rows in set (0.00 sec)
  9. Ex6: List the supplier numbers for all pairs of suppliers such that two suppliers are located in the same city. 
    mysql> SELECT T1.SID, T2.SID FROM S AS T1, S AS T2 
    WHERE T1.CITY=T2.CITY AND T1.SI>T2.SID;
+-----+-----+
| SID | SID |
+-----+-----+
| S2  | S4  |
+-----+-----+
1 row in set (0.00 sec)
  10. List all the suppliers who have supplied P2: 
    mysql> SELECT DISTINCT SNAME FROM S,SP 
    WHERE S.SID=SP.SID AND SP.PID="P2";
+-----------+
| SNAME     |
+-----------+
| Rick      |
| Jefferey  |
| Alexandre |
| Tylan     |
+-----------+
4 rows in set (0.00 sec)
  11. List the suppliers who have supplied RED colored parts: 
    mysql> SELECT DISTINCT SNAME FROM S,P,SP 
     WHERE S.SID=SP.SID AND SP.PID=P.PID AND P.COLOR="RED";
+-----------+
| SNAME     |
+-----------+
| Rick      |
| Jefferey  |
| Vitaliano |
| Alexandre |
| Tylan     |
+-----------+
5 rows in set (0.00 sec)
  12. List suppliers who have supplied P2 using IN command: 
    mysql> SELECT SNAME FROM S WHERE 
    S.SID IN (SELECT DISTINCT SID FROM SP WHERE SP.PID="P2");
+-----------+
| SNAME     |
+-----------+
| Rick      |
| Jefferey  |
| Alexandre |
| Tylan     |
+-----------+
4 rows in set (0.00 sec)
  13. List the suppliers who have supplied RED colored parts using IN command: 
    mysql> SELECT SNAME FROM S WHERE S.SID IN (SELECT DISTINCT 
    SP.SID FROM SP, P WHERE SP.PID=P.PID AND P.COLOR="RED");
+-----------+
| SNAME     |
+-----------+
| Rick      |
| Jefferey  |
| Vitaliano |
| Alexandre |
| Tylan     |
+-----------+
5 rows in set (0.00 sec)
  14. List the supplier numbers for suppliers with status less than the current maximum status value in the S table 
    mysql> SELECT SID FROM S WHERE STATUS 
    < ANY (SELECT STATUS FROM S);
+-----+
| SID |
+-----+
| S2  |
| S4  |
| S5  |
+-----+
3 rows in set (0.00 sec)
  15. For each Part, get the PID and the total number of suppliers supplying the part. 
    mysql>  SELECT PID,COUNT(SID) AS NUMSPPLRS FROM SP GROUP BY PID ;

+-----+-----------+
| PID | NUMSPPLRS |
+-----+-----------+
| P1  |         2 |
| P2  |         4 |
| P3  |         1 |
| P4  |         1 |
| P5  |         1 |
| P6  |         3 |
+-----+-----------+
6 rows in set (0.00 sec)
  16. List the part numbers for all parts supplied by more than one supplier. 
    mysql> SELECT PID FROM SP GROUP BY SP.PID HAVING COUNT(*)>1;
+-----+
| PID |
+-----+
| P1  |
| P2  |
| P6  |
+-----+
3 rows in set (0.00 sec)
  17. List total number of currently registered suppliers, 
    mysql> SELECT COUNT(*) FROM S ;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
  18. Count the number of suppliers, who have actually made a supply 
    mysql> SELECT COUNT(SID) FROM
     (SELECT DISTINCT SID FROM SP ) AS A;
+------------+
| COUNT(SID) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)
  19. Get the total quantity of part P2 being supplied 
    mysql> SELECT SUM(QTY) FROM SP WHERE SP.PID="P2";
+----------+
| SUM(QTY) |
+----------+
|      700 |
+----------+
1 row in set (0.00 sec)
  20. List suppliers whose name starts with letter A 
    mysql> SELECT * FROM S WHERE SNAME LIKE "A%";
+-----+-----------+--------+-------+
| SID | SNAME     | STATUS | CITY  |
+-----+-----------+--------+-------+
| S4  | Alexandre |     10 | Paris |
+-----+-----------+--------+-------+
1 row in set (0.00 sec)
In the next post I will post the notes for the lecture on Data Definition Language, so as to document the metamorphosis.
Posted by at No comments:
Subscribe to: Posts (Atom)