aUDvDrU9

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