- Xempleris systems owns a business dealing with spare parts. This business is aiming for increased sales and a more efficient spare parts supply chain. The database designer at Xempleris has designed the following entities: Stock, Parts and Suppliers
where:
Parts contains details of parts,
Supplier contains details of suppliers, and Stock contains stock details.
| SNo | PNo | Qty |
| S1 | P1 | 300 |
| S3 | P2 | 200 |
| S1 | P3 | 400 |
| S3 | 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 |
Stock
Parts
| PNo | Pname | Colour | Weight | unitCost |
| P1 | Nut | Red | 12 | 10 |
| P2 | Bolt | Green | 17 | 16 |
| P3 | Screw | Blue | 17 | 25 |
| P4 | Screw | Red | 14 | 23 |
| P5 | Cam | Blue | 12 | 40 |
| P6 | Cog | Red | 19 | 33 |
Suppliers
| SNo | SName | Contact | City |
| S1 | Smith | Mark Dras | London |
| S2 | Jones | David Clark | Paris |
| S3 | Blake | John Smith | Paris |
| S4 | Clark | Nancy Vo | London |
| S5 | Adams | Bill Zhank | Athens |
Given the suppliers-parts database, write the SQL statements for the following queries. You also need to explain the formulation of the query with relational algebra.
- List the full details of all suppliers in Paris.
- List the name and weight of all parts that cost between $15 and $25 per subject. Sort the result in ascending order of part name.
- List the full details of all suppliers who have not supplied any parts yet.
- List the total number of parts that have been supplied by each supplier.
