INFO-I590: Usable Artificial Intelligence
Assignment-2: SQL

The aim of the assignment is to provide hands-on experience with Python along
with SQL.
SQL:
In previous assignment, you were able to process a text file and create a disctionary. In
this section you are going to save the text and the dictionary in an SQLite database and
write some select queries.
1. Creating database: Create a database with name panchatantra.sqlite and create the
following tables using python
a. Create a ‘stories’ table for storing the story that is read from file in the following
attributes. The story_id attribute is just a primary key and stores a unique
integer value. The story attribute is used to store the story read from text file.
i. story_id(INTEGER, PK), story(Text)
b. Create an ‘information’ table to store the content present in dictionary. The
information_id attribute is the primary key and stores a unique integer value.
The word, occurrences attributes to the key value pair present in your dictionary.
The story_id attribute in this table refers to story_id present in stories table.
i. information_id(INTEGER, PK), word(text), occurrences(INTEGER),
story_id(INTEGER, FK)
2. Insert data into tables:
a. Now update the python script to store the data into SQLite database.
3. Writing “Select” queries and execute them using python:
a. Queries part 1: Write SQL queries using the data you have inserted in your
SQLite database
i. Print all those story_id(s) which have the word “Panchatantra” more
than once.
ii. Print the story_id, story, occurrences of the word “the” in each story
using stories and information table.
1. Hint: Apply join on stories and information table and use ‘where’
clause with word attribute.
b. Queries part 2: Write SQL queries using the soccer data(database.sqlite)
attached along with the assignment
i. Print the birthday of the player whose name is “Aaron Kuhl”
1. Hint: Use ‘Player’ table
ii. Print the number of times the team_fifa_api_id ‘673’ appeared in
Team_attribute table.
1. Hint: Apply GROUP BY clause on team_fifa_api_id attribute


iii. Print country name, league name that have matches on “2014-04-20
00:00:00”
1. Hint: Apply join on Match Table and Country table, Match
Table and League Table and use where clause with
condition on date attribute.
4. Hints/outcome:
a. Create tables with foreign key relationship.
b. Inserting records in different tables using the foreign relationship key.
c. Python database connection.
d. Write ‘Select’ queries using one or more the tables.
e. I recommend you execute your queries in SQLite Browser before you execute
using python script.
5. Submission:
a. SQLite file i.e.., panchatantra.sqlite file
b. Python script containing
i. All the create, Insert and select queries within it.