CS 4010 Spring 2018

Lab 8

 Find a computer and log into the OSX side using your CS login name and password.

Open Firefox and find the class web page.

Open TextWrangler - use the finder window and go to applications.
Open a new text file, called Lab8.txt, to record your work today - you will paste in your command lines and the results of running them. The header of the file should look like:
Your Name
CS 4010
Lab 8
May 14, 2018

Find the Terminal - use the finder window and go to applications and then utilities.
Today we are going to work on the things we learned in Chapter 15:
The SQL server is on Hopper. I will give you a paper with your account information and instructions on how to login. Since we will only be using it for a short time, changing your password is optional.

All work today will be done int the Terminal, with cuting and pasting into your Lab8.txt file in TextWrangler. Run the commands below
(they are also in your pcfb/examples/mysql_commands.txt):

(~) ssh hopper
Password: this is your usual cs password

yourlogin@hopper:(~) mysql -u <yourlogin> -p
Enter password: this is you database password

Note: You may need to edit your bash profile to add the following to your path (see Lab 4):
/usr/local/mysql/bin
Then close the Terminal window and relaunch to see your changes.

mysql> SHOW DATABASES;

mysql> USE <yourlogin>;

mysql> CREATE TABLE specimens (
    ->  specimen_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  vehicle TINYTEXT,
    ->  dive INTEGER,
    ->  date DATE,
    ->  lat FLOAT,
    ->  lon FLOAT,
    ->  depth FLOAT,
    ->  notes TEXT
    -> );

mysql> INSERT INTO specimens SET
    ->  vehicle='Tiburon',
    ->  dive=596,
    ->  date='2003-07-03',
    ->  lat=36.602,
    ->  lon=-122.375,
    ->  depth=1190,
    ->  notes='holotype';

mysql> INSERT INTO specimens SET    vehicle='Tiburon',    dive=515,    date='2002-11-24',    lat=36.7000,    lon=-122.0330,    depth=1156.0,    notes='';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='Tiburon',    dive=531,    date='2003-03-13',    lat=24.3170,    lon=-109.2030,    depth=1144.0,    notes='';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='Tiburon',    dive=547,    date='2003-03-31',    lat=24.2340,    lon=-109.6670,    depth=1126.0,    notes='';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='JSL II',    dive=1411,    date='1986-09-16',    lat=39.9400,    lon=-70.2383,    depth=518.0,    notes='paratype';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='JSL II',    dive=930,    date='1984-08-18',    lat=40.0838,    lon=-69.0502,    depth=686.0,    notes='Youngbluth (1989)';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='JSL II',    dive=3457,    date='2003-09-26',    lat=40.2962,    lon=-68.1113,    depth=862.0,    notes='Francesc Pages (personal communication)';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='Ventana',    dive=1575,    date='1999-03-11',    lat=36.7040,    lon=-122.0420,    depth=767.0,    notes='';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='Ventana',    dive=1777,    date='2000-06-16',    lat=36.7100,    lon=-122.0450,    depth=934.0,    notes='';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO specimens SET    vehicle='Ventana',    dive=2243,    date='2002-09-09',    lat=36.7080,    lon=-122.0640,    depth=1001.0,    notes='';
Query OK, 1 row affected (0.00 sec)

mysql> DESCRIBE specimens;

mysql> SELECT * FROM specimens;

CREATE TABLE ctd (
    ->    ctd_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    clock DATETIME,
    ->    vehicle TINYTEXT,
    ->    dive INTEGER,
    ->    depth FLOAT,
    ->    temperature FLOAT,
    ->    salinity FLOAT,
    ->    oxygen FLOAT,
    ->    lat FLOAT,
    ->    lon FLOAT
    ->    );

You will need to edit the path to your directory. This will allow you to read in files to the ctd table.
mysql> LOAD DATA LOCAL INFILE '~mmartin/pcfb/examples/ctd/Marrus_ctdTib515.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES  (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=515;
Query OK, 491 rows affected (0.02 sec)
Records: 491  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE '~mmartin/pcfb/examples/ctd/Marrus_ctdTib531.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=531;
Query OK, 1348 rows affected, 1746 warnings (0.02 sec)
Records: 1348  Deleted: 0  Skipped: 0  Warnings: 1746

mysql>  LOAD DATA LOCAL INFILE '~mmartin/pcfb/examples/ctd/Marrus_ctdTib547.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=547;
Query OK, 486 rows affected, 3 warnings (0.01 sec)
Records: 486  Deleted: 0  Skipped: 0  Warnings: 3

mysql>  LOAD DATA LOCAL INFILE '~mmartin/pcfb/examples/ctd/Marrus_ctdTib596.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=596;
Query OK, 760 rows affected (0.01 sec)
Records: 760  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE '~mmartin/pcfb/examples/ctd/Marrus_ctdVen1575.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=1575;
Query OK, 100 rows affected, 4 warnings (0.01 sec)
Records: 100  Deleted: 0  Skipped: 0  Warnings: 4

mysql>  LOAD DATA LOCAL INFILE '~mmartin/pcfb/examples/ctd/Marrus_ctdVen1777.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=1777;
Query OK, 210 rows affected (0.00 sec)
Records: 210  Deleted: 0  Skipped: 0  Warnings: 0

mysql>  LOAD DATA LOCAL INFILE '~mmartin/pcfb/examples/ctd/Marrus_ctdVen2243.txt' INTO TABLE ctd FIELDS TERMINATED BY ',' IGNORE 1 LINES (clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET dive=2243;
Query OK, 343 rows affected (0.01 sec)
Records: 343  Deleted: 0  Skipped: 0  Warnings: 0

This is going to be big
mysql> SELECT * FROM ctd;


Now for some experiments with queries. You should make notes about what these do and feel free to experiment.
mysql> UPDATE ctd SET vehicle='TIBURON' WHERE vehicle='tibr';

mysql> UPDATE ctd SET vehicle='VENTANA' WHERE vehicle='vnta';

mysql> SELECT COUNT(*) FROM specimens;

mysql> SELECT COUNT(*) FROM ctd;

mysql> SELECT vehicle,date FROM specimens;

mysql> SELECT DISTINCT vehicle FROM specimens;

mysql> SELECT vehicle,COUNT(*) FROM specimens GROUP BY vehicle;

mysql> SELECT vehicle,dive,COUNT(*) FROM ctd GROUP BY vehicle, dive;

mysql> SELECT vehicle, AVG(depth) FROM specimens GROUP BY vehicle;

Turn it in:
Go to the CS Homework Submission System: https://www.cs.csustan.edu/cshomework/
Choose: instructor Martin
              Spring 2018
              CS4010
              Lab8
Upload your Lab8.txt file as prompted.