**Objectives:** To be able to create and manipulate tables in sqlite3, and write simple queries using SQL. To be familiar with git and version control.
### Problems
1. First, create a simple table using the following steps: Write a SQL statement to create a table Edges(Source, Destination) where both Source and Destination are integers. Write a SQL statement to insert the tuples `(10,5)`, `(6,25)`, `(1,3)`, and `(4,4)` Write a SQL statement that returns all tuples. Write a SQL statement that returns only column Source for all tuples. Write a SQL statement that returns all tuples where Source > Destination. Now insert the tuple `(-1,2000)`. Do you get an error? Why? Answer this as a comment in your file. This is a tricky question, you might want to [check the documentation](http://www.sqlite.org/datatype3.html).
2. Next, you will create a table with attributes of types integer, varchar, date, and Boolean.However, SQLite does not have date and Boolean: you will use `varchar` and `int` instead. Some notes: 0 (false) and 1 (true) are the values used to interpret Booleans. Date strings in SQLite are in the form: YYYY-MM-DD.Examples of valid date strings include: `1988-01-15`, `0000-12-31`, and `2011-03-28`.Examples of invalid date strings include: `11-11-01`, `1900-1-20`, `2011-03-5`, and `2011-03-50`. Examples of date operations on date strings (feel free to try them):`select date(2011-03-28)`;`select date(now)`;`select date(now, -5 year)`;`select date(now, -5 year, +24 hour)`;`select case when date(now) < date(2011-12-09) then Taking classes when date(now) < date(2011-12-16) then Exams else Vacation end;` What does this query do? (no need to turn in your answer)Create a table called `MyRestaurants` with the following attributes (you can pick your own names for the attributes, just make sure it is clear which one is for which): Name of the restaurant: a `varchar` field Type of food they make: a `varchar` field Distance (in minutes) from your house: an `int` Date of your last visit: a `varchar` field, interpreted as date Whether you like it or not: an `int`, interpreted as a Boolean
3.Insert at least five tuples using the SQL INSERT command five (or more) times.You should insert at least one restaurant you liked, at least one restaurant you did not like,and at least one restaurant where you leave the I like field `NULL`.
4.Write a SQL query that returns all restaurants in your table. Experiment with a few of SQLitesoutput formats and show the command you use to format the output along with your query (do not include the query output): print the results in comma-separated form print the results in list form, delimited by ` | ` print the results in column form, and make each column have width 15 for each of the formats above, show the command you use to print the column headers with the results
5.Write a SQL query that returns only the name and distance of all restaurants within andincluding 20 minutes of your house. The query should list the restaurants in alphabetical order of names.
6.Write a SQL query that returns all restaurants that you like, but have not visitedsince more than 3 months ago.
7.Write a SQL query that returns all restaurants that are within and including 10 mins from your house.
### Sign up on Amazon Web Services
We will be using Amazon web services in a later assignment. Before that, you need to sign up for an account and receive some free credits ($$$) from them. Follow these steps to set up your Amazon Web Services account. **Make sure you do this ASAP as the process can take time and it is beyond our control.**
1. If you do not already have an Amazon account, go to [their website](http://aws.amazon.com/) and sign up. Note: Amazon will ask you for your credit card information during the setup process. This is normal. Then sign in to your AWS console, go to Support -> Support center in the navigation bar, and locate your **account number**.
2. To get $$$ to use Amazon AWS, you must apply for credits by going to their [education website](https://aws.amazon.com/education/awseducate/apply/).**You must use your UW email address, `<your_uwid>@uw.edu`, when registering for the credits, as they use this to verify your identity.** Leave the promo code blank, and enter your AWS account number on the next page. Make sure you dont check the starter account option on the final page as that has limited permissions which may cause problems.
3. After applying, you will have to wait to be approved. You should get an email when your application has been approved, which gives you a credit code. Make sure you check the spam folder. Once you have it, go to [the AWS website](http://aws.amazon.com/awscredits/) and apply the credit. We have no control / idea how long this can take, but was told it can range from minutes to days. Hence, it is crucial that you apply ASAP before we release the assignment later in the quarter!
You should get $100 from AWS once your application is approved. The credits that you have left over after the assignment are for you to keep, but if you exceeded the credits due to forgetting to turn on your clusters / mining bitcoins etc then you will be responsible for paying the extra bill :).
For HW1, you dont need to turn in anything for this part of the assignment. We will ask you to acknowledge that your application has been approved in HW2.
### Programming styleTo encourage good SQL programming style please follow these two simple style rules:
Give explicit names to all tables referenced in the `FROM` clause.For instance, instead of writing:`select * from flights, carriers where carrier_id = cid`write`select * from flights as F, carriers as C where F.carrier_id = C.cid`(notice the `as`) so that it is clear which table you are referring to.
Similarly, reference to all attributes must be qualified by the table name.Instead of writing:`select * from flights where fid = 1`write`select * from flights as F where F.fid = 1`This will be useful when you write queries involving self joins in later assignments.
To help you check on whether your query is compliant with the above, use the [Cosette tool](http://cosette.cs.washington.edu/) developed by the [UW Database research group](http://db.cs.washington.edu/). Cosette is built to do more than syntax checking. To use Cosette, sign up for an account, read through the brief tutorial, and put your query as one of the input queries (make sure you declare the input table schemas correctly). If your query contains stylistic errors, you will get a Syntax Error in the results pane. Otherwise, it will return whether the two input queries are equivalent or not.
Note: Cosette currently can only check the syntax for read queries (i.e., no inserts, updates, or deletes).
## Submission Instructions<a name=submission></a>
We will be using `git`, a source code control tool, for distributing and submitting homework assignments in this class.This will allow you to download the code and instruction for the homework,and also submit the labs in a standardized format that will streamline grading.
You will also be able to use `git` to commit your progress on the labsas you go. **This is important**: Use `git` to back up your work. Backup regularly by both committing and pushing your code as we describe below.
Course git repositories will be hosted as arepository in [GitHub](https://gitlab.cs.washington.edu/cse414-18sp/cse414-18sp).However, your code will be in a private repository hosted on [CSEs gitlab](https://gitlab.cs.washington.edu)that is visible only to you and the course staff.
### Getting started with Git
There are numerous guides on using `git` that are available. They range from being interactive to just text-based.Find one that works and experiment making mistakes and fixing them is a great way to learn.Here is a [link to resources](https://help.github.com/articles/what-are-other-good-resources-for-learning-git-and-github)that GitHub suggests starting with. If you have no experience with `git`, you may find this[web-based tutorial helpful](https://try.github.io/levels/1/challenges/1).
Git may already be installed in your environment; if its not, youll need to install it first.For `bash`/Linux environments, git should be a simple `apt-get` / `yum` / etc. install.More detailed instructions may be [found here](http://git-scm.com/book/en/Getting-Started-Installing-Git).Git is already installed on CSE virtual machine.
If you are using Eclipse or IntelliJ, many versions come with git already configured.The instructions will be slightly different than the command line instructions listed but will workfor any OS. For Eclipse, detailed instructions can be found at[EGit User Guide](http://wiki.eclipse.org/EGit/User_Guide) or the[EGit Tutorial](http://eclipsesource.com/blogs/tutorials/egit-tutorial).
### Cloning your repository for homework assignments
We have created a git repository that you will use to commit and submit your the homework assignments.This repository is hosted on the [CSEs GitLab](https://gitlab.cs.washington.edu) ,and you can view it by visiting the GitLab website at`https://gitlab.cs.washington.edu/cse414-18sp/cse414-[your CSE username]`.
Youll be using this **same repository** for each of the homework assignments this quarter,so if you dont see this repository or are unable to access it, let us know immediately!
The first thing youll need to do is set up a SSH key to allow communication with GitLab:
1. If you dont already have one, generate a new SSH key. See [these instructions](http://doc.gitlab.com/ce/ssh/README.html) for details on how to do this.2. Visit the [GitLab SSH key management page](https://gitlab.cs.washington.edu/profile/keys). Youll need to log in using your CSE account.3. Click Add SSH Key and paste in your **public** key into the text area.
While youre logged into the GitLab website, browse around to see which projects you have access to.You should have access to `cse414-[your username]`.Spend a few minutes getting familiar with the directory layout and file structure. For now nothing willbe there except for the `hw1` directory with these instructions.
We next want to move the code from the GitLab repository onto your local file system.To do this, youll need to clone the 414 repository by issuing the following commands on the command line:
`sh$ cd [directory that you want to put your 414 assignments]$ git clone [email protected]:cse414-18sp/cse414-[your CSE username].git$ cd cse414-[your CSE username]`
This will make a complete replica of the repository locally. If you get an error that looks like:
`shCloning into cse414-[your CSE username]Permission denied (publickey).fatal: Could not read from remote repository.`
then there is a problem with your GitLab configuration. Check to make sure that your GitLab username matches the repository suffix, that your private key is in your SSH directory (`~/.ssh`) and has the correct permissions, and that you can view the repository through the website.
Cloning will make a complete replica of the homework repository locally. Any time you `commit` and `push` your local changes, they will appear in the GitLab repository. Since well be grading the copy in the GitLab repository, its important that you remember to push all of your changes!
### Adding an upstream remote
The repository you just cloned is a replica of your own private repository on GitLab.The copy on your file system is a local copy, and the copy on GitLab is referred to as the `origin` remote copy. You can view a list of these remote links as follows:
`sh$ git remote -v`
There is one more level of indirection to consider.When we created your `cse414-[your CSE username]` repository, we forked a copy of it from anotherrepository `cse414-18sp`. In `git` parlance, this original repository referred to as an `upstream` repository.When we release bug fixes and subsequent labs, we will put our changes into the upstream repository, and you will need to be able to pull those changes into your own. See [the documentation](https://git-scm.com/book/en/v2/Git-Basics-Working-with-Remotes) for more details on working with remotes they can be confusing!
In order to be able to pull the changes from the upstream repository, well need to record a link to the `upstream` remote in your own local repository:
`sh$ # Note that this repository does not have your username as a suffix!$ git remote add upstream [email protected]:cse414-18sp/cse414-18sp.git`
For reference, your final remote configuration should read like the following when its setup correctly:
`sh$ git remote -vorigin [email protected]:cse414-18sp/cse414-[your CSE username].git (fetch)origin [email protected]:cse414-18sp/cse414-[your CSE username].git (push)upstream [email protected]:cse414-18sp/cse414-18sp.git (fetch)upstream [email protected]:cse414-18sp/cse414-18sp.git (push)`
In this configuration, the `origin` (default) remote links to **your** repositorywhere youll be pushing your individual submission. The `upstream` remote points to **our**repository where youll be pulling subsequent homework and bug fixes (more on this below).
Lets test out the origin remote by doing a push of your master branch to GitLab. Do this by issuing the following commands:
`sh$ touch empty_file$ git add empty_file$ git commit empty_file -m Testing git$ git push # to origin by default`
The `git push` tells git to push all of your **committed** changes to a remote. If none is specified, `origin` is assumed by default (you can be explicit about this by executing `git push origin`). Since the `upstream` remote is read-only, youll only be able to `pull` from it `git push upstream` will fail with a permission error.
After executing these commands, you should see something like the following:
`shCounting objects: 4, done.Delta compression using up to 4 threads.Compressing objects: 100% (2/2), done.Writing objects: 100% (3/3), 286 bytes | 0 bytes/s, done.Total 3 (delta 1), reused 0 (delta 0)To [email protected]:cse414-18sp/cse414-[your CSE username].gitcb5be61..9bbce8d master -> master`
We pushed a blank file to our origin remote, which isnt very interesting. Lets clean up after ourselves:
`sh$ # Tell git we want to remove this file from our repository$ git rm empty_file$ # Now commit all pending changes (-a) with the specified message (-m)$ git commit -a -m Removed test file$ # Now, push this change to GitLab$ git push`
If you dont know Git that well, this probably seemed very arcane. Just keep using Git and youll understand more and more. Well provide explicit instructions below on how to use these commands to actually indicate your final lab solution.
### Pulling from the upstream remote
If we release additional details or bug fixes for this homework,well push them to the repository that you just added as an `upstream` remote. Youll need to `pull` and `merge` them into your own repository. (Youll also do this for subsequent labs!) You can do both of these things with the following command:
`sh$ git pull upstream masterremote: Counting objects: 3, done.remote: Compressing objects: 100% (3/3), done.remote: Total 3 (delta 2), reused 0 (delta 0)Unpacking objects: 100% (3/3), done.From gitlab.cs.washington.edu:cse414-18sp/cse414-18sp* branch master -> FETCH_HEAD7f81148..b0c4a3e master -> upstream/masterMerge made by the recursive strategy.README.md | 2 +-1 file changed, 1 insertion(+), 1 deletion(-)`
Here we pulled and merged changes to the `README.md` file. Git may open a text editor to allow you to specify a merge commit message; you may leave this as the default. Note that these changes are merged locally, but we will eventually want to push them to the GitLab repository (`git push`).
Note that its possible that there arent any pending changes in the upstream repository for you to pull. If so, `git` will tell you that everything is up to date.
### Collaboration
All CSE 414 assignments are to be completed **INDIVIDUALLY**! However, you may discuss your high-level approach to solving each lab with other students in the class (and you are always welcome to discuss with any of the course staff).
### Regrades
We will pushing feedback on the assignment to your repo. For regrading, please post a *private message to the staff* on [Piazza](piazza.com/washington/spring2018/cse414) *within one week* after you receive the staff feedback. Unfortunately we will not be able to handle late regrade requests.
### Submitting your assignment
You may submit your code multiple times; we will use the latest version you submit that arrivesbefore the deadline.Put your `.sql` files in `hw1/submission`. Your directory structure shouldlook like this after you have completed the assignment:
`shcse414-[your CSE username] README.md turnInHW.sh # script for turning in hw hw1 hw1.md # this is the file that you are currently reading submission hw1-q1.sql # your solution to question 1 hw1-q2.sql # your solution to question 2 hw1-q3.sql # your solution to question 3`
**Important**: In order for your write-up to be added to the git repo, you need to explicitly add it:
`sh$ cd submission$ git add hw1-q1.sql hw1-q2.sql `
Or if you do`sh$ git add submission`
Then it will add *all* the files inside the `submission` directory to the repo.
The criteria for your homework being submitted on time is that your code must be tagged andpushed by the due date and time. This means that if one of the TAs or the instructor were to open up GitLab, they would be able to see your solutions on the GitLab web page.
**Just because your code has been committed on your local machine does not mean that it has been submitted it needs to be on GitLab!**
There is a bash script `turnInHw.sh` in the root level directory of your repository that commits your changes, deletes any prior tag for the current lab, tags the current commit, and pushes the branch and tag to GitLab. If you are using Linux or Mac OSX, you should be able to run the following:
`sh$ ./turnInHw.sh hw1`
You should see something like the following output:
`sh$ ./turnInHw.sh hw11[master b155ba0] Homework 11 file changed, 1 insertion(+)Deleted tag hw1 (was b26abd0)To [email protected]:cse414-18sp/cse414-[your CSE username].git [deleted] hw1Counting objects: 11, done.Delta compression using up to 4 threads.Compressing objects: 100% (4/4), done.Writing objects: 100% (6/6), 448 bytes | 0 bytes/s, done.Total 6 (delta 3), reused 0 (delta 0)To [email protected]:cse414-18sp/cse414-[your CSE username].gitae31bce..b155ba0 master -> masterCounting objects: 1, done.Writing objects: 100% (1/1), 152 bytes | 0 bytes/s, done.Total 1 (delta 0), reused 0 (delta 0)To [email protected]:cse414-18sp/cse414-[your CSE username].git* [new tag] hw1 -> hw1`
#### Final Word of Caution!
Git is a distributed version control system. This means everything operates offline until you run `git pull` or `git push`. This is a great feature.
The bad thing is that you may **forget to `git push` your changes**. This is why we strongly, strongly suggest that you **check GitLab to be sure that what you want us to see matches up with what you expect**. As a second sanity check, you can re-clone your repository in a different directory to confirm the changes:
`sh$ git clone [email protected]:cse414-18sp/cse414-[your CSE username].git confirmation_directory$ cd confirmation_directory$ # make sure everything is as you expect `
Reviews
There are no reviews yet.