NCg8sEFV

1DROP DATABASE IF EXISTS dbJoin;
2CREATE DATABASE dbJoin;
3USE dbJoin;
4
5CREATE TABLE Customers(
6  customerid  int(11)  PRIMARY KEY NOT  NULL,
7  companyname  varchar(100)  NOT   NULL,
8  firstname  varchar(30)  NOT   NULL,
9  lastname  varchar(30)  NOT   NULL,
10  billingaddress  varchar(255)  NOT   NULL,
11  city  varchar(30)  NOT   NULL,
12  stateofprovince  varchar(100)  NOT   NULL,
13  zipcode  varchar(20),
14  email  varchar(255),
15  companywebsite  varchar(255),
16  phonenumber  varchar(30),
17  faxnumber  varchar(30),
18  shipaddress  varchar(255)  NOT   NULL,
19  shipcity  varchar(50)  NOT   NULL,
20  shipstateofprovince  varchar(50)  NOT   NULL,
21  shipzipcode  varchar(20)  NOT   NULL,
22  shipphonenumber  varchar(30)  NOT   NULL,
23  notes  text
24);
25
26CREATE TABLE Products(
27  productid  int(11) PRIMARY KEY NOT  NULL,
28  productname  varchar(100)  NOT   NULL,
29  unitprice  double,
30  instock  char(1)
31);
32
33CREATE TABLE ShippingMethods(
34  shippingmethodid  int(11)  PRIMARY KEY NOT  NULL,
35  shippingmethod  varchar(100) NOT NULL
36);
37
38CREATE TABLE Employees(
39  employeeid  int(11)  PRIMARY KEY NOT NULL,
40  firtname  varchar(30)  NOT NULL,
41  lastname  varchar(30)  NOT   NULL,
42  title  varchar(30),
43  workphone  varchar(20)
44);
45
46CREATE TABLE Orders(
47  orderid  int(11)  PRIMARY KEY NOT  NULL,
48  customerid  int(11),
49  employeeid  int(11),
50  orderdate  timestamp  NOT NULL,
51  purchaseordernumber  varchar(30),
52  shipdate  timestamp  NOT NULL,
53  shippingmethodid  int(11),
54  freightcharge  double,
55  taxes  double,
56  paymentreceived  char(1),
57  comment  text,
58    FOREIGN KEY (customerid)
59      REFERENCES Customers(customerid),
60    FOREIGN KEY (employeeid)
61      REFERENCES Employees(employeeid),
62    FOREIGN KEY (shippingmethodid)
63      REFERENCES ShippingMethods(shippingmethodid)
64);
65
66CREATE TABLE OrderDetails(
67  orderdetailid  int(11)  PRIMARY KEY NOT  NULL,
68  orderid  int(11),
69  productid  int(11),
70  quantity  int(11),
71  unitprice  double,
72  discount  double,
73    FOREIGN KEY (orderid)
74      REFERENCES Orders(orderid)
75);
76
77load data local infile 'Customers.csv'
78into table Customers fields terminated by ","
79enclosed by """"
80ignore 1 rows;
81
82load data local infile 'Products.csv'
83into table Customers fields terminated by ","
84enclosed by """"
85ignore 1 rows;
86
87load data local infile 'ShippingMethods.csv'
88into table Customers fields terminated by ","
89enclosed by """"
90ignore 1 rows;
91
92load data local infile 'Employees.csv'
93into table Customers fields terminated by ","
94enclosed by """"
95ignore 1 rows;
96
97load data local infile 'Orders.csv'
98into table Customers fields terminated by ","
99enclosed by """"
100ignore 1 rows;
101
102load data local infile 'OrderDetails.csv'
103into table Customers fields terminated by ","
104enclosed by """"
105ignore 1 rows;
106
107SELECT * FROM Customers;
108--SELECT * FROM Products;
109--SELECT * FROM ShippingMethods;
110--SELECT * FROM Employees;
111--SELECT * FROM Orders;
112--SELECT * FROM OrderDetails;