49469044-Dbms-Record-1

S.No

INDEX Program

Page No

1.

(a)Create a table to represent sb_account of a bank consisting of account_no,customer-name , balance-amount. (b)Write a PL/SQL block to implement deposit and withdraw. Withdraw should not be allowed if the balance goes below Rs.1000.

2.

Create the following two tables: 12 College-info College-info College-info consists of fields : college-code, college-name, address Faculty-info consists of fields : college-code, faculty-code, faculty-name, qualification, college-code is foreign key. (a)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.

3.

Create the following tables for library-information systems : Book: (Accession -no, title, publisher, author, status) Status could be issued, present, in the library, dent for binding, and cannot be issued. (a)Write a trigger which sets the status of a book to “cannot be issued “, if it is published 20 years back.

15

4.

Create the following tables for library-information systems : Book : (Accession- no, title, publisher, author, status, date-of -purchase) Status could be issued, present, in the library, dent for binding, and cannot be issued. Generate queries to do the following : (i) List all those books which are new arrivals. The books which are requires during the last 6 months are categorized as new arrivals. (ii) List all those books that cannot be issued and purchased 20 years ago.

17

5.

Create the following tables: Student (roll-no, name, date-of-birth, course-id) Course (course-id, name, fee, duration) (a)Generate the queries to do the following: (i) List all those students who are greater than 18 years of age and

19

1

8

have opted or MCA course. (ii) List all those courses whose fee is grater than that of MCA course.

S.No 6.

7.

8.

9.

10.

11.

Program Create the following table : Student(roll-no,name subject-opted) Subject(faculty-code, faculty-name, specialization) (a)Generate queries to do the following : (i) Find the number of students who haveenrolles for the subject “DBMS” (ii) Find all those faculty members who have not offered any subject. Create the following table : Item (item-code, item-name, qty-in-stock, reorder-level) Supplier (supplier-code, supplier-name, address) Can-Supply (supplier-code, item-code) (a) Generate queries to do the following: (i) List all those suppliers who can supply the given item. (ii) List all those items which cannot be supplied by given company. Create the following table: Student(roll-no,marks,category, district, state) Student-rank(roll-no, marks,rank) (a)Generate queries to do the following : (i) List all those students who have come from tamilnadu state and secured a rank above 100. (ii) List all those students who have come from Anshra Pradesh state and belong togiven category who have secured a rank above 100. Create the following table: Branch (branch-id, branch-name, branch-city) Customer (customer-id, customer-name, customer-city, branch-id) (a) Generate queries to do the following: (i) List all those customers who live in the same city as the branch in which theyhave account. (ii) List all those customers who have an account in a given branch city Create the following tables: Book (accession no, title, publisher, year, date-of-purchase, status) Member (member-id, number-of-books-issued, max-limit)Book-issue (accession-no, member-id, date-of-issue) (a) Generate the following to do the following: (i) List all those books which are due from the students to be returned. A book is considered to be issued 15 days back and yet not returned. (ii) List all those members who cannot be issued any more books Create the following tables: 2

Page No 22

25

28

31

34

38

12.

13.

14.

15.

16.

Book (accession no, title, publisher, year, date-of-purchase, status) Member (member-id, number-of-books-issued, max-limit) Book-issue (accession-no, member-id, date-of-issue) (a) Write a PL/SQL procedure to issue the book. (b)Write a trigger to set the status of student to “back listed” if they have taken book but not returned even one year. Create the following Book (accession-no, title, publisher, year, date-of-purchase) Book-place (accession-no, rack-id, rack-position) Member (member-id, name, number-of-books-issued, max-limit, status) Book-issue (accession-no, member-id, date-of-purchase) (a) Write a PL/SQL procedure to issue the book (b) Write a trigger to set the status of the book neither to “lost” which is neither issued nor in the library. Create the following Book (accession-no, title, publisher, year, date-of-purchase) Member (member-id, name, number-of-books-issued, max-limit, status) Book-issue (accession-no, member-id, date-of-purchase ,due-date) (a) Write a PL/SQL to list all those students who are defaulters. Astudent is considered to be a defaulter if he has not returned a book even after the due-date (b) Write a trigger to set the status of the student to” back listed” if they have taken book but not returned even after one year. Create the following tables: Branch (branch-id, branch-name, branch_-city) Customer (customer-id, customer-name, customer-city, branch-id) (b) Generate queries to do the following: (i) List all those customers who live in the same city as the branch in which they have account. (ii) List all those customers who have an account in more than one branch. Create the following tables: Branch (branch-id, branch-name, branch_-city) Customer (customer-id, customer-name, customer-city, branch-id) (a) Generate queries to do the following: (i)List all those branches who have more than 2 customers. (ii) List all those customers who have an account in more than one branch. Create the following table: Student (roll-no, name, category, district, state)

3

43

49

53

57

61

17.

18.

19.

20.

21.

22.

Student-rank (roll-no, marks, rank) (b) Generate queries to do the following : (i) List names of the students who are having same rank bur they should reside in different districts. (ii) List details of students they belongs to same category with same tank. Create the following tables: Student (roll-no, name, date-of-birth, course-id) Course (course-id, name, fee, duration) (a) Generate queries to do the following: (i) List all those student who are between 10-19 years of age and have opted for MCAcourse. (ii) List all those courses in which number of students are less than 10. Create the following tables: Student (roll_no, name,date_of_birth,course-id) Course (course-id, name, fee, duration, status) (a) Write PL/SQL procedure to do the following: Set the status of course to “not offered” in which the number of candidate is less than 5. Create the following tables: Student (roll_no, name,date_of_birth,course-id) Course (course-id, name, fee, duration, status) (a) Write PL/SQL procedure to do the following: Set the status of course to “not offered” in which the number of candidate is less than 5. Create the following tables: Student (roll-no, name, date_of_birth, course-id) Course (course-id, name, fee, duration, status) (a) Write PL/SQL procedure to do the following: Set the status of course to “offered” in which the number of candidate are at least 10 otherwise not offered. Create the following tables Item(item_code,item_name,qty_in_stock,reorder_level) Supplier(supplier_code,supplier_name,address) Can_supply(supplier_code,item_code) (a)Create a form to accept the data from the user with appropriate validation checks (b)Create a report to list the items whose qty_in_stock is less than or equal to their reorder levels. Create the following table : Item (item-code, item-name, qty-in-stock, reorder-level) Supplier (supplier-code, supplier-name, address)

4

65

69

73

77

81

84

23.

24.

25.

26.

27.

Can-Supply (supplier-code, item-code) (a)Write PL/SQL procedure to do the following: Set the status of the supplier to “important” if the supplier can supply more than five items. create the following tables Item(item_code,item_name,qty_in_stock,reorder_level) Supplier(supplier_code,supplier_name,address,status) Can_supply(supplier_code,item_code) (a)Create a form to accept the data from the user with appropriate validation checks (b)Write PL/SQL procedure to do the following (i) Generate a report of those items that are supplied by those suppliers whose status is“important”. create the following tables Student(roll_no,marks,category,district,state) Student_rank(roll_no,marks,rank) (a)Create a form to accept the data from the user with appropriate validation checks. (b)Write a PL/SQL procedure to do the following (i)Generate a report to list of those districts from which the first hundred rankers come from create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code,status) Faculty(faculty_code,faculty_name,specialization) (a)Write a PL/SQL procedure to the following: Set the status of the subject to “not offered” if the subject is not opted by at least 5 students. create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code,status) Faculty(faculty_code,faculty_name,specialization) (a)Write a PL/SQL procedure to the following: Set the status of the subject to “not offered” if the subject is not offered by any of the faculty members. create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) (a)Generate the queries to do the following (i) Find the number of students who have enrolled for the subject ‘DBMS’.

5

88

92

94

97

100

(ii)Find all those subjects which are not offered by any faculty member. create the following tables 103 Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) (a)Generate the queries to do the following (i)Find the number of students who have enrolled for the subject ‘DBMS’. (ii)Find all those subjects which are offered by more than one faculty member. create the following tables 106 Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) (a)Generate the queries to do the following (i)Find the number of students who have enrolled for the subject ‘OS’. (ii)Find all those students who have opted for more than 5 subjects. create the following tables 109 Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) (a)Generate the queries to do the following (i)Find the number of students who have not enrolled for the subject ‘DBMS’. (ii)Find all those subjects which are offered by more than one faculty member.

28.

29.

30.

S.No

Program

Page No

1.

Steps For Creating A Data Source

112

2.

Steps To Create A Form

114

3.

Steps To Generate A Report

121

1. (a)Create a table to represent sb_account of a bank consisting of account_no, customer-name , balance-amount. 6

(b)Write a PL/SQL block to implement deposit and withdraw. Withdraw should not be allowed if the balance goes below Rs.1000. PROGRAM: Creating Tables: SQL> create table sb_account 2 ( ACC_NO varchar(10) primary key, 3 cust_name varchar(15), 4 balance number(10)); Table created. Inserting values in to table: SQL> insert into sb_account values 2 ('&ACC_NO','&cust_name',&balance); Enter value for acc_no: 101 Enter value for cust_name: abcd Enter value for balance: 15000 old 2: ('&ACC_NO','&CUST_NAME','&BALANCE') new 2: ('101','abcd','15000') 1 row created. SQL> / Enter value for acc_no: 102 Enter value for cust_name: defg Enter value for balance: 1000 old 2: ('&ACC_NO','&CUST_NAME','&BALANCE') new 2: ('102','defg','1000') 1 row created. SQL> select * from sb_account; ACC_NO -----------101 102 103 104

CUST_NAME ----------------abcd defg fhdkd dfhdl

BALANCE -------------15000 1000 2000 25000

(b) PL/SQL Code: SQL> declare

7

2 bal number(10); 3 acno varchar(10); 4 min_bal constant number(7,2):=1000.00; 5 amount number(10); 6 ch number(1); 7 begin 8 dbms_output.put_line(to_char(1)||'.deposit'); 9 dbms_output.put_line(to_char(2)||'.Withdraw'); 10 dbms_output.put_line('enter choice'); 11 ch:=&ch; 12 acno:=&acno; 13 select balance into bal from sb_account where acc_no=acno; 14 amount:=&amount; 15 if(ch=1) then 16 update sb_account set balance=balance+amount where acc_no=acno; 17 dbms_output.put_line('deposited'); 18 else 19 if (bal-amount) / Enter value for ch: 1 old 11: ch:=&ch; new 11: ch:=1; Enter value for acno: 101 old 12: acno:=&acno; new 12: acno:=101; Enter value for amount: 1000 old 14: amount:=&amount; new 14: amount:=1000; 1.deposit 2.Withdraw enter choice deposited PL/SQL procedure successfully completed. SQL> select * from sb_account;

8

ACC_NO CUST_NAME ---------- --------------- ---------101 abcd 16000 102 defg 1000 103 fhdkd 2000 104 dfhdl 25000

BALANCE

SQL>/ (reexecuting yhe same PL/SQL code) Enter value for ch: 2 old 11: ch:=&ch; new 11: ch:=2; Enter value for acno: 102 old 12: acno:=&acno; new 12: acno:=102; Enter value for amount: 1000 old 14: amount:=&amount; new 14: amount:=1000; enter choice 1.deposit 2.Withdraw cant withdraw PL/SQL procedure successfully completed. SQL> / (reexecuting yhe same PL/SQL code) Enter value for ch: 2 old 11: ch:=&ch; new 11: ch:=2; Enter value for acno: 103 old 12: acno:=&acno; new 12: acno:=103; Enter value for amount: 1000 old 14: amount:=&amount; new 14: amount:=1000; enter choice 1.deposit 2.Withdraw withdrawn PL/SQL procedure successfully completed. SQL> select * from sb_account;

9

ACC_NO CUST_NAME ---------- --------------- ---------101 abcd 16000 102 defg 1000 103 fhdkd 1000 104 dfhdl 25000

BALANCE

2. Create the following two tables: College-info

10

College-info College-info consists of fields : college-code, college-name, address Faculty-info consists of fields : college-code, faculty-code, faculty-name, qualification, college-code is foreign key. (a) 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. Program: Creating tables: SQL>create table college_info. (college_code number(5) primary key, college_name varchar(15), address varchar(15)); Table created. SQL>create table faculty_info 2 ( faculty_code number(5) primary key, 3 college_code number(5), 4 faculty_name varchar(15), 5 qualification varchar(15), 6 experience number(2), 7 address varchar(15), 8constraints fk foreign key(college_code) references college_info( college_code )); Table created. Inserting Values Into Tables: SQL> insert into college_info values 2 (&college_code,'&college_name','&address'); Enter value for college_code: 101 Enter value for college_name: xyz Enter value for address: old alwal 1; old 2: (&college_code,'&college_name','&address') new 2: (101,'xyz','old alwal 1;') 1 row created. SQL> / Enter value for college_code: 202

11

Enter value for college_name: abc Enter value for address: old alwal 2; old 2: (&college_code,'&college_name','&address') new 2: (202,'abc','old alwal 2;') 1 row created SQL> insert into faculty_info values (&faculty_code,&college_code,'&faculty_name','&qualification','&experience','&ad dress') Enter value for faculty_code: 1111 Enter value for college_code: 101 Enter value for faculty_name: ssss Enter value for qualification: M.Tech Enter value for experience: 10 Enter value for address: zzzzzzz old 2: (&faculty_code,&college_code,'&faculty_name','&qualification','&experience','&address' ) new 2: (1111,101,'ssss','M.Tech','10','zzzzzzz') 1 row created. SQL> / Enter value for faculty_code: 2222 Enter value for college_code: 202 Enter value for faculty_name: yyyy Enter value for qualification: M.Phil Enter value for experience: 20 Enter value for address: xxxxxx old 2: (&faculty_code,&college_code,'&faculty_name','&qualification','&experience','&address' ) new 2: (2222,202,'yyyy','M.Phil','20','xxxxxx') 1 row created.

SQL> select * from college_info;

12

COLLEGE_CODE ---------------------101 202 303 404 505

COLLEGE_NAME --------------------xyz abc def ghi klm

ADDRESS ----------------old alwal 1; old alwal 2; old alwal 3; old alwal 4; old alwal 5;

SQL> select * from faculty_info; FACTY_C0 COLLE_CODE FATY_NAME QUALIF -------------- ------------------ --------------------------1111 101 ssss M.Tech 2222 202 yyyy M.Phil 3333 303 dddd M.Tech 4444 404 ffff B.Sc 55555 505 rrrr M.Tech

EXPERI -------10 20 15 5 5

ADDRESS ------------zzzzzzz xxxxxx ccccc vvvvv qqqqq

(a) Query: (i)SQL> select faculty_name from faculty_info where experience>=10 and qualification='M.Tech); Output: FACULTY_NAME -------------------ssss dddd (ii) Query: SQL> select faculty_name from faculty_info where experience>10 and qualification!= 'M.Tech'); Output: FACULTY_NAME -----------------------yyyy

3. Create the following tables for library-information systems : Book: (Accession -no, title, publisher, author, status)

13

Status could be issued, present, in the library, dent for binding, and cannot be issued. Write a trigger which sets the status of a book to “cannot be issued “, if it is published 20 years back. PROGRAM: Creating Tables: SQL> create table libr_info 2 ( accession_no varchar(4) primary key, 3 title varchar(8), 4 published date, 5 author varchar(5), 6 status varchar(14)); Table created Inserting Values Into Table: .

SQL> insert into libr_info values 2 ('&accession_no','&title','&publisher','&author','&status'); Enter value for accession_no: mc24 Enter value for title: icg Enter value for published: 06-mar-1985 Enter value for author: ghij Enter value for status: issued old 2: ('&accession_no','&title','&publisher','&author','&status') new 2: ('mc24','icg','06-mar-1985','ghij','issued') 1 row created. SQL> / Enter value for accession_no: mc25 Enter value for title: dcn Enter value for published: 06-apr-2005 Enter value for author: ijkl Enter value for status: cannot issue old 2: ('&accession_no','&title','&publisher','&author','&status') new 2: ('mc25','dcn','06-apr-2005','ijkl','cannot issue') 1 row created.

SQL> select * from libr_info;

14

ACCE -------mc21 mc22 mc23 mc24 mc25

TITLE -------dbms or os icg dcn

PUBLISHED ---------------06-JAN-86 06-FEB-00 06-SEP-99 06-MAR-85 06-APR-05

AUTHO --------abcd cdef efgh ghij ijkl

STATUS ----------binding present issued issued cannot issue

SQL> create or replace trigger t03 before insert or update on libr_info for each row begin if (:new.published<'01-jan-1989') then update libr_info set status='cant issue'; end if; end; SQL> / Trigger created. insert into libr_info * ERROR at line 1: ORA-04098: trigger 'SCOTT. LIBR_TRIGGER' is invalid and failed re-validation ORA-06512: at "SCOTT.T03”.line 3 ORA-04088: error during execution of trigger 'SCOTT.T03’

4. Create the following tables for library-information systems : Book : (Accession- no, title, publisher, author, status, date-of -purchase)

15

Status could be issued, present, in the library, dent for binding, and cannot be issued. (a) Generate queries to do the following : (i) List all those books which are new arrivals. The books which are requires during the last 6 months are categorized as new arrivals. (ii) List all those books that cannot be issued and purchased 20 years ago. PROGRAM: Creating table: SQL> create table library_info 2 (accession_no varchar(7) primary key, 3 title varchar(15), 4 publisher varchar(10), 5 author varchar(10), 6 status varchar(10) 7 date_of_purcahse date; Table created. Inserting into table: SQL>insert into library_info values ('&accession_no','& title','&publisher','&author','&status','&date_of_purcahse' ); Enter value for accession_no: mc1122 Enter value for title: dbms Enter value for publisher: tata Enter value for author: ramakrsih Enter value for status: new Enter value for date_of_purcahse: 06-mar-08 old 2: ('&accession_no','& title','&publisher','&author','&status','&date_of_purcahse' ) new 2: ('mc1122','dbms','tata','ramakrsih','new','06-mar-08' ) 1 row created. SQL> / Enter value for accession_no: mc5566 Enter value for title: c++ Enter value for publisher: hhhhh Enter value for author: aaaaa Enter value for status: isuued Enter value for date_of_purcahse: 12-sep-1986 old 2: ('&accession_no','& title','&publisher','&author','&status','&date_of_purcahse' ) new 2: ('mc5566','c++','hhhhh','aaaaa','isuued','12-sep-1986' )

16

1 row created. SQL> select * from library_info; ACCESSI TITLE ------- --------------------mc1122 dbms mc2233 os mc3344 or mc4455 icg mc5566 c++ mc6677 eit

PUBLISHER ---------------tata Mcgrahill ssss ggggg hhhhh jjjjj

AUTHOR -----------ramakrsih Galvin wwww xxxxx aaaaa lllll

STATUS -----------present present binding issued issued issued

DATE_OF_P ----------------06-MAR-08 05-JAN-05 25-SEP-08 01-AUG-08 12-SEP-86 15-JAN-85

6 rows selected. (a) Queries: (i) SQL> select * from library_info where(months_between(sysdate,date_of_purcahse)<=6) ACCESSION_NO TITLE PUBLISHER AUTHOR STATUS DATE_OF_PURCAHSE ---------------------- -------- ---------------- ----------- - --------- -----------------------------mc3344 or ssss wwwww binding 25-SEP-08 mc4455 icg ggggg xxxxx issued 01-AUG-08 SQL> select * from library_info where(months_between(sysdate,date_of_purcahse)/12>=20); ACCESSI -----------mc5566 mc6677

TITLE -------c++ eit

PUBLISHER AUTHOR ----------------- -----------hhhhh aaaaa jjjjj lllll

5. Create the following tables: Student (roll-no, name, date-of-birth, course-id)

17

STATUS -----------isuued issued

DATE_OF_P ---------- --------12-SEP-86 15-JAN-85

Course (course-id, name, fee, duration) (a) Create a form to accept the data from the user with appropriate validation checks (b) Generate the queries to do 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 courses whose fee is grater than that of MCA course. PROGRAM: Creating tables: SQL> create table course 2 (course_id varchar(6) primary key, 3 name varchar(10), 4 fee number(6), 5 duration number(2)); Table created. Inserting into values: SQL> insert into course values 2 ('&course_id','& name ',&fee ,&duration ); Enter value for course_id: 123 Enter value for name: M.C.A Enter value for fee: 25400 Enter value for duration: 3 old 2: ('&course_id','& name ',&fee ,&duration ) new 2: ('123','M.C.A ',25400 ,3 ) 1 row created. SQL> / Enter value for course_id: 456 Enter value for name: B.Tech Enter value for fee: 36000 Enter value for duration: 4 old 2: ('&course_id','& name ',&fee ,&duration ) new 2: ('456','B.Tech ',36000 ,4 ) 1 row created.

SQL> select * from course;

18

COURSE -----------123 456 789 012 156

NAME -------M.C.A B.Tech M.B.A Bio-Tech Food-Tech

FEE DURATION ------- --------------25400 3 36000 4 20000 2 40000 3 10000 3

SQL> create table student 2 (roll_no varchar(5) primary key, 3 name varchar(5), 4 date_of_birth date, 5 course_id varchar(5), 6 constraints fk1 foreign key(course_id) references course(course_id)); Table created. SQL> insert into student values 2 ('&roll_no','& name','& date_of_birth','&course_id'); Enter value for roll_no: nmca1 Enter value for name: aaaa Enter value for date_of_birth: 01-jan-1986 Enter value for course_id: 123 old 2: ('&roll_no','& name','& date_of_birth','&course_id') new 2: ('nmca1','aaaa','01-jan-1986','123') 1 row created. SQL> / Enter value for roll_no: nmca2 Enter value for name: bbbb Enter value for date_of_birth: 25-feb-2000 Enter value for course_id: 456 old 2: ('&roll_no','& name','& date_of_birth','&course_id') new 2: ('nmca2','bbbb','25-feb-2000','456') 1 row created.

SQL> select * from student;

19

SQL> select * from student; ROLL_ --------nmca1 nmca2 nmca3 nmca4 nmca5 nmca6

NAME -------aaaa bbbb cccc dddd eeee ffff

DATE_OF_B ---------------01-JAN-86 25-FEB-00 30-MAR-87 15-APR-99 08-MAY-87 26-JUN-83

COURS ------------123 456 123 123 789 012

6 rows selected. (a) Queries:(i) SQL>select * from student where(months_between(sysdate,date_of_birth)/12>18)and course_id='123' ROLL_ -------nmca1 nmca3

NAME --------aaaaa ccccc

DATE_OF_B COURS ---------------- ---------10-JAN-86 123 30-MAR-87 123

(ii) SQL> select * from course where fee>25400; COURSE ----------456 012

NAME ---------B.Tech Bio-Tech

FEE DURATION ------- --------------36000 4 40000 3

6. Create the following table : Student(roll-no,name subject-opted)

20

Subject(faculty-code, faculty-name, specialization) (a) Generate queries to do the following : (i) Find the number of students who haveenrolles for the subject “DBMS” (ii) Find all those faculty members who have not offered any subject. PROGRAM: Creating the table: SQL> create table subject_06 2 (faculty_code varchar(5), 3 faculty_name varchar(5), 4 specilization varchar(5) primary key); Table created. SQL> create table student_06 2 (roll_no varchar(5) primary key, 3 name varchar(5), 4 subject_opted varchar(5), 5 constraints fk2 foreign key(subject_opted) references subject_06(specilization)); Table created. Inserting the values into table: SQL> insert into subject_06 values 2 ('&faculty_code','&faculty_name','&specilization'); Enter value for faculty_code: mca01 Enter value for faculty_name: abcde Enter value for specilization: dbms old 2: ('&faculty_code','&faculty_name','&specilization') new 2: ('mca01','abcde','dbms') 1 row created. SQL> / Enter value for faculty_code: mca04 Enter value for faculty_name: pqrst Enter value for specilization: icg old 2: ('&faculty_code','&faculty_name','&specilization') new 2: ('mca04','pqrst','icg') 1 row created. SQL> insert into student_06 values 2 ('&roll_no','&name ','& subject_opted');

21

Enter value for roll_no: mc001 Enter value for name: abc Enter value for subject_opted: dbms old 2: ('&roll_no','&name ','& subject_opted') new 2: ('mc001','abc ','dbms') 1 row created. SQL> / Enter value for roll_no: mc002 Enter value for name: def Enter value for subject_opted: os old 2: ('&roll_no','&name ','& subject_opted') new 2: ('mc002','def ','os') 1 row created. SQL> select * from subject_06; FACUL ----------mca01 mca02 mca03 mca04 mca05

FACUL ----------abcde fghij klmno pqrst uvwxy

SPECI --------dbms co os icg or

SQL> select * from student_06; ROLL_ ---------mc001 mc002 mc003 mc004 mc005

NAME ---------abc def ghi klm nop

SUBJE ---------dbms os or dbms dbms

(a) Queries:

22

(i)SQL> select * from student_06 where subject_opted='dbms'; ROLL_ --------mc001 mc004 mc005

NAME ---------abc klm nop

SUBJE ---------dbms dbms dbms

(ii) SQL> select * from subject_06 where specilization not in (select subject_opted from student_06); FACUL --------mca02 mca04

FACUL ---------fghij pqrst

SPECI --------co icg (or)

SQL> select * from subject_06 where specilization!= all('dbms','os','or'); FACUL ----------mca02 mca04

FACUL ----------fghij pqrst

SPECI ---------co icg

7. Create the following table : Item (item-code, item-name, qty-in-stock, reorder-level)

23

Supplier (supplier-code, supplier-name, address) Can-Supply (supplier-code, item-code) (a) Create a form to accept the data from the user with appropriate validation checks. (b) Generate queries to do the following: (i) List all those suppliers who can supply the given item. (ii) List all those items which cannot be supplied by given company. PROGRAM: Creating the tables: SQL> create table item 2 (item_code varchar(5) primary key, 3 item_name varchar(6), 4 qty_in_stock number(3), 5 reorder_level number(3)); Table created. SQL> create table supplier 2 (supplier_code varchar(5) primary key, 3 supplier_name varchar(6), 4 address varchar(10)); Table created. SQL> create table can_supply (supplier_code varchar(5), item_code varchar(5), constraints fk3 foreign key(supplier_code) references supplier(supplier_code), constraints fk4 foreign key(item_code) references item(item_code)); Table created. Inserting the values into tables: SQL> insert into item values 2 ('&item_code','& item_name ',&qty_in_stock,&reorder_level); Enter value for item_code: i303 Enter value for item_name: drives Enter value for qty_in_stock: 30 Enter value for reorder_level: 18 old 2: ('&item_code','&item_name','&qty_in_stock','&reorder_level') new 2: ('i303','drives','30','18') 1 row created.

24

SQL> / Enter value for item_code: i404 Enter value for item_name: keys Enter value for qty_in_stock: 35 Enter value for reorder_level: 20 old 2: ('&item_code','&item_name','&qty_in_stock','&reorder_level') new 2: ('i404','keys','35','20') 1 row created. SQL> insert into supplier values 2 ('&SUPPLIER_CODE','&SUPPLIER_NAME','&ADDRESS'); Enter value for supplier_code: s111 Enter value for supplier_name: abcd Enter value for address: xxxx old 2: ('&supplier_code','&supplier_name','&address') new 2: ('s111','abcd','xxxx') 1 row created. SQL> / Enter value for supplier_code: s222 Enter value for supplier_name: efgh Enter value for address: yyyy old 2: ('&supplier_code','&supplier_name','&address') new 2: ('s222','efgh','yyyy') 1 row created. SQL> insert into can_supply values 2 ('&supplier_code','&item_code'); Enter value for supplier_code: s222 Enter value for item_code: i202 old 2: ('&supplier_code','&item_code') new 2: ('s222','i202') 1 row created. SQL> / Enter value for supplier_code: s333 Enter value for item_code: i303 old 2: ('&supplier_code','&item_code') new 2: ('s333','i303') 1 row created. SQL> select * from item;

25

ITEM_ -------i101 i202 i303 i404 i505

ITEM_N ----------cpu mouse drives keys pens

QTY_IN_STOCK --------------------25 20 30 35 21

REORDER_LEVEL ----------------------5 15 18 20 13

SQL> select * from supplier; SUPPL -------s111 s222 s333 s444 s555

SUPPLI ----------abcd efgh ijkl mnop rstu

ADDRESS -------------xxxx yyyy zzzz wwww vvvv

(a) Queries: (i) SQL> select * from supplier where supplier_code = (select supplier_code from can_supply where item_code = (select item_code from item where item_name='drives')); SUPPL --------s333

SUPPLI ---------ijkl

ADDRESS ------------zzzz

(ii) SQL> select * from item where item_code not in (select item_code from can_supply where supplier_code = ( select supplier_code from supplier where supplier_name='ijkl')); ITEM_ ------i101 i202 i404 i505

ITEM_N --------cpu mouse keys pens

QTY_IN_STOCK ---------------------25 20 35 21

8. Create the following table:

26

REORDER_LEVEL ------------------------5 15 20 13

Student(roll-no,marks,category, district, state) Student-rank(roll-no, marks,rank) (a) Generate queries to do the following : (i) List all those students who have come from tamilnadu state ans secured a rank above 100. (ii) List all those students who have come from Anshra Pradesh state and belong to given category who have secured a rank above 100. PROGRAM: Creating the tables: SQL>create table student_07 2 (roll_no varchar(6) primary key, 3 marks number(3), 4 category varchar(15), 5 district varchar(10), 6 state varchar(18)); Table created. SQL> create table student_rank 2 (roll_no varchar(6), 3 marks number(3), 4 rank number(5), 5 constraints fk_7 foreign key(roll_no) references student_07(roll_no)); Table created. Inserting values into tables: SQL> insert into student_07 values 2 ('&roll_no','&marks','&category','&district','&state'); Enter value for roll_no: nmca01 Enter value for marks: 150 Enter value for category: apptitude Enter value for district: chennai Enter value for state: tamilnadu old 2: ('&roll_no','&marks','&category','&district','&state') new 2: ('nmca01','150','apptitude','chennai','tamilnadu') 1 row created. SQL>/ Enter value for roll_no: nmca05 Enter value for marks: 350 Enter value for category: reasoning

27

Enter value for district: kurnool Enter value for state: andhrapradesh old 2: ('&roll_no','&marks','&category','&district','&state') new 2: ('nmca05','350',' reasoning','kurnool','andhrapradesh') 1 row created. SQL> insert into student_rank values 2 ('&roll_no','&marks','&rank'); Enter value for roll_no: nmca01 Enter value for marks: 150 Enter value for rank: 50 old 2: ('&roll_no','&marks','&rank') new 2: ('nmca01','150','7') 1 row created. SQL> / Enter value for roll_no: nmca02 Enter value for marks: 200 Enter value for rank: 150 old 2: ('&roll_no','&marks','&rank') new 2: ('nmca02','200','150') 1 row created SQL> select * from student_07; ROLL_N ----------nmca01 nmca02 nmca03 nmca04 nmca05 nmca06 nmca07

MARKS ----------150 200 250 300 350 400 450

CATEGORY ----------------apptitude apptitude reasoning reasoning reasoning apptitude apptitude

7 rows selected.

SQL> select * from student_rank;

28

DISTRICT ------------chennai mailadu trivan hyd kurnool guntur vizag

STATE ---------tamilnadu tamilnadu tamilnadu andhrapradesh andhrapradesh andhrapradesh andhrapradesh

ROLL_N ----------nmca01 nmca02 nmca03 nmca04 nmca05 nmca06 nmca07

MARKS ----------150 200 250 300 350 400 450

RANK ---------400 350 300 250 200 150 50

7 rows selected. (a) Queries: (i) select * from student_07 where state='tamilnadu' and roll_no in (select roll_no from student_rank where rank>100); ROLL_N ----------nmca02 nmca03

MARKS ---------200 250

CATEGORY ---------------apptitude reasoning

DISTRICT -------------mailadu trivan

STATE --------tamilnadu tamilnadu

(ii) SQL> select * from student_07 where state='andhrapradesh' and category='apptitude' and roll_no in (select roll_no from student_rank where rank>100); ROLL_N ----------nmca06

MARKS ----------

CATEGORY ----------------

DISTRICT --------------

400

apptitude

guntur


29

STATE -----------andhrapradesh

Branch (branch-id, branch-name, branch-city) Customer (customer-id, customer-name, customer-city, branch-id) (a) Create a form to accept the data from the user with appropriate validation checks (b) Generate queries to do the following: (i) List all those customers who live in the same city as the branch in which they have account. (ii) List all those customers who have an account in a given branch city. PROGRAM: Creating the tables: SQL> create table branch_09 2 (branch_id varchar(6) primary key, 3 branch_name varchar(8), 4 branch_city varchar(15)): Table created. SQL> create table customer_09 2 (customer_id varchar(6) primary key, 3 customer_name varchar(10), 4 customer_city varchar(15), 5 branch_id varchar(6), 6constraints fk09 foreign key(branch_id) references branch_09(branch_id)); Table created. SQL> insert into branch_09 values 2 ('&branch_id','&branch_name','&branch_city'); Enter value for branch_id: sbh01 Enter value for branch_name: sbh Enter value for branch_city: hyd old 2: ('&branch_id','&branch_name','&branch_city') new 2: ('sbh01','sbh','hyd') 1 row created. SQL> / Enter value for branch_id: sbh02 Enter value for branch_name: sbh Enter value for branch_city: hyd old 2: ('&branch_id','&branch_name','&branch_city') new 2: ('sbh02','sbh','hyd')

30

SQL> insert into customer_09 values 2 ('&customer_id','&customer_name','&customer_city','&branch_id'); Enter value for customer_id: cus01 Enter value for customer_name: abcde Enter value for customer_city: hyd Enter value for branch_id: sbh01 old 2: ('&customer_id','&customer_name','&customer_city','&branch_id') new 2: ('cus01','abcde','hyd','sbh01') 1 row created. SQL> / Enter value for customer_id: cuso2 Enter value for customer_name: fghij Enter value for customer_city: kurnool Enter value for branch_id: sbh02 old 2: ('&customer_id','&customer_name','&customer_city','&branch_id') new 2: ('cuso2','fghij','kurnool','sbh02') 1 row created. SQL> select * from branch_09; BRANCH ------------sbh01 sbh02 sbh03 icci01 icci02 icci03

BRANCH_N ---------------sbh sbh sbh icci icci icci

BRANCH_CITY --------------------hyd hyd kurnool hyd hyd kurnool

6 rows selected. SQL> select * from customer_09; CUSTOM -----------cus01 cuso2 cus03 cus04 cus05 cus06

CUSTOMER_N --------------------abcde fghij klmno pqrst uvwxy sjfkdl

CUSTOMER_CITY -----------------------hyd kurnool hyd xxxx hyd kurnool

6 rows selected. (a) Queries:

31

BRANCH --------------sbh01 sbh02 sbh03 icci01 icci02 icci03

(i) select * from customer_09 where branch_id in (select branch_id from branch_09 where branch_city=customer_city); CUSTOM -----------cus01 cus05 cus06

CUSTOMER_N -------------------abcde uvwxy sjfkdl

CUSTOMER_CITY -----------------------hyd hyd kurnool

BRANCH ------------sbh01 icci02 icci03

(ii) SQL> select * from customer_09 where branch_id in (select branch_id from branch_09 where branch_city='kurnool'); CUSTOM -----------cus03 cus06

CUSTOMER_N ------------------klmno sjfkdl

CUSTOMER_CITY -----------------------hyd kurnool

10. Create the following tables:

32

BRANCH -----------sbh03 icci03

Book (accession no, title, publisher, year, date-of-purchase, status) Member (member-id, number-of-books-issued, max-limit) Book-issue (accession-no, member-id, date-of-issue) (b) Generate the following to do the following: (i) List all those books which are due from the students to be returned. A book is considered to be isuued 15 days back and yet not returned. (ii) List all those members who cannot be issued any more books PROGRAM: Creating the tables: SQL> create table book_10 2 (accession_no varchar(7) primary key, 3 title varchar(10), 4 publisher varchar(10), 5 year date, 6 date_of_purchase date, 7 status varchar(8)); Table created. SQL> create table memeber 2 (memeber_id varchar(5) primary key, 3 name varchar(8), 4 number_of_books_issued number(2), 5 max_limit number(2)); Table created. SQL> create table book_issue 2 (accession_no varchar(7), 3 memeber_id varchar(5), 4 date_of_issue date, 5 constraints fk_10 foreign key(accession_no) references book_10(accession_no), 6 constraints fk10 foreign key(memeber_id) references memeber(memeber_id)); Table created. Inserting values into table: SQL> insert into book_10 values 2 ('&ACCESSION_NO','&TITLE','&PUBLISHER','&YEAR','& DATE_OF_PURCHASE','&STATUS'); Enter value for accession_no: mca2110 Enter value for title: dbms Enter value for publisher: tata

33

Enter value for year: 06-sep-2005 Enter value for date_of_purchase: 08-oct-2006 Enter value for status: issued old 2: ('&ACCESSION_NO','&TITLE','&PUBLISHER','&YEAR','& DATE_OF_PURCHASE','&STATUS') new 2: ('mca2110','dbms','tata','06-sep-2005','08-oct-2006','issued') 1 row created. SQL> / Enter value for accession_no: mca2115 Enter value for title: c++ Enter value for publisher: lll Enter value for year: 19-dec-2000 Enter value for date_of_purchase: 20-apr-2001 Enter value for status: notissue old 2: ('&ACCESSION_NO','&TITLE','&PUBLISHER','&YEAR','& DATE_OF_PURCHASE','&STATUS') new 2: ('mca2115','c++','lll','19-dec-2000','20-apr-2001','notissue') 1 row created. SQL> insert into memeber values 2 ('&MEMEBER_ID','&NAME','&NUMBER_OF_BOOKS_ISSUED','&MAX_LIMIT'); Enter value for memeber_id: mc21 Enter value for name: xxxxx Enter value for number_of_books_issued: 2 Enter value for max_limit: 3 old 2: ('&MEMEBER_ID','&NAME','&NUMBER_OF_BOOKS_ISSUED','&MAX_LIMIT') new 2: ('mc21','xxxxx','2','3') 1 row created SQL> / Enter value for memeber_id: mc22 Enter value for name: yyyyy Enter value for number_of_books_issued: 3 Enter value for max_limit: 3 old 2: ('&MEMEBER_ID','&NAME','&NUMBER_OF_BOOKS_ISSUED','&MAX_LIMIT') new 2: ('mc22','yyyyy','3','3') 1 row created. SQL> insert into book_issue values

34

2 ('&ACCESSION_NO','&MEMEBER_ID','&DATE_OF_ISSUE'); Enter value for accession_no: mca2114 Enter value for memeber_id: mc25 Enter value for date_of_issue: 27-sep-2008 old 2: ('&ACCESSION_NO','&MEMEBER_ID','&DATE_OF_ISSUE') new 2: ('mca2114','mc25','27-sep-2008') 1 row created. SQL> / Enter value for accession_no: mca2115 Enter value for memeber_id: mc26 Enter value for date_of_issue: 15-sep-2008 old 2: ('&ACCESSION_NO','&MEMEBER_ID','&DATE_OF_ISSUE') new 2: ('mca2115','mc26','15-sep-2008') 1 row created. SQL> select * from book_10; ACCESSI -----------mca2110 mca2111 mca2112 mca2113 mca2114 mca2115

TITLE -------dbms os or icg dcn c++

PUBLISHER YEAR DATE_OF_PUR --------------------------------------------tata 06-SEP-05 08-OCT-06 galvin 01-JAN-01 01-FEB-01 sharma 02-MAR-99 06-DEC-00 xxxxx 05-APR-95 06-MAY-96 yyyyy 06-OCT-96 08-NOV-97 ll 19-DEC-00 20-APR-01

STATUS ---------issued issued notissue issued issued notissue

6 rows selected. SQL> select * from memeber; MEMEB ---------mc21 mc22 mc33 mc24 mc25 mc26

NAME NUMBER_OF_BOOKS_ISSUED --------- -----------------------------------------xxxxx 2 yyyyy 3 llllll 2 mmmmm 3 rrrrr 3 pppp 3

6 rows selected. SQL> select * from book_issue;

35

MAX_LIMIT -----------------3 3 3 3 3 4

ACCESSI MEMEB ------- ----- --------mca2110 mc21 mca2111 mc22 mca2112 mc33 mca2113 mc24 mca2114 mc25 mca2115 mc26

DATE_OF_I 01-OCT-08 20-SEP-08 06-OCT-08 25-SEP-08 27-SEP-08 15-SEP-08

6 rows selected. (b) Queries: (i)SQL> select * from book_issue where (months_between(sysdate,date_of_issue)*30>15); ACCESSI -----------mca2111 mca2115

MEMEB ----------mc22 mc26

DATE_OF_ISSUE ----------------------20-SEP-08 15-SEP-08

(ii) SQL> select * from memeber where number_of_books_issued=max_limit; MEMEB ----------mc22 mc24 mc25

NAME NUMBER_OF_BOOKS_ISSUED -------- -----------------------------------------yyyyy 3 mmmmm 3 rrrrr 3

MAX_LIMIT ----------------3 3 3

11. Create the following tables: Book (accession no, title, publisher, year, date-of-purchase, status)

36

Member (member-id, number-of-books-issued, max-limit) Book-issue (accession-no, member-id, date-of-issue) (b) Write a PL/SQL procedure to issue the book. Write a trigger to set the status of student to “back listed” if they have taken book but not returned even one year. PROGRAM: Creating Tables: SQL> create table book_11 2 (accession_no varchar(7) primary key, 3 title varchar(10), 4 publisher varchar(10), 5 year date, 6 date_of_purchase date, 7 status varchar(8)); Table created. SQL> create table member 2 (memeber_id varchar(5) primary key, 3 name varchar(8), 4 number_of_books_issued number(2), 5 max_limit number(2)); Table created. SQL> create table book_issue 2 (accession_no varchar(7), 3 memeber_id varchar(5), 4 date_of_issue date, 5 constraints fk_10 foreign key(accession_no) references book_10(accession_no), 6 constraints fk10 foreign key(memeber_id) references memeber(memeber_id)); Table created Inserting values into tables: SQL> insert into book_11 values 2 ('&accession_no','&title','&publisher','&year','&date_of_purchase','&status'); Enter value for accession_no: mca2110 Enter value for title: dbms Enter value for publisher: tata Enter value for year: 06-sep-2005 Enter value for date_of_purchase: 08-oct-2001 Enter value for status: issued

37

old 2: ('&accession_no','&title','&publisher','&year','&date_of_purchase','&status') new 2: ('mca2110','dbms','tata','06-sep-2005','08-oct-2001','issued') 1 row created. SQL> / Enter value for accession_no: mca2111 Enter value for title: or Enter value for publisher: galvin Enter value for year: 01-jan-2001 Enter value for date_of_purchase: 01-feb-2001 Enter value for status: issued old 2: ('&accession_no','&title','&publisher','&year','&date_of_purchase','&status') new 2: ('mca2111','or','galvin','01-jan-2001','01-feb-2001','issued') 1 row created. SQL> insert into member values 2 ('&MEMBER_ID','&NAME','&NUMBER_OF_BOOKS_ISSUED','&MAX_LIMIT'); Enter value for member_id: mc21 Enter value for name: xxxxx Enter value for number_of_books_issued: 2 Enter value for max_limit: 3 old 2: ('&MEMBER_ID','&NAME','&NUMBER_OF_BOOKS_ISSUED','&MAX_LIMIT') new 2: ('mc21','xxxxx','2','3') 1 row created. SQL> / Enter value for member_id: mc22 Enter value for name: yyyyy Enter value for number_of_books_issued: 3 Enter value for max_limit: 3 old 2: ('&MEMBER_ID','&NAME','&NUMBER_OF_BOOKS_ISSUED','&MAX_LIMIT') new 2: ('mc22','yyyyy','3','3') 1 row created. SQL> insert into book_issue_11 values 2 ('&ACCESSION_NO','&MEMBER_ID','&DATE_OF_ISSUE'); Enter value for accession_no: mca2110 Enter value for member_id: mc21 Enter value for date_of_issue: 01-oct-2007

38

old 2: ('&ACCESSION_NO','&MEMBER_ID','&DATE_OF_ISSUE') new 2: ('mca2110','mc21','01-oct-2007') 1 row created. SQL> / Enter value for accession_no: mca2111 Enter value for member_id: mc22 Enter value for date_of_issue: 20-sep-2008 old 2: ('&ACCESSION_NO','&MEMBER_ID','&DATE_OF_ISSUE') new 2: ('mca2111','mc22','20-sep-2008') SQL> select * from book_11; ACCESSI -----------mca2110 mca2111 mca2112 mca2113 mca2114 mca2115

TITLE ------dbms or os icg dcn c++

PUBLISHER ----------------tata galvin sharma xxxxx yyyyy llll

YEAR DATE_OF_P STATUS ----------------------- ----------06-SEP-05 08-OCT-01 issued 01-JAN-01 01-FEB-01 issued 02-MAR-99 06-DEC-00 notissue 05-APR-95 06-MAY-96 issued 06-OCT-96 08-NOV-97 issued 19-DEC-00 20-APR-01 notissue

6 rows selected. SQL> select * from member; MEMBE ---------mc21 mc22 mc23 mc24 mc25 mc26

NAME NUMBER_OF_BOOKS_ISSUED -----------------------------------------------xxxxx 2 yyyyy 3 lllll 2 mmmmm 3 rrrrr 3 ppppp 3

6 rows selected

SQL> select * from book_issue_11;

39

MAX_LIMIT ---------------3 3 3 3 3 4

ACCESSI -----------mca2110 mca2111 mca2112 mca2113 mca2114 mca2115

MEMBE ----------mc21 mc22 mc23 mc23 mc24 mc25

DATE_OF_I ---------------01-OCT-07 20-SEP-08 06-OCT-08 25-SEP-07 27-SEP-07 15-SEP-08

6 rows selected. PROCEDURE: SQL>create or replace procedure p11 is ac_no book_11.accession_no%type; sta book_11.status%type; bookissued exception; begin ac_no:='&ac_no'; select status into sta from book_11 where accession_no=ac_no; if(sta='notissue') then update book_11 set status='issued' where accession_no=ac_no; dbms_output.put_line('book issued'); else raise bookissued; end if; exception when bookissued then dbms_output.put_line('already book has been issued'); end; SQL>/ Enter value for ac_no: mca2112 old 6: ac_no:='&ac_no'; new 6: ac_no:='mca2112'; book issued SQL>exec p11; Book issued. 3L/SQL procedure successfully completed.

40

select * from book_11; ACCESSI -----------mca2110 mca2111 mca2112 mca2113 mca2114 mca2115

TITLE ---------dbms or os icg dcn c++

PUBLISHER ---------------tata galvin sharma xxxxx yyyyy llll

YEAR DATE_OF_P -------- ----------------06-SEP-05 08-OCT-01 01-JAN-01 01-FEB-01 02-MAR-99 06-DEC-00 05-APR-95 06-MAY-96 06-OCT-96 08-NOV-97 19-DEC-00 20-APR-01

STATUS ----------issued issued issued issued issued notissue

6 rows selected. Trigger: SQL> create or replace trigger t12 before insert or update on book_issue for each row begin if(:new.date_of_issue<'01-jan-2007') then update book_11 set status='back listed'; end if; end; SQL> / Trigger created. Execution of trigger: SQL> insert into book_issue values ('mca2156','mc27','01-dec-2006') SQL> / insert into book_issue * ERROR at line 1: ORA-04098: trigger 'SCOTT.BOOKTRIGGER' is invalid and failed re-validation ORA-06512: at "SCOTT.T12", line 3 ORA-04088: error during execution of trigger 'SCOTT.T12'

12. Create the following Book (accession-no, title, publisher, year, date-of-purchase)

41

Book-place (accession-no, rack-id, rack-position) Member (member-id, name, number-of-books-issued, max-limit, status) Book-issue (accession-no, member-id, date-of-purchase) (a) Write a PL/SQL procedure to issue the book (b) Write a trigger to set the status of the book neither to “lost” which is neither issued nor in the library. PROGRAM: Creating Tables: SQL> create table book_12 2 (accession_no varchar(7) primary key, 3 title varchar(10), 4 publisher varchar(8), 5 year number(4), 6 date_of_purchase date, 7 status varchar(11)); Table created. SQL> create table book_place_12 2 (accession_no varchar(7), 3 rack_id varchar(5), 4 rack_position varchar(5), 5 constraints fkk foreign key(accession_no) references book_12(accession_no)); Table created. SQL> 2 3 4 5 6

create table member_12 (member_id varchar(7) primary key, name varchar(5), no_of_bokks_issued number(2), max_limit number(2), status varchar(11));

Table created. SQL> create table book_issue_12 2 (accession_no varchar(7), 3 member_id varchar(7), 4 date_of_issue date, 5 constraints fk12 foreign key(accession_no) references book_12(accession_no), 6 constraints fk_12 foreign key(member_id) references member_12(member_id)); Table created.

42

Inserting Values into table: (i) Into table book_12 SQL> insert into book_12 values 2 ('&accession_no','&title','&publisher',&year,'&date_of_purchase','&status'); Enter value for accession_no: mca2113 Enter value for title: icg Enter value for publisher: wwwww Enter value for year: 2005 Enter value for date_of_purchase: 13-oct-2006 Enter value for status: issued old 2: ('&accession_no','&title','&publisher',&year,'&date_of_purchase','&status') new 2: ('mca2113','icg','wwwww',2005,'13-oct-2006','issued') 1 row created. SQL> / Enter value for accession_no: mca2114 Enter value for title: dcn Enter value for publisher: gggg Enter value for year: 2007 Enter value for date_of_purchase: 25-jan-2007 Enter value for status: not issued old 2: ('&accession_no','&title','&publisher',&year,'&date_of_purchase','&status') new 2: ('mca2114','dcn','gggg',2007,'25-jan-2007','not issued') 1 row created. SQL> select * from book_12; ACCESSI -----------mca2110 mca2111 mca2112 mca2113 mca2114 mca2115

TITLE -------dbms os or icg dcn c++

PUBLISHE -------------tata xxxx yyyy wwwww gggg jjjj

YEAR -------2000 1995 2001 2005 2007 1995

6 rows selected.

43

DATE_OF_P ---------------06-OCT-01 15-NOV-96 14-OCT-01 13-OCT-06 25-JAN-07 09-SEP-96

STATUS ---------issued not issued not issued issued not issued not issued

(ii) Into book_place_12 SQL> insert into book_place_12 values 2 ('&ACCESSION_NO','&RACK_ID','&RACK_POSITION'); Enter value for accession_no: mca2110 Enter value for rack_id: 01 Enter value for rack_position: 11 old 2: ('&ACCESSION_NO','&RACK_ID','&RACK_POSITION') new 2: ('mca2110','01','11') 1 row created. SQL> / Enter value for accession_no: mca2111 Enter value for rack_id: 02 Enter value for rack_position: 12 old 2: ('&ACCESSION_NO','&RACK_ID','&RACK_POSITION') new 2: ('mca2111','02','12') 1 row created. SQL> select * from book_place_12; ACCESSI RACK_ ID RACK_POSI --------------------------------------mca2110 01 11 mca2111 02 12 mca2112 03 00 mca2113 04 14 mca2114 05 01 mca2115 06 15 6 rows selected. (iii) Into table member_12 SQL> insert into member_12 values ('&MEMBER_ID','&NAME',&NO_OF_BOKKS_ISSUED,&MAX_LIMIT,'&STAT US'); Enter value for member_id: m1211 Enter value for name: abcd Enter value for no_of_bokks_issued: 2 Enter value for max_limit: 4 Enter value for status: can issue

44

old 2: ('&MEMBER_ID','&NAME',&NO_OF_BOKKS_ISSUED,&MAX_LIMIT,'&STATUS') new 2: ('m1211','abcd',2,4,'can issue') 1 row created. SQL> / Enter value for member_id: mc1212 Enter value for name: efgh Enter value for no_of_bokks_issued: 4 Enter value for max_limit: 4 Enter value for status: cant issue old 2: ('&MEMBER_ID','&NAME',&NO_OF_BOKKS_ISSUED,&MAX_LIMIT,'&STATUS') new 2: ('mc1212','efgh',4,4,'cant issue') 1 row created. SQL> select * from member_12; MEMBER_ NAME NO_OF_BOKKS_ISSUED MAX_LIMIT STATUS ------- ----- ------------------ ---------- ----------mc1211 abcd 2 4 can issue mc1212 defg 4 4 cant issue mc1213 efgh 4 4 cant issue mc1214 ghij 1 3 can issue mc1215 jklm 0 4 can issue mc1216 mnop 3 4 can issue 6 rows selected.. (iv) Into book-issue SQL> insert into book_issue_12 values 2 ('&accession_no','&member_id','&date_of_issue'); Enter value for accession_no: mca2110 Enter value for member_id: m1211 Enter value for date_of_issue: 01-sep-2008 old 2: ('&accession_no','&member_id','&date_of_issue') new 2: ('mca2110','m1211','01-sep-2008') 1 row created.

45

SQL> / Enter value for accession_no: mca2111 Enter value for member_id: mc1212 Enter value for date_of_issue: 05-sep-2008 old 2: ('&accession_no','&member_id','&date_of_issue') new 2: ('mca2111','mc1212','05-sep-2008') 1 row created. SQL> select * from book_issue_12; ACCESSI -----------mca2110 mca2111 mca2112 mca2113 mca2114 mca2115

MEMBER_ -------------m1211 mc1212 mc1213 mc1214 mc1215 mc2116

DATE_OF_I ---------------01-SEP-08 05-SEP-08 01-OCT-08 08-SEP-08 05-OCT-08 25-SEP-08

6 rows selected. (b) PROCEDURE: SQL> create or replace procedure p12 is 2 ac_no book_12.accession_no%type; 3 sta book_12.status%type; 4 bookissued exception; 5 begin 6 ac_no:='&ac_no'; 7 select status into sta from book_12 where accession_no=ac_no; 8 if(sta='not issued') then 9 update book_12 set status='issued' where accession_no=ac_no; 10 dbms_output.put_line('book issued'); 11 else 12 raise bookissued; 13 end if; 14 exception 15 when bookissued then 16 dbms_output.put_line('already book has been issued'); 17 end; 18 / Enter value for ac_no: mca2111 old 6: ac_no:='&ac_no'; new 6: ac_no:='mca2111';

46

Procedure created. SQL> exec p12; book issued PL/SQL procedure successfully completed. SQL> select * from book_12; ACCESSI TITLE PUBLISHE ------------- -------- --------------mca2110 dbms tata mca2111 os xxxx mca2112 or yyyy mca2113 icg wwwww mca2114 dcn gggg mca2115 c++ jjjj

YEAR -------2000 1995 2001 2005 2007 1995

DATE_OF_P ----------------06-OCT-01 15-NOV-96 14-OCT-01 13-OCT-06 25-JAN-07 09-SEP-96

6 rows selected. SQL>create or replace trigger t122 before insert or update on book_place_12 for each row begin if((:new.rack_position=00)) then update book_12 set status='lost'; end if; end; SQL> / Trigger created.

13. Create the following Book (accession-no, title, publisher, year, date-of-purchase)

47

STATUS -----------issued issued not issued issued not issued not issued

Member (member-id, name, number-of-books-issued, max-limit, status) Book-issue (accession-no, member-id, date-of-purchase ,due-date) (a) Write a PL/SQL to list all thos estudents who are defaulters. A student is considered to be a defaulter if he has not returned a book even after the due-date (b) Write a trigger to set the status of the student to“back listed”if they have taken book but not returned even after one year. PROGRAM: Creating Tables: SQL> create table book_13 2 (accession_no varchar(7) primary key, 3 title varchar(10), 4 publisher varchar(8), 5 year number(4), 6 date_of_purchase date, 7 status varchar(11)); Table created. SQL> create table member_13 2 (member_id varchar(7) primary key, 3 name varchar(5), 4 no_of_books_issued number(2), 5 max_limit number(2), 6 status varchar(11)); Table created. SQL> create table book_issue_13 2 (accession_no varchar(7), 3 member_id varchar(7), 4 date_of_issue date, 5 Due_date date, 6 constraints fk13 foreign key(accession_no) references book_13(accession_no), 7 constraints fk_13 foreign key(member_id) references member_13(member_id)); Table created.

48

SQL> select * from book_13; ACCESSI TITLE PUBLISHE YEAR DATE_OF_P STATUS ------- ---------- -------- ---------- --------- ----------mca2110 dbms tata 2000 06-OCT-01 issued mca2111 os xxxx 1995 15-NOV-96 not issued mca2112 or yyyy 2001 14-OCT-01 not issued mca2113 icg wwwww 2005 13-OCT-06 issued mca2114 dcn gggg 2007 25-JAN-07 not issued mca2115 c++ jjjj 1995 09-SEP-96 not issued 6 rows selected. SQL> select * from member_13; MEMBER_ NAME NO_OF_BOOKS MAX_LIMIT STATUS ------- ----- ----------- ---------- ----------mc1211 abcd 2 4 can issue mc1212 defg 4 4 cant issue mc1213 efgh 4 4 cant issue mc1214 ghij 1 3 can issue mc1215 jklm 0 4 can issue mc1216 mnop 3 4 can issue 6 rows selected. SQL> select * from book_issue_13; ACCESSI MEMBER_ DATE_OF_I DUE_DATE ------- ------- --------- --------mca2110 mc1211 01-SEP-07 01-SEP-08 mca2111 mc1212 05-SEP-08 10-OCT-08 mca2112 mc1213 01-OCT-05 30-OCT-07 mca2113 mc1214 08-SEP-08 28-SEP-08 mca2114 mc1215 05-OCT-08 28-OCT-08 mca2115 mc1216 25-SEP-07 25-SEP-08 6 rows selected.

Procedure:

49

SQL> declare 2 cursor c13 is select * from book_issue_13 where months_between(sysdate,date_of_isue)/12>1; 3 str_ac_no book_issue_13.accession_no%type; 4 str_m_id book_issue_13.member_id%type; 5 dt_issuedate book_issue_13.date_of_isue%type; 6 dt_due book_issue_13.due_date%type; 7 begin 8 dbms_output.put_line('DEFAULTERS'); 9 dbms_output.put_line('acession_no member_id date_of_issue due_date '); 10 open c13; 11 loop 12 fetch c13 into str_ac_no,str_m_id,dt_issuedate,dt_due ; 13 dbms_output.put_line(str_ac_no ||str_m_id ||dt_issuedate ||dt_due); 14 exit when c13 % notfound; 15 end loop; 16 close c13; 17* end; SQL> / DEFAULTERS acession_no member_id mca2110 mc1211 mca2112 mc1213 mca2115 mc1216

date_of_issue 01-SEP-07 01-OCT-05 25-SEP-07

due_date 01-SEP-08 30-OCT-07 25-SEP-08

PL/SQL procedure successfully completed. ] Trigger: SQL> create or replace trigger t13 before insert or update on book_issue for each row begin if(:new.date_of_issue<'01-jan-2007') then update book_13 set status='back listed'; end if; end; SQL> / Trigger created. Execution of trigger:

50

SQL> insert into book_issue values ('mca2156','mc27','01-dec-2006') SQL> / insert into book_issue * ERROR at line 1: ORA-04098: trigger 'SCOTT.BOOKTRIGGER' is invalid and failed re-validation ORA-06512: at "SCOTT.T12", line 3 ORA-04088: error during execution of trigger 'SCOTT.T12'

14. Create the following tables: Branch (branch-id, branch-name, branch_-city)

51

Customer (customer-id, customer-name, customer-city, branch-id) (b) Generate queries to do the following: (i) List all those customers who live in the same city as the branch in which they have account. (ii) List all those customers who have an account in more than one branch. PROGRAM: Creation of tables: SQL> create table branch15 (branch_id number(5) primary key, branch_name varchar(15), branch_city varchar(15)); Table created. SQL> create table customer15 (customer_id number(5) primary key, customer_name varchar(15), customer_city varchar(15)); Table created. SQL> create table branchcust15 (branch_id number(5), customer_id number(5), constraint fk_15 foreign key(branch_id) references branch15(branch_id), constraint fk_1555 foreign key(customer_id) references customer15(customer_id)); Table created. Inserting values: SQL> insert into branch15 values (&branch_id,'&branch_name','&branch_city'); Enter value for branch_id: 001 Enter value for branch_name: sbh Enter value for branch_city: hyd old 2: (&branch_id,'&branch_name','&branch_city') new 2: (001,'sbh','hyd') 1 row created. SQL> / Enter value for branch_id: 002 Enter value for branch_name: andhra

52

Enter value for branch_city: vizag old 2: (&branch_id,'&branch_name','&branch_city') new 2: (002,'andhra','vizag') 1 row created. SQL> insert into customer15 values (&customer_id,'&customer_name','&customer_city'); Enter value for customer_id: 101 Enter value for customer_name: abcd Enter value for customer_city: khamam old 2: (&customer_id,'&customer_name','&customer_city') new 2: (101,'abcd','khamam') 1 row created. SQL> / Enter value for customer_id: 102 Enter value for customer_name: defg Enter value for customer_city: hyd old 2: (&customer_id,'&customer_name','&customer_city') new 2: (102,'defg','hyd') 1 row created SQL> insert into branchcust15 values (&branch_id,&customer_id); Enter value for branch_id: 10 Enter value for customer_id: 101 old 2: (&branch_id,&customer_id) new 2: (10,101) 1 row created. SQL> / Enter value for branch_id: 10 Enter value for customer_id: 102 old 2: (&branch_id,&customer_id) new 2: (10,102) 1 row created.

SQL> select * from branch15;

53

BRANCH_ID BRANCH_NAME ---------- --------------- --------------10 sbh 11 icici 12 andhra 13 indian 14 hdfc

BRANCH_CITY khamam hyd secun vizag kurnool

SQL> select * from customer15; CUSTOMER_ID -------------------101 102 103 104 105 106 107 108 109

CUSTOMER_NAME ------ ------------------abcd defg fghi ijkl lmno opqr stuv wxyz afrg

9 rows selected. SQL> select * from branchcust15; BRANCH_ID -----------------10 101 11 102 12 105 13 106 14 108 11 101 12 101 13 105 14 105 12 105

CUSTOMER_ID --------------------

10 rows selected.

(b) Queries:

54

CUSTOMER_CITY ----------------------khamam hyd secun kurnool vizag hyd secun secun secun

(i) select * from customer15 where customer_id in (select customer_id from branchcust15 where branch_id in (select branch_id from branch15 where customer_city=branch_city)); CUSTOMER_ID CUSTOMER_NAME CUSTOMER_CITY -------------------------------------------------------------------= 101 abcd khamam 102 defg hyd 105 lmno vizag (ii) select * from customer15 where customer_id in (select customer_id from branchcust15 group by customer_id having count(branch_id)>1); CUSTOMER_ID -------------------101 105

CUSTOMER_NAME --------------------------abcd lmno

15. Create the following tables: Branch (branch-id, branch-name, branch_-city)

55

CUSTOMER_CITY -----------------------khamam vizag

Customer (customer-id, customer-name, customer-city, branch-id) (b) Generate queries to do the following: (i)List all those branches who have more than 2 customers. (ii) List all those customers who have an account in more than one branch. PROGRAM: Creation of tables: SQL> create table branch15 (branch_id number(5) primary key, branch_name varchar(15), branch_city varchar(15)); Table created. SQL> create table customer15 (customer_id number(5) primary key, customer_name varchar(15), customer_city varchar(15)); Table created. SQL> create table branchcust15 (branch_id number(5), customer_id number(5), constraint fk_15 foreign key(branch_id) references branch15(branch_id), constraint fk_1555 foreign key(customer_id) references customer15(customer_id)); Table created. Inserting values: SQL> insert into branch15 values (&branch_id,'&branch_name','&branch_city'); Enter value for branch_id: 001 Enter value for branch_name: sbh Enter value for branch_city: hyd old 2: (&branch_id,'&branch_name','&branch_city') new 2: (001,'sbh','hyd') 1 row created. SQL> / Enter value for branch_id: 002 Enter value for branch_name: andhra Enter value for branch_city: vizag

56

old 2: (&branch_id,'&branch_name','&branch_city') new 2: (002,'andhra','vizag') 1 row created. SQL> insert into customer15 values (&customer_id,'&customer_name','&customer_city'); Enter value for customer_id: 101 Enter value for customer_name: abcd Enter value for customer_city: khamam old 2: (&customer_id,'&customer_name','&customer_city') new 2: (101,'abcd','khamam') 1 row created. SQL> / Enter value for customer_id: 102 Enter value for customer_name: defg Enter value for customer_city: hyd old 2: (&customer_id,'&customer_name','&customer_city') new 2: (102,'defg','hyd') 1 row created SQL> insert into branchcust15 values (&branch_id,&customer_id); Enter value for branch_id: 10 Enter value for customer_id: 101 old 2: (&branch_id,&customer_id) new 2: (10,101) 1 row created. SQL> / Enter value for branch_id: 10 Enter value for customer_id: 102 old 2: (&branch_id,&customer_id) new 2: (10,102) 1 row created.

SQL> select * from branch15; BRANCH_ID

BRANCH_NAME

BRANCH_CITY

57

---------- --------------- --------------10 sbh 11 icici 12 andhra 13 indian 14 hdfc

khamam hyd secun vizag kurnool

SQL> select * from customer15; CUSTOMER_ID -------------------101 102 103 104 105 106 107 108 109

CUSTOMER_NAME ------ ------------------abcd defg fghi ijkl lmno opqr stuv wxyz afrg

CUSTOMER_CITY ----------------------khamam hyd secun kurnool vizag hyd secun secun secun

9 rows selected. SQL> select * from branchcust15; BRANCH_ID -----------------10 101 11 102 12 105 13 106 14 108 11 101 12 101 13 105 14 105 12 105

CUSTOMER_ID --------------------

(i)select * from branch15 where branch_id in (select branch_id from branchcust15 group by branch_id having count(customer_id)>2); BRANCH_ID ---------- ------12

BRANCH_NAME BRANCH_CITY --------- -------------- --------------andhra secun

58

(ii) select * from customer15 where customer_id in (select customer_id from branchcust15 group by customer_id having count(branch_id)>1); CUSTOMER_ID -------------------101 105

CUSTOMER_NAME --------------------------abcd lmno


59

CUSTOMER_CITY -----------------------khamam vizag

Student (roll-no, name, category, district, state) Student-rank (roll-no, marks, rank) (b) Generate queries to do the following : (i) List names of the students who are having same rank bur they should reside in different districts. (ii) List details of students they belongs to same category with same tank. PROGRAM: Creation of tables: SQL> create table student16 2 (roll_no number(5) primary key, 3 name varchar(15), 4 category varchar(10), 5 district varchar(15), 6 state varchar(5)); Table created. SQL> create table student_rank16 2 (roll_no number(5), 3 marks number(5), 4 rank number(3), 5 constraint fk_16 foreign key(roll_no)references student16(roll_no)); Table created. Inserting Values: SQL> insert into student16 values (&roll_no,'&name','&category','&district','&state'); Enter value for roll_no: 09 Enter value for name: abc Enter value for category: maths Enter value for district: rrr Enter value for state: vvv old 2: (&roll_no,'&name','&category','&district','&state') new 2: (09,'abc','maths','rrr','vvv') 1 row created. SQL> / Enter value for roll_no: 10 Enter value for name: def

60

Enter value for category: maths Enter value for district: xxx Enter value for state: yyy old 2: (&roll_no,'&name','&category','&district','&state') new 2: (10,'def','maths','xxx','yyy') 1 row created. . SQL> insert into student_rank16 values (&roll_no,&marks,&rank); Enter value for roll_no: 09 Enter value for marks: 200 Enter value for rank: 25 old 2: (&roll_no,&marks,&rank) new 2: (09,200,25) 1 row created. SQL> / Enter value for roll_no: 10 Enter value for marks: 150 Enter value for rank: 50 old 2: (&roll_no,&marks,&rank) new 2: (10,150,50) 1 row created. SQL> select * from student16; ROLL_NO ------------9 10 11 12 13 14 15

NAME -------abc def ghi jkl mno pqr stu

CATEGORY ---------------maths maths apptitude apptitude reasoning reasoning apptitude

DISTRICT ------------rrr xxx zzz ttt xxx sss www

7 rows selected

61

STATE --------vvv yyy bbb ppp yyy ccc qqq

SQL> select * from student_rank16; ROLL_NO ------------9 10 11 12 13 14 15

MARKS ---------200 150 150 300 150 400 150

RANK -------25 50 50 20 50 10 50

create view A as select category,count(category) from student group by category having count(category)>1; create view B as select student.rollno,A.category from student,A where student.category=a.category; create view C as select rank,count(rank) from student_rank group by rank having count(rank)>1; create view D as select student_rank.rollno,C.rank from student,C where student_rank.rank=C.rank; create view E as select B.rollno,B.category,D.rank where B.rollno=D.rollno; select student.rollno,student.name,student.district,student.state,E.category,E.rank where student.rollno=E.rollno;

62


63

Student (roll-no, name, date-of-birth, course-id) Course (course-id, name, fee, duration) (b) Generate queries to do the following: (i) List all those student who are between 10-19 years of age and have opted for MCA course. (ii) List all those courses in which number of students are less than 10. PROGRAM: Creation of tables: SQL> create table course17 2 ( course_id number(5) primary key, 3 name varchar(15), 4 fee number(10), 5 duration number(3)); Table created. SQL> create table student17 2 ( roll_no number(5) primary key, 3 name varchar(15), 4 date_of_birth date, 5 course_id number(5), 6 constraints fk17 foreign key(course_id)references course17(course_id)); Table created. Inserting values into tables: SQL> insert into course17 values 2 (&course_id,'&name',&fee,&duration); Enter value for course_id: 101 Enter value for name: MCA Enter value for fee: 24700 Enter value for duration: 3 old 2: (&course_id,'&name',&fee,&duration) new 2: (101,'MCA',24700,3) 1 row created.

SQL> / Enter value for course_id: 102

64

Enter value for name: MBA Enter value for fee: 20000 Enter value for duration: 2 old 2: (&course_id,'&name',&fee,&duration) new 2: (102,'MBA',20000,2) 1 row created. SQL> / Enter value for course_id: 103 Enter value for name: MSc Enter value for fee: 15000 Enter value for duration: 2 old 2: (&course_id,'&name',&fee,&duration) new 2: (103,'MSc',15000,2) 1 row created. SQL> insert into student17 values 2 (&roll_no,'&name','&date_of_birth',&course_id); Enter value for roll_no: 27 Enter value for name: rs Enter value for date_of_birth: 06-oct-1986 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (27,'rs','06-oct-1986',103) 1 row created. SQL> / Enter value for roll_no: 28 Enter value for name: st Enter value for date_of_birth: 06-JUN-95 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (28,'st','06-JUN-95',103) 1 row created.

SQL> select * from course17;

65

COURSE_ID ---------------101 102 103

NAME --------MCA MBA MSc

FEE ----24700 20000 15000

DURATION --------------3 2 2

SQL> select * from student17; ROLL_NO ------------1 2 3 11 12 13 19 20 21 22 23 24 25 26 27 28

NAME --------ab bc cd ef fg gh ij kl lm mn no op pq qr rs st

DATE_OF_B ----------------01-JAN-90 02-FEB-02 03-MAR-88 04-APR-85 05-MAY-02 06-JUN-95 07-JUL-86 08-AUG-89 07-JUL-86 08-AUG-89 01-JAN-99 02-FEB-00 03-MAR-98 04-APR-96 06-OCT-86 06-JUN-95

COURSE_ID --------------101 101 101 101 102 102 103 103 103 103 103 103 103 103 103 103

16 rows selected. SQL> select * from course17; COURSE_ID ---------------101 102 103


FEE -----24700 20000 15000

DURATION ---------------3 2 2

QUERIES: (i) SQL> select * from student17 where roll_no in 2 (select roll_no from student17 where 3 (months_between(sysdate,date_of_birth)/12 between 18 and 19) ) 4* and course_id = (select course_id from course17 where name='MCA'); ROLL_NO

NAME

DATE_OF_B

66

COURSE_ID

-------------1 11

---------ab ef

----------------01-JAN-90 04-APR-90

---------------101 101

(ii) select * from course17 where course_id in (select course_id from student17 group by course_id having count(roll_no)<10); COURSE_ID ---------------101 102

NAME --------MCA MBA

FEE ------24700 20000

67

DURATION --------------3 2

18. Create the following tables: Student (roll_no, name,date_of_birth,course-id) Course (course-id, name, fee, duration, status) (b) Write PL/SQL procedure to do the following: Set the status of course to “not offered” in which the number of candidate is less than 5. PROGRAM: Creation of tables: SQL> create table course18 2(course_id number(5) primary key, 3 name varchar(15), 4 fee number(10), 5 duration number(3), 6 status varchar(12)); Table created. SQL> create table student18 2( roll_no number(5) primary key, 3 name varchar(15), 4 date_of_birth date, 5 course_id number(5), 6 constraints fk18 foreign key(course_id)references course17(course_id)); Table created. Inserting values into tables: SQL> insert into course18 values 2 (&course_id,'&name',&fee,&duration); Enter value for course_id: 101 Enter value for name: MCA Enter value for fee: 24700 Enter value for duration: 3 old 2: (&course_id,'&name',&fee,&duration) new 2: (101,'MCA',24700,3) 1 row created.

68

SQL> / Enter value for course_id: 102 Enter value for name: MBA Enter value for fee: 20000 Enter value for duration: 2 old 2: (&course_id,'&name',&fee,&duration) new 2: (102,'MBA',20000,2) 1 row created.

SQL> insert into student17 values 2 (&roll_no,'&name','&date_of_birth',&course_id); Enter value for roll_no: 27 Enter value for name: rs Enter value for date_of_birth: 06-oct-1986 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (27,'rs','06-oct-1986',103) 1 row created. SQL> / Enter value for roll_no: 28 Enter value for name: st Enter value for date_of_birth: 06-JUN-95 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (28,'st','06-JUN-95',103) 1 row created. SQL> select * from course18; COURSE_ID ---------------101 102 103


FEE ------24700 20000 15000

SQL> select * from student18;

69

DURATION --------------3 2 2

STATUS ----------offered offered offered

ROLL_NO ------------1 2 3 11 12 13 19 20 21 22 23 24 25 26 27 28



COURSE_ID --------------101 101 101 101 102 102 103 103 103 103 103 103 103 103 103 103

16 rows selected. PROCEDURE: SQL> declare 2 c_id course18.course_id%type; 3 ctr number; 4 cursor c18 is select course_id from course18; 5 begin 6 open c18; 7 loop 8 fetch c18 into c_id; 9 select count(roll_no) into ctr from student18 where course_id=c_id; 10 if(ctr<5) then 11 update course18 set status='not offered' where course_id=c_id; 12 dbms_output.put_line('tables has been updated'); 13 end if; 14 exit when c18 % notfound; 15 end loop; 16 close c18; 17 end; 18 /

70

OUTPUT: tables has been updated tables has been updated PL/SQL procedure successfully completed. SQL>select * from course18; COURSE_ID ---------------101 102 103


FEE -------24700 20000 15000


71

DURATION --------------3 2 2

STATUS -----------not offered not offered offered

Student (roll_no, name,date_of_birth,course-id) Course (course-id, name, fee, duration, status) (b) Write PL/SQL procedure to do the following: Set the status of course to “not offered” in which the number of candidate is less than 5. PROGRAM: Creation of tables: SQL> create table course19 2(course_id number(5) primary key, 3 name varchar(15), 4 fee number(10), 5 duration number(3), 6 status varchar(12)); Table created. SQL> create table student19 2( roll_no number(5) primary key, 3 name varchar(15), 4 date_of_birth date, 5 course_id number(5), 6 constraints fk18 foreign key(course_id)references course17(course_id)); Table created. Inserting values into tables: SQL> insert into course19 values 2 (&course_id,'&name',&fee,&duration); Enter value for course_id: 101 Enter value for name: MCA Enter value for fee: 24700 Enter value for duration: 3 old 2: (&course_id,'&name',&fee,&duration) new 2: (101,'MCA',24700,3) 1 row created.

SQL> /

72

Enter value for course_id: 102 Enter value for name: MBA Enter value for fee: 20000 Enter value for duration: 2 old 2: (&course_id,'&name',&fee,&duration) new 2: (102,'MBA',20000,2) 1 row created.

SQL> insert into student19 values 2 (&roll_no,'&name','&date_of_birth',&course_id); Enter value for roll_no: 27 Enter value for name: rs Enter value for date_of_birth: 06-oct-1986 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (27,'rs','06-oct-1986',103) 1 row created. SQL> / Enter value for roll_no: 28 Enter value for name: st Enter value for date_of_birth: 06-JUN-95 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (28,'st','06-JUN-95',103) 1 row created. SQL> select * from course18; COURSE_ID ---------------101 102 103


FEE ------24700 20000 15000

SQL> select * from student18;

73

DURATION --------------3 2 2

STATUS ----------offered offered offered

ROLL_NO ------------1 2 3 11 12 13 19 20 21 22 23 24 25 26 27 28



COURSE_ID --------------101 101 101 101 102 102 103 103 103 103 103 103 103 103 103 103

16 rows selected. PROCEDURE: SQL> declare 2 c_id course18.course_id%type; 3 ctr number; 4 cursor c18 is select course_id from course18; 5 begin 6 open c18; 7 loop 8 fetch c18 into c_id; 9 select count(roll_no) into ctr from student18 where course_id=c_id; 10 if(ctr<5) then 11 update course18 set status='not offered' where course_id=c_id; 12 dbms_output.put_line('tables has been updated'); 13 end if; 14 exit when c18 % notfound; 15 end loop; 16 close c18; 17 end; 18 /

74

OUTPUT: tables has been updated tables has been updated PL/SQL procedure successfully completed. SQL>select * from course18; COURSE_ID ---------------101 102 103


FEE -------24700 20000 15000


75

DURATION --------------3 2 2

STATUS -----------not offered not offered offered

Student (roll-no, name, date_of_birth, course-id) Course (course-id, name, fee, duration, status) (b) Write PL/SQL procedure to do the following: Set the status of course to “offered” in which the number of candidate ist atleast 10 otherwise not offered. PROGRAM: Creation of tables : SQL> create table course20 2 (course_id number(5) primary key, 3 name varchar(15), 4 fee number(10), 5 duration number(3), 6 status varchar(12)); Table created. SQL> SQL> create table student20 2 ( roll_no number(5) primary key, 3 name varchar(15), 4 date_of_birth date, 5 course_id number(5), 6 constraints fk18 foreign key(course_id)references course17(course_id)); Table created. Inserting values into tables: SQL> insert into course20 values 2 (&course_id,'&name','&fee','&duration','&status') 3 ; Enter value for course_id: 101 Enter value for name: MCA Enter value for fee: 24700 Enter value for duration: 3 Enter value for status: old 2: (&course_id,'&name','&fee','&duration','&status') new 2: (101,'MCA','24700','3','') 1 row created. SQL> / Enter value for course_id: 102

76

Enter value for name: MBA Enter value for fee: 20000 Enter value for duration: 2 Enter value for status: old 2: (&course_id,'&name','&fee','&duration','&status') new 2: (102,'MBA','20000','2','') 1 row created. SQL> insert into student20 values 2 (&roll_no,'&name','&date_of_birth',&course_id); Enter value for roll_no: 27 Enter value for name: rs Enter value for date_of_birth: 06-oct-1986 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (27,'rs','06-oct-1986',103) 1 row created. SQL> / Enter value for roll_no: 28 Enter value for name: st Enter value for date_of_birth: 06-JUN-95 Enter value for course_id: 103 old 2: (&roll_no,'&name','&date_of_birth',&course_id) new 2: (28,'st','06-JUN-95',103) 1 row created. SQL> select * from course20; COURSE_ID ---------------101 102 103


FEE ------24700 20000 15000

77

DURATION --------------3 2 2

STATUS -----------

SQL> select * from student20; ROLL_NO ------------1 2 3 11 12 13 19 20 21 22 23 24 25 26 27 28



COURSE_ID --------------101 101 101 101 102 102 103 103 103 103 103 103 103 103 103 103

16 rows selected. PROCEDURE: SQL> declare 2 c_id course18.course_id%type; 3 ctr number; 4 cursor c20 is select course_id from course20; 5 begin 6 open c20; 7 loop 8 fetch c20 into c_id; 9 select count(roll_no) into ctr from student20 where course_id=c_id; 10 if(ctr>=10) then 11 update course20 set status='offered' where course_id=c_id; 12 else 13 update course20 set status=' not offered' where course_id=c_id; 14 end if; 15 exit when c20 % notfound; 16 dbms_output.put_line('table has been updated'); 17 end loop; 18 close c20; 19 end;

78

OUTPUT: table has been updated table has been updated table has been updated PL/SQL procedure successfully completed. SQL> select * from course20; COURSE_ID --------------------------101 102 103

NAME ---------

FEE -------

MCA MBA MSc

24700 20000 15000

79

DURATION ---------------3 2 2

STATUS not offered not offered offered

21. create the following tables Item(item_code,item_name,qty_in_stock,reorder_level) Supplier(supplier_code,supplier_name,address) Can_supply(supplier_code,item_code) (a)Create a form to accept the data from the user with appropriate validation checks (b)Create a report to list the items whose qty_in_stock is less than or equal to their reorder levels. Program: Creating Tables: SQL> create table item 2 (item_code varchar(5) primary key, 3 item_name varchar(6), 4 qty_in_stock number(3), 5 reorder_level number(3)); Table created. SQL> create table supplier 2 (supplier_code varchar(5) primary key, 3 supplier_name varchar(6), 4 address varchar(10)); Table created. SQL> create table can_supply (supplier_code varchar(5), item_code varchar(5), constraints fk3 foreign key(supplier_code) references supplier(supplier_code), constraints fk4 foreign key(item_code) references item(item_code)); Table created. SQL> select * from item; ITEM_ ITEM_N QTY_IN_STOCK REORDER_LEVEL ----- ------ ------------ ------------i101 cpu 25 5 i202 mouse 20 15 i303 drives 30 18 i404 keys 35 20 i505 pens 21 13 SQL> select * from supplier;

80

SUPPL SUPPLI ADDRESS ----- ------ ---------s111 abcd xxxx s222 efgh yyyy s333 ijkl zzzz s444 mnop wwww s555 rstu vvvv SQL> select * from can_supply; SUPPL ITEM_ ----- ----s111 i101 s222 i202 s333 i303 s444 i404 s555 i505

Creation of form *Steps for creation of form refer index The resulting form is as shown below

Creation of report

81

*Steps for creation of report refer index The resulting reportis as shown below

82

22.Create the following table : Item (item-code, item-name, qty-in-stock, reorder-level) Supplier (supplier-code, supplier-name, address) Can-Supply (supplier-code, item-code) b) Write PL/SQL procedure to do the following: Set the status of the supplier to “important” if the supplier can supply more than five items. PROGRAM: Creating tables: SQL> create table item22 2 (item_code number(5) primary key, 3 item_name varchar(15), 4 qty_in_stock number(5), 5 record_level number(5)); Table created. SQL> create table supplier22 2 (supp_code number(5) primary key, 3 supp_name varchar(15), 4 address varchar(15), 5 status varchar(15)); Table created. SQL> create table can_supply22 2 (supp_code number(5), 3 item_code number(5), 4 constraint fk22 foreign key(supp_code) references supplier22(supp_code), 5 constraint fh_22 foreign key(item_code) references item22(item_code)); Table created Inserting Values Into Tables: SQL> insert into item22 values 2 (&item_code,'&item_name',&qty_in_stock,&record_level); Enter value for item_code: 1111 Enter value for item_name: xxxx Enter value for qty_in_stock: 20 Enter value for record_level: 15

83

old 2: (&item_code,'&item_name',&qty_in_stock,&record_level) new 2: (1111,'xxxx',20,15) 1 row created. SQL> / Enter value for item_code: 2222 Enter value for item_name: yyyy Enter value for qty_in_stock: 15 Enter value for record_level: 10 old 2: (&item_code,'&item_name',&qty_in_stock,&record_level) new 2: (2222,'yyyy',15,10) 1 row created. SQL> insert into supplier22 values 2 (&supp_code,'&supp_name','&address','&status'); Enter value for supp_code: 1001 Enter value for supp_name: abcd Enter value for address: 1234 Enter value for status: processing old 2: (&supp_code,'&supp_name','&address','&status') new 2: (1001,'abcd','1234','processing') 1 row created. SQL> / Enter value for supp_code: 2002 Enter value for supp_name: defg Enter value for address: 4567 Enter value for status: processing old 2: (&supp_code,'&supp_name','&address','&status') new 2: (2002,'defg','4567','processing') 1 row created. SQL> select * from item22; ITEM_CODE -----------------1111 2222 3333 4444 5555 6666

ITEM_NAME ------------------xxxx yyyy zzzz aaaa bbbb cccc

QTY_IN_STOCK --------------------20 15 10 25 15 7

84

RECORD_LEVEL ----------------------15 10 5 15 15 5

SQL> select * from supplier22; SUPP_CODE ----------------1001 2002 3003

SUPP_NAME -----------------abcd defg hijk

ADDRESS -------------1234 4567 7890

SQL> insert into can_supply22 values 2 (&supp_code,&item_code); Enter value for supp_code: 1001 Enter value for item_code: 1111 old 2: (&supp_code,&item_code) new 2: (1001,1111) 1 row created. SQL> / Enter value for supp_code: 2002 Enter value for item_code: 2222 old 2: (&supp_code,&item_code) new 2: (2002,2222) 1 row created. SQL> select * from can_supply22; SUPP_CODE -----------------1001 2002 1001 3003 1001 3003 1001 2002 1001 1001

ITEM_CODE -----------------1111 2222 2222 1111 3333 2222 4444 4444 5555 6666

9 rows selected.

85

STATUS -------------processing processing processing

PROCEDURE: SQL> declare 2 s_c supplier22.supp_code%type; 3 ctr number; 4 cursor c22 is select supp_code from supplier22; 5 begin 6 open c22; 7 loop 8 fetch c22 into s_c; 9 select count(item_code) into ctr from can_supply22 where supp_code=s_c; 10 if(ctr>5) then 11 update supplier22 set status='important' where supp_code=s_c; 12 dbms_output.put_line('table updated'); 13 end if; 14 exit when c22 % notfound; 15 end loop; 16 close c22; 1 17 end; 18 / Output: table updated PL/SQL procedure successfully completed. SQL> select * from supplier22; SUPP_CODE ---------------1001 2002 3003

SUPP_NAME ----------------abcd defg hijk

ADDRESS --------------1234 4567 7890

23.Create the following tables

86

STATUS ----------important processing processing

Item(item_code,item_name,qty_in_stock,reorder_level) Supplier(supplier_code,supplier_name,address,status) Can_supply(supplier_code,item_code) Generate a report of those items that are supplied by those suppliers whose status is “important Program: SQL> create table item22(item_code number(5) primary key,item_name varchar(15), qty_in_stock number(5),record_level number(5)); Table created. SQL> create table supplier22(supp_code number(5) primary key,supp_name varchar(15), address varchar(15),status varchar(15)); Table created. SQL> create table can_supply22(supp_code number(5),item_code number(5),constraint fk22 foreign key(supp_code) references supplier22(supp_code),constraint fh_22 foreign key(item_code) references item22(item_code)); Table created Inserting Values Into Tables SQL> insert into item22 values(&item_code,'&item_name',&qty_in_stock,&record_level); Enter value for item_code: 1111 Enter value for item_name: xxxx Enter value for qty_in_stock: 20 Enter value for record_level: 15 old 2: (&item_code,'&item_name',&qty_in_stock,&record_level) new 2: (1111,'xxxx',20,15) 1 row created.

SQL> / Enter value for item_code: 2222 Enter value for item_name: yyyy Enter value for qty_in_stock: 15 Enter value for record_level: 10 old 2: (&item_code,'&item_name',&qty_in_stock,&record_level)

87

new 2: (2222,'yyyy',15,10) 1 row created. SQL> insert into supplier22 values(&supp_code,'&supp_name','&address','&status'); Enter value for supp_code: 1001 Enter value for supp_name: abcd Enter value for address: 1234 Enter value for status: processing old 2: (&supp_code,'&supp_name','&address','&status') new 2: (1001,'abcd','1234','processing') 1 row created. SQL> / Enter value for supp_code: 2002 Enter value for supp_name: defg Enter value for address: 4567 Enter value for status: processing old 2: (&supp_code,'&supp_name','&address','&status') new 2: (2002,'defg','4567','processing') 1 row created. SQL> select * from item22; ITEM_CODE ITEM_NAME QTY_IN_STOCK RECORD_LEVEL ---------- --------------- ------------ -----------1111 xxxx 20 15 2222 yyyy 15 10 3333 zzzz 10 5 4444 aaaa 25 15 5555 bbbb 15 15 6666 cccc 7 5 SQL> select * from supplier22; SUPP_CODE SUPP_NAME ADDRESS ---------- --------------- --------------- --------------1001 abcd 1234 processing 2002 defg 4567 processing 3003 hijk 7890 processing SQL> insert into can_supply22 values 2 (&supp_code,&item_code);

88

STATUS

Enter value for supp_code: 1001 Enter value for item_code: 1111 old 2: (&supp_code,&item_code) new 2: (1001,1111) 1 row created. SQL> / Enter value for supp_code: 2002 Enter value for item_code: 2222 old 2: (&supp_code,&item_code) new 2: (2002,2222) 1 row created. SQL> select * from can_supply22; SUPP_CODE ITEM_CODE ---------- ---------1001 1111 2002 2222 1001 2222 3003 1111 1001 3333 3003 2222 1001 4444 2002 4444 1001 5555 1001 6666 9 rows selected.

Procedure SQL> declare 2 s_c supplier23.supp_code%type; 3 ctr number; 4 cursor c23 is select supp_code from supplier23; 5 begin 6 open c23; 7 loop 8 fetch c23 into s_c;

89

9 select count(item_code) into ctr from can_supply23 where supp_code=s_c; 10 if(ctr>5) then 11 update supplier23 set status='important' where supp_code=s_c; 12 dbms_output.put_line('table updated'); 13 end if; 14 exit when c23 % notfound; 15 end loop; 16 close c23; 1 17 end; 18 / Output: table updated PL/SQL procedure successfully completed. SQL> select * from supplier22; SUPP_CODE SUPP_NAME -------------------------------1001 abcd 2002 defg 3003 hijk Creation of Report

ADDRESS --------------1234 4567 7890

STATUS ----------important processing processing

*Steps for creation of Report same as given in Problem21. The report generated is as follows.

24.Create the following tables

90

Student(roll_no,marks,category,district,state) Student_rank(roll_no,marks,rank) Generate a report to list of those districts from which the first hundred rankers come from. Creating the tables SQL> Create table student8(roll_no number(4) primary key,marks number(4),category varchar(2),district varchar(15),state varchar(15));

ROLL_NO

MARKS

CATEGORY

DISTRICT

STATE

6780

200

A

warangal

andhra pradesh

6781

180

A

ranga reddy

andhra pradesh

6782

176

A

nizambad

andhra pradesh

6783

175

A

nalgonda

andhra pradesh

6784

173

A

khammam

andhra pradesh

6785

170

A

adilabad

andhra pradesh

6786

168

B

tiruchhi

tamilnadu

6787

167

B

kanchi

tamilnadu

6788

165

B

kanchi

tamilnadu

6789

164

B

drwad

karnataka

6790

160

B

hubli

karnataka

6791

159

B

erode

Tamilnadu

6792

158

B

erode

Tamilnadu

6793

157

B

hubli

karnataka

6794

156

B

krishna

andhra pradesh

6795

153

B

guntur

andhra pradesh

6796

152

B

kadapa

andhra pradesh

6797

150

C

prakasham

andhra pradesh

6798

148

C

nellore

andhra pradesh

SQL> create table student_rank(roll_no number(4),marks number(4),rank number(3),constraints fk81 foreign key(roll_no) references student8(roll_no));

91

ROLL_NO

MARKS

RANK

6781

180

2

6780

200

1

6782

176

8

6783

175

10

6784

173

25

6785

170

32

6786

168

41

6787

167

59

6788

165

72

6789

164

84

6790

160

100

6791

159

110

6792

158

129

6793

157

143

6794

156

164

6795

153

170

6796

152

179

6797

150

184

6798

148

197

Creation of Report Steps for creation of Report refer index The report generated is as follows.

25.Create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code,status) Faculty(faculty_code,faculty_name,specialization)

92

Write a PL/SQL procedure to the following: Set the status of the subject to “not offered” if the subject is not opted by at least 5 students.

Creating the table

SQL> Create table faculty25(faculty_code number(4) primary key,faculty_name varchar(25),specialization varchar(10)); FACULTY_CODE

FACULTY_NAME

SPECIALIZATION

5678

Syed Nazia Begum

M.Tech

5679

M Suhasini

M.Sc

5680

S Zareena

M.com

5681

B Sujana Kumari

MCA

5682

K Balaramudu

MBA

5683

G Gopinath

MA

5684

M Rama Krishna

B.Sc

5685

B Ramesh

B.Com

5686

D.Anilkumar Reddy

M.Tech

5687

J.Haribabu

M.Sc

5688

S.Mahammad Goush

M.com

5689

L.Sudhakar

MCA

5690

O.Sivaprasad

BA

5691

G.Manesh

B.Tech

5692

M.Nagaraju

BE

SQL> Crete table student25(roll_no varchar(19),subject_opted varchar(15)); ROLL_NO

NAME

number(4)

SUBJECT_OPTED

5687

A.Nagedra

DBMS

5688

B.Adinarayana

History

5689

B.Ayyawaru

Physics

5690

B.Dastagiri

History

5691

B.Ramakrishna

Physics

5692

B.Ramesh

Physics

5693

C.Jayaraju

Physics

5694

C.Rajashekar

Physics

5695

C.Subramanyam

DBMS

5696

C.Venkatasiva

Physics

5697

D.Anilkumar Reddy

History

5698

D.Udaykiran

History

93

primary

key,name

5699

G.Ganganna

DBMS

5700

G.Gurappa

Accounts

5701

G.Manesh

Accounts

5702

G.Suresh

History

5703

G.Venkataramana

Accounts

5704

J.Haribabu

DBMS

5705

K.Diwakar Reddy

Physics

5706

K.Gourisankar

DBMS

5707

K.Ramakrishna

OS

5708

K.Ramakrishna

History

5709

K.Ramesh

OS

5710

K.Ramesh

DBMS

5711

K.Sankar Reddy

DBMS

5712

K.Sivasankar

History

5713

L.Sudhakar

DBMS

5714

M.Nagaraju

Accounts

5715

M.Nagarjuna

Physics

5716

M.Raghavendra Reddy

DBMS

5717

O.Sivaprasad

OS

5718

P.Bashakar

Accounts

5719

P.Moulali

OS

5720

P.Nagaraju

Physics

5721

P.Rafi

OS

5722

P.Sreeramulu

Accounts

5723

P.Tirupalu

OS

5724

S.Anwar Basha

OS

5725

S.Baba

OS

5726

S.Gangaraju

OS

5727

S.Ibrahim

DBMS

5728

S.Jilani Basha

Accounts

5729

S.Mahammad Goush

OS

5730

T.Babaiah

History

5731

T.Balagangi Reddy

Accounts

5732

T.Sreenivasulu

DBMS

5733

Y.Vijayaram

OS

5734

N.jagan

Electronics

5735

K.Hari Babu

Chemistry

SQL> create table subject_rank25(subject_code number(4) primary key,subject_name varchar(15),faculty_code number(4),status varchar(18)); SUBJECT_CODE

SUBJECT_NAME

FACULTY_CODE

STATUS

9110

Accounts

5688

Offered

9111

DBMS

5681

Offered

9112

History

5683

Offered

9113

OS

5678

Offered

94

9114

Physics

5687

Offered

9115

Electronics

0

Offered

9116

Chemistry

0

Offered

Procedure SQL> create or replace procedure PRO25

is z1 subject_rank25.subject_code%type; ctr number; cursor c1 is select subject_code from subject_rank25; begin open c1; loop fetch c1 into z1; select count(roll_no) into ctr from student25 where subject_opted=z1; if(ctr<5) then update subject_rank25 set status='Not offered' where subject_code=z1; end if; exit when c1%notfound; end loop; close c1; end; PL/SQL procedure successfully completed SQL> select * from subject_rank25; SUBJECT_CODE

SUBJECT_NAME

FACULTY_CODE

STATUS

9110

Accounts

5688

Offered

9111

DBMS

5681

Offered

9112

History

5683

Offered

9113

OS

5678

Offered

9114

Physics

5687

Offered

9115

Electronics

0

Not offered

9116

Chemistry

0

Not offered

26.Create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code,status) Faculty(faculty_code,faculty_name,specialization) Write a PL/SQL procedure to the following: Set the status of the subject to “not offered” if the subject is not offered by any faculty 95

Creating the table


FACULTY_NAME

SPECIALIZATION

5678

Syed Nazia Begum

M.Tech

5679

M Suhasini

M.Sc

5680

S Zareena

M.com

5681

B Sujana Kumari

MCA

5682

K Balaramudu

MBA

5683

G Gopinath

MA

5684

M Rama Krishna

B.Sc

5685

B Ramesh

B.Com

5686

D.Anilkumar Reddy

M.Tech

5687

J.Haribabu

M.Sc

5688

S.Mahammad Goush

M.com

5689

L.Sudhakar

MCA

5690

O.Sivaprasad

BA

5691

G.Manesh

B.Tech

5692

M.Nagaraju

BE


NAME

number(4)

SUBJECT_OPTED

5687

A.Nagedra

DBMS

5688

B.Adinarayana

History

5689

B.Ayyawaru

Physics

5690

B.Dastagiri

History

5691

B.Ramakrishna

Physics

5692

B.Ramesh

Physics

5693

C.Jayaraju

Physics

5694

C.Rajashekar

Physics

5695

C.Subramanyam

DBMS

5696

C.Venkatasiva

Physics

5697

D.Anilkumar Reddy

History

5698

D.Udaykiran

History

5699

G.Ganganna

DBMS

5700

G.Gurappa

Accounts

5701

G.Manesh

Accounts

5702

G.Suresh

History

5703

G.Venkataramana

Accounts

96

primary

key,name

5704

J.Haribabu

DBMS

5705

K.Diwakar Reddy

Physics

5706

K.Gourisankar

DBMS

5707

K.Ramakrishna

OS

5708

K.Ramakrishna

History

5709

K.Ramesh

OS

5710

K.Ramesh

DBMS

5711

K.Sankar Reddy

DBMS

5712

K.Sivasankar

History

5713

L.Sudhakar

DBMS

5714

M.Nagaraju

Accounts

5715

M.Nagarjuna

Physics

5716

M.Raghavendra Reddy

DBMS

5717

O.Sivaprasad

OS

5718

P.Bashakar

Accounts

5719

P.Moulali

OS

5720

P.Nagaraju

Physics

5721

P.Rafi

OS

5722

P.Sreeramulu

Accounts

5723

P.Tirupalu

OS

5724

S.Anwar Basha

OS

5725

S.Baba

OS

5726

S.Gangaraju

OS

5727

S.Ibrahim

DBMS

5728

S.Jilani Basha

Accounts

5729

S.Mahammad Goush

OS

5730

T.Babaiah

History

5731

T.Balagangi Reddy

Accounts

5732

T.Sreenivasulu

DBMS

5733

Y.Vijayaram

OS

5734

N.jagan

Electronics

5735

K.Hari Babu

Chemistry

SQL> create table subject_rank25(subject_code number(4) primary key,subject_name varchar(15),faculty_code number(4),status varchar(18)); SUBJECT_CODE

SUBJECT_NAME

FACULTY_CODE

STATUS

9110

Accounts

5688

Offered

9111

DBMS

5681

Offered

9112

History

5683

Offered

9113

OS

5678

Offered

9114

Physics

5687

Offered

9115

Electronics

0

Offered

9116

Chemistry

0

Offered

97

Procedure SQL> create or replace procedure PRO25

is z1 subject_rank25.subject _code%type; fac subject_rank25.faculty_code%type; ctr number; cursor c1 is select faculty_code as fac from subject_rank25; begin open c1; loop fetch c1 into z1; if(fac=0) then update subject_rank25 set status='Not offered' where subject_code=z1; end if; exit when c1%notfound; end loop; close c1; end; PL/SQL procedure successfully completed SQL> select * from subject_rank25; Subject_code Subject_namefaculty_code staus 9115 Electronics

0

Offered

9116 Chemistry

0

Offered

27.Create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) Generate the queries to do the following a. Find the number of students who have enrolled for the subject ‘DBMS’. b. Find all those subjects which are not offered by any faculty member.

98

SQL> create table subject_rank(subject_code number(4),subject_name varchar(15),faculty_code number(4)); SUBJECT_CODE

SUBJECT_NAME

FACULTY_CODE

1

9113

OS

5678

2

9114

Physics

5679

3

9110

Accounts

5680

4

9111

DBMS

5681

5

9112

History

5683

6

9114

Physics

5687

7

9110

Accounts

5688

8

9111

DBMS

5689

9

9112

History

5690

10 9115

Electronics

0

11 9116

Chemistry

0


FACULTY_NAME

SPECIALIZATION

5678

Syed Nazia Begum

M.Tech

5679

M Suhasini

M.Sc

5680

S Zareena

M.com

5681

B Sujana Kumari

MCA

5682

K Balaramudu

MBA

5683

G Gopinath

MA

5684

M Rama Krishna

B.Sc

5685

B Ramesh

B.Com

5686

D.Anilkumar Reddy

M.Tech

5687

J.Haribabu

M.Sc

5688

S.Mahammad Goush

M.com

5689

L.Sudhakar

MCA

5690

O.Sivaprasad

BA

5691

G.Manesh

B.Tech

5692

M.Nagaraju

BE

SQL> create table student27(roll_no varchar(19),subject_opted varchar(15)); ROLL_NO

NAME

number(4)

SUBJECT_OPTED

11001

A.Nagedra

DBMS

11002

B.Adinarayana

History

11003

B.Ayyawaru

Physics

11004

B.Dastagiri

History

11005

B.Ramakrishna

Physics

99

primary

key,name

11006

B.Ramesh

Physics

11007

C.Jayaraju

Physics

11008

C.Rajashekar

Physics

11009

C.Subramanyam

DBMS

11010

C.Venkatasiva

Physics

11011

D.Anilkumar Reddy

History

11012

D.Udaykiran

History

11013

G.Ganganna

DBMS

11014

G.Gurappa

Accounts

11015

G.Manesh

Accounts

11016

G.Suresh

History

11017

G.Venkataramana

Accounts

11018

J.Haribabu

DBMS

11019

K.Diwakar Reddy

Physics

11020

K.Gourisankar

DBMS

11021

K.Ramakrishna

OS

11022

K.Ramakrishna

History

11023

K.Ramesh

OS

11024

K.Ramesh

DBMS

11025

K.Sankar Reddy

DBMS

11026

K.Sivasankar

History

11027

L.Sudhakar

DBMS

11028

M.Nagaraju

Accounts

11029

M.Nagarjuna

Physics

11030

M.Raghavendra Reddy

DBMS

11031

O.Sivaprasad

OS

11032

P.Bashakar

Accounts

11033

P.Moulali

OS

11034

P.Nagaraju

Physics

11035

P.Rafi

OS

11036

P.Sreeramulu

Accounts

11037

P.Tirupalu

OS

11038

S.Anwar Basha

OS

11039

S.Baba

OS

11040

S.Gangaraju

OS

11041

S.Ibrahim

DBMS

11042

S.Jilani Basha

Accounts

11043

S.Mahammad Goush

OS

11044

T.Babaiah

History

11045

T.Balagangi Reddy

Accounts

11046

T.Sreenivasulu

DBMS

11047

Y.Vijayaram

OS

11048

N.jagan

Electronics

11049

K.Hari Babu

Chemistry

100

Queries (a) select count(roll_no) from student27 where subject_opted='DBMS'; COUNT(ROLL_NO) 11

(b) select subject_name from subject_rank where faculty_code=0; SUBJECT_NAME Electronics Chemistry

28.Create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) Generate the queries to do the following Find the number of students who have enrolled for the subject ‘DBMS’. Find all those subjects which are offered by more than one faculty member.

Creating the Tables SQL> create table subject_rank(subject_code number(4),subject_name varchar(15),faculty_code number(4)); SUBJECT_CODE

SUBJECT_NAME

101

FACULTY_CODE

1

9113

OS

5678

2

9114

Physics

5679

3

9110

Accounts

5680

4

9111

DBMS

5681

5

9112

History

5683

6

9114

Physics

5687

7

9110

Accounts

5688

8

9111

DBMS

5689

9

9112

History

5690

10 9115

Electronics

0

11 9116

Chemistry

0


FACULTY_NAME

SPECIALIZATION

5678

Syed Nazia Begum

M.Tech

5679

M Suhasini

M.Sc

5680

S Zareena

M.com

5681

B Sujana Kumari

MCA

5682

K Balaramudu

MBA

5683

G Gopinath

MA

5684

M Rama Krishna

B.Sc

5685

B Ramesh

B.Com

5686

D.Anilkumar Reddy

M.Tech

5687

J.Haribabu

M.Sc

5688

S.Mahammad Goush

M.com

5689

L.Sudhakar

MCA

5690

O.Sivaprasad

BA

5691

G.Manesh

B.Tech

5692

M.Nagaraju

BE

SQL> create table student27(roll_no varchar(19),subject_opted varchar(15)); ROLL_NO

NAME

number(4)

SUBJECT_OPTED

11001

A.Nagedra

DBMS

11002

B.Adinarayana

History

11003

B.Ayyawaru

Physics

11004

B.Dastagiri

History

11005

B.Ramakrishna

Physics

11006

B.Ramesh

Physics

102

primary

key,name

11007

C.Jayaraju

Physics

11008

C.Rajashekar

Physics

11009

C.Subramanyam

DBMS

11010

C.Venkatasiva

Physics

11011

D.Anilkumar Reddy

History

11012

D.Udaykiran

History

11013

G.Ganganna

DBMS

11014

G.Gurappa

Accounts

11015

G.Manesh

Accounts

11016

G.Suresh

History

11017

G.Venkataramana

Accounts

11018

J.Haribabu

DBMS

11019

K.Diwakar Reddy

Physics

11020

K.Gourisankar

DBMS

11021

K.Ramakrishna

OS

11022

K.Ramakrishna

History

11023

K.Ramesh

OS

11024

K.Ramesh

DBMS

11025

K.Sankar Reddy

DBMS

11026

K.Sivasankar

History

11027

L.Sudhakar

DBMS

11028

M.Nagaraju

Accounts

11029

M.Nagarjuna

Physics

11030

M.Raghavendra Reddy

DBMS

11031

O.Sivaprasad

OS

11032

P.Bashakar

Accounts

11033

P.Moulali

OS

11034

P.Nagaraju

Physics

11035

P.Rafi

OS

11036

P.Sreeramulu

Accounts

11037

P.Tirupalu

OS

11038

S.Anwar Basha

OS

11039

S.Baba

OS

11040

S.Gangaraju

OS

11041

S.Ibrahim

DBMS

11042

S.Jilani Basha

Accounts

11043

S.Mahammad Goush

OS

11044

T.Babaiah

History

11045

T.Balagangi Reddy

Accounts

11046

T.Sreenivasulu

DBMS

11047

Y.Vijayaram

OS

11048

N.jagan

Electronics

11049

K.Hari Babu

Chemistry

Queries

103

(i)SQL> select count(roll_no) from student27 where subject_opted='DBMS'; COUNT(ROLL_NO) 11

(ii)SQL> select subject_code,subject_name,count(subject_code) from subject_rank group by subject_code having count(subject_code)>1; SUBJECT_CODE

SUBJECT_NAME

COUNT

9114

Physics

2

9110

Accounts

2

9111

DBMS

2

9112

History

2

29.Create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) Generate the queries to do the following (i)Find the number of students who have enrolled for the subject ‘OS’. (ii)Find all those students who have opted for more than 5 subjects. SQL> create table subject_rank(subject_code number(4),subject_name varchar(15),faculty_code number(4)); Select * from subject_rank; SUBJECT_CODE

SUBJECT_NAME

FACULTY_CODE

1

9113

OS

5678

2

9114

Physics

5679

3

9110

Accounts

5680

104

4

9111

DBMS

5681

5

9112

History

5683

6

9114

Physics

5687

7

9110

Accounts

5688

8

9111

DBMS

5689

9

9112

History

5690

10

9115

Electronics

0

11

9116

Chemistry

0


FACULTY_NAME

SPECIALIZATION

5678

Syed Nazia Begum

M.Tech

5679

M Suhasini

M.Sc

5680

S Zareena

M.com

5681

B Sujana Kumari

MCA

5682

K Balaramudu

MBA

5683

G Gopinath

MA

5684

M Rama Krishna

B.Sc

5685

B Ramesh

B.Com

5686

D.Anilkumar Reddy

M.Tech

5687

J.Haribabu

M.Sc

5688

S.Mahammad Goush

M.com

5689

L.Sudhakar

MCA

5690

O.Sivaprasad

BA

5691

G.Manesh

B.Tech

5692

M.Nagaraju

BE

SQL> create table student27(roll_no varchar(19),subject_opted varchar(15));

number(4)

primary

select * from student27; ROLL_NO

NAME

SUBJECT_OPTED

11001

A.Nagedra

DBMS

11002

B.Adinarayana

History

11003

B.Ayyawaru

Physics

11004

B.Dastagiri

History

11005

B.Ramakrishna

Physics

11006

B.Ramesh

Physics

11007

C.Jayaraju

Physics

105

key,name

11008

C.Rajashekar

Physics

11009

C.Subramanyam

DBMS

11010

C.Venkatasiva

Physics

11011

D.Anilkumar Reddy

History

11012

D.Udaykiran

History

11013

G.Ganganna

DBMS

11014

G.Gurappa

Accounts

11015

G.Manesh

Accounts

11016

G.Suresh

History

11017

G.Venkataramana

Accounts

11018

J.Haribabu

DBMS

11019

K.Diwakar Reddy

Physics

11020

K.Gourisankar

DBMS

11021

K.Ramakrishna

OS

11022

K.Ramakrishna

History

11023

K.Ramesh

OS

11024

K.Ramesh

DBMS

11025

K.Sankar Reddy

DBMS

11026

K.Sivasankar

History

11027

L.Sudhakar

DBMS

11028

M.Nagaraju

Accounts

11029

M.Nagarjuna

Physics

11030

M.Raghavendra Reddy

DBMS

11031

O.Sivaprasad

OS

11032

P.Bashakar

Accounts

11033

P.Moulali

OS

11034

P.Nagaraju

Physics

11035

P.Rafi

OS

11036

P.Sreeramulu

Accounts

11037

P.Tirupalu

OS

11038

S.Anwar Basha

OS

11039

S.Baba

OS

11040

S.Gangaraju

OS

11041

S.Ibrahim

DBMS

11042

S.Jilani Basha

Accounts

11043

S.Mahammad Goush

OS

11044

T.Babaiah

History

11045

T.Balagangi Reddy

Accounts

11046

T.Sreenivasulu

DBMS

11047

Y.Vijayaram

OS

11048

N.jagan

Electronics

11049

K.Hari Babu

Chemistry

Queries

106

(i)select count(subject_opted) from student27 group by subject_opted having subject_opted =’OS’;

COUNT(SUBJECT_OPTED) 10

(ii)select rollno,student_name,count(rollno) from subject_opted group by rollno having count(rollno)>5; COUNT(ROLLNO) 0

30. Create the following tables Student(roll_no,name,subject_opted) Subject_rank(subject_code,subject_name,faculty_code) Faculty(faculty_code, faculty_name, specialization) Generate the queries to do the following a. Find the number of students who have not enrolled for the subject ‘DBMS’. b. Find all those subjects which are offered by more than one faculty member.

Creating Tables SQL>create table subject_rank(subject_code number(4),subject_name varchar(15),faculty_code number(4)); Select * from subject_rank; SUBJECT_CODE

SUBJECT_NAME

FACULTY_CODE

1

9113

OS

5678

2

9114

Physics

5679

3

9110

Accounts

5680

107

4

9111

DBMS

5681

5

9112

History

5683

6

9114

Physics

5687

7

9110

Accounts

5688

8

9111

DBMS

5689

9

9112

History

5690

10

9115

Electronics

0

11

9116

Chemistry

0


FACULTY_NAME

SPECIALIZATION

5678

Syed Nazia Begum

M.Tech

5679

M Suhasini

M.Sc

5680

S Zareena

M.com

5681

B Sujana Kumari

MCA

5682

K Balaramudu

MBA

5683

G Gopinath

MA

5684

M Rama Krishna

B.Sc

5685

B Ramesh

B.Com

5686

D.Anilkumar Reddy

M.Tech

5687

J.Haribabu

M.Sc

5688

S.Mahammad Goush

M.com

5689

L.Sudhakar

MCA

5690

O.Sivaprasad

BA

5691

G.Manesh

B.Tech

5692

M.Nagaraju

BE


NAME

number(4)

primary

SUBJECT_OPTED

11001

A.Nagedra

DBMS

11002

B.Adinarayana

History

11003

B.Ayyawaru

Physics

11004

B.Dastagiri

History

11005

B.Ramakrishna

Physics

11006

B.Ramesh

Physics

11007

C.Jayaraju

Physics

11008

C.Rajashekar

Physics

11009

C.Subramanyam

DBMS

11010

C.Venkatasiva

Physics

108

key,name

11011

D.Anilkumar Reddy

History

11012

D.Udaykiran

History

11013

G.Ganganna

DBMS

11014

G.Gurappa

Accounts

11015

G.Manesh

Accounts

11016

G.Suresh

History

11017

G.Venkataramana

Accounts

11018

J.Haribabu

DBMS

11019

K.Diwakar Reddy

Physics

11020

K.Gourisankar

DBMS

11021

K.Ramakrishna

OS

11022

K.Ramakrishna

History

11023

K.Ramesh

OS

11024

K.Ramesh

DBMS

Queries (i)SQL> select count(roll_no) from student27 having subject_opted!='DBMS'; COUNT(ROLL_NO) 38

(ii)SQL> select subject_code,subject_name,count(subject_code) from subject_rank group by subject_code having count(subject_code)>1; SUBJECT_CODE

SUBJECT_NAME

COUNT

9114

Physics

2

9110

Accounts

2

9111

DBMS

2

9112

History

2

109

Steps For Creating A Data Source STEPS FOR CREATING DATA SOURCE Step1: Go to control panel select administrative tools->Data Source (ODBC) icon . Step2: Then a dialogue box appears naming ODBC Data Source then click on Add and then select the option Microsoft ODBC for Oracle and click finish then a dialogue appear naming Microsoft ODBC for Oracle SetUp Step 3: Now enter the values as Data Source Name as nmca10 User Name as scott/tiger(password) (is SQL is opened with host string is given as oracle then scott/tiger@oracle) Then click ok Step4: Then that data source name is entered into the ODBC Data Source Administrator. In this way we connect the data source to database

110

:

111

Steps To Create A Form Steps To Create A Form : Step1: Open Microsoft Visual Basic 6.0 Step2: Open standard EXE Step 3: It generates an empty form as shown. Step 3: Select the required no of labels by selecting icon Label and enter the label names in the properties table in Caption row Step 4: Select the required no of text fields by selecting the icon Text Step 5: create an adodc icon by selecting Project ->Components then a dialogue box appears then a dialogue appears Step 6: Tick on Microsoft ADO Data Control 6.0 (OLEDB) the click apply and then ok. Step 7: choose the icon adodc create the adodc1 box as shown Step 8: Select the required command fields by selecting the icon Command Button and And enter the command names like add,save,delete in the properties table in Caption row. Step 8: Now select the adodc1 box and enter the command type as table in properties table 112

Step 9: Now select the command string the a dialogue box appears in the box select the use ODBC Data source Name and enter the data source name i.e nmca10 as shown. Step 10: Now select the Resource type in properties table then a dialodue box appears select the table for which the form being created as shown an then select ok. Step 11: Now select the Data Source as adodc1 and Data Field as necessary (Eg: Course_id); Step 12: In order to write code for the command buttons double click the command button then a window appears the write the code as follows (i) for add: Private Sub Command1_Click() Adodc1.Recordset.AddNew End Sub (ii) for save: Private Sub Command1_Click() Adodc1.Recordset.update End Sub (iii) for delete: Private Sub Command1_Click() Adodc1.Recordset.update End Sub Step 13: In order to give Validation checks select required text box on which we have to define validate and same as step11and write the code as follows. Fro Eg to text 3 Private Sub Text3_Change() If (Text3.Text) = 0 Then MsgBox ("fee cant be zero") Text3.SetFocus End If End but the following must be used Step 14: In order to execute the form click start button the menu bar and the form is created as follows: Step 15: For example we have generated the for and have add the values as follows Course_id as 1111 Name as Commerce Fee as 15000 Duration as 3 Step 16: In order to see the added recored go to SQL promt and gine the following command SQL> select * from course; COURSE -----------1111 123 456 789 012

NAME -------Commerce M.C.A B.Tech M.B.A Bio-Tech

FEE ----15000 25400 36000 20000 40000

DURATION --------------3 3 4 2 3

113

156

Food-Tech

10000

3

6 rows selected.

Output of Step 3:

114

Output of Step 5:

Output of Step 6:

115

Output of Step 7:

Output of Step 9:

116

Output of step10:

Output of step11:

117

Output of step12 & 13:

Output of step 14:

118

Steps To Generate A Report STEPS TO GENERATE REPORT 119

Step1: Create form on the table for which a report has to generated. Step2: Now select option projects->add from->VB Wizard form. Step3: a dialogue box appears naming Data Form wizard-Introduction click on Next. Step4: A dialogue box appears naming Data Form wizard-Database Type select the option remote(ODBC) and click Next. Step5:A dialogue box appears naming Data Form wizard-Connection Information and enter the values for ODBC Connect Data as DSN : nmca10(name of the data source) UID : scott PWD :tiger Then click on Next. Step6: A dialogue box appears naming Data Form wizard-Forms select the Form Layout as Grid(database) and Binding code as AOD Data control if we want to select all the rows or if we want to retrieve data based upon a condition then select the ption as AOD Code and click on Next. Step 7: A dialogue box appears naming Data Form wizard-Record Source then select the record source as table name (eg item22) then it displays available fields select all the fields if we want to retrive entire data if not select those columns on which a condition should be specified. Step8: A dialogue box appears naming Data Form wizard-Control Section delete all the available controls and click on Next Step9: A dialogue box appears naming Data Form wizard-Finished then select finish. Step10: Then report is generated Step11: In order to give a condition double click the report and insert the SQL query and then run the report.

120

Step2:

: Step3:

121

Step4:

Step5:

Step6:

122

Step7:

123

Step8:

Step9:

Step10: 124

125

49469044-Dbms-Record-1

Recommend Documents