Pl/SQl Programs ADS

16.
declare
cursor cursor is select * from emp7567 order by salary desc;
id emp7567.id%type;
name emp7567.name%type;
salary emp7567.salary%type;
begin
open cursor;
loop
fetch cursor into id,name,salary;
dbms_output.put('Id: '||id||' ');
dbms_output.put('Name: '||name||' ');
dbms_output.put('Salary: '||salary||' ');
dbms_output.put_line(' ');
exit when(cursor%rowcount>5);
end loop;
close cursor;
end;

17.
declare
rows number;
begin

update emp7567
set
salary=salary+0.25*salary;

if sql%notfound then
dbms_output.put_line('No Row Updated');
else
rows:=sql%rowcount;
dbms_output.put_line(rows||' Rows Updated');
end if;


end;

18.

declare
cursor cursor is select * from emp7567;
id emp7567.id%type;
name emp7567.name%type;
salary emp7567.salary%type;
begin
open cursor;
loop
fetch cursor into id,name,salary;
dbms_output.put('Id: '||id||' ');
dbms_output.put('Name: '||name||' ');
dbms_output.put('Salary: '||salary||' ');
dbms_output.put_line(' ');
exit when(cursor%notfound);
end loop;
dbms_output.put_line(cursor%rowcount||' Results Found');
close cursor;
end;

19.

create trigger uppercase7567 before insert  or update on emp7567
for each row
when (New.id>0)
begin
:New.name:=UPPER(:new.name);
end;

20.

PL/SQL Programs ADS Lab

Programs:

1.      1.to display the word “HELLO”

Code: 

declare
word varchar2(10) :='Hello';
begin
dbms_output.put_line(word);
end; 

2.which will get the salary of an employee with particular id from

emp table and display it on the screen

Code:

declare 
ida number :=&a; 
salary emp7567.salary%type; 
begin 
select salary into salary from emp7567 where id=ida; 

dbms_output.put_line(salary); 
 
end;

3.

which

creates two variables in the outer block and assign their product to the third

variable created in the inner block
declare
a number:=75;
b number:=67;
begin
declare
c number;
begin
c :=a*b;
dbms_output.put_line('Product is : '||c);
end;
end;

 4.

which

will increase the salary of the employees by 25%, you can declare a constant

and use it thro

ughout the program

declare
increment number(2):=0.25;
begin
update emp7567
set
tsalary= tsalary + tsalary*increment;

if sql%notfound then
dbms_output.put_line('No Rows Updated');
else
dbms_output.put_line(sql%rowcount||' Rows Updated');
end if;
end;

5.

to declare a record called

employee_rec

based on a user

-

defined type

Code:

DECLARE
   employee_rec emp7567%rowtype;
BEGIN
   SELECT * into employee_rec
   FROM emp7567
   WHERE id = 1507567; 
   dbms_output.put_line('Employee ID: ' || employee_rec.id);
   dbms_output.put_line('Employee Name: ' || employee_rec.name); 
   dbms_output.put_line('Employee Salary: ' || employee_rec.tsalary);
END;  

6.

create procedure adslab  
as 
begin 
 
goto section3; 
 
<< Section1>> 
begin 
dbms_output.put_line('Section 1'); 
goto section4; 
end; 
 
 
<< Section2>> 
begin 
dbms_output.put_line('Section 2'); 
goto section1; 
end; 
 
 
<< Section3>> 
begin 
dbms_output.put_line('Section 3'); 
goto section2; 
end; 
 
 
<< Section4>> 
begin 
dbms_output.put_line('Section 4'); 
end; 
 
end;

7.

which

use the relational operators to compare character values for equality or

inequality.

declare
char varchar2(1):='b';
begin
if char='a' then
dbms_output.put_line('Equal to a');
else
dbms_output.put_line('Not Equal to a');
end if;
end;

8.

declare
input number:=&a;
begin
if Mod(input,2)=0 then
dbms_output.put_line('Even');
else
dbms_output.put_line('Odd');
end if;
end;

9.

declare
input number:=&a;
i number :=1;
begin
loop
dbms_output.put_line(input||' * '||i||' = '||input*i);
i:=i+1;
exit when(i=11);
end loop;
end;

10.

declare
input number:=&a;
i number(1);
begin
for i in 1..10 loop
dbms_output.put_line(input||' * '||i||' = '||input*i);
end loop;
end;

11.
declare
input number:=10;
i number:=1;
begin
while i<11 loop
dbms_output.put_line(input);
input:=input+1;
i :=i+1;
end loop;
end;

12.

declare
j number;
i number;
begin
for i in 1..3 loop
    for j in 1..10 loop
        dbms_output.put(i*j||' ');
    end loop;
    dbms_output.put_line('');
end loop;   

end;

13.

declare
j number;
i number;
begin
<< loop1 >> /*Label of loop*/
for i in 1..4 loop
<< loop2 >>
    for j in 1..10 loop
        dbms_output.put(i*j||' ');
    end loop loop2;
    dbms_output.put_line('');
end loop loop1;   

end;

14.

declare
a number:=1;
begin

while a<10 loop
if a=5 then
    goto breakloop;
else
a:=a+1;
dbms_output.put_line(a);
end if;
end loop;

<<breakloop>>
dbms_output.put_line('Loop Breaked');

end;

15.

declare
Type names is varray(10) of varchar(10);
Type salary is varray(10) of number;
namearray names;
salaryarray salary;
total number;
begin
namearray:=names('John','Jennie','Ben','Ash','May','Max','Broke','Misty','Sherry','Ammmy');
salaryarray:=salary(2500,1200,1400,1500,1700,1230,1450,1681,1421,7567);
total:=namearray.count;
for i in 1..total loop
    insert into emp7567 values(i,namearray(i),salaryarray(i));
end loop;
end;

16.

Giveaway GndecProgramming

PHP and AJAX

Test4.php:

<?php

$q=$_GET['q'];

$c=new mysqli("localhost","root","naina","nearur");

if($c->connect_error){
    die("eror : ".$c->connect_error);
}else{
    $sql="Select * from bank";

    $result=$c->query($sql);
    $h="";
    if($result->num_rows>0){
        while ($row=$result->fetch_assoc()) {
            $n=$row['userid'];
            if(stristr($q, substr($n, 0,strlen($q)))){
                if($h == ""){
                    $h=$n;
                }else{
                    $h.=" , ".$n;               
                }
            }

        }
        echo $h;
    }   
}

 ?>

Test4.html:

<html>
<head>
    <title></title>
<script type="text/javascript">
   
    function show(){
        alert("hello");
       
    }

</script>

<script>
function showHint(str) {
   
    if(str.length==0){
            document.getElementById("h").innerHTML="";
        }else{
        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("h").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET", "test4.php?q=" + str, true);
        xmlhttp.send();

            /*var xml=new XMLHttpRequest();


            xml.onreadystatechange=function(){
                if(this.readyState==4 && this.status==200){
                    alert("hello");
                    document.getElementById.("h").innerHTML=this.responseText;
                }
            };

            xml.open("POST","test4.php",true);
            xml.setRequestHeader("Content-type","application/x-www-form-urlencoded");
            xml.send("q="+str);*/
        }
}
</script>


</head>
<body>
<form>
<input type="text" onkeyup="showHint(this.value)">
</form>
<p id="h">Suggestions</p>
</body>
</html>

Using Json in AJAX Application

Test.php file:


<?php 

$c=new mysqli("localhost","root","naina","nearur");

$obj = json_decode($_POST["v"], false);

$sql="Select * from ".$obj->table;


$result=$c->query($sql);

if($result->num_rows>0){
   
    $r = $result->fetch_all(MYSQLI_ASSOC);//{
    //    $s .="<tr><td>".$r["name"]."</td><td>".$r["userid"]."</td></tr>";
    //}
    //$s .="</table>";
    echo json_encode($r);
}else{
    echo "0 rows";
}


?>



Test.html:

<html>
<head>
    <title>WT</title>
<script type="text/javascript">
   
function g (a) {
    var my={"table":a};
    var json=JSON.stringify(my);
    var xml=new XMLHttpRequest();
    xml.onreadystatechange=function(){
        if(this.readyState==4&& this.status==200){
            var rjson=JSON.parse(this.responseText);
           
            txt="<table border='1'>";//<tr><th>ID</th><th>Name</th><th>Email</th><th>Roll</th><th>Gender</th><th>PINCODE</th><th>Mobile</th></tr>";
            for(x in rjson){
                txt+="<tr>";
                for (h in rjson[x]) {       
                txt+="<td>"+rjson[x][h]+"</td>";
                }
                txt+="</tr>";
            }
            document.getElementById("d").innerHTML=txt;
        }
    };

    xml.open("POST","test.php",true);
    xml.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    xml.send("v="+json);
   
}


</script>

</head>

<body>
    <select onchange="g(this.value)" size="1">
        <option>.....Select....</option>
        <option>Users</option>
        <option>Bank</option>
        <option>Auri</option>
        <option>Student</option>
        <option>Employees</option>
    </select>
    <p id="d">Here</p>
</body>
</html>

Kruskal's Algoritm DAA

Question: Write a program to find the minimum cost of connecting all the engineering colleges in your state using Kruskal's algorithm.
Code:

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

public class Kruskal {

    static  class  edge{
        int distance,s,d;

        public edge(int distance, int s, int d) {
            this.distance = distance;
            this.s = s;
            this.d = d;
        }

        @Override        public String toString() {
            return "edge{" +
                    "distance=" + distance +
                    ", s=" + s +
                    ", d=" + d +
                    '}';
        }
    }

    public static void main(String[] nt){

        int graph[][]= {{0, 2, 0, 6, 0},
                {2, 0, 3, 8, 5},
                {0, 3, 0, 0, 7},
                {6, 8, 0, 0, 9},
                {0, 5, 7, 9, 0},
        };

        boolean[] visited=new boolean[graph.length];
        ArrayList<edge>edgeArrayList=new ArrayList<>();

        for(int i=0;i<graph.length;i++){
            visited[i]=false;
            for(int x=0;x<graph[i].length;x++){
                if(graph[i][x]!=0){
                    edgeArrayList.add(new edge(graph[i][x],i,x));
                }
            }
        }

        Collections.sort(edgeArrayList, new Comparator<edge>() {
            @Override            public int compare(edge edge, edge t1) {
                return edge.distance-t1.distance;
            }
        });
        int count=0;
        edge edgenow;
        int min=0;
        while (count!=graph.length-1){
            edgenow=edgeArrayList.get(0);
            edgeArrayList.remove(0);
            if(visited[edgenow.d]==false || visited[edgenow.s]==false){
                min+=edgenow.distance;
               System.out.println((edgenow.s+1)+" -> "+(edgenow.d+1)+" : "+edgenow.distance);
                visited[edgenow.d]=true;
                visited[edgenow.s]=true;
                count++;
            }
        }
        System.out.println("Cost is : "+min);
    }
}

Output:

Dijkstra’s algorithm.

Question: Write a program to find shortest path from your home to college using Dijkstra’s algorithm.

Code:

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

public class Dijkstras {

    static class vertex{
        int distance;
        boolean visited;
        int no;
        int previous;

        @Override        public String toString() {
            return "vertex{" +
                    "distance=" + distance +
                    ", visited=" + visited +
                    ", no=" + (no+1) +
                    ", previous=" + (previous+1) +
                    '}';
        }

        public vertex(int distance, boolean visited, int no, int previous) {
            this.distance = distance;
            this.visited = visited;
            this.no = no;
            this.previous = previous;
        }


    }

    public static void main(String[] nt){
        ArrayList<vertex> arrayList=new ArrayList<>();
        ArrayList<vertex> result=new ArrayList<>();
       /* int graph[][] = new int[][]{{0, 4, 0, 0, 0, 0, 0, 8, 0},                {4, 0, 8, 0, 0, 0, 0, 11, 0},                {0, 8, 0, 7, 0, 4, 0, 0, 2},                {0, 0, 7, 0, 9, 14, 0, 0, 0},                {0, 0, 0, 9, 0, 10, 0, 0, 0},                {0, 0, 4, 14, 10, 0, 2, 0, 0},                {0, 0, 0, 0, 0, 2, 0, 1, 6},                {8, 11, 0, 0, 0, 0, 1, 0, 7},                {0, 0, 2, 0, 0, 0, 6, 7, 0}        };       */       int[][] graph={{0,4,8,0,0},{0,0,5,8,10},{0,4,0,0,3},{0,0,0,0,6},{0,0,0,7,0}};
       for (int i=0;i<graph.length;i++){
            arrayList.add(new vertex(Integer.MAX_VALUE,false,i,Integer.MAX_VALUE));
        }

        arrayList.get(0).distance=0;
        arrayList.get(0).previous=0;

        Comparator<vertex> com=new Comparator<vertex>() {
            @Override            public int compare(vertex vertex, vertex t1) {
                return vertex.distance-t1.distance;
            }
        };

        vertex poped;
        while (arrayList.size()!=0){
            Collections.sort(arrayList,com);
            poped=arrayList.get(0);
            arrayList.remove(0);


            if(poped.visited==false){
                for (int i=0;i<graph[poped.no].length;i++){
                    if(graph[poped.no][i]!=0&& find(arrayList,i)!=Integer.MAX_VALUE){
                        int x=find(arrayList,i);
                            if(graph[poped.no][i]+poped.distance<arrayList.get(x).distance){
                                arrayList.get(x).distance=graph[poped.no][i]+poped.distance;
                                arrayList.get(x).previous=poped.no;
                            }
                    }
                }
                poped.visited=true;
                result.add(poped);
            }
        }

        Comparator<vertex>comp2=new Comparator<vertex>() {
            @Override            public int compare(vertex vertex, vertex t1) {
                return vertex.no-t1.no;
            }
        };
        Collections.sort(result,comp2);
        for(int i=0;i<result.size();i++){

            System.out.println(result.get(i).toString());
        }

    }

    static int find(ArrayList<vertex> arrayList,int n){
        int i=Integer.MAX_VALUE;

        for (int x=0;x<arrayList.size();x++){
            if(arrayList.get(x).no==n){
                return x;
            }
        }
        return i;
    }
}

Output: