76cJY0cp

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