Question

You have information about recent sales that you want to use for testing the database. 1. Create the tables for the data provided. The tables should include the primary and foreign keys. Provide the SQL statements. 2. Insert the data into the tables. Provide the SQL statements. 3. Show the contents of each table. Provide the SQL statements.

Customer Table Customer ID, Last Name, First Name, Street Address, City, State, Zip Code, Current Balance, Credit Limit, Sales Rep. ID Sales Representatives Table Sales Rep ID, Last Name, First Name, Street Address, City, State, Zip, Region, Region Description, Total Commission, Commission Rate Orders Table Order ID, Order Date, Customer, Shipping Date, Order Lines Order ID, Part ID, Number Ordered, Quoted Price Part Table Part ID, Part Description, Units on Hand, Class, Warehouse Number, Unit Price

Please show all work

Answer 1

CREATE DATABASE sales;

// Coustomer Table

CREATE TABLE IF NOT EXISTS `customer_table` (
`Customer_ID` int(100) NOT NULL AUTO_INCREMENT,
`Last_Name` varchar(100) NOT NULL,
` First_Name` varchar(100) NOT NULL,
`Street_Address` varchar(100) NOT NULL,
`City` varchar(100) NOT NULL,
`State` varchar(100) NOT NULL,
`Zip_code` int(100) NOT NULL,
`Current_Balance` int(200) NOT NULL,
`Credit_Limit` int(200) NOT NULL,
`Sales_Rep_ID` varchar(1000) NOT NULL,
PRIMARY KEY (`Customer_ID`),
UNIQUE KEY `Customer_ID` (`Customer_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

// Sales Representative Data Table

CREATE TABLE IF NOT EXISTS `sales_rep_table` (
`sales_rep_id` int(100) NOT NULL AUTO_INCREMENT,
`Last_Name` varchar(100) NOT NULL,
`First_Name` varchar(100) NOT NULL,
`Street_Address` varchar(100) NOT NULL,
`City` varchar(100) NOT NULL,
`State` varchar(100) NOT NULL,
`Zip_code` int(100) NOT NULL,
`Region` varchar(100) NOT NULL,
`Region_description` varchar(200) NOT NULL,
`Total_Commission` int(100) NOT NULL,
PRIMARY KEY (`sales_rep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

// Orders Table

CREATE TABLE IF NOT EXISTS `orders_table` (
`Order_ID` int(100) NOT NULL AUTO_INCREMENT,
`Order_Date` date NOT NULL,
`Customer` varchar(100) NOT NULL,
`Shipping_Date` date NOT NULL,
`Order_Lines_Order_ID` varchar(100) NOT NULL,
`Part_ID` varchar(100) NOT NULL,
`Number_Ordered` varchar(100) NOT NULL,
`Quoted_Price_Part_Table_Part ID` varchar(100) NOT NULL,
`Part_Description` varchar(100) NOT NULL,
`Units_on_Hand` varchar(100) NOT NULL,
`Class` varchar(100) NOT NULL,
`Warehouse_Number` varchar(100) NOT NULL,
`Unit_Price` int(100) NOT NULL,
PRIMARY KEY (`Order_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*//Insertion of Data into Tables*/
INSERT INTO `sales`.`customer_table` (`Customer_ID`, `Last_Name`, ` First_Name`, `Street_Address`, `City`, `State`, `Zip_code`, `Current_Balance`, `Credit_Limit`, `Sales_Rep_ID`)
VALUES ('1', 'Text', 'Text', 'Text', 'Text', 'Text', '12313', '100', '1000', 'S1');

INSERT INTO `sales`.`sales_rep_table` (`sales_rep_id`, `Last_Name`, `First_Name`, `Street_Address`, `City`, `State`, `Zip_code`, `Region`, `Region_description`, `Total_Commission`)
VALUES ('1', 'Text', 'Text', 'Text', 'Text', 'Text', '111', 'Text', 'Text', '123');

INSERT INTO `sales`.`orders_table` (`Order_ID`, `Order_Date`, `Customer`, `Shipping_Date`, `Order_Lines_Order_ID`, `Part_ID`, `Number_Ordered`, `Quoted_Price_Part_Table_Part ID`, `Part_Description`, `Units_on_Hand`, `Class`, `Warehouse_Number`, `Unit_Price`)
VALUES ('1', '2019-03-13', 'Name', '2019-03-21', 'O12', 'P12', '2', 'QPPTP12', 'Text', 'Text', 'Text', 'Number', '123');

/* SELECTION of all the items of table and display*/
/* Coustomer Table*/
SELECT *
FROM `customer_table`
ORDER BY 'Customer_ID' ;

/* Sales representatives Table*/
SELECT *
FROM `orders_table`
ORDER BY 'Order_ID' ;

/* Orders Table*/
SELECT *
FROM `sales_rep_table`
ORDER BY 'sales_rep_id' ;

**** Thank You*******