Satyaki De - SQL Exploration

I'm not habituated with this blogging skill - since i'm a not a very old here.

I may be wrong - but today after reading many blogs - i felt we are really missing something.

Now, We all are alone and desperately want to get cordial relationship and attention which we lacks in our home or with our childhood friends(Not always true...).

Question is - why are we going for this kind of deliberate exposure? I think we are miserably lonely even we have cell phone, mail and other latest gizmo.

I'm not pessimistic. But, this is what currently happening among all of us. Being a software developer i could hardly get my time to spent with my family & friends. And, moreover the stress has a strong impact in our personal life, too.

I have to attend office party quite frequently. But, did we really enjoy that? To the some extent yes. But, short lived.

But, think about your childhood days where you and your most beloved friends or relatives enjoyed every seconds of events like Pre-Puja Shopping, Mohaloya (Auspicious days for almost all the Bengali) etc. In Mohaloya, we wake up in the early morning. Some senior person from our family turned on the radio. Everyone sit and listening it with home made snacks & privileged tea.

Think about those days when we used to fight with our neighbors as part of a big battle between Mohon Bagan & East Bengal by listening it in someone's house. It is still presents but transformed into much more corporate way. Now, we have latest cell where i can listen live commentary with my new cool blue tooth handset. After all we are developing in a massive rate. But lost the cordiality, the essence of bonding.....

Don't you think - that is most cherished treasure you have spent?

I'm not a orthodox person and never support that old is always gold. But, few things certainly cannot be overlooked, and specially the relationship with others. In many cases, we are very much protective and maintains some strict boundaries.

Now, we also ignore our people even if we find them in trouble. We keep our eyes closed - so that at the end of the day you don't have to feel guilty about what you have done. Rather, you can convince yourself by saying - "You actually didn't notice - what was happen there". In some situation situation force us to do so. But, still there are instances when we could act - but escaped without paying attention due to complex and aggressive environment. Are we really progressing? When we are in danger - we expect to get help from others. But, when somebody really needs help - we forget our old days.

May be many of my new friends from here who are reading my blog certainly differ with me.

I would like to hear from you guys. It's up to you to decide. Will you?

Satyaki De.

Hi!

scott>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Elapsed: 00:00:00.09

scott>
scott>create table test_dummy
2     (
3       a       varchar2(10)
4     );

Table created.

Elapsed: 00:00:05.00
scott>
scott>
scott>alter table &tab add (& col varchar2 ( 10 ));
Enter value for tab: test_dummy
Enter value for col: b
old   1: alter table &tab add (& col varchar2 ( 10 ))
new   1: alter table test_dummy add (b varchar2 ( 10 ))

Table altered.

Elapsed: 00:00:01.19

scott>
scott>desc test_dummy;
Name                 Null?    Type
-------------------- -------- --------------
A                             VARCHAR2(10)
B                             VARCHAR2(10)

scott>
scott>SELECT lag(null, 1, d.dname)
over (partition by e.deptno order by e.ename) as dname,
2         e.ename
3  from emp e, dept d
4  where e.deptno = d.deptno
5  ORDER BY D.dname, e.ename;

DNAME          ENAME
-------------- ----------
ACCOUNTING     CLARK
KING
MILLER
RESEARCH       ADAMS
FORD
JONES
SCOTT
SMITH
SALES          ALLEN
BLAKE
JAMES

DNAME          ENAME
-------------- ----------
MARTIN
TURNER
WARD

14 rows selected.

Elapsed: 00:00:00.52
scott>

SQL> create or replace view v_emp
2 as
3 select ename
4 from emp
5 /
View created.

SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
SQL>
SQL> create or replace view v_emp
2 as
3 select cast (ename as varchar2 (30)) ename
4 from emp
5 /
View created.

SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)

create or replace view temp_vv
as
select replace(ename,' ') ename
from (
select rpad(ename,100) ename
from emp
);

satyaki>
satyaki>select e.empno,e.deptno,d.loc "DEPT_10"
2  from emp e, dept d
3  where e.deptno = d.deptno
4  and d.deptno = 10;

EMPNO     DEPTNO DEPT_10
---------- ---------- -------------
7782         10 NEW YORK
7839         10 NEW YORK
7934         10 NEW YORK

Elapsed: 00:00:00.04
satyaki>
satyaki>select e.empno,e.deptno,d.loc "DEPT_OTH"
2  from emp e, dept d
3  where e.deptno = d.deptno
4  and e.deptno not in (10);

EMPNO     DEPTNO DEPT_OTH
---------- ---------- -------------
7369         20 DALLAS
7876         20 DALLAS
7566         20 DALLAS
7788         20 DALLAS
7902         20 DALLAS
7900         30 CHICAGO
7844         30 CHICAGO
7654         30 CHICAGO
7521         30 CHICAGO
7499         30 CHICAGO
7698         30 CHICAGO

11 rows selected.

Elapsed: 00:00:00.04
satyaki>
satyaki>
satyaki>select a.empno,(
2                   select d.loc
3                   from emp e, dept d
4                   where e.deptno = d.deptno
5                   and e.empno = a.empno
6                   and d.deptno = 10
7                 ) "DEPT_10" ,
8                 (
9                   select d.loc
10                   from emp e, dept d
11                   where e.deptno = d.deptno
12                   and e.empno = a.empno
13                   and d.deptno not in (10)
14                 ) "DEPT_OTH"
15  from emp a
16  order by a.empno;

EMPNO DEPT_10       DEPT_OTH
---------- ------------- -------------
7369               DALLAS
7499               CHICAGO
7521               CHICAGO
7566               DALLAS
7654               CHICAGO
7698               CHICAGO
7782 NEW YORK
7788               DALLAS
7839 NEW YORK
7844               CHICAGO
7876               DALLAS

EMPNO DEPT_10       DEPT_OTH
---------- ------------- -------------
7900               CHICAGO
7902               DALLAS
7934 NEW YORK

14 rows selected.

Elapsed: 00:00:00.30
satyaki>