CREATE VIEW customer_order_summary AS
SELECT 
    c.c_custkey,
    c.c_name,
    c.c_mktsegment,
    COUNT(o.o_orderkey) AS order_count,
    COALESCE(SUM(o.o_totalprice), 0) AS total_spent
FROM customer c
LEFT JOIN orders o ON c.c_custkey = o.o_custkey
GROUP BY c.c_custkey, c.c_name, c.c_mktsegment;

CREATE VIEW regional_suppliers AS
SELECT 
    r.r_name AS region_name,
    n.n_name AS nation_name,
    s.s_suppkey,
    s.s_name AS supplier_name,
    s.s_phone
FROM region r
INNER JOIN nation n ON r.r_regionkey = n.n_regionkey
INNER JOIN supplier s ON n.n_nationkey = s.s_nationkey;

SELECT c_name, c_mktsegment, order_count, total_spent
FROM customer_order_summary
ORDER BY order_count DESC
LIMIT 10;

SELECT c_name, order_count, total_spent
FROM customer_order_summary
WHERE c_mktsegment = 'BUILDING'
  AND order_count > 10
ORDER BY total_spent DESC;

SELECT 
    rs.region_name,
    rs.supplier_name,
    ps.ps_partkey,
    ps.ps_supplycost
FROM regional_suppliers rs
INNER JOIN partsupp ps ON rs.s_suppkey = ps.ps_suppkey
WHERE rs.region_name = 'ASIA'
ORDER BY ps.ps_supplycost
LIMIT 20;

CREATE OR REPLACE VIEW customer_order_summary AS
SELECT 
    c.c_custkey,
    c.c_name,
    c.c_mktsegment,
    COUNT(o.o_orderkey) AS order_count,
    COALESCE(SUM(o.o_totalprice), 0) AS total_spent,
    COALESCE(AVG(o.o_totalprice), 0) AS avg_order_amount
FROM customer c
LEFT JOIN orders o ON c.c_custkey = o.o_custkey
GROUP BY c.c_custkey, c.c_name, c.c_mktsegment;

DROP VIEW IF EXISTS customer_order_summary;
CREATE VIEW customer_order_summary AS
SELECT ...;

SHOW CREATE VIEW customer_order_summary\\G

SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    CHECK_OPTION,
    IS_UPDATABLE
FROM information_schema.views 
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'customer_order_summary'\\G

DROP VIEW IF EXISTS customer_order_summary;

DROP VIEW IF EXISTS customer_order_summary, regional_suppliers;