Basic SQL Homework

For this assignment you will submit some database schemas for SQLite databases, and files of insert statements to populate the databases.

See the SQLite Guide under the “Guides” section of the class site for help on using sqlite3 and basic SQL statements.

The .sql files that you are to edit can be edited in any text editor such as Atom, Sublime Text, etc.

First Database

First, create a schema in schema.sql. Place two CREATE TABLE statements in this file to create two tables. The first table must be named student and must store the following 4 fields:

• student_id, an integer
• name, a string
• major, a string
• graduation_year, an integer

The second table must be named faculty and must store the following 3 fields:

• faculty_id, an integer
• name, a string
• department, a string

Use the INTEGER type for integer fields and the TEXT type for string fields.

In the file inserts.sql, write 4 INSERT INTO statements that insert 2 records into student and 2 records into faculty.

Run all these statements on a database file and make sure there are no errors. Perform SELECT statements on the tables to be sure the data was inserted correctly. For example, the following commands would run your SQL statements on the file test.sqlite. This file will be created automatically if it does yet exist.

sqlite3 test.sqlite < schema.sql
sqlite3 test.sqlite < inserts.sql

If there are syntax errors you can simply delete test.sqlite and start over. You can delete the file as you normally would using Finder or File Explorer, or you can delete a file on the terminal using the command rm. Be very careful with the rm command, it will permanently delete files. This command will delete the file test.sqlite:

rm test.sqlite

Once you have the statements from the files working without errors, you can test SELECT statements by entering interactive mode on the database:

sqlite3 test.sqlite

Make sure to also read the guide linked above, and refer back to it when needed.

Second Database

In the files your_schema.sql and your_inserts.sql do the same thing you did for the first database, but come up with your own schema. Create 2 tables to store data about 2 types of items that are related in some way. This can be anything you want, but each table should have at least 3 fields.

Submission

Add the .sql files using git add, then commit and push. There is no need to submit the database itself, because that can be built from the .sql files.

git-keeper will run tests against schema.sql and inserts.sql to ensure the statements run correctly. your_schema.sql and your_inserts.sql will be graded manually. For full credit you must follow the instructions above and your SQL statements must be free of syntax errors.