Sample SQL statements for use with VBScript in ASP pages Given the following tables with fields as indicated: Table name: teacher (this table contains names & data of homeroom teachers) fields: teacherID (unique number, key field) tsurname (text: Mr., Mrs., Miss, Dr., etc.) tname (text: teacher's name) class (text: name of the class this teacher teaches) classcount (number: the number of students in this class) Table name: student fields: studentID (unique number, key field) slname (text: student's last name) sfname (text: student's first name) snumber (text: student's 5-digit school ID number) sgrade (number: student's grade, 9-12) teacherID (number matching teacherID of student's homeroom teacher) requesting ********************** SELECT * FROM teacher 'select all fields from table named teacher SELECT teacherID, tsurname, tname, class FROM teacher SELECT studentID, slname, sfname, snumber, sgrade, teacherID FROM student requesting an "equi-join" (two tables with a matching field) ************* SELECT slname, sfname, sgrade, tsurname, tname, class FROM student, teacher WHERE snumber='55555' AND student.teacherID=teacher.teacherID requesting a sorted "equi-join" ******************** SELECT slname, sfname, sgrade, voted, tsurname, tname, class FROM student, teacher WHERE snumber='55555' AND student.teacherID=teacher.teacherID ORDER BY slname requesting a sorted list, ascending ************** SELECT slname, sfname, sgrade FROM student ORDER BY slname ASC 'note: if ASC is omitted, it is assumed requesting a sorted list, descending ************ SELECT slname, sfname, sgrade FROM student ORDER BY slname DESC requesting a sorted list, mixed ascending and descending SELECT slname, sfname, sgrade FROM student ORDER BY sgrade DESC, slname ASC requesting a matching pattern *************** SELECT slname, sfname, sgrade FROM student WHERE slname Like 'd*' * means any number of characters ? means just character Is the match case sensitive? SELECT slname, sfname, sgrade FROM student WHERE slname Like 'd[a-e]*' locates records whose last name starts with letter d, followed by a through e only, followed by any number of other characters. Updating **************** UPDATE teacher SET class='',classcount=0 WHERE teacherID=7 Deleting **************** DELETE * FROM students WHERE studentID=77 Inserting ******************** INSERT INTO teachers(tsurname,tname,class,classcount) VALUES('Mr.', 'C. Monroe', 'IT Programming', 0) note: the INSERT command actually appends the data to the bottom of the teachers table. *********************************************************** Other notes: Boolean values are True and False, without quotation marks, or you get a type-mismatch. In an Access database, a Yes/No field is displayed in Access as a checkbox, however, the actual values stored and/or retries are True and False. SQL is case sensitive in some database system, but not Access.