Mini-hw-1

Due date: Nov 27, 2018.

Objectives: Mini-hw-2 on Vertica.

Assignment tools: Docker or local machine with Vertica.

What to turn in: You will turn command and results of a query using Vertica on the lobsters dataset from section. Submit everything as a single file.

How to submit the assignment: In your gitlab repository, you should see a directory called mini-hw2. Put your report in that directory. Remember to git add, git commit, and git push. You can add your report early and keep updating it and pushing it as you do more work. We will collect the final version after the deadline passes. If you need extra time on an assignment, let us know. This is a graduate course, so we are reasonably flexible with deadlines but please do not overuse this flexibility. Use extra time only when you truly need it.

Assignment Details

In this Assignment you will be required to have access to a Vertica server either via the docker container or locally.

The lobster data set was introduced in section 7. In this hoemwork you will make a few more queries to this database. As a reminder the tables in the lobster database we will be working with are:

stories: (id: int, created_at: time, description: string, hotness: float, markeddown_description: string, short_id: string, title:string, url:string user_id:int)
taggings: (id: int, story_id: int, tag_id: int)
tags:(id: int, tag:string)

Run the following queries. For 1 and 2 return the SQL, and the rows returned. You may restrict the columns you list for these two queries. For 3, run the queries a couple times and return mean runtime for each of the queries as well as the discussion of the resulting query times, no need to return number of rows or actual rows from the queries.

List the top 10 stories by hotness. (10 points)
List top 10 stories with the higest upvotes and lowest number of downvotes. How does this list compare with the results from the previous query? (10 points)
For the stories relation run the following queries, note the time to run these queries and discuss the observations(10 points):
- select * from stories;
- select id from stories;
- select id, title from stories;