Useful SQL Queries

SELECT: A basic 'select' Query - to pull all records

Select * from emp;

JOINS
  1. Inner joins
  2. Outer joins
    1. Left outer join
    2. Right outer join
    3. Full outer join
  3. Self join
  4. Cross join (Cartesian)
CROSS JOIN: 

 A Cross Join is also called as Cartesian Product. In simple terms this is a multiplication of two tables.

S1= {10,20,30,40}
S2= {A,B}

select * from S1 cross join S2;

S1 X S2 = {(a,10),(a,20),(b,10),(b,20),(c,10),(c,20),(d,10),(d,20)}


SELF JOIN


empName
City
A
Texas
B
Dallas
C
Chicago
D
Texas
X
Texas
Y
Dallas
Z
Dallas

Applying a Join condition on the same table for manipulating a column values with the same column.
e.g: Display all the list of employees where 'a' is living
so for this we need to get the city of 'a' and compare with all other values of the same table.

select e2.* from employee e1 join employee e2 on e1.empName='a' and e1.city=e2.city;

Always try to get the data from second table as you are already fixing the column name in table1.
If we try to get the data like "e1.*" then for all the employee names 'a' employee name is displayed.









INNER JOIN

Get the values matching in TableA and TableB.

select <column_name> from TableA INNER Join TableB on A.<somecolum>=B.<someColumn>
(OR)
select <column_name> from TableA Join TableB on A.<somecolum>=B.<someColumn>
"INNER" phrase is optional.

select empName, empID, deptName from Emp JOIN Dept on emp.dno=dept.dno;

LEFT OUTER JOIN

Matching values of TableA and TableB and all values of TableA

select empId, empName, deptName from emp left join dept on emp.dno=dept.dno;



RIGHT OUTER JOIN

Matching values of TableA and TableB and all values of TableB

select empId, empName, deptName from emp right join dept on emp.dno=dept.dno;


FULL OUTER JOIN

Matching values of TableA and TableB and all values of TableA and TableB

select * from emp full outer join dept on emp.dno=dept.dno;
EQUI JOIN

 Equi JOIN is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi Join.
Equi JOIN can only have equality (=) operator, whereas Inner Join can have other operators such as (<,>,<>) in the join condition.

NATURAL JOIN

 Natural Join is a type of equi join which occurs implicitly by comparing all the same column names in both tables. The join result have only one column for each pair of equally named columns.

select * from Emp natural join Emp2;

PRINTING TO CONSOLE

DBMS_OUTPUT.PUT_LINE('Hello World');
this is like a sysout statement, used to display console output in pl/sql blocks.

UNION AND UNIONALL

select id,name,email from Table1
UNION
select id,name,email from Table2

select id,name,email from Table1
UNION ALL
select id,name,email from Table2

UNION will remove the duplicate records.
UNION has to perform distinct sort to remove duplicates, which makes it less faster compared to UNION ALL.

UNION AND JOIN

UNION combines two or more select queries into one single result set whereas JOIN is used to retrieve data from two or more tables based on logical relation ships between the tables.


GROUP BY

Used for Aggregation.
Aggregation functions like,
  • SUM
  • COUNT
  • MIN
  • MAX
  • AVERAGE

ID NAME GENDER SALARY CITY
1 RAM M 4000 DELHI
2 RAHIM M 3000 HYDERABAD
3 JOHN M 3500 MUMBAI
4 SEETHA F 1500 HYDERABAD
5 GEETHA F 4500 LONDON
6 RITA F 7000 NEWYORK
7 SANA F 9000 SYDNEY
8 TED M 6590 LONDON
9 VADIM M 6500 DENMARK
10 DAN M 8500 CHICAGO
11 ROBERT M 6000 CHENNAI


select city, sum(salary) from Employee group by city;

select city,gender,sum(salary) as total from employee group by city, gender;

select gender, city, sum(salary) as TotalSalary, count(ID) as TotalEmployees from employee group by gender, city;

select gender, city, sum(salary) as totalSalary, count(id) as totalEmployees from employee group by gender, city having gender='M';

select gender, city, sum(salary) as TotalSalary, count(id) as TotalEmp from employee where gender='M' group by gender, city;

  • 'having' clause should come after the 'group by'.
  • The difference between where and having is, 'where' clause filters the records before Aggregation, means first all male records are fetched then Aggregation is applied.
  • But, In 'having' all the records are fetched applying the Aggregation but only the 'having' clause records are displayed.
  • 'having' can only be used with 'select' statement, whereas 'where' can be used with SELECT, INSERT, UPDATE AND DELETE.
  • Both are having Good performance, But 'where' clause is more preferred.


HIGHEST SALARY'S

select max(salary) from employee;         -->   1st highest

select max(salary) from employee where salary < (select max(salary) from employee);   --> 2nd highest.

select max(salary) from employee where salary < (select max(salary) from employee where salary < (select max(salary) from employee ));   --> 3rd highest.

Then, what about nth highest, say 15th ???

Here we go,

  1. DENSE RANK
  2. ROW NUMBER

select * from 
(
select salary, dense_rank() over (order by salary desc) denseRank from employee
)
where denseRank = 15;

select * from 
(
select salary, row_number() over (order by salary desc) rowNumber from employee
)
where rowNumber=15;


The difference between above is, Dense Rank returns actual rank of the record, meaning it will not consider the duplicate rank, whereas Row_number function considers the duplicate as well.

Apart from above, we can also use LIMIT function, but this doesn't work in Oracle DB, you can use in MySQL DB.

select * from employee order by salary desc limit (0,14); --> returns 15th highest.
 ############# more to be added ..........