Skip to content

sql Learnings

May 10, 2012
tags:

in oracle we have these- not in mysql.
Lead over() partition, rank over() partition.

charindex (‘j’, p1) != -1 etc.

Transpose the data -> Row to Column : 11g uses pivot function or Decode in older versions. decode function has the functionality of an IF-THEN-ELSE statement

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency

3NF – primary key, fkey , remove non key dependencies , remove multi valued dependencies.

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid;

select u1.name, u2.name
FROM household h
LEFT JOIN adult a1 on a1.aID = h.a1
LEFT JOIN user u1 on u1.ID = a1.aid
LEFT JOIN adult a2 on a2.aID = h.a2
LEFT JOIN User u2 ON u2.ID = a2.aid

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

select salary from emp e1 where (n-1)=(select count(*) from emp where salary >= e1.salary )

When the subquery returns even one null, NOT IN will not match any rows.
Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned

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 non-clustered index by default. Another major difference is that, primary key does not allow NULLs, but unique key allows one NULL only.

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: