Remember
me?
RegisterForgot Your Password?

Category Archives: SY BCA CODES

PL/SQL Procedure and Cursor for Library Database with entities like Book and Department

Consider the following Entities and their Relationships                                           

            Book (bno, bname, pubname, price)

Department (dno, dname)

Relationship between Book and Department is many to one   

Constraints:   Primary key,Price should be > 0

Create a RDB in 3NF & write queries in Oracle 8i for following.

a)      Create or Replace a PL/SQL procedure to display name of department spending maximum amount on books.

b)     Write a cursor to display department wise expenditure on books.

SOLUTION:

create table dept2(dno    number (5) primary key, dname  varchar2(20));

 

Create table book(bno       number(5) primary key,bname  varchar2(25),pubname   varchar2(25),price     number(6,2) check (price>0),dno       number(5) references dept2);

 

SQL> select * from dept2;

DNO DNAME

———- ——————–

10 Litreature

20 Computer

30 Networking

40 Arts

SQL> select * from book;

 

BNO    BNAME                     PUBNAME                        PRICE        DNO

———- ————————- ————————- ———- ———-

1        wings of fire                    Tata                              500            10

3        c Programming              Kanitakar                       150            20

5        Learn English                 Mehta Pub                    350           40

PL/SQL Trigger and Function for Movie Database with entities like Movie and Actor

Consider the following Entities and their Relationships                                          

Movie (mvno, mvname, releaseyear)

Actor (actno, actname)

Movie and Actor are related with many to many relationships with descriptive attribute rate of actor for movie.

Constraints:   Primary Key.

Create a RDB in 3NF & write queries in Oracle 8i for following.

 a)   Create or replace a Trigger that restricts insertion or updation of movies released before year 2000.

b)   Create or Replace PL/SQL function to return total number of actors acted in movie ‘Gajani’

SOLUTION:

Create table movie(mvno       number(5) primary key, mvname    varchar2(20), relese_yr  date );

 

Create Table Actor(actno      number(5) primary key,actname  varchar2(25));

 

Create table mv_act(mvno       number(5),actno       number(5) ,Rate        number(7));

 

SQL>select * from movie;

 

MVNO   MVNAME               RELESE_YR

———- -   ——————-                  ———

1      Gajni                                 01-SEP-09

2     Umrao_jaan                       01-SEP-85

3     Masoom                             01-SEP-83

4     Lord Of the rings                 01-SEP-04

PL/SQL function and Cursor for Party database with entities like Politician and Party

Consider the following Entities and their Relationships                           

            Politician (pno, pname, description)

Party (partycode, partyname)

Relationship between Politician and Party is many to one

Constraints:   Primary key,partyname should not be null.

Create a RDB in 3NF & write queries in Oracle 8i for following.

 a)      Create or Replace a PL/SQL function to return total number of politician of a given party.

b)  Write a cursor to display details of all politicians of ‘BJP’ party.

SOLUTION:

create table politician(pno               number(5) primary key,pname           varchar2(20),des                varchar2(25),party_code    number(5) references party);

 

create table party(party_code number(5) primary key,party_name varchar2(25) not null);

 

select * from politician ;

 

PNO PNAME                DES                       PARTY_CODE

———- ——————– ————————-         ———-

101 Rahul Gandhi         Leader                             1

102 Soniya Gandhi        President                         1

103 Varun Gandhi         Leader                             2

104 Menka Gandhi         Leader                            2

105 Barak Obama          Leader                             3

 

select * from party;

 

PARTY_CODE PARTY_NAME

———- ————————-

1 Congress

2 BJP

3 Democratic

4 Republic

 

PL/SQL function and Trigger for Employee database with entities like department and employee

Consider the following Entities and their Relationships                           

 Department (deptno, deptname,location)

Employee (empno, empname, salary, commission, designation)

Relationship between Department and Employee is many-to-one.

Constraints : Primary Key,

Create a RDB in 3NF & write queries in Oracle 8i for following.

 a)      Create or Replace PL/SQL function to return total number of employees working in ‘Computer’ Department

b)      Create or replace a Trigger for updation of an Employee table that restricts new salary should not less than old salary.

SOLUTION:

Create table Emp1(empno           number(5) constraint emp1_eno_pk primary key,ename           varchar2(25),salary            number(5),comm           number(2,2),desig             varchar2(10),dno               number(5) references dept1);

 

Select * from emp1;

 

EMPNO ENAME                  SALARY       COMM      DESIG             DNO

———- ————————-               ———-       ———-      ———-

1 sam                                        2000                    12           sales_rep          50

2 john                                       15000                            10           sales_rep          50

3 mathew                                 12000                   5             clerk                 10

 

Create table Dept1(dno                number   (2) primary key,deptname      varchar2 (20),location        varchar2  (20));

 

select * from dept1;

 

DNO   DEPTNAME             LOCATION

—– ——————– ——————–

10      sales                  - london

20      HR                      Delhi

30      purchase               new jersy

40      purchase               new jersy

50      Computer             Pune

PL/SQL Procedure and Cursor for Employee Database with entities like employee and project

Consider the following Entities and their Relationships                          

            Employee (eno, ename, city, deptname)

Project (pno, pname, status)

Relationship between Employee and project is many to many with descriptive attribute number_of_days employee worked on that project.

Constraints:   Primary key,Status has to be C-Complete, P-Progressive, I-Incomplete

Create a RDB in 3NF & write queries in Oracle 8i for following.

a)      Create or Replace a PL/SQL procedure to find number of employees of a given department working on project having status ‘Incomplete’.

b)      Write a cursor to display details of all projects along with status and number_of_days on which a given employee works.

SOLUTION:

create table Employee(eno       number(5) constraint employee_eno_pk primary key,ename             varchar2(25),city          varchar2(25),deptname  varchar2(25));

 

Select * from employee;

 

ENO ENAME                     CITY                      DEPTNAME

———- ————————- ————————- ——————–

1 rajiv seth                     mumbai                    sales

2 siddarth vishnu           pune                         Hr

3 madhu shetty            bangalore                  Hr

4 veena srishty             delhi                        sales

5 chaitnya agarwal         pune                       purchase

 

create table project (pno               number(5) constraint project_pno_pk primary key,pname                 varchar2(25),status         varchar2(5));

PL/SQL functions for Student Database with entities like Student and Teacher

Consider the following Entities and their Relationships                           

                    Student (rollno, name, class, totalmarks)

                   Teacher (tno, tname)

The relation between Student and Teacher is many-to-many with subject as descriptive attribute.

 Constraints : Primary Key,Class has to be FY, SY or TY.

Create a RDB in 3NF & write queries in Oracle 8i for following.

 a)      Create or Replace PL/SQL function to return student name that has scored maximum marks in ‘TY’.

b)      Create or Replace PL/SQL function to return total number of teachers who are teaching subject ‘Accounts’.

SOLUTION:

create table student(rno number(5) primary key,s_name varchar2(20),classvarchar2(20),tot_mrks number(5));

 

SQL> select *from student;

 

RNO    S_NAME      CLASS            TOT_MRKS

———-    ——————- ——————– ———-

101  shital            ty                          600

102  sapna           fy                          400

103  sneha           sy                          350

104  shaziya         ty                          560

105  renu              ty                          250

 

create table teacher(tno number(4) primary key, tname varchar2(20));

 

SQL> select *from teacher;

 

TNO TNAME

———- ——————–

21 shakila

22 seema

23 veena

24 jyotsna

 

create table stud_teach(rno number(5) references student, tno number(5)references teacher, subject varchar2(20));

 

SQL> select *from stud_teach;

 

RNO        TNO SUBJECT

———- ———- ——————–

102         21 Maths

101         21 History

103         22 Accounts

104         23 Accounts

105         24 Computer

 

/* slip 25 a */

 

Create or Replace Function stud_max return varchar2 is

v_name varchar2(20);

PL/SQL Procedure and Cursor for Bank Database with entities like Customer and Account

Consider the following Entities and their Relationships                                           

            Customer (cno, cname, city)

Account (ano, acc_type, balance)

Relationship between Customer and Account is one to many

Constraints:   Primary key,acc_type should not be null.

Create a RDB in 3NF & write queries in Oracle 8i for following.

a)      Create or Replace a PL/SQL procedure that will accept any two account number and withdrawal amount as input parameter to transfer withdrawal amount from first account to second account.

b)      Write a cursor to list all customers & their account details.

SOLUTION:

create table Customer(cno number primary key,cname varchar2(20),

city varchar2(20));

 

SQL> select * from Customer;

 

CNO          CNAME               CITY

———- ——————–  ——————–

1          Sapna                Pune

2           Shital            Mumbai

3          Sneha               Nagar

4          Anjum                Pune

5          Kavita             Mumbai

 

create table Account(ano number primary key,acc_type varchar2(20) not null,balance number(6,2),cno number references Customer);

 

SQL> select *from account;

 

ANO ACC_TYPE   CNO    BALANCE

———- ——————– ———- ———-

102 Current                    2        32900

103 Joint                         3       32000

105 Current                     5       50000

104 Saving                      4       45000`

101 Saving                      1       20000

106 Saving                      2       12000

 

/* slip 24 a */

 

CREATE OR REPLACE  PROCEDURE upd_acc(p_anum1 number,p_anum2 number,pbalance number) IS

 

v_cno  customer.cno%type;

v_cno1 number(6);

v_bal1 number(6);

v_bal2 number(6);

 

Begin

PL/SQL Procedure and Cursor for Movie Database with entities like movie and actor

Consider the following Entities and their Relationships                                          

                 Movie (mvno, mvname, releaseyear)

                 Actor (actno, actname)

Movie and Actor are related with many to many relationships with descriptive attribute rate of actor for movie.

Constraints:   Primary Key.

Create a RDB in 3NF & write queries in Oracle 8i for following.

 a)      Create or Replace a PL/SQL Procedure to display details of all actors acted in movie ‘Race’.

b)      Write a cursor to display the list of actors and their movies for which their rate is greater than 20 Lakhs.

SOLUTION:

Create table movie(mvno       number(5) primary key,mvname     varchar2(20),relese_yr  date);

SQL> select * from movie;

 

MVNO MVNAME     RELESE_YR

———- ——————–   ———

1 Gajni                    12-APR-09

2 Umrao_jaan          20-JAN-85

3 race                      10-JUL-08

4 Masoom               09-AUG-83

5 Lord Of the rings  27-NOV-04

 

Create Table Actor(actno    number(5) primary key, actname  varchar2(25));

 

SQL> select *from actor;

 

ACTNO ACTNAME

———-      ————————-

11     Aamir Khan

12     Asin

13    akshay

14    saif

15    katrina