Basics: January 2016

Friday, January 29, 2016

Database Basics

Download

1) SQL Developer at http://www.oracle.com/technetwork/developer-tools/sql-developer/

2) Oracle XE

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

3) Latest JDK

Install

Once downloaded install all softwares

Unlock HR Using the SQL Command Line

To unlock the sample user account using the SQL command line:

Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and thenRun SQL Command Line.
Connect as the SYSTEM user:
- Type: connect
- Enter user-name: system
- Enter password: <password-for-system>
Enter the following statement to unlock the HR account:
```
SQL> ALTER USER hr ACCOUNT UNLOCK;
```
Enter a statement in the following form to specify the password that you want for the HR user:
```
SQL> ALTER USER hr IDENTIFIED BY <hr-password>;
```
Optionally, exit SQL*Plus (which also closes the command window):
```
SQL> exit
```

or you can directly run the command

 ALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;

Then go to sql developer & connect as

To see all the tables

```
 select * from tab
```

SOME Examples

SELECT

select COUNT(DISTINCT DEPARTMENT_ID) AS DEPARTMENTS from DEPARTMENTS
select CONCAT(last_name,FIRST_NAME) as NAME ,SALARY*12 as CTC FROM employees
select last_name||' '||FIRST_NAME as NAME FROM employees
Describe employees
select last_name from employees where salary >10000
select last_name from employees where HIRE_DATE>'10-JUNE-1985'
select last_name from employees where HIRE_DATE BETWEEN '10-JUNE-1985' AND '10-JUNE-2005'
select first_name from employees where first_name like '_u%'
select last_name as NAME from Employees where manager_id IN (101,102,103) AND DEPARTMENT_ID IS NULL

CHAR FUNCTION

select LPAD(salary,10,'*') from employees where salary >10000 (o/p *****24000)
select RPAD(salary,10,'*') from employees where salary >10000 (o/p 24000*****)
select CONCAT(' MR. ', last_name) from employees where lower(first_name)='david'
select substr(last_name,1,5) from employees where lower(first_name)='subrat'
select length(last_name) from employees where lower(first_name)='david'
select instr(last_name,'a') from employees where lower(first_name)='david'
select trim('D' from first_name) from employees where lower(first_name)='david'

NUMBER FUNCTION

select round(55.923,2) , round(55.923,0) , round(55.923,-1) from dual;

o/p 55.92 56 60

select trunc(55.923,2) , trunc(55.923,0) , trunc(55.923,-1) from dual;

o/p 55.92 55 50

 MOD(29,3)                                               |
+---------------------------------------------------------+
| 2

DATE FUNCTION

select sysdate from dual;
select last_name from employees where ((sysdate-hire_date)/(365))>12
To find max experience person : select * from (select round(((sysdate-hire_date)/(365)),1) as Experience from employees order by Experience desc) where ROWNUM = 1

select max(salary) from employees where salary<(select max(salary) from employees)

GROUP FUNCTION

select AVG(salary),MIN(salary),MAX(salary),SUM(salary) from employees
select distinct(department_id), AVG(salary) from employees group by DEPARTMENT_ID having AVG(salary)>9000

Thursday, January 28, 2016

EQUI JOIN / INNER JOIN

NATURAL JOIN

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

Based on the columns in two tables that have same name based on equal values in matched tables

EX : select department_id , DEPARTMENT_NAME ,LOCATION_ID , CITY from DEPARTMENTS natural join LOCATIONS where department_id in(10,20)

USING clause

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

If columns have same name but data is not matching , use the USING clause to specify column for equijoin

Ex : select last_name , location_id,department_id from employees JOIN departments using (department_id) where department_id=50

ON Clause

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

On clause specify columns to join

Ex : select e.last_name , d.location_id,d.department_id from employees e JOIN departments d ON (e.department_id=d.department_id) and e.MANAGER_ID=149;

ON is the more general of the two. One can join tables ON a column, a set of columns and even a condition. For example:

SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...

USING is useful when both tables share a column of the exact same name on which they join. In this case, one may say:

SELECT ... FROM film JOIN film_actor USING (film_id) WHERE .

SELF JOIN

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

Sometime you need to join table to itself to retrieve data . For example to find emp's managaer Name

select e.last_name, m.last_name from employees e join employees m ON (e.manager_id = m.employee_id)

NON -EQUIJOIN

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

A join condition other than an equality operator

Ex :

OUTER JOIN :

result of inner join + Return record with no direct match

Extension of ON clause

LEFT OUTER JOIN

----------------
In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.

Brings all data from employee table even if no match from departments

Ex : select e.last_name , d.location_id,d.department_id from employees e LEFT outer JOIN departments d ON (e.department_id=d.department_id)

RIGHT OUTER JOIN

----------------
In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

Brings all data from departments table even if no match from employees

Ex : select e.last_name , d.location_id,d.department_id from employees e RIGHT outer JOIN departments d ON (e.department_id=d.department_id)

FULL OUTER JOIN

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

In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

ALL data

Ex : select e.last_name , d.location_id,d.department_id from employees e FULL outer JOIN departments d ON (e.department_id=d.department_id)

CROSS JOIN

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

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

ALL rows from first table is joined to all tables in second table

Ex : select last_name , department_name from employees cross join departments

Basics

Friday, January 29, 2016

Database Basics

Database Basics

Unlock HR Using the SQL Command Line

SOME Examples

SELECT

Thursday, January 28, 2016

Joins Basics

Joins Basics

EQUI JOIN / INNER JOIN

NATURAL JOIN