using databases with python week 2 assignment

Single-Table SQL

1. Structured Query Language (SQL) is used to (check all that apply)

  • Insert data
  • Delete data
  • Check Python code for errors
  • Create a table

2. Which of these is the right syntax to make a new table?

  • MAKE people;
  • MAKE DATASET people;
  • CREATE people;
  • CREATE TABLE people;

3. Which SQL command is used to insert a new row into a table?

  • INSERT AFTER
  • ADD ROW
  • INSERT ROW
  • INSERT INTO

4. Which command is used to retrieve all records from a table?

  • SELECT * FROM Users
  • RETRIEVE all FROM User
  • RETRIEVE * FROM Users
  • SELECT all FROM Users

5. Which keyword will cause the results of the query to be displayed in sorted order?

  • GROUP BY
  • ORDER BY
  • WHERE
  • None of these

6. In database terminology, another word for table is

  • field
  • attribute
  • row
  • relation

7. In a typical online production environment, who has direct access to the production database?

  • Project Manager
  • Database Administrator
  • Developer
  • UI/UX Designer

8. Which of the following is the database software used in this class?

  • Oracle
  • SQL Server
  • Postgres
  • MySQL
  • SQLite

9. What happens if a DELETE command is run on a table without a WHERE clause?

  • The first row of the table will be deleted
  • It is a syntax error
  • All the rows in the table are deleted
  • All the rows without a primary key will be deleted

10. Which of the following commands would update a column named "name" in a table named "Users"?

  • Users->name = ‘new name’ WHERE …
  • Users.name=’new name’ WHERE …
  • UPDATE Users SET name=’new name’ WHERE …
  • UPDATE Users (name) VALUES (‘new name’) WHERE …

11. What does this SQL command do?

SELECT COUNT(*) FROM Users

Hint: This is not from the lecture

  • It only retrieves the rows of Users if there are at least two rows
  • It is a syntax errror
  • It adds a COUNT column to the Users table
  • It counts the rows in the table Users

Our First Database

Instructions If you don't already have it, install the SQLite Browser from http://sqlitebrowser.org/. Then, create a SQLITE database or use an existing database and create a table in the database called "Ages": CREATE TABLE Ages ( name VARCHAR(128), age INTEGER ) Then make sure the table is empty by deleting any rows that you previously inserted, and insert these rows and only these rows with the following commands: DELETE FROM Ages; INSERT INTO Ages (name, age) VALUES ('Dani', 17); INSERT INTO Ages (name, age) VALUES ('Jasim', 14); INSERT INTO Ages (name, age) VALUES ('Lacci', 33); INSERT INTO Ages (name, age) VALUES ('Salvador', 38); INSERT INTO Ages (name, age) VALUES ('Cale', 34); INSERT INTO Ages (name, age) VALUES ('Sajjad', 26); Once the inserts are done, run the following SQL command: SELECT hex(name || age) AS X FROM Ages ORDER BY X Find the first row in the resulting record set and enter the long string that looks like 53656C696E613333. Note: This assignment must be done using SQLite - in particular, the SELECT query above will not work in any other database. So you cannot use MySQL or Oracle for this assignment.

I’m unable to execute or interact with an external database directly. However, I can guide you through the steps you need to follow to complete this assignment using SQLite.

  1. Install the SQLite Browser: If you haven’t already, download and install the SQLite Browser from http://sqlitebrowser.org/.

  2. Create an SQLite database and the “Ages” table: Launch the SQLite Browser and create a new database. Then, execute the following SQL commands to create the “Ages” table and insert the specified rows into the table:

CREATE TABLE Ages (
name VARCHAR(128),
age INTEGER
);

DELETE FROM Ages;

INSERT INTO Ages (name, age) VALUES (‘Dani’, 17);
INSERT INTO Ages (name, age) VALUES (‘Jasim’, 14);
INSERT INTO Ages (name, age) VALUES (‘Lacci’, 33);
INSERT INTO Ages (name, age) VALUES (‘Salvador’, 38);
INSERT INTO Ages (name, age) VALUES (‘Cale’, 34);
INSERT INTO Ages (name, age) VALUES (‘Sajjad’, 26);

3. Execute the SQL query: After inserting the data, execute the following SQL query to find the first row in the resulting record set:

SELECT hex(name || age) AS X FROM Ages ORDER BY X;

  1. Look for the long string that looks like “53656C696E613333” in the results.

Note: Make sure to use an SQLite database for this assignment, as the provided SQL query won’t work in other database systems.

Counting Email in a Database

To get credit for this assignment, perform the instructions below and upload your SQLite3 database here: No file chosen (Must have a .sqlite suffix) Hint: The top organizational count is 536. You do not need to export or convert the database - simply upload the .sqlite file that your program creates. See the example code for the use of the connect() statement. Counting Organizations This application will read the mailbox data (mbox.txt) and count the number of email messages per organization (i.e. domain name of the email address) using a database with the following schema to maintain the counts. CREATE TABLE Counts (org TEXT, count INTEGER) When you have run the program on mbox.txt upload the resulting database file above for grading. If you run the program multiple times in testing or with different files, make sure to empty out the data before each run. You can use this code as a starting point for your application: http://www.py4e.com/code3/emaildb.py. The data file for this application is the same as in previous assignments: http://www.py4e.com/code3/mbox.txt. Because the sample code is using an UPDATE statement and committing the results to the database as each record is read in the loop, it might take as long as a few minutes to process all the data. The commit insists on completely writing all the data to disk every time it is called. The program can be speeded up greatly by moving the commit operation outside of the loop. In any database program, there is a balance between the number of operations you execute between commits and the importance of not losing the results of operations that have not yet been committed.

I’m unable to execute Python programs or accept file uploads directly. However, I can provide you with a Python script to count email messages per organization and store the results in an SQLite database. You can then run this script on your local machine to process the mbox.txt file and generate the database file.

Here’s the Python script:

import sqlite3

conn = sqlite3.connect(’emaildb.sqlite’)
cur = conn.cursor()

cur.execute(”’
CREATE TABLE IF NOT EXISTS Counts (org TEXT, count INTEGER)”’)

file_name = input(‘Enter file name: ‘)
if len(file_name) < 1:
file_name = ‘mbox.txt’
try:
file_handle = open(file_name)
except:
print(“File not found”)
quit()

for line in file_handle:
if not line.startswith(‘From: ‘):
continue
pieces = line.split()
email = pieces[1]
parts = email.split(‘@’)
org = parts[1]

cur.execute(‘SELECT count FROM Counts WHERE org = ?’, (org,))
row = cur.fetchone()
if row is None:
cur.execute(‘INSERT INTO Counts (org, count) VALUES (?, 1)’, (org,))
else:
cur.execute(‘UPDATE Counts SET count = count + 1 WHERE org = ?’, (org,))
conn.commit()

sqlstr = ‘SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10’

for row in cur.execute(sqlstr):
print(str(row[0]), row[1])

cur.close()

Make sure you have the SQLite database module for Python installed (sqlite3). Save the script as a .py file and run it. You will be prompted to enter the file name (press Enter to use the default “mbox.txt”).

The script will process the mbox.txt file, count the email messages per organization, and store the results in an SQLite database named “emaildb.sqlite.” Finally, it will display the top 10 organizations with the highest email counts.

After running the script, you can upload the “emaildb.sqlite” database file for grading.

Leave a Reply