5. Data Stores: SQL and NoSQL Databases

Activity 5.01: Managing the Inventory of an E-Commerce Website Using a MySQL Query

Solution

1. Open a Terminal and run the MySQL client using the following command, based on your OS:

   Windows:

   mysql

   Linux:

   sudo mysql

   macOS:

   mysql

2. Create and select the PacktFashion database using the following commands:

   Create database PacktFashion;
   use PacktFashion;

   You should get the following output:

   Figure 5.71: Created and selected database for operation

   Next, we will create the tables as per the data model.

3. Create the manufacturer table based on the data model, as shown in the following query:

   CREATE TABLE manufacturer (m_id INT,
   m_name TEXT,
   m_created_at TIMESTAMP,
   PRIMARY KEY (m_id)
   );

4. Create the products table based on the data model, as shown in the following query:

   CREATE TABLE products (p_id INT,
   p_name TEXT,
   p_buy_price FLOAT,
   p_manufacturer_id INT,
   p_created_at TIMESTAMP,
   PRIMARY KEY (p_id),
   FOREIGN KEY (p_manufacturer_id)
     ...