Labs, pr2_en

PR2EN14: SQL

Lab materials

Lab tasks

In this lab task, you are going to interface with an SQLite3 database using the libsqlite3-dev library. You start out by adding data to the existing database in the first lab task and continue by querying the data in the second lab task.

The lab has two advanced tasks, first of which switches out the adding of the data. Second one introduces you to updating existing data rows.

Download the lab task database: study_information.db

Data model

Lab task 1: Add yourself to the database

NB! The task might not be doable on the P drive due to locking issues with Windows SMB shares (unable to commit changes to the database) . To get around this, store the database file on the local computer (i.e. on Desktop) for the duration of the tasks and move to P drive after completion!

In this part you will practice doing INSERT queries. The base task does not need to be implemented in your C code! You can use the “Execute SQL” feature in the SQLite browser.

Requirements
  • Write down all the SQL queries you did (just as a text document)! Show the queries once all are performed and show the database. There should be a total of 5 queries!
  • ID values used for the primary key of each table  (students.id , subjects.id , declarations.id ) must not be hardcoded into your queries! They are generated automatically by the database managem system (those attributes have auto increment as a part of their database design with each table having a corresponding sequence generator).
  • Perform the following insertions
    • Add yourself as a student into the database.
    • Add a subject that you have completed into the database.
    • Add 3 declarations to yourself. One to the subject that you just created and two more for subjects in the database.
  • Note: grades and eID do not need to be real.

Once data is added, present the queries you performed and show you database in SQLite Browser.

Lab task 2: Retrieving data

For this lab task, you will need to write queries to retrieve data from the database. All queries need to be performed in the C program you write. You have 3 sample codes provided in the beginning of this page. Pick the one you like the most and use that as a template to writing your own queries.

Task is divided into three separately graded parts.

Requirements
  • All queries for the part you intend to present must be completed. Each part is graded individually..
  • Data must be completely prepared on the database engine side – any kind of calculation (sums, averages, max etc) must be written into the SQL query. No calculations in your C code!
  • All relevant data must be printed. Do not print id values (e.g. subject.id, declaration.id, ..)
  • Queries can either be performed through a menu selectively or can just execute one after another. Up to you.
  • All resources must be released by the end of the program, no leaks – check with valgrind.
Task 2 gradable part 1

First part contains basic SELECT queries.

Query 1: Find all subjects that are less than 6 credits

Query 2: Find all subjects that have an exam, ordered by the number of credits from lowest to highest.

Note: Query 1 and 2 are independent of each other. Do not tie them together.

Task 2 gradable part 2

Second part contains more complex SELECT queries where you have to use JOIN and double JOIN to merge data from multiple tables together through their PK-FK key-pairs.

Query 1: Find all grades for students whose first name is “Marko”

Query 2: Find all classes you’ve completed, show with grades, starting from the highest grade.

Note: Query 1 and 2 are independent of each other. Do not tie them together.

Task 2 gradable part 3

In this part we will practice data aggregation for calculating for our SELECT queries. We will use both JOIN, GROUP BY and additional functions for calculating results.

Query: Find each students total earned credits and average grade

Advanced task 1: Adding yourself to the database more reliably

In the base task 1, likely you added data by just observing the values and hardcoding everything in. In a production database, this wouldn’t be feasible.

Information

For a better solution, we have 2 different paths we can take.

SQL  supports subqueries – queries that are a part of another query. For an example, we can embed a SELECT query inside of an INSERT query.

For an example, if we want to add another car for Marko Mets  in the sample database, we could execute

This of course isn’t perfect since there could be two people named Marko mets. However it will be good enough for the lab.

A better approach would be to insert ourselves as students first. Then get the ID value from that insertion using the query SELECT last_insert_rowid();  This will give us the ID value of that last insertion.

In a real database scenario this would be tied together with transactions so that there wouldn’t be race conditions (other people adding data to database before you get your id). However this is again unnecessary for us since we are using SQLite, which is an offline file based database.

Requirements
  • Write the insertion to the database as a part of your C program code.
  • If this code is executed again, make sure that a duplicate wouldn’t be created. Check before adding using a unique value (e.g. eID).
  • You must use the auto-generated ID value value. Hardcoding any IDs into the query is not permitted.
    • You can use either one of the proposed approaches from the information section.

Advanced task 2 : Changing existing data

In this task, you will practice UPDATE queries, where you will first have to identify the line and then change its attribute(s). You will need to generate and add uni-id’s to all students.

Requirements
  • Generate all the students Uni-ID’s.
  • Uni-IDs must be added to the database using UPDATE statements. Populate the uni_id attribute.
  • Your code must work for any number of students
  • You must also resolve conflicts if a Uni-ID already exists. Conflict resolution can (and should) be done in C.

After this class, you should

Additional materials