DBMS

June 2012
M	T	W	T	F	S	S
« Nov
	1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

Download the original attachment
1. Create a table to represent sb_account of a bank consisting of account _no,
customer_name , Balance_amount.
Write a PL/SQL block to implement deposit a withdraw. Withdraw should not
be allowed if the balance goes below Rs.1000.
SQL> create table sb_account (acc_no number(5), customer_name varchar2(10),
balance_amt number(5));

Table created;

SQL> desc sb_account;
Name Null? Type
—————————————– ——– —————————-
ACC_NO NUMBER(5)
CUSTOMER_NAME VARCHAR2(10)
BALANCE_AMT NUMBER(5)
SQL> insert into sb_account values(&acc_no, ‘&customer_name’, &balance_amt);
Enter value for acc_no: 1001
Enter value for customer_name: khan
Enter value for balance_amt: 500
old 1: insert into sb_account values(&acc_no,’&customer_name’,&balance_amt)
new 1: insert into sb_account values(1001,’khan’,500)
1 row created.
SQL> select * from sb_account;
ACC_NO CUSTOMER_N BALANCE_AMT
———- ———- ———————————————–
1001 khan 500
1002 ahmed 1500
1003 saleem 2000
1004 ali 5000
1005 raza 800
1006 raheem 1000
1007 faiyaz 500
7 rows selected.

SQL> set serveroutput on
SQL> save program1.sql
1 declare
2 vacc_no number(5);
3 vbal number(5);
4 withdraw number(5);
5 begin
6 withdraw := &withdraw;
7 vacc_no :=&vacc_no;
8 select balance_amt into vbal from sb_account where acc_no =vacc_no;
9 if vbal >= 1000 then
10 update sb_account set balance_amt=vbal where acc_no=vacc_no;
11 else
12 dbms_output.put_line(‘withdraw is not possible’);
13 end if;
14* end;
15. /
SQL> /
Enter value for withdraw: 500
old 6: withdraw := &withdraw;
new 6: withdraw := 500;
Enter value for vacc_no: 1004
old 7: vacc_no :=&vacc_no;
new 7: vacc_no := 1004;
PL/SQL procedure successfully completed.
SQL> select * from sb_account;
ACC_NO CUSTOMER_N BALANCE_AMT
———- ———- ——————————————-
1001 khan 500
1002 ahmed 1500
1003 saleem 2000
1004 ali 4500
1005 raza 800
1006 raheem 1000
1007 faiyaz 500
7 rows selected.
SQL> /
Enter value for withdraw: 1000
old 6: withdraw := &withdraw;
new 6: withdraw := 1000;
Enter value for vacc_no: 1001
old 7: vacc_no :=&vacc_no;
new 7: vacc_no :=1001;
Withdraw is not possible
2. Create the following tables:
College_info consist of fields : college_code,college _name,address.
Faculty _info consist of fields: college_code, faculty_code ,faculty_name,
Qualification, experience in no.of years, address.
The field college code is foreign key.
a. Design a form to accept the data from the user.
b. Generate queries to do the following:
i)List all those faculty members whose experience is greater than or equal to 10 years and
have M.Tech degree.
ii)List all those faculty members, who have at least 10 years of experience but do not have
M.Tech degree
MASTER TABLE:
SQL> create table college_info(college_code number(5) primary key, college_name varchar2(10),
2* address varchar2(10));

Table created
SQL> desc college_info;
Name Null? Type
—————————————– ——– —————————-
COLLEGE_CODE NOT NULL NUMBER(5)
COLLEGE_NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
SQL> insert into college_info values(&college_code,’&college_name’, ‘&address’);
Enter value for college_code: 100
Enter value for college_name: dcet
Enter value for address: nampally
old 1: insert into college_info values(&college_code,’&college_name’, ‘&address’)
new 1: insert into college_info values(100,’dcet’, ‘nampally’)
1 row created.
SQL> select * from college_info;
COLLEGE_CODE COLLEGE_NA ADDRESS
———— ———- ————————————–
100 dcet nampally
101 cbit gandhipet
102 mjcet b. J.hills
103 viet gandhipet

DETAIL TABLE:
SQL> create table faculty_info(college_code number(5)references college_info,faculty_code 2. 2 2 2 2 number(5), faculty_name varchar2(10), qualification varchar2(10),
3 experience number(3),address varchar2(10));
Table created.
SQL> desc faculty_info;
Name Null? Type
—————————————– ——– —————————-
COLLEGE_CODE NUMBER(5)
FACULTY_CODE NUMBER(5)
FACULTY_NAME VARCHAR2(10)
QUALIFICATION VARCHAR2(10)
EXPERIENCE NUMBER(3)
ADDRESS VARCHAR2(10)

SQL> insert into faculty_info values(&college_info,&faculty_code,’&faculty_name’,'&qualification’,
2 &experience,’&address’);
Enter value for college_info: 100
Enter value for faculty_code: 10
Enter value for faculty_name: tauseef
Enter value for qualification: M.Tech
old 1: insert into faculty_info values(&college_info,&faculty_code,’&faculty_name’,'&qualification
new 1: insert into faculty_info values(100,10,’tauseef’,'M.Tech’,
Enter value for experience: 7
Enter value for address: nampally
old 2: &experience,’&address’)
new 2: 7,’nampally’)
1 row created.

SQL> select * from faculty_info;
COLLEGE_CODE FACULTY_CODE FACULTY_NA QUALIFICAT EXPERIENCE ADDRESS
———— ———— ———- ———- ———- ———- ——————————————————————
100 10 tauseef M.Tech 7 nampally
101 11 sultan P.HD 11 M.P
102 12 asif M.Tech 10 M.P
103 13 sajed M.C.A 10 mallapally

a)
To Design a Form follow the following steps:
Go to Start>programs>Oracle_developer_suite>Form developer>Form Builder.
This will open oracle forms builder window.

Under this window select Data Block option.
Click on(+) option this will open a new small window under that select Use the “data block wizard” and then click on OK button.

A new Data block Wizard window will be opened under that click on “Display this page Next Time” and then click on NEXT button.

This will open another Data Block Wizard window. Under that mark on Table or view and then click on NEXT.

A new Table window will be opened. Under that tick on” Current user” and “Tables “and select the desired table .On which you want to create the Form “College-info” and then click on OK button.

This will open all available fields of “College_info” select all the fields and
then click on NEXT button.

An another “ Data Block Wizard” will be opened. Under that write the appropriate Data Block Name for this “COLLEGE-INFO” and click on NEXT button. This will open another window under that tick on “create the data block”, then “create the layout wizard” and then click on FINISH button.

An another window will be opened under that tick on “Display the next time” and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. Another new Layout wizard window will be opened, that window shows all the width and height of all the fields and then click on NEXT button.

This will open a new window which shows various fields of Forms College_code, College_Name, Address. For Running the form go to

Start>programs>oracle_Developer_suite>Form Developer>start OC4J instant.
This will open a new DOS window minimize that window.

And then again go to Form Builder window under that go to Program>Run Form.
This will open a form output window. From menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

For DETAILED table:
Directly go to oracle Form builder window. Select DATA BLOCK option from the list and then click (+) create button. This will open a new small data block window under that click on use the data block wizard and then click on OK. This will open another window under that check on display the page next time. After that click on NEXT button. This will open another window under that check mark on Table or View and then click on NEXT button. This will open a New window under that click on BROWSE button and select the detail table Name “ Faculty_info” and then click on OK.

This will open a new window select the fields of the table.click on Next

This will open a data block wizard under that click on create relationship. This will open a new windowThen click on OK button. This will open a new window . click on NEXT button.

Which will open a new window there write the name of the detail table name as Faculty_info1 and click on OK button. Under next window click on FINISH button. This will open a new window under that tick on display the next time and click on NEXT button. Select all the fields of from the next window. And click on NEXT.

This will show the height and wideth of the table fields.then click on next button.

Under that check mark on Form and then NEXT. Under coming window write the frame name as “Faculty_Info” and click on NEXT and then on Finish. This will open a new window.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form output window.From menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

i)List all those employees whose experience is grater than equal to 10 years and have M.Tech degree.
SQL> select faculty_name from faculty_info where experience>=10 and qualification=’M.Tech’;
Output:
FACULTY_NA
———-
Asif sir
ii) List all those faculty members , who have at least 10 years of experience but do not have M.Tech degree.
SQL> select faculty_name from faculty_info where experience >=10 and qualification ‘M.Tech’;
Output:
FACULTY_INFO
———-
Sultan sir
Sajed sir

3. Create the following tables for Library Information System:
Book(accession_no, title, publisher, author, status)
Status could be issued, present in the Library, send for binding, and cannot be issued.
Write a trigger which sets the status of a book “cannot be issued”, if it is published 20 years back.
SQL> create table Book(accession_no number(5), title varchar2(10), publisher varchar2(10),
author varchar2(10), status varchar2(20));
Table created.
SQL> desc Book;
Name Null? Type
—————————————– ——– —————————-
ACCESSION_NO NUMBER(5)
TITLE VARCHAR2(10)
PUBLISHER VARCHAR2(10)
AUTHOR VARCHAR2(10)
STATUS VARCHAR2(20)

SQL> insert into book values(&accession_no,’&title’,'&publisher’,'&author’,'&status’);
SQL> insert into book values(&accession_no,’&title’,'&publisher’,'&author’,'&status’,&published);
SQL> /
Enter value for accession_no: 3
Enter value for title: OR
Enter value for publisher: 15-mar-87
Enter value for author: Ivan
Enter value for status: not issued
old 1: insert into book values(&accession_no,’&title’,'&publisher’,'&author’,'&status’)
new 1: insert into book values(3,’OR’,’15-mar-87′,’Ivan’,'not issued’)
1 row created.
SQL> select * from book;
ACCESSION_NO TITLE PUBLISHER AUTHOR STATUS
———— ———- ———- ———- ———————————
1 Dbms 01-jan-80 Baros not issued
2 DC 02-feb-81 sawithch not issued
3 OR 15-mar-87 Ivan not issued
4 OS 01-apr-88 Das not issued
5 ICG 03-dec-89 Rahul not issued
SQL> create or replace trigger tone after insert on book
2 begin
3 update book set status=’cannot be issued’ where round((sysdate-publisher)/365)>=20;
4 end;
5 /
Trigger created.
SQL> insert into book values(&accession_no,’&title’,'&publisher’,'&author’,'&status’);
Enter value for accession_no: 6
Enter value for title: test
Enter value for publisher: 19-oct-1980
Enter value for author: Ivan
Enter value for status: null
old 1: insert into book values(&accession_no,’&title’,'&publisher’,'&author’,'&status’)
new 1: insert into book values(6,’test’, ‘19-oct-80’,’Ivan’,'null’)
1 row created.

Ourput:
SQL> select * from book;
ACCESSION_NO TITLE PUBLISHER AUTHOR STATUS
———— ———- ———- ———- ———————————
1 Dbms 01-jan-80 Baros cannot be issued
2 DC 02-feb-81 sawithch cannot be issued
3 OR 15-mar-87 Ivan not issued
4 OS 01-apr-88 Das not issued
5 ICG 03-dec-89 Rahul not issued
6 test 19-oct-80 Ivan cannot be issued
6 rows selected.

4. Create the following tables for Library Information System:
Book (accession_no, title, publisher, author, status, date-of -purchase)
Status could be issued, present in the Library, send for binding, and cannot be issued
a. Create a form to accept the data from the user with appropriate validation check.
b. Generate queries to the following:
i) List all those books which are new arrivals. The books which are acquired during the last 6 months and categorized as new arrival.
ii) List all those books that cannot be issued and purchased 20 years ago.
SQL> create table bool1(accession_no number(5), title varchar2(10),author varchar2(10),
2 status varchar2(10),dop date);
Table created.
SQL> desc bool1;
Name Null? Type
—————————————– ——– —————————-
ACCESSION_NO NUMBER(5)
TITLE VARCHAR2(10)
AUTHOR VARCHAR2(10)
STATUS VARCHAR2(10)
DOP DATE

SQL> insert into bool1 values(&accession_no,’&title’,'&author’,'&status’,'&dop’);
Enter value for accession_no: 10
Enter value for title: dbms
Enter value for author: ivan
Enter value for status: issued
Enter value for dop: 16-mar-1999
old 1: insert into bool1 values(&accession_no,’&title’,'&author’,'&status’,'&dop’)
new 1: insert into bool1 values(10,’dbms’,'ivan’,'issued’,’16-mar-1999′)
1 row created.
SQL> select * from bool1;
ACCESSION_NO TITLE AUTHOR STATUS DOP
———— ———- ———- ———- ———
10 dbms ivan issued 16-MAR-99
11 os das not issued 10-APR-88
12 or rahul not issued 10-MAR-09
13 icg khan issued 16-OCT-87
14 dc william issued 04-AUG-09
a. Create a form to accept the data from the user with appropriate validation check.

To Design a Form follow the following steps:
Go to Start>programs>Oracle_developer_suite>Form developer>Form Builder.
This will open oracle forms builder window.

Under this window select Data Block option.
Click on(+) option this will open a new small window under that select Use the data block wizard and then click on OK button.

This will open a new Data block Wizard under that click on Display this page Next Time and then click on NEXT button.

This will open another Data Block Wizard window. Under that mark on Table or view and then click on NEXT.

This will open a new Table window. Under that tick on Current user and Tables and select the desired table .On which you want to create the Book1 form and click on OK button.

This will open all available fields of Book1 select all the fields and then click on NEXT button.

This will open a another Data Block Wizard. Under that write the appropriate Data Block Name for this “Book” and click on NEXT button. This will open another window under that tick on create the data block, then call the layout wizard and then click on FINISH button

This will open another window under that tick on Display the next time and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. This will open a new Layout wizard window, that window shows all the width and height of all the fields and then click on NEXT button.

This will open another window under that click on forms and then NEXT button. This will open another window under that write the appropriate frame name Book1 and then click on NEXT button. By clicking NEXT button your form designing is over. click on Finish button. This will open a new window which shows various fields of Forms accession_no, author, title,status,dop. For Running the form go to

Start>programs>oracle_Developer_suite>Form Developer>start OC4J instant.
This will open a new DOS window minimize that window.

i. List all those books which are new arrivals. The books which are acquired during the last 6 months and categorized as new arrival.

SQL> select * from bool1
2 where
3 months_between (sysdate,dop) select title,status,dop from bool1
2 where
3 status=’not issued’ and round((sysdate-dop)/365)>=20;
TITLE STATUS DOP
———- ———- ———
os not issued 10-APR-88

5. Create the following tables:
Student(roll_no, name, date-of-birth, course_id)
Course(course_id, name, fee, duration)
a. Create a form to accept the data from the user with appropriate validation check.
b. Generate queries to the following:
i) List all those students who are greater than 18 years of age and have opted for MCA course.
ii)List all those course whose fee is greater than of MCA.
SQL> create table student(roll_n0 number(5), name varchar2(10),dob date,
2 course_id varchar2(10) primary key);
Table created.

SQL> desc student;
Name Null? Type
—————————————– ——– —————————-
ROLL_N0 NUMBER(5)
NAME VARCHAR2(10)
DOB DATE
COURSE_ID NOT NULL VARCHAR2(10)
SQL> insert into student values(&roll_no, ‘&name’,’ &dob’,'&course_id’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for dob: 16-jan-1986
Enter value for course_id: MCA
old 1: insert into student values(&roll_no,’&name’,'&dob’,'&course_id’)
new 1: insert into student values(100,’khan’,’16-jan-1986′,’MCA’)
1 row created.
SQL> select * from student;
ROLL_N0 NAME DOB COURSE_ID
———- ———- ——— —————————
107 azeem 17-MAR-92 16
101 khan 16-JAN-88 10
102 ahmed 14-JAN-89 11
103 raheem 18-MAR-87 12
104 pasha 18-FEB-87 13
105 rizwan 16-JUN-88 14
106 faiyaz 15-SEP-86 15
7 rows selected.
SQL> create table course(course_id number(5) references student,cname varchar2(10),
2 fee number(5), duration number(2));
Table created
SQL> desc course;
Name Null? Type
—————————————– ——– —————————-
COURSE_ID NUMBER(5)
CNAME VARCHAR2(10)
FEE NUMBER(5)
DURATION NUMBER(2)
SQL> insert into course values(&course_id,’&cname’,&fee,&duration);
Enter value for course_id: 10
Enter value for cname: MCA
Enter value for fee: 26700
Enter value for duration: 3
old 1: insert into course values(&course_id,’&cname’,&fee,&duration)
new 1: insert into course values(10,’MCA’,26700,3)
1 row created.
SQL> select * from course;
COURSE_ID CNAME FEE DURATION
———- ———- ———- ———- —————
16 Ms.c 30000 2
10 MCA 26700 3
11 M.Tech 30000 2
12 MCA 26700 3
13 MCA 26700 3
14 MBA 26700 2
15 MCA 26700 3
7 rows selected.

a. Create a form to accept the data from the user with appropriate validation check.

Click on(+) option this will open a new small window under that select Use the data block wizard and then click on OK button.

This will open a new Data block Wizard under that click on Display this page Next Time and then click on NEXT button

This will open another Data Block Wizard window. Under that mark on Table or view and then click on NEXT.

Now another Data Block Wizard window is open. Then select the table on which we want to create form by clicking on Browns button this will open connect wizard. Under that write your USER NAME , PASSWORD and DATABASE NAME and then click on CONNECT button.
This will open a new Table window. Under that tick on Current user and Tables and select the desired table .On which you want to create the Form Faculty and then click on OK button.

This will open all available fields of student table select all the fields and then click on NEXT button.

This will open a another Data Block Wizard. Under that write the appropriate Data Block Name for this “Student” and click on NEXT button. This will open another window under that tick on create the data block, then call the layout wizard and then click on FINISH button. This will open another window under that tick on Display the next time and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. This will open a new Layout wizard window, that window shows all the width and height of all the fields and then click on NEXT button.

This will open another window under that click on forms and then NEXT button. This will open another window under that write the appropriate frame name College _info and then click on NEXT button. By clicking NEXT button your form designing is over. click on Finish button This will open a new window which shows various fields of Forms roll_no,name,fee, duration.
.
For Running the form go to Start>programs>oracle_Developer_suite>Form Developer>start OC4J instant. This will open a new DOS window minimize that window.

This will open a new window select the fields of the table.click on Next.

This will open a data block wizard under that click on create relationship. This will open a new window.

Then click on OK button. This will open a new window . click on NEXT button.

Which will open a new window there write the name of the detail table name as Course and click on OK button. Under next window click on FINISH button. This will open a new window under that tick on display the next time and click on NEXT button. Select all the fields of from the next window. And click on NEXT.

This will show the height and wideth of the table fields.then click on next button.

Under that check mark on Form and then NEXT. Under coming window write the frame name as “Course_Info” and click on NEXT and then on Finish. This will open a new window.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer..

i) List all those students who are greater than 18 years of age and have opted for MCA course.

SQL> select distinct name from student, course
2 where round((sysdate-dob)/365)>18
3 and cname=’MCA’;
Output:
NAME
——————-
Khan
Raheem
Pasha
Rizwan
fayaz

ii)List all those course whose fee is greater than of MCA.
SQL> select cname from course
2 where fee > (select fee from course where cname =’MCA’);
Output:
CNAMe
—————–
MS.c
M.Tech

6. Create the following table :
Student(roll_no, name, subject_opted)
Subject(subject-code, subject_name, faculty_code)
Faculty(faculty_code, faculty_name, specialization)
SQL> create table student1(roll_no number(5), name varchar2(10), subject_opted varchar2(10));
Table created.
SQL> desc student1;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NUMBER(5)
NAME VARCHAR2(10)
SUBJECT_OPTED VARCHAR2(10)
SQL> insert into student1 values(&roll_no,’&name’,'&subject_opted’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for subject_opted: Dbms
old 1: insert into student1 values(&roll_no,’&name’,'&subject_opted’)
new 1: insert into student1 values(100,’khan’,'Dbms’)
1 row created.
SQL> select * from student1;
ROLL_NO NAME SUBJECT_OP
———- ———- ———-
100 khan Dbms
101 rahman Dbms
102 rizwan OS
103 saleem OS
104 ahmed OR
105 kaleem dbms
6 rows selected.

SQL> create table faculty(faculty_code number(5) primary key, faculty_name varchar2(10),
2 spec varchar2(10));
SQL> desc faculty;

SQL> create table subject(subject_code number(5),subject_name varchar2(10),
2 faculty_code number(5) references faculty);
Table created.

SQL> desc subject;
Name Null? Type
—————————————– ——– —————————-
SUBJECT_CODE NUMBER(5)
SUBJECT_NAME VARCHAR2(10)
FACULTY_CODE NUMBER(5)
SQL> insert into subject values(&subject_code,’&subject_name’,&faculty_code);
Enter value for subject_code: 10
Enter value for subject_name: Dbms
Enter value for faculty_code: 200
old 1: insert into subject values(&subject_code,’&subject_name’,&faculty_code)
new 1: insert into subject values(10,’Dbms’,200)
1 row created.
SQL> select * from subject;
SUBJECT_CODE SUBJECT_NA FACULTY_CODE
———— ———- ————
10 Dbms 200
11 OS 201
12 OR 203
13 EIT 204
14 MEA 202
15 ICG 205
6 rows selected.

a. Create a form to accept the data from the user with appropriate validation check.

To Design a Form follow the following steps:
Go to Start>programs>Oracle_developer_suite>Form developer>Form Builder.
This will open oracle forms builder window. Under this window select Data Block option.
Click on(+) option this will open a new small window under that select Use the data block wizard and then click on OK button.

This will open a new Data block Wizard under that click on Display this page Next Time and then click on NEXT button.This will open another Data Block Wizard window. Under that mark on Table or view and then click on NEXT. Now another Data Block Wizard window is open. Then select the table on which we want to create form by clicking on Browns button this will open connect wizard. Under that write your USER NAME , PASSWORD and DATABASE NAME and then click on CONNECT button.

This will open a new Table window. Under that tick on Current user and Tables and select the desired table .On which you want to create the Form “Faculty” and then click on OK button

This will open all available fields of student select all the fields and
then click on NEXT button.

This will open a another Data Block Wizard. Under that write the appropriate Data Block Name for this “Faculty” and click on NEXT button. This will open another window under that tick on create the data block, then call the layout wizard and then click on FINISH button.

This will open another window under that click on forms and then NEXT button. Another window will be opened under that write the appropriate frame name “Faculty” and then click on NEXT button. By clicking NEXT button your form designing is over. click on Finish button.A new window will be opened which shows various fields of Forms Faculty_code,Faculty_name,Spec.

For running the form:Start>programs>oracle_Developer_suite>Form Developer>start OC4J instant.This will open a new DOS window minimize that window.And then again go to Form Builder window under that go to Program>Run Form.
A form output window will be opened. From menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

For DETAILED table:
Directly go to oracle Form builder window. Select DATA BLOCK option from the list and then click (+) create button. This will open a new small data block window under that click on use the data block wizard and then click on OK. Another window will be opened under that check mark on display the page next time. After that click on NEXT button. This will open another window under that check mark on Table or View and then click on NEXT button. This will open a New window under that click on BROWSE button and select the detail table Name “ subject” and then click on OK.

This will open a new window select the fields of the table.click on Next. This will open a data block wizard window under that click on create relationship. Another window will be opened.Then click on OK button. Another window will be opened. click on NEXT button.
]]
A new window will be opened, there write the name of the detail table name as “Subject”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT.

This will show the height and wideth of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Student_Info” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

b)
i. Find the number of students who have enrolled for the subject “DBMS”.

SQL> select count(subject_opted)from student1
2 where
3 subject_opted=’dbms’;
Output:
COUNT(SUBJECT_OPTED)
———————————
3
ii) Find all those faculty members who have not offerd any subject.
SQL> select faculty_name from faculty
2 where
3 spec=’Null’;
Output:
FACULTY_NAME
——————-
vinod
rafi
parveen madam

7. Create the following tables:
Item(item_code, item_name, qty_in_hand, reorder_level)
Supplier(supplier_code, supplier_name, address)
Can-supply(supplier_code, Item_code)
SQL> create table Item(item_code number(5) primary key,item_name varchar2(10),
qty_in_stock number(5), reorder_level number(5));
Table created.
SQL> desc item;
Name Null? Type
—————————————– ——– —————————-
ITEM_CODE NOT NULL NUMBER(5)
ITEM_NAME VARCHAR2(10)
QTY_IN_STOCK NUMBER(5)
REORDER_LEVEL NUMBER(5)
SQL> insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level);
Enter value for item_code: 10
Enter value for item_name: soap
Enter value for qty_in_stock: 12
Enter value for reorder_level: 10
old 1: insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level)
new 1: insert into item values(10,’soap’,12,10)
1 row created.

SQL> select * from item;
ITEM_CODE ITEM_NAME QTY_IN_STOCK REORDER_LEVEL
——— ———- ———— ———————————————-
10 soap 12 10
11 shampoos 16 15
12 oil 20 49
13 cream 30 25
14 powder 25 10

SQL> create table supplier(supplier_code number(5) primary key, supplier_name varchar2(10),
address varchar2(10 ));
Table created.
SQL> desc supplier;
Name Null? Type
—————————————– ——– —————————-
SUPPLIER_CODE NOT NULL NUMBER(5)
SUPPLIER_NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
SQL> insert into supplier values(&supplier_code,’&supplier_name’,'&address ‘);
Enter value for supplier_code: 100
Enter value for supplier_name: khan
Enter value for address: charminar
old 1: insert into supplier values(&supplier_code,’&supplier_name’,'&address
new 1: insert into supplier values(100,’khan’,'charminar
1 row created.
SQL> select * from supplier;
SUPPLIER_CODE SUPPLIER_N ADDRESS
————- ———- —————————-
100 khan charminar
101 Hindustan Kphp
102 L.G kondapoor
103 B.P.L dsnr
104 khan charminar

SQL> create table Can_supply(supplier_code number(5) references supplier,
2 item_code number(5) references item);
Table created.

SQL> desc can_supply;
Name Null? Type
—————————————– ——– —————————
SUPPLIER_CODE NUMBER(5)
ITEM_CODE NUMBER(5)

SQL> insert into can_supply values(&supplier_code,&item_code);
Enter value for supplier_code: 100
Enter value for item_code: 10
old 1: insert into can_supply values(&supplier_code,&item_code)
new 1: insert into can_supply values(100,10)
1 row created.
SQL> select * from can_supply;
SUPPLIER_CODE ITEM_CODE
————- ———-
100 10
101 11
102 12
103 13
104 14

a. Create a form to accept the data from the user with appropriate validation checks.

This will open a new Data block Wizard under that click on “Display this page Next Time” and then click on NEXT button.This will open another Data Block Wizard window. Under that mark on “Table or view “and then click on NEXT. Now another Data Block Wizard window is opened. Then select the table on which we want to create form by clicking on Browns button this will open connect wizard. Under that write your USER NAME , PASSWORD and DATABASE NAME and then click on CONNECT button.

This will open a new Table window. Under that tick on “Current user” and “Tables” and select the desired table .On which you want to create the Form “Item” and then click on OK button

This will open all available fields of student select all the fields and
then click on NEXT button.

Another Data Block Wizard will be opened. Under that write the appropriate Data Block Name for this “Item” and click on NEXT button. An another window will be opened, under that tick on “create the data block”, then create the layout wizard and then click on FINISH button. Another window will be opened under that tick on “Display the next time” and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. A new Layout wizard window will be opened, that window shows the width and height of all the fields and then click on NEXT button.

Now a new window will be opened under that click on forms and then NEXT button. Next window will be opened under that write the appropriate frame name “item” and then click on NEXT button. By clicking NEXT button your form designing is over. click on Finish button. A new window will be opened which shows various fields of Forms Item_code,item_name,qty_in_stock,reorder_level.

For running the form:Start>programs>oracle_Developer_suite>Form Developer>start OC4J instanc. A DOS window will be opened,minimize that window.And then again go to Form Builder window under that go to Program>Run Form.
A form output window will be opened. From menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

Another Master table:
Follow the same procedure as we follow for the “ITEM” table.
Directly go to oracle Form builder window. Select “DATA BLOCK “option from the list and then click (+) create button. This will open a new small “data block window” under that click on use the “data block wizard “ and then click on OK. Another window will be opened under that check mark on “display the page next time”. After that click on NEXT button. This will open another window under that check mark on “Table or View “and then click on NEXT button. This will open a New window under that click on BROWSE button and select the detail table Name “ Supplier” and then click on OK. This will open a new window select the fields of the table.click on Next.

This will open a data block wizard window under that click on “Delete relationship”. Another window will be opened.Then click on OK button. Another window will be opened. click on NEXT button.

A new window will be opened, there write the name of the table name as “supplier”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and wideth of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Supplier” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

For DETAIL table:
Directly go to oracle Form builder window. Select “DATA BLOCK “option from the list and then click (+) create button. This will open a new small “data block window” under that click on use the “data block wizard “ and then click on OK. Another window will be opened under that check mark on “display the page next time”. After that click on NEXT button. This will open another window under that check mark on “Table or View “and then click on NEXT button. This will open a New window under that click on BROWSE button and select the detail table Name “ Can_supply” and then click on OK.

This will open a new window select the fields of the table.click on Next. This will open a data block wizard window under that click on “create relationship” two times. Another window will be opened.Then click on OK button. Another window will be opened. click on NEXT button.

A new window will be opened, there write the name of the detail table name as “Can_supply”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and wideth of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Can_supply” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

b)
i) List all those suppliers who can supply the given item.
SQL> select supplier_name from supplier s, item i, can_supply c
2 where
3 item_name=’soap’ and i.item_code=c.item_code and s.supplier_code=c.supplier_code;
Output:
SUPPLIER_N
———-
Khan
ii) List all those items which cannot be supplied by given company.
SQL> select item_name from Supplier s,item i, can_supply c
2 where
3 supplier_name’Hindustan’ and i.item_code=c.item_code
4 and s.supplier_code=c.supplier_code;
Output:

ITEM_NAME
———-
soap
oil
cream
powder

8. Create the following tables:
Student2(roll_no, name. category, district, state)
Student_rank(roll_no, marks, rank)
SQL> create table student2(roll_no number(5)primary key, name varchar2(10),
2 category varchar2(5), district varchar2(10), state varchar2(20));
Table created.
SQL> desc student2;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NOT NULL NUMBER(5)
NAME VARCHAR2(10)
CATEGORY VARCHAR2(5)
DISTRICT VARCHAR2(10)
STATE VARCHAR2(20)
SQL> insert into student2 values(&roll_no,’&name’,'&category’,
2 ‘&district’,'&state’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for category: oc
old 1: insert into student2 values(&roll_no,’&name’,'&category’,
new 1: insert into student2 values(100,’khan’,'oc’,
Enter value for district: Hyderabad
Enter value for state: Andhra Pradesh
old 2: ‘&district’,'&state’)
new 2: ‘Hyderabad’,'Andhra Pradesh’)
1 row created.
SQL> select * from student2;
ROLL_NO NAME CATEG DISTRICT STATE
———- ———- —– ———- —————————————————-
100 khan oc Hyderabad Andhra Pradesh
101 Raheem oc Nalgonda Andhra Pradesh
102 Rizwan oc Hyderabad Andhra Pradesh
103 Sameer BC Triuv Tamil NAdu
104 Aslam BC Kholi Tamil Nadu
105 Imroz Oc T.M Tamil Nadu
6 rows selected.
SQL> create table Student_rank(roll_no number(5)references student2,marks number(4),rank number(5));
Table created.
SQL> desc Student_rank;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NUMBER(5)
MARKS NUMBER(4)
RANK NUMBER(5)
SQL> insert into Student_rank values(&roll_no,&marks, &rank);
Enter value for roll_no: 100
Enter value for marks: 60
Enter value for rank: 105
old 1: insert into Student_rank values(&roll_no,&marks, &rank)
new 1: insert into Student_rank values(100,60, 105)
1 row created.
SQL> select * from Student_rank;
ROLL_NO MARKS RANK
———- ———- ——————–
100 60 105
101 70 110
102 50 98
103 80 101
104 65 99
105 55 120
6 rows selected.

a. Create a form to accept the data from the user with appropriate validation check.

Click on(+) option this will open a new small window under that select “Use the data block wizard” and then click on OK button.

This will open a new Data block Wizard under that click on “Display this page Next Time “and then click on NEXT button. This will open another Data Block Wizard window. Under that mark on” Table or view” and then click on NEXT.

This will open a new Table window. Under that tick on “Current user and Tables” and select the desired table .On which you want to create the Form “Student2” and then click on OK button.

This will open all available fields of “Student2” table select all the fields and then click on NEXT button.

This will open a another Data Block Wizard. Under that write the appropriate Data Block Name for this “Student2” and click on NEXT button. This will open another window under that tick on “create the data block”, then create the layout wizard and then click on FINISH button. Another window will be opened under that tick on “Display the next time” and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. This will open a new Layout wizard window, that window shows all the width and height of all the fields and then click on NEXT button.

This will open another window under that click on forms and then NEXT button. Another window will be opened,under that write the appropriate frame name “student2” and then click on NEXT button. By clicking NEXT button your form designing is over. click on Finish button This will open a new window which shows various fields of Forms roll_no,name,category,district,state.

.
For Running the form go to Start>programs>oracle_Developer_suite>Form Developer>start OC4J instant. This will open a new DOS window minimize that window.And then again go to Form Builder window under that go to Program>Run Form.
This will open a form output window. From menu bar select Queries under Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

For DETAILED table:
Directly go to oracle Form builder window. Select DATA BLOCK option from the list and then click (+) create button. This will open a new small data block window under that click on “use the data block wizard” and then click on OK. Another window will be opened”under that check on “display the page next time”. After that click on NEXT button. This will open another window under that check mark on “Table or View” and then click on NEXT button. A New windowwill be opened under that click on BROWSE button and select the detail table Name “ Student_rank” and then click on OK.

This will open a new window select the fields of the table.click on Next.

This will open a data block wizard under that click on “create relationship”. This will open a new window. Then click on OK button. This will open a new window . click on NEXT button.

Which will open a new window there write the name of the detail table name as “Student_rank” and click on OK button. Under next window click on FINISH button. This will open a new window under that tick on display the next time and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and wideth of the table fields.then click on next button.

Under that check mark on Form and then NEXT. Under coming window write the frame name as “student_Rank” and click on NEXT and then on Finish. This will open a new window.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Exicute. This will start showing the previous and next values of the table which was present in PL/SQL table created early. For Next value go to fields menu under that select NEXT or PREVIOUS. To see the next values.

a) i) List all those students who have come from Tamil Nadu state and secured a rank above 100.
SQL> select name from student2 st,student_rank sr
2 where
3 state=’Tamil Nadu’ and st.roll_no=sr.roll_no and rank>100;
Output:
NAME
———-
Sameer
Imroz
ii) List all those students who come from Andhra Pradesh state and belong to given
category who have secured a rank above 100.
SQL> select name from student2 st, student_rank sr
2 where
3 state=’Andhra Pradesh’ and st.Roll_no=sr. Roll_no and rank>100
4 and category=’oc’;
Output:
NAME
———-
khan
raheem

9. Create the following tables:
Branch(branch_id,branch_name, branch_city)
Customer(customer_id, customer_name, customer_city, branch_id)
SQL> create table Branch(branch_id number(5) primary key,branch_name varchar2(10),
branch_city varchar2(10));
Table created.
SQL> desc branch;
Name Null? Type
—————————————– ——– —————————-
BRANCH_ID NOT NULL NUMBER(5)
BRANCH_NAME VARCHAR2(10)
BRANCH_CITY VARCHAR2(10)
SQL> insert into branch values(&branch_id,’&branch_name’,'&branch_city’);
Enter value for branch_id: 100
Enter value for branch_name: dsnr
Enter value for branch_city: Hyderabad
old 1: insert into branch values(&branch_id,’&branch_name’,'&branch_city’)
new 1: insert into branch values(100,’dsnr’,'Hyderabad’)
1 row created.
SQL> select * from Branch;
BRANCH_ID BRANCH_NAM BRANCH_CIT
———- ———- ———-
100 dsnr Hyderabad
101 abids Hyderabad
102 Nampally Hyderabad
103 M.P Hyderabad
104 sundaze Bombay
105 Nalgonda Nalgonda
6 rows selected.
SQL> create table Customer(customer_id number(5), customer_name varchar2(10),
2 customer_city varchar2(10), branch_id number(5) references branch);
Table created.

SQL> desc customer;
Name Null? Type
—————————————– ——– —————————-
CUSTOMER_ID NUMBER(5)
CUSTOMER_NAME VARCHAR2(10)
CUSTOMER_CITY VARCHAR2(10)
BRANCH_ID NUMBER(5)
SQL> insert into customer values(&customer_id,’&customer_name’,'&customer_city’,&branch_id);
Enter value for customer_id: 1001
Enter value for customer_name: khan
Enter value for customer_city: Hyderabad
Enter value for branch_id: 100
old 1: insert into customer values(&customer_id,’&customer_name’,'&customer_city’,&branch_id)
new 1: insert into customer values(1001,’khan’,'Hyderabad’,100)
1 row created.
SQL> select * from customer;
CUSTOMER_ID CUSTOMER_N CUSTOMER_C BRANCH_ID
———– ———- ———- ———-
1001 khan Hyderabad 100
1002 rizwan Hyderabad 101
1003 faiyaz Hyderabad 103
1004 Azeem Warangal 104
1005 kareem kodad 105

a. Create a form to accept the data from the user with appropriate validation check.

Click on(+) option this will open a new small window under that select “Use the data block wizard” and then click on OK button.

This will open a new Table window. Under that tick on “Current user and Tables” and select the desired table .On which you want to create the Form “Branch” and then click on OK button.

This will open all available fields of “Branch” table select all the fields and then click on NEXT button.

This will open a another Data Block Wizard. Under that write the appropriate Data Block Name for this “Branch” and click on NEXT button. This will open another window under that tick on “create the data block”, then create the layout wizard and then click on FINISH button. Another window will be opened under that tick on “Display the next time” and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. This will open a new Layout wizard window, that window shows all the width and height of all the fields and then click on NEXT button.

This will open another window under that click on forms and then NEXT button. Another window will be opened, under that write the appropriate frame name “Branch” and then click on NEXT button. By clicking NEXT button your form designing is over. click on Finish button This will open a new window which shows various fields of Forms branch_id ,branch_name,branch_city.

.
For Running the form go to Start>programs>oracle_Developer_suite>Form Developer>start OC4J instant. This will open a new DOS window minimize that window.And then again go to Form Builder window under that go to Program>Run Form.
This will open a form output window. From menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

For DETAILED table:
Directly go to oracle Form builder window. Select DATA BLOCK option from the list and then click (+) create button. This will open a new small data block window under that click on “use the data block wizard” and then click on OK. Another window will be opened”under that check on “display the page next time”. After that click on NEXT button. This will open another window under that check mark on “Table or View” and then click on NEXT button. A New windowwill be opened under that click on BROWSE button and select the detail table Name “ Customer” and then click on OK.

This will open a new window select the fields of the table.click on Next.

This will open a data block wizard under that click on “create relationship”. This will open a new window. Then click on OK button. This will open a new window . click on NEXT button.

Which will open a new window there write the name of the detail table name as “Customer” and click on OK button. Under next window click on FINISH button. This will open a new window under that tick on display the next time and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and width of the table fields.then click on next button

Under that check mark on Form and then NEXT. Under coming window write the frame name as “Customer_detail” and click on NEXT and then on Finish. This will open a new window.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Exicute. This will start showing the previous and next values of the table which was present in PL/SQL table created early. For Next value go to fields menu under that select NEXT or PREVIOUS. To see the next values.

a. List all those customers who live in the same city as the branch in which they have account.

SQL> select customer_name from customer c, branch b
2 where c.branch_id=b.branch_id and c.customer_city=b.branch_city;
Output:
CUSTOMER_N
———-
khan
rizwan
faiyaz
b. List all those customers who have an account in a abids branch.

SQL> select customer_name from customer c, branch b
2 where b.branch_city=’Hyderabad’ and c.branch_id=b.branch_id;
Output:
CUSTOMER_N
———-
khan
rizwan
faiyaz

10. Create the following tables:
Book(acc_no, title, published, dop, status)
Member(mem_id, name, No_of_books_issued, max_limit)
Book_issued(acc_no, mem_id, date_of_issued)
SQL> create table book10(acc_no number(5) primary key, title varchar2(10), published date,
2 dop date, status varchar2(10));
Table created.
SQL> desc book10;
Name Null? Type
—————————————– ——– —————————-
ACC_NO NOT NULL NUMBER(5)
TITLE VARCHAR2(10)
PUBLISHED DATE
DOP DATE
STATUS VARCHAR2(10)
SQL> insert into book10 values(&acc_no,’&title’,'&published’,'&dop’,'&status’);
Enter value for acc_no: 1001
Enter value for title: Dbms
Enter value for published: 16_oct_2009
Enter value for dop: 17-oct-2009
Enter value for status: not issued
old 1: insert into book10 values(&acc_no,’&title’,'&published’,'&dop’,'&status’)
new 1: insert into book10 values(1001,’Dbms’,’16_oct_2009′,’17-oct-2009′,’not issued’)
1 row created. SQL> create table book10(acc_no number(5) primary key, title varchar2(10), published date,
2 dop date, status varchar2(10));
Table created.
SQL> select * from book10;
ACC_NO TITLE PUBLISHED DOP STATUS
———- ———- ——— ——— ———-
1001 Dbms 16-OCT-09 17-OCT-09 not issued
1002 os 15-JAN-08 20-MAR-08 issued
1003 or 13-FEB-07 16-APR-07 issued
1004 Icg 13-JAN-09 20-FEB-09 not issued
1005 dc 18-DEC-08 01-JAN-09 not issued

SQL> create table Member(mem_id number(5) primary key, name varchar2(10),
2 no_of_book_issued number(5), max_limit number(2));
Table created.
SQL> desc member;
Name Null? Type
—————————————– ——– —————————-
MEM_ID NOT NULL NUMBER(5)
NAME VARCHAR2(10)
NO_OF_BOOK_ISSUED NUMBER(5)
MAX_LIMIT NUMBER(2)
SQL> insert into member values(&mem_id, ‘&name’,&no_of_book_issued, &max_limit);
Enter value for mem_id: 100
Enter value for name: rizwan
Enter value for no_of_book_issued: 3
Enter value for max_limit: 4
old 1: insert into member values(&mem_id, ‘&name’,&no_of_book_issued, &max_limit)
new 1: insert into member values(100, ‘rizwan’,3, 4)
1 row created.
SQL> select * from member;
MEM_ID NAME NO_OF_BOOK_ISSUED MAX_LIMIT
———- ———- —————– —————————————
100 rizwan 3 4
101 faiyaz 2 4
102 ahmed 4 4
103 Nisar 1 4
104 azeem 4 4
SQL> create table book_issued(acc_no number(5) references book10,
2 mem_id number(5) references member, date_of_issued date);
Table created.

SQL> insert into book_issued values(&acc_no,&mem_id, ‘&date_of_issued’);
Enter value for acc_no: 1001
Enter value for mem_id: 100
Enter value for date_of_issued: 16-oct-2009
old 1: insert into book_issued values(&acc_no,&mem_id, ‘&date_of_issued’)
new 1: insert into book_issued values(1001,100, ’16-oct-2009′)
1 row created.
SQL> select * from book_issued;
ACC_NO MEM_ID DATE_OF_ISSUED
———- ———- ———————————-
1001 100 16-OCT-09
1002 101 15-SEP-09
1003 102 01-OCT-09
1004 103 02-JAN-07
1004 104 19-OCT-09

a. Create a form to accept the data from the user with appropriate validation checks.

This will open a new Table window. Under that tick on “Current user” and “Tables” and select the desired table .On which you want to create the Form “Book” and then click on OK button

This will open all available fields of “Book” select all the fields and
then click on NEXT button.

Another Data Block Wizard will be opened. Under that write the appropriate Data Block Name for this “Book” and click on NEXT button. An another window will be opened, under that tick on “create the data block”, then create the layout wizard and then click on FINISH button. Another window will be opened under that tick on “Display the next time” and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. A new Layout wizard window will be opened, that window shows the width and height of all the fields and then click on NEXT button.

Now a new window will be opened under that click on forms and then NEXT button. Next window will be opened under that write the appropriate frame name “Book” and then click on NEXT button. By clicking NEXT button your form designing is over. click on Finish button. A new window will be opened which shows various fields of Forms acc_no, title, publisher, year, dop, status.

Another Master table:
Follow the same procedure as we follow for the “Book” table.
Directly go to oracle Form builder window. Select “DATA BLOCK “option from the list and then click (+) create button. This will open a new small “data block window” under that click on use the “data block wizard “ and then click on OK. Another window will be opened under that check mark on “display the page next time”. After that click on NEXT button. This will open another window under that check mark on “Table or View “and then click on NEXT button. This will open a New window under that click on BROWSE button and select the detail table Name “ Member” and then click on OK. This will open a new window select the fields of the table.click on Next.

This will open a data block wizard window under that click on “Delete relationship”. Another window will be opened.Then click on OK button. Another window will be opened. click on NEXT button.

A new window will be opened, there write the name of the table name as “Member”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and wideth of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Member” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter. Now enter the values into the empty fields of form and then go to Action menu from the menu bar and then click on save button which will save the values in the PL/SQL table which was created earlyer.

A new window will be opened, there write the name of the detail table name as “Book_issued”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and width of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Book_issued” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Exicute. This will start showing the previous and next values of the table which was present in PL/SQL table created early. For Next value go to fields menu under that select NEXT or PREVIOUS. To see the next values

10)

b.
i. List all those books which are due from the student to be returned . A book is considered to be due if it has been issued 15 days back and yet not returned;

SQL> select title from book10 b,book_issued bi
2 where
3 (sysdate-date_of_issued)>15 and b.acc_no=bi.acc_no;
Output:
TITLE
———-
os
or
Icg
ii) List all those members who cannot be issued any more books.
SQL> select
2 name from member where no_of_book_issued=max_limit;
Output:
NAME
———-
ahmed
azeem

11.Create the following tables:
Book(accession_no, title, publisher, year, date-of –purchase,status)
Book-place(accession_no, rack_id, rack_position)
Members(member_id, name, number_of _books_issued, max_limit, status)
Book-issued(accession_no, member_id, date-of-issued)
SQL>create table book12(accession_no number(1) primary key, title varchar2(10), published date,
2 dop date, status varchar2(10));
Table created.
SQL> desc book12;
Name Null? Type
—————————————– ——– —————————-
ACCESSION_NO Not null NUMBER(1)
TITLE VARCHAR2(10)
PUBLISHED DATE
DOP DATE
STATUS VARCHAR2(10)
SQL> insert into book12 values(&accession_no,’&title’,'&published’,'&dop’,'&status’);
Enter value for accession_no: 1
Enter value for title: Dbms
Enter value for published: 16-jan-2006
Enter value for dop: 17-mar-2007
Enter value for status: not issued
old 1: insert into book12 values(&accession_no,’&title’,'&published’,'&dop’,'&status’)
new 1: insert into book12 values(1,’Dbms’,’16-jan-2006′,’17-mar-2007′,’not issued’)
1 row created.
SQL> select * from book12;
ACCESSION_NO TITLE PUBLISHED DOP STATUS
———— ———- ——— ——— ———-
6 AFM 16-JAN-07 17-FEB-08 not issued
7 MEA 19-jan-88 18-feb-05 issued
1 Dbms 16-JAN-06 17-MAR-07 not issued
2 OS 14-JAN-05 13-JUL-07 not issued
3 OR 15-SEP-07 13-JUL-09 issued
4 DC 16-NOV-05 19-JUN-08 not issued
5 ICG 19-JAN-99 14-FEB-00 not issued
7 rows selected.

SQL> create table book_place12(accession_no number(1) references book12, rack_id number(3),
2 rack_position varchar2(20));
Table created.
SQL> desc book_place12;
Name Null? Type
—————————————– ——– —————————-
ACCESSION_NO NUMBER(1)
RACK_ID NUMBER(3)
RACK_POSITION VARCHAR2(20)
SQL> insert into book_place12 values(&accession_no,&rack_id,’&rack_position’);
Enter value for accession_no: 1
Enter value for rack_id: 20
Enter value for rack_position: 2nd
old 1: insert into book_place12 values(&accession_no,&rack_id,’&rack_position’)
new 1: insert into book_place12 values(1,20,’2nd’)
1 row created.
SQL> select * from book_place12;
ACCESSION_NO RACK_ID RACK_POSITION
———— ———- ——————–
1 20 2nd
2 20 3rd
3 36 1st
4 21 4th
5 21 4th
SQL> create table member12(member_id number(4) primary key, name varchar2(10),
2 no_of_book_issued number(2),max_limit number(2), status varchar2(10));
Table created.
SQL> desc member12
Name Null? Type
—————————————– ——– —————————-
MEMBER_ID NOT NULL NUMBER(4)
NAME VARCHAR2(10)
NO_OF_BOOK_ISSUED NUMBER(2)
MAX_LIMIT NUMBER(2)
STATUS VARCHAR2(10)
SQL> insert into member12 values(&member_issued, ‘&name’,&no_of_book_issued,&max_limit,’&status’);
Enter value for member_issued: 100
Enter value for name: Rizwan
Enter value for no_of_book_issued: 3
Enter value for max_limit: 4
Enter value for status: not return
old 1: insert into member12 values(&member_issued, ‘&name’,&no_of_book_issued,&max_limit,’&status’
new 1: insert into member12 values(100, ‘Rizwan’,3,4,’not return’)
1 row created.
SQL> select * from member12;
MEMBER_ID NAME NO_OF_BOOK_ISSUED MAX_LIMIT STATUS
———- ———- —————– ———- ——————————————————
105 kareem 4 4 returned
106 raheem 3 4 not return
100 Rizwan 3 4 not return
101 Inayath 2 4 returned
102 Azeem 4 4 not return
103 Ahmed 1 4 returned
104 Faiyaz 4 4 not return
7 rows selected.SQL> create table book_issued12(accession_no number(2) references book12,
2 member_id number(3) references member12, doi date);
Table created.
SQL> desc book_issued12;
Name Null? Type
—————————————– ——– —————————-
ACCESSION_NO NUMBER(2)
MEMBER_ID NUMBER(3)
DOI DATE
SQL> insert into book_issued12 values(&accession_no,&member_id,’&doi’);
Enter value for accession_no: 1
Enter value for member_id: 100
Enter value for doi: 23-jan-2009
old 1: insert into book_issued12 values(&accession_no,&member_id,’&doi’)
new 1: insert into book_issued12 values(1,100,’23-jan-2009′)
1 row created.
SQL> select * from book_issued12;
ACCESSION_NO MEMBER_ID DOI
———— ———- ———
1 100 23-JAN-09
2 101 15-FEB-08
3 102 16-FEB-07
4 103 18-FEB-09
5 104 30-JUL-08
7 rows selected.
a)Create a form to accept the data from the user with appropriate validation checks.
To Design a Form follow the following steps:
Go to Start>programs>Oracle_developer_suite>Form developer>Form Builder.
This will open oracle forms builder window. Under this window select Data Block option.
Click on(+) option this will open a new small window under that select Use the data block wizard and then click on OK button.

This will open a new Table window. Under that tick on “Current user” and “Tables” and select the desired table .On which you want to create the Form “Book12” and then click on OK button

This will open all available fields of student select all the fields and
then click on NEXT button.

Another Data Block Wizard will be opened. Under that write the appropriate Data Block Name for this “Book12” and click on NEXT button. An another window will be opened, under that tick on “create the data block”, then create the layout wizard and then click on FINISH button. Another window will be opened under that tick on “Display the next time” and then click NEXT button. This will open a Layout Wizard under that write the same name and then click on Next button. A new Layout wizard window will be opened, that window shows the width and height of all the fields and then click on NEXT button.

For running the form:Start>programs>oracle_Developer_suite>Form Developer>start OC4J instanc. A DOS window will be opened,minimize that window.And then again go to Form Builder window under that go to Program>Run Form.
A form output window will be opened. From menu bar select Queries under that Enter. Nor insert the values in the fields and then go to Action menu option under that click on save button. This willl the value in th table which was created in PL/SQL.

A new window will be opened, there write the name of the detail table name as “Book_palce12”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and wideth of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Can_supply” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter. Now enter the values into into the fields and then go to Action menu option under that click on “save” this will enter the values in the table which was created in PL/SQL.

Another Master table:
Follow the same procedure as we follow for the “Member12” table.
Directly go to oracle Form builder window. Select “DATA BLOCK “option from the list and then click (+) create button. This will open a new small “data block window” under that click on use the “data block wizard “ and then click on OK. Another window will be opened under that check mark on “display the page next time”. After that click on NEXT button. This will open another window under that check mark on “Table or View “and then click on NEXT button. This will open a New window under that click on BROWSE button and select the detail table Name “ Member12” and then click on OK. This will open a new window select the fields of the table.click on Next.

This will open a data block wizard window under that click on “Delete relationship”. Another window will be opened.Then click on OK button. Another window will be opened. click on NEXT button. A new window will be opened, there write the name of the table name as “Member12”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and wideth of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Member12” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter.Now insert the values into the fields and then go to Action menu under that clilck on save. This will save the entered fields into the PL/SQL tables which will created earlier.

For Combined DETAIL table:
Directly go to oracle Form builder window. Select “DATA BLOCK “option from the list and then click (+) create button. This will open a new small “data block window” under that click on use the “data block wizard “ and then click on OK. Another window will be opened under that check mark on “display the page next time”. After that click on NEXT button. This will open another window under that check mark on “Table or View “and then click on NEXT button. This will open a New window under that click on BROWSE button and select the detail table Name “ Book_issued12” and then click on OK.

A new window will be opened, there write the name of the detail table name as “Book_issued12”and click on OK button. Under next window click on FINISH button. Another window will be opened, under that tick on” display the next time “and click on NEXT button. Select all the fields of from the next window. And click on NEXT. This will show the height and wideth of the table fields.then click on next button.

Under that check mark on “Form” and then NEXT. Under coming window write the frame name as “Book_issued12” and click on NEXT and then on Finish. An another window will be opened.

Minimize that window go to START>programs> oracle_developer>Form builder>start OCJ4 instant this will open a new Dos window. Then minimize that window and then directly go to Form Builder window under that click on PROGRAMS>RUN FORM . This will open a new form outpur window. From that window goto menu bar select Queries under that Enter. Now insert the values into the empty fields and then go to Action menu bar and under that click on Save button. This will save the inserted values into the PL/SQL table which was created early.

With this output our form generation will be complited for four interrelated tables.

12. create the following tables:
Branch(branch_id, branch_name, branch_city)
Customer(customer_id, customer_name, customer_city, branch_id)
SQL> create table Branch(branch_id number(5) primary key,branch_name varchar2(10),
branch_city varchar2(10));
Table created.
SQL> desc branch;
Name Null? Type
—————————————– ——– —————————-
BRANCH_ID NOT NULL NUMBER(5)
BRANCH_NAME VARCHAR2(10)
BRANCH_CITY VARCHAR2(10)
SQL> insert into branch values(&branch_id,’&branch_name’,'&branch_city’);
Enter value for branch_id: 100
Enter value for branch_name: dsnr
Enter value for branch_city: Hyderabad
old 1: insert into branch values(&branch_id,’&branch_name’,'&branch_city’)
new 1: insert into branch values(100,’dsnr’,'Hyderabad’)
1 row created.
SQL> select * from Branch;
BRANCH_ID BRANCH_NAM BRANCH_CIT
———- ———- ————————————————
100 dsnr Hyderabad
101 abids Hyderabad
102 Nampally Hyderabad
103 M.P Hyderabad
104 sundaze Bombay
105 Nalgonda Nalgonda
6 rows selected.
SQL> create table Customer(customer_id number(5), customer_name varchar2(10),
2 customer_city varchar2(10), branch_id number(5) references branch);
Table created.

SQL> desc customer;
Name Null? Type
—————————————– ——– —————————-
CUSTOMER_ID NUMBER(5)
CUSTOMER_NAME VARCHAR2(10)
CUSTOMER_CITY VARCHAR2(10)
BRANCH_ID NUMBER(5)
SQL> insert into customer values(&customer_id,’&customer_name’,'&customer_city’,&branch_id);
Enter value for customer_id: 1001
Enter value for customer_name: khan
Enter value for customer_city: Hyderabad
Enter value for branch_id: 100
old 1: insert into customer values(&customer_id,’&customer_name’,'&customer_city’,&branch_id)
new 1: insert into customer values(1001,’khan’,'Hyderabad’,100)
1 row created.
SQL> select * from customer;
CUSTOMER_ID CUSTOMER_N CUSTOMER_C BRANCH_ID
———– ———- ———- ——————————————————
1001 khan Hyderabad 100
1002 rizwan Hyderabad 101
1003 faiyaz Hyderabad 103
1004 Azeem Warangal 104
1005 kareem kodad 105

a)
i) List all those customers who have an account in more than one branch.
SQL> select customer_id from customer group by customer_id
2 having count(customer_id)>1;
Output:
no rows selected

13. create the following tables:
Branch(branch_id, branch_name, branch_city)
Customer(customer_id, customer_name, customer_city, branch_id)
SQL> create table Branch(branch_id number(5) primary key,branch_name varchar2(10),
branch_city varchar2(10));
Table created.
SQL> desc branch;
Name Null? Type
—————————————– ——– —————————-
BRANCH_ID NOT NULL NUMBER(5)
BRANCH_NAME VARCHAR2(10)
BRANCH_CITY VARCHAR2(10)
SQL> insert into branch values(&branch_id,’&branch_name’,'&branch_city’);
Enter value for branch_id: 100
Enter value for branch_name: dsnr
Enter value for branch_city: Hyderabad
old 1: insert into branch values(&branch_id,’&branch_name’,'&branch_city’)
new 1: insert into branch values(100,’dsnr’,'Hyderabad’)
1 row created.
SQL> select * from Branch;
BRANCH_ID BRANCH_NAM BRANCH_CIT
———- ———- ———-
100 dsnr Hyderabad
101 abids Hyderabad
102 Nampally Hyderabad
103 M.P Hyderabad
104 sundaze Bombay
105 Nalgonda Nalgonda
106 M.P Hyderabad
6 rows selected.
SQL> create table Customer(customer_id number(5), customer_name varchar2(10),
2 customer_city varchar2(10), branch_id number(5) references branch);
Table created.

14 . Create the following tables:
Student(roll_no, name, date-of-birth, course_id)
Course(course_id, name, fee, duration)

SQL> create table student(roll_n0 number(5), name varchar2(10),dob date,
2 course_id varchar2(10) primary key);
Table created.
SQL> desc student;
Name Null? Type
—————————————– ——– —————————-
ROLL_N0 NUMBER(5)
NAME VARCHAR2(10)
DOB DATE
COURSE_ID NOT NULL VARCHAR2(10)
SQL> insert into student values(&roll_no, ‘&name’,’ &dob’,'&course_id’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for dob: 16-jan-1986
Enter value for course_id: MCA
old 1: insert into student values(&roll_no,’&name’,'&dob’,'&course_id’)
new 1: insert into student values(100,’khan’,’16-jan-1986′,’MCA’)
1 row created.
SQL> select * from student;
ROLL_N0 NAME DOB COURSE_ID
———- ———- ——— ———————————
107 azeem 17-MAR-92 16
101 khan 16-JAN-88 10
102 ahmed 14-JAN-89 11
103 raheem 18-MAR-87 12
104 pasha 18-FEB-87 13
105 rizwan 16-JUN-88 14
106 faiyaz 15-SEP-86 15
7 rows selected.

SQL> create table course(course_id number(5) references student,cname varchar2(10),
2 fee number(5), duration number(2));
Table created
SQL> desc course;
Name Null? Type
—————————————– ——– —————————-
COURSE_ID NUMBER(5)
CNAME VARCHAR2(10)
FEE NUMBER(5)
DURATION NUMBER(2)

SQL> insert into course values(&course_id,’&cname’,&fee,&duration);
Enter value for course_id: 10
Enter value for cname: MCA
Enter value for fee: 26700
Enter value for duration: 3
old 1: insert into course values(&course_id,’&cname’,&fee,&duration)
new 1: insert into course values(10,’MCA’,26700,3)
1 row created.
SQL> select * from course;
COURSE_ID CNAME FEE DURATION
———- ———- ———- ———- —————
16 Ms.c 30000 2
10 MCA 26700 3
11 M.Tech 30000 2
12 MCA 26700 3
13 MCA 26700 3
14 MBA 26700 2
15 MCA 26700 3
7 rows selected.

i)List all those students who are between 18-19 years of age and have opted for MCA course.
SQL>select distinct s.name from student s, course c
where
c.cname=’MCA’ and c.course_id=s.course_id and between
round((sysdate-s.dob)/365)>20 and round((sysdate-s.dob)/365) select s.course_id from course c, student s
2 where
3 c.course_id=s.course_id group by s.course_id having (s.course_id) create table student(roll_n0 number(5), name varchar2(10),dob date,
2 course_id varchar2(10) primary key);
Table created.
SQL> desc student;
Name Null? Type
—————————————– ——– —————————-
ROLL_N0 NUMBER(5)
NAME VARCHAR2(10)
DOB DATE
COURSE_ID NOT NULL VARCHAR2(10)
SQL> insert into student values(&roll_no, ‘&name’,’ &dob’,'&course_id’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for dob: 16-jan-1986
Enter value for course_id: MCA
old 1: insert into student values(&roll_no,’&name’,'&dob’,'&course_id’)
new 1: insert into student values(100,’khan’,’16-jan-1986′,’MCA’)
1 row created.
SQL> select * from student;
ROLL_N0 NAME DOB COURSE_ID
———- ———- ——— ———-
107 azeem 17-MAR-92 16
101 khan 16-JAN-88 10
102 ahmed 14-JAN-89 11
103 raheem 18-MAR-87 12
104 pasha 18-FEB-87 13
105 rizwan 16-JUN-88 14
106 faiyaz 15-SEP-86 15
7 rows selected.

SQL> insert into course values(&course_id,’&cname’,&fee,&duration);
Enter value for course_id: 10
Enter value for cname: MCA
Enter value for fee: 26700
Enter value for duration: 3
old 1: insert into course values(&course_id,’&cname’,&fee,&duration)
new 1: insert into course values(10,’MCA’,26700,3)
1 row created.
SQL> select * from course;
COURSE_ID CNAME FEE DURATION STATUS
———- ———- ———- ———- ————— ——————
16 Ms.c 30000 2
10 MCA 26700 3
11 M.Tech 30000 2
12 MCA 26700 3
13 MCA 26700 3
14 MBA 26700 2
15 MCA 26700 3
7 rows selected.

a).Write a PL/SQL procedure to do the following:
Set the status of course to “not Offered” in which the number of candidates is less than 5.

SQL> save c18.sql;
Created file c18.sql
SQL> set serveroutput on
SQL> create or replace procedure p1
2 (s1 in varchar2)
3 is
4 begin
5 update course set status=’not offered’
6 where
7 course_id=s1;
8 end;
9 /
Procedure created.

SQL>save proc13.sql;
SQL> declare
2 cursor c is select course_id from student
3 group by course_id having count(course_id) select * from course;
COURSE_ID CNAME FEE DURATION STATUS
———- ———- ———- ———- ——————————————
16 M.Sc 30000 2 not offered
10 MCA 26700 3 not offered
11 M.Tech 30000 2 not offered
12 MCA 26700 3 not offered
13 MCA 26700 3 not offered
14 MBA 26700 2 not offered
15 MCA 26700 3 not offered
7 rows selected.

16 . Create the following tables:
Student(roll_no, name, date-of-birth, course_id)
Course20(course_id, name, fee, duration, status)

SQL> create table student(roll_n0 number(5), name varchar2(10),dob date,
2 course_id varchar2(10) primary key);
Table created.
SQL> desc student;
Name Null? Type
—————————————– ——– —————————-
ROLL_N0 NUMBER(5)
NAME VARCHAR2(10)
DOB DATE
COURSE_ID NOT NULL VARCHAR2(10)
SQL> insert into student values(&roll_no, ‘&name’,’ &dob’,'&course_id’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for dob: 16-jan-1986
Enter value for course_id: MCA
old 1: insert into student values(&roll_no,’&name’,'&dob’,'&course_id’)
new 1: insert into student values(100,’khan’,’16-jan-1986′,’MCA’)
1 row created.
SQL> select * from student;
ROLL_N0 NAME DOB COURSE_ID
———- ———- ——— ———-
107 azeem 17-MAR-92 16
101 khan 16-JAN-88 10
102 ahmed 14-JAN-89 11
103 raheem 18-MAR-87 12
104 pasha 18-FEB-87 13
105 rizwan 16-JUN-88 14
106 faiyaz 15-SEP-86 15
7 rows selected.
SQL> create table course(course_id number(5) references student,cname varchar2(10),
2 fee number(5), duration number(2));
Table created
SQL> desc course;
Name Null? Type
—————————————– ——– —————————-
COURSE_ID NUMBER(5)
CNAME VARCHAR2(10)
FEE NUMBER(5)
DURATION NUMBER(2)

SQL> select * from course20;
COURSE_ID CNAME FEE DURATION STATUS
———- ———- ———- ——————————————
11 MCA 26700 3
12 MCA 26700 3
13 MCA 26700 3
14 M.Sc 30000 2
15 MBA 26700 2
16 MCA 26700 3
6 rows selected.
a).Write a PL/SQL procedure to do the following:
Set the status of the student to “offered “ in which the number of candidates is at lease 2
Otherwise set if to “not offered”.
SQL> declare
2 cursor c is select course_id from student
3 group by course_id having count(course_id)>=2;
4 vcourse_id student.course_id% type;
5 begin
6 open c;
7 loop
8 fetch c into vcourse_id;
9 exit when c% notfound;
10 update course20 set status=’Offered’
11 where
12 course_id=vcourse_id;
13 update course20 set status=’not offered’
14 where
15 course_id !=vcourse_id;
16 end loop;
17 close c;
18 commit;
19 end;
20 /
PL/SQL procedure successfully completed.

Output:
SQL> select * from course20;
COURSE_ID CNAME FEE DURATION STATUS
———- ———- ———- ———- ——————–
11 MCA 26700 3 offered
12 MCA 26700 3 offered
13 MCA 26700 3 offered
14 M.Sc 30000 2 not offered
15 MBA 26700 2 not offered
16 MCA 26700 3 offered
6 rows selected.
17. Create the following tables:
Item(item_code, item_name, qty_in_hand, reorder_level)
Supplier(supplier_code, supplier_name, address)
Can-supply(supplier_code, Item_code)
SQL> create table Item(item_code number(5) primary key,item_name varchar2(10),
qty_in_stock number(5), reorder_level number(5));
Table created.
SQL> desc item;
Name Null? Type
—————————————– ——– —————————-
ITEM_CODE NOT NULL NUMBER(5)
ITEM_NAME VARCHAR2(10)
QTY_IN_STOCK NUMBER(5)
REORDER_LEVEL NUMBER(5)
SQL> insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level);
Enter value for item_code: 10
Enter value for item_name: soap
Enter value for qty_in_stock: 12
Enter value for reorder_level: 10
old 1: insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level)
new 1: insert into item values(10,’soap’,12,10)
1 row created.

SQL> select * from item;
ITEM_CODE ITEM_NAME QTY_IN_STOCK REORDER_LEVEL
———- ———- ———— ————————- —————————
10 soap 12 12
11 shampoos 16 15
12 oil 20 49
13 cream 30 35
14 powder 25 10
15 gel 10 25
16 rice 16 16
7 rows selected.
SQL> create table supplier(supplier_code number(5) primary key, supplier_name varchar2(10),
address varchar2(10 ));
Table created.
SQL> desc supplier;
Name Null? Type
—————————————– ——– —————————-
SUPPLIER_CODE NOT NULL NUMBER(5)
SUPPLIER_NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)

SQL> insert into supplier values(&supplier_code,’&supplier_name’,'&address ‘);
Enter value for supplier_code: 100
Enter value for supplier_name: khan
Enter value for address: charminar
old 1: insert into supplier values(&supplier_code,’&supplier_name’,'&address’, )
new 1: insert into supplier values(100,’khan’,'charminar’)
1 row created.
SQL> select * from supplier;
SUPPLIER_CODE SUPPLIER_N ADDRESS
————- ———- ———- ——————–
100 khan charminar
101 Hindustan kphp
102 L.G kondapoor
103 B.P.L dsnr
104 khan charminar
SQL> create table Can_supply(supplier_code number(5) references supplier,
2 item_code number(5) references item);
Table created.
SQL> desc can_supply;
Name Null? Type
—————————————– ——– —————————
SUPPLIER_CODE NUMBER(5)
ITEM_CODE NUMBER(5)

a)Write PL/SQL procedure to do the following:
i) Generate a report to list the items whose qty_in_stock is less than or equal to their reorder_level.
To generate a report follow these steps:
Go to Start menu>Programs> Oracle_developer>Report Developer> Report Builder
This will open a starting window of report builder application under that check mark the “Ust the Report wizard” and click on “Ok”.

Another window will be opened click on NEXT button. In next window chick mark the radio button “Create the both web and paper Layout” and click the Next button.

This will open a new window under that write the “Title” name as Item and select radio button as “Tabuler” click on NEXT.

Another window will be opened under that select “SQL query” from the list click on Next button.

Another window will be opened. Under that click on Connect which will opoen a small window under that write the USERNAME, PASSWORD and DATABASE name and click on OK. Another window will be
Opened under that write the “Data source defenation” as
“ Select * from item where qty_in_stockprogram>oracle_developer>report developer>report builder>start oc4j instance
This will open a Dos window minimize that window and directly go to “report builder” under that
Go to program> run web layout

18. Create the following tables:
Item(item_code, item_name, qty_in_hand, reorder_level)
Supplier(supplier_code, supplier_name, address)
Can-supply(supplier_code, Item_code)
SQL> create table Item(item_code number(5) primary key,item_name varchar2(10),
qty_in_stock number(5), reorder_level number(5));
Table created.
SQL> desc item;
Name Null? Type
—————————————– ——– —————————-
ITEM_CODE NOT NULL NUMBER(5)
ITEM_NAME VARCHAR2(10)
QTY_IN_STOCK NUMBER(5)
REORDER_LEVEL NUMBER(5)
SQL> insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level);
Enter value for item_code: 10
Enter value for item_name: soap
Enter value for qty_in_stock: 12
Enter value for reorder_level: 10
old 1: insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level)
new 1: insert into item values(10,’soap’,12,10)
1 row created.

SQL> select * from item;
ITEM_CODE ITEM_NAME QTY_IN_STOCK REORDER_LEVEL
——- ———- ———— —————————————————
10 soap 12 10
11 shampoos 16 15
12 oil 20 49
13 cream 30 25
14 powder 25 10
SQL> create table supplier(supplier_code number(5) primary key, supplier_name varchar2(10),
address varchar2(10 ), status varchar2(20));
Table created.

SQL> desc supplier;
Name Null? Type
—————————————– ——– —————————-
SUPPLIER_CODE NOT NULL NUMBER(5)
SUPPLIER_NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
STATUS VARCHAR2(20)
SQL> insert into supplier values(&supplier_code,’&supplier_name’,'&address ‘,’&status’);
Enter value for supplier_code: 100
Enter value for supplier_name: khan
Enter value for address: charminar
Enter the values for status: local
old 1: insert into supplier values(&supplier_code,’&supplier_name’,'&address’,’&status’)
new 1: insert into supplier values(100,’khan’,'charminar’,’local’)
1 row created.
SQL> select * from supplier;
SUPPLIER_CODE SUPPLIER_N ADDRESS STATUS
————- ———- —————————————————–
100 khan charminar local
101 Hindustan Kphp local
102 L.G kondapoor important
103 B.P.L dsnr important
104 khan charminar local

SQL> create table Can_supply(supplier_code number(5) references supplier,
2 item_code number(5) references item);
Table created.

SQL> desc can_supply;
Name Null? Type
—————————————– ——– —————————
SUPPLIER_CODE NUMBER(5)
ITEM_CODE NUMBER(5)
SQL> insert into can_supply values(&supplier_code,&item_code);
Enter value for supplier_code: 100
Enter value for item_code: 10
old 1: insert into can_supply values(&supplier_code,&item_code)
new 1: insert into can_supply values(100,10)
1 row created.
SQL> select * from can_supply;
SUPPLIER_CODE ITEM_CODE
————- ———-
100 10
101 11
102 12
103 13
104 14

a. Write a PL/SQL procedure to do the following:
Set the status of the supplier to “important” if the supplier can supply more than one item.
SQL> set serveroutput on
SQL> save p22.sql;
Created file p22.sql
SQL> create or replace procedure p22(s1 in number)
2 is begin
3 update supplier set status=’Important’ where supplier_code=s1;
4 end;
5 /
Procedure created.
SQL>set serveroutput on;
SQL >save prog22.sql
1 declare
2 cursor c is select supplier_code from Can_supply group by supplier_code
3 having count(supplier_code)>1;
4 vsupplier_code Can_supply.supplier_code% type;
5 begin
6 open c;
7 loop
8 fetch c into vsupplier_code;
9 exit when c% notfound;
10 p22(vsupplier_code);
11 end loop;
12 close c;
13 commit;
14* end;
SQL> /
PL/SQL procedure successfully completed.

Output:
SQL> select * from supplier;
SUPPLIER_CODE SUPPLIER_N ADDRESS STATUS
————- ———- ———- ———————————–
100 khan charminar local
101 Hindustan kphp local
102 L.G kondapoor local
103 B.P.L dsnr local
104 khan charminar Important
19. Create the following tables:
Item(item_code, item_name, qty_in_hand, reorder_level)
Supplier(supplier_code, supplier_name, address,status)
Can-supply(supplier_code, Item_code)
SQL> create table Item(item_code number(5) primary key,item_name varchar2(10),
qty_in_stock number(5), reorder_level number(5));
Table created.
SQL> desc item;
Name Null? Type
—————————————– ——– —————————-
ITEM_CODE NOT NULL NUMBER(5)
ITEM_NAME VARCHAR2(10)
QTY_IN_STOCK NUMBER(5)
REORDER_LEVEL NUMBER(5)
SQL> insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level);
Enter value for item_code: 10
Enter value for item_name: soap
Enter value for qty_in_stock: 12
Enter value for reorder_level: 10
old 1: insert into item values(&item_code,’&item_name’,&qty_in_stock,&reorder_level)
new 1: insert into item values(10,’soap’,12,10)
1 row created.

SQL> create table supplier(supplier_code number(5) primary key, supplier_name varchar2(10),
address varchar2(10 ),status varchar2(10));
Table created.
SQL> desc supplier;
Name Null? Type
—————————————– ——– —————————-
SUPPLIER_CODE NOT NULL NUMBER(5)
SUPPLIER_NAME VARCHAR2(10)
ADDRESS VARCHAR2(10)
STATUS VARCHAR2(10)
SQL> insert into supplier values(&supplier_code,’&supplier_name’,'&address ‘,’&status’);
Enter value for supplier_code: 100
Enter value for supplier_name: khan
Enter value for address: charminar
Enter the value for status: important
old 1: insert into supplier values(&supplier_code,’&supplier_name’,'&address’,’&status’)
new 1: insert into supplier values(100,’khan’,'charminar’,’important’)
1 row created.

SQL> select * from supplier;
SUPPLIER_CODE SUPPLIER_N ADDRESS STATUS
————- ———- ———- ——————– ——————–
100 khan charminar important
101 Hindustan kphp important
102 L.G kondapoor important
103 B.P.L dsnr local
104 khan charminar Important
SQL> create table Can_supply(supplier_code number(5) references supplier,
2 item_code number(5) references item);
Table created.
SQL> desc can_supply;
Name Null? Type
—————————————– ——– —————————
SUPPLIER_CODE NUMBER(5)
ITEM_CODE NUMBER(5)

a)Write PL/SQL procedure to do the following:
i) Generate a report of those items that are supplied by those supplier whose status is ‘important’.
To generate a report follow these steps:
Go to Start menu>Programs> Oracle_developer>Report Developer> Report Builder
This will open a starting window of report builder application under that check mark the “Ust the Report wizard” and click on “Ok”.

Another window will be opened click on NEXT button. In next window chick mark the radio button “Create the both web and paper Layout” and click the Next button.

This will open a new window under that write the “Title” name as “Supplier” and select radio button as “Tabuler” click on NEXT.

Another window will be opened under that select “SQL query” from the list click on Next button.

Another window will be opened. Under that click on Connect which will open a small window under that write the USERNAME, PASSWORD and DATABASE name and click on OK. Another window will be opened there write the Data source defenation as
“Select item_name, supplier_name, status from
item i,supplier s, can_supply c
where
i.item_code=c.item_code and s.supplier_code=c.supplier_code
and s.status=’important’;”
and then click on Next button

Another window will be opened under that select all the fields of the “Supplier” table. Select one field from that and click on Next.

Another window will be opened under that select one field click any one field “Average” and click on Next.

This will open another window which shows the Width and height of the table. And click on Next.

Another window will be opened under that select the report format as “Peace”. And click on Next.
And then Finish. To this our report generation will be over.
To run the report.
Go to start>program>oracle_developer>report developer>report builder>start oc4j instance
This will open a Dos window minimize that window and directly go to “report builder” under that
Go to program> run web layout

20. Create the following tables:
Student(roll_no, name, category, district, state)
Student_rank(roll_no, marks, rank)
SQL> create table student24(roll_no number(5) primary key, name varchar2(10),
2 category varchar2(3), district varchar2(10), state varchar2(4));
Table created.
SQL> desc student24;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NOT NULL NUMBER(5)
NAME VARCHAR2(10)
CATEGORY VARCHAR2(3)
DISTRICT VARCHAR2(10)
STATE VARCHAR2(4)
SQL> insert into student24 values(&roll_no, ‘&name’,'&category’,'&district’,'&state’);
Enter value for roll_no: 1001
Enter value for name: khan
Enter value for category: OC
Enter value for district: Hyd
Enter value for state: A.P
old 1: insert into student24 values(&roll_no, ‘&name’,'&category’,'&district’,'&state’)
new 1: insert into student24 values(1001, ‘khan’,'OC’,'Hyd’,'A.P’)
1 row created.
SQL> select * from student24;
ROLL_NO NAME CATEGORTY DISTRICT STATE
———- ———- — ———- —————————
1001 khan OC Hyd A.P
1002 Rizwan OC Nalgonda A.P
1003 Faiyaz OC Hyd A.P
1004 kaleem BC M.P M.P
1005 Ahmed BC U.P U.P
1006 Saleem OC H.P H.P
1007 Raheem BCE Bombay Ar.P
7 rows selected.

SQL> create table student_rank(roll_no number(5) references student24, marks number(3),
2 rank number(3));
Table created.
SQL> desc student_rank;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NUMBER(5)
MARKS NUMBER(3)
RANK NUMBER(3)

SQL> insert into student_rank values(&roll_no,&marks,&rank);
Enter value for roll_no: 1001
Enter value for marks: 70
Enter value for rank: 6
old 1: insert into student_rank values(&roll_no,&marks,&rank)
new 1: insert into student_rank values(1001,70,6)
1 row created.
SQL> /
Enter value for roll_no: 1002
Enter value for marks: 45
Enter value for rank: 5
old 1: insert into student_rank values(&roll_no,&marks,&rank)
new 1: insert into student_rank values(1002,45,5)
1 row created.
SQL> select * from student_rank;
ROLL_NO MARKS RANK
———- ———- ———-
1001 70 6
1002 45 5
1003 50 8
1004 55 4
1005 60 3
1006 54 4
1007 65 5
7 rows selected.
a)Write the PL/SQL procedure to do the following
Generate the report to list of those states from which the first 5 rankers come from.
To generate a report follow these steps:
Go to Start menu>Programs> Oracle_developer>Report Developer> Report Builder
This will open a starting window of report builder application under that check mark the “Ust the Report wizard” and click on “Ok”.

Another window will be opened click on NEXT button. In next window chick mark the radio button “Create the both web and paper Layout” and click the Next button.

This will open a new window under that write the “Title” name as “Student” and select radio button as “Tabuler” click on NEXT.

Another window will be opened under that select “SQL query” from the list click on Next button.

Another window will be opened. Under that click on Connect which will open a small window under that write the USERNAME, PASSWORD and DATABASE name and click on OK. Another window will be opened there write the “Data source defenation” as
“ select s.roll_no, s.name, sr.rank, s.state from student24 s, student_rank sr
where
s.roll_no=sr.roll_no and sr.rankprogram>oracle_developer>report developer>report builder>start oc4j instance
This will open a Dos window minimize that window and directly go to “report builder” under that
Go to program> run web layout

21. Create the following table:
Student(roll_no, name, subject_opted)
Subject(subject_code, subject_name, faculty_code, status)
Faculty(faculty_code, faculty_name,spec)

SQL> insert into student1 values(&roll_no,’&name’,'&subject_opted’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for subject_opted: Dbms
old 1: insert into student1 values(&roll_no,’&name’,'&subject_opted’)
new 1: insert into student1 values(100,’khan’,'Dbms’)
1 row created.
SQL> select * from student1;

ROLL_NO NAME SUBJECT_OP
———- ———- ————————–
100 khan Dbms
101 rahman Dbms
102 rizwan OS
103 saleem OS
104 ahmed OR
105 kaleem Dbms
6 rows select
SQL> create table faculty(faculty_code number(5) primary key, faculty_name varchar2(10),
spec varchar2(10), status varchar2(20));
SQL> desc faculty;

SQL> select * from faculty;
FACULTY_CODE FACULTY_NA SPEC
———— ———- ———————–
200 tauseef EIT
201 sajed OS
202 Vinod Null
203 azher OR
204 rafi Null
205 parveen Null
6 rows selected.
SQL> create table subject(subject_code number(5),subject_name varchar2(10),
faculty_code number(5) references faculty);
Table created.
SQL> desc subject;
Name Null? Type
—————————————– ——– —————————-
SUBJECT_CODE NUMBER(5)
SUBJECT_NAME VARCHAR2(10)
FACULTY_CODE NUMBER(5)
STATUS VARCHAR2(20)
SQL> insert into subject values(&subject_code,’&subject_name’,&faculty_code);
Enter value for subject_code: 10
Enter value for subject_name: Dbms
Enter value for faculty_code: 200
Enter the value for status: offered
old 1: insert into subject values(&subject_code,’&subject_name’,&faculty_code,’&status’)
new 1: insert into subject values(10,’Dbms’,200,’offered’)
1 row created.

SQL> select * from subject;
SUBJECT_CODE SUBJECT_NA FACULTY_CODE STATUS
———— ———- ———————————————————
10 Dbms 200 offered
11 OS 201 offered
12 OR 203 offered
13 EIT 204 not offered
14 MEA 202 not offered
15 ICG 205 offered
6 rows selected.
a. Write a PL/SQL procedure to do the following:
Set the status of the subject to”not offered” if the subject is not opted by at least 1 students.
SQl>set serveroutput on
SQl> save p25.sql;
SQL> create or replace procedure p25(s1 in varchar2)
2 is begin
3 update subject set status=’not offered’ where subject_name=s1;
4 end;
5 /
Procedure created.
SQL>save proc25.sql
SQL> declare
2 cursor c is select subject_name from subject
3 group by subject_name having count(subject_name)>=1;
4 vsubject_name subject.subject_name % type;
5 begin
6 open c;
7 loop
8 fetch c into vsubject_name;
9 exit when c% notfound;
10 p25(vsubject_name);
11 end loop;
12 close c;
13 commit;
14 end;
15 /
PL/SQL procedure successfully completed.
Output:
SQL> select * from subject;
SUBJECT_CODE SUBJECT_NAME FACULTY_CODE STATUS
———— ———- ———— —————————————————–
10 Dbms 200 not offered
11 OS 201 not offered
12 OR 203 not offered
13 EIT 204 not offered
14 MEA 202 not offered
15 ICG 205 not offered
6 rows selected.

22. Create the following table:
Student(roll_no, name, subject_opted)
Subject(subject_code, subject_name, faculty_code, status)
Faculty(faculty_code, faculty_name,spec)

SQL> select * from student1;
ROLL_NO NAME SUBJECT_OP
———- ———- —————————
100 khan Dbms
101 rahman Dbms
102 rizwan OS
103 saleem OS
104 ahmed OR
105 kaleem Dbms
6 rows select
SQL> create table faculty(faculty_code number(5) primary key, faculty_name varchar2(10),
spec varchar2(10), status varchar2(20));
SQL> desc faculty;

SQL> select * from faculty;
FACULTY_CODE FACULTY_NA SPEC
———— ———- ———————–
200 tauseef EIT
201 sajed OS
202 Vinod Null
203 azher OR
204 rafi Null
205 parveen Null
6 rows selected.
SQL> create table subject(subject_code number(5),subject_name varchar2(10),
faculty_code number(5) references faculty);
Table created.
SQL> desc subject;
Name Null? Type
—————————————– ——– —————————-
SUBJECT_CODE NUMBER(5)
SUBJECT_NAME VARCHAR2(10)
FACULTY_CODE NUMBER(5)
STATUS VARCHAR2(20)
SQL> insert into subject values(&subject_code,’&subject_name’,&faculty_code,’&status’);
Enter value for subject_code: 10
Enter value for subject_name: Dbms
Enter value for faculty_code: 200
Enter the value for status: offered
old 1: insert into subject values(&subject_code,’&subject_name’,&faculty_code,’&status’)
new 1: insert into subject values(10,’Dbms’,200,’offered’)
1 row created.
SQL> select * from subject;

SUBJECT_CODE SUBJECT_NA FACULTY_CODE STATUS
———— ———- ———————————————————
10 Dbms 200 offered
11 OS 201 offered
12 OR 203 offered
13 EIT 204 not offered
14 MEA 202 not offered
15 ICG 205 offered
6 rows selected.
a. Write a PL/SQL procedure to do the following:
Set the status of the subject to “not offered” if the subject not offered by any of the
Faculty members.
SQL> set serveroutput on
SQL> save p26.sql;
Created file p26.sql
SQL> create or replace procedure p26(s1 in varchar2)
2 is begin
3 update subject set status=’not offered’ where
4 subject_name=s1;
5 end;
6 /
Procedure created.
SQL>save prog26.sql;
SQL> declare
2 cursor c is select subject_name from subject s
3 where not exists(select faculty_code from faculty f where
4 s.faculty_code=f.faculty_code);
5 vsubject_name subject.subject_name % type;
6 begin
7 open c;
8 loop
9 fetch c into vsubject_name;
10 exit when c% notfound;
11 p26(vsubject_name);
12 end loop;
13 close c;
14 commit;
15 end;
16 /
PL/SQL procedure successfully completed.
Output:
SQL> select * from subject;
SUBJECT_CODE SUBJECT_NA FACULTY_CODE STATUS
———— ———- ———— —————————————
10 Dbms 200 not offered
11 OS 201 not offered
12 OR 203 not offered
13 EIT 204 not offered
14 MEA 202 not offered
15 ICG 205 not offered
6 rows selected.
23. Create the following table:
Student(roll_no, name, subject_opted)
Subject(subject_code, subject_name, faculty_code, status)
Faculty(faculty_code, faculty_name,spec)

SQL> select * from student1;
ROLL_NO NAME SUBJECT_OP
———- ———- ——————————-
100 khan Dbms
101 rahman Dbms
102 rizwan OS
103 saleem OS
104 ahmed OR
105 kaleem Dbms
6 rows select
SQL> create table faculty(faculty_code number(5) primary key, faculty_name varchar2(10),
spec varchar2(10));
SQL> desc faculty;

Name Null? Type
—————————————– ——– —————————-
FACULTY_CODE NOT NULL NUMBER(5)
FACULTY_NAME VARCHAR2(10)
SPEC VARCHAR2(10)
SQL> insert into faculty values(&faculty_code, ‘&faculty_name’,'&spec’);
Enter value for faculty_code: 200
Enter value for faculty_name: tauseef
Enter value for spec: EIT
old 1: insert into faculty values(&faculty_code, ‘&faculty_name’,'&spec’)
new 1: insert into faculty values(200, ‘tauseef’,'EIT’)
1 row created.
SQL> select * from faculty;
FACULTY_CODE FACULTY_NA SPEC
———— ———- ———- ——————–
200 tauseef EIT
201 sajed OS
202 Vinod Null
203 azher OR
204 rafi EIT
205 parveen OS
6 rows selected
SQL> create table subject(subject_code number(5),subject_name varchar2(10),
faculty_code number(5) references faculty);
Table created.
SQL> desc subject;
Name Null? Type
—————————————– ——– —————————-
SUBJECT_CODE NUMBER(5)
SUBJECT_NAME VARCHAR2(10)
FACULTY_CODE NUMBER(5)

SQL> insert into subject values(&subject_code,’&subject_name’,&faculty_code);
Enter value for subject_code: 10
Enter value for subject_name: Dbms
Enter value for faculty_code: 200
old 1: insert into subject values(&subject_code,’&subject_name’,&faculty_code,)
new 1: insert into subject values(10,’Dbms’,200)
1 row created.
SQL> select * from subject;
SUBJECT_CODE SUBJECT_NA FACULTY_CODE
———— ———- ——————————————
10 Dbms 200
11 OS 201
12 OR 203
13 EIT 204
14 MEA 202
15 ICG 205
6. rows selected.

b. Generate the following query:
i)Find all those subjects which are offered by more than one faculty members.

SQL> select subject_name from subject
2 where
3 subject_name in(select spec from faculty
4 group by (spec) having count(spec)>1);
Output:
SUBJECT_NA
———-
OS
EIT

24. Create the following table:
Student(roll_no, name, subject_opted)
Subject(subject_code, subject_name, faculty_code, status)
Faculty(faculty_code, faculty_name,spec)

SQL> select * from student1;
ROLL_NO NAME SUBJECT_OP
———- ———- ———-
100 khan Dbms
101 rahman Dbms
102 rizwan OS
103 saleem OS
104 ahmed OR
105 kaleem Dbms
106 faiyaz OS
107 Inayath OS
7. rows selected.

SQL> create table faculty(faculty_code number(5) primary key, faculty_name varchar2(10),
spec varchar2(10));
SQL> desc faculty;

SQL> select * from faculty;
FACULTY_CODE FACULTY_NA SPEC
———— ———- ———- ——————–
200 tauseef EIT
201 sajed OS
202 Vinod Null
203 azher OR
204 rafi EIT
205 parveen OS
6 rows selected
a. Generate the following Queries:
i)Find the number of students who have enrolled for the subject “OS”.
SQL> select count(roll_no) from student1
2 where
3 subject_opted=’OS’;
Output:
COUNT(ROLL_NO)
————–
4
ii) Find all those student who opted for more than 2 subjects.
SQL> select count(Roll_no) from student1 group by
2 roll_no having count(roll_no)>2;
Output:
no rows selected
25. Create the following table:
Student(roll_no, name, subject_opted)
Subject(subject_code, subject_name, faculty_code, status)
Faculty(faculty_code, faculty_name,spec)

SQL> create table student1(roll_no number(5), name varchar2(10), subject_opted varchar2(10));
Table created.
SQL> desc student1;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NUMBER(5)
NAME VARCHAR2(10)
SUBJECT_OPTED VARCHAR2(10)
SQL> insert into student1 values(&roll_no,’&name’,'&subject_opted’);
Enter value for roll_no: 100
Enter value for name: khan
Enter value for subject_opted: Dbms
old 1: insert into student1 values(&roll_no,’&name’,'&subject_opted’)
new 1: insert into student1 values(100,’khan’,'Dbms’)
1 row created.
SQL> select * from student1;
ROLL_NO NAME SUBJECT_OP
———- ———- ———-
100 khan Dbms
101 rahman Dbms
102 rizwan OS
103 saleem OS
104 ahmed OR
105 kaleem Dbms
106 faiyaz OS
107 Inayath OS
8 rows selected.
SQL> create table faculty(faculty_code number(5) primary key, faculty_name varchar2(10),
spec varchar2(10));
SQL> desc faculty;

Name Null? Type
—————————————– ——– —————————-
FACULTY_CODE NOT NULL NUMBER(5)
FACULTY_NAME VARCHAR2(10)
SPEC VARCHAR2(10)

SQL> insert into faculty values(&faculty_code, ‘&faculty_name’,'&spec’);
Enter value for faculty_code: 200
Enter value for faculty_name: tauseef
Enter value for spec: EIT
old 1: insert into faculty values(&faculty_code, ‘&faculty_name’,'&spec’)
new 1: insert into faculty values(200, ‘tauseef’,'EIT’)
1 row created.
SQL> select * from faculty;
FACULTY_CODE FACULTY_NA SPEC
———— ———- ———- ——————–
200 tauseef EIT
201 sajed OS
202 Vinod Null
203 azher OR
204 rafi EIT
205 parveen OS
6 rows selected
a).Generate the following Query:
i) Find the number of students who have not enrolled for the subject “DBMS”.
SQL> select count(roll_no) from student1
2 where
3 subject_opted!=’Dbms’;
Output:
COUNT(ROLL_NO)
————–
5
26. Create the following tables:
Employee(emp_id, emp_name,emp_salary,dept_no)
a)Create a cursor to increase the salary for all employees whose dept no is 20 by 0.5.
SQL> create table employee(employee_id number(5) primary key, emp_name varchar2(10),
2 emp_sal number(5), dept_no number(5));
Table created.

SQL> desc employee;
Name Null? Type
—————————————– ——– —————————-
EMPLOYEE_ID NOT NULL NUMBER(5)
EMP_NAME VARCHAR2(10)
EMP_SAL NUMBER(5)
DEPT_NO NUMBER(5)

SQL> insert into employee values(&employee_id,’&emp_name’,&emp_sal,&dept_no);
Enter value for employee_id: 1001
Enter value for emp_name: Rahman
Enter value for emp_sal: 10000
Enter value for dept_no: 10
old 1: insert into employee values(&employee_id,’&emp_name’,&emp_sal,&dept_no)
new 1: insert into employee values(1001,’Rahman’,10000,10)
1 row created.
SQL> select * from employee;
EMPLOYEE_ID EMP_NAME EMP_SAL DEPT_NO
———– ———- ———- ———————————————–
1001 Rahman 10000 10
1002 Ahmed 8000 20
1003 Rizwan 15000 20
1004 Inayath 12000 20
1005 Faiyaz 13000 10

SQL>set serveroutput on
SQL >save temp.sql
SQL> declare
2 cursor c is select employee_id, emp_sal from employee
3 where
4 dept_no=20;
5 vemployee_id employee.employee_id% type;
6 vsal employee.emp_sal% type;
7 begin
8 open c;
9 if c % isopen then
10 loop
11 fetch c into vemployee_id,vsal;
12 exit when c% notfound;
13 update employee set emp_sal=vsal+(vsal*0.5)
14 where employee_id=vemployee_id;
15 end loop;
16 commit;
17 close c;
18 else
19 dbms_output.put_line(‘unable to open cursor’);
20 end if;
21 end;
22 /
PL/SQL procedure successfully completed.
Output:
SQL> select * from employee;
EMPLOYEE_ID EMP_NAME EMP_SAL DEPT_NO
———– ———- ———- ———————————————–
1001 Rahman 10000 10
1002 Ahmed 12000 20
1003 Rizwan 22500 20
1004 Inayath 18000 20
1005 Faiyaz 13000 10

27. Create the following tables:
Student(roll_no, student_name, DBMS_marks, OS_marks, OR_marks, DC_marks, ICG_marks, Total, Average)
Marks(roll_no, total, average,grade)
Create a trigger to check the grade of students and insert into the marks table.
SQL> create table student30(roll_no number(5) primary key,student_name varchar2(10),
2 Dbms_marks number(3), Dc_marks number(3), Os_marks number(3),Or_marks number(3),
3 Icg_marks number(3));
Table created.
SQL> desc student30;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NOT NULL NUMBER(5)
STUDENT_NAME VARCHAR2(10)
DBMS_MARKS NUMBER(3)
DC_MARKS NUMBER(3)
OS_MARKS NUMBER(3)
OR_MARKS NUMBER(3)
ICG_MARKS NUMBER(3)
SQL> insert into student30 values(&roll_no,’&student_name’,&Dbms_marks,&Dc_marks,
2 &os_marks,&or_marks,&icg_marks);
Enter value for roll_no: 1001
Enter value for student_name: Rizwan
Enter value for dbms_marks: 60
Enter value for dc_marks: 55
old 1: insert into student30 values(&roll_no,’&student_name’,&Dbms_marks,&Dc_marks,
new 1: insert into student30 values(1001,’Rizwan’,60,55,
Enter value for os_marks: 75
Enter value for or_marks: 58
Enter value for icg_marks: 70
old 2: &os_marks,&or_marks,&icg_marks)
new 2: 75,58,70)
1 row created.

SQL> select * from student30;
ROLL_NO STUDENT_NA DBMS_MARKS DC_MARKS OS_MARKS OR_MARKS ICG_MARKS
———- ———- ———- ———- ———- ———- ———————————————————————-
1001 Rizwan 60 55 75 58 70
1002 Inayath 65 46 52 55 45
1003 Ahmed 32 35 36 34 42
1004 kareem 40 35 41 32 36
1005 Faiyaz 55 45 63 54 64
SQL> create table marks30(roll_no number(5) references student30,total number(4),
2 average number(3,2),grade varchar2(3));
Table created.
SQL> desc marks30;
Name Null? Type
—————————————– ——– —————————-
ROLL_NO NUMBER(5)
TOTAL NUMBER(4)
AVERAGE NUMBER(3,2)
GRADE VARCHAR2(3)
SQL>create or replace trigger m30
2 after insert on student30
3 for each row
4 declare
5 T_tot number(3);
6 T_Avg number(4,2);
7 T_Gra varchar2(2);
8 begin
9 T_tot:=(:New.Dbms_marks)+(:New.Dc_marks)+(:New.OS_marks)+
10 (:New.or_marks)+(:New.Icg_marks);
11 T_avg:=(T_tot/5);
12 If(T_avg=40) and (T_avg select * from marks30;
ROLL_NO TOTAL AVERAGE GRADE
————————————————————————-
1001 318 63.60 A
1002 263 52.60 B
1003 182 36.40 F
1004 184 36.80 F
1005 281 56.20 B

Like this:

Be the first to like this page.

	ravindra chary on Yesterday i saw a movie-j…
	pvkoushik on Notice Board
	pvkoushik on 3D internet
	saipradeepthi on 3D internet
	pvkoushik on 3D internet
	vijay on CAA
	koushik on Ads By MCA
	koushik on hi Friends
	saipradeepthi on Ads By MCA
	prvrbond on Galaxy with key to Milky Way c…

VasaviMca

Email Subscription

VasaviCalender

Blogroll

Status

Recent Posts

Recent comments

Archieves

Categories

DBMS

Like this:

Leave a Reply Cancel reply

Tags

Calendar

Archives

Blogroll

Follow “VasaviMca”