SQL-Working With Dates 1-Example exercise based on date data type of employee table for HR schema

--1. sysdate,hiredate  as general format
================================

SQL> select sysdate from dual;

SYSDATE
---------
14-JUL-17

SQL> select hire_date from employees where employee_id=100;

HIRE_DATE
---------
17-JUN-87

SQL>

--2 Display the only year of hire date  for employee_id 100,101,102;
===============================================================

SQL> select employee_id,to_char(hire_date,'YYYY')year from employees where employee_id in (100,101,102);

EMPLOYEE_ID YEAR
-----------               ----
        100               1987
        101               1989
        102               1993

SQL> select to_char(sysdate,'YYYY')year from dual;

YEAR
----
2017

SQL>

--3 Display the only month in full word of hire date  for employee_id 100,101,102;
===============================================================

SQL> select employee_id,to_char(hire_date,'MONTH')MONTH from employees where employee_id in (100,101,102);

EMPLOYEE_ID  MONTH
-----------               ---------
        100               JUNE
        101               SEPTEMBER
        102              JANUARY

--4 Display the only DAY_of_week of hire date for employee_id 100,101,102;
==========================================================

SQL> select employee_id,to_char(hire_date,'DAY')DAY_of_week from employees where employee_id in (100,101,102);

EMPLOYEE_ID    DAY_OF_WE
-----------                 ---------
        100                WEDNESDAY
        101                THURSDAY
        102                WEDNESDAY

SQL>

--5 Display the only DAY_of_week in three letter  of hire date  for employee_id 100,101,102;
=====================================================================

SQL> select employee_id,to_char(hire_date,'DY')DAY_of_week from employees where employee_id in (100,101,102);

EMPLOYEE_ID       DAY
-----------                      ---
        100                    WED
        101                    THU
        102                    WED

--6 Display the only month in three letters word  of hire date  for employee_id 100,101,102;
==================================================================

SQL> select employee_id,to_char(hire_date,'MON')MON from employees where employee_id in (100,101,102);

EMPLOYEE_ID MON
-----------                ---
        100               JUN
        101               SEP
        102               JAN

--7 Display the only year in words of hire date for employee_id 100,101,102;
==============================================================
SQL> select employee_id,to_char(hire_date,'YEAR')year from employees where employee_id in (100,101,102);

EMPLOYEE_ID                 YEAR
-----------                   ------------------------------------------
        100                   NINETEEN EIGHTY-SEVEN
        101                   NINETEEN EIGHTY-NINE
        102                  NINETEEN NINETY-THREE

SQL>

--8 Write a query to find the list of employees who were hire before 1990.
=======================================================

SQL> select last_name,to_char(hire_date,'DD-Mon-yyyy') from employees where to_date(hire_date,'DD-Mon-RR')<'01-Jan-1990';

LAST_NAME                 TO_CHAR(HIR
-------------------------     -----------
King                                 17-Jun-1987
Kochhar                           21-Sep-1989
Whalen                             17-Sep-1987

SQL>

--9  find the numetic day of month whose last_name are King,Austin,Lorentz;
=========================================================

SQL> select last_name,hire_date,to_char(hire_date,'DD')"Numeric Day of Month" from employees where last_name in('King','Austin','Lorentz');

LAST_NAME           HIRE_DATE     Numeric Day Of Month
------------------------- ---------                     --
Austin                    25-JUN-97                 25
King                      30-JAN-96                  30
King                      17-JUN-87                  17
Lorentz                   07-FEB-99                 07


10-find two digit of  value of month whose last_name are King,Austin,Lorentz;
=========================================================
LAST_NAME  HIRE_DATE Two Digit Value Of Month
Austin 25-JUN-97      06
King 30-JAN-96      01
King 17-JUN-87      06
Lorentz 07-FEB-99      02

PL/SQL Procedure- Create the procedure add_jobs on the jobs table of HR schema.

PL/SQL Procedure-Insert data into Table
======================================================================

create or replace procedure add_jobs(v_job_id  jobs.job_id%type,v_job_title   jobs.job_title%type,v_max_salary   jobs.max_salary%type,v_min_salary  jobs.min_salary%type)
is
begin
insert into jobs(job_id, job_title,min_salary,max_salary) values(v_job_id ,v_job_title ,v_min_salary ,v_max_salary );
end;
/

=======================================================================
--Method 1-call the procedure  
========================================================================

begin
add_jobs('DM','Dupty Manager',11000,1000);
end;
/

--Verify the data inserted into the table jobs

SQL> select * from jobs where job_id='DM';

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
DM         Dupty Manager                             1000      11000

SQL>
--========================================================================
Method 2 :Ask user to enter the  values  into jobs  table and verify the data
=============================================================

SQL> begin
  2  add_jobs( '&v_job_id' ,'&v_job_title' ,&v_min_salary ,&v_max_salary);
  3  end;
  4  /
Enter value for v_job_id: HR1
Enter value for v_job_title: HR Dept
Enter value for v_min_salary: 500
Enter value for v_max_salary: 3500
old   2: add_jobs( '&v_job_id' ,'&v_job_title' ,&v_min_salary ,&v_max_salary);
new   2: add_jobs( 'HR1' ,'HR Dept' ,500 ,3500);

PL/SQL procedure successfully completed.

--check the inserted data into the table 

SQL> select * from jobs where job_id='HR1';

JOB_ID     JOB_TITLE                           MIN_SALARY    MAX_SALARY
---------- ----------------------------------- ----------                       ----------
HR1        HR Dept                                    500                            3500

SQL>

Program6-Write a program to find the area of Square in PL/SQL

Method-1:
======================================================================
Suppose Square have length and width of equal size .so area of Square =side*side
Input-let its  side is a
Output-Area of the Square
Operation- Square_area=a*a

Code-
SQL> set serveroutput on;
SQL> declare
  2   a  number;
  3   Begin
  4  a :=20;
  5  Dbms_output.put_line('Area of the Square='||a*a);
  6  End;
  7  /
Area of the Square=400

PL/SQL procedure successfully completed.

======================================================================
Method-2:
======================================================================
Input-Ask user to enter side of the square and store the result into third variable result.
Output- Area of the square
Operation- result=a*a

SQL> declare
  2   a  number;
  3  result number;
  4   Begin
  5  a :=&a;
  6  result:=a*a;
  7  Dbms_output.put_line('Area of the Square='||result);
  8  End;
  9  /
Enter value for a: 10
old   5: a :=&a;
new   5: a :=10;
Area of the Square=100

PL/SQL procedure successfully completed.

Program5-Write a program to calculate the simple interest in PL/SQL

Method-1:
==========================================================
Input-let p=1000 ,r=10 and t=10
Output- Simple interest (si) =1000
Calculation -Let Principal(P),Rate (r% per annum) and Time = t years Then
Simple Interest, si = P*r*t/100
===========================================================
Code-
SQL> set serveroutput on;
SQL> declare
  2  p number;
  3  r number;
  4  t number;
  5  si number;
  6  Begin
  7  p :=1000;
  8  r :=10;
  9  t :=10;
 10  si:=(p*r*t)/100;
 11  Dbms_output.put_line('The Simple Interest='||si);
 12  End;
 13  /
The Simple Interest=1000

PL/SQL procedure successfully completed.
======================================================================

Method-2:
======================================================================
Input-Ask user to enter the principal amount, rate of interest and time take and store the result into third variable si .
Output- simple interest
Operation-Simple Interest, si = P*r*t/100
========================================
Code-
SQL> declare
  2  p number;
  3  r number;
  4  t number;
  5  si number;
  6  Begin
  7  p :=&p;
  8  r :=&r;
  9  t :=&t;
 10  si :=(p*r*t)/100;
 11  Dbms_output.put_line('The Simple Interest='||si);
 12  End;
 13  /
Enter value for p: 1000
old   7: p :=&p;
new   7: p :=1000;
Enter value for r: 10
old   8: r :=&r;
new   8: r :=10;
Enter value for t: 10
old   9: t :=&t;
new   9: t :=10;
The Simple Interest=1000

PL/SQL procedure successfully completed.

SQL>

Program4-Write a program to find the area of rectangle in PL/SQL

Method-1:
=================
Suppose rectangle have length and breadth .so area of rectangle is multiplication of length and width.
Input-length=20 meters and width=10 meters
Output-200 meter square
Operation- area of rectangle=length*width

Code-
SQL> set serveroutput on;
SQL> declare
  2      length  number;
  3      width  number;
  4      Begin
  5         length  :=20;
  6         width :=10;
  7     Dbms_output.put_line('Area of the Rectangle='||length*width);
  8     End;
  9  /
Area of the Rectangle=200

PL/SQL procedure successfully completed.
========================================================================

Method-2:
=======================
Input-Take two variables length and width  and store the result into third variable result.
Output- Area of the rectangle
Operation- result=length*width

SQL> declare
  2      length  number;
  3      width  number;
  4     result number;
  5      Begin
  6         length  :=20;
  7         width :=10;
  8        result:=length*width;
  9     Dbms_output.put_line('Area of the Rectangle='||result);
 10     End;
 11  /
Area of the Rectangle=200

PL/SQL procedure successfully completed.

SQL>
===============================================================
Method-3:
====================
Input-Ask user to enter two number  length and width  and store the result into third variable rec_area.
Output- rec_area
Operation-Area of rectangle

Code-
SQL> declare
  2      length  number;
  3      width  number;
  4      rec_area number;
  5      Begin
  6         length :=&length ;
  7         width :=&width;
  8      rec_area :=length*width;
  9
 10     Dbms_output.put_line('Area of the Rectangle='||rec_area);
 11     End;
 12  /
Enter value for length: 20
old   6:        length :=&length ;
new   6:        length :=20 ;
Enter value for width: 5
old   7:        width :=&width;
new   7:        width :=5;
Area of the Rectangle=100

PL/SQL procedure successfully completed.

SQL>

Program3- Advance concept : Write a program to add two number using the function and procedure.

Write a program to add two number using the function  named as find_sum  and procedure.

Method-1

Input-Suppose two input values are n1 and n2
Output-Add of two numbers in third variable

Code-
SQL> create or replace function find_sum
  2  (n1 in number, n2 in number)
  3  return number
  4  as
  5  v_sum number;
  6  begin
  7  v_sum := n1+n2;
  8  return v_sum;
  9  end;
 10  /

Function created.

--Perform the sum using function find_sum

SQL> select find_sum (10, 30) sum from dual;

      SUM
----------
        40

SQL> select find_sum (10, 30)  from dual;

FIND_SUM(10,30)
---------------
             40

SQL> select find_sum (10, 30) "Sum of two numbers" from dual;

Sum of two numbers
------------------
                40


=================================================================
Method-2 :Procedure
==================================================================

SQL> create or replace procedure find_sum_procedure
  2  (n1 in number, n2 in number)
  3  as
  4  v_sum number;
  5  begin
  6  v_sum := n1+n2;
  7  DBMS_OUTPUT.PUT_LINE('Sum of two numbers='||v_sum);
  8  end;
  9  /

Procedure created.

SQL> execute find_sum_procedure(10,20);

Sum of two numbers=30

PL/SQL procedure successfully completed.

SQL>

Program2-Write a program to add two number in PL/SQL

This program can be solved in many ways. So Let us understand how the add is performed
Method-1:
Suppose we have two constant value 2 and 3 .So we need the addition operator(+) to add these two numbers
Input-2 and 3
Output-5
Operation- addition so use addition operator (+)

Code-
SQL> set serveroutput on
SQL>
SQL>
SQL> Begin
  2  Dbms_output.put_line(2+3);
  3  End;
  4  /
5

PL/SQL procedure successfully completed.

SQL>


Method-2:
Suppose you want to add two values taken at run time
Input-Take two variable and store two value into it.Assume two variable are n1 and n2.
Output- addition a+b
Operation- addition (+)
Code-
SQL> declare
  2  n1 number;
  3  n2 number;
  4  Begin
  5  n1 :=2;
  6  n2 :=3;
  7  Dbms_output.put_line(n1+n2);
  8  End;
  9  /
5

PL/SQL procedure successfully completed.

SQL>



Method-3:
Take two number n1 and n2 and store it into third variable result.
Input-Take two variable and store the result into third variable result.
Output- Result of addition
Operation- addition (+)

SQL> declare
  2    n1 number;
  3    n2 number;
  4    result number;
  5    Begin
  6     n1 :=2;
  7     n2 :=3;
  8     result:=n1+n2;
  9    Dbms_output.put_line(result);
 10    End;
 11    /
5

PL/SQL procedure successfully completed.

SQL>
Method-4:
Ask user to enter two number n1 and n2 and store it into third variable result.
Input-Take two variable and store the result into third variable result.
Output- Result of addition
Operation- addition (+)

Code-
SQL> declare
  2    n1 number;
  3    n2 number;
  4    result number;
  5    Begin
  6     n1 :=&n1;
  7     n2 :=&n2;
  8     result:=n1+n2;
  9    Dbms_output.put_line(result);
 10    End;
 11    /
Enter value for n1: 10
old   6:    n1 :=&n1;
new   6:    n1 :=10;
Enter value for n2: 20
old   7:    n2 :=&n2;
new   7:    n2 :=20;
30

PL/SQL procedure successfully completed.

SQL>

Note- This program can be solved in many ways .I have taken from very basic to often used method.
I hope you can get the idea how to approach the problem to solve in real-time in PL/SQL.

SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

Suppose we have two table customer and supplier
CREATE TABLE supplier
( supplier_id numeric(10) not null,
 supplier_name varchar2(50) not null,
 contact_name varchar2(50),
 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

insert into supplier values(1,'Sarat','Chand');
insert into supplier values(2,'Sartaj','Ahmed');
insert into supplier values(3,'Saral','Chand');


CREATE TABLE products
( product_id numeric(10) not null,
 supplier_id numeric(10) not null,
 CONSTRAINT fk_supplier
   FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE CASCADE
);

insert into products values(101,1);
insert into products values(102,2);
insert into products values(103,3);

--Check/verify the entered data into the table using below command

select * from products;
select * from supplier ;

--not Allowed to add the product into the supplier table because the supplier id is not exist in the parent table name supplier

insert into products values(103,4);

--Error Message
--ORA-02291: integrity constraint (HR.FK_SUPPLIER) violated - parent key not found

So we can say that the table relation between table is established

--The data can be deleted from the supplier table because it is parent table but cannot delete the data into child table
--delete the data from supplier table whose supplier_id is 1;
delete from supplier where supplier_id=1;

delete from products where supplier_id=1;
--check the constraints
select * from user_cons_columns;
select * from user_constraints;