JOINS IN ORACLE

1- The purpose of join is to combine the data across tables.
2-A Join is actually performed by the where clause which combines the specified rows of tables.
3-IF a join involves in more than two tables , 
then oracle joins first two tables based on join condition and then compares the result with the next table 
and so on.

TYPES:
1.EQUI join or  Simple join or Inner Joins  
2.Non-equi join 
3.Self join 
4.Natural join 
5.Cross join 
6.Outer  join 
  a)Left outer
  b)Right outer
  c)Full outer
7. Inner join
8.Using clause
9.On clause

1. Equijoin:

• A join which contains an equal to '=' operator in the join condition.
• It will retrieve the matching rows from both the tables.
• This type of join involves primary and foreign key complements.

SELECT e.employee_id, e.last_name, e.job_id , d.department_name, d.location_id
from employees e , departments d
where e.department_id = d.department_id;


SELECT e.last_name, d.department_name, l.city
from employees e ,   departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

--or By mentioning USING clause:

SELECT e.employee_id, e.last_name, e.job_id ,d.department_name, d.location_id
from employees e  join  departments d
USING (department_id);

106 rows selected.

--or By mentioning ON  clause:

SELECT e.employee_id, e.last_name, e.job_id ,d.department_name, d.location_id
from employees e  join  departments d
ON  (e.department_id=d.department_id);

2. Non-equi join  :

•  A join which contains an operator other than equal to '=' in the Join condition.

SELECT e.last_name, e.salary,j.grade_level
FROM  employees e ,job_grades j
WHERE e.salary BETWEEN j.lowest_sal and j.highest_sal;

3.Self join 

•  joining the table to itself is called self join.

SELECT worker.last_name || 'works for '|| manager.last_name 
from employees worker , employees manager
where worker.manager_id= manager.employee_id;

4.Natural join 

• Natural join joins two tables based on the same column name.

select *  from employees
natural join  departments;

--common column is department_id and manager_id.

5.Cross join 

•  This will give the cross product of two tables.

select * from employees cross join departments;

or

select * from employees , departments ; 107*27=2889 rows.

6.Outer  join 

• Outer join gives the non-matching records along with matching records.
• In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

a) Left outer Join:

• It will display all matching records from both the tables along with the records which are in left hand side table those who don't have matching records in the in the right hand side table.
• Note: The LEFT OUTER JOIN keyword returns all records from the left table (employees), even if there are no matches in the right table (Departments).

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e 
LEFT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON (e.department_id= d.department_id) ;

For Employee with Last_Name 'Grant', the department_id is NULL in employees table. Hence in Left Outer Join , it will retrive the Last_Name 'Grant' along with NULL entries from Departments Table. 

Additional Filter condition:

SELECT  e.last_name,e.department_id,d.department_name

FROM  employees e 

LEFT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON e.department_id= d.department_id 

WHERE e.SALARY > 1000

and d.department_name ='Purchasing' ;

Joining 3 Tables:

SELECT  e.last_name,e.department_id,d.department_name,l.city

FROM  employees e 

LEFT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON e.department_id= d.department_id 

LEFT OUTER JOIN locations l

ON d.location_id = l.location_id 

--WHERE e.SALARY > 1000

--and d.department_name ='Purchasing'

-- Number of Records : 107 ( Full employees table records and matching /unmatching from dept and loc table ) 

OR

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id =d.department_id (+);

-- --The "(+)" goes on the columns of the table that generates subrows of nulls. 

-- It is advisable to mention (+) sign right side of '=' operator in Left Outer Join Case.

--The '+' sign is in the department table because it is lacking of information in employees table (No Department_id assigned or department_id is NULL for few employees ) for employee GRANT in employees table.

b) Right outer Join :

• It will display all matching records from both the tables along with the records which are in RIGHT HAND SIDE table those who don't have matching records in the in the LEFT HAND SIDE  table.
• Note: The RIGHT OUTER JOIN keyword returns all records from the right table (departments ), even if there are no matches in the left table (employees).

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e 
RIGHT OUTER JOIN  departments d   -- After 'LEFT OUTER JOIN' or 'RIGHT OUTER JOIN'         .                                                            Keyword we have to mention right side table name with alias

ON (e.department_id= d.department_id) ;

For department names Treasury and Corporate Tax etc from Department table(Right Side Table) , 
there is no entry of department_id  in employees (Left Side Table) table. This means these department_ids ( 110,120,130,140,150,160 ) has not yet assigned to any employee in employees table.  Hence it will retrive all department name from department table even though there is no match present in employees table. 

OR

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id (+) =d.department_id ;

-- It is advisable to mention (+) sign left side of '=' operator in Right Outer Join Case.

--The "(+)" goes on the columns of the table that generates subrows of nulls. 

c) Full outer join :

• This will display all matching records and non-matching records from both the tables.

SELECT e.last_name,e.department_id,d.department_name
FROM  employees e 
FULL OUTER JOIN  departments d
ON (e.department_id= d.department_id) ;

----or----

SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id =d.department_id (+) 

UNION


SELECT  e.last_name,e.department_id,d.department_name
FROM  employees e , departments d
WHERE  e.department_id (+) =d.department_id ;



7. Inner Join :

• It will retrieve the matching rows from both the tables. 

SELECT e.employee_id, e.last_name, e.job_id ,d.department_name, d.location_id
from employees e  INNER JOIN   departments d
USING (department_id);

An inner join is a join with a join condition that may contain both equality or non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign. 
So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.

It is good to know the difference between join and INNER JOIN keywoed. 
Actually there is no difference. If we write JOIN then by default Inner join is performed. 

SL 8 (Using clause)  and SL  9 (On clause): Already mentioned above.

Cheers
Rajani

SCOPE OF VARIABLE in PL/SQL

--1st Scenario
SET SERVEROUTPUT ON
DECLARE
v_weight NUMBER(3):=600;
v_message VARCHAR2(255):=' Product 10012';
v_new_locn VARCHAR2(50) :='America';

BEGIN
        DECLARE
        v_weight  NUMBER(3) :=1;
        v_message VARCHAR2(255):='Product 11001';
        v_new_locn VARCHAR2(50) :='Europe';
        BEGIN
        v_weight :=v_weight+1;
        DBMS_OUTPUT.PUT_LINE(v_weight);
        DBMS_OUTPUT.PUT_LINE(v_message);
        v_new_locn:='Western  ' || v_new_locn;
        DBMS_OUTPUT.PUT_LINE(v_new_locn);
        END;      
v_weight := v_weight+1;
DBMS_OUTPUT.PUT_LINE(v_weight);
v_message:=v_message || ' is in stock';
DBMS_OUTPUT.PUT_LINE(v_message);
v_new_locn :='Eastern  '||v_new_locn;
DBMS_OUTPUT.PUT_LINE(v_new_locn);
END;


1st Output:

2
Product 11001
Western  Europe
601
Product 10012 is in stock
Eastern  America

*******************************xxxxxxxxxxxx****************************************

2nd Scenario:

-We can access a variable from outer block whether it is declared or not in the inner block but not vice versa.
--Inner Block executes once before the outer block.
--The scope of the variable ends within the BEGIN ... END statement of the inner block.

DECLARE
v_weight NUMBER(3):=600;
v_message VARCHAR2(255):=' Product 10012';
v_new_locn VARCHAR2(50) :='America';

BEGIN
        DECLARE
        v_message VARCHAR2(255):='Product 11001';
        v_new_locn VARCHAR2(50) :='Europe';
        --v_weight  NUMBER(3) :=1;
/*v_weight variable commented out in the inner block to see the difference by not declaring in inner
block but calling it from outer block */
BEGIN
        v_weight :=v_weight+1;   /* taking value from outer block and incremented */
        DBMS_OUTPUT.PUT_LINE(v_weight);
        DBMS_OUTPUT.PUT_LINE(v_message);      
        v_new_locn:='Western  ' || v_new_locn;
        DBMS_OUTPUT.PUT_LINE(v_new_locn);
        END;      
v_weight := v_weight+1;  /* taking value derived from inner block and further incremented as inner block executes once before outer block*/
DBMS_OUTPUT.PUT_LINE(v_weight);
v_message:=v_message || ' is in stock';
DBMS_OUTPUT.PUT_LINE(v_message);
v_new_locn :='Eastern  '||v_new_locn;
DBMS_OUTPUT.PUT_LINE(v_new_locn);
END;



2nd Output: 

601
Product 11001
Western  Europe
602
Product 10012 is in stock
Eastern  America

*********************************xxxxxx**********************************

--3rd  Scenario
DECLARE
v_weight NUMBER(3):=600;
v_message VARCHAR2(255):=' Product 10012';

BEGIN
        DECLARE
        v_weight  NUMBER(3) :=1;
        v_message VARCHAR2(255):='Product 11001';
        v_new_locn VARCHAR2(50) :='Europe';
        BEGIN
        v_weight :=v_weight+1;
        DBMS_OUTPUT.PUT_LINE(v_weight);
        DBMS_OUTPUT.PUT_LINE(v_message);
        
        v_new_locn:='Western  ' || v_new_locn;
        DBMS_OUTPUT.PUT_LINE(v_new_locn);
        END;        
v_weight := v_weight+1;
DBMS_OUTPUT.PUT_LINE(v_weight);
v_message:=v_message || ' is in stock';
DBMS_OUTPUT.PUT_LINE(v_message);
v_new_locn :='Eastern  '||v_new_locn; /* We can't call v_new_locn from inner block as it is not declared in outer block*/
/*v_new_locn is not visible in the main block and you would see an error*/  

DBMS_OUTPUT.PUT_LINE(v_new_locn);
END;

compilation error:

PLS-00201: identifier 'V_NEW_LOCN' must be declared
ORA-06550: line 23, column 1:
PL/SQL: Statement ignored 


Cheers
Rajani

Access denied” when logging to Solaris 10 virtual machine using ssh (PUTTY) and Create new user and delete existing user

By editing /etc/ssh/sshd_config and adding the line PermitRootLogin yes (or changing if it's already set to No) should do it.

#

# Copyright 2009 Sun Microsystems, Inc.  All rights reserved.

# Use is subject to license terms.

#

# ident "@(#)sshd_config 1.9 09/04/30 SMI"

#

# Configuration file for sshd(1m)

# Protocol versions supported

#

# The sshd shipped in this release of Solaris has support for major versions

# 1 and 2.  It is recommended due to security weaknesses in the v1 protocol

# that sites run only v2 if possible. Support for v1 is provided to help sites

# with existing ssh v1 clients/servers to transition. 

# Support for v1 may not be available in a future release of Solaris.

#

# To enable support for v1 an RSA1 key must be created with ssh-keygen(1).

# RSA and DSA keys for protocol v2 are created by /etc/init.d/sshd if they

# do not already exist, RSA1 keys for protocol v1 are not automatically created.

# Uncomment ONLY ONE of the following Protocol statements.

# Only v2 (recommended)

Protocol 2

# Both v1 and v2 (not recommended)

#Protocol 2,1

# Only v1 (not recommended)

#Protocol 1

# Listen port (the IANA registered port number for ssh is 22)

Port 22

# The default listen address is all interfaces, this may need to be changed

# if you wish to restrict the interfaces sshd listens on for a multi homed host.

# Multiple ListenAddress entries are allowed.

# IPv4 only

#ListenAddress 0.0.0.0

# IPv4 & IPv6

ListenAddress ::

# Port forwarding

AllowTcpForwarding no

# If port forwarding is enabled, specify if the server can bind to INADDR_ANY. 

# This allows the local port forwarding to work when connections are received

# from any remote host.

GatewayPorts no

# X11 tunneling options

X11Forwarding yes

X11DisplayOffset 10

X11UseLocalhost yes

# The maximum number of concurrent unauthenticated connections to sshd.

# start:rate:full see sshd(1) for more information.

# The default is 10 unauthenticated clients.

#MaxStartups 10:30:60

# Banner to be printed before authentication starts.

#Banner /etc/issue

# Should sshd print the /etc/motd file and check for mail.

# On Solaris it is assumed that the login shell will do these (eg /etc/profile).

PrintMotd no

# KeepAlive specifies whether keep alive messages are sent to the client.

# See sshd(1) for detailed description of what this means.

# Note that the client may also be sending keep alive messages to the server.

KeepAlive yes

# Syslog facility and level 

SyslogFacility auth

LogLevel info

#

# Authentication configuration

# 

# Host private key files

# Must be on a local disk and readable only by the root user (root:sys 600).

HostKey /etc/ssh/ssh_host_rsa_key

HostKey /etc/ssh/ssh_host_dsa_key

# Length of the server key

# Default 768, Minimum 512

ServerKeyBits 768

# sshd regenerates the key every KeyRegenerationInterval seconds.

# The key is never stored anywhere except the memory of sshd.

# The default is 1 hour (3600 seconds).

KeyRegenerationInterval 3600

# Ensure secure permissions on users .ssh directory.

StrictModes yes

# Length of time in seconds before a client that hasn't completed

# authentication is disconnected.

# Default is 600 seconds. 0 means no time limit.

LoginGraceTime 600

# Maximum number of retries for authentication

# Default is 6. Default (if unset) for MaxAuthTriesLog is MaxAuthTries / 2

MaxAuthTries 6

MaxAuthTriesLog 3

# Are logins to accounts with empty passwords allowed.

# If PermitEmptyPasswords is no, pass PAM_DISALLOW_NULL_AUTHTOK 

# to pam_authenticate(3PAM).

PermitEmptyPasswords no

# To disable tunneled clear text passwords, change PasswordAuthentication to no.

PasswordAuthentication yes

# Use PAM via keyboard interactive method for authentication.

# Depending on the setup of pam.conf(4) this may allow tunneled clear text

# passwords even when PasswordAuthentication is set to no. This is dependent

# on what the individual modules request and is out of the control of sshd

# or the protocol.

PAMAuthenticationViaKBDInt yes

# Are root logins permitted using sshd.

# Note that sshd uses pam_authenticate(3PAM) so the root (or any other) user

# maybe denied access by a PAM module regardless of this setting.

# Valid options are yes, without-password, no.

PermitRootLogin yes

# sftp subsystem

Subsystem sftp /usr/lib/ssh/sftp-server

# SSH protocol v1 specific options

#

# The following options only apply to the v1 protocol and provide

# some form of backwards compatibility with the very weak security

# of /usr/bin/rsh.  Their use is not recommended and the functionality

# will be removed when support for v1 protocol is removed.

# Should sshd use .rhosts and .shosts for password less authentication.

IgnoreRhosts yes

RhostsAuthentication no

# Rhosts RSA Authentication

# For this to work you will also need host keys in /etc/ssh/ssh_known_hosts.

# If the user on the client side is not root then this won't work on

# Solaris since /usr/bin/ssh is not installed setuid.

RhostsRSAAuthentication no

# Uncomment if you don't trust ~/.ssh/known_hosts for RhostsRSAAuthentication.

#IgnoreUserKnownHosts yes

# Is pure RSA authentication allowed.

# Default is yes

RSAAuthentication yes

Then Restart the SSH server from terminal  (for solaris 10 only )

svcadm disable ssh

svcadm enable ssh



for user notice :Save and close the file 

To add a user :

# useradd -d /export/home/amar -m -s /bin/ksh -c "amar" amar

64 blocks

# passwd amar

New Password:

Re-enter new Password:

passwd: password successfully changed for amar

#

# useradd -d /export/home/rajani -m -s /bin/ksh -c "rajani" rajani

64 blocks

# passwd rajani

New Password:

Re-enter new Password:

passwd: password successfully changed for rajani

#

To Delete a user:

# userdel amar

How to find nth Highest Salary in Oracle

We have taken 3rd (nth )  highest salary for example.


Type:1

SELECT empno, sal
FROM
(
select empno, sal, ROW_NUMBER() OVER (order by sal desc) RN 
from emp 
order by sal desc
)
WHERE RN = 3;


Type 2 : 

select sal from
(select emp.*,
       dense_rank() over (order by sal desc) rank
from emp)
where rank=3;



Type 3 :

                                                                               /* 3 for 3rd highest and 4 for 4th  highest  salary etc..*/


select distinct (a.sal)                                              
from emp a
WHERE 3= (SELECT count(distinct(b.sal)) from emp b
where a.sal<=b.sal);




2nd highest salary in SQL :


SELECT MAX(Sal) FROM Emp
WHERE Sal NOT IN (SELECT MAX(Sal) FROM Emp);


/* Display top three earner name and salaries from EMPLOYEES table */


SELECT ROWNUM as RANK, last_name ,salary
from (SELECT last_name , salary FROM employees
      ORDER BY salary desc) 
      
WHERE ROWNUM<=3;


Oracle assigns values to ROWNUM sequentially as rows are produced by the query - thus, the first row fetched gets ROWNUM=1, the second row fetched gets ROWNUM=2, the third row fetched gets ROWNUM=3, etc. Notice - for a row to be assigned ROWNUM=3 two preceding rows MUST be fetched. And this is why your query returns no rows. You're asking the database for the third row fetched - but rows 1 and 2 have never been fetched.