SQL Practice - Relational Joins

Setup

  1. Download the binary database file Baseball-AL-East-2015.sqlite into a top-level directory named exactly Baseball-2.
    The file contains a populated database of information about the teams in the American League East for 2015.
    Executing the following command will attach this database to the sqlite3 RDBMS:

    sqlite3 Baseball-AL-East-2015.sqlite

  2. Download the ActivityJournal.txt and fill it in for each query.

  3. The table schema are a tad different that what we showed in class - there are extra attributes for some tables and an entirely new table showing the coaches for each team. Run the .schema command to see the available tables and how they are structured.

  4. Download Query.sql and fill in the dummy placeholder queries with "real" queries that provide whatever is requested in the preceding SQL comments; SQL comments start with -- and continue to the end of the line. Placeholder queries have the form:

    SELECT 'Placeholder for X' ;

    where X identifies the query.

  5. Loading the database and executing .read Query.sql on the unaltered provided file will simply print the placeholder strings.

  6. To test your program, we will run the following command using a fresh database:

    sqlite3 Baseball-AL-East-2015.sqlite '.read Query.sql' > your_output.txt

    and compare your output to QueryOutput.txt. using the diff command:

    diff -w your_output.txt QueryOutput.txt

    The -w flag ignores differences due solely to whitespace characters.

Submission and Grading