CSE 344 Homework 1

Objectives:

To be able to create and manipulate tables in sqlite3.

Reading assignment:

Ch. 2.3, Ch. 6.1

Assignment tools:

SQLite 3

Due date:

April 4, 2011 at 11:59pm. Turn it in here.

What to turn in:

A file containing all of your SQL and SQLite commands, and SQL comments for your responses that are not in SQL.

You will use SQLite for this assignment. To run SQLite do the following:

• on Mac or Linux, open a terminal and type sqlite3
• on Windows, open cygwin and type sqlite3 (you may have to install it by running setup --> database --> sqlite3).

1. [25 points] First, create a simple table using the following steps:
   1. Create a table R(A,B) where both A and B are integers.
   2. Insert the tuples (2,4), (1,1), (3,2)
   3. Write a SQL statement that returns all tuples
   4. Now insert the tuple ('5','2'). Do you get an error? Why?
   5. Write a SQL statement that returns only column A for all tuples
   6. Write a SQL statement that returns all tuples where A<=B
For the next question you will be asked to create tables with attributes of types integer, varchar, date, and Boolean. However, SQL Lite does not have date and Boolean: you will use varchar and int instead:
• 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 (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-06-03') then 'Taking classes' when date('now') < date('2011-06-09') then 'Exams' else 'Vacation' end;
    
2. [10 points] Create a table called MyRestaurants with the following attributes:
   
   • Name of the restaurant: a varchar field
   • Type of food they make: a varchar field
   • Distance (in minutes) from your house: an integer
   • Date of your last visit: a varchar field, interpreted as date
   • Whether you like it or not: an integer, interpreted as a Boolean
3. [10 points] 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 iLike field NULL.

4. [10 points] Write a SQL query to return all restaurants in your table.
5. [15 points] Now experiment with a few of SQLite's output formats using the SQL query you wrote for question 4:
   1. print the results in comma-separated form
   2. print the results in list form, delimited by " | "
   3. print the results in column form, and make each column have width 15
   4. for each of the formats above, try printing/not printing the column headers with the results
6. [15 points] Modify your SQL query such that it prints "I liked it" or "I hated it" for each restaurant you liked or not.
7. [15 points] Write a SQL query that returns all restaurants that you like, and have not visited since more than 3 months ago.

ADDITIONAL RESOURCES:

• Some important SQLite commands:
  • to view help contents: ".help"
  • to view a list of all your tables: ".tables"
  • to exit: ".exit"
    
    
• This page provides a simple guide for commonly used command-line functions in SQLite.
  
  
• This page provides more information on formatting output in SQLite.
  
  
• This page provides an index of more detailed information for SQL commands in SQLite.