Database Normalization at Fred's Furniture

Hi, friends

My name is Parmeet and I am learning full stack backend. I am making this project topic of Database topic number from 29 on Database normalization. Please be free to any comments on it and if it helpful then I will very happy.
sorry for my English. The solution is given below:-

–[1]

select * from store

limit 10;

–[2]

SELECT COUNT(DISTINCT(order_id))

FROM store;

select count(DISTINCT(customer_id))

from store;

–[3]

select customer_id, customer_email, customer_phone

from store

where customer_id = 1;

–[4]

select item_1_id, item_1_name, item_1_price

from store

where item_1_id = 4;

–Create a Normalized Version of the Database

–[5]

create table customer as

select distinct customer_id, customer_phone, customer_email

from store;

–[6]

ALTER TABLE customer

ADD PRIMARY KEY (customer_id);

–[7]

CREATE TABLE items AS

SELECT DISTINCT item_1_id as item_id, item_1_name as name, item_1_price as price

FROM store

UNION

SELECT DISTINCT item_2_id as item_id, item_2_name as name, item_2_price as price

FROM store

WHERE item_2_id IS NOT NULL

UNION

SELECT DISTINCT item_3_id as item_id, item_3_name as name, item_3_price as price

FROM store

WHERE item_3_id IS NOT NULL;

–[8]

ALTER TABLE items

ADD PRIMARY KEY (item_id);

–[9]

CREATE TABLE orders_items AS

SELECT order_id, item_1_id as item_id

FROM store

UNION ALL

SELECT order_id, item_2_id as item_id

FROM store

WHERE item_2_id IS NOT NULL

UNION ALL

SELECT order_id, item_3_id as item_id

FROM store

WHERE item_3_id IS NOT NULL;

–[10]

create table orders as

select distinct order_id, order_date, customer_id

from store;

–[11]

ALTER TABLE orders

ADD PRIMARY KEY (order_id);

–[12]

ALTER TABLE orders

ADD FOREIGN KEY (customer_id)

REFERENCES customer(customer_id);

–[13]

ALTER TABLE orders_items

ADD FOREIGN KEY (item_id)

REFERENCES items(item_id);

ALTER TABLE orders_items

ADD FOREIGN KEY (order_id)

REFERENCES orders(order_id);

—Query your Databases!

–[14]

select customer_email, order_date from store

where order_date > ‘2019-07-25’

limit 10;

–[15]

SELECT customer_email

FROM customer, orders, items, orders_items

WHERE customer.customer_id = orders.customer_id

AND

order_date > ‘2019-07-25’

limit 10;

–[16]

WITH all_items AS (

SELECT item_1_id as item_id

FROM store

UNION ALL

SELECT item_2_id as item_id

FROM store

WHERE item_2_id IS NOT NULL

UNION ALL

SELECT item_3_id as item_id

FROM store

WHERE item_3_id IS NOT NULL

)

SELECT item_id, COUNT(*)

FROM all_items

GROUP BY item_id

limit 10;

–[17]

SELECT item_id, COUNT(order_id)

FROM orders_items

GROUP BY item_id

limit 10;

–[18]

select count(DISTINCT(customer_id))

from store

where order_id > 1;

WITH all_items AS (

SELECT item_1_name as item_name

FROM store

UNION ALL

SELECT item_2_name as item_name

FROM store

WHERE item_2_name IS NOT NULL

UNION ALL

SELECT item_3_name as item_name

FROM store

WHERE item_3_name IS NOT NULL

)

SELECT item_name, COUNT(*)

FROM all_items

where item_name = ‘lamp’

GROUP BY item_name

limit 10;

WITH all_items AS (

SELECT item_1_name as item_name

FROM store

UNION ALL

SELECT item_2_name as item_name

FROM store

WHERE item_2_name IS NOT NULL

UNION ALL

SELECT item_3_name as item_name

FROM store

WHERE item_3_name IS NOT NULL

)

SELECT item_name, COUNT(*)

FROM all_items

where item_name = ‘chair’

GROUP BY item_name

limit 10;