-1

I have three tables created within my SQLite database to limit redundancy. I understand these methods are a little "behind", but these were the requirements for such an assignment. The tables are created as follows:

 db=openOrCreateDatabase("STUDENTGRADES", Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS STUDENT_TABLE(studentid VARCHAR, fname VARCHAR, lname VARCHAR);");
    db.execSQL("CREATE TABLE IF NOT EXISTS CLASS_TABLE(studentid VARCHAR, classid VARCHAR PRIMARY KEY UNIQUE, classname VARCHAR UNIQUE);");
    db.execSQL("CREATE TABLE IF NOT EXISTS GRADE_TABLE(studentid VARCHAR, classid VARCHAR, classname VARCHAR, pointgrade INTEGER, lettergrade VARCHAR);");

I then insert data into each of these tables individually, however am looking for a method to JOIN the tables into one with the following tags: Student ID, Student First Name, Student Last Name, Class ID, Class Name, Point Grade, and Letter Grade in which I can just use a singular table for the rest of the program

Thank you!

EDIT:

    ContentValues contentValues2 = new ContentValues();
            contentValues2.put("classid", classid.getText().toString());
            contentValues2.put("classname", classname.getText().toString());
            long result2 = db.insertWithOnConflict("CLASS_TABLE", "classid", contentValues2, SQLiteDatabase.CONFLICT_IGNORE);

EDIT, Here is the entire "Add"

 add.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            if(fname.getText().toString().trim().length()==0||
                    lname.getText().toString().trim().length()==0 || studentid.getText().toString().trim().length()==0)
            {
                showMessage("Error", "Please enter First & Last Name and Student ID");
                return;
            }


            ContentValues contentValues = new ContentValues();
            contentValues.put("studentid", studentid.getText().toString());
            contentValues.put("fname", fname.getText().toString());
            contentValues.put("lname", lname.getText().toString());
            long result =  db.insertWithOnConflict("STUDENT_TABLE", "studentid", contentValues, SQLiteDatabase.CONFLICT_IGNORE);

            if (result == -1) {
                showMessage("Error", "This Name Data entry already exists");
            }


            ContentValues contentValues2 = new ContentValues();
            contentValues2.put("classid", classid.getText().toString());
            contentValues2.put("classname", classname.getText().toString());
            long result2 = db.insertWithOnConflict("CLASS_TABLE", "classid", contentValues2, SQLiteDatabase.CONFLICT_IGNORE);


            if (result2 == -1) {
                showMessage("Error", "This Class Data entry already exists");
            }

            ContentValues contentValues3 = new ContentValues();
            contentValues3.put("studentid", studentid.getText().toString());
            contentValues3.put("classid", classid.getText().toString());
            contentValues3.put("pointgrade", pointgrade.getText().toString());
            contentValues3.put("lettergrade", lettergrade.getText().toString());
            long result3 =   db.insertWithOnConflict("GRADE_TABLE", "studentid", contentValues3, SQLiteDatabase.CONFLICT_IGNORE);

            if (result3 == -1) {
                showMessage("Error", "This Grade Data entry already exists");
            }

            if (result != -1 && result2 != -1 && result3 != -1)
                showMessage("Success", "Student Record added successfully");

            clearText();


        }
    });

DELETE FUNCTION:

 delete.setOnClickListener(new OnClickListener() {

        Cursor csr = db.query("GRADE_TABLE JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid",null,null,null,null,null,null);

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            if(studentid.getText().toString().trim().length()==0 || classid.getText().toString().trim().length()==0)
            {
                showMessage("Error", "Please enter Student and Class ID ");
                return;
            }
            Cursor csr=db.rawQuery("SELECT * FROM GRADE_TABLE WHERE studentid='"+studentid.getText()+"' AND classid='"+classid.getText()+"'", null);
            if(c.moveToFirst())
            {
                db.execSQL("DELETE FROM GRADE_TABLE WHERE studentid='"+studentid.getText()+"' AND classid='"+studentid.getText()+"'");
                showMessage("Success", "Record Deleted");
            }
            else
            {
                showMessage("Error", "Invalid First and Last Name or Student ID");
            }
            clearText();
        }
    });
SE Optimix
  • 23
  • 1
  • 7

1 Answers1

1

I'd suggest that your schema is only going to cause you issues as :-

  • The CLASS_TABLE would only allow a single student per class as other than the student id the other columns have the UNIQUE constraint.

  • The GRADE_TABLE is introducing redundancy, rather than limiting it, by storing the class name.

I'd also suggest that you concept of creating some master all columns combined table will cause you issues (e.g. to obtain the overallgrade results shown below would likely require a whole lot of coding to accomplish using such a combined table as the source)

I'd suggest a subtle change to the schema e.g. :-

CREATE TABLE IF NOT EXISTS STUDENT_TABLE (studentid TEXT, fname TEXT, lname TEXT);
CREATE TABLE IF NOT EXISTS CLASS_TABLE(classid TEXT PRIMARY KEY, classname TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS GRADE_TABLE (studentid TEXT, classid TEXT, pointgrade INTEGER, lettergrade TEXT);

Let's say you then used the following to insert some data :-

INSERT INTO STUDENT_TABLE 
    VALUES('00001','Fred','Smith'),('00010','Mary','Thomas'),('00910','Angela','Jones')
;

INSERT INTO CLASS_TABLE VALUES('001','English'),('101','Mathematics'),('201','Chemistry');

INSERT INTO GRADE_TABLE VALUES
    ('00001','001',99,'A'), -- Fred Smith has 99 point grade as an A in English
    ('00001','101',25,'F'), -- Fred Smith has 25 point grade as an F on Mathematics
    ('00010','201',76,'B'), -- Angela Jones 76 a B in Chemistry
    ('00910','101',50,'C'), 
    ('00910','201',63,'C'),
    ('00910','001',89,'A')
;

The resultant tables would then be :-

STUDENT_TABLE :-

enter image description here

CLASS_TABLE :-

enter image description here

GRADE_TABLE

enter image description here

You could then do the magic with joins, say to produce a list of students overall points grade (all points per student added for all their classes) along with the classes they are in e.g. :-

SELECT fname,
    lname, 
    sum(pointgrade) AS overallgrade, 
    group_concat(classname,' - ') AS classes
FROM GRADE_TABLE 
    JOIN STUDENT_TABLE ON GRADE_TABLE.studentid = STUDENT_TABLE.studentid
    JOIN CLASS_TABLE ON GRADE_TABLE.classid = CLASS_TABLE.classid
GROUP BY STUDENT_TABLE.studentid
ORDER BY overallgrade DESC
;

Which would result in :-

enter image description here

  • Note sum and group_contact are aggregate functions explained at SQL As Understood By SQLite - Aggregate Functions, they will work on the GROUPED results.
    • If there is no GROUP BY then you will get **1* result row for all rows.
    • However as GROUP BY STUDENT_TABLE.studentid is used a result is returned for each GROUP i.e. student
    • note GROUP BY GRADE_TABLE.studentid would have the same result.

To fully answer your question, but with the suggested schema then the following SQL would join accordingly :-

SELECT 
    STUDENT_TABLE.studentid, 
    fname, 
    lname, 
    CLASS_TABLE.classid, 
    classname, 
    pointgrade, 
    lettergrade
FROM GRADE_TABLE 
    JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid
      JOIN CLASS_TABLE ON  CLASS_TABLE.classid = GRADE_TABLE.classid;

This would result in the following :-

enter image description here

Based upon the original code and suggested schema and uitilising a class available here Are there any methods that assist with resolving common SQLite issues?

NOTE the above code will only work once, as subsequent runs will result in UNIQUE constraint conflicts (aka - you can't add duplicate rows to the CLASS_TABLE)

The following code :-

    SQLiteDatabase db;
    db=openOrCreateDatabase("STUDENTGRADES", Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS STUDENT_TABLE (studentid TEXT, fname TEXT, lname TEXT)");
    db.execSQL("CREATE TABLE IF NOT EXISTS CLASS_TABLE(classid TEXT PRIMARY KEY, classname TEXT UNIQUE)");
    db.execSQL("CREATE TABLE IF NOT EXISTS GRADE_TABLE (studentid TEXT, classid TEXT, pointgrade INTEGER, lettergrade TEXT)");
    db.execSQL("INSERT INTO STUDENT_TABLE \n" +
            "    VALUES('00001','Fred','Smith'),('00010','Mary','Thomas'),('00910','Angela','Jones')"
    );
    db.execSQL("INSERT INTO CLASS_TABLE VALUES('001','English'),('101','Mathematics'),('201','Chemistry');");
    db.execSQL("INSERT INTO GRADE_TABLE VALUES\n" +
            "    ('00001','001',99,'A'), -- Fred Smith has 99 point grade as an A in English\n" +
            "    ('00001','101',25,'F'), -- Fred Smith has 25 point grade as an F on Mathematics\n" +
            "    ('00010','201',76,'B'), -- Angela Jones 76 a B in Chemistry\n" +
            "    ('00910','101',50,'C'), \n" +
            "    ('00910','201',63,'C'),\n" +
            "    ('00910','001',89,'A')\n" +
            ";");
    Cursor csr = db.query("GRADE_TABLE JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid",null,null,null,null,null,null);
    CommonSQLiteUtilities.logDatabaseInfo(db);
    CommonSQLiteUtilities.logCursorData(csr);
    csr.close();

Results in :-

05-31 04:20:32.605 3582-3582/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/cpa.carpurchases/databases/STUDENTGRADES
    Database Version = 0
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
    Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = STUDENT_TABLE Created Using = CREATE TABLE STUDENT_TABLE (studentid TEXT, fname TEXT, lname TEXT)
    Table = STUDENT_TABLE ColumnName = studentid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = STUDENT_TABLE ColumnName = fname ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = STUDENT_TABLE ColumnName = lname ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = CLASS_TABLE Created Using = CREATE TABLE CLASS_TABLE(classid TEXT PRIMARY KEY, classname TEXT UNIQUE)
    Table = CLASS_TABLE ColumnName = classid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = CLASS_TABLE ColumnName = classname ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table Name = GRADE_TABLE Created Using = CREATE TABLE GRADE_TABLE (studentid TEXT, classid TEXT, pointgrade INTEGER, lettergrade TEXT)
    Table = GRADE_TABLE ColumnName = studentid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = GRADE_TABLE ColumnName = classid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = GRADE_TABLE ColumnName = pointgrade ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = GRADE_TABLE ColumnName = lettergrade ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
05-31 04:20:32.609 3582-3582/? D/SQLITE_CSU: logCursorData Cursor has 12 rows with 9 columns.
    Information for row 1 offset=0
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column pointgrade Type is INTEGER value as String is 99 value as long is 99 value as double is 99.0
        For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0
    Information for row 2 offset=1
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column pointgrade Type is INTEGER value as String is 99 value as long is 99 value as double is 99.0
        For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0
    Information for row 3 offset=2
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column pointgrade Type is INTEGER value as String is 25 value as long is 25 value as double is 25.0
        For Column lettergrade Type is STRING value as String is F value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
05-31 04:20:32.621 3582-3584/? D/dalvikvm: GC_CONCURRENT freed 338K, 11% free 6233K/6983K, paused 10ms+0ms, total 12ms
05-31 04:20:32.621 3582-3582/? D/SQLITE_CSU: Information for row 4 offset=3
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column pointgrade Type is INTEGER value as String is 25 value as long is 25 value as double is 25.0
        For Column lettergrade Type is STRING value as String is F value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
        For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
    Information for row 5 offset=4
        For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column pointgrade Type is INTEGER value as String is 76 value as long is 76 value as double is 76.0
        For Column lettergrade Type is STRING value as String is B value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
        For Column fname Type is STRING value as String is Mary value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Thomas value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
    Information for row 6 offset=5
        For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column pointgrade Type is INTEGER value as String is 76 value as long is 76 value as double is 76.0
        For Column lettergrade Type is STRING value as String is B value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
        For Column fname Type is STRING value as String is Mary value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Thomas value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
    Information for row 7 offset=6
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column pointgrade Type is INTEGER value as String is 50 value as long is 50 value as double is 50.0
        For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
05-31 04:20:32.637 3582-3584/? D/dalvikvm: GC_CONCURRENT freed 409K, 12% free 6226K/7047K, paused 11ms+0ms, total 13ms
05-31 04:20:32.637 3582-3582/? D/SQLITE_CSU: Information for row 8 offset=7
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column pointgrade Type is INTEGER value as String is 50 value as long is 50 value as double is 50.0
        For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
        For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
    Information for row 9 offset=8
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column pointgrade Type is INTEGER value as String is 63 value as long is 63 value as double is 63.0
        For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
    Information for row 10 offset=9
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column pointgrade Type is INTEGER value as String is 63 value as long is 63 value as double is 63.0
        For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
        For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
    Information for row 11 offset=10
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column pointgrade Type is INTEGER value as String is 89 value as long is 89 value as double is 89.0
        For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0
    Information for row 12 offset=11
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column pointgrade Type is INTEGER value as String is 89 value as long is 89 value as double is 89.0
        For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
        For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
        For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
        For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
        For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
        For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0

Additional re duplicate class

The following code extends the above by adding a new student to be enrolled in the 001 - English class (which already exists) using :-

    ContentValues cv = new ContentValues();
    String studentid = "00002";
    String fname = "Bert";
    String lname = "Jones";
    String classid = "001";
    String classname = "English";
    int pointgrade = 56;
    String lettergrade = "C";

    cv.put("studentid",studentid);
    cv.put("fname",fname);
    cv.put("lname",lname);
    if (db.insert("STUDENT_TABLE",null,cv) > 0) {
        cv.clear();
        cv.put("classid",classid);
        cv.put("classname",classname);
        if (db.insert("CLASS_TABLE",null,cv) > 0) {
            Log.d(
                    "CLASSADD",
                    "Class " +
                            cv.getAsString("classid") +
                            " - " +
                            cv.getAsString("classname") +
                            " ADDED."
            );
        } else {
            Log.d(
                    "CLASSADD",
                    "Class " +
                            cv.getAsString(
                                    "classid") +
                            " - " +
                            cv.getAsString("classname") +
                            " NOT ADDED (Exists)"
            );
        }
        cv.clear();
        cv.put("studentid",studentid);
        cv.put("classid",classid);
        cv.put("pointgrade",pointgrade);
        cv.put("lettergrade",lettergrade);
        if (db.insert("GRADE_TABLE",null,cv) > 0) {
            Log.d("ENROLRESULT",
                    "Student " +
                            cv.getAsString("studentid") +
                            " - " +
                            cv.getAsString("fname") +
                            " " +
                            cv.getAsString("lname")
                            + " pointgrade " +
                            String.valueOf(cv.getAsInteger("pointgrade")) +
                            " lettergrade " +
                            cv.getAsString("lettergrade") +
                            " ENROLLED"
            );
        }
    }

This :-

  1. Attempts to add (insert) the new student.
  2. If the student wasn't added then nothing else is done.
  3. Otherwise :-
    1. Attempts to add (insert) the class (001 - English) which already exists using the normal insert method (SQL would be INSERT OR IGNORE INTO CLASS_TABLE .....).
    2. Logs message indicating added or not <<<< The important bit
    3. Adds the appropriate GRADE_TABLE row

Running the above issues the following messages to the log :-

05-31 08:13:43.557 4476-4476/? D/CLASSADD: Class 001 - English NOT ADDED (Exists)
05-31 08:13:43.565 4476-4476/? D/ENROLRESULT: Student 00002 - null null pointgrade 56 lettergrade C ENROLLED

The App doesn't crash.

However the log also includes :-

05-31 08:13:43.557 4476-4476/? E/SQLiteDatabase: Error inserting classname=English classid=001
    android.database.sqlite.SQLiteConstraintException: column classname is not unique (code 19)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:775)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
        at cpa.carpurchases.MainActivity.onCreate(MainActivity.java:99)
        at android.app.Activity.performCreate(Activity.java:5008)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
        at android.app.ActivityThread.access$600(ActivityThread.java:130)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)
  • Note the absence of a FATAL RUNTIME EXCEPTION
  • Note the timestamps
    • fails to insert @
    • 05-31 08:13:43.557
    • but reports enrollment @
    • 05-31 08:13:43.565

Basically SQLite's insert has trapped the error allowing processing to continue but has printed the stack trace (arguably it shouldn't, arguably it should)

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • Now this is an amazing breakdown! Thank you! Where would I put the SQL join however? – SE Optimix May 31 '18 at 02:17
  • You could use rawQuery method with the entire SQL or if you wanted to use the query method then it is passed along with the table name in the first parameter . So `yourdb.query("GRADE_TABLE JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid",......);` *so really consider the first parameter as the **FROM** clause*. Note not checked so there may be typos. – MikeT May 31 '18 at 02:51
  • db.query("GRADE_TABLE JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid);"); is just all green and not working – SE Optimix May 31 '18 at 03:07
  • The ..... represents "the rest", i.e. the other 6 parameters of the [query method](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#query_11). Full command could be `Cursor csr = db.query("GRADE_TABLE JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid",null,null,null,null,null,null);` which would retrieve a Cursor with all the data. Which works as tested. – MikeT May 31 '18 at 04:02
  • Thank you. However even with the new db.execSQL, I am still unable to add the same class for more than one student – SE Optimix May 31 '18 at 04:20
  • Sounds like you are using what I warned against using. In the suggested solution you have a single row for each class, it is the grade that ties student to a class. See answer which now has working Android solution. – MikeT May 31 '18 at 04:26
  • As linked that class is from the link, it's something I use to write info to the Log. – MikeT May 31 '18 at 04:32
  • It might have to do with my ContentValues entry, please check my edit – SE Optimix May 31 '18 at 04:33
  • Added even further – SE Optimix May 31 '18 at 04:36
  • Also wondering, how to optimize the delete function to work with the query – SE Optimix May 31 '18 at 05:39
  • It appears as if you are trying to add everything at once and of course if a class already exists you wouldn't want to add it again just use insert method instead of insertWithOnConflict for the Class then it will ignore the conflict and skip adding the duplicate class. However when a new student enrols in a class does the educational establishment create a new class? That's what you appear to be trying to do. – MikeT May 31 '18 at 05:40
  • Example added to answer showing handling of non-unique class but not failing/crashing (even though it may appear so) of duplicate class. – MikeT May 31 '18 at 08:33
  • You dont know how much I appreciate your help, it is people like you that keep us new guys motivated and encouraged to become something of honor like yourself. I WAS able to get this working after changing some things around. My Final Question is how can I delete from a JOIN? I added my current delete function to my original post. Thank you – SE Optimix May 31 '18 at 16:49
  • It appears that you re-asked this as another user, there's an answer [here](https://stackoverflow.com/questions/50628386/how-to-update-delete-with-elements-from-two-different-tables-sqlite) – MikeT May 31 '18 at 23:32