Gain insights into relational databases and SQL. Learn about creating, managing, and editing tables using MySQL. Explore complex queries, indexes, and join functions for efficient data handling.

testmysql.tar.gz

sql spa

Relational databases store data in a row-based table structure. Structured Query Language (SQL) is a core language used to retrieve data from the relational database. One of its most widely used extensions, MySQL, also gives you the power to edit, create, and manage the queried data.

In this course, you’ll be introduced to the basics of relational database management with SQL. You’ll use MySQL to create a table, edit the data, and even change the structure. You’ll also create indexes to quickly access data for faster performance on common queries. Additionally, you’ll explore more complex queries to extract useful data with aggregate functions, grouping, ordering, and creating limits and filters in output tables. Finally, you’ll learn how to combine data from different tables with join functions.

By the end of this course, you’ll be able to create, manage, edit, and merge relational databases with confidence. MySQL will prepare you for data projects large and small.

Getting Started with SQL and Relational Databases



# Introduction
We’ll continue the creation of our simple customer relationship management system. We already created the `customers` and `products` tables, and we’re now ready to create two more tables, `orders` and `order_items`.



# Model details

The `orders` table holds the header of the order:
   - It has a reference to the owning `customer` by the `customer_id` column. This is going to be a foreign key. Given one order, we can locate the single customer that the order belongs to. That is why the order-to-customer relationship is a one-to-one relationship. On the other hand, given one specific customer, we can locate more than one order that belong to the particular customer. Hence, the customer-to-orders relationship is a one-to-many relationship.
- Besides that, `orders` also have the `order_number`. This will be mandatory and will have a string representation like  `ORD123`. It will also be unique. No two or more orders will have the same `order_number`. Also, it will be used to quickly locate an order by `order_number`.

- ​​We’ll use ​​`ordered_at` to record the date and time an order has been placed. This will be mandatory too.

The `order_items` table holds the details of an order. Since each entry in this table belongs to an order, we see the following:
- We need the `order_id` column to reference the owning order. This is another foreign key in our database. Also, given one order item, we can locate the single order the item belongs to. Hence, the order item-to-order relationship is a one-to-one relationship. Given one order, we can locate more than one  item belonging to this particular order. Hence, the order-to-order items relationship is a one-to-many relationship.
- Moreover, each order item needs to specify which product it’s referring to. We see the existence of another foreign key here, the `product_id`. This is a reference to the product that the `order item` refers to. The order item-to-product relationship is a one-to-one relationship while product-to-order items is a one-to-many.
- The `order_items` table also has a price column. This is going to be a number with `15` digits of length and `2` decimal digits rounding. It holds the `price` of the product sold at the moment the `order item` is created. It will be mandatory.

For educational reasons, we have decided to prevent the same product from appearing in the same order more than once. So, if an order were to include the same product more than once, there would be an `order item` referencing that particular product with a `quantity` equal to the number of instances of the particular product in the order.

Having said that, how can we ask RDBMS to help implement this constraint? Maybe a unique index? We can’t necessarily link `order_items` with `product_id` because it wouldn’t allow the same product to be sold on two different orders. So, the uniqueness needs to be the combination of the `product_id` and `order_id`. We’ll see how we’re going to create a unique index on that later.


Learn to create another table with appropriate attributes and constraints.

Getting Started

Manage MySQL Server

Basic SQL Commands

Add Columns and Indexes

Count and Sort Records

Foreign Keys

Joins, Left Joins And Subqueries

Conclusion

Create the orders Table

Introduction

Model details