mhzZgvyM

1CREATE DATABASE CarSales;
2-- createing the Database 
3-- Data base named CarSales
4
5-- Car table
6
7create TABLE if NOT Exists `Car`(
8	`CarID` int (10) unsigned not null AUTO_INCREMENT,
9    `make`  varchar (50),
10    `colour` varchar (25),
11    `Year` int (4),
12    `Condition` varchar (10),
13    PRIMARY KEY (`CarID`) 
14    ) ENGINE=InnoDB CHARSET=utf8; 
15    
16create TABLE if Not exists `Sale`(
17	`SalesID` int (10) unsigned not null AUTO_INCREMENT,
18	`SalesPersonID` int (10), 
19    `retail price` int (6),
20    `sale price` int (6),
21    `PaymentID` int (10),
22    `CarID` int (10),
23    `BuyerID` int (10),
24    PRIMARY KEY (`SalesID`),
25    FOREIGN KEY (`SalesPersonID`) REFERENCES SalesPerson.SalesPersonID,
26    FOREIGN KEY (`PaymentID`) REFERENCES Payment.PaymentID,
27    FOREIGN KEY (`CarID`) REFERENCES Car.CarID,
28    FOREIGN KEY (`BuyerID`) REFERENCES Buyer.BuyerID
29    )ENGINE=InnoDB CHARSET=utf8;
30    
31CREATE TABLE if NOT Exists `SalesPerson`(
32	`SalesPersonID` int (10) unsigned not null AUTO_INCREMENT,
33    `Name` varchar (20),
34    `date of birth` int (8),
35    PRIMARY KEY (`SalesPersonID`)
36    )ENGINE=InnoDB CHARSET=utf8;
37    
38    -- Table for Buyer below
39    
40CREATE TABLE if NOT Exists `Buyer`(
41	`BuyerID` int (10) unsigned not null AUTO_INCREMENT,
42    `Billing address 1` Varchar (20),
43    `Billing address 2` Varchar (20),
44    `Billing address 3` Varchar (20),
45    `Mailing address 1` Varchar (20),
46    `Mailing address 2` Varchar (20),
47    `Mailing address 3` Varchar (20),
48    `postcode` Varchar (6), 
49    PRIMARY KEY (`BuyerID`)
50    )ENGINE=InnoDB CHARSET=utf8;
51    
52CREATE TABLE IF NOT Exists `Payment`(
53    `PaymentID` int (10) unsigned not null AUTO_INCREMENT,
54    `PaymmentMethod` int (10),
55    `Term` Int (8),
56    `intrest` int (5),
57    `downpayment` Int (10),
58    `MonthlyPayment` Int(6) 
59    PRIMARY KEY (`PaymentID`)
60    )ENGINE=InnoDB CHARSET=utf8;
61    
62    
63INSERT INTO `Car`(CarID) Values (1111);
64INSERT INTO `Car`(make)  Values ('toyota');
65INSERT INTO `Car`(colour)Values ('blue');
66INSERT INTO `Car`(Year) Values (1999);
67INSERT INTO `Car`(`Condition`) Values ('new');
68
69INSERT INTO `Sale`(SalesID) Values (1001);
70INSERT INTO `Sale`(SalesPersonID)  Values ('207143');
71INSERT INTO `Sale`(`retail price`)Values (12000);
72INSERT INTO `Sale`(`sale price`) Values (11000);
73INSERT INTO `Sale`(PaymentID) Values ('new');
74INSERT INTO `Sale`(CarID) Values (1111);
75INSERT INTO `Sale`(BuyerID)  Values (25534);