Answer to Question #46910 in Databases | SQL | Oracle | MS Access for SANDILE

Question

Create a database named MyCustomers, with the following tables using MySQL. Use
the appropriate data types and note that the underlined fields denotes a field to be
either a primary key or a foreign key
Customers (CustomerID, Name, Address, City)
Orders (OrderID, CustomerID, Amount, Date)
Insert at least 5 records in each of the tables appropriately.

Answer 1

CREATE DATABASE `MyCustomers` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `MyCustomers`;
CREATE TABLE `Customers` (
`CustomerID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(30) NOT NULL,
`Address` varchar(50) NOT NULL,
`City` varchar(30) NOT NULL,
PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `Orders` (
`OrderID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`CustomerID` int(11) NOT NULL,
`Amount` int(11) NOT NULL,
`Date` date NOT NULL,
CONSTRAINT `fk_CustomerID` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Customers` (`Name`, `Address`, `City`) VALUES('Maria Anders', '121 Hanover Sq.', 'London');
INSERT INTO `Customers` (`Name`, `Address`, `City`) VALUES('Hanna Moos', 'Cerrito 678', 'Buenos Aires');
INSERT INTO `Customers` (`Name`, `Address`, `City`) VALUES('Yang Wang', '27 Baker Blvd.', 'Eugene');
INSERT INTO `Customers` (`Name`, `Address`, `City`) VALUES('Manuel Pereira', 'City Center Plaza 280 Main St.', 'Elgin');
INSERT INTO `Customers` (`Name`, `Address`, `City`) VALUES('Annette Roulet', '15 Orchestra Terrace', 'Walla Walla');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(1, 5, '2014-03-17');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(1, 67, '2014-03-17');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(2, 32, '2014-04-11');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(3, 23, '2014-05-04');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(3, 35, '2014-05-20');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(4, 59, '2014-06-14');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(4, 7, '2014-07-10');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(5, 78, '2014-08-03');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(5, 32, '2014-08-05');
INSERT INTO `Orders` (`CustomerID`, `Amount`, `Date`) VALUES(5, 87, '2014-09-06');