--
-- Host: localhost    Database: car_dealership
-- ------------------------------------------------------


--
-- Table structure for table `car`
--

CREATE TABLE `car` (
  `vin` varchar(8) NOT NULL,
  `make` varchar(16) NOT NULL,
  `model` varchar(16) NOT NULL,
  `year` int NOT NULL,
  `price` double NOT NULL,
  `transaction_id` varchar(5) NOT NULL,
  PRIMARY KEY (`vin`),
  KEY `transaction_id` (`transaction_id`),
  CONSTRAINT `car_ibfk_1` FOREIGN KEY (`transaction_id`) REFERENCES `transaction` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `car`
--

INSERT INTO `car` VALUES ('VIN11111','Ford','Taurus',2016,10000,'T0005'),('VIN12345','Chevy','Nova',1975,1000,'T0002'),('VIN22222','Ford','Fiesta',2016,10000,'T0007'),('VIN33333','Volvo','X90',2015,11000,'T0004'),('VIN44444','Volvo','850',1996,5000,'T0001'),('VIN45678','Volvo','240 DL',1983,1000,'T0011'),('VIN55555','Volvo','S60',2016,16000,'T0006'),('VIN66666','Honda','Accord',2014,2200,'T0001'),('VIN77777','Honda','Civic',2005,5000,'T0010'),('VIN88888','Toyota','Prius',2015,15000,'T0002'),('VIN98765','Volkswagen','Beetle',1965,200,'T0010'),('VIN99999','Ford','Focus',2012,700,'T0007');

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

CREATE TABLE `customer` (
  `id` varchar(4) NOT NULL,
  `name` varchar(32) NOT NULL,
  `gender` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `customer` VALUES ('C111','Ron','m'),('C222','Mara','f'),('C333','Tom','m'),('C444','Susan','f'),('C555','Marsha','f'),('C666','Max','m'),('C777','Arnold','m');

--
-- Table structure for table `salesperson`
--

CREATE TABLE `salesperson` (
  `id` varchar(4) NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `salesperson`
--

INSERT INTO `salesperson` VALUES ('SP11','Sarah'),('SP22','Leslie'),('SP33','Steve'),('SP44','Judy'),('SP55','Bruno');

--
-- Temporary view structure for view `salesperson_sales_sept`
--

/*!50001 DROP VIEW IF EXISTS `salesperson_sales_sept`*/;
/*!50001 CREATE VIEW `salesperson_sales_sept` AS SELECT 
 1 AS `pname`,
 1 AS `cars_sold_in_sept`*/;

--
-- Table structure for table `transaction`
--

CREATE TABLE `transaction` (
  `id` varchar(5) NOT NULL,
  `month` int NOT NULL,
  `customer_id` varchar(4) NOT NULL,
  `salesperson_id` varchar(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `salesperson_id` (`salesperson_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`salesperson_id`) REFERENCES `salesperson` (`id`),
  CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `transaction`
--

INSERT INTO `transaction` VALUES ('T0001',9,'C444','SP22'),('T0002',9,'C333','SP11'),('T0003',9,'C444','SP55'),('T0004',9,'C222','SP22'),('T0005',9,'C666','SP33'),('T0006',9,'C111','SP55'),('T0007',9,'C333','SP44'),('T0008',9,'C222','SP44'),('T0009',9,'C555','SP22'),('T0010',9,'C777','SP11'),('T0011',9,'C222','SP22'),('T0012',9,'C777','SP11'),('T0013',9,'C777','SP11');

--
-- Final view structure for view `salesperson_sales_sept`
--

/*!50001 DROP VIEW IF EXISTS `salesperson_sales_sept`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `salesperson_sales_sept` AS select `sp`.`name` AS `pname`,count(`sp`.`name`) AS `cars_sold_in_sept` from (`salesperson` `sp` join `transaction` `t`) where ((`t`.`salesperson_id` = `sp`.`id`) and (`t`.`month` = 9)) group by `pname` */;


