* In your directory

/ projects/coursework/2021-summer/cs329e-mitra/login_name

create a subdirectory called "dbs" and cd to "dbs".

* copy the following two .csv files into that directory

https://www.cs.utexas.edu/users/mitra/csSummer2021/cs329/lectures/dbs/customers.csv

https://www.cs.utexas.edu/users/mitra/csSummer2021/cs329/lectures/dbs/items_ordered.csv

* Make sure that you are in the same directory as your .csv files and then
  login to your MySQL database server. Use your database.

* Create the two tables customers and items_ordered.
create table customers (customerid varchar(5), firstname varchar(20),
                        lastname varchar(20), city varchar(20),
                        state varchar(20));

create table items_ordered (customerid varchar(5), order_date date,
             item varchar(20), quantity int(11), price decimal(5,2));

* At the "mysql>" prompt run the following two commands (the upper case
  is purely optional, sql is case insensitive):

LOAD DATA LOCAL INFILE "customers.csv" INTO TABLE customers

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n'

IGNORE 1 LINES

(customerid, firstname, lastname, city, state);


LOAD DATA LOCAL INFILE "items_ordered.csv" INTO TABLE items_ordered

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n'

IGNORE 1 LINES

(customerid, @datevar, item, quantity, price)

set order_date = STR_TO_DATE(@datevar, '%d-%b-%y');

* The above commands should have inserted the data into your tables on
  the MySQL database.

* Now in the same directory (dbase) create a file called - exercises.sql

* The file should have two lines:

SELECT * FROM customers;

SELECT * FROM items_ordered;

* Run the file from the MySQL command line:

mysql> source exercises.sql;

* It should show you all the data from both the tables.

* Sample HR Database

* Download the Database Schema

https://www.cs.utexas.edu/users/mitra/csSummer2021/cs329/lectures/dbs/hr_database.pdf

* To understand the notation and symbols used in the HR Database schema:

https://vertabelo.com/blog/crow-s-foot-notation/

* Download the sql script to create the HR database tables:

https://www.cs.utexas.edu/users/mitra/csSummer2021/cs329/lectures/dbs/hr_table.sql

* Run the script on the command line:

mysql> source hr_table.sql;

* Download the sql script to load data into the HR database tables:

https://www.cs.utexas.edu/users/mitra/csSummer2021/cs329/lectures/dbs/hr_data.sql

* Run the script on the command line:

mysql> source hr_data.sql;