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

Question #46910
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.
1
Expert's answer
2014-09-24T12:11:46-0400
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');

Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be the first!

Leave a comment

LATEST TUTORIALS
New on Blog
APPROVED BY CLIENTS