CSE514-Homework 1 Solved

Q1 Assignment Setup

We will use SQLite for this assignment. SQLite is a software library that implements a
SQL database engine. We will use SQLite in this assignment because it offers an
extremely lightweight method to create and analyze structured datasets (by structured
we mean datasets in the form of tables rather than, say, free text). Using SQLite is a
minimal hassle approach to realizing the benefits of a relational database management
system.
Of course, SQLite does not do everything, but we will get to that point in later
assignments. In the meantime, you can also learn when to use SQLite and when not to
use it.
Resources
●     Ed discussion board

●     Important SQLite commands:

                                   ○    To view help contents: .help

                                   ○    To view a list of all your tables: .tables

                                   ○    To exit: .exit

●     A simple guide for commonly used command-line functions in SQLite.

●     More information on formatting output in SQLite.

●     An index of more detailed information for SQL commands in SQLite.

●     A SQL style guide in case you are interested (FYI only).

Q2
First, create a simple table using the following steps:
Q2.1
 

Create a table Edges(Source, Destination) where both Source and Destination are
integers.
Q2.2
Insert the tuples (10,5), (6,25), (1,3), and (4,4)

Q2.3
Write a SQL statement that returns all tuples.
 

Q2.4
Write a SQL statement that returns only column Source for all tuples

Q2.5
Write a SQL statement that returns all tuples where Source > Destination.

Q2.6
Now write a statement that inserts the tuple ('-1','2000'). Do you get an error? Why?
This is a tricky question, you might want to check the documentation.

Q3
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

 

Q4
Write the commands to 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.
Q5
Write a SQL query that returns every row in your MyRestaurants table. Experiment with
a few of SQLite's output formats and write this query six times, formatting the output in
the following ways.
Remember to include both the command you use to format the output along with your
query. When we run your code for 5.1 and 5.2 we should see the table printed 6 times.
Q5.1
Write the code to turn column headers on, then output the results in these three formats:

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 every column have width 15 (be sure that
every column has width 15 and not just the first one)
Q5.2
 

Now write the code to turn the column headers off, and output the results again in the
three formats.
Q6
Write a SQL query that returns only the name and distance of all restaurants within and
including 20 minutes of your house. The query should list the restaurants in alphabetical
order of names.
Q7