Creating SQLite: Multiple-choice quiz application

Creating SQLite: Multiple-choice quiz application
Creating SQLite: Multiple-choice quiz application

Most of us have thorough knowledge about the various concepts used in Android and Java, but there is no strategy to measure or exhibit such skills unless you have a few projects. The best way to get a hold of the concepts is to use them in your projects, the more you explore, the more you learn.

In theoretical concepts, you don’t stumble across any concept as you don’t implement them while when you work in the Android Studio, you will encounter numerous bugs. Don’t worry; you will be able to eradicate them all, eventually. This is the proper channel of learning. In the previous two articles, we have discussed the procedure for creating a Calculator App and a Tic-Tac-Toe App. Both, the projects were logic-driven and we didn’t work on any database, while in the real-world, majority of applications need a database.

A database is required for storing and processing the data. Earlier, databases were based on queries, but nowadays, we have databases that are run via procedural languages also. There are numerous in-built functions that allow manipulation of data efficiently.

A database can be of numerous types, depending upon the scalability of your project you can pick the most suitable one. A database can be connected to a cloud, making it accessible worldwide or just a device-hosted database, which is restricted to the host device only. For creating a Quiz App, we prefer an SQLite database; it is a host-driven database and can’t be accessed elsewhere.

SQLite is widely known as an in-process software library that serves as a serverless, self-contained, zero-configurationally, transactional SQL database engine. SQLite is preferred over other databases as it is zero-configured, which implies that there are no pre-requisites for configuring it in your system. The source code for SQLite is open-source and is publically accessible to the growth of developers.

In contrast to the other databases, the SQLite engine is not a standalone process. Developers are allowed to link it statically or dynamically in coherence with their requirements within the application. SQLite makes direct access to the storage files. The demand for SQLite is exponentially increasing, the convince provide by this engine leads to this growing demand. However, the size is restricted but the functions and features are quite extended.


Top reasons for picking SQLite

  • SQLite is written in ANSI-C and provides a simplified and user-friendly API.
  • SQLite does not call for any segregated server for data manipulation or storage or any system to operate (serverless).
  • SQLite comes with zero-configuration costs; this implies there is no pre-required setup or administration needed.
  • SQLite is very compact and lightweight, less than 400KiB with full configuration. This can be reduced to even less than 250KiB by omitting the optional features.
  • SQLite is a self-contained database. Therefore, you don’t need to add any external dependencies.
  • The entire SQLite database can be preserved in a single cross-platform disk file.
  • SQLite transactions follow the ACID paradigm; this allows simultaneous closed transactions from multiple processes or threads.
  • SQLite supports a wide range of query language features present in SQL92 (SQL2) standard.
  • SQLite can be set-up on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT) machines.

Phase 1: Designing the layouts

For the quiz app, we design a home screen that displays the latest high-score of the user and displays the various categories of quizzes that the user may pick and then begin the Quiz. For every question, the second layout gets recycled, from the list of questions the TextView is set according to the question number, and a stopwatch is reset after each question, the user can view his score, the number of questions, the category is chosen, difficult level and so on. You can append additional parameters according to your requirement. You can even alter the colours used by changing the hex code in the attributes.

The XML code given below is for designing the start screen with the optional sections and the high score:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="@color/colorBackground"
    android:padding="16dp"
    tools:context=".StarttScreen">
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="30dp"
        android:text="Coding Ninjas Quiz"
        android:textColor="@android:color/black"
        android:textSize="35sp" />
    <TextView
        android:id="@+id/text_view_highscore"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@id/button_start_quiz"
        android:layout_centerHorizontal="true"
        android:layout_marginBottom="32dp"
        android:text="Highscore: 0"
        android:textSize="20sp" />
    <Button
        android:id="@+id/button_start_quiz"
        android:layout_width="200dp"
        android:layout_height="wrap_content"
        android:layout_centerInParent="true"
        android:background="#303F9F"
        android:textSize="24sp"
        android:textColor="#FFFFFF"
        android:text="Start Quiz" />
    <Spinner
        android:id="@+id/spinner_category"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignStart="@+id/button_start_quiz"
        android:layout_below="@+id/button_start_quiz"
        android:layout_marginTop="16dp"
        android:layout_alignLeft="@+id/button_start_quiz" />
    <Spinner
        android:id="@+id/spinner_difficulty"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignStart="@+id/button_start_quiz"
        android:layout_below="@+id/spinner_category"
        android:layout_marginTop="16dp"
        android:layout_alignLeft="@+id/button_start_quiz" />
</RelativeLayout>
Image Source: Screenshot from Android Emulator

The XML code given below is for designing the question screen with timer and the instantaneous score:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">


<TextView
    android:id="@+id/text_view_score"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:freezesText="true"
    android:text="Score: 0"
    android:textColor="@android:color/black" />
<TextView
    android:id="@+id/text_view_question_count"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@id/text_view_score"
    android:freezesText="true"
    android:text="Question: 1/x"
    android:textColor="@android:color/black" />
<TextView
    android:id="@+id/text_view_category"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@id/text_view_question_count"
    android:freezesText="true"
    android:text="Category: "
    android:textColor="@android:color/black" />
<TextView
    android:id="@+id/text_view_difficulty"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@id/text_view_category"
    android:freezesText="true"
    android:text="Difficulty: "
    android:textColor="@android:color/black" />
<TextView
    android:id="@+id/text_view_countdown"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentEnd="true"
    android:freezesText="true"
    android:text="00:30"
    android:textColor="@android:color/black"
    android:textSize="40sp"
    android:layout_alignParentRight="true" />
<TextView
    android:id="@+id/text_view_question"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:background="#42C6D5"
    android:layout_above="@id/radio_group"
    android:layout_marginBottom="16dp"
    android:freezesText="true"
    android:text="Which of these is a high -level programming language?"
    android:textAlignment="center"
    android:textColor="@android:color/black"
    android:textSize="20sp"
    android:gravity="center_horizontal" />
<RadioGroup
    android:id="@+id/radio_group"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_centerVertical="true">
    <RadioButton
        android:id="@+id/radio_button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:freezesText="true"
        android:text="Java" />
    <RadioButton
        android:id="@+id/radio_button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:freezesText="true"
        android:text="JavaScript" />
    <RadioButton
        android:id="@+id/radio_button3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:freezesText="true"
        android:text="HTML" />
</RadioGroup>
<Button
    android:id="@+id/button_confirm_next"
    android:layout_width="match_parent"
    android:background="#000000"
    android:layout_height="wrap_content"
    android:layout_below="@id/radio_group"
    android:layout_marginTop="16dp"
    android:freezesText="true"
    android:textSize="20dp"
    android:textColor="@android:color/holo_blue_bright"
    android:text="Confirm" />
</RelativeLayout>
Image Source: Screenshot from Android Emulator

FAQs on the above mentioned UI designs:

1. What is the difference between <RadioGroup> and <RadioButton> tag?
A radio button is a dual-configuration button that can be marked checked or unchecked, indicating the assigned subjects. If the user clicks on an unchecked radio button, it becomes checked and vice-versa. Once checked, the user is not allowed to uncheck a radio button.

Radio buttons are embraced collectively in a RadioGroup, this confines the user to cheque any one button from the given radio group. It is used if you want to limit the selected option to just one, among the options provided. Radio buttons are widely used in taking the consumers gender while creating registration forms. If one of the radio buttons is checked, automatically all the radio buttons present in that radio group are unchecked. Therefore, they are referred as multiple exclusion set.

2. What is the difference between a Radio Button and a Check Box?

  • Radio buttons restrict the user to pick any one of the options from a given radio group, whereas check boxes allow a user to select one or multiple options to be selected simultaneously from a given set.
  • Radio button work with dual-configuration, namely- True & False. Checkbox work with triple-configuration, namely- Checked, unchecked & indeterminate.
  • The radio button is a single control unit, while the check box is a multiple control unit.
  • Radio buttons are indicated by tiny discs. In contrast, checkboxes are present as tiny squares on the user-interface.
  • Radio buttons are used when there are only two possible outcomes such as the nationality of a person is Indian or not, whereas, Checkboxes are used when there can be multiple true possibilities such as a person can know English, Hindi, Japanese all at the same time.

3. What is the role of an android:freezesText=”true” attribute?
The attribute:android:freezesText=”true” is also a dual-state Boolean attribute. If it is set to true, it preserves the state of the TextVIew to the last completed text. Many a time, when we change the screen orientation from landscape mode to portrait mode or vice-versa, the text entered by the user in the TextView is lost. This happens even if the fragment is traced from the Back-stack. Therefore, the freezesText is set to true, to avoid the loss of entered text during any of the two situations.

By default this attribute is disabled for a TextView, while for an EditText it is always enabled, irrespective of the Boolean value of the attribute. The definition from developers.Google states, “If set, the text view will include its current complete text inside of its frozen icicle in addition to meta-data such as the current cursor position”. We have used this in our Quiz App so that in case the user rotates the screen, his score or other valuable information remains preserved.

blog banner 1

4. What is the difference between MarginStart/MarginEnd and MarginLeft/MarginRight?
As the growth of Natural language processing (NLP) began, apps developers began to develop multi-lingual apps or apps in various regional languages. Therefore, a slight modification was introduced in the margin attribute of the Android Studio.

Android supports RTL layouts from API 17 and newer, which means from Android 4.2 (Jelly Bean). When we design layouts for supporting both RTL and LTR then we cannot use layout_marginleft and layout_marginRight.In such cases, we have to use layout_marginstart and layout_maginend.

When we use the attribute marginLeft, it refers to the left (fixed) margin of the element in the LTR view. Whereas, if you use marginStart, this margin applies to the start(relative). In the case of the LTR, the view is the left of the element, while for RLT is the right of the element.

Image indicates the start of the screen with respect to LTR and RLT
Image source: StackOverflow

Phase 2: Java Class For The Quiz App

Step 1.Write the complete Java code for the starting screen:

import android.content.Intent;
import android.content.SharedPreferences;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.Spinner;
import android.widget.TextView;
import java.util.List;
public class StartingScreenActivity extends AppCompatActivity {
private static final int REQUEST_CODE_QUIZ = 1;
public static final String EXTRA_CATEGORY_ID = "extraCategoryID";
public static final String EXTRA_CATEGORY_NAME = "extraCategoryName";
public static final String EXTRA_DIFFICULTY = "extraDifficulty";
public static final String SHARED_PREFS = "sharedPrefs";
public static final String KEY_HIGHSCORE = "keyHighscore";
private TextView textViewHighscore;
private Spinner spinnerCategory;
private Spinner spinnerDifficulty;
private int highscore;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_starting_screen);
textViewHighscore = findViewById(R.id.text_view_highscore);
spinnerCategory = findViewById(R.id.spinner_category);
spinnerDifficulty = findViewById(R.id.spinner_difficulty);
loadCategories();
loadDifficultyLevels();
loadHighscore();
Button buttonStartQuiz = findViewById(R.id.button_start_quiz);
buttonStartQuiz.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
startQuiz();
}
});
}
private void startQuiz() {
Category selectedCategory = (Category) spinnerCategory.getSelectedItem();
int categoryID = selectedCategory.getId();
String categoryName = selectedCategory.getName();
String difficulty = spinnerDifficulty.getSelectedItem().toString();
Intent intent = new Intent(StartingScreenActivity.this, QuizActivity.class);
intent.putExtra(EXTRA_CATEGORY_ID, categoryID);
intent.putExtra(EXTRA_CATEGORY_NAME, categoryName);
intent.putExtra(EXTRA_DIFFICULTY, difficulty);
startActivityForResult(intent, REQUEST_CODE_QUIZ);
}
@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
if (requestCode == REQUEST_CODE_QUIZ) {
if (resultCode == RESULT_OK) {
int score = data.getIntExtra(QuizActivity.EXTRA_SCORE, 0);
if (score > highscore) {
updateHighscore(score);
}
}
}
}
private void loadCategories() {
QuizDbHelper dbHelper = QuizDbHelper.getInstance(this);
List<Category> categories = dbHelper.getAllCategories();
ArrayAdapter<Category> adapterCategories = new ArrayAdapter<>(this,
android.R.layout.simple_spinner_item, categories);
adapterCategories.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinnerCategory.setAdapter(adapterCategories);
}
private void loadDifficultyLevels() {
String[] difficultyLevels = Question.getAllDifficultyLevels();
ArrayAdapter<String> adapterDifficulty = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, difficultyLevels);
adapterDifficulty.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinnerDifficulty.setAdapter(adapterDifficulty);
}
private void loadHighscore() {
SharedPreferences prefs = getSharedPreferences(SHARED_PREFS, MODE_PRIVATE);
highscore = prefs.getInt(KEY_HIGHSCORE, 0);
textViewHighscore.setText("Highscore: " + highscore);
}
private void updateHighscore(int highscoreNew) {
highscore = highscoreNew;
textViewHighscore.setText("Highscore: " + highscore);
SharedPreferences prefs = getSharedPreferences(SHARED_PREFS, MODE_PRIVATE);
SharedPreferences.Editor editor = prefs.edit();
editor.putInt(KEY_HIGHSCORE, highscore);
editor.apply();
}
}

Step 2. Create a Java Class for displaying questions one by one:

import android.content.Intent;
import android.content.res.ColorStateList;
import android.graphics.Color;
import android.os.CountDownTimer;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.TextView;
import android.widget.Toast;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Locale;
public class QuizActivity extends AppCompatActivity {
public static final String EXTRA_SCORE = "extraScore";
private static final long COUNTDOWN_IN_MILLIS = 30000;
private static final String KEY_SCORE = "keyScore";
private static final String KEY_QUESTION_COUNT = "keyQuestionCount";
private static final String KEY_MILLIS_LEFT = "keyMillisLeft";
private static final String KEY_ANSWERED = "keyAnswered";
private static final String KEY_QUESTION_LIST = "keyQuestionList";
private TextView textViewQuestion;
private TextView textViewScore;
private TextView textViewQuestionCount;
private TextView textViewCategory;
private TextView textViewDifficulty;
private TextView textViewCountDown;
private RadioGroup rbGroup;
private RadioButton rb1;
private RadioButton rb2;
private RadioButton rb3;
private Button buttonConfirmNext;
private ColorStateList textColorDefaultRb;
private ColorStateList textColorDefaultCd;
private CountDownTimer countDownTimer;
private long timeLeftInMillis;
private ArrayList<Question> questionList;
private int questionCounter;
private int questionCountTotal;
private Question currentQuestion;
private int score;
private boolean answered;
private long backPressedTime;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_quiz);
textViewQuestion = findViewById(R.id.text_view_question);
textViewScore = findViewById(R.id.text_view_score);
textViewQuestionCount = findViewById(R.id.text_view_question_count);
textViewCategory = findViewById(R.id.text_view_category);
textViewDifficulty = findViewById(R.id.text_view_difficulty);
textViewCountDown = findViewById(R.id.text_view_countdown);
rbGroup = findViewById(R.id.radio_group);
rb1 = findViewById(R.id.radio_button1);
rb2 = findViewById(R.id.radio_button2);
rb3 = findViewById(R.id.radio_button3);
buttonConfirmNext = findViewById(R.id.button_confirm_next);
textColorDefaultRb = rb1.getTextColors();
textColorDefaultCd = textViewCountDown.getTextColors();
Intent intent = getIntent();
int categoryID = intent.getIntExtra(StartingScreenActivity.EXTRA_CATEGORY_ID, 0);
String categoryName = intent.getStringExtra(StartingScreenActivity.EXTRA_CATEGORY_NAME);
String difficulty = intent.getStringExtra(StartingScreenActivity.EXTRA_DIFFICULTY);
textViewCategory.setText("Category: " + categoryName);
textViewDifficulty.setText("Difficulty: " + difficulty);
if (savedInstanceState == null) {
QuizDbHelper dbHelper = QuizDbHelper.getInstance(this);
questionList = dbHelper.getQuestions(categoryID, difficulty);
questionCountTotal = questionList.size();
Collections.shuffle(questionList);
showNextQuestion();
} else {
questionList = savedInstanceState.getParcelableArrayList(KEY_QUESTION_LIST);
questionCountTotal = questionList.size();
questionCounter = savedInstanceState.getInt(KEY_QUESTION_COUNT);
currentQuestion = questionList.get(questionCounter - 1);
score = savedInstanceState.getInt(KEY_SCORE);
timeLeftInMillis = savedInstanceState.getLong(KEY_MILLIS_LEFT);
answered = savedInstanceState.getBoolean(KEY_ANSWERED);
if (!answered) {
startCountDown();
} else {
updateCountDownText();
showSolution();
}
}
buttonConfirmNext.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
if (!answered) {
if (rb1.isChecked() || rb2.isChecked() || rb3.isChecked()) {
checkAnswer();
} else {
Toast.makeText(QuizActivity.this, "Please select an answer", Toast.LENGTH_SHORT).show();
}
} else {
showNextQuestion();
}
}
});
}
private void showNextQuestion() {
rb1.setTextColor(textColorDefaultRb);
rb2.setTextColor(textColorDefaultRb);
rb3.setTextColor(textColorDefaultRb);
rbGroup.clearCheck();
if (questionCounter < questionCountTotal) {
currentQuestion = questionList.get(questionCounter);
textViewQuestion.setText(currentQuestion.getQuestion());
rb1.setText(currentQuestion.getOption1());
rb2.setText(currentQuestion.getOption2());
rb3.setText(currentQuestion.getOption3());
questionCounter++;
textViewQuestionCount.setText("Question: " + questionCounter + "/" + questionCountTotal);
answered = false;
buttonConfirmNext.setText("Confirm");
timeLeftInMillis = COUNTDOWN_IN_MILLIS;
startCountDown();
} else {
finishQuiz();
}
}
private void startCountDown() {
countDownTimer = new CountDownTimer(timeLeftInMillis, 1000) {
@Override
public void onTick(long millisUntilFinished) {
timeLeftInMillis = millisUntilFinished;
updateCountDownText();
}
@Override
public void onFinish() {
timeLeftInMillis = 0;
updateCountDownText();
checkAnswer();
}
}.start();
}
private void updateCountDownText() {
int minutes = (int) (timeLeftInMillis / 1000) / 60;
int seconds = (int) (timeLeftInMillis / 1000) % 60;
String timeFormatted = String.format(Locale.getDefault(), "%02d:%02d", minutes, seconds);
textViewCountDown.setText(timeFormatted);
if (timeLeftInMillis < 10000) {
textViewCountDown.setTextColor(Color.RED);
} else {
textViewCountDown.setTextColor(textColorDefaultCd);
}
}
private void checkAnswer() {
answered = true;
countDownTimer.cancel();
RadioButton rbSelected = findViewById(rbGroup.getCheckedRadioButtonId());
int answerNr = rbGroup.indexOfChild(rbSelected) + 1;
if (answerNr == currentQuestion.getAnswerNr()) {
score++;
textViewScore.setText("Score: " + score);
}
showSolution();
}
private void showSolution() {
rb1.setTextColor(Color.RED);
rb2.setTextColor(Color.RED);
rb3.setTextColor(Color.RED);
switch (currentQuestion.getAnswerNr()) {
case 1:
rb1.setTextColor(Color.GREEN);
textViewQuestion.setText("Answer 1 is correct");
break;
case 2:
rb2.setTextColor(Color.GREEN);
textViewQuestion.setText("Answer 2 is correct");
break;
case 3:
rb3.setTextColor(Color.GREEN);
textViewQuestion.setText("Answer 3 is correct");
break;
}
if (questionCounter < questionCountTotal) {
buttonConfirmNext.setText("Next");
} else {
buttonConfirmNext.setText("Finish");
}
}
private void finishQuiz() {
Intent resultIntent = new Intent();
resultIntent.putExtra(EXTRA_SCORE, score);
setResult(RESULT_OK, resultIntent);
finish();
}
@Override
public void onBackPressed() {
if (backPressedTime + 2000 > System.currentTimeMillis()) {
finishQuiz();
} else {
Toast.makeText(this, "Press back again to finish", Toast.LENGTH_SHORT).show();
}
backPressedTime = System.currentTimeMillis();
}
@Override
protected void onDestroy() {
super.onDestroy();
if (countDownTimer != null) {
countDownTimer.cancel();
}
}
@Override
protected void onSaveInstanceState(Bundle outState) {
super.onSaveInstanceState(outState);
outState.putInt(KEY_SCORE, score);
outState.putInt(KEY_QUESTION_COUNT, questionCounter);
outState.putLong(KEY_MILLIS_LEFT, timeLeftInMillis);
outState.putBoolean(KEY_ANSWERED, answered);
outState.putParcelableArrayList(KEY_QUESTION_LIST, questionList);
}
}

PHASE 3: Creating the SQLite Database


For managing the DataBase you will have to import the SQLiteDatabase class and then you can sync it with your Quiz class by referring to the code mentioned below:

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.codinginflow.myawesomequiz.QuizContract.*;
import java.util.ArrayList;
import java.util.List;
public class QuizDbHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "MyAwesomeQuiz.db";
private static final int DATABASE_VERSION = 1;
private static QuizDbHelper instance;
private SQLiteDatabase db;
private QuizDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public static synchronized QuizDbHelper getInstance(Context context) {
if (instance == null) {
instance = new QuizDbHelper(context.getApplicationContext());
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
this.db = db;
final String SQL_CREATE_CATEGORIES_TABLE = "CREATE TABLE " +
CategoriesTable.TABLE_NAME + "( " +
CategoriesTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
CategoriesTable.COLUMN_NAME + " TEXT " +
")";
final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
QuestionsTable.TABLE_NAME + " ( " +
QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
QuestionsTable.COLUMN_QUESTION + " TEXT, " +
QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
QuestionsTable.COLUMN_CATEGORY_ID + " INTEGER, " +
"FOREIGN KEY(" + QuestionsTable.COLUMN_CATEGORY_ID + ") REFERENCES " +
CategoriesTable.TABLE_NAME + "(" + CategoriesTable._ID + ")" + "ON DELETE CASCADE" +
")";
db.execSQL(SQL_CREATE_CATEGORIES_TABLE);
db.execSQL(SQL_CREATE_QUESTIONS_TABLE);
fillCategoriesTable();
fillQuestionsTable();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + CategoriesTable.TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + QuestionsTable.TABLE_NAME);
onCreate(db);
}
@Override
public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);
db.setForeignKeyConstraintsEnabled(true);
}
private void fillCategoriesTable() {
Category c1 = new Category("Programming");
addCategory(c1);
Category c2 = new Category("Geography");
addCategory(c2);
Category c3 = new Category("Math");
addCategory(c3);
}
private void addCategory(Category category) {
ContentValues cv = new ContentValues();
cv.put(CategoriesTable.COLUMN_NAME, category.getName());
db.insert(CategoriesTable.TABLE_NAME, null, cv);
}
private void fillQuestionsTable() {
Question q1 = new Question("Programming, Easy: A is correct",
"A", "B", "C", 1,
Question.DIFFICULTY_EASY, Category.PROGRAMMING);
addQuestion(q1);
Question q2 = new Question("Geography, Medium: B is correct",
"A", "B", "C", 2,
Question.DIFFICULTY_MEDIUM, Category.GEOGRAPHY);
addQuestion(q2);
Question q3 = new Question("Math, Hard: C is correct",
"A", "B", "C", 3,
Question.DIFFICULTY_HARD, Category.MATH);
addQuestion(q3);
Question q4 = new Question("Math, Easy: A is correct",
"A", "B", "C", 1,
Question.DIFFICULTY_EASY, Category.MATH);
addQuestion(q4);
Question q5 = new Question("Non existing, Easy: A is correct",
"A", "B", "C", 1,
Question.DIFFICULTY_EASY, 4);
addQuestion(q5);
Question q6 = new Question("Non existing, Medium: B is correct",
"A", "B", "C", 2,
Question.DIFFICULTY_MEDIUM, 5);
addQuestion(q6);
}
private void addQuestion(Question question) {
ContentValues cv = new ContentValues();
cv.put(QuestionsTable.COLUMN_QUESTION, question.getQuestion());
cv.put(QuestionsTable.COLUMN_OPTION1, question.getOption1());
cv.put(QuestionsTable.COLUMN_OPTION2, question.getOption2());
cv.put(QuestionsTable.COLUMN_OPTION3, question.getOption3());
cv.put(QuestionsTable.COLUMN_ANSWER_NR, question.getAnswerNr());
cv.put(QuestionsTable.COLUMN_DIFFICULTY, question.getDifficulty());
cv.put(QuestionsTable.COLUMN_CATEGORY_ID, question.getCategoryID());
db.insert(QuestionsTable.TABLE_NAME, null, cv);
}
public List<Category> getAllCategories() {
List<Category> categoryList = new ArrayList<>();
db = getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM " + CategoriesTable.TABLE_NAME, null);
if (c.moveToFirst()) {
do {
Category category = new Category();
category.setId(c.getInt(c.getColumnIndex(CategoriesTable._ID)));
category.setName(c.getString(c.getColumnIndex(CategoriesTable.COLUMN_NAME)));
categoryList.add(category);
} while (c.moveToNext());
}
c.close();
return categoryList;
}
public ArrayList<Question> getAllQuestions() {
ArrayList<Question> questionList = new ArrayList<>();
db = getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM " + QuestionsTable.TABLE_NAME, null);
if (c.moveToFirst()) {
do {
Question question = new Question();
question.setId(c.getInt(c.getColumnIndex(QuestionsTable._ID)));
question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
question.setCategoryID(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_CATEGORY_ID)));
questionList.add(question);
} while (c.moveToNext());
}
c.close();
return questionList;
}
public ArrayList<Question> getQuestions(int categoryID, String difficulty) {
ArrayList<Question> questionList = new ArrayList<>();
db = getReadableDatabase();
String selection = QuestionsTable.COLUMN_CATEGORY_ID + " = ? " +
" AND " + QuestionsTable.COLUMN_DIFFICULTY + " = ? ";
String[] selectionArgs = new String[]{String.valueOf(categoryID), difficulty};
Cursor c = db.query(
QuestionsTable.TABLE_NAME,
null,
selection,
selectionArgs,
null,
null,
null
);
if (c.moveToFirst()) {
do {
Question question = new Question();
question.setId(c.getInt(c.getColumnIndex(QuestionsTable._ID)));
question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
question.setCategoryID(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_CATEGORY_ID)));
questionList.add(question);
} while (c.moveToNext());
}
c.close();
return questionList;
}
}

Try to add more parameters to the application to make your project customised and specific. You can even create report cards or add reminders and push notifications so that the user is notified about the due quizzes and his test scores. Mix and match the attributes and create your own UI designs for better understanding.

The more you explore, the more you grow! To explore our courses, visit our website.

By Vanshika Singolia