Start using PostgreSQL now….

Spend 10 minutes and have PostgreSQL on your own lilo flavor

Purpose of this article to give jump starts to install and performed basic PostgreSQL (pgsql) administrator / user job.

Download binaries from this location

tar -xzvf postgresql-8.3.1.tar.gz

gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su – postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

You are all set with test database, go ahead and start exploring sql commands on that
$ psql test

If you leave off the database name then it will default to your user account name.

In psql, you will be greeted with the following message:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# create sequence pk_seq;

test=# create table my_data (
test(# data_pk integer DEFAULT nextval(‘pk_seq’::text) NOT NULL,
test(# my_value integer
test(# );

test=# insert into my_data (my_value) values (1);
INSERT 17240 1
test=# select * from my_data;
data_pk | my_value
1 | 1
(1 row)

test=# insert into my_data (my_value) values (2);
INSERT 17241 1
test=# select * from my_data;
data_pk | my_value
1 | 1
2 | 2
(2 rows)

test=# \d
List of relations
Schema | Name | Type | Owner
public | my_data | table | postgres
public | pk_seq | sequence | postgres
(2 rows)

test=# \d my_data
Table “public.my_data”
Column | Type | Modifiers
data_pk | integer | not null default nextval(‘pk_seq’::text)
my_value | integer |

test=# \q
[mst3k@hera ~]$

Spend 10 more minutes and learn how to connect PostgeSQL from Perl

[ ~]$ cat

use strict;

use DBI;

my $dbargs = {AutoCommit => 0, PrintError => 1};

my $dbh =

my $sql = “select * from my_data”;
my $sth = $dbh->prepare($sql);
if ($dbh->err()) { die “prepare:\n$DBI::errstr\n”; }

if ($dbh->err()) { die “execute:\n$DBI::errstr\n”; }

while(my $hr = $sth->fetchrow_hashref())
print “pk: $hr->{data_pk} value: $hr->{my_value}\n”;

Spend 10 more minutes to learn few more SQL tricks

To use tab as a field separator you can use bash shell features when
launching psql:

psql -A -t -U $DBUSER -d $DB -c “$QUERY” -F $’\t’

When you are already running psql, use this command:


Apparently, using the single ticks (quotes) causes the \t to be
evaluated by the shell’s command processor.

In order to get tab separated columns, you will also need to disable
“aligned output” with \a.

Here is the entire sequence of commands:

\o myfile.txt
select * from big_table;

I suspect that if you want true CSV output (comma separated with
quoted fields, etc.) that you will need to write a small Perl script
using a Perl CSV module, and using DBI to talk to Postgres. I’ve
already got a Perl API which makes SQL even easier than DBI, so I
could write the whole application in 10 lines of code.

The following does a date conversion from an integer containing the
Unix timestamp, as well as truncating some columns for brevity and
renaming other columns with long names. \H enables HTML output which
in this case means a file with an HTML table (no <HTML> or <BODY>
tags). This works fine in Firefox, but I haven’t tried it in othwer
browsers. \o is send output to a file. Stop outputting with a blank \o.

\o public_html/summary.html
select cl_pk,who_updated as who,plate,prow,pcol,substring(date ‘1970-01-01’ + screen_date * interval ‘1 second’ from ‘.*\ ‘) as scrn_date,comments,substring(gene_id from ‘^.{5}’)||’…’ as gene,substring(sequence from ‘^.{5}’)||’…’ as seq,image_file,localization as loc,morphology as morph,migration as mig,secretion as sec,is_valid as ok from clone order by cl_pk;

Here is the same query formatted:

SELECT cl_pk,
who_updated AS who,
SUBSTRING(DATE ‘1970-01-01’ + screen_date * INTERVAL ‘1 SECOND’ FROM ‘.*\ ‘) AS scrn_date,
SUBSTRING(gene_id FROM ‘^.{5}’)||’…’ AS gene,
SUBSTRING(sequence FROM ‘^.{5}’)||’…’ AS seq,
localization AS loc,
morphology AS morph,
migration AS mig,
secretion AS sec,
is_valid AS ok
FROM clone ORDER BY cl_pk;

This query was run against a single table that looks like:

create table “clone”
“cl_pk” integer DEFAULT nextval(‘pk_seq’::text) NOT NULL,
“ec_fk” integer, — foreign key to ec_pk in exp_condition
“plate” varchar(128), — plate name aka plate id
“prow” varchar(128), — plate row A-F
“pcol” integer, — place column 1-12
“lab_book” varchar(256),
“screen_date” integer, — unix timestamp
“userid” integer, — us_pk from usersec table or it’s equivalent
“clone_status” integer, — probably from the type table
“gene_id” varchar(256),
“sequence” text,
“localization” integer, — from the type table
“morphology” integer, — from the type table
“migration” integer, — from the type table
“secretion” boolean,
“comments” text,
“image_file” varchar(256), — image file to display in web page
“who_updated” varchar(256), — last userid to update
“ip_address” varchar(128), — ip address from which record was updated
“original_image_file” varchar(256), — original uploaded image file
is_valid integer DEFAULT 1 — one=valid, zero=invalid
) without OIDs;

Spend 10 more minutes and understand why PostgreSQL is better than Oracle

The following list is not complete, and I’m not an Oracle expert. I’m
facile with Postgres, but would not call myself a Postgres expert
either. Therefore the following list may contain errors. My conclusion
is unchanged: PostgreSQL is the best database.

– Postgres isn’t encumbered by licenses. You can install it as often
as you like on as many computers as you like.

– Postgres is part of Fedora and therefore faster and easier to

– Postgres is much smaller than Oracle. Postgres is around 6MB. Oracle
10g is over 200MB.

– The Postgres Perl DBD driver is part of Fedora. You’ll have to go to
CPAN, download and install the Oracle DBD driver.

– Read the DBD documentation for the Oracle DBD driver. The docs are
full of features that may or may not work. There are loads of
warnings about various features. For whatever reason, the Postgres
DBD driver seems simpler, more robust and more mature.

– Oracle doesn’t seem to provide a “native” Oracle supported DBD
driver. It isn’t clear that Oracle supports the authors of the Perl
DBD driver (maybe they do, maybe they don’t… I can’t tell).

– Postgres allows functions as default values for columns in
tables. Oracle doesn’t.

– The Oracle command line, sqlplus, is a flaming pile of crap. It
doesn’t have any cursor control support, the previous command buffer
is barely accessible, and it is missing tab complete, is appears to
have no way to cancel a command (i.e., no control-C). It looks like
something from the 1970s when a user interface meant a teletype or
card reader. Yes, there is a fancy web interface, but many types of
system administration simply are not feasible via a GUI interface,
especially a web interface.

– The PostgreSQL command line interface, psql, has fully functional
editing, a command buffer, tab complete for commands, tables and
columns, cancel (via control-C). It is wonderful.

– At the command line interface, Postgres has help for every
command. Oracle doesn’t. In fact, Oracle’s sqlplus has no built-in
help for any SQL commands.

– The standard record display from sqlplus is very crude, and
difficult to read.

– Postgres has a “text” datatype which does not require a size
specification. There is no performance penalty for using this data
type. Oracle has LONG and a couple of types of BLOBs, each of which
has several show-stopper limitations.

– Oracle can have only one LONG per table. Postgres can have as many
TEXT fields per table as you like. Oracle apparently can have only
one BLOB per table (perhaps including LONG). Postgres does not have
this limitation.

– When Perl DBI does a fetchrow_hashref() from Oracle, the field names
come back all uppercase. Postgres returns field names lowercase. I
enter all my field names lower case since they are easier to type.

– Oracle’s sqlplus defaults to autocommit off. That’s fine but when
you update a record from sqlplus, any other processes that are
trying to update that record will be locked until you
commit. Perhaps this is why PostgreSQL’s psql command line interface
defaults to autocommit (actually, there isn’t even a setting for it)
and if you want a non-autocommit transaction in psql, you use begin;
command; commit; to explicitly create a transaction.

– Postgres psql has many commands that display and/or manage the
database meta data. There is useful “help” for all these
commands. If you wish, there is also a mode that displays the actual
SQL code used to display/manage the meta data.


Sybase iSQL ASE 12.5 drive

This article will get you up and running with Sybase database commands. I personally refer it many times when I need  specific command. I kept it comprehensive reference material for anyone to jump start.

What is difference between SQL & T-SQL?

SQL- set of sqls are submitted individually to the database server.

T-SQL- the batch program is written where in all commands are submitted to the server in a single go. usually batches are run overnight and and all inserts and updates happen and these batches are scheduled. where as sqls’ are to run separately…..

all sqls’ are put in a file and schedule them called –t-sql .. besides it offers some other commands too.

SQL is the Structured Query Language the ANSI/ISO Standard database language. SQL Server’s implementation of the language is called Transact-SQL (T-SQL).


Connect sybase from Linux console
isql -Hpiidb199:9794 -SNYQ_MWDATA -Umware_rw -DGE2

use DN_Name

select DB_Name

sp_helpdb DB_Name

shows this info
name,db_size,owner,dbid,created,status,device_fragments,size,usage,created,free kbytessp_help

It shows all the availble objects of following type
view, user table, triggers, system table, stored procedure

sp_help Table_Name

shows description of table i.e.
Column_name,Type,Length,Prec,Scale Nulls,Default_name,Rule_name,Access_Rule_name,Identity

set rowcount 2
select * from ge_company

Below query will display exactly 2 records from table
select * from dbo.ge_comapany where company_id = 766

*Note table should be given with owner, here dbo is owner of ge_company

sp_helpindex ge_company

This command will show you list of indexes available into ge_company table
select name from sysobjects where name like “ge_%”

By default these tables will be created automatically when new database created
1> sp_help
2> go

Name Owner Object_type
—————————— —————————— ———————-
syb_auto_db_extend_control dbo user table
sysalternates dbo system table
sysattributes dbo system table
syscolumns dbo system table
syscomments dbo system table
sysconstraints dbo system table
sysdepends dbo system table
sysgams dbo system table
sysindexes dbo system table
sysjars dbo system table
syskeys dbo system table
syslogs dbo system table
sysobjects dbo system table
syspartitions dbo system table
sysprocedures dbo system table
sysprotects dbo system table
sysqueryplans dbo system table
sysreferences dbo system table
sysroles dbo system table
syssegments dbo system table
sysstatistics dbo system table
systabstats dbo system table
systhresholds dbo system table
systypes dbo system table
sysusermessages dbo system table
sysusers dbo system table
sysxtypes dbo system table
Some more tips of Sybase blocking handel

2> sp_block
3> go

no process should be in Absolute blocking spid

spid hostname
————————- —— ———-
Absolute blocking spid is 537 piias1359
Absolute blocking spid is 49 pi944c3n9
Absolute blocking spid is 616 paias1186
One login can play with these commands

Show login user information
> sp_displaylogin
> go

Suid: 3018
Loginame: irfansh

Default Database: env
Default Language:
Auto Login Script:
Configured Authorization:
Locked: NO
Date of Last Password Change: Jun 14 2007 2:01PM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: ANY
(return status = 0)

To change logged in users password
>sp_password old_passwd, new_passwd

Selecting data from Table: querys and functions
> select sum(salary) from emp_tbl
> go

Determining which table columns to join
1> sp_helpjoins ge2_group, ge2_group_apps
2> go

—————————— ——————————
group_id group_id

(1 row affected)
(return status = 0)

To find a table’s referenced tables.
1> sp_helpconstraint
2> go

Aggregate Function Result

sum([all|distinct] expression) Total of the (distinct) values in the expression

avg([all|distinct] expression) Average of the (distinct) values in the expression

count([all|distinct] expression) Number of (distinct) non-null values in the expression

count(*) Number of selected rows

max(expression) Highest value in the expression

min(expression) Lowest value in the expression

count vs. count(*)

While count finds the number of non-null values in the expression, count(*) finds the total number of rows in a table. This statement finds the total number of books:

Adaptive Server ignores any null values in the column on which the aggregate function is operating for the purposes of the function (except count(*), which includes them). If you have set ansi null to on, Adaptive Server returns an error message whenever a null value is ignored. For more information, see the set command in the Reference Manual.

Use the having clause to display or reject rows defined by the group by clause. The having clause sets conditions for the group by clause in the same way where sets conditions for the select clause, except where cannot include aggregates, while having often does.

This example is legal:

select title_id from titles where title_id like “PS%” having avg(price) > $2.0

But this example is not:

select title_id from titles where avg(price) > $20

How the having, group by, and where clauses interact

When you include the having, group by, and where clauses in a query, the sequence in which each clause affects the rows determines the final results:

  • The where clause excludes rows that do not meet its search conditions.
  • The group by clause collects the remaining rows into one group for each unique value in the group by expression.
  • Aggregate functions specified in the select list calculate summary values for each group.
  • The having clause excludes rows from the final results that do not meet its search conditions.

Sorting query results

The order by clause allows you to sort query results by one or more columns, up to 31.

How joins work

When you join two or more tables, the columns being compared must have similar values—that is, values using the same or similar datatypes.

There are several types of joins, such as equijoins, natural joins, and outer joins. The most common join, the equijoins, is based on equality.

The following join finds the names of authors and publishers located in the same city:

select au_fname, au_lname, pub_name

from authors, publishers

where =

au_fname au_lname pub_name

——– ——– ——————–

Cheryl Carson Algodata Infosystems

Abraham Bennet Algodata Infosystems

(2 rows affected)

This earlier query is an example of an equijoin:

select * from authors, publishers

where =

In the results of that statement, the city column appears twice. By definition, the results of an equijoin contain two identical columns. Because there is usually no point in repeating the same information, one of these columns can be eliminated by restating the query. The result is called a natural join.

example of a natural join is:

select au_fname, au_lname, pub_name

from authors, publishers

where =

Self-joins and correlation names

Joins that compare values within the same column of one table are called self-joins. To distinguish the two roles in which the table appears, use aliases, or correlation names.


select au1.au_fname, au1.au_lname,

au2.au_fname, au2.au_lname

from authors au1, authors au2

where = "Oakland" and = "Oakland"

and au1.state = "CA" and au2.state = "CA"

and au1.postalcode = au2.postalcode

Outer joins

Joins that include all rows, regardless of whether there is a matching row, are called outer joins. Adaptive Server supports both left and right outer joins. For example, the following query joins the titles and the title author

tables on their title_id column:

select *

from titles, titleauthor

where titles.title_id *= titleauthor.title_id

Inner joins

In which the joined table includes only the rows of the inner and outer tables that meet the conditions of the on clause.

Outer joins, in which the joined table includes all the rows from the outer table whether or not they meet the conditions of the on clause.

This query finds the names of all the publishers who publish business books:

select pub_name

from publishers

where exists

(select *

from titles

where pub_id = publishers.pub_id

and type = "business")

pub_name to find the names of publishers who do not publish business books, the query is:

select pub_name

from publishers

where not exists

(select *

from titles

where pub_id = publishers.pub_id

and type = "business")

Using and Creating Datatype

Datatype by category Synonyms Range Bytes of storage

Integer Type

Datatype Stores Bytes of storage
bigint Whole numbers between -263 and 263 – 1 (from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, inclusive. 8
int[eger] Whole numbers between-231 and 231 – 1 (-2,147,483,648 and 2,147,483,647), inclusive. 4
smallint Whole numbers between -215 and 215 -1 (-32,768 and 32,767), inclusive. 2
tinyint Whole numbers between 0 and 255, inclusive. (Negative numbers are not permitted.) 1
unsigned bigint Whole numbers between 0 and 18,446,744,073,709,551,615 8
unsigned int Whole numbers between 0 and 4,294,967,295 4
unsigned smallint Whole numbers between 0 and 65,535 2

Approximate numeric

Datatype Bytes of storage
float[(default precision)] 4 for default precision < 168 for default precision >= 16
double precision 8
real 4


Datatype Range Bytes of storage
money Monetary values between +922,337,203,685,477.5807 and -922,337,203,685,477.5808 8
smallmoney Monetary values between +214,748.3647 and -214,748.3648 4


Datatype Range Bytes of storage
datetime January 1, 1753 through December 31, 9999 8
smalldatetime January 1, 1900 through June 6, 2079 4
date January 1, 0001 to December 31, 9999 4
time 12:00:00 AM to 11:59:59:999 PM 4


Datatype Stores Bytes of storage
char(n) Character n
unichar(n) Unicode character n*@@unicharsize (@@unicharsize equals 2)
nchar(n) National character n * @@ncharsize
varchar(n) Character varying Actual number of characters entered
univarchar(n) Unicode character varying Actual number of characters * @@unicharsize
nvarchar(n) National character varying Actual number of characters * @@ncharsize

Creating user-defined datatypes

Here is the syntax for sp_addtype:

sp_addtype datatypename,

phystype [ (length) | (precision [, scale] ) ][, “identity” |nulltype]

Here is how tid was defined:

sp_addtype tid, “char(6)”, “not null”

Dropping a user-defined datatype

To drop a user-defined datatype, execute sp_droptype:

sp_droptype typename

Note You cannot drop a datatype that is already in use in any table.

Getting information about datatypes

sp_help money

What are the databases and tables?

Adaptive Server database objects include:

  1. Tables
  2. Rules
  3. Defaults
  4. Stored procedures
  5. Triggers
  7. Referential integrity constraints
  8. Check integrity constraints

Single database can have 2 billion tables as per ASE 11.5

Indian Quotation Amount International Quotation
1 Lakh 100,000.00 100 Thousands
10 Lakhs 1,000,000.00 1 Million
1 Crore 10,000,000.00 10 Million
10 Crores 100,000,000.00 100 Million
100 Crores 1,000,000,000.00 1 Billion

Temporary table

You can create temporary tables either by preceding the table name in a create table statement with a pound sign (#) or by specifying the name

prefix “tempdb..”

Temporary tables are created in the tempdb database.

There are two kinds of temporary tables:

Tables that can be shared among Adaptive Server sessions

create table tempdb..authors

(au_id char(11))

drop table tempdb..authors

Tables that are accessible only by the current Adaptive Server session or procedure

create table #authors

(au_id char (11))

exists until the current session or procedure ends, or owner drops it.

Important Rules on temporary tables

  1. cannot create views on these tables.
  2.  cannot associate triggers with these tables.
  3. cannot tell which session or procedure has created these tables.

*NOTE: These restrictions do not apply to shareable, temporary tables created in tempdb.


You define an IDENTITY column by specifying the keyword identity,


(sale_id numeric(5,0) identity,create table sales_daily

stor_id char(4) not null)

Column ‘sale_id’ will be maintained by sybase server. Similar to MySQL auto_increment feature. A row can be uniquely identified by this key.

*NOTE:- Avoid using IDENTITY in production since some time server enters a value which might not be in order.

By default, Adaptive Server begins numbering rows with the value 1, and continues numbering rows consecutively as they are added. Some activities, such as manual insertions, deletions, or transaction rollbacks, and server shutdowns or failures, can create gaps in IDENTITY column values.

Adaptive Server provides several methods of controlling identity gaps described in “Managing identity gaps in tables”

The IDENTITY column contains a unique ID number, generated by Adaptive Server, for each row in a table.

Uniq VS Primary key

Both constraints create unique indexes to enforce this data integrity. However, primary key constraints are more restrictive than unique constraints. Columns with primary key constraints cannot contain a NULL value.

Definition of unique constraints in the SQL standards specifies that the column definition shall not allow null values.

sp_primarykey, sp_foreignkey, and sp_commonkey define the logical relationship of keys (in the syskeys table) for table columns, which you enforce by creating indexes and triggers

There can be only one clustered index on a table,

The maximum number of references allowed for a table is 192.

Using sp_helpconstraint to find a table’s constraint information

A schema is a collection of objects owned by a particular user, and the permissions associated with those objects.


create schema authorization dbo

create table list1

(col_a char(10) primary key,

col_b char(10) null

references list2(col_A))

create table list2

(col_A char(10) primary key,

col_B char(10) null

references list1(col_a))

You can declare a check constraint to limit the values users insert into a column in a table.


create table my_new_publishers

(pub_id char(4)

check (pub_id in ("1389", "0736", "0877",

"1622", "1756")

or pub_id like "99[0-9][0-9]"),

pub_name varchar(40),

city varchar(20),

state char(2))

Create table from existing table using “select into”


1> select pname, sname into friends_name from froends_etc

2> go

(0 rows affected)

Execution Time (ms.): 1000 Clock Time (ms.): 1000

View Limitation

There are a few restrictions on the select statements in a view definition:

• You cannot include order by or compute clauses.

• You cannot include the into keyword.

• You cannot reference a temporary table.

For example, to rename titleview to bookview, enter:

sp_rename titleview, bookview

Follow these conventions when renaming views:

To display the text of the create view statement, execute sp_helptext


sp_helptext hiprice



(1 row affected)



create view hiprice

as select *

from titles

where price > $15 and advance > $5000

(1 row affected, return status = 0)

sp_depends lists all the objects that the view or table references in the

current database, and all the objects that reference that view or table.


sp_depends titles

Things inside the current database that reference the object.

object type

————- —————————

dbo.history_proc stored procedure

dbo.title_proc stored procedure

dbo.titleid_proc stored procedure

dbo.deltitle trigger

dbo.totalsales_trig trigger

dbo.accounts view

dbo.bookview view

dbo.categories view

dbo.hiprice view

dbo.multitable_view view

dbo.titleview view

(return status = 0)

sp_tables lists all views in a database when used in the following format:

sp_tables @table_type = “’VIEW’”

Transact SQL: numeric functions

Mathematic Functions

abs            absolute value             abs(-5) = 5
ceiling        next highest int           ceiling(5.3) = 6
floor          next lowest int            floor(5.7) = 5
power          exponential                power(2,8)=256
rand           random number              rand=0.315378 for example
round          round to n places          round(5.6,0)=6   round(5.66,1)=5.7
sign           -1,0,1                     sign(-5)=-1

Transact SQL: string functions

plus sign (+)      concatenation             'one'+'two'='onetwo'
ascii              char->ascii value         ascii('A')=65
char               ascii->char               char(65)='A'
charindex          similar to instring       charindex('two','onetwothree')=4
char_length        length of string          charlength('onetwo')=6
lower              lower case                lower('ONE')='one'
ltrim              trim left blanks          ltrim('   one')='one'
replicate          repeat chars              replicate('-',8)='--------'
reverse            flip string               reverse('salad')='dalas'
right              right chunk of string     right('Chicago',2)='go'
rtrim              trim right blanks         rtrim('test   ')='test'
space              spaces                    space(5)='     '
str                float->char               str(5.6,12,2)='        5.60'
stuff              insert chars within str   stuff('onetwothree',4,3,'-----')='one-----three'
substring          get piece of string       substring('sybase',1,2)='sy'
upper              upper case                upper('one')='ONE'

Transact SQL: misc functions

convert          convert between data types      convert(float,'5.50')=5.50
suser_name()     current login id
getdate()        current date

Transact SQL: date/time functions

datepart*        get part of a date         datepart(MM,'10/21/98')=10
dateadd*         manipulate a date          dateadd(DD,10,'10/21/98')= 10/31/98
getdate          todays date and time       getdate()=Nov 16 1998-2000  7:27PM

* date parts are MM,DD,YY,HH,MI,SS,MS

Its a QnA time

I will be discussing 36 most probable interview questioner one could expect for Sybase SQL developer or similar role


What is stored procedure? How many maximum parameter can pass through it?

It is a db objects that exists independly of the table ,Which contain a set of query. Maximum paraperters pass through it is 255

what is Advantage of stored procedure?
faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency

what is Trigger? how many trigger can a table have and what are?
It is internal part of a statement that fired when insert,update,delete operation taken place.Three trigger,Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
What is magic table?
The table that exists within a trigger named as Inserted and deleted are Magic table
what is view? how many table can join for creating view?
It is logical way of looking at physical data located in a table, Upto 16

how will you add a column and drop a column?
Alter table table name [add][drop] column name
explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a cursor

compare WHERE clause and HAVING clause?
Where clause cannot have Aggeregate function ,and it select the row before the group by function .having select the row after the group by
What is dead lock?
A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources.

What is sp_dboption?
It is used to enable or disable db option for your database

What is CURSOR?
It is a pointer , use for row by row operation
Why we should go for Deallocating the cursors?
When we Deallocate the cursor so that server will clear the memory space occupied by the cursor we can use that space for some other action

How many index can have for a table?

What is Clustered and Nonclustered index?
When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater
How do I get the Duplicate rows from a table?
select * from table group by column having count(*)>1
While creating index I forgot to tell what type what will happen?
nonclustered index

Compare Primary key and Unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

What is BCP?and Types?
It is used for import and export the values from table to os file and vice-ver. Fast bcp and Slow bcp

Compare left and Right Outer Join?
For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL

What is Hotspot?
Whan a multipe processes attempt to modify a same page in table

How do I force the lock?
Using Holdlock

What is Isolation levels and explain them?
The categories of locking behavior within transaction that are defined by ANSI,Level are 0,1,2,3

how to get Yesterday’s date?

What is –I option in Sybase tools?
It is the name of the interface file to use when trying to find a server to connect to

How do u copy the output results into a file?
isql -Usa -P -S -oos_file_name

If I want current database name?

What are the different types of lock in Sybase?
Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.
Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.

List out all System procedure in your server?
select name from sysobjects where type=’P’
There’s a performance issue What will be your very first step towards the solution?
First you need to identify which query is creating the problem and than to identify where the problem lies in that query. Query that is taking maximum time is the first you want to consider for the optimization. To check which query is taking maximum time to execute place getdate() both before and after the query. Identity the type of the query whether it is a data look-up query, or data modification query.
What is @@rowcount?
returns the no of rows affected by the preceding query

How do you check whether the rollback was correct?
By checking the @@trancount value before rollback statement execute

What is Roll Forward and Roll Back?
Roll forward : commited transaction not written to data area are rolled forward into the data.
Roll back:uncommited transaction rollback all data modification done with in transaction are reversed

Compare Delete and Truncate?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

How many columns can table have?

How will you find second maximum value in a table?
select max(column1) from table where column1<(select max(column1) from table)

I strongly recommend to all my users that for further reading and reference of Sybase ASE 12.5 please refer to


Ruby Rocks :)

Today I am going to share my very first experience with RubyOnRails, and to describe my feelings its fabulous.

Lets begin

What you need to know already?

  • You should be familiar with any MVC pattern
  • Should have knowledge of Ruby
  • Should know DB & Web server fundamentals

I guess thats more then enough to begin rails journey.

To start with you need following software installed on your box, 🙂 I ma LILO fan so all below configuration and are Linux based. But based on this understanding one can quickly start writing on any other OS too.

Software Needed

  • ruby 1.8.4
  • rails 1.2.3

Once installation and configuration done

rails test_app

cd test_app

vi config/database.ym

add your db credential

script/generate migration add_formelements

> create db/migrate
> create db/migrate/001_add_formelements.rb

vim db/migrate/001_add_formelements.rb

Add code
class AddUsers < ActiveRecord::Migration
def self.up
create_table “formelements” do |t|
t.column “user”, :string
t.column “email”, :string
t.column “password”, :string
t.column “passordreminder”, :text

def self.down
drop_table “

rake db:migrate

== AddUsers: migrating ========================================================
— create_table(“formelements”)
-> 0.0234s
== AddUsers: migrated (0.0236s) ===============================================

script/generate scaffold Formelement

exists app/controllers/
exists app/helpers/
exists app/views/formelements
create app/views/layouts/
create test/functional/
dependency model
create app/models/
create test/unit/
create test/fixtures/
create app/models/formelement.rb
create test/unit/formelement_test.rb
create test/fixtures/formelement.yml
identical app/views/formelements/_form.rhtml
create app/views/formelements/list.rhtml
create app/views/formelements/show.rhtml
create app/views/formelements/new.rhtml
create app/views/formelements/edit.rhtml
create app/controllers/formelements_controller.rb
create test/functional/formelements_controller_test.rb
create app/helpers/formelements_helper.rb
create app/views/layouts/formelements.rhtml
create public/stylesheets/scaffold.css



That’s all enjoy!

Hope you have grate time doing above exercise.

Check this out


Perl GMAIL Feed

#!/usr/bin/env perl
use warnings;
use strict;


Checks if there are new unread messages in your GMail Inbox.

=head1 USAGE

$ perl

############## Configuration ##############

# Change this to your correct username.
use constant GMAIL_USERNAME => “username”;
# Change this to your correct password.
use constant GMAIL_PASSWORD => “password”;

########## Don’t change anything below this. ##########

use LWP::UserAgent;
use XML::Atom::Feed;

my $fetcher = LWP::UserAgent->new();

my $request = HTTP::Request->new(
‘GET’   => “”,
$request->authorization_basic(GMAIL_USERNAME, GMAIL_PASSWORD);

my $response = $fetcher->request($request);

if (! $response->is_success())
die(“Unsuccessful in trying to talk to GMail”);

my $content = $response->content;
my $feed = XML::Atom::Feed->new(\$content);
my @new_messages = $feed->entries();

my $i = 1;
foreach my $message(@new_messages)
print join(“\t”, $i, $message->author->name,
$message->title), “\n”;

# The End

MYSQL replication in the same box

MYSQL replication in the same box


MYSQL 5.0.17 standard (SLAVE) –

MYSQL 4.1.1 standard (MASTER) – MASTER running on port 3306 SLAVE running on port 3308 MASTER (my.cnf)
server-id = 1
log-bin SLAVE (my.cnf)
server-id = 2
master-host = localhost
master-user = root
master-password = mysql
master-port = 3306

replicate-do-db = dbrep

to check the MASTER status on mysql cosole execute this querymysql > SHOW MASTER STATUS;

mysql > grant replication slave, replication client,file,super,reload,select on *.* to root@’%hostname%’ identified by ‘mysql’; to check the SLAVE status on mysql cosole execute this querymysql > SHOW SLAVE STATUS;

mysql > grant all on *.* to root@%hostname% identified by ‘mysql’; mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’master_user_name’,

-> MASTER_PASSWORD=’master_pass’,

-> MASTER_LOG_FILE=’recorded_log_file_name’,

-> MASTER_LOG_POS=recorded_log_position;
Above mentioned query can be used to chnage the slave info at runtime. restart both MYSQL SLAVE & MASTER On slave execute the command
mysql > start slave; Now create the database at MASTER and start creating tables and inserting values into it. Check the slave it started replicating all those tables. Isn’t it simple. )