Introduction MySQL is a relational database management system considered to be the most popular open source database in the world, especially for the web development environments we are interested in.
What are the main objectives of this project?
⦁ Consolidate queries to a database.
⦁ To understand how relationships between tables are realized.
⦁ Consolidate how to interact with data stored in the database.
1.W3Schools Exercises.
⦁ Perform the following w3schools sql exercises. W3Schools SQL Exercises
⦁ Take the w3schools quiz. W3Schools SQL Quiz
2.1.Create diagram
⦁ Create a diagram using DBeaver of an e-commerce (online store) database with the following tables: User table Product table Order table Category table
It should show the types of relationships between each table. *Remember that in the case of a many-to-many relationship you will need an intermediate table.
2.2. Execute the following SQL queries
2.2.1 INSERT DATA
⦁ Insert at least 5 new users. ⦁ Insert at least 5 new products. ⦁ Insert at least 5 new orders. ⦁ Insert at least 2 types of categories.
2.2.2 UPDATING DATA
⦁ Change the name of a product. To do this, generate a query that affects only a certain product based on its id. ⦁ Change the price of a product to 50€. To do so, it generates a query that affects only a certain product according to its id.
2.2.3 GET DATA
⦁ Selects all the products with a price higher than 20€. ⦁ Displays the products in descending order. ⦁ Select all products and display the category to which they belong. ⦁ Select all users and display their orders. ⦁ Select a product by its id and show the category to which it belongs. ⦁ Select a user by his id and show the orders he has.
- Extra
3.1.1 DELETE DATA
⦁ Deletes a product by its id.
3.2 Update diagram
⦁ Create a new reviews table and add it to the diagram, also specifying the relationship type.
3.3. Execute the following SQL queries
3.3.1 INSERT DATA
Insert at least 5 new reviews.
3.3.2 UPDATE DATA
⦁ Change the content of a review
3.3.3 GET DATA
⦁ Select all reviews. ⦁ Select all products with their respective reviews. ⦁ Displays a product with its reviews. ⦁ Displays the products together with the category it belongs to and its reviews. ⦁ Selects a user and displays his orders together with the products contained in each order.
3.3.4 DELETE DATA
⦁ Deletes a review by its id.