Assignment 1 Q.5

By September 25, 2014


Book (bno, bname, pubname, price)
Department (dno, dname)
***************************************************************************
SQL> create table department52(dno number(5) primary key,dept_name varchar2(10));

Table created.

SQL> desc department52;

SQL>  insert into department52 values(11,'Computer');
1 row created.
SQL>  insert into department52 values(12,'Civil');
1 row created.
SQL>  insert into department52 values(13,'Electronic');
1 row created.
SQL>  insert into department52 values(14,'IT');
1 row created.
SQL>  insert into department52 values(15,'Mech');
1 row created.

SQL> select * from department52;

SQL>  create table book52(bno number(5) primary key,b_name varchar2(10),pub_name   varchar2(10),price number(5) check (price>0),dno number(5) references department52);

Table created.

SQL> desc book52;

SQL>  insert into book52 values(1,'VB','BPB',200,11);
1 row created.
SQL>  insert into book52 values(2,'Graphics','Nirali',350,12);
1 row created.
SQL>  insert into book52 values(3,'DS','vision',250,11);
1 row created.
SQL>  insert into book52 values(4,'Design','Thakur',150,13);
1 row created.
SQL>  insert into book52 values(5,'Micro','Bill',400,14);
1 row created.
SQL>  insert into book52 values(6,'RDBMS','BPB',100,11);
1 row created.
SQL>  insert into book52 values(7,'Math','Tech-max',220,15);
1 row created.

SQL> select * from book52;

1)create or replace   pl/sql function to return total no of books purchased by given department

SQL> create or replace function f3
  2  (deptname varchar2)
  3  return number
  4  is
  5  v_bno number(5);
  6  begin
  7  select count(book52.bno) into v_bno from department52,book52
  8  where department52.dno=book52.dno and
  9  department52.dept_name=deptname;
 10  return v_bno;
 11  END f3;
 12  /

Function created.

SQL> DECLARE
  2     v_deptname varchar2(10);
  3      c number;
  4     BEGIN
  5     v_deptname:='&v_deptname';
  6    c:=f3(v_deptname);
  7     dbms_output.put_line('The total no.of books-'||c);
  8     END;
  9   /
***Output***
Enter value for v_deptname: Computer
old   5:    v_deptname:='&v_deptname';
new   5:    v_deptname:='Computer';

The total no.of books-3

PL/SQL procedure successfully completed.

2)To return total expenditure on book of given department

SQL> create or replace function f4
  2  (deptname varchar2)
  3  return number
  4  is
  5  v_price number(5);
  6  begin
  7  select sum(book52.price) into v_price from department52,book52
  8  where department52.dno=book52.dno and
  9  department52.dept_name=deptname;
 10  return v_price;
 11  END f4;
 12  /          

Function created.

SQL> DECLARE
  2     v_deptname varchar2(10);
  3      c number;
  4     BEGIN
  5     v_deptname:='&v_deptname';
  6    c:=f3(v_deptname);
  7     dbms_output.put_line('Total Expenditure is-’||c);
  8     END;
  9   /
***OUTPUT***
Enter value for v_deptname: Computer
old   5:    v_deptname:='&v_deptname';
new   5:    v_deptname:='Computer';

Total Expenditure is-550

PL/SQL procedure successfully completed.

Assignment 1 Q.4

By September 25, 2014


Politician (pno, pname, description)
Party (partycode, partyname)
******************************************************************************
SQL> create table party52(party_code number(5) primary key,party_name varchar2(10) NOT NULL);

Table created.


SQL> desc party52;

SQL> insert into party52 values(101,'MNS');
1 row created.
SQL> insert into party52 values(102,'RSP');
1 row created.
SQL> insert into party52 values(103,'BJP');
1 row created.
SQL> insert into party52 values(104,'Congress');
1 row created.
SQL> insert into party52 values(105,'Shivsena');
1 row created.

SQL> select * from party52;

SQL> create table politician52(pno number(5) primary key,pname varchar2(10),description varchar2(10),party_code number(5) references party52 );

Table created.

SQL> desc politician52;

SQL> insert into  politician52 values(1,'Raj','MLA',101);
1 row created.
SQL> insert into  politician52 values(2,'Mahadev','CM',102);
1 row created.
SQL> insert into  politician52 values(3,'Devendra','CM',103);
1 row created.
SQL> insert into  politician52 values(4,'Rahul','MLA',104);
1 row created.
SQL> insert into  politician52 values(5,'Modi','PM',103);
1 row created.
SQL> insert into  politician52 values(6,'Uddhav','MLA',105);
1 row created.
SQL> insert into  politician52 values(7,'Rajabhau','CM',101);
1 row created.
SQL> insert into  politician52 values(8,'Vijay','PM',105);
1 row created.

SQL> select * from politician52;

1)create or replace function in pl/sql to total no of politician in given party

SQL>create or replace function f5
  2    (ppname in varchar2)
  3     return number
  4    is
  5    v_count number;
  6    begin
  7    select count(politician52.pno) into v_count from party52, politician52
  8    where party52.party_code= politician52.party_code and
  9    party52.party_name=ppname;
 10    return v_count;
 11  END f5;
 12    /

Function created.

SQL> declare
  2  v_pname varchar2(20);
  3  c number;
  4  begin
  5  v_pname:='&v_pname';
  6  c:=f5(v_pname);
  7  dbms_output.put_line(‘Total no of politician-‘||c);
  8  end;
  9  /
***OUTPUT***
Enter value for v_pname: BJP
old   5: v_pname:='&v_pname';
new   5: v_pname:='BJP';

Total no of politician-2

PL/SQL procedure successfully completed.

Assignment 1 Q.3

By September 25, 2014


Employee (eno, ename, city, deptname)
Project (pno, pname, status)
******************************************************************************
SQL> create table employee52(eno number(5) primary key,ename varchar2(10),
          city varchar2(10),dept_name varchar2(10));

Table created.

SQL> desc employee52;

SQL> insert into employee52 values(1,’yogesh’,’pune’,’mech’);
1 row created.
SQL> insert into employee52 values(2,'samir','satara','civil');
1 row created.
SQL> insert into employee52 values(3,'ganesh','phaltan','comp');
1 row created.
SQL> insert into employee52 values(4,'suraj','mumbai','it');
1 row created.
SQL> insert into employee52 values(5,'raj','indapur','bca');
1 row created.

SQL> select * from employee52;

SQL> create table project52(pno number(5) primary key,pname varchar2(10),
       status varchar2(10) check(status in('c','p','i')));

Table created.

SQL> desc project52;

 SQL> insert into project52 values(101,'engg','p');
1 row created.
SQL> insert into project52 values(102,'swmaking','i');
1 row created.
SQL> insert into project52 values(103,'polution','c');
1 row created.
SQL> insert into project52 values(104,'game','i');
1 row created.
SQL> insert into project52 values(105,'software','p');
1 row created.

SQL> select * from project52;
 
SQL> create table emp_pro52(eno number(5) references employee52,pno number(5)
          references project52,no_of_days number(5));

Table created.

SQL> desc emp_pro52;

SQL>  insert into emp_pro52 values(1,102,5);
1 row created.
SQL>  insert into emp_pro52 values(1,105,3);
1 row created.
SQL>  insert into emp_pro52 values(2,101,2);
1 row created.
SQL>  insert into emp_pro52 values(1,104,1);
1 row created.
SQL>  insert into emp_pro52 values(3,105,4);
1 row created.
SQL>  insert into emp_pro52 values(4,102,5);
1 row created.
SQL>  insert into emp_pro52 values(5,104,7);
1 row created.
SQL>  insert into emp_pro52 values(2,102,6);
1 row created.

SQL> select * from emp_pro52;

1)create or replace pl/sql function to return total no of incomplete project of given employee.

SQL> create or replace function f9
  2  (v_ename in varchar2)
  3  return number
  4  is
  5  v_count number;
  6  begin
  7  select count(project52.pno) into v_count from employee52,project52,emp_pro52
  8  where employee52.eno=emp_pro52.eno and project52.pno=emp_pro52.pno and
  9  status='i' and employee52.ename=v_ename;
 10  return v_count;
 11  end f9;
 12  /
Function created.

SQL> declare
  2  eename varchar2(10);
  3  c number;
  4  begin
  5  eename:='&eename';
  6  c:=f9(eename);
  7  dbms_output.put_line('No of incomplite project-'||c);
  8  end;
  9  /
***Output***
Enter value for eename: yogesh
old   5: eename:='&eename';
new   5: eename:='yogesh';

No of incomplite project-2

PL/SQL procedure successfully completed.

2) create or replace pl/sql function to return total number of employees working on any project for more than 25 days
SQL>     create or replace function f10
  2      return number
  3      is
  4      v_count number;
  5      begin
  6      select count(employee52.eno) into v_count from employee52,project52, emp_pro52
 7      where employee52.eno=emp_pro52.eno and project52.pno=emp_pro52.pno and
  8      no_of_days>25;
  9      return v_count;
 10     end f10;
 11  /

Function created.

SQL> declare
  2  c number;
  3  begin
  4  c:=f10();
  5  dbms_output.put_line('Total number of employee-'||c);
  6  end;
  7  /
***OUTPUT***
Total number of employee-3

PL/SQL procedure successfully completed.

Search