hw5.sql

hw5.sql

/*
CSCI E63 HW5 - Walter Yu, Fall 2018
Script commands to complete HW5
*/

-- Q1: Create tables:
create table stations(
  stations_id    int(11)       ,
  name           varchar(64)   ,
  latitude       decimal(11)   ,
  longitude      decimal(11)   ,
  dockcount      varchar(64)   ,
  landmark       varchar(64)   ,
  installation   timestamp     );

create table trips(
  TripID         int(11)       ,
  Duration       int(11)       ,
  StartDate      timestamp     ,
  StartStation   varchar(128)  ,
  StartTerminal  varchar(11)   ,
  EndDate        timestamp     ,
  EndStation     varchar(128)  ,
  EndTerminal    varchar(11)   ,
  BikeNo         varchar(11)   ,
  SubscriberType varchar(64)   ,
  ZipCode        varchar(11)   );

ALTER TABLE trips
MODIFY COLUMN StartTerminal int(11);

ALTER TABLE trips
MODIFY COLUMN EndTerminal int(11);

LOAD DATA LOCAL INFILE '/home/kharma/data/201508_station_data.csv' INTO\
  TABLE stations FIELDS TERMINATED by ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;

LOAD DATA LOCAL INFILE '/home/kharma/data/201508_trip_data.csv' INTO\
  TABLE trips FIELDS TERMINATED by ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;

-- Q2: Join tables:
SELECT s.stations_id, s.landmark, t.StartTerminal, t.EndTerminal
FROM stations s, trips t
WHERE s.stations_id = t.StartTerminal
AND t.StartTerminal BETWEEN '40' AND '83'
AND t.EndTerminal BETWEEN '40' AND '83'
AND t.StartTerminal != '80'
AND t.EndTerminal != '80';