CREATE TABLE Designers (
designer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
brand_name TEXT,
contact_info TEXT,
specialization TEXT
);
CREATE TABLE Suppliers (
supplier_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
contact_info TEXT,
material_type TEXT
);
CREATE TABLE Organizers (
organizer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
contact_info TEXT,
experience TEXT
);
CREATE TABLE Agencies (
agency_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
contact_info TEXT
);
CREATE TABLE Retail_Stores (
store_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
location TEXT,
contact_info TEXT,
type TEXT
);
CREATE TABLE Materials (
material_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
type TEXT,
supplier_id INTEGER,
FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);
CREATE TABLE Collections (
collection_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
season TEXT,
year INTEGER,
designer_id INTEGER,
FOREIGN KEY (designer_id) REFERENCES Designers(designer_id)
);
CREATE TABLE Garments (
garment_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
type TEXT,
collection_id INTEGER,
material_id INTEGER,
FOREIGN KEY (collection_id) REFERENCES Collections(collection_id),
FOREIGN KEY (material_id) REFERENCES Materials(material_id)
);
CREATE TABLE Fashion_Shows (
show_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
date TEXT,
location TEXT,
organizer_id INTEGER,
FOREIGN KEY (organizer_id) REFERENCES Organizers(organizer_id)
);
CREATE TABLE Models (
model_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
agency_id INTEGER,
measurements TEXT,
FOREIGN KEY (agency_id) REFERENCES Agencies(agency_id)
);
CREATE TABLE Show_Garments (
show_id INTEGER,
garment_id INTEGER,
model_id INTEGER,
PRIMARY KEY (show_id, garment_id, model_id),
FOREIGN KEY (show_id) REFERENCES Fashion_Shows(show_id),
FOREIGN KEY (garment_id) REFERENCES Garments(garment_id),
FOREIGN KEY (model_id) REFERENCES Models(model_id)
);
CREATE TABLE Store_Garments (
store_id INTEGER,
garment_id INTEGER,
price REAL,
stock INTEGER,
PRIMARY KEY (store_id, garment_id),
FOREIGN KEY (store_id) REFERENCES Retail_Stores(store_id),
FOREIGN KEY (garment_id) REFERENCES Garments(garment_id)
);
CREATE TABLE Promotions (
promotion_id INTEGER PRIMARY KEY,
description TEXT NOT NULL,
start_date TEXT,
end_date TEXT,
store_id INTEGER,
FOREIGN KEY (store_id) REFERENCES Retail_Stores(store_id)
);
CREATE TABLE Garment_Promotions (
garment_id INTEGER,
promotion_id INTEGER,
discount_percentage REAL,
PRIMARY KEY (promotion_id, garment_id),
FOREIGN KEY (promotion_id) REFERENCES Promotions(promotion_id),
FOREIGN KEY (garment_id) REFERENCES Garments(garment_id)
);
CREATE TABLE Fashion_Magazines (
magazine_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
publisher TEXT,
issue_date DATE,
garment_id INTEGER,
FOREIGN KEY (garment_id) REFERENCES Garments(garment_id)
);
CREATE TABLE Magazine_Articles (
article_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
content TEXT,
author TEXT,
magazine_id INTEGER,
FOREIGN KEY (magazine_id) REFERENCES Fashion_Magazines(magazine_id)
);
INSERT INTO Designers (designer_id, name, brand_name, contact_info, specialization) VALUES
(1, 'Alice Smith', 'Alice Couture', 'alice@example.com', 'Evening Wear'),
(2, 'Bob Johnson', 'Bob Fashion', 'bob@example.com', 'Casual Wear'),
(3, 'Charlie Lee', 'Charlie Designs', 'charlie@example.com', 'Sportswear'),
(4, 'Diana King', 'Diana Exclusive', 'diana@example.com', 'Luxury Wear'),
(5, 'Eve Martinez', 'Eve Modern', 'eve@example.com', 'Streetwear'),
(6, 'Frank Wilson', 'Frank Classic', 'frank@example.com', 'Formal Wear'),
(7, 'Grace Brown', 'Grace Originals', 'grace@example.com', 'Childrens Wear'),
(8, 'Hank Davis', 'Hank Trends', 'hank@example.com', 'Avant-Garde'),
(9, 'Ivy Green', 'Ivy Casual', 'ivy@example.com', 'Casual Wear'),
(10, 'Jack White', 'Jack Styles', 'jack@example.com', 'Vintage');
INSERT INTO Suppliers (supplier_id, name, contact_info, material_type) VALUES
(1, 'Textile Corp', 'contact@textilecorp.com', 'Cotton'),
(2, 'Fabrics Ltd', 'info@fabricsltd.com', 'Silk'),
(3, 'Materials Inc', 'sales@materialsinc.com', 'Wool'),
(4, 'Supreme Textiles', 'support@supremetextiles.com', 'Polyester'),
(5, 'Luxury Fabrics', 'info@luxuryfabrics.com', 'Linen'),
(6, 'Eco Materials', 'contact@ecomaterials.com', 'Bamboo'),
(7, 'Synthetic Co', 'support@syntheticco.com', 'Nylon'),
(8, 'Organic Textiles', 'info@organictextiles.com', 'Organic Cotton'),
(9, 'Premium Fabrics', 'sales@premiumfabrics.com', 'Cashmere'),
(10, 'Global Textiles', 'contact@globaltextiles.com', 'Denim');
INSERT INTO Organizers (organizer_id, name, contact_info, experience) VALUES
(1, 'Elite Events', 'contact@eliteevents.com', '10 years'),
(2, 'Fashion Forward', 'info@fashionforward.com', '8 years'),
(3, 'Glamour Shows', 'support@glamourshows.com', '12 years'),
(4, 'Trendsetters', 'sales@trendsetters.com', '5 years'),
(5, 'Runway Pro', 'info@runwaypro.com', '15 years'),
(6, 'Style Showcase', 'contact@styleshowcase.com', '7 years'),
(7, 'Chic Events', 'support@chicevents.com', '9 years'),
(8, 'Fashion Hub', 'info@fashionhub.com', '11 years'),
(9, 'Catwalk Masters', 'sales@catwalkmasters.com', '6 years'),
(10, 'Vogue Events', 'contact@vogueevents.com', '13 years');
INSERT INTO Agencies (agency_id, name, contact_info) VALUES
(1, 'Top Models Agency', 'info@topmodels.com'),
(2, 'Elite Model Management', 'contact@elitemodels.com'),
(3, 'Fashion Faces', 'support@fashionfaces.com'),
(4, 'Model Pro', 'info@modelpro.com'),
(5, 'Glamour Agency', 'contact@glamouragency.com'),
(6, 'Runway Models', 'support@runwaymodels.com'),
(7, 'Chic Models', 'info@chicmodels.com'),
(8, 'Catwalk Talent', 'contact@catwalktalent.com'),
(9, 'Fashion Icons', 'support@fashionicons.com'),
(10, 'Vogue Talent', 'info@vogutalent.com'),
(11, 'Next Model Management', 'info@nextmodels.com'),
(12, 'IMG Models', 'contact@imgmodels.com'),
(13, 'Ford Models', 'support@fordmodels.com'),
(14, 'Wilhelmina Models', 'info@wilhelmina.com'),
(15, 'Storm Model Management', 'contact@stormmodels.com'),
(16, 'Select Model Management', 'support@selectmodels.com'),
(17, 'Premier Model Management', 'info@premiermodels.com'),
(18, 'The Society Management', 'contact@thesocietymgmt.com'),
(19, 'Marilyn Agency', 'support@marilynagency.com'),
(20, 'Supreme Management', 'info@suprememanagement.com');
INSERT INTO Retail_Stores (store_id, name, location, contact_info, type) VALUES
(1, 'Fashion Boutique', '123 Fashion St, NY', 'contact@fashionboutique.com', 'Boutique'),
(2, 'Luxury Styles', '456 Luxury Ave, LA', 'info@luxurystyles.com', 'Luxury'),
(3, 'Casual Corner', '789 Casual Rd, SF', 'support@casualcorner.com', 'Casual'),
(4, 'Trendy Wear', '101 Trendy Blvd, MI', 'sales@trendywear.com', 'Trendy'),
(5, 'Chic Fashion', '202 Chic Ln, TX', 'info@chicfashion.com', 'Chic'),
(6, 'Formal Attire', '303 Formal Dr, FL', 'contact@formalfashion.com', 'Formal'),
(7, 'Streetwear Hub', '404 Streetwear Ct, WA', 'support@streetwearhub.com', 'Streetwear'),
(8, 'Vintage Vogue', '505 Vintage Ave, OR', 'info@vintagevogue.com', 'Vintage'),
(9, 'Eco Fashion', '606 Eco Blvd, CO', 'sales@ecofashion.com', 'Eco-Friendly'),
(10, 'Modern Trends', '707 Modern St, NV', 'contact@moderntrends.com', 'Modern');
INSERT INTO Materials (material_id, name, type, supplier_id) VALUES
(1, 'Cotton Fabric', 'Cotton', 1),
(2, 'Silk Fabric', 'Silk', 2),
(3, 'Wool Fabric', 'Wool', 3),
(4, 'Polyester Fabric', 'Polyester', 4),
(5, 'Linen Fabric', 'Linen', 5),
(6, 'Bamboo Fabric', 'Bamboo', 6),
(7, 'Nylon Fabric', 'Nylon', 7),
(8, 'Organic Cotton Fabric', 'Organic Cotton', 8),
(9, 'Cashmere Fabric', 'Cashmere', 9),
(10, 'Denim Fabric', 'Denim', 10);
INSERT INTO Collections (collection_id, name, season, year, designer_id) VALUES
(1, 'Spring Collection', 'Spring', 2024, 1),
(2, 'Summer Collection', 'Summer', 2024, 2),
(3, 'Fall Collection', 'Fall', 2024, 3),
(4, 'Winter Collection', 'Winter', 2024, 4),
(5, 'Resort Collection', 'Resort', 2024, 5),
(6, 'Holiday Collection', 'Holiday', 2024, 6),
(7, 'Pre-Fall Collection', 'Pre-Fall', 2024, 7),
(8, 'Cruise Collection', 'Cruise', 2024, 8),
(9, 'Capsule Collection', 'Capsule', 2024, 9),
(10, 'Limited Edition Collection', 'Limited Edition', 2024, 10),
(11, 'Spring/Summer Collection', 'Spring/Summer', 2024, 1),
(12, 'Autumn/Winter Collection', 'Autumn/Winter', 2024, 2),
(13, 'Resort 2025', 'Resort', 2025, 3),
(14, 'Holiday 2025', 'Holiday', 2025, 4),
(15, 'Spring 2025', 'Spring', 2025, 5),
(16, 'Summer 2025', 'Summer', 2025, 6),
(17, 'Fall 2025', 'Fall', 2025, 7),
(18, 'Winter 2025', 'Winter', 2025, 8),
(19, 'Pre-Spring 2025', 'Pre-Spring', 2025, 9),
(20, 'Pre-Fall 2025', 'Pre-Fall', 2025, 10);
INSERT INTO Garments (garment_id, name, type, collection_id, material_id) VALUES
(1, 'Evening Gown', 'Dress', 1, 1),
(2, 'Casual T-Shirt', 'Top', 2, 2),
(3, 'Sports Jacket', 'Jacket', 3, 3),
(4, 'Winter Coat', 'Coat', 4, 4),
(5, 'Resort Dress', 'Dress', 5, 5),
(6, 'Holiday Sweater', 'Sweater', 6, 6),
(7, 'Pre-Fall Blazer', 'Blazer', 7, 7),
(8, 'Cruise Shorts', 'Shorts', 8, 8),
(9, 'Capsule Jeans', 'Pants', 9, 9),
(10, 'Limited Edition Hat', 'Accessory', 10, 10),
(11, 'Spring Dress', 'Dress', 11, 1),
(12, 'Summer Shorts', 'Shorts', 12, 2),
(13, 'Autumn Coat', 'Coat', 13, 3),
(14, 'Winter Scarf', 'Accessory', 14, 4),
(15, 'Holiday Sweater', 'Sweater', 15, 5),
(16, 'Resort Hat', 'Accessory', 16, 6),
(17, 'Spring Blouse', 'Top', 17, 7),
(18, 'Summer Skirt', 'Bottom', 18, 8),
(19, 'Fall Jacket', 'Jacket', 19, 9),
(20, 'Winter Boots', 'Shoes', 20, 10);
INSERT INTO Fashion_Shows (show_id, name, date, location, organizer_id) VALUES
(1, 'NY Fashion Week', '2024-09-01', 'New York', 1),
(2, 'LA Fashion Week', '2024-10-01', 'Los Angeles', 2),
(3, 'Paris Fashion Week', '2024-03-01', 'Paris', 3),
(4, 'London Fashion Week', '2024-04-01', 'London', 4),
(5, 'Milan Fashion Week', '2024-05-01', 'Milan', 5),
(6, 'Tokyo Fashion Week', '2024-06-01', 'Tokyo', 6),
(7, 'Berlin Fashion Week', '2024-07-01', 'Berlin', 7),
(8, 'Sydney Fashion Week', '2024-08-01', 'Sydney', 8),
(9, 'Dubai Fashion Week', '2024-11-01', 'Dubai', 9),
(10, 'Shanghai Fashion Week', '2024-12-01', 'Shanghai', 10),
(11, 'NY Spring Fashion Week', '2025-09-01', 'New York', 1),
(12, 'LA Summer Fashion Week', '2025-10-01', 'Los Angeles', 2),
(13, 'Paris Autumn Fashion Week', '2025-03-01', 'Paris', 3),
(14, 'London Winter Fashion Week', '2025-04-01', 'London', 4),
(15, 'Milan Spring Fashion Week', '2025-05-01', 'Milan', 5),
(16, 'Tokyo Summer Fashion Week', '2025-06-01', 'Tokyo', 6),
(17, 'Berlin Autumn Fashion Week', '2025-07-01', 'Berlin', 7),
(18, 'Sydney Winter Fashion Week', '2025-08-01', 'Sydney', 8),
(19, 'Dubai Spring Fashion Week', '2025-11-01', 'Dubai', 9),
(20, 'Shanghai Summer Fashion Week', '2025-12-01', 'Shanghai', 10);
INSERT INTO Models (model_id, name, agency_id, measurements) VALUES
(1, 'Anna Taylor', 1, '34-24-34'),
(2, 'Bella Harris', 2, '32-22-34'),
(3, 'Chloe Wilson', 3, '34-26-36'),
(4, 'Daisy Evans', 4, '36-24-36'),
(5, 'Ella Davis', 5, '34-25-35'),
(6, 'Fiona White', 6, '32-23-33'),
(7, 'Grace Johnson', 7, '33-24-34'),
(8, 'Holly Brown', 8, '35-25-35'),
(9, 'Isla Green', 9, '32-24-34'),
(10, 'Jade Lee', 10, '34-26-36'),
(11, 'Kimberly Clark', 11, '34-24-35'),
(12, 'Natalie Adams', 12, '32-22-33'),
(13, 'Sophia Carter', 13, '34-26-36'),
(14, 'Zoe Hill', 14, '36-24-36'),
(15, 'Mia Wright', 15, '34-25-35'),
(16, 'Olivia Scott', 16, '32-23-33'),
(17, 'Ava King', 17, '33-24-34'),
(18, 'Emma Thompson', 18, '35-25-35'),
(19, 'Lily Roberts', 19, '32-24-34'),
(20, 'Emily Lewis', 20, '34-26-36');
INSERT INTO Show_Garments (show_id, garment_id, model_id) VALUES
(1, 11, 11),
(2, 12, 12),
(3, 13, 13),
(4, 14, 14),
(5, 15, 15),
(6, 16, 16),
(7, 17, 17),
(8, 18, 18),
(9, 19, 19),
(10, 20, 20),
(11, 1, 2),
(12, 2, 3),
(13, 3, 4),
(14, 4, 5),
(15, 5, 6),
(16, 6, 7),
(17, 7, 8),
(18, 8, 9),
(19, 9, 10),
(20, 10, 1);
INSERT INTO Store_Garments (store_id, garment_id, price, stock) VALUES
(1, 11, 160.00, 15),
(2, 12, 55.00, 25),
(3, 13, 210.00, 7),
(4, 14, 310.00, 4),
(5, 15, 130.00, 20),
(6, 16, 90.00, 30),
(7, 17, 190.00, 10),
(8, 18, 70.00, 35),
(9, 19, 110.00, 15),
(10, 20, 80.00, 25),
(1, 1, 150.00, 10),
(2, 2, 50.00, 20),
(3, 3, 200.00, 5),
(4, 4, 300.00, 3),
(5, 5, 120.00, 15),
(6, 6, 80.00, 25),
(7, 7, 180.00, 8),
(8, 8, 60.00, 30),
(9, 9, 100.00, 12),
(10, 10, 70.00, 22);
INSERT INTO Promotions (promotion_id, description, start_date, end_date, store_id) VALUES
(1, 'Summer Sale', '2024-06-01', '2024-06-30', 1),
(2, 'Winter Clearance', '2024-12-01', '2024-12-31', 2),
(3, 'Spring Discount', '2024-03-01', '2024-03-31', 3),
(4, 'Fall Savings', '2024-09-01', '2024-09-30', 4),
(5, 'Holiday Special', '2024-11-25', '2024-12-25', 5),
(6, 'Black Friday Deal', '2024-11-29', '2024-11-29', 6),
(7, 'Cyber Monday Deal', '2024-12-02', '2024-12-02', 7),
(8, 'End of Season Sale', '2024-08-01', '2024-08-31', 8),
(9, 'New Year Sale', '2024-01-01', '2024-01-31', 9),
(10, 'Back to School', '2024-08-15', '2024-09-15', 10);
INSERT INTO Garment_Promotions (garment_id, promotion_id, discount_percentage) VALUES
(1, 2, 12.0),
(2, 3, 18.0),
(3, 4, 22.0),
(4, 5, 28.0),
(5, 6, 32.0),
(6, 7, 38.0),
(7, 8, 42.0),
(8, 9, 48.0),
(9, 10, 52.0),
(10, 1, 58.0),
(11, 2, 14.0),
(12, 3, 19.0),
(13, 4, 23.0),
(14, 5, 29.0),
(15, 6, 34.0),
(16, 7, 39.0),
(17, 8, 43.0),
(18, 9, 49.0),
(19, 10, 53.0),
(20, 1, 59.0);
INSERT INTO Fashion_Magazines (magazine_id, name, publisher, issue_date, garment_id) VALUES
(1, 'Vogue', 'Conde Nast', '2024-06-01', 1),
(2, 'Harpers Bazaar', 'Hearst', '2024-07-01', 2),
(3, 'Elle', 'Lagardère Group', '2024-08-01', 3),
(4, 'GQ', 'Conde Nast', '2024-09-01', 4),
(5, 'InStyle', 'Meredith Corporation', '2024-10-01', 5),
(6, 'Marie Claire', 'Hearst', '2024-11-01', 6),
(7, 'Cosmopolitan', 'Hearst', '2024-12-01', 7),
(8, 'Esquire', 'Hearst', '2024-01-01', 8),
(9, 'W', 'Future US', '2024-02-01', 9),
(10, 'Vanity Fair', 'Conde Nast', '2024-03-01', 10);
INSERT INTO Magazine_Articles (article_id, title, content, author, magazine_id) VALUES
(1, 'Spring Fashion Trends', 'This season is all about vibrant colors...', 'Jane Doe', 1),
(2, 'Summer Essentials', 'Must-have items for your summer wardrobe...', 'John Smith', 2),
(3, 'Fall Fashion Preview', 'Get ready for the upcoming fall with...', 'Emily Johnson', 3),
(4, 'Winter Warmers', 'Stay stylish and warm this winter with...', 'Michael Brown', 4),
(5, 'Resort Wear', 'Perfect outfits for your next resort vacation...', 'Sophia Davis', 5),
(6, 'Holiday Glam', 'Shine bright this holiday season with...', 'Olivia Wilson', 6),
(7, 'Pre-Fall Picks', 'Our top picks for the pre-fall season...', 'Lucas Martinez', 7),
(8, 'Cruise Collection Highlights', 'Discover the latest cruise collection...', 'Isabella Anderson', 8),
(9, 'Capsule Wardrobe', 'Essential items for a minimalist wardrobe...', 'Liam White', 9),
(10, 'Limited Edition Must-Haves', 'Exclusive pieces to add to your collection...', 'Charlotte Green', 10),
(11, 'Spring Fashion Guide', 'Discover the must-have items for this spring...', 'Jane Doe', 1),
(12, 'Summer Wardrobe Essentials', 'Get ready for summer with these stylish pieces...', 'John Smith', 2),
(13, 'Autumn Trends', 'Stay ahead of the curve with these autumn trends...', 'Emily Johnson', 3),
(14, 'Winter Fashion Tips', 'Keep warm and fashionable this winter with...', 'Michael Brown', 4),
(15, 'Resort Wear Ideas', 'Perfect outfits for your next tropical vacation...', 'Sophia Davis', 5),
(16, 'Holiday Fashion', 'Look glamorous this holiday season with...', 'Olivia Wilson', 6),
(17, 'Pre-Fall Must-Haves', 'Our top picks for the pre-fall season...', 'Lucas Martinez', 7),
(18, 'Cruise Collection Highlights', 'Explore the latest cruise collections...', 'Isabella Anderson', 8),
(19, 'Capsule Wardrobe Basics', 'Essential items for a minimalist wardrobe...', 'Liam White', 9),
(20, 'Limited Edition Picks', 'Exclusive pieces to add to your collection...', 'Charlotte Green', 10);
-- stats
-- WITH designer_garments AS (
-- SELECT
-- d.designer_id,
-- d.name AS designer_name,
-- d.specialization,
-- COUNT(g.garment_id) AS total_garments
-- FROM
-- Designers d
-- LEFT JOIN Collections c ON d.designer_id = c.designer_id
-- LEFT JOIN Garments g ON c.collection_id = g.collection_id
-- GROUP BY
-- d.designer_id,
-- d.name,
-- d.specialization
-- ),
-- designer_prices AS (
-- SELECT
-- d.designer_id,
-- AVG(sg.price) AS average_price
-- FROM
-- Designers d
-- LEFT JOIN Collections c ON d.designer_id = c.designer_id
-- LEFT JOIN Garments g ON c.collection_id = g.collection_id
-- LEFT JOIN Store_Garments sg ON g.garment_id = sg.garment_id
-- GROUP BY
-- d.designer_id
-- )
-- SELECT
-- dg.designer_name,
-- dg.specialization,
-- dg.total_garments,
-- dp.average_price
-- FROM
-- designer_garments dg
-- LEFT JOIN designer_prices dp ON dg.designer_id = dp.designer_id
-- ORDER BY
-- dp.average_price DESC;
-- WITH model_show_counts AS (
-- SELECT
-- m.model_id,
-- m.name AS model_name,
-- a.name AS agency_name,
-- COUNT(DISTINCT sg.show_id) AS total_shows
-- FROM
-- Models m
-- JOIN Agencies a ON m.agency_id = a.agency_id
-- JOIN Show_Garments sg ON m.model_id = sg.model_id
-- GROUP BY
-- m.model_id,
-- m.name,
-- a.name
-- HAVING
-- COUNT(DISTINCT sg.show_id) >= 1
-- )
-- SELECT
-- model_name,
-- agency_name,
-- total_shows
-- FROM
-- model_show_counts
-- ORDER BY
-- total_shows DESC
-- LIMIT 5;
-- WITH garment_discounts AS (
-- SELECT
-- g.garment_id,
-- g.name AS garment_name,
-- SUM(gp.discount_percentage) AS total_discount,
-- COUNT(gp.promotion_id) AS promotion_count
-- FROM
-- Garments g
-- JOIN Garment_Promotions gp ON g.garment_id = gp.garment_id
-- GROUP BY
-- g.garment_id,
-- g.name
-- )
-- SELECT
-- garment_name,
-- total_discount,
-- promotion_count
-- FROM
-- garment_discounts
-- ORDER BY
-- total_discount DESC
-- LIMIT 5;
-- WITH garment_sales AS (
-- SELECT
-- sg.garment_id,
-- SUM(sg.price * sg.stock) AS total_sales_value
-- FROM
-- Store_Garments sg
-- GROUP BY
-- sg.garment_id
-- ),
-- top_garments AS (
-- SELECT
-- garment_id,
-- total_sales_value
-- FROM
-- garment_sales
-- ORDER BY
-- total_sales_value DESC
-- LIMIT 10
-- ),
-- supplier_garments AS (
-- SELECT
-- s.supplier_id,
-- s.name AS supplier_name,
-- s.material_type,
-- COUNT(DISTINCT tg.garment_id) AS garment_count,
-- SUM(tg.total_sales_value) AS supplier_sales_value
-- FROM
-- Suppliers s
-- JOIN Materials m ON s.supplier_id = m.supplier_id
-- JOIN Garments g ON m.material_id = g.material_id
-- JOIN top_garments tg ON g.garment_id = tg.garment_id
-- GROUP BY
-- s.supplier_id,
-- s.name,
-- s.material_type
-- )
-- SELECT
-- supplier_name,
-- material_type,
-- garment_count,
-- supplier_sales_value
-- FROM
-- supplier_garments
-- ORDER BY
-- supplier_sales_value DESC;
-- most featured designers
-- WITH designer_articles AS (
-- SELECT
-- d.designer_id,
-- d.name AS designer_name,
-- d.brand_name,
-- COUNT(ma.article_id) AS total_articles
-- FROM
-- Designers d
-- JOIN Collections c ON d.designer_id = c.designer_id
-- JOIN Garments g ON c.collection_id = g.collection_id
-- JOIN Fashion_Magazines fm ON g.garment_id = fm.garment_id
-- JOIN Magazine_Articles ma ON fm.magazine_id = ma.magazine_id
-- GROUP BY
-- d.designer_id,
-- d.name,
-- d.brand_name
-- )
-- SELECT
-- designer_name,
-- brand_name,
-- total_articles
-- FROM
-- designer_articles
-- ORDER BY
-- total_articles DESC
-- LIMIT 3;
-- eco show
-- WITH eco_material_garments AS (
-- SELECT
-- g.garment_id
-- FROM
-- Garments g
-- JOIN Materials m ON g.material_id = m.material_id
-- JOIN Suppliers s ON m.supplier_id = s.supplier_id
-- WHERE
-- s.name = 'Eco Materials'
-- ),
-- show_garment_counts AS (
-- SELECT
-- sg.show_id,
-- COUNT(DISTINCT sg.garment_id) AS total_garments
-- FROM
-- Show_Garments sg
-- GROUP BY
-- sg.show_id
-- ),
-- eco_show_garment_counts AS (
-- SELECT
-- sg.show_id,
-- COUNT(DISTINCT sg.garment_id) AS eco_garments
-- FROM
-- Show_Garments sg
-- JOIN eco_material_garments emg ON sg.garment_id = emg.garment_id
-- GROUP BY
-- sg.show_id
-- )
-- SELECT
-- fs.name AS show_name,
-- fs.date,
-- fs.location
-- FROM
-- show_garment_counts sgc
-- JOIN eco_show_garment_counts esgc ON sgc.show_id = esgc.show_id
-- JOIN Fashion_Shows fs ON sgc.show_id = fs.show_id
-- WHERE
-- sgc.total_garments = esgc.eco_garments;
-- WITH designer_collections AS (
-- SELECT
-- d.designer_id,
-- d.name AS designer_name,
-- COUNT(DISTINCT c.collection_id) AS total_collections
-- FROM
-- Designers d
-- JOIN Collections c ON d.designer_id = c.designer_id
-- GROUP BY
-- d.designer_id,
-- d.name
-- ),
-- designer_garments AS (
-- SELECT
-- d.designer_id,
-- COUNT(g.garment_id) AS total_garments
-- FROM
-- Designers d
-- JOIN Collections c ON d.designer_id = c.designer_id
-- JOIN Garments g ON c.collection_id = g.collection_id
-- GROUP BY
-- d.designer_id
-- )
-- SELECT
-- dc.designer_name,
-- dc.total_collections,
-- dg.total_garments,
-- ROUND(dg.total_garments * 1.0 / dc.total_collections, 2) AS avg_garments_per_collection
-- FROM
-- designer_collections dc
-- JOIN designer_garments dg ON dc.designer_id = dg.designer_id
-- ORDER BY
-- avg_garments_per_collection DESC;
-- WITH garment_discounts AS (
-- SELECT
-- g.garment_id,
-- SUM(COALESCE(gp.discount_percentage, 0)) AS total_discount_percentage
-- FROM
-- Garments g
-- LEFT JOIN Garment_Promotions gp ON g.garment_id = gp.garment_id
-- GROUP BY
-- g.garment_id
-- ),
-- garment_revenue AS (
-- SELECT
-- g.garment_id,
-- (sg.price * sg.stock) * (1 - (gd.total_discount_percentage / 100)) AS total_revenue
-- FROM
-- Garments g
-- JOIN Store_Garments sg ON g.garment_id = sg.garment_id
-- JOIN garment_discounts gd ON g.garment_id = gd.garment_id
-- ),
-- designer_revenue AS (
-- SELECT
-- d.designer_id,
-- d.name AS designer_name,
-- d.brand_name,
-- SUM(gr.total_revenue) AS total_designer_revenue,
-- COUNT(DISTINCT g.garment_id) AS number_of_garments
-- FROM
-- Designers d
-- JOIN Collections c ON d.designer_id = c.designer_id
-- JOIN Garments g ON c.collection_id = g.collection_id
-- JOIN garment_revenue gr ON g.garment_id = gr.garment_id
-- GROUP BY
-- d.designer_id,
-- d.name,
-- d.brand_name
-- )
-- SELECT
-- designer_name,
-- brand_name,
-- total_designer_revenue,
-- number_of_garments
-- FROM
-- designer_revenue
-- ORDER BY
-- total_designer_revenue DESC
-- LIMIT 5;
-- WITH model_garments AS (
-- SELECT
-- m.model_id,
-- m.name AS model_name,
-- a.name AS agency_name,
-- sg.garment_id
-- FROM
-- Models m
-- JOIN Agencies a ON m.agency_id = a.agency_id
-- JOIN Show_Garments sg ON m.model_id = sg.model_id
-- ),
-- garment_articles AS (
-- SELECT
-- mg.model_id,
-- mg.model_name,
-- mg.agency_name,
-- mg.garment_id,
-- COUNT(DISTINCT ma.article_id) AS article_count
-- FROM
-- model_garments mg
-- JOIN Fashion_Magazines fm ON mg.garment_id = fm.garment_id
-- JOIN Magazine_Articles ma ON fm.magazine_id = ma.magazine_id
-- GROUP BY
-- mg.model_id,
-- mg.model_name,
-- mg.agency_name,
-- mg.garment_id
-- ),
-- model_exposure AS (
-- SELECT
-- ga.model_id,
-- ga.model_name,
-- ga.agency_name,
-- SUM(ga.article_count) AS total_exposure_score,
-- COUNT(DISTINCT ga.garment_id) AS unique_garments
-- FROM
-- garment_articles ga
-- GROUP BY
-- ga.model_id,
-- ga.model_name,
-- ga.agency_name
-- )
-- SELECT
-- model_name,
-- agency_name,
-- total_exposure_score,
-- unique_garments
-- FROM
-- model_exposure
-- ORDER BY
-- total_exposure_score DESC
-- LIMIT 5;
-- WITH collection_prices AS (
-- SELECT
-- c.collection_id,
-- c.name AS collection_name,
-- d.name AS designer_name,
-- g.type AS garment_type,
-- AVG(sg.price) AS average_price
-- FROM
-- Collections c
-- JOIN Designers d ON c.designer_id = d.designer_id
-- JOIN Garments g ON c.collection_id = g.collection_id
-- JOIN Store_Garments sg ON g.garment_id = sg.garment_id
-- GROUP BY
-- c.collection_id,
-- c.name,
-- d.name,
-- g.type
-- ),
-- collection_average AS (
-- SELECT
-- cp.collection_id,
-- cp.collection_name,
-- cp.designer_name,
-- AVG(cp.average_price) AS collection_avg_price
-- FROM
-- collection_prices cp
-- GROUP BY
-- cp.collection_id,
-- cp.collection_name,
-- cp.designer_name
-- ),
-- max_collection AS (
-- SELECT
-- ca.collection_id,
-- ca.collection_name,
-- ca.designer_name
-- FROM
-- collection_average ca
-- WHERE
-- ca.collection_avg_price = (SELECT MAX(collection_avg_price) FROM collection_average)
-- )
-- SELECT
-- cp.collection_name,
-- cp.designer_name,
-- cp.garment_type,
-- cp.average_price
-- FROM
-- collection_prices cp
-- JOIN max_collection mc ON cp.collection_id = mc.collection_id
-- ORDER BY
-- cp.average_price DESC;
-- WITH agency_designers AS (
-- SELECT
-- a.agency_id,
-- a.name AS agency_name,
-- d.designer_id,
-- d.name AS designer_name
-- FROM
-- Agencies a
-- JOIN Models m ON a.agency_id = m.agency_id
-- JOIN Show_Garments sg ON m.model_id = sg.model_id
-- JOIN Garments g ON sg.garment_id = g.garment_id
-- JOIN Collections c ON g.collection_id = c.collection_id
-- JOIN Designers d ON c.designer_id = d.designer_id
-- GROUP BY
-- a.agency_id,
-- a.name,
-- d.designer_id,
-- d.name
-- ),
-- agency_designer_counts AS (
-- SELECT
-- ad.agency_id,
-- ad.agency_name,
-- COUNT(DISTINCT ad.designer_id) AS total_designers,
-- GROUP_CONCAT(DISTINCT ad.designer_name) AS designer_names
-- FROM
-- agency_designers ad
-- GROUP BY
-- ad.agency_id,
-- ad.agency_name
-- HAVING
-- COUNT(DISTINCT ad.designer_id) >= 1
-- )
-- SELECT
-- agency_name,
-- total_designers,
-- designer_names
-- FROM
-- agency_designer_counts
-- ORDER BY
-- total_designers DESC
-- LIMIT 4;
-- top garments by season
-- WITH season_garments AS (
-- SELECT
-- c.season,
-- c.year,
-- g.type AS garment_type,
-- SUM(sg.stock) AS total_stock
-- FROM
-- Collections c
-- JOIN Garments g ON c.collection_id = g.collection_id
-- JOIN Store_Garments sg ON g.garment_id = sg.garment_id
-- GROUP BY
-- c.season,
-- c.year,
-- g.type
-- HAVING
-- SUM(sg.stock) >= 10
-- ),
-- season_rankings AS (
-- SELECT
-- sg.season,
-- sg.year,
-- sg.garment_type,
-- sg.total_stock,
-- RANK() OVER (
-- PARTITION BY sg.season, sg.year
-- ORDER BY sg.total_stock DESC
-- ) AS rank
-- FROM
-- season_garments sg
-- )
-- SELECT
-- season,
-- year,
-- garment_type,
-- total_stock,
-- rank
-- FROM
-- season_rankings
-- WHERE
-- rank <= 2
-- ORDER BY
-- season,
-- year,
-- rank;
-- WITH designer_metrics AS (
-- SELECT
-- d.designer_id,
-- d.name AS designer_name,
-- d.brand_name,
-- COUNT(DISTINCT sg.garment_id) AS total_garments_sold,
-- COUNT(DISTINCT fs.show_id) AS total_shows,
-- COUNT(DISTINCT ma.article_id) AS total_articles
-- FROM
-- Designers d
-- LEFT JOIN Collections c ON d.designer_id = c.designer_id
-- LEFT JOIN Garments g ON c.collection_id = g.collection_id
-- LEFT JOIN Store_Garments sg ON g.garment_id = sg.garment_id
-- LEFT JOIN Show_Garments sgg ON g.garment_id = sgg.garment_id
-- LEFT JOIN Fashion_Shows fs ON sgg.show_id = fs.show_id
-- LEFT JOIN Fashion_Magazines fm ON g.garment_id = fm.garment_id
-- LEFT JOIN Magazine_Articles ma ON fm.magazine_id = ma.magazine_id
-- GROUP BY
-- d.designer_id,
-- d.name,
-- d.brand_name
-- ),
-- max_values AS (
-- SELECT
-- MAX(total_garments_sold) AS max_garments_sold,
-- MAX(total_shows) AS max_shows,
-- MAX(total_articles) AS max_articles
-- FROM
-- designer_metrics
-- ),
-- designer_popularity AS (
-- SELECT
-- dm.designer_name,
-- dm.brand_name,
-- (
-- (dm.total_garments_sold * 1.0 / mv.max_garments_sold) * 0.5 +
-- (dm.total_shows * 1.0 / mv.max_shows) * 0.3 +
-- (dm.total_articles * 1.0 / mv.max_articles) * 0.2
-- ) AS popularity_score
-- FROM
-- designer_metrics dm
-- CROSS JOIN max_values mv
-- )
-- SELECT
-- designer_name,
-- brand_name,
-- ROUND(popularity_score, 4) AS popularity_score
-- FROM
-- designer_popularity
-- ORDER BY
-- popularity_score DESC;
WITH show_diversity AS (
SELECT
fs.show_id,
fs.name AS show_name,
fs.date,
fs.location,
COUNT(DISTINCT g.type) AS garment_type_count,
COUNT(DISTINCT a.agency_id) AS agency_count,
COUNT(DISTINCT m.model_id) AS total_models,
(COUNT(DISTINCT g.type) + COUNT(DISTINCT a.agency_id)) AS diversity_score
FROM
Fashion_Shows fs
JOIN Show_Garments sg ON fs.show_id = sg.show_id
JOIN Garments g ON sg.garment_id = g.garment_id
JOIN Models m ON sg.model_id = m.model_id
JOIN Agencies a ON m.agency_id = a.agency_id
GROUP BY
fs.show_id,
fs.name,
fs.date,
fs.location
)
SELECT
show_name,
date,
location,
garment_type_count,
agency_count,
total_models,
diversity_score
FROM
show_diversity
ORDER BY
diversity_score DESC
LIMIT 5;
To embed this program on your website, copy the following code and paste it into your website's HTML: