PL/SQL:Anchored data type

Data type which you assign to a variable based on database object.
Syntax:
variable_name typed_attribute %type

Example:
Declare
vname employees.first_name %type
Begin
select first_name into vname from employees where employee_id=100;
dbms_output.put_line(vname);
end;


In the above example vname is the name of variable and employees is table name of database, first_name is attibute(column)of employees table %type used for anchored datatype. This querry will asign datatype with respect to database object.

PL/SQL: If Statement

If statement is descision making statement. You will check the condition and perform any action based on the outcome of if statement.
The outcome of if statement is either True or False.

Syntax:
If condition then
statement(s);
end if;

For example:

Example:
declare
vsalary number(10,2);
begin
select salary into vsalary from employees where employee_id=100;
if vsalary>6000 then
dbms_output.put_line('He earn more than TOM');
end if;
end;

Result:
He earn more than TOM

PL/SQL: INSERT DATA AS MEMORY VARIABLES.

First of all , we create a table.

Table:
create table t1 (c1 number(3), c2 varchar2(30), c3 date);

Result:

table created.

Insert data into table t1 Using PL/SQL MEMORY_VARIABLE:

Code:

declare

a number:=2;

d date:=sysdate;

begin

insert into t1 values(1,'Talha','29-apr-2017');

insert into t1 values(a,'Zubair',d);

insert into t1 (c1,c3) values(3,d);

end;

Result:

3 ROWS inserted

Now check

select * from t1;

Result:

1 Talha 29-APR-17

2 Zubair 30-APR-17

3         30-APR-17

PL/SQL: Insert/update/delete Data into table

If we had to insert data into the columns then the syntax will be the same.

Syntax:

begin
insert into t1
values (3,'cc');
end;
/

Or by using Update statement In Sql :
update t1 set c2='bb' where c1=2;

Update statement in PL/SQL:
begin
update t1 set c1=33 where c1=3;
end;
/

Delete BY Using PL/SQL:
begin
delete from t1 
where c1=33;
end;
/

PL/SQL:Storing Data into Memory Variables.

By using pl/sql, we can get data from table and store them into variables( memory variables). After that we will use this data for further processing.

For Example: We want to retrive value from table employees and show first name and salary of employee whose employee id is 100.

Code:

declare

sal number;

name varchar2(30);

begin

select salary , first_name into sal,name from employees where employee_id=100;

dbms_output.put_line('Salary of Mr. '||name||' is '||sal);

end;

Result:

Salary of Mr. Steven is 24000

In above example sal and name are used as memory variables.

PL/SQL:Single Line Output With different datatypes.

This example will explain different data types, and output in a single line.

Example:

declare

bday date:='21-apr-1994';

name varchar2(30):='Muhammad Talha Zubair';

salary number(5):=20000;

begin

dbms_output.put_line('Salary Of Mr. ' || name||' is ' ||salary ||' and has Birthday on '||bday);

end;

Output:

Salary Of Mr. Muhammad Talha Zubair is 20000 and has Birthday on 21-APR-94

PL/SQL DIVISION TWO NUMBERS WITH EXCEPTION SECTION

In this , we use the declaration, begin and exception area.

Withou dividing by zero.

Code:

declare 

n1 number;

n2 number;

result number;

begin

n1:=10;

n2:=2;

result:=n1/n2;

dbms_output.put_line(N1/N2);

EXCEPTION

  WHEN ZERO_DIVIDE THEN

    DBMS_OUTPUT.PUT_LINE('Divided by zero is not allowed.');

END;

Result:

5

Dividing by Zero

code:

declare 

n1 number;

n2 number;

result number;

begin

n1:=10;

n2:=0;

result:=n1/n2;

dbms_output.put_line(N1/N2);

EXCEPTION

  WHEN ZERO_DIVIDE THEN

    DBMS_OUTPUT.PUT_LINE('Divided by zero is not allowed.');

END;

Result:

Divided by zero is not allowed.

PL/SQL ADDING TWO NUMBERS

Add Two Numbers and display result:
Adding two numbers consists on following steps.
i) Identifying the first number
ii) Identifying the second number
iii) Identifying the third number where addition of first and second number is stored. (result)
iv) Add two numbers into result
v) Display result


CODE:

declare
n1 number;
n2 number;
result number;
begin
n1:=10;
n2:=20;
result:=n1+n2;
dbms_output.put_line(result);
end;

Result: 30

PL/SQL Syntax

Declare             --optional

this area contains variables and their datatypes.

Begin                 --mandatory

this area contains statements thats are to be executed.

exception           --optional

Error handling area

end;

SQL: Unlock User account

In database, either we should have to unlock user account or user schema during installation, Or We can unlock the user schema using cmd or sqlplus
First of all connect with database as sysdba with sys user.
By using cmd:
C:\> sqlplus sys/oracle as sysdba
connected to:
(edition detail)
SQL>

SQL> alter  user scott identified by tiger user unlock;
user altered.

In above query, alter is keyword used to change, here used to change user, user is also keyword, scott is name of user or schema, identified by is keyword used for password, tiger is password for scott schema unlock is instruction for alter query.
we can lock or unlock  user by using above query,
To lock user, just write above query as
SQL> alter user account scott identified by tiger account lock;
user altered.
Now account is locked.

SQL: Cross Join

This join Cartesion join, becasuce it returns values with cartesian prodct.
It mean that every value of first colume is attached with all the values of  second column .

Syntax:
select columns_name from table1 cross join table2 On(expression) or Using(column_name) Where(expression) order by column name;

Example:

select emp_name, dept_name from emp cross join dept;

Result:

Talha IT

Talha Oracle

Talha Finance

Talha Accounts

Talha Marketing

Zubair IT

Zubair Oracle

Zubair Finance

Zubair Accounts

Zubair Marketing

Salman IT

Salman Oracle

Salman Finance

Salman Accounts

Salman Marketing

shani IT

shani Oracle

shani Finance

shani Accounts

shani Marketing

Waqass IT

Waqass Oracle

Waqass Finance

Waqass Accounts

Waqass Marketing

Or Example:

select emp_name, dept_name from emp , dept;

Result:

Talha IT

Talha Oracle

Talha Finance

Talha Accounts

Talha Marketing

Zubair IT

Zubair Oracle

Zubair Finance

Zubair Accounts

Zubair Marketing

Salman IT

Salman Oracle

Salman Finance

Salman Accounts

Salman Marketing

shani IT

shani Oracle

shani Finance

shani Accounts

shani Marketing

Waqass IT

Waqass Oracle

Waqass Finance

Waqass Accounts

Waqass Marketing

Example with Where clause:

select emp_name, dept_name from emp cross join dept where dept_name='IT';

Result:

Talha IT

Zubair IT

Salman IT

shani IT

Waqass IT

SQL: Inner Join

Inner join only return the values from columns which satisfy the Join Condition.

Syntax:
select columns_name from table1 fInner join/ join table2 On(expression) or Using(column_name) Where(expression) order by column name;

Example using On clause:
select emp_name, Dept_name from emp Inner join dept on(emp.emp_id = dept.emp_id);

Result:
Talha IT
Zubair Oracle

Salman Finance

Example With Using Clause:
select emp_name, Dept_name from emp Inner join dept using(emp_id);

Result:
Talha IT
Zubair Oracle

Salman Finance

SQL: Full Outer join

Full outer join is the combination of both, left outer join and right outer join.
It Returns the all values from left table as well as from right table.
Let we see the sytax of full outer join.

Syntax:
select columns_name from table1 full outer join/ full join table2 On(expression) or Using(column_name) Where(expression) order by column name;

Example:
select emp_name, dept_name from emp full outer join dept on(emp.emp_id = dept.emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
(null) Accounts
(null) Marketing
Waqass (null)

shani (null)

To explain working, change the side of table name from the full outer join, and check the result.

Example:
select emp_name, dept_name from dept full outer join emp on(emp.emp_id = dept.emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani (null)
Waqass (null)
(null) Marketing

(null) Accounts

Example:
select emp_name, dept_name from dept full outer join emp on(emp.emp_id = dept.dept_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani Accounts

Waqass Marketing

Example with using clause:
select emp_name, dept_name from dept full outer join emp using(emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani (null)
Waqass (null)
(null) Marketing

(null) Accounts

Example with using clause and Where clause:
select emp_name, dept_name from dept full outer join emp using(emp_id) where emp_salary>2000;
Result:
Zubair Oracle
Salman Finance
shani (null)

Waqass (null)

Example with using clause and Where clause and Order by employee Name:
select emp_name, dept_name from dept full outer join emp using(emp_id) where emp_salary>2000 order by emp_name;
Result:
Salman Finance
Waqass (null)
Zubair Oracle

shani (null)

SQL:Left outer join

Left outer join select all the data from all columns of source table and select only required data from the target table.

Syntax:
Select columns_name from source_table left outer join target_table;

In left outer join, left table of left outer join is the source table and right side of left outer join is the target table.
It also works with both parameters, with On(expression) or with using(column_name).

Example:
select emp_name, dept_name from emp left outer join dept on(emp.emp_id = dept.emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani

Waqass

To explain the working, i revert the query as.

Example:
select emp_name, dept_name from dept left outer join emp on(emp.emp_id = dept.emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
Accounts

Marketing

We can use Left outer with different on expression to explain the working.

Example:
select emp_name, dept_name from dept left outer join emp on(emp.emp_id = dept.dept_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani Accounts

Waqass Marketing.

Left outer join with using clause:

Example:
select emp_name, dept_name from emp left outer join dept using(emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani

Waqass

SQL: Right outer join

Right outer join retrieved all data from the source table and extract only required columns or data from the target table.

Syntax:

Select columns... from target_table right outer join Source_table on(expression);

To explain the Working of right outer join we create tables .

Note::: I am working on hr schema.

Query:
create table emp
(
emp_id number(3) constraints emp_id_pk primary key,
emp_name varchar2(30),
emp_salary number(4)

);
Result:
table emp created.

Now create a dept table  with the constraint foreign key.

Query:
create table dept
(
dept_id number(3) constraints dept_did_pk primary key,
dept_name varchar2(30) ,
emp_id constraint dept_eid_fk references emp(emp_id)
);

Result:
table created.

Insert data into the table as follows

Queries:
insert into hr.emp (emp_id,emp_name, emp_salary) values (1,'Talha','2000');
insert into hr.emp (emp_id,emp_name, emp_salary) values (2,'Zubair','20000');
insert into hr.emp (emp_id,emp_name, emp_salary) values (3,'salman','25000');
insert into hr.emp (emp_id,emp_name, emp_salary) values (4,'shani',30000);
insert into hr.emp (emp_id,emp_name, emp_salary) values (5,'Waqass',22000);

Result:
5 rows inserted.

Inserting data into the dept table.

Quries:
insert into hr.dept (dept_id,dept_name, emp_id) values (1,'IT',1);
insert into hr.dept (dept_id,dept_name, emp_id) values (2,'Oracle',2);
insert into hr.dept (dept_id,dept_name, emp_id) values (3,'Finance',3);
insert into hr.dept (dept_id,dept_name) values (4,'Accounts');

insert into hr.dept (dept_id,dept_name) values (5,'Marketing');

Result:

5 rows inserted.

Right Outer Join With the On clause:
Query:
select emp_name, dept_name from dept right outer join emp on(emp.emp_id = dept.emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani

Waqass

In the above query emp_name belongs to source table, and dept_name is the coulumn of target table.
Result shows that right outer join retrive all data of the column from the souce table and only required data from the target table.

Right Outer Join With the Using clause:

Right outer join with using clause is used when the data type and the name of column is the same.Or when we are performing equie join, thats means that whenever we use equal to comparing on columns over which we want to put join.

Query:
select emp_name, dept_name from dept right outer join emp using(emp_id);

Result:
Talha IT
Zubair Oracle
Salman Finance
shani

Waqass

Right Outer Join With the Where clause:
we use where clause when we want to filter the result return by or query. In previous clause we extract the name of employees thats are working. now if we want to retrieve the name of employees whos salary is greater than twenty thousand.
      It will be the same as previous query but editional with where clause.

Query:
select emp_name, dept_name from dept right outer join emp using(emp_id) where(emp.emp_salary>20000);

Result:
Salman Finance
shani

Waqass

SQL: Substr() Function

Substr() function is used to substring the charater or letter.

Syntax:

Substr(source_string, Initial possition, length_of_string);

This consists on three things. First is source string.
Source string is the basic string, its data type is varchar, or somtime number.

2nd thing is the Initial possition. From where substr() function start substring the letter or source string.

3rd is the length of substring character.

Example:
Select substr('M Talha Zubair'3,5) from dual;

Result:
Talha


Scanrio 1:
If start possition is > source_string

then it return null  value.

Scanrio 2:
If Length of String > source_string

Then it result from start possition to last of source_string.

Scanrio 3:
If the function contain, any arithimatical operation, then its first evaluate it and then Give result from the answer of arithematical operation.

Example:

Select substr(50000-7,2,4) from dual;       // result of arithematic operation is 49993
result:
993

Example:

Select substr(sysdate,4,3) from dual;
Reslut:
Apr

SQL: Concatination Parameter. '||' Vs Concat() Function

Concatenation parameter is used to combine the result of one or more columns into a single column.

Syntax:

Select first_column ||  second_column from table_name;

Example:

Select ename||job from emp;

result:

ENAME||JOB
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK

Here is a problem as shown in result , there is no space between the employee name and job.
We can  fix it by using a space between parameters.

Query:

Select ename||' '||job from emp;

Resutl:

Ename||' '||job
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK

Concat() Function:

Concat function is also used to combine the result of  two columns.

Syntax:
Select concat(first_column, Second_column) from table_name;

Example:
Select concat(ename, job) from emp;
Result:

concat(ename,job)
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK

And if we want to create a space between the employee name and the job. then we use anothor concatenation function.

Example:
select concat(concat(ename,' '),job) from emp;
Result:

concat(concat(ename, ' '), job) from emp;
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK

The use of concatenation function is complicated.

SQL: Lower() case manipulation function

This is also character function.
Used to Convert all alphabets into small letters.

Example:

Select Lower('HELLO World') from dual;

Result:

hello world.



We can use case manipulation functions when inserting  data into tables. Here to check we, First we create a table with name of example, then apply lower() case parameter.

Query:

Create table example(name varchar2(10));

Result:
table created.

Now insert data into example.

insert into example values(lower('TALHA'));

Result:

One Row inserted.

Now check by retrieve data.

Query:

Select * from example;

Result:

talha

SQL: Initcap()

initcap() parameter is also case manipulation function of character function.

This parameter changes all first characters to capital and other remains small

Example:

select initcap('hello world') from dual;

Result:

Hello World

SQL: Upper case Parameter

Upper():

          This is case manipulation function of character function.
This function changes all characters into UPPER case.

EXAMPLE:

 Select upper('hello world') from dual;

Result:

HELLO WORLD

SQL: Add or Delete a Column Or Change the name of Column

To add column in existing table:
Syntax:
alter table table_name add new_column_name datatype(size);


To Delete column :
Syntax:
alter table table_name drop column column_name;


For the modification of name of column in table:
we use following statement

Syntax:

alter table table_name rename column column_old_name to column_new_name;

Example:

alter table authors rename column author_id to author_loc;

Result:

table authors altered.

We can change / modify data type of column:

Syntax:

Alter table table_name modify column_name datatype(size);

Example:

alter table authors modify author_loc varchar2(100);

Result:

Altered.

SQL: Change the name of Existing Table

We can change the name of existing table using alter statement.

Syntax:

alter table table_name rename to New_name;

in above statement,  Alter table and rename to are the keywords.

Example:

alter table authors rename to author_detail;

result:

table altered

SQL: insert into the table.

Insert into command is used to insert data into the columns of table.
We can insert data into table by two ways.
 One mathod is by spacifying the columns names. and other is without using columns name.

Syntax:

insert into table_name(col_1, col_2, col_3) values(val1, val2, val3);

In the above example. we use table name with its column names in which we want to insert the data. and values is the keyword and val1 is the value for column 1, val2 is the value for column 2 and upto so on as.

Example:

insert into authors(author_id,author_name) values (1,'Muhammad Talha Zubair');

Result:

One row is inserted.

And if we want to insert data in all the columns, then we can use insert into command without using the columns names.

Example:

insert into authors values (2,'Talha Zubair Khan');

Result:

One row is inserted.

commit;

it will save the inserted data. or changes which you performed.

Check the updated table by using Select * from authors;

Result:
Author_id          Author_name
1 Muhammad Talha Zubair

2 Talha Zubair Khan

Two rows inserted into authors table.

SQL: Create table

Table is the collection of rows and columns.
Sql CREATE table command is used to create a table in database/schema.
This is DDL command.

Syntax:

Create table table_name (column_1 datatype(size) constraints , Column_2 datatype(size) constraints,........, column_n datatype (size) constraints);

Table is created.

Example:

create table authors (author_id number(3) primary key, author_name varchar2(30));

Result:
table created.

In the above example we created a table with the name of authors and have only two columns

SQL: Retrieve unique Data From table

When unique data is required, then we use disticnt keyword with the name of columns of a table.
it works automatically with single column or with multiple columns.

Syntax:

Select distinct column_name from table_name;

In the above query , only one column is used. we chek the it with example.

Example:

select distinct job from emp;

Result:

job
CLERK
SALESMAN
PRESIDENT
MANAGER

ANALYST

Now we see with the multiple columns

Example:

select distinct job,mgr from emp;

Result:

Job     Mgr
CLERK 7902
PRESIDENT
CLERK 7698
CLERK 7788
CLERK 7782
SALESMAN 7698
MANAGER 7839
ANALYST 7566

You can chek result, Distinct statement retrive unique data from the multiple columns or from single column

SQL:How to Combine result of Two or more columns of a table.

For example if we want to combine the data of ename and job coloumn in a single column, We use Concatenation operator or concatenation function .

Syntax:

Select col_1 || col_2 from table_name;

Example:

select ename || job from emp;

Result:
Ename || job
SMITHCLERK
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
TURNERSALESMAN
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK

Here Problem is that there is no space between the data of two coloumns.
We use space ' ' in the single quotes with the concatenation operator to overcome on the above problem.

Query:
select ename ||' '|| job from emp;

Result:
ename ||' '||job
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST

MILLER CLERK

Here the above problem is solved.

We can further clear our concepts by the below example.

Question:
Write query which give the result as, The Salary of Mr. Smith is 800.

Asnwer:
select 'The salary of Mr. '|| ename ||' is '|| sal from emp;

Result:
'The salary of Mr. '|| ename ||' is '|| sal
The salary of Mr. SMITH is 800
The salary of Mr. ALLEN is 1600
The salary of Mr. WARD is 1250
The salary of Mr. JONES is 2975
The salary of Mr. MARTIN is 1250
The salary of Mr. BLAKE is 2850
The salary of Mr. CLARK is 2450
The salary of Mr. SCOTT is 3000
The salary of Mr. KING is 5000
The salary of Mr. TURNER is 1500
The salary of Mr. ADAMS is 1100
The salary of Mr. JAMES is 950
The salary of Mr. FORD is 3000

The salary of Mr. MILLER is 1300

Here is also a problem with the coloumn name . we can remove this problem by this.
Query:
select 'The salary of Mr. '|| ename ||' is '|| sal as Employees_Detail from emp;

Reuslt:
Employees_Detail
The salary of Mr. SMITH is 800
The salary of Mr. ALLEN is 1600
The salary of Mr. WARD is 1250
The salary of Mr. JONES is 2975
The salary of Mr. MARTIN is 1250
The salary of Mr. BLAKE is 2850
The salary of Mr. CLARK is 2450
The salary of Mr. SCOTT is 3000
The salary of Mr. KING is 5000
The salary of Mr. TURNER is 1500
The salary of Mr. ADAMS is 1100
The salary of Mr. JAMES is 950
The salary of Mr. FORD is 3000
The salary of Mr. MILLER is 1300

Thanks everyone
Hope it will be helpfull for you.

SQL:Select statement

Select statement is used to retrieve or extract data from the database table.
Here we can discuss,

All data retrieve from the table:

Syntax:

Select * from table_name;

In the above query, Select and from are the keywords , table_name is the name of which table from where data is retrieved(extract) and * is used for select all data , mean data from all the columns of the table,

this query will retrieve all data from the emp table.


Example:

Select * from emp;

emp is table of employees in the scott schema of oracle database.

Data From One column :

Syntax:

Select column_name from table_name;

Column name is the name of column and table name is the name of table from where data is retrieved.

Example:

Select ename from emp;

this query will show the name of all employees in the emp table;

Data From multiple columns:

we can retrieve data from two or more columns seperated by comma.

Syntax:

select col_1, col_2, .........,coln ffrom table_name;

you can add columns name seperated by comma.

Example:

Select ename, job , sal from emp;

This Query will retrieve data from ename, job and salary column of employee table.
.