DROP TABLE IF EXISTS student; DROP TABLE IF EXISTS class; CREATE TABLE class ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ); CREATE TABLE student ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT, classid INTEGER, FOREIGN KEY(classid) REFERENCES class(id) ); INSERT INTO class(id, name) VALUES (1, "Physics"); INSERT INTO class(id, name) VALUES (2, "Mathematics"); INSERT INTO student(first_name, last_name, classid) VALUES ("Bob", "Smith", "1"); INSERT INTO student(first_name, last_name, classid) VALUES ("Pete", "Brown", "2"); INSERT INTO student(first_name, last_name, classid) VALUES ("Robert", "Carter", "2"); INSERT INTO student(first_name, last_name, classid) VALUES ("Aaron", "Yong", "2"); SELECT c.name className, s.first_name firstName, s.last_name lastName FROM student s JOIN class c ON s.classid=c.id WHERE c.id=2 ORDER BY s.first_name;