Schema Change/Update script for Database deploy I have a need to change the database schema . I'm planning to write Schema change and update scripts for tracking database changes and updating them. I followed

Versioning Databases – Change Scripts

for a start, I got a gist of what he is getting at however since I haven't worked much on SQL scripts before, a tutorial or something to start with would be good. I did some research on the web and came to know that most people use Automatic comparing tools to generate the script which I don't want to do for obvious reason that I won't learn the anything in the process.

I'm looking for some tutorials/links on How to write Change scripts and Update scripts ? Especially update scripts as I couln't find even a single script/pseudo-code on how to do update schema by comparing SchemaChangeLog table, connecting to the table using scripts...

Thanks in advance!
First, I would go with an existing solution, such as Liquibase. If you use an ORM such as Hibernate, it can also do schema migrations for you.

Otherwise, it would go something like this:

Start with an initial database schema script, and give it a version number, such as 0.0.0001.sql. It would contain mostly CREATE statements to create tables, etc, such as:

-- use me to keep track of what version the database schema is using:
create table schema_version (
  name text primary key
);

create table contact (
  id bigserial primary key,
  first_name text not null
);

insert into schema_version values ('0.0.0001');


You would store all of your .sql schema scripts in source control, such as git.

Then, as needed, you would add patch files, such as 0.0.0002.sql. It would normally contain ALTER statements that adjust your database schema:

alter table contact add column last_name text;

insert into schema_version values ('0.0.0002');


You will probably need to read the documentation for your particular database, as they often have slightly different ALTER syntax. 

You would then have a non-sql script (bash perhaps), that would check which schema version the database is using and apply any available patches that are greater than that version, or the first sql script if there is no schema_version table.

This book is highly rated on Amazon:

Refactoring Databases: Evolutionary Database Design