Using SQL Queries

Before looking at SQL, it is important to understand the structure of the Basmati database. The chart below provides a snapshot of the tables and fields within the database:

Tables:	CLIENTS	COURSEINFO	GMSCORES	GROUPS	PERSONAL	SCHOOLS
Fields:	client_id client_pw client_school client_expdate client_fullname webinfo webinfodate	cc schoolid facultyname facultycode period email phone misc coursename assignlist assignvals ealr modified type	sid cc schoolid scores percent grade comments	groupname groupsids grouppw schoolid	sid schoolid last first grade password did emailaddress	school_id school_name school_state school_city school_url

A more complete description of each field is pending.

• One of the most useful queries is the SELECT query -- which allows you to easily view the contents of your database. The syntax for this query is very straightforward. Several examples are shown below: SELECT * FROM COURSEINFO;
  SELECT * FROM COURSEINFO WHERE CC = 'CAL50203';
  SELECT * FROM COURSEINFO ORDER BY CC;
  SELECT FACULTYNAME, EMAIL FROM COURSEINFO ORDER BY EMAIL;
  Get the picture? The generic form for the SELECT statement is SELECT {fields or *} FROM {table} WHERE {fieldname} {comparison} {value}.

• Perhaps the next most useful statement is the DELETE verb. Be very careful with this one -- if you don't qualify what you're deleting, you'll probably end up deleting the entire table! Here are some examples: DELETE FROM COURSEINFO; (Careful! This will delete the entire table!)
  DELETE FROM COURSEINFO WHERE CC = 'CAL50203';

• To modify an existing field in a table, use the UPDATE statement. For example, let's suppose that we need to change the name of the course (coursename) corresponding the the coursecode 'CAL50203'. The SQL statement used to do this would be... UPDATE COURSEINFO SET coursename = 'newemailaddress@here.com' WHERE CC = 'CAL50203'; In practice, there is no reason to actually do this -- when you send an export file to Basmati, any changes (such as email address) should automatically be reflected.

• Finally, you can use the INSERT statement to add a new record to a table. Here's a brief example (adding a new record to the CLEINTS table): INSERT INTO CLIENTS (client_id, client_pw, client_school, client_expdate, client_fullname) VALUES ('me@here.com', 'password', 'HS', '8/4/2002', 'Mr. Me.');