This exercise introduces students to the fundamentals of databases; specifically, the concept of tables and how to query them. Joins are also covered. Although this exercise doesn’t specifically involve hacking, it is a prerequisite for exploits involving SQL injection.
- A Kali Linux instance:
- No extra programs beyond those provided by a clean install are needed to complete this exercise.
- A SQLite database instance; I prepared and used this one: DatabasePractice.sqlite.
Background Info to Provide Students
I prepared a problem set document with examples and exercises for students to work through on their own. The LaTex source of that problem set is available here as DatabaseIntroProblemSet1.tex, and the PDF output is available here as DatabaseIntroProblemSet1.pdf.
Students were given the PDF file, asked to work through the examples in the provided SQLite database, then work together to solve the problems and record their solutions in a separate text file.
- Write a SQL query to get the names of nations on the Asian continent.
- Write a SQL query to get the names of nations that are in Europe or have a population greater than 300 million.
- Write a SQL query to get the names of nations with a population less than 55 million or with a population greater than 200 million.
- Write a SQL query to get the names of everyone who is a student.
- Write a SQL query to get the names of everyone who comes from a nation with a population of 90 million or more.
- Write a SQL query to get the names and nation of everyone who lives in Africa.
- Write a SQL query to get the names and nation of everyone who lives in Asia or Europe.
- Write a SQL query to get the name of everyone in Tanzania who is not a student.
Students worked through the first four problems with relative ease and without instructor assistance. As expected, JOINs proved to be trickier for them to incorporate in their answers, as they initially resorted to selecting on the numeric IDs of the nations themselves rather than on the nations’ names. Despite that being a valid approach, the goal is for them to acclimate to the concept of joining so that they can understand and work with a broad range of queries in the future.