#Written by Jen Chen
#Copyrighted by Jen Chen
#Lesson 2 
#Spring 2005

			==============================
			* Data modifying language (DML)
			==============================

==================================================================================
	Creating/Removing PRIMARY KEY(s) and MULTIPLE PRIMARY KEY(s).
==================================================================================

   - To add a primary key(s) to an existing table, we use the sql command 
	"alter table" together with the "add primary key()" option; inside the
	perentheses we provide the field(s) which we want to be primary.

	alter table Course
	add primary key(courseName);

   You can add more than one primary keys into your table as follow: (providing that
   there is no PRIMARY KEY exists in your table).
   If there is a primary key exists in your table, then you have to drop this primary key
   BEFORE you can add any primary key(s) to your table.

	alter table Course
	add primary key(courseId, courseId);	

   - To remove a primary key, we issue the statement "alter table" with the option
"drop primary key" as follow:

	alter table Course
	drop primary key

   NOTE: You can not add a new primary key to a table with an existing primary key.
	To add a new primary key to a table with an existing primary key, we HAVE
	to DROP the existing primary key(s), then add the new primary key(s) to
	your table as above.

   - To add a MULTIPLE PRIMARY into an existing table, we use "ALTER TABLE" follow by
"ADD KEY(field_name); where filed_name is the field that you'd like to be the multiple
primary key field.
	ex.:
	alter table Course add key(class);	
	alter table Course add key(classIndex);
	
	Note that we add the multiple primary key ONE AT A TIME into a table. We can
	not add more than one multiple primary key into an existing table in mysql.

	NOTE: If we'd like to remove a multiple primary key, then use the "drop index"
	syntax as follow:

		DROP INDEX index_name ON tbl_name

	Thus, to remove the multiple primary key "classIndex" frmo the Course table,
	we issue the SQL command:

		drop index classIndex on Course

	CAUTION: If there exist an auto_increment field in your table, which is also
	a primary key field, then in order to drop this primary key field, we have to
	alter the data type (int auto_increment) to int BEFORE we can drop this key.
	For example, if the classIndex is an auto_increment primary key field, then
	we have to do the following steps in order to remove the this field primary key.
	
	alter table Course change classIndex classIndex int;
	alter table Course drop primary key;
	
	Now, if we want to add two new primary keys into the Course table, then we
	issue the following SQL statement:
	
	alter table Course add primary key(classIndex, somethingElse);

   - To add an index into an existing table, we issue the statement "alter table .."
with the "add index ( column_name )" as follow:

	alter table Course
	   add index (courseId)

   - To REMOVE an existing index from a table, we issue the statement "alter table .."
with the "drop index index_name" as follow:

	alter table Course
	   drop index courseId

==================================================================================
	RENAMING a table
==================================================================================

   - To CHANGE the name of an existing table. we use the sql command "alter table"
with the option "rename" as follow:

	alter table Course
	   rename CourseTmp

==================================================================================
	ADDING/REMOVING/RENAMING COLUMN(s)
==================================================================================

   - To ADD a NEW COLUMN after an existing column, we use the "alter table" statement
with the option "add [column] column_definition [FIRST | AFTER col_name]". If we
use this same statement but without the option [FIRST | AFTER], then the new column
will be added to the end of the existing table.

	alter table Course
	   add newColumn varchar(20)

   This SQL statement will add a new column called "newColumn" into the table "Course"
and this column will be added to the end of this table.

	alter table Course
	   add newColumn varchar(20) after section

   This SQL statement will add a new column called "newColumn" into the table "Course"
and this column will be added after the column "section".

   - To CHANGE the NAME of a field (column) use the "alter table ..." statement together
with the word "change" follow by the old_column_name, then _new_column_name, then the
data type of the new column. Remember that you have to enter the data type for the
new_column_name; otherwise it does not work as follow:

	alter table Tmp change Amount Price decimal(10,2) not null; 

	This will change the column "Amount" to "Price" from the table Tmp with the 
	new data type.

   - To add a column (which must be primary) into a table and this column must automatically
increment when a new record is added to an existing table, we use "alter table" with the
"add [COLUMN] option". (NOTE: this column MUST be an integer type) as follow:

	alter table prereq
	   add rowNum int not null primary key auto_increment


==================================================================================
   UPLOADING a comma/tab/space (or anything) delimited file to your Mysql database.
==================================================================================

   - To upload a csv file to an existing mysql table. we issue the statement 

	load data local infile "NAME_OF_YOUR_FILE"
	   into table Course
	   fields terminated by ","

CAUTION: BEFORE you upload a csv file into your mysql table, make sure that
the structure of your table matches the data in your csv file.
	Look carefully at the data in your csv file BEFORE you upload to your
	table. If there is any discrepancy in the data then you have to make some 
	changes	to your data before uploading it.
	You can upload any flat file (text delimited file) into an existing table.
	The only condition is that you have to indicate the field delimiter by declaring
	'fields delimited by' follow by the delimiter inside a pair of parentheses.
	Also make sure that you upload the csv (or tab, space, etc...) file onto
	the directory where you start your mysql. Otherwise you will get an error message
	saying "File not found".
	After you upload your data, double check the contents of your table to make
	sure that all data is loaded properly.
	Also make sure that there is no warning. If there is any warning, then
	issue the sql command "show WARNINGS" (for version 4.1 and above) to see what
	the warning is all about. The integrity of your data is extremely important;
	therefore make sure that your uploading is clean and there is no error nor
	warning message display after your upload.

   * NOTE: We can undo any change made to a table by issuing the SQL command "rollback".
   * NOTE: To save any change made to a table, we issue the SQL command "commit". Once
   this command is issued, then any change made to a table can not be undo.

	By default mysql set commit to be true. Thus we have to issue the SQL command
		set autocommit = 0
	to undo the default. Once this is done then we are able to issue the "rollback" command
	to undo our changes.


==================================
* Data manipulation language (DML).
==================================
   - To modify the field of a record, we issue the statement 

	UPDATE TABLE_NAME
	SET COLUMN_NAME = ...........

   For example if we want to change the field "year" to the year 2004 for ALL
existing records, then issue the statement:
	
	update ClassWW
	set year = 2004
	This will set the field "year" on ALL records to 2004.

   - If you want to modify the contents of ONE record, then issue the UPDATE
statement together with the WHERE clause to specify which record(s) that we
want to modify as follow:

	update ClassJC
	   set location = 'E&T A210'
	where
	   courseNum = 'xxxxx'
	   and location = 'xxxxx'
	   and instId = 'xxxxx';

where you replace the 'xxxxx' with your conditions.
	You can UPDATE more than one record at a time, depend on the condition
that you issue in the WHERE clause.	

* Task: To query all Instructors whose instId's appear in the table ClassJC.

- Tables: CS122.ClassJC, CS122.FacultyJC

   Note: An alias is a short hand for a field/table and it is placed right after
   the field/table's name, seperated by a space(s).
   
   Include these fields in your result set: 
	From table FacultyJC: courseName, lastName, instId.
	From table ClassJC: location, quarter, year.

   We normally use aliases if there are more than one tables to query from;
or in the case that there exists some duplicate fields in these tables.

	select F.courseName, F.lastName, F.instId, C.location,C.quarter, C.year
	from
	FacultyJC F,
	ClassJC C
	where
	F.instId=C.instId

   - To select all fields in a table, we use the SELECT statement together with the 
wild card (*) as follow:

	select * from ClassJC;