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.