--
-- Host: localhost    Database: zagi_sales
-- ------------------------------------------------------


--
-- Table structure for table `category`
--

CREATE TABLE `category` (
  `category_id` char(2) NOT NULL,
  `category_name` varchar(25) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `category`
--

INSERT INTO `category` VALUES ('CP','Camping'),('FW','Footwear');

--
-- Table structure for table `customer`
--

CREATE TABLE `customer` (
  `customer_id` char(7) NOT NULL,
  `customer_name` varchar(15) NOT NULL,
  `customer_zip` char(5) NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` VALUES ('1-2-333','Tina','60137'),('2-3-444','Tony','60611'),('3-4-555','Pam ','35401');

--
-- Temporary view structure for view `highest_sales`
--

/*!50001 DROP VIEW IF EXISTS `highest_sales`*/;
/*!50001 CREATE VIEW `highest_sales` AS SELECT 
 1 AS `highest_sales`*/;

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `product_id` char(3) NOT NULL,
  `product_name` varchar(25) NOT NULL,
  `product_price` decimal(7,2) NOT NULL,
  `vendor_id` char(2) NOT NULL,
  `category_id` char(2) NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `vendor` (`vendor_id`),
  CONSTRAINT `product_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` VALUES ('1X1','Zzz Bag',100.00,'PG','CP'),('2X2','Easy Boot',70.00,'MK','FW'),('3X3','Cosy Sock',15.00,'MK','FW'),('4X4','Dura Boot',90.00,'PG','FW'),('5X5','Tiny Tent',150.00,'MK','CP'),('6X6','Biggy Tent',250.00,'MK','CP');

--
-- Temporary view structure for view `products_in_multiple_trnsc`
--

/*!50001 DROP VIEW IF EXISTS `products_in_multiple_trnsc`*/;
/*!50001 CREATE VIEW `products_in_multiple_trnsc` AS SELECT 
 1 AS `product_id`,
 1 AS `product_name`,
 1 AS `product_price`*/;

--
-- Temporary view structure for view `products_more_than_3_sold`
--

/*!50001 DROP VIEW IF EXISTS `products_more_than_3_sold`*/;
/*!50001 CREATE VIEW `products_more_than_3_sold` AS SELECT 
 1 AS `product_id`,
 1 AS `product_name`,
 1 AS `product_price`*/;

--
-- Table structure for table `region`
--

CREATE TABLE `region` (
  `region_id` char(1) NOT NULL,
  `region_name` varchar(25) NOT NULL,
  PRIMARY KEY (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `region`
--

INSERT INTO `region` VALUES ('C','Chicagoland'),('T','Tristate');

--
-- Temporary view structure for view `sales_per_product`
--

/*!50001 DROP VIEW IF EXISTS `sales_per_product`*/;
/*!50001 CREATE VIEW `sales_per_product` AS SELECT 
 1 AS `product_id`,
 1 AS `total_sales`*/;

--
-- Table structure for table `sales_transaction`
--

CREATE TABLE `sales_transaction` (
  `tid` text,
  `customer_id` text,
  `store_id` text,
  `tdate` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `sales_transaction`
--

INSERT INTO `sales_transaction` VALUES ('T111','1-2-333','S1','2023-01-23'),('T222','2-3-444','S2','2023-02-06'),('T333','1-2-333','S2','2023-03-14'),('T444','3-4-555','S1','2023-04-17'),('T555','1-2-333','S2','2023-05-01'),('T666','2-3-444','S2','2023-06-07'),('T777','3-4-555','S3','2023-06-19');

--
-- Table structure for table `sold_via`
--

CREATE TABLE `sold_via` (
  `product_id` char(3) NOT NULL,
  `tid` varchar(8) NOT NULL,
  `no_of_items` int NOT NULL,
  PRIMARY KEY (`product_id`,`tid`),
  CONSTRAINT `sold_via_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `sold_via`
--

INSERT INTO `sold_via` VALUES ('1X1','T111',1),('1X1','T333',1),('1X1','T555',4),('1X1','T777',2),('2X2','T222',1),('2X2','T444',2),('3X3','T333',5),('4X4','T444',1),('5X5','T555',2),('6X6','T555',1),('6X6','T666',3);

--
-- Table structure for table `store`
--

CREATE TABLE `store` (
  `store_id` varchar(3) NOT NULL,
  `store_zip` char(5) NOT NULL,
  `region_id` char(1) NOT NULL,
  PRIMARY KEY (`store_id`),
  KEY `region_id` (`region_id`),
  CONSTRAINT `store_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `region` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `store`
--

INSERT INTO `store` VALUES ('S1','60600','C'),('S2','60605','C'),('S3','35400','T');

--
-- Table structure for table `vendor`
--

CREATE TABLE `vendor` (
  `vendor_id` char(2) NOT NULL,
  `vendor_name` varchar(25) NOT NULL,
  PRIMARY KEY (`vendor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `vendor`
--

INSERT INTO `vendor` VALUES ('MK','Mountain King'),('PG','Pacifica Gear');

--
-- Final view structure for view `highest_sales`
--

/*!50001 DROP VIEW IF EXISTS `highest_sales`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `highest_sales` AS select max(`sales_per_product`.`total_sales`) AS `highest_sales` from `sales_per_product` */;

--
-- Final view structure for view `products_in_multiple_trnsc`
--

/*!50001 DROP VIEW IF EXISTS `products_in_multiple_trnsc`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `products_in_multiple_trnsc` AS select `product`.`product_id` AS `product_id`,`product`.`product_name` AS `product_name`,`product`.`product_price` AS `product_price` from `product` where `product`.`product_id` in (select `sold_via`.`product_id` from `sold_via` group by `sold_via`.`product_id` having (count(0) > 1)) */;

--
-- Final view structure for view `products_more_than_3_sold`
--

/*!50001 DROP VIEW IF EXISTS `products_more_than_3_sold`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `products_more_than_3_sold` AS select `product`.`product_id` AS `product_id`,`product`.`product_name` AS `product_name`,`product`.`product_price` AS `product_price` from `product` where `product`.`product_id` in (select `sold_via`.`product_id` from `sold_via` group by `sold_via`.`product_id` having (sum(`sold_via`.`no_of_items`) > 3)) */;

--
-- Final view structure for view `sales_per_product`
--

/*!50001 DROP VIEW IF EXISTS `sales_per_product`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `sales_per_product` AS select `sold_via`.`product_id` AS `product_id`,sum(`sold_via`.`no_of_items`) AS `total_sales` from `sold_via` group by `sold_via`.`product_id` */;


