This database engineering project features comprehensive structured query sets executed within a relational **SPJ (Suppliers, Parts, Projects)** schema. The assignment outlines relational algebra translations, structural data aggregation filters, complex pattern filters, and nested analytical conditions targeting isolated datasets.
The query scripts are separated into logical database processing structures:
user()), database names, timestamp vectors (now()), and mathematical evaluations (BIN, POW).s.city = p.city AND p.city = j.city).NOT EXISTS) and collection inclusions (NOT IN) to track parts distributions.UNION blocks to generate an unstructured list of processing nodes.The terminal logs below map out the active database session showing the SQL command structure alongside the matrix tables generated by the server engine:
[etnelson@ip-172-31-84-83 ~]$ mysql ETNELSON -u etnelson -p
Enter password:
Reading table information for completion of table and column names
Welcome to the MySQL monitor. Commands end with ; or \g.
Server version: 8.0.32 Source distribution
mysql> select user();
+--------------------+
| user() |
+--------------------+
| etnelson@localhost |
+--------------------+
mysql> select database();
+------------+
| database() |
+------------+
| ETNELSON |
+------------+
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-09-17 15:59:22 |
+---------------------+
mysql> SELECT BIN(34);
+---------+
| BIN(34) |
+---------+
| 100010 |
+---------+
mysql> SELECT POW(2,4);
+----------+
| POW(2,4) |
+----------+
| 16 |
+----------+
mysql> SELECT S_NUM from spj where J_NUM='J1';
+-------+
| S_NUM |
+-------+
| S1 |
| S2 |
| S3 |
+-------+
mysql> select * from spj where qty BETWEEN 300 AND 750;
+-------+-------+-------+------+
| s_num | p_num | j_num | qty |
+-------+-------+-------+------+
| S1 | P1 | J4 | 700 |
| S2 | P3 | J1 | 400 |
| S2 | P3 | J4 | 500 |
| S4 | P6 | J3 | 300 |
| S5 | P5 | J5 | 500 |
+-------+-------+-------+------+
mysql> select S_NUM, P_NUM, J_NUM
-> from s,p,j
-> WHERE s.city = p.city
-> AND p.city = j.city;
+-------+-------+-------+
| S_NUM | P_NUM | J_NUM |
+-------+-------+-------+
| S2 | P5 | J1 |
| S3 | P5 | J1 |
+-------+-------+-------+
mysql> select SUM(QTY) from spj WHERE S_NUM ='S1' AND P_NUM = 'P1';
+----------+
| SUM(QTY) |
+----------+
| 900 |
+----------+
mysql> select P_NUM, AVG(QTY) AS avg_qty
-> from spj
-> GROUP BY P_NUM
-> HAVING avg_qty > 350;
+-------+----------+
| P_NUM | avg_qty |
+-------+----------+
| P3 | 388.8889 |
| P4 | 650.0000 |
+-------+----------+
mysql> select CITY from(
-> select CITY from s
-> UNION
-> select CITY from p
-> UNION
-> select CITY from j
-> ) AS AllCities;
+--------+
| CITY |
+--------+
| London |
| Paris |
| Athens |
| Oslo |
| Rome |
+--------+