DBMS Handson

DDL HANDSON
create table Department (dept_id number(10),dept_name varchar(20));
Press continue
alter table Department add Location varchar(20);

Press continue
drop table Department;

DQL HANDS ON

Step 1: click on the commands given side
step 2: select DEPT_NAME
              from department
              where DEPT_ID = 100;


DML HANDSON

Step 1: insert into Employee values(1,'AAA',20000,100);
Insert into Employee values(2,'BBB',30000,200);
insert into Employee values(3,'CCC',30000,300);
commit;
STEP 2: update Department set DEPT_NAME='FINANCE' where DEPT_ID=100;
commit;


Constraints hands on

alter table department add constraint dept_id primary key (dept_id);
alter table employee add foreign key (dept_id) references department (dept_id);

*5. Joins hands-on*

Step 1:
Simply enter User name and password

Step 2:
Select SALARY
from Employee, department
where DEPT_NAME = 'IT'
and Employee.DEPT_ID = department.DEPT_ID;

Step 3:
Select DEPT_NAME
from department
where DEPT_ID = (select DEPT_ID from
(Select DEPT_ID, count(DEPT_ID)
from Employee
group by DEPT_ID
order by 2 desc) where rownum = 1);p
----------------
PL/SQL
SET SERVEROUTPUT ON
DECLARE
    i NUMBER(3);
    j NUMBER(3);
   s NUMBER(3);
BEGIN
    s := 0;
    i := 2;
    LOOP
        j := 2;
        LOOP
            EXIT WHEN( ( MOD(i, j) = 0 )
                        OR ( j = i ) );
            j := j + 1;
        END LOOP;
        IF( j = i )THEN
           s := s + I;       
        END IF;
        i := i + 1;
        exit WHEN i = 30;
    END LOOP;
   dbms_output.Put(s||'   ');
 dbms_output.new_line;
END;
/

CURSORS
SET SERVEROUTPUT ON
DECLARE
    s NUMBER(3);
    CURSOR employee_cur IS
      SELECT salary
      FROM   Employee;
BEGIN
    s := 0;
    FOR employee_rec IN employee_cur LOOP
        IF employee_rec.salary < 50000 THEN
          s := s + 1;
        END IF;
    END LOOP;
 dbms_output.Put(s||'   ');
dbms_output.new_line;
END;
/


IamAnarchy:
PL/SQL:
SET SERVEROUTPUT ON
DECLARE
    i NUMBER(3);
    j NUMBER(3);
   s NUMBER(3);
BEGIN
    s := 0;
    i := 2;
    LOOP
        j := 2;
        LOOP
            EXIT WHEN( ( MOD(i, j) = 0 )
                        OR ( j = i ) );
            j := j + 1;
        END LOOP;
        IF( j = i )THEN
           s := s + I;         
        END IF;
        i := i + 1;
        exit WHEN i = 30;
    END LOOP;
   dbms_output.Put(s||'   ');
 dbms_output.new_line;
END;
/

CURSORS:
SET SERVEROUTPUT ON
DECLARE 
s NUMBER(3);
 c_salary Employee.salary%type;
CURSOR c_employee is 
      SELECT salary FROM Employee; 
BEGIN 
s := 0;
   OPEN c_employee; 
   LOOP 
      FETCH c_employee into c_salary; 
      EXIT WHEN c_employee%notfound; 
     IF c_salary < 50000 THEN
          s := s + 1;
        END IF;
   END LOOP; 
 dbms_output.put_line(s || ' '); 
   CLOSE c_employee; 
END; 
/

AVERAGE:
create or replace function findAverage(a number,b number,c number)
return number
as out number;
begin
 out := a+b+c;
 out := out/3;
 return out;
end;
/