Hbase Architecture:

------------------------------------------------------------------------------------------------------------------

Available as

1. Stand Alone mode

2. Psuedo Distributed mode

3. Fully Distributed mode

2.2.2. Distributed

Distributed mode can be subdivided into distributed but all daemons run on a single node -- a.k.a pseudo-distributed-- and fully-distributed where the daemons are spread across all nodes in the cluster 

Distributed modes require an instance of the Hadoop Distributed File System (HDFS). 

2.2.2.1. Pseudo-distributed

A pseudo-distributed mode is simply a distributed mode run on a single host.

First, setup your HDFS in pseudo-distributed mode.

Next, configure HBase. Below is an example conf/hbase-site.xml. This is the file into which you add local customizations

Note that the hbase.rootdir property points to the local HDFS instance.

Note

Let HBase create the hbase.rootdir directory. If you don't, you'll get warning saying HBase needs a migration run because the directory is missing files expected by HBase (it'll create them if you let it).

 Pseudo-distributed Configuration File

Below is a sample pseudo-distributed file for the node h-24-30.example.com. hbase-site.xml

<configuration>

  ...

  <property>

    <name>hbase.rootdir</name>

    <value>hdfs://h-24-30.sfo.stumble.net:8020/hbase</value>

  </property>

  <property>

    <name>hbase.cluster.distributed</name>

    <value>true</value>

  </property>

  <property>

    <name>hbase.zookeeper.quorum</name>

    <value>h-24-30.sfo.stumble.net</value>

  </property>

  ...

</configuration>

. Pseudo-distributed Extras

 Startup

To start up the initial HBase cluster...

% bin/start-hbase.sh

To start up an extra backup master(s) on the same server run...

% bin/local-master-backup.sh start 1

... the '1' means use ports 60001 & 60011, and this backup master's logfile will be at logs/hbase-${USER}-1-master-${HOSTNAME}.log.

To startup multiple backup masters run...

% bin/local-master-backup.sh start 2 3

You can start up to 9 backup masters (10 total).

To start up more regionservers...

% bin/local-regionservers.sh start 1

where '1' means use ports 60201 & 60301 and its logfile will be at logs/hbase-${USER}-1-regionserver-${HOSTNAME}.log.

To add 4 more regionservers in addition to the one you just started by running...

% bin/local-regionservers.sh start 2 3 4 5

This supports up to 99 extra regionservers (100 total).

2.2.2.1.2.2. Stop

Assuming you want to stop master backup # 1, run...

% cat /tmp/hbase-${USER}-1-master.pid |xargs kill -9

Note that bin/local-master-backup.sh stop 1 will try to stop the cluster along with the master.

To stop an individual regionserver, run...

% bin/local-regionservers.sh stop 1

                

2.2.2.2. Fully-distributed

For running a fully-distributed operation on more than one host, make the following configurations. In hbase-site.xml, add the property hbase.cluster.distributed and set it to true and point the HBase hbase.rootdir at the appropriate HDFS NameNode and location in HDFS where you would like HBase to write data. For example, if you namenode were running at namenode.example.org on port 8020 and you wanted to home your HBase in HDFS at /hbase, make the following configuration.

<configuration>

  ...

  <property>

    <name>hbase.rootdir</name>

    <value>hdfs://namenode.example.org:8020/hbase</value>

    <description>The directory shared by RegionServers.

    </description>

  </property>

  <property>

    <name>hbase.cluster.distributed</name>

    <value>true</value>

    <description>The mode the cluster will be in. Possible values are

      false: standalone and pseudo-distributed setups with managed Zookeeper

      true: fully-distributed with unmanaged Zookeeper Quorum (see hbase-env.sh)

    </description>

  </property>

  ...

</configuration>

2.2.2.2.1. regionservers

In addition, a fully-distributed mode requires that you modify conf/regionservers. The Section 2.4.1.2, “regionservers” file lists all hosts that you would have runningHRegionServers, one host per line (This file in HBase is like the Hadoop slaves file). All servers listed in this file will be started and stopped when HBase cluster start or stop is run.

----------------------------------------------------------------------------------------------------------------

has own webui

--------------------------------------------------------------------------------------------------------------------

Distributed Column Oriented Database - ontop of hdfs

ROW                                                                                                Column

1. OLTP                                                                                 1. OLAP (has a single column with all values)

2. Single row insert                                                         2. Aggregation over

3.Small number of rows and columns                    3. High Compression

----------------------------------------------------------------------------------------------------------------

HBase                                                                                                             RDBMS

1. Schema-less                                                                  1. Schema

2. for Wide Tables                                                           2. Thin tables

3. Denormalized                                                              3. Normalised

-----------------------------------------------------------------------------------------------------------

HBase                                                                                                   HDFS

1. Low latency access                                                     1. High Latency Access

2. Random access                                                            2. No concept of Random access

(e.g. Facebook )

--------------------------------------------------------------------------------------------------------------

                                                                HBase Architecture

1. Master Server - assign region, handling load balancing, Finding out where the data is, sharding

a) when tables get bigger it is splitted at the middle and distributed across the region server uniformally

b) when slow , just add more region server

zookeeper - this is the guy to whom master talks with

2. Region Server 

Table

region

store

memstore(file gets stored first and then flushed to hfile)

hfile(store file)

Column Family for grouping similar data

Pseudo mode:

zookeper is for quorum management.

Download Apache Hbase

1. From Apache website

2. unpack the tar

3. move to /usr/local/hbase (with app permission to the appropriate user)

export $HBASE_HOME

export $PATH

Configuration:

vim /usr/local/hbase/conf/hbase-env.sh

edit JAVA_HOME

vim /usr/local/hbase/conf/hbase-site.xml

hbase.rootdir=hdfs://hw1:10001/hbase - points hbase to hdfs and the directory is the shared directory used by all region server

#hbase.zookeeper.quorum=zoo1,zoo2 - used to point to zookeeper node

#hbase.cluster.distributed=false - for pseudo and stand alone mode

Region Server

vim vim /usr/local/hbase/conf/regionservers

localhost for pseudo and ip for distributed mode

command is start-hbase.sh

webui : hw1:60010

firing region servers - command is

local-regionservers.sh start 1 2 3

Creating TABLE

hbase shell

create 'htest' ,'cf'

put 'htest','r1','cf:c1','v1'

put 'htest','r1','cf:c2','v2'

put 'htest','r1','cf:c3','v3'

scan 'htest'

cells are versioned in hbase table

get 'htest','r2'

put 'htest','r2','cf:c2','v2updated'

get 'htest','r2'

delete 'htest','r3','cf:c3'

scan 'htest'

disable 'htest'

drop 'htest'

HBASE DATA-ACCESS

1. JAVA

2. HBASE SHELL

both 1&2 uses ClientAPI

3. REST - for text

4. AVRO - for Binary

5. THRIFT - for both Text and Binary

6. HIVE

7. PIG

8. MapReduce

4,5 are interactive clients

6,7,8 for Batch Processing Clients

Loading HBASE

1. IMPORT TSV - for tab seperated values

2. Complete Bulk Load

3. MapReduce

4. Pig and Hive

Configuring Fully Distributed HBASE

1. vim /usr/local/hbase/conf/hbase-site.xml

Properties:

hbase.rootdir

same for region server

hbase.cluster.distributed - true

hbase.zookeeper.quorum - HNHBMaster

hbase.zookeeper.property.clientport -2181

hbase.zookeeper.property.datadir - path to the directory where zookeeper stores its data

these configurations are to be scp to all region server too

start-hbase.sh

fires all services

Hands on SQL Queries

create table employee( empid varchar(9) NOT NULL, fname varchar(8), bdate DATE,
salary number(7) not null, dno number(1) not null);
insert into employee values( 4, 'rathna' , '10-dec-1955' ,1200000 , 4);
select * from employee where salary > 200000;
update employee set salary=salary+200 where salary > 10000;
alter table employee rename to newemp;
alter table newemp add sex varchar2(5) not null;
delete from newemp where salary>1;
alter table newemp modify sex number(5);
alter table newemp drop column sex;
alter table newemp add constraint prim primary key(empid);
create table empnew as select * from newemp;
alter table newemp add constraint fk foreign key(empid) references empnew(empid);
create index ix on newemp (empid,fname);
drop index ix;

create sequence newempseq
increment by 1
start with 100
minvalue 0
maxvalue 10000
cache 10;

drop sequence newempseq;
create view idname as select empid,fname from newemp;
drop view idname;
describe idname;
select * from user_constraints;
select distinct * from newemp;
select * from newemp where salary between 1000000 and 1300000;
select * from newemp where salary in (1000000 , 1300000);
select * from newemp where fname like '%na' or fname like '%th' or fname like '%en';
select * from newemp where salary not between 1000000 and 1300000;
select * from newemp where salary not in (1000000 , 1300000);
select * from newemp where fname not like '%na' and fname not like '%th' and fname like '%en';


SQL> select * from newemp where sex is null
  2  ;

EMPID     FNAME    BDATE          SALARY        DNO SEX
--------- -------- --------- ----------- ---------- -----
2         praveen  16-NOV-87    $500,000          2
4         rathna   10-DEC-55  $1,200,000          4
3         sambath  25-DEC-54  $1,000,000          3

SQL> select * from newemp where sex is not null
  2  ;

no rows selected


SQL> select * from newemp where fname like '%r%';

EMPID     FNAME    BDATE          SALARY        DNO SEX
--------- -------- --------- ----------- ---------- -----
2         praveen  16-NOV-87    $500,000          2
4         rathna   10-DEC-55  $1,200,000          4


SQL> select * from emp1 where fname between 'p' and 't';

EMPID     FNAME    BDATE          SALARY        DNO SEX
--------- -------- --------- ----------- ---------- -----
2         praveen  16-NOV-87    $500,000          2
4         rathna   10-DEC-55  $1,200,000          4
3         sambath  25-DEC-54  $1,000,000          3


equivalent is ===>>>

select * from emp1 where fname > 'p' and fname < 't';


SQL> create table emp1 as select * from newemp;

Table created.

SQL> select ROWNUM, fname from emp1 where ROWNUM=2;

no rows selected


SQL> select * from emp1 order by fname;

EMPID     FNAME    BDATE          SALARY        DNO SEX
--------- -------- --------- ----------- ---------- -----
5         kani     30-SEP-91    $100,000          5 f
2         praveen  16-NOV-87    $500,000          2
4         rathna   10-DEC-55  $1,200,000          4
3         sambath  25-DEC-54  $1,000,000          3

SQL> select * from emp1 order by empid;

EMPID     FNAME    BDATE          SALARY        DNO SEX
--------- -------- --------- ----------- ---------- -----
2         praveen  16-NOV-87    $500,000          2
3         sambath  25-DEC-54  $1,000,000          3
4         rathna   10-DEC-55  $1,200,000          4
5         kani     30-SEP-91    $100,000          5 f


SQL> select * from emp1 order by 2 desc;

EMPID     FNAME    BDATE          SALARY        DNO SEX
--------- -------- --------- ----------- ---------- -----
3         sambath  25-DEC-54  $1,000,000          3
4         rathna   10-DEC-55  $1,200,000          4
2         praveen  16-NOV-87    $500,000          2
5         kani     30-SEP-91    $100,000          5 f

-------------------------------------------------------------------

column alias


SQL> select fname as "FirstName" , bdate "Birthday", salary from emp1 ;

FirstNam Birthday       SALARY
-------- --------- -----------
praveen  16-NOV-87    $500,000
rathna   10-DEC-55  $1,200,000
sambath  25-DEC-54  $1,000,000
kani     30-SEP-91    $100,000


SQL> select fname as "FirstName" , bdate "Birthday", salary from emp1 order by "FirstName";

FirstNam Birthday       SALARY
-------- --------- -----------
kani     30-SEP-91    $100,000
praveen  16-NOV-87    $500,000
rathna   10-DEC-55  $1,200,000
sambath  25-DEC-54  $1,000,000


-------------------------------------------------------------------------------
order by anamoly


SQL> select distinct fname , bdate from emp1 order by salary;
select distinct fname , bdate from emp1 order by salary
                                                 *
ERROR at line 1:
ORA-01791: not a SELECTed expression


SQL> select  fname , bdate from emp1 order by salary;

FNAME    BDATE
-------- ---------                                            >>>>Distinct!!!
kani     30-SEP-91
praveen  16-NOV-87
sambath  25-DEC-54
rathna   10-DEC-55

SQL> select  fname , bdate from emp1 order by salary desc;

FNAME    BDATE
-------- ---------
rathna   10-DEC-55
sambath  25-DEC-54
praveen  16-NOV-87
kani     30-SEP-91

-------------------------------------------------------------------------------

Null value and Order by

SQL> select  fname , bdate, salary, sex from emp1 order by sex desc;

FNAME    BDATE          SALARY SEX
-------- --------- ----------- -----
praveen  16-NOV-87    $500,000
rathna   10-DEC-55  $1,200,000
sambath  25-DEC-54  $1,000,000
kani     30-SEP-91    $100,000 f

SQL> select  fname , bdate, salary, sex from emp1 order by sex ;

FNAME    BDATE          SALARY SEX
-------- --------- ----------- -----
kani     30-SEP-91    $100,000 f
sambath  25-DEC-54  $1,000,000
praveen  16-NOV-87    $500,000
rathna   10-DEC-55  $1,200,000

SQL> select  fname , bdate, salary, sex from emp1 order by sex null first;
select  fname , bdate, salary, sex from emp1 order by sex null first
                                                          *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select  fname , bdate, salary, sex from emp1 order by sex nulls first;

FNAME    BDATE          SALARY SEX
-------- --------- ----------- -----
praveen  16-NOV-87    $500,000
rathna   10-DEC-55  $1,200,000
sambath  25-DEC-54  $1,000,000
kani     30-SEP-91    $100,000 f

-----------------------------------------------------------------------------
SQL> select upper(fname), upper("firstname") from emp1;
select upper(fname), upper("firstname") from emp1
 s                          *
ERROR at line 1:
ORA-00904: "firstname": invalid identifier


SQL> select upper(fname), upper('firstname') from emp1;

UPPER(FN UPPER('FI
-------- ---------
PRAVEEN  FIRSTNAME
RATHNA   FIRSTNAME
SAMBATH  FIRSTNAME
KANI     FIRSTNAME

------------------------------------------------------------------------------


SQL> select rpad(fname,10,'*') from emp1;

RPAD(FNAME,10,'*')
----------------------------------------
praveen***
rathna****
sambath***
kani******

>>>>>>>>>>> also we have left pad function lpad

------------------------------------------------------------------------------
Dual table - Dummy table

-----------------------------------------------------------------------------

SQL> select substr(fname, 1,3) from emp1;

SUBSTR(FNAME
------------
pra
rat
sam
kan

SQL> select substr(fname, 4) from emp1;

SUBSTR(FNAME,4)
--------------------
veen
hna
bath
i
-----------------------------------------------------------------------------


SQL> select fname,length(fname) from emp1;

FNAME    LENGTH(FNAME)
-------- -------------
praveen              7
rathna               6
sambath              7
kani                 4

------------------------------------------------------------------------------

SQL> select fname, instr(fname,'r') from emp1;

FNAME    INSTR(FNAME,'R')
-------- ----------------
praveen                 2
rathna                  1
sambath                 0
kani                    0

----------------------------------------------------------------

SQL> select fname from emp1 where instr(fname,'n') >0
  2  ;

FNAME
--------
praveen
rathna
kani
-------------------------------------------------------------


SQL> select fname , substr(fname, instr(fname,'.')-1 ) firstname, substr(fname,1, instr(fname,'.')-2 ) inal  from emp1;

FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------

INAL
--------------------------------------------------------------------------------

praveens.
s.
praveen

rathnas.
s.
rathna

FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------

INAL
--------------------------------------------------------------------------------


sambaths.
s.
sambath

kanis.
s.

FNAME
------------------------------
FIRSTNAME
--------------------------------------------------------------------------------

INAL
--------------------------------------------------------------------------------

kani

<<<<<<<<<<<<<<<<<<<<<<<----------------------------------------------->>>>>>>>>

Concatenation Operator:


SQL> update emp1 set fname= fname || 's.' ;

-------------------------------------------------------------------------------

SQL> select concat(fname,empid) from emp1;

CONCAT(FNAME,EMPID)
---------------------------------------
praveens.2
rathnas.4
sambaths.3
kanis.5


SQL> select fname || 'Employee id is ' || empid || ' and salary is ' || salary f
rom emp1;

FNAME||'EMPLOYEEIDIS'||EMPID||'ANDSALARYIS'||SALARY
--------------------------------------------------------------------------------

praveens.Employee id is 2 and salary is 500000
rathnas.Employee id is 4 and salary is 1200000
sambaths.Employee id is 3 and salary is 1000000
kanis.Employee id is 5 and salary is 100000
-------------------------------------------------------------------------------


SQL> select replace(fname,'r','1') from emp1;

REPLACE(FNAME,'R','1')
------------------------------
p1aveens.
1athnas.
sambaths.
kanis.

-------------------------------------------------------------------------------

SQL> select substr(fname,-5,3) from emp1;

SUBSTR(FNAME
------------
een
hna                    >>>>>>>>>> displays  3 letters from last 5 position
ath
ani
------------------------------------------------------------------------------

Finding the count of occurance of a substring using length and replace


SQL> select ( length('fredm fedm tedm bread') - length( replace( 'fredm fedm ted
m bread','edm',null) ) ) /3  as occur from dual;

     OCCUR
----------
         3
-----------------------------------------------------------------------------


SQL> select 'The absolute value of -768763 is ' || abs(-768763) from dual;

'THEABSOLUTEVALUEOF-768763IS'||ABS(-768
---------------------------------------
The absolute value of -768763 is 768763

-------------------------------------------------------------------------------


SQL> select sign(-212),sign(0) from dual;

SIGN(-212)    SIGN(0)
---------- ----------
        -1          0
------------------------------------------------------------------------------

SQL> select round(234.349,2),trunc(31231.3221,2) from dual;

ROUND(234.349,2) TRUNC(31231.3221,2)
---------------- -------------------
          234.35            31231.32


SQL> select mod(23,8) from dual;

 MOD(23,8)
----------
         7

SQL> select mod(5,8) from dual;

  MOD(5,8)
----------
         5

SQL> select remainder(25,8) from dual;

REMAINDER(25,8)
---------------
              1

------------------------------------------------------------------------------
NVL - null value


SQL> select sex, nvl(sex,'Not Specified') from emp1;

SEX   NVL(SEX,'NOTS
----- -------------
      Not Specified
      Not Specified
      Not Specified
f     f

------------------------------------------------------------------------------
Decode - Expands value


SQL> select sex, decode(sex, null,'not specified'), decode(sex,'f','Female') fro
m emp1;

SEX   DECODE(SEX,NU DECODE
----- ------------- ------
      not specified
      not specified
      not specified
f                   Female


select distinct state,
Decode(state, 'ny' , 'New York',
'nj' , 'New Jersey',
 'other')
from tablename;


-------------------------------------------------------------------------------
SQL> select fname, salary ,
  2  case
  3  when salary > 1000000 then 1
  4  else salary
  5  end "My case"
  6  from emp1;

FNAME                              SALARY    My case
------------------------------ ---------- ----------
praveens.                          500000     500000
rathnas.                          1200000          1
sambaths.                         1000000    1000000
kanis.                             100000     100000


SQL> select fname, salary ,
  2  case
  3  when salary > 1000000 then 1
  4  when salary < 1000000 then 100
  5  when salary = 1000000 then 0
  6  else null
  7  end "Test case"
  8  from emp1;

FNAME                              SALARY  Test case
------------------------------ ---------- ----------
praveens.                          500000        100
rathnas.                          1200000          1
sambaths.                         1000000          0
kanis.                             100000        100


SQL> select fname, salary
  2  from emp1 where
  3  salary * ( case
  4  when salary > 1000000 then 1
  5  when salary < 1000000 then 100
  6  when salary = 1000000 then 0
  7  else 0
  8  end ) > 0;

FNAME                              SALARY
------------------------------ ----------
praveens.                          500000
rathnas.                          1200000
kanis.                             100000

------------------------------------------------------------------------------
 SQL> select count(sex) from emp1;

COUNT(SEX)
----------                                    >>> excludes null
         1

SQL> select count(*) from emp1;

  COUNT(*)
----------
         4

-----------------------------------------------------------------------------

SQL> select min(bdate), max(bdate) from emp1;

MIN(BDATE MAX(BDATE
--------- ---------
25-DEC-54 30-SEP-91


SQL> select min(fname), max(fname) from emp1;

MIN(FNAME)                     MAX(FNAME)
------------------------------ ------------------------------
kanis.                         sambaths.

-----------------------------------------------------------------------------

Using AVG and CASE

SQL> select avg( case when salary > 1000000 then 7735
  2  when salary < 1000000 then 8898
  3  when salary = 1000000 then 0
  4  else 6
  5  end) as AVG
  6  from emp1;

       AVG
----------
    6885.8
------------------------------------------------------------------------------

Group by and distinct return the same result
See what is ora-00979 - wowwwwwwwwwww!! i found the answer , it says every column in the select list except the aggregate must be repeated in the group by clause.


SQL> select fname,bdate,sex from emp1 where sex='m' group by sex,bdate,fname;

FNAME                          BDATE     SEX
------------------------------ --------- -----
sambaths.                      25-DEC-54 m
venkat                         13-SEP-13 m
praveens.                      16-NOV-87 m

Group by + order by - column in order by must be in selected list


SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname order by bdate;

FNAME                          BDATE     SEX
------------------------------ --------- -----
sambaths.                      25-DEC-54 m
rathnas.                       10-DEC-55 f
praveens.                      16-NOV-87 m
radhika                        28-SEP-91 f
kanis.                         30-SEP-91 f
venkat                         13-SEP-13 m
shanthini                      29-OCT-13 f

7 rows selected.

Having Clause - purpose is to filter group result


SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having bdate > '3
0-sep-1991' order by bdate;

FNAME                          BDATE     SEX
------------------------------ --------- -----
venkat                         13-SEP-13 m
shanthini                      29-OCT-13 f
>>>>>>>>>> where clause eliminate rows

>>>>>>>>>> columns in having clause must be either found in group by list or it should be a aggregate function


SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having count(fname) > 0;

FNAME                          BDATE     SEX
------------------------------ --------- -----
radhika                        28-SEP-91 f
rathnas.                       10-DEC-55 f
sambaths.                      25-DEC-54 m
shanthini                      29-OCT-13 f
venkat                         13-SEP-13 m
praveens.                      16-NOV-87 m
kanis.                         30-SEP-91 f

7 rows selected.

SQL> select fname,bdate,sex from emp1 group by sex,bdate,fname having count(fname) > 1;

FNAME                          BDATE     SEX
------------------------------ --------- -----
praveens.                      16-NOV-87 m

>>>>>>>>> constant such as text, number literal or function that doesnt take any parameter may be listed in the select list without being repeated in group by clause


SQL> select 'hi' greetings , fname, sex , sysdate Today , salary from emp1 group
 by fname,sex,salary;

GR FNAME                          SEX   TODAY         SALARY
-- ------------------------------ ----- --------- ----------
hi praveens.                      m     10-NOV-13     232434
hi rathnas.                       f     10-NOV-13    1200000
hi kanis.                         f     10-NOV-13     100000
hi venkat                         m     10-NOV-13      23213
hi sambaths.                      m     10-NOV-13    1000000
hi praveens.                      m     10-NOV-13     500000
hi shanthini                      f     10-NOV-13      23213
hi radhika                        f     10-NOV-13      45000

8 rows selected.


SQL> rename department to dept;

Table renamed.


Nested Aggregate!!!

SQL> select max(count(*)) from emp group by deptno;

MAX(COUNT(*))
-------------
            3

-------------------------------------------------------------------------------

<<<<<<<<<<<<<<<<<<<<<<<<Joins>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

1. Narrowing down results
SQL> select ename, salary, location,manager_no,sex from dept d, emp e where d.dept_no=e.deptno and (ename like 'a%' or ename like 's%') order by ename asc;

ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
adharsh                             87644 GHT1                     8 m
adhi                                89342 GHT1                     9 m
sam                                  1000 GHT1                     5
soumya                              43581 GHT1                     5 f
surya                               23231 GHT3                     7 f



SQL> select ename, salary, location,manager_no,sex from dept d join emp e using
(deptno);
select ename, salary, location,manager_no,sex from dept d join emp e using (dept
no)
                                                                            *
ERROR at line 1:
ORA-00904: "D"."DEPTNO": invalid identifier

>>> you should have the same column for using the USING keyword and also alias are not allowed

ON

SQL> select ename, salary, location,manager_no,sex from dept d join emp e on(e.d
eptno=d.dept_no);

ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen                             23213 GHT2                     6 m
sam                                  1000 GHT1                     5
kani                                43222 GHT3                     7 f
surya                               23231 GHT3                     7 f
naveensai                           25567 GHT1                     8 m
matlang                             67565 GHT1                     8 m
adharsh                             87644 GHT1                     8 m
adhi                                89342 GHT1                     9 m
soumya                              43581 GHT1                     5 f

9 rows selected.

Natural join :

SQL> select ename, salary, location,manager_no,sex from dept d join emp e on(e.
eptno=d.dept_no);

ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen                             23213 GHT2                     6 m
sam                                  1000 GHT1                     5
kani                                43222 GHT3                     7 f
surya                               23231 GHT3                     7 f
naveensai                           25567 GHT1                     8 m
matlang                             67565 GHT1                     8 m
adharsh                             87644 GHT1                     8 m
adhi                                89342 GHT1                     9 m
soumya                              43581 GHT1                     5 f

9 rows selected.

SQL> select ename, salary, location,manager_no,sex from dept natural join emp;


ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
praveen                             23213 GHT1                     5 m
sam                                  1000 GHT1                     5
kani                                43222 GHT1                     5 f
surya                               23231 GHT1                     5 f
naveensai                           25567 GHT1                     5 m
matlang                             67565 GHT1                     5 m
adharsh                             87644 GHT1                     5 m
adhi                                89342 GHT1                     5 m
soumya                              43581 GHT1                     5 f
praveen                             23213 GHT2                     6 m
sam                                  1000 GHT2                     6

ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
kani                                43222 GHT2                     6 f
surya                               23231 GHT2                     6 f
naveensai                           25567 GHT2                     6 m
matlang                             67565 GHT2                     6 m
adharsh                             87644 GHT2                     6 m
adhi                                89342 GHT2                     6 m
soumya                              43581 GHT2                     6 f
praveen                             23213 GHT3                     7 m
sam                                  1000 GHT3                     7
kani                                43222 GHT3                     7 f
surya                               23231 GHT3                     7 f

ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
naveensai                           25567 GHT3                     7 m
matlang                             67565 GHT3                     7 m
adharsh                             87644 GHT3                     7 m
adhi                                89342 GHT3                     7 m
soumya                              43581 GHT3                     7 f
praveen                             23213 GHT1                     8 m
sam                                  1000 GHT1                     8
kani                                43222 GHT1                     8 f
surya                               23231 GHT1                     8 f
naveensai                           25567 GHT1                     8 m
matlang                             67565 GHT1                     8 m

ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
adharsh                             87644 GHT1                     8 m
adhi                                89342 GHT1                     8 m
soumya                              43581 GHT1                     8 f
praveen                             23213 GHT1                     9 m
sam                                  1000 GHT1                     9
kani                                43222 GHT1                     9 f
surya                               23231 GHT1                     9 f
naveensai                           25567 GHT1                     9 m
matlang                             67565 GHT1                     9 m
adharsh                             87644 GHT1                     9 m
adhi                                89342 GHT1                     9 m

ENAME                              SALARY LOCATION        MANAGER_NO SEX
------------------------------ ---------- --------------- ---------- ----
soumya                              43581 GHT1                     9 f

45 rows selected.
-----------------------------------------------------------------------------

CARTESIAN PRODUCT

SQL> select count(*) from emp,dept;

  COUNT(*)
----------
        45
-----------------------------------------------------------------------------

Sub-Query

Scalar sub query aka single row sub query - returns one row and one column


SQL> select ename, sex, salary from emp where salary=(select max(salary) from emp);

ENAME                          SEX      SALARY
------------------------------ ---- ----------
soumya                         f         99999


SQL> select ename, sex, salary from emp where salary IN (select salary from emp
where ename like 's%');

ENAME                          SEX      SALARY
------------------------------ ---- ----------
sam                                       1000
soumya                         f         99999
surya                          f         34234
shanthini                      f         54323


SQL> select ename, sex, salary from emp where salary NOT IN (select salary from
emp where ename like 's%');

ENAME                          SEX      SALARY
------------------------------ ---- ----------
praveen                        m         23243
naveensai                      m         34453
kani                           f         34532

Subquery nesting max goes to a depth of 5 levels !!!

>>>>>>>>>Sub queries and join

SQL> select ename,sex,salary from emp where deptno IN (select dept_no from dept)
;

ENAME                          SEX      SALARY
------------------------------ ---- ----------
praveen                        m         23243
sam                                       1000
naveensai                      m         34453
kani                           f         34532
soumya                         f         99999
surya                          f         34234
shanthini                      f         54323

7 rows selected.


Finding the department with the highest number of employees


SQL> select deptno, count(*) from emp group by(deptno) having count(*)= ( select  max(count(empno)) from emp group by deptno);

    DEPTNO   COUNT(*)
---------- ----------
         4          2
         5          2
         3          2

SQL> select * from emp;

     EMPNO ENAME                              SALARY     DEPTNO SEX
---------- ------------------------------ ---------- ---------- ----
      1004 praveen                             23243          4 m
      1003 sam                                  1000          3
      1005 naveensai                           34453          5 m
      1006 kani                                34532          6 f
      1007 soumya                              99999          3 f
      1008 surya                               34234          4 f
      1009 shanthini                           54323          5 f

7 rows selected.


-------------------------------------------------------------------------------

Correlated subquery - it references the outer query plus it executes repeatedly also

EXISTS : checks if a particular value from a table exists in another table...!!

SQL> select ename, salary from emp e where EXISTS ( select 'X' from dept where e.deptno= dept_no);

ENAME                              SALARY
------------------------------ ----------
praveen                             23243
sam                                  1000
naveensai                           34453
kani                                34532
soumya                              99999
surya                               34234
shanthini                           54323

Finding the departments with less than 2 employees


SQL> select dept_no, dept_name, location from dept outer where 2 > (select count(*) from emp where emp.deptno=outer.dept_no);

   DEPT_NO DEPT_NAME       LOCATION
---------- --------------- ---------------
         6 HSE             GHT3
         7 IS              GHT3

7 rows selected.

-----------------------------------------------------------------------------
<<<<<<<<<<<<<<<<<<INLINE VIEWS AND SCALAR SUBQUERY EXPRESSIONS>>>>>>>>>>>>

Inline view allows to use a virtual table in from clause - useful in many ways some example are below


SQL> select deptno, d.location, d.manager_no from ( select deptno, count(*) from
 emp where rownum=1 group by(deptno) order by count(*) desc), dept d where d.dep
t_no=deptno;

    DEPTNO LOCATION        MANAGER_NO
---------- --------------- ----------
         4 GHT1                     6

Scalar Sub query can be used in following clause

1. select city, state, zip, (select count(*) from student s where s.zip=z.zip ) as student_count from zipcode z where state = 'ct';

2.select student_id, last_name from student s where ( select count(*) from enrollment e where s.student_id = e.student_id) > ( select avg(count(*)) from enrollment

group by student_id) order by 1

3. Select student_id, lastname from student s where student_id between 230 and 335 order by ( select count(*) from enrollment e where s.student_id=e.student_id) desc

4. select course_no, cost , case when cost <= ( select avg(cost) from course ) then cost*1.5
when cost= ( select max(cost) from course ) then ( select cost from course where course_no=20)
else cost
end "Testcase"
from course
where course_no in (20,80)
order by 2

---------------------------------------------------------------------------
 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<ANY SOME ALL >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> select ename, sex, deptno from emp where salary < any ( 100000,1200000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
praveen                        m             4
sam                                          3
naveensai                      m             5
kani                           f             6
soumya                         f             3
surya                          f             4
shanthini                      f             5

7 rows selected.

SQL>
SQL> select ename, sex, deptno from emp where salary < any ( 100000,12000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
praveen                        m             4
sam                                          3
naveensai                      m             5
kani                           f             6
soumya                         f             3
surya                          f             4
shanthini                      f             5

7 rows selected.

SQL> select ename, sex, deptno from emp where salary < any ( 10000,12000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
sam                                          3

<<<<<<<<<<<<<<<<<<<ALL>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> select ename, sex, deptno from emp where salary < all ( 10000,12000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
sam                                          3

SQL> select ename, sex, deptno from emp where salary > all ( 10000,12000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
praveen                        m             4
naveensai                      m             5
kani                           f             6
soumya                         f             3
surya                          f             4
shanthini                      f             5

6 rows selected.

SQL> select ename, sex, deptno from emp where salary > all ( 100000,12000);

no rows selected

SQL> select ename, sex, deptno from emp where salary > all ( 10000,12000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
praveen                        m             4
naveensai                      m             5
kani                           f             6
soumya                         f             3
surya                          f             4
shanthini                      f             5

6 rows selected.

SQL> select ename, sex, deptno from emp where salary > all ( 20000,12000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
praveen                        m             4
naveensai                      m             5
kani                           f             6
soumya                         f             3
surya                          f             4
shanthini                      f             5

6 rows selected.

SQL> select ename, sex, deptno from emp where salary > all ( 50000,12000);

ENAME                          SEX      DEPTNO
------------------------------ ---- ----------
soumya                         f             3
shanthini                      f             5

----------------------------------------------------------------------------