MySQL

SQL practice with CRUD operations.

C - CREATE
R - READ
U - UPDATE
D - DELETE

The created database consist of 3 tables:

shopping_list containing fields with names: id, product_name, price, quantity;
fridge containing fields with names: item_id, product_id, product_name, quantity, expiration_date;
order containing fields with names: order_id, product_id, item_id, date, quantity.

The repository contains the following queries:

Create the database SQL query
Use the database SQL query
Create the shopping_list table SQL query
Create the fridge table SQL query
Add primary key in the fridge table SQL query
Create an order table in an existing database SQL query
Insert values into shopping_list table SQL query
Insert values into fridge table SQL query
Add 10 products to the fridge table, 5 of which are in shopping_list SQL query
Add the products in the order table (fill it with data according to the columns that are available) SQL query
Link fridge table to the shopping list table using a foreign key SQL query
Relate the order table to the fridge and shopping_list tables SQL query AND SQL query

Display products that are not in the shopping_list table SQL query
Display information from the shopping_list table about the most expensive product SQL query
Display information from the shopping_list table about the cheapest product SQL query
Calculate the average cost of products from the shopping_list table SQL query
Display information from the fridge about the number of products SQL query
Display information from the fridge about number of products in the shopping list SQL query
Display information from the fridge about the total number of all products in the fridge table SQL query
Display information about the number of products and their names (select columns from the fridge table). Group the result by expiration date SQL query
Display all products starting with the letter M from shopping_list table SQL query
Use the JOIN operator to make a query to find products from the shopping list that are already in the fridge SQL query
Display information about products that are not in the fridge table but are in the shopping_list table SQL query
Display information about all products from the order table that are in the shopping_list table and have expired SQL query

Update the expiration date in the order table where quantity is equal to 6 SQL query
Delete any record from the order table SQL query

The Entity Relationship Diagram of created database:

Name		Name	Last commit message	Last commit date
Latest commit History 11 Commits
README.md		README.md
Screenshot_30.png		Screenshot_30.png
add_foreign_key.sql		add_foreign_key.sql
add_foreign_key_order_with_fridge.sql		add_foreign_key_order_with_fridge.sql
add_foreign_key_order_with_shopping_list.sql		add_foreign_key_order_with_shopping_list.sql
add_primary_key_fridge.sql		add_primary_key_fridge.sql
avg_price_from_SL.sql		avg_price_from_SL.sql
count_SL_from_f.sql		count_SL_from_f.sql
count_all_from_f.sql		count_all_from_f.sql
create_db.sql		create_db.sql
create_tb_fridge.sql		create_tb_fridge.sql
create_tb_order.sql		create_tb_order.sql
create_tb_shopping_list.sql		create_tb_shopping_list.sql
delete_item.sql		delete_item.sql
group_by_from_f.sql		group_by_from_f.sql
insert_into_fridge.sql		insert_into_fridge.sql
insert_into_fridge_new_values.sql		insert_into_fridge_new_values.sql
insert_into_order.sql		insert_into_order.sql
insert_into_shopping_list.sql		insert_into_shopping_list.sql
max_price_from_SL.sql		max_price_from_SL.sql
min_price_from_SL.sql		min_price_from_SL.sql
select_all_from_shopping_list.sql		select_all_from_shopping_list.sql
select_all_where_date.sql		select_all_where_date.sql
select_in_join.sql		select_in_join.sql
select_not_in.sql		select_not_in.sql
select_pr_with_M.sql		select_pr_with_M.sql
select_product_from_fridge.sql		select_product_from_fridge.sql
sum_from_f.sql		sum_from_f.sql
update_date_order.sql		update_date_order.sql
use_db.sql		use_db.sql

spoy-ler/MySQL