PL/SQL: TABLE BASED DATA TYPE.

Record datatype variables are composit data structure made up of different components called fields which have the same name and datatype as that of the columns of the table which is using in creating the record.
Example:

set serveroutput on;
declare
v_emp employees%rowtype;
begin
select * into v_emp from employees where employee_id=100;
dbms_output.put_line(v_emp.first_name||' '||v_emp.salary);
end;

/

REsult:
Steven 24000

In the above example v_emp is of employees row type..

PL/SQL: Backup Trigger.

To make synchronize backup of table, We create a table to store the backup.
Create Table:
create table heros_backup as select * from heros where 1=2;

This will create the table same as heros but does not copy the data of hero table.
Now
Create Backup Trigger:
create or replace trigger heros_bu
before insert or delete or update on heros
for each row
enable
begin
if inserting then
  insert into heros_backup (hero_name) values(:NEW.hero_name);
elsif deleting then
  delete from heros_backup where hero_name=:old.hero_name;
elsif updating then
  update heros_backup set hero_name = :NEW.hero_name where hero_name=:old.hero_name;
end if;

end;

Result:

Trigger HEROS_BU compiled

Now check by inserting data into heros.

insert into heros values('Muhammad Talha Zubair');

To Check:

select * from heros_backup;

Result:

Muhammad Talha Zubair

PL/SQL Audit Trigger

In previous topic we discuss the audit of table when inserting, updatind, or deleting any row. If we want to save the audition in another table then
First of all we have to create a table.

Create Table:
create table audit_heros
(
new_name varchar2(30),
old_name varchar2(30),
user_name varchar2(30),
operation_date varchar2(30),
operation varchar2(30)
);

/
Result:
Table audit_heros created.

Now we create an audit trigger on audit heros, that keeps the record of changing the heros table.

Audit Trigger:
create or replace trigger au_heros
before insert or delete or update on heros
for each row
enable
declare
v_user varchar2(30);
v_date varchar2(30);
begin
select user, to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') into v_user,v_date from dual;
if inserting then
  insert into audit_heros values(:NEW.hero_name,'NULL',v_user,v_date,'INSERT');
elsif deleting then
  insert into audit_heros values('NULL',:OLD.hero_name,v_user,v_date,'DELETE');
elsif updating then
  insert into audit_heros values(:NEW.hero_name,:OLD.hero_name,v_user,v_date,'update');
end if;
end;

/
Result:
Trigger au_heros  compiled.

Now by inserting or deleting or updating the row in heros table. we can check the working of au_heros trigger.
When Deleting:
delete from heros where hero_name='Salman';

Result:
3 rows deleted.
Now chek audit_heros table

select * from audit_heros;
Result:

NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE

When updating:
update heros set hero_name='Aybue khan' where hero_name='Ayube';

And again check the audit_heros table
select * from audit_heros;
Result:

NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE

Aybue khan Ayube HR 13/05/2017 16:40:56 update

Note:
NEW keyword allows you access a row that is being proceed . It means that it allow acess to a row that is being inserted or updated in the table

OLD keyword allow access to a table that is already inserted or updated in the table.

PL/SQL DML Trigger With Before Insert, Update and Delete DML Examples

To test this trigger, first of all we have to create a table.

Table:

create table heros

(hero_name varchar2(30));

Result:

Table HEROS created.

Now we create a dml trigger,

CREATE OR REPLACE TRIGGER bi_heros

BEFORE INSERT ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just inserted a line');

end;

/

Result:

Trigger BI_HEROS compiled

To check that is this trigger working or not, We Insert a row into the table and find out the result.

insert into heros values('Talha Zubair');

Result:

1 row inserted.

User HR Just inserted a line

We add another trigger on heros table.

CREATE OR REPLACE TRIGGER bu_heros

BEFORE update ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just updated a line');

end;

/

Update data

update heros set hero_name='Talha Zubair Khan' where hero_name='Talha Zubair';

Result:

1 row updated.

User HR Just updated a line

Create Insert, update, delete Trigger at a time.

code:

create or replace trigger tr_heros

before insert or delete or update on heros

for each row

enable

declare

v_user varchar2(30);

begin

Select user into v_user from dual;

if inserting then

dbms_output.put_line('User '||v_user||' Just Inserted A line');

elsif deleting then

dbms_output.put_line('User '||v_user||' Just deleted A line');

elsif updating then

dbms_output.put_line('User '||v_user||' Just Updated A line');

end if;

end;

/

Result:

Trigger TR_HEROS compiled

Now test the trigger.

inserting:

insert into heros values('Salman');

Result:

1 row inserted.

User HR Just Inserted A line

Updating:

delete from heros where hero_name='Salman';

Result:

1 rows deleted.

User HR Just deleted A line

PL/SQL: TRIGGERS INTRO

Named PL/SQL blocks which are stored in database are called triggers.
Or
Specialized stored programs which executes implicitly when trigger event occurs.
Events:
Events may be anything,
such as
1. DML statement, which includes insert or delete statement.
2. DDL statement, which includes create or alter statements.
3. System event, which includes shutdown or restart database.
4. User event, Which include logoff or log on of user on the database.

TYPES of TRIGGERS:
There are 5 types of trigges.

1. DML TRIGGERS.
As name show, this type or trigger fire with the dml statements, before or after the insert, delete or update statement.

2. DDL TRIGGERS:
These are the triggers which are created with the ddl statements, such as create or alter statement, These triggers fire when any changes occure.

3. SYSTEM EVENT TRIGGERS:
This type of triggers occure when database or system event occur, such as logon or log off of the database.

4. INSTEAD-OF TRIGGERS:
These tirggers enables you to stop or redirected the performance of dml triggers.

5. COMPOUND TRIGGERS:
These are multi tasking triggers that act as low level triggers when the data inserted, deleted or updated from the database table. Compound triggers used to audit, check, replace or save even before the change.

PL/SQL:WHILE LOOP

WHILE LOOP is also used for the itteration of  block of statement.
The best usable of while is when the number of itteration are unknown.

Syntax:
begin
while condition loop
statement1;
statement2;
statement3;
.
.
statementn;
end loop;

In the above example, while and loop are the keywords. and condition is the critarea , which decides the number of itteration.

Example:
declare
result number:=0;
counter number:=1;
table_num number:= &Enter_number_For_table;
begin
while counter <=10 loop
result:=counter*table_num;
dbms_output.put_line(table_num||' * '||counter||' = '||result);
counter:=counter+1;
end loop;
dbms_output.put_line('M Talha Zubair, Out from loop');

end;

Result:
19 * 1 = 19
19 * 2 = 38
19 * 3 = 57
19 * 4 = 76
19 * 5 = 95
19 * 6 = 114
19 * 7 = 133
19 * 8 = 152
19 * 9 = 171
19 * 10 = 190
M Talha Zubair, Out from loop


While Loop with Boolean Variable as test Variable:
Example:
declare
v_test boolean:=true;
counter number:=1;
begin
while v_test loop
dbms_output.put_line(counter);
counter:=counter+1;
if counter=10 then
exit;
end if;
end loop;
dbms_output.put_line('M Talha Zubair, Out from loop');
end;

Result:
1
2
3
4
5
6
7
8
9
M Talha Zubair, Out from loop

PL/SQL: FOR LOOP

FOR LOOP  is used for the fixed number of execution of statement or set of statement.

Syntax:
For Counter_variable in lower_limit . . upper limit Loop
statement 1;
statement 2;
statement 3;
.
.
statement n;
end loop;
end;

Counter variable us is used to continue the loop or execution of statements. IN is the keyword. lower LIMIT and UPPER LIMIT is the critarea for which loops executes the statement(s).

Example:
declare
counter number:=0;
begin
for counter in 1 .. 10 loop
dbms_output.put_line(counter||' I Love Pakistan');
end loop;

end;

Result:
1 I Love Pakistan
2 I Love Pakistan
3 I Love Pakistan
4 I Love Pakistan
5 I Love Pakistan
6 I Love Pakistan
7 I Love Pakistan
8 I Love Pakistan
9 I Love Pakistan
10 I Love Pakistan


To print the table of any number input by the user, The query will be as follow

Example:
declare
result number:=0;
table_num number:= &Enter_number_For_table;
begin
for counter in 1 .. 10 loop
result:=counter*table_num;
dbms_output.put_line(table_num||' * '||counter||' = '||result);
end loop;

end;

Result:
Enter_number_For_table = 19
19 * 1 = 19
19 * 2 = 38
19 * 3 = 57
19 * 4 = 76
19 * 5 = 95
19 * 6 = 114
19 * 7 = 133
19 * 8 = 152
19 * 9 = 171
19 * 10 = 190

PL/SQL: SIMPLE LOOPS

LOOPS executes statement or set of statement  or part of program for several times.
First of all we discuss Simple loop.

Simple Loop:
Syntax:
Loop
statement 1;
statement 2;
.
.
.
statement n;
End loop;

Example:
declare
counter number:=0;
result number;
num number:=&Enter_Number_For_Table;
begin
loop
counter := counter+1;
result := num*counter;
dbms_output.put_line(num||' * '||counter||' = '||result);
if counter>=10 then
exit;
end if;
end loop;

end;

Result:

Enter_Number_For_table = 14

14 * 1 = 14

14 * 2 = 28

14 * 3 = 42

14 * 4 = 56

14 * 5 = 70

14 * 6 = 84

14 * 7 = 98

14 * 8 = 112

14 * 9 = 126

14 * 10 = 140

First we declare variables for counter , result and a number for which table is created.

After begin part, We Define a loop. Increment by 1 in counter. and give way  how generate or calcute the result.

Then we print result for every execution.

Then we use IF STATEMENT  to stop or finalize the loop. Then end if and end of loop and end of program.
we can also Finalize the Loop as
Exit when counter>=10;
 The result of all query is above mentioned. If any problem,then feel free to ask.

PL/SQL: IF ELSEIF OR IF THEN ELSEIF STATEMENT

If Then else if statement is used with the multiple conditions in Selection structure.

Syntax:
IF condition then
statement(s);
elseif conditon  then
statement(s);
elseif  condition then
statement(s);
else
statement(s);
end if;
statement(s);
end;


IF THEN ELSE Statement is responsible for the execution of first statement if the condition is true. and if conditin is true then it will never evaluate the rest conditions .In case non of this condition is true, it evaluate the ELSE statement.

Example:

declare
place varchar2(30):= '&Enter_City_name';
begin
if place = 'Metropolis' then
dbms_output.put_line('City Is protected by Salman Khan');
elsif place='Gotham' then
dbms_output.put_line('City is protected by Amir khan');
elsif place='Amazon' then
dbms_output.put_line('City is protected by Shahrukh Khan');
else
dbms_output.put_line('Call to the Police');
end if;
dbms_output.put_line('Thank you For Contacting us');

end;

Result:
Enter_City_name = Metropolis

City Is protected by Salman Khan
Thank you For Contacting us


Now check it with using city name that is not in else statements.
Try it again.

declare
place varchar2(30):= '&Enter_City_name';
begin
if place = 'Metropolis' then
dbms_output.put_line('City Is protected by Salman Khan');
elsif place='Gotham' then
dbms_output.put_line('City is protected by Amir khan');
elsif place='Amazon' then
dbms_output.put_line('City is protected by Shahrukh Khan');
else
dbms_output.put_line('Call to the Police');
end if;
dbms_output.put_line('Thank you For Contacting us');
end;

Result:
Enter_City_name = Faisalabad

Call to the Police

Thank you For Contacting us

PL/SQL: IF ELSE OR IF THEN ELSE STATEMENT

If then else statement executes sequence of statements only when the condition is evaluated to be true.

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

Example:
declare
v_num number:= &Enter_a_Number;
begin
if mod(v_num,2)=0 then
dbms_output.put_line('You Entered an Even number '||v_num);
  else
  dbms_output.put_line('You Entered an Odd number '||v_num);
end if;
dbms_output.put_line('Muhammad Talah Zubair');
end;

/
give input 10

Result:
You Entered an Even number 10
Muhammad Talah Zubair

Now check it again, by providing odd number as input , and check out the result.

Example:
declare
v_num number:= &Enter_a_Number;
begin
if mod(v_num,2)=0 then
dbms_output.put_line('You Entered an Even number '||v_num);
  else
  dbms_output.put_line('You Entered an Odd number '||v_num);
end if;
dbms_output.put_line('Muhammad Talah Zubair');
end;

/
give input 11 

Result:
You Entered an Odd number 11

Muhammad Talah Zubair

PL/SQL: Constant

Constants are the user defined identifiers whose value remain unchanged during the execution of a program.
Syntax:
constant_name Constant datatype(size) := value;

Constant must be initialized during the declaration.

Example:
Declare
v_pi constant number:=3.14157;
begin
dbms_output.put_line(v_pi);
end;

Result:
3.14157

OR:

declare
v-pi constant number(7,6) not null default 3.14157;
begin
dbms_output.put_line(v_pi);

Result:
3.14157

This query prevent you to assigning null values to you constant.

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.
.