Thursday, July 10, 2014

Learning curve: SQL writing by Unlocking HR schema

Its been a long time since I have studied anything with enthusiasm or a goal to achieve. The last time was when I took MarkLogic CCP but I don’t remember anything on that now, I never got an opportunity to work on it, not even a POC. Now I’m part of study group which has a goal, to get certified in SQL writing.

The learning is exiciting after all the java codes I write and debug its a refreshing change to use SQL. Wait a minute, does that mean I’m not using any SQL database at the back end? No, Yes – my application uses Oracle 11g but I’m not having any SQL developer to work with it, rarely I open SQL + to check if password is to expire.

The bad part is that I have to fill boring tables to query on them, quering is natural to human but not recording :D Some guy said “I’ll find the laziest person to do toughest job so he finds a shortcut” guess he was rit! I found the lazy easy way to query without to having to record.

Unlock the HR user

So I found there is a user called HR who is locked and kept in Oracle 11g Express edition by default. The user has around 7 tables pre-loaded with records – Jackpot! all you have to do is to issue the simplest statement by logging in an user with admin rights, say – SYSTEM or SYS (depends on version and installation)

ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;

Novice tip: Not just 11g its available in many versions; yes as you see password for HR user is password. Done, if you get a message that reads ‘User altered’ hi-five yourself else bring that hand to face and do a face-palm your system admin might have deleted that account