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

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:

\f’\t’

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:

\f’\t’
\a
\o myfile.txt
select * from big_table;
\o

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.

\H
\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;

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
install.

– 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.

– 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.

– 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.

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).

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:

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.

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

IDENTITY

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.

3)
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.
4)
What is magic table?
The table that exists within a trigger named as Inserted and deleted are Magic table
5)
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

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

8)
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
9)
What is dead lock?
A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources.

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

11)
What is CURSOR?
It is a pointer , use for row by row operation
12)
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

13)
How many index can have for a table?
250

14)
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
15)
How do I get the Duplicate rows from a table?
select * from table group by column having count(*)>1
16)
While creating index I forgot to tell what type what will happen?
nonclustered index

17)
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.

18)
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

19)
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

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

22)
How do I force the lock?
Using Holdlock

23)
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

24)
how to get Yesterday’s date?
dataadd(dd,-1,getdate())

25)
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

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

27)
If I want current database name?
db_name()

28)
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.

29)
List out all System procedure in your server?
select name from sysobjects where type=’P’
30)
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.
31)
What is @@rowcount?
returns the no of rows affected by the preceding query

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

33)
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

34)
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.

35)
How many columns can table have?
250

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

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.