DBMS LAB Q & A

1. Create a table to represent sb-account of a bank consisting of account-no, customer-name,

balance-amount.
Write a PL/SQL block to implement deposit and withdraw. Withdraws should
not be allowed if the balance goes below Rs.1000.

TABLE CREATION:

SQL> CREATE table sb_acc(AC_NO number(11),C_NAME varchar2(30),BAL number,PRIMARY

KEY(ac_no));

Table created.

VALUE INSERTION:

SQL> INSERT into sb_acc VALUES('&ac_no','&c_name','&balance');

Enter value for ac_no: 11111
Enter value for c_name: SMITH
Enter value for balance: 20000
old 1: INSERT into sb_acc VALUES('&ac_no','&c_name','&balance')
new 1: INSERT into sb_acc VALUES('0000011111','SMITH','20000')

1 row created.

TABLE VALUES:

SQL> SELECT *FROM sb_acc;

AC_NO C_NAME BAL

---------- ------------------------------ ----------
11111 SMITH 20000
11112 ALEX 900
11113 KING 15000
11114 ADAMS 1000
11115 FORD 25000

PL/SQL block with name demo1:

DECLARE
acno SB_ACC.ac_no%type:='&acno';
amt SB_ACC.bal%type:='&amount';
balance SB_ACC.bal%type;
ch char:='&Transaction'; --D:Deposit W:Withdraw

BEGIN
SELECT bal into balance from SB_ACC WHERE ac_no=acno;

ch:=UPPER(ch);
CASE

WHEN ch='D' then

balance:=balance+amt;
 dbms_output.put_line('TRANSACTION SUCCESSFULLY COMPLETED');
dbms_output.put_line('YOUR CURRENT BALANCE : '||balance);

WHEN ch='W' then

if(balance>1000) then
balance:=balance-amt;
dbms_output.put_line('TRANSACTION SUCCESSFULLY COMPLETED');
dbms_output.put_line('YOUR CURRENT BALANCE : '||balance);
else
dbms_output.put_line('AMOUNT NOT AVAILABLE');
end if;

ELSE
dbms_output.put_line('WRONG CHOICE');

END CASE;

UPDATE SB_ACC set bal=balance WHERE ac_no=acno;

COMMIT;
END;
/
EXECUTION:
SQL> @demo1
Enter value for acno: 11111
old 2: acno sb_acc.ac_no%type:='&acno';
new 2: acno sb_acc.ac_no%type:='11111';
Enter value for amount: 1000
old 3: amt sb_acc.bal%type:='&amount';
new 3: amt sb_acc.bal%type:='1000';
Enter value for transaction: d
old 5: ch char:='&Transaction';
new 5: ch char:='d';
TRANSACTION SUCCESSFULLY COMPLETED
YOUR CURRENT BALANCE : 22000

PL/SQL procedure successfully completed.

2. Create The following two tables :

College-info
Faculty-info
College-info consists of fields : college-code, college-name, address
Faculty-info consists of fields : college-code, faculty-code, faculty-name,
qualification, experience-in-no-of-years, address.
The field college-code is foreign key.
(a) Design a form to accept the data from the user.
(b) Generate queries to do the following :
(i) List all those faculty members whose experience is greater than or equal to 10 years and
have M. Tech degree.
(ii) List all those faculty members, who have at least 10 years of experience but do not have M.
Tech degree.
 TABLE CREATION:

SQL> CREATE
table COLLEGE_INFO(C_CODE number(4),C_NAME varchar2(30),ADDRESS varchar2(30),PRIMA
RY KEY(c_code));

Table created.
SQL> CREATE table FACULTY_INFO(C_CODE number(4)
REFERENCES COLLEGE_INFO(c_code),F_CODEnumber(4) PRIMARY
KEY,F_NAME varchar2(30),QUAL varchar2(10),EXP number(2),ADDRESSvarchar2(30));

Table created.
VALUE INSERTION:

SQL> INSERT into college_info VALUES('&c_code','&c_name','&address');

Enter value for c_code: 1111
Enter value for c_name: NIZAM
Enter value for address: HYDERABAD
old 1: INSERT into college_info VALUES('&c_code','&c_name','&address')
new 1: INSERT into college_info VALUES('1111','NIZAM','HYDERABAD')

1 row created.
SQL> INSERT into faculty_info VALUES('&c_code','&f_code','&f_name','&qual','&exp','&address');
Enter value for c_code: 1111
Enter value for f_code: 101
Enter value for f_name: KING
Enter value for qual: MCA
Enter value for exp: 5
Enter value for address: HYD
old 1: INSERT into faculty_info VALUES('&c_code','&f_code','&f_name','&qual','&exp','&address')
new 1: INSERT into faculty_info VALUES('1111','101','KING','MCA','5','HYD')
1 row created.

TABLE VALUES:
SQL> SELECT * FROM college_info;
C_CODE C_NAME ADDRESS
--------- ------------------------------ ------------------------------
1111 NIZAM HYDERABAD
1112 CBIT SEC
2 rows selected.

SQL> SELECT *FROM faculty_info;

C_CODE F_CODE F_NAME QUAL EXP ADDRESS

---------- ---------- ---------------------- ---------- ------------------------------
1111 101 KING MCA 5 HYD
1112 1005 SMITH MTECH 10 SEC
1111 102 ALEX BTECH 12 SEC
1111 103 WARD MCA 15 HYD
1112 1004 JAMES MTECH 11 HYD
1112 1003 JONES BTECH 3 HYD
1111 104 MILLER MTECH 13 SEC
7 rows selected.
QUERIES:
(I)
SQL> SELECT * FROM faculty_info WHERE exp>=10 AND qual='MTECH';

C_CODE F_CODE F_NAME QUAL EXP ADDRESS

---------- ------------------------- ------- -----------------------------------------------
1112 1005 SMITH MTECH 10 SEC
1112 1004 JAMES MTECH 11 HYD
1111 104 MILLER MTECH 13 SEC
3 rows selected.
(II)
SQL> SELECT * FROM faculty_info WHERE exp>=10 AND qual<>'MTECH';

C_CODE F_CODE F_NAME QUAL EXP ADDRESS

---------- ---------- --------------- ------- ---------- -----------------------
1111 102 ALEX BTECH 12 SEC
1111 103 WARD MCA 15 HYD

2 rows selected.

3. Create the following tables for Library Information System :

Book : (accession-no, title, publisher, author,year,status)
Status could be issued, present in the library, sent 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.
TABLE CREATION:

SQL> CREATE table BOOK(AC_NO number PRIMARY

KEY,TITLE varchar2(10),PUBLISHER varchar2(15),AUTHORvarchar2(15),YEAR number(4),STATU
S varchar2(17));

Table created.

TRIGGER:
CREATE OR REPLACE trigger trg3 BEFORE insert on book FOR EACH ROW
BEGIN
if to_char(sysdate,'yyyy')-:new.year>=20 then
:new.status:='cannot be issued';
end if;
END;
/
INSERTION:
SQL> INSERT INTO BOOK VALUES('&ac_no','&title','&publisher','&author','&year','&status');
Enter value for ac_no: 113
Enter value for title: C
Enter value for publisher: SMITH
Enter value for author: CLARK
Enter value for year: 1980
Enter value for status:
old 1: INSERT INTO BOOK VALUES('&ac_no','&title','&publisher','&author','&year','&status')
new 1: INSERT INTO BOOK11 VALUES('113','C','SMITH','CLARK','1980','')
1 row created.
TABLE VALUES:
SQL> SELECT * FROM book;

AC_NO TITLE PUBLISHER AUTHOR YEAR STATUS

---------- ---------- ---------- ---------- ----------------- -----------------------
113 C SMITH CLARK 1980 cannot be issued
111 JAVA ALEX KING 1990
112 C++ MILLER JONES 1988 cannot be issued
4. Create the following tables for Library Information System :
Book(accession-no, title, publisher, author, status, date-of-purchase)
Status could be issued, present in the library, sent for binding, and account be
issued.
(a) Create a form to accept the data from the user 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 books which are new arrivals. The books which are acquired during the last 6
months are categorized as new arrivals.
(ii) List all those books that cannot be issued and purchased 20 years ago.

TABLE CREATION:
SQL> CREATE table BOOK(AC_NO number PRIMARY
KEY,TITLE varchar2(10),PUBLISHER varchar2(15),AUTHORvarchar2(15),STATUS varchar2(17) C
HECK(status in('issued','cannot be issued')),d_o_purchase date);
Table created.

VALUE INSERTION:

SQL> INSERT into book

VALUES('&ac_no','&title','&publisher','&author',LOWER('&status'),'&d_o_purchase');
Enter value for ac_no: 111
Enter value for title: C WITH DS
Enter value for publisher: ALEX
Enter value for author: SMITH
Enter value for status: ISSUED
Enter value for d_o_purchase: 01-MAR-2009
old 1: INSERT into book
VALUES('&ac_no','&title','&publisher','&author',LOWER('&status'),'&d_o_purchase')
new 1: INSERT into book VALUES('111','C WITH DS','ALEX','SMITH',LOWER('ISSUED'),'01-MAR-
2009')

1 row created.

TABLE VALUES:

SQL> SELECT * FROM book;

AC_NO TITLE PUBLISHER AUTHOR STATUS D_O_PURCH

-------------------- ------------------------------ ---------------------------------------------------
111 C WITH DS ALEX SMITH issued 01-MAR-09
112 C++ KING CLERK cannot be issued 15-JAN-85
113 JAVA FORD MILLER cannot be issued 23-SEP-88
114 ORACLE ADAMS JAMES issued 28-MAR-91
115 .NET SCOTT TURNER cannot be issued 17-JUL-09
QUERIES:
(I)
SQL> SELECT * from book1 WHERE 6>=MONTHS_BETWEEN(SYSDATE,d_o_purchase);

AC_NO TITLE PUBLISHER AUTHOR STATUS D_O_PURCH

-------------------------------------------------- ----------------------------------------------------
115 .NET SCOTT TURNER cannot be issued 17-JUL-09

1 row selected.
(II)
SQL> SELECT * from book1 WHERE status='cannot be issued' AND
20<=TRUNC((MONTHS_BETWEEN(SYSDATE,d_o_purchase))/12);

AC_NO TITLE PUBLISHER AUTHOR STATUS D_O_PURCH

-------------------- ------------------------------ ------------ --------------------------------------
112 C++ KING CLERK cannot be issued 15-JAN-85
113 JAVA FORD MILLER cannot be issued 23-SEP-88
2 rows selected.

5. Create the following tables :

Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate 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 greater than that of MCA course.

TABLE CREATION:

SQL> CREATE table COURSE(C_ID number(3) PRIMARY

KEY,NAME varchar2(15),FEE number(5),DURATIONnumber(2));

Table created.

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),D_O_BIRTH date,C_IDnumber(3) REFERENCES COURSE(c_id));

Table created.

VALUE INSERTION:

SQL> INSERT into Course VALUES('&c_id','&name','&fee','&duration');

Enter value for c_id: 11
Enter value for name: MCA
Enter value for fee: 27500
Enter value for duration: 3
old 1: INSERT into Course VALUES('&c_id','&name','&fee','&duration')
new 1: INSERT into Course VALUES('11','MCA','27500','3')

1 row created.
SQL> INSERT into student values('&r_no','&name','&d_o_birth','&c_id');
Enter value for r_no: 1
Enter value for name: KING
Enter value for d_o_birth: 19-JAN-88
Enter value for c_id: 11
old 1: INSERT into student values('&r_no','&name','&d_o_birth','&c_id')
new 1: INSERT into student values('1','KING','19-JAN-88','11')

1 row created.

TABLE VALUES:

SQL> SELECT * FROM course;

C_ID NAME FEE DURATION

----------------------------------- ----------------------
11 MCA 27500 3
12 BSC(COMPS) 11000 3
13 BTECH 31500 4

SQL> SELECT * FROM student;

R_NO NAME D_O_BIRTH C_ID

---------- --------------- --------- -----------------------
1 KING 19-JAN-88 11
2 FORD 25-MAR-93 13
3 LANGER 01-JAN-92 11
4 PONTING 07-APR-75 11
5 SYMONDS 28-JUL-91 12

QUERIES:

(I)
SQL> SELECT *from student WHERE 18<=TRUNC(MONTHS_BETWEEN(SYSDATE,d_o_birth)/12)
AND c_id=(SELECT c_id from course WHERE name='MCA');

R_NO NAME D_O_BIRTH C_ID

---------- --------------- --------- ------------- ----------
1 KING 19-JAN-88 11
4 PONTING 07-APR-75 11

(II)
SQL> SELECT * from course WHERE fee>(SELECT fee from course WHERE name='MCA');

C_ID NAME FEE DURATION

---------- --------------- ---------- -----------------------
13 BTECH 31500 4
 6. Create the following table :
Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) Find the number of students who have enrolled for the subject "DBMS".
(ii) Find all those faculty members who have not offered any subject.

TABLE CREATION:

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),S_OPTED varchar2(10));

Table created.

SQL> CREATE table FACULTY(F_CODE number(2) PRIMARY

KEY,F_NAME varchar2(15),SPEC varchar2(10));

Table created.

SQL> CREATE table SUB_RANK(S_CODE number(2) PRIMARY

KEY,S_NAME varchar2(13),F_CODE number(2) REFERENCES FACULTY(f_code));

Table created.

VALUE INSERTION:
SQL> INSERT into student VALUES('&r_no','&name','&s_opted');
Enter value for r_no: 1
Enter value for name: CLARK
Enter value for s_opted: DBMS
old 1: INSERT into student VALUES('&r_no','&name','&s_opted')
new 1: INSERT into student VALUES('1','CLARK','DBMS')

1 row created.

SQL> INSERT into Faculty values('&f_code','&f_name','&specialization');

Enter value for f_code: 11
Enter value for f_name: ALEX
Enter value for specialization: JAVA
old 1: INSERT into Faculty values('&f_code','&f_name','&specialization')
new 1: INSERT into Faculty values('11','ALEX','JAVA')

1 row created.
TABLE VALUES:

SQL> SELECT * FROM student;

R_NO NAME S_OPTED
---------- --------------- ---------- --------------------
1 CLARK DBMS
2 SMITH JAVA
3 SCOTT DBMS
SQL> SELECT * FROM Faculty;

F_CODE F_NAME SPEC

---------- --------------- ------ ---------
11 ALEX JAVA
12 LAKE DBMS
13 PONTING C
14 SYMANDS C++
15 MILLER ORACLE

SQL> SELECT * FROM sub_rank;

S_CODE S_NAME F_CODE

---------- ------------- -------------------
21 DBMS 12
22 JAVA 11
23 C++ 14

QUERIES:

(I)
SQL> SELECT count(*) NO_OF_STUDENTS from student WHERE s_opted='DBMS';

NO_OF_STUDENTS
--------------
2
(II)
SQL> SELECT * from faculty WHERE f_code NOT IN(SELECT f_code from sub_rank);

F_CODE F_NAME SPEC

---------- ------------- ------------------
13 PONTING C
15 MILLER ORACLE

7. 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) 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.

TABLE CREATION:

SQL> CREATE table ITEM(I_CODE number PRIMARY

KEY,I_NAME varchar2(10),QTY number,REORDER number);
Table created.

SQL> CREATE table SUPPLIER(S_CODE number PRIMARY

KEY,S_NAME varchar2(15),ADDRESS varchar2(15));
Table created.
SQL> CREATE table CAN_SUPPLY(S_CODE number
REFERENCES SUPPLIER(s_code),I_CODE number REFERENCES item(i_code));

Table created.

VALUE INSERTION:

SQL> INSERT into item VALUES('&i_code','&i_name','&qty_in_stock','&reorder_level');

Enter value for i_code: 11
Enter value for i_name: BOOK
Enter value for qty_in_stock: 55
Enter value for reorder_level: 10
old 1: INSERT into item VALUES('&i_code','&i_name','&qty_in_stock','&reorder_level')
new 1: INSERT into item VALUES('11','BOOK','55','10')

1 row created.
SQL> INSERT into supplier VALUES('&s_code','&s_name','&address');
Enter value for s_code: 111
Enter value for s_name: SMITH
Enter value for address: SEC
old 1: INSERT into supplier VALUES('&s_code','&s_name','&address')
new 1: INSERT into supplier VALUES('111','SMITH','SEC')

1 row created.
SQL> INSERT into can_supply VALUES('&s_code','&i_code');
Enter value for s_code: 111
Enter value for i_code: 13
old 1: INSERT into can_supply VALUES('&s_code','&i_code')
new 1: INSERT into can_supply VALUES('111','13')

1 row created.

TABLE VALUES:

SQL> SELECT * FROM ITEM;

I_COD E I_NAME QTY REORDER

---------- ---------- ---------- -------------------------
11 BOOK 55 10
12 PEN 100 12
13 PENCIL 250 15

SQL> SELECT * FROM SUPPLIER;

S_CODE S_NAME ADDRESS

---------- --------------- -----------------------
111 SMITH SEC
112 ALEX HYD
113 PONTING HYD
114 CLARK SEC
SQL> SELECT * FROM can_supply;

S_CODE I_CODE
---------- --------------------
111 13
112 11
113 11

QUERIES:
(I)

SQL> SELECT distinct S.* from supplier S,item I,can_supply C WHERE C.i_code=(SELECT i_code from
item WHEREi_name=UPPER('&i_name')) and C.s_code=S.s_code;

(OR)

SQL> SELECT * from supplier WHERE s_code IN (SELECT s_code from can_supply WHERE
i_code=(SELECT i_code from item WHERE i_name=UPPER('&i_name')));
Enter value for i_name: BOOK
old 1: SELECT * from supplier WHERE s_code IN (SELECT s_code from can_supply WHERE
i_code=(SELECT i_code from item WHERE i_name=UPPER('&i_name')))
new 1: SELECT * from supplier WHERE s_code IN (SELECT s_code from can_supply WHERE
i_code=(SELECT i_code from item WHERE i_name=UPPER('BOOK')))

S_CODE S_NAME ADDRESS

---------- --------------- -------------------------
113 PONTING HYD
112 ALEX HYD
(II)

SQL> SELECT distinct I.* from item I,supplier S,can_supply C WHERE C.s_code=(SELECT S.s_code from
supplier S WHERE S.s_name=upper('&s_name')) and not C.i_code=I.i_code;

(OR)

SQL> SELECT i_code,i_name from item WHERE i_code in(SELECT i_code from can_supply WHERE
s_code in(SELECT s_code from supplier WHERE s_name=UPPER('&s_name')));
Enter value for s_name: alex
old 1: SELECT i_code,i_name from item WHERE i_code in(SELECT i_code from can_supply WHERE
s_code in(SELECT s_code from supplier WHERE s_name=UPPER('&s_name')))
new 1: SELECT i_code,i_name from item WHERE i_code in(SELECT i_code from can_supply WHERE
s_code in(SELECT s_code from supplier WHERE s_name=UPPER('alex')))

I_CODE I_NAME QTY REORDER

---------- ---------- ---------- ------------------------------
12 PEN 100 12
13 PENCIL 250 15
 8. Create the following tables:
Student (roll-no, name, 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) 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 come from Andhra Pradesh state and belong to
given category who have secured a rank above 100.

TABLE CREATION:
SQL> CREATE table STUDENT2(R_NO number PRIMARY
KEY,NAME varchar2(15),CATEGORY varchar2(4),DISTvarchar2(10),STATE varchar2(2));

Table created.

SQL> CREATE table STD_RANK(R_NO number

REFERENCES STUDENT2(r_no),MARKS number,RANK number PRIMARY KEY);

Table created.

VALUE INSERTION:

SQL> INSERT into student2 values('&r_no','&name','&category','&dist','&state');

Enter value for r_no: 1
Enter value for name: RAMU
Enter value for category: BC-D
Enter value for dist: ADB
Enter value for state: AP
old 1: INSERT into student2 values('&r_no','&name','&category','&dist','&state')
new 1: INSERT into student2 values('1','RAMU','BC-D','ADB','AP')

1 row created.

SQL> INSERT into std_rank values('&r_no','&marks','&rank');

Enter value for r_no: 1
Enter value for marks: 90
Enter value for rank: 1000
old 1: INSERT into std_rank values('&r_no','&marks','&rank')
new 1: INSERT into std_rank values('1','90','1000')

1 row created.
TABLE VALUES:
SQL> SELECT * from student2;
R_NO NAME CATE DIST ST
---------- --------------- ---- ---------- -----------
1 RAMU BC-D ADB AP
2 BHEEM ST DGF TM
3 SOMU OC HYD AP
4 REETA SC ABC TM
5 REENA BC-C NLGD AP
SQL> SELECT * from std_rank;

R_NO MARKS RANK

---------- ---------- --------------------
1 90 1000
2 85 900
3 99 50
4 98 45
5 84 950
QUERIES:
(I)
SQL> SELECT distinct S.* from student2 S,std_rank R WHERE S.state='TM' AND R.rank>100;
(OR)
SQL> SELECT * from student2 WHERE state='TM' and r_no in (SELECT r_no from std_rank WHERE
rank>100);

R_NO NAME CATE DIST ST

---------- --------------- ---- ---------- --------------------
2 BHEEM ST DGF TM
(II)
SQL> SELECT distinct S.* from student2 S,std_rank R WHERE S.state='AP' AND R.rank>100 AND
S.category=UPPER('&category');
(OR)
SQL> SELECT * from student2 WHERE state='AP' and category=UPPER('&category') and r_no in(SELECT
r_no from std_rank WHERE rank>100);
Enter value for category: bc-d
old 1: SELECT * from student2 WHERE state='AP' and category=UPPER('&category') and r_no
in(SELECT r_no from std_rank WHERE rank>100)
new 1: SELECT * from student2 WHERE state='AP' and category=UPPER('bc-d') and r_no in(SELECT
r_no from std_rank WHERE rank>100)

R_NO NAME CATE DIST ST

---------- --------------- ---- ---------- -------------
1 RAMU BC-D ADB AP
9. Create the following tables :
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.

TABLE CREATION:

SQL> CREATE table BRANCH(B_ID number PRIMARY KEY,B_NAME varchar2(15),B_CITY varchar2(10));

Table created.

SQL> CREATE table CUSTOMER(C_ID number PRIMARY

KEY,C_NAME varchar2(10),C_CITY varchar2(10),B_IDnumber REFERENCES BRANCH(b_id));
Table created.
VALUE INSERTION:
SQL> INSERT into branch VALUES('&b_id','&b_name','&b_city');
Enter value for b_id: 123
Enter value for b_name: SBH
Enter value for b_city: HYD
old 1: INSERT into branch VALUES('&b_id','&b_name','&b_city')
new 1: INSERT into branch VALUES('123','SBH','HYD')
1 row created.

SQL> INSERT into customer VALUES('&c_id','&c_name','&c_city','&b_id');

Enter value for c_id: 1111
Enter value for c_name: MILLER
Enter value for c_city: HYD
Enter value for b_id: 123
old 1: INSERT into customer VALUES('&c_id','&c_name','&c_city','&b_id')
new 1: INSERT into customer VALUES('1111','MILLER','HYD','123')
1 row created.

TABLE VALUES:
SQL> SELECT * from branch;
B_ID B_NAME B_CITY
---------- --------------- ----------
123 SBH HYD
124 SBI SEC
125 ICICI VIZAG

SQL> SELECT * from customer;

C_ID C_NAME C_CITY B_ID
---------- ---------- ---------- -----------------------
1111 MILLER HYD 123
1112 SCOTT HYD 125
1113 KING VIZAG 125
1114 SMITH SEC 124
1115 CLARK HYD 124

QUERIES:
(I)
SQL> SELECT distinct C.* from customer C,branch B WHERE B.b_id=C.b_id AND B.b_city=C.c_city;

C_ID C_NAME C_CITY B_ID

---------- ---------- ---------- ------------------------
1114 SMITH SEC 124
1111 MILLER HYD 123
1113 KING VIZAG 125
(II)
SQL> SELECT C.* from customer C,branch B where B.b_id=C.b_id and C.c_city=upper('&branch_city');
Enter value for branch_city: sec
old 1: SELECT C.* from customer C,branch B where B.b_id=C.b_id and C.c_city=upper('&branch_city')
new 1: SELECT C.* from customer C,branch B where B.b_id=C.b_id and C.c_city=upper('sec')

C_ID C_NAME C_CITY B_ID

---------- ---------- ---------- --------------------------
1114 SMITH SEC 124
10. Create the following tables :
Book(accession-no, title, publisher, year, date-of-purchase, status)
Member(member-id, name, number-of-books-issued, max-limit)
Book-issue(accession-no, member-id, date-of-issue)
(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 books which are due from the students to be returned. A book is considered
to be due if it has been issued 15 days back and yet not returned.
(ii) List all those members who cannot be issued any more books.

TABLE CREATION:

SQL> CREATE table BOOK1(AC_NO number PRIMARY

KEY,TITLE varchar2(8),PUBLISHER varchar2(8),YEARnumber,D_O_PUR date,STATUS varchar2(
17));

Table created.

SQL> CREATE table MEMBER(M_ID number PRIMARY

KEY,NAME varchar2(10),NO_OF_BOOK number,M_LIMITnumber);

Table created.

SQL> CREATE table BOOK_ISSUE(AC_NO number REFERENCES BOOK1(ac_no),M_ID

NUMBER REFERENCES MEMBER(m_id),D_O_ISSUE date);

Table created.

VALUE INSERTION:

SQL> INSERT into book1 VALUES('&ac_no','&title','&publisher','&year','&d_o_pur',upper('&status'));

Enter value for ac_no: 111
Enter value for title: JAVA
Enter value for publisher: ALEX
Enter value for year: 1985
Enter value for d_o_pur: 20-DEC-1999
Enter value for status: ISSUED
old 1: INSERT into book1 VALUES('&ac_no','&title','&publisher','&year','&d_o_pur',upper('&status'))
new 1: INSERT into book1 VALUES('111','JAVA','ALEX','1985','20-DEC-1999',upper('ISSUED'))

1 row created.
SQL> INSERT into member VALUES('&m_id','&name','&no_of_book_issued','&max_limit');
Enter value for m_id: 1
Enter value for name: LAXMI
Enter value for no_of_book_issued: 1
Enter value for max_limit: 4
old 1: INSERT into member VALUES('&m_id','&name','&no_of_book_issued','&maxlimit')
new 1: INSERT into member VALUES('1','LAXMI','1','4')

1 row created.
SQL> INSERT into book_issue VALUES('&ac_no','&m_id','&d_o_issue');
Enter value for ac_no: 111
Enter value for m_id: 5
Enter value for d_o_issue: 14-oct-2009
old 1: INSERT into book_issue VALUES('&ac_no','&m_id','&d_o_issue')
new 1: INSERT into book_issue VALUES('111','5','14-oct-2009')
1 row created.

TABLE VALUES:
SQL> SELECT * from book1;
AC_NO TITLE PUBLISHE YEAR D_O_PUR STATUS
---------- -------- -------- ---------- --------- --------------------------------------------
111 JAVA ALEX 1985 20-DEC-99 ISSUED
112 ORACLE SMITH 1999 12-APR-01 ISSUED
113 C CLARK 1986 16-MAR-88 ISSUED
114 C++ PONTING 1995 23-SEP-88 ISSUED
115 DBMS SYMONDS 2005 17-JUL-06 ISSUED

SQL> SELECT * from member;

M_ID NAME NO_OF_BOOK M_LIMIT
---------- ---------- ---------- -----------------------------
1 LAXMI 1 4
2 BALAJI 1 4
3 RAMU 2 4
4 RAJU 1 4
5 BABU 4 4
6 BHEEM 0 2

SQL> SELECT * from book_issue;

AC_NO M_ID D_O_ISSUE
---------- ---------- ----------------------
111 5 14-OCT-09
115 1 01-SEP-09
114 3 12-SEP-08
112 2 24-OCT-09
113 4 23-SEP-09
QUERIES:
(I)SQL> SELECT B.*,I.d_o_issue from book1 B,book_issue I WHERE B.ac_no=I.ac_no AND
(I.d_o_issue+15)<SYSDATE;
AC_NO TITLE PUBLISHE YEAR D_O_PUR STATUS D_O_ISSUE
---------- -------- -------- ---------- --------- ----------------- ---------------------------------
113 C CLARK 1986 16-MAR-88 ISSUED 23-SEP-09
114 C++ PONTING 1995 23-SEP-88 ISSUED 12-SEP-08
115 DBMS SYMONDS 2005 17-JUL-06 ISSUED 01-SEP-09
(II)SQL> SELECT M.* from member M,book_issue I WHERE M.m_id=I.m_id;
M_ID NAME NO_OF_BOOK M_LIMIT
---------- ---------- ---------- ---------------------------
5 BABU 4 4
1 LAXMI 1 4
3 RAMU 2 4
2 BALAJI 1 4
4 RAJU 1 4
11. Create the following tables :
Book(accession-no, title, publisher, year, date-of-purchase, status)
Member(member-id, name, number-of-books-issued, max-limit,status)
Book-issue(accession-no, member-id, date-of-issue)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write a PL/SQL procedure to issue the book.
Write a trigger to set the status of students to "back listed" if they have taken book but not
returned even after one year.

TABLE CREATION:

SQL> CREATE table BOOK1(AC_NO number PRIMARY

KEY,TITLE varchar2(8),PUBLISHERvarchar2(8),YEAR number,D_O_PUR date,STATUS varchar2(
17));
Table created.

SQL> CREATE table MEMBER(M_ID number PRIMARY

KEY,NAME varchar2(10),NO_OF_BOOKnumber,M_LIMIT number,STATUS varchar2(10));
Table created.

SQL> CREATE table BOOK_ISSUE(AC_NO number REFERENCES BOOK1(ac_no),M_ID number

REFERENCES MEMBER(m_id),D_O_ISSUE date);
Table created.

VALUE INSERTION:
SQL> INSERT into book1 VALUES('&ac_no','&title','&publisher','&year','&d_o_pur','&status');
Enter value for ac_no: 111
Enter value for title: JAVA
Enter value for publisher: ALEX
Enter value for year: 1985
Enter value for d_o_pur: 20-DEC-1999
Enter value for status:
old 1: INSERT into book1 VALUES('&ac_no','&title','&publisher','&year','&d_o_pur',upper('&status'))
new 1: INSERT into book1 VALUES('111','JAVA','ALEX','1985','20-DEC-1999','')

1 row created.
SQL> INSERT into member VALUES('&m_id','&name','&no_of_book_issued','&max_limit');
Enter value for m_id: 1
Enter value for name: LAXMI
Enter value for no_of_book_issued: 1
Enter value for max_limit: 4
old 1: INSERT into member VALUES('&m_id','&name','&no_of_book_issued','&maxlimit')
new 1: INSERT into member VALUES('1','LAXMI','1','4')
1 row created.

SQL> INSERT into book_issue VALUES('&ac_no','&m_id','&d_o_issue');

Enter value for ac_no: 111
Enter value for m_id: 5
Enter value for d_o_issue: 14-oct-2009
old 1: INSERT into book_issue VALUES('&ac_no','&m_id','&d_o_issue')
new 1: INSERT into book_issue VALUES('111','5','14-oct-2009')
1 row created.
TABLE VALUES:

SQL> SELECT * from book1;

AC_NO TITLE PUBLISHE YEAR D_O_PUR STATUS

---------- -------- -------- ---------- --------- --------------------------------------------
111 JAVA ALEX 1985 20-DEC-99
112 ORACLE SMITH 1999 12-APR-01
113 C CLARK 1986 16-MAR-88
114 C++ PONTING 1995 23-SEP-88
115 DBMS SYMONDS 2005 17-JUL-06

SQL> SELECT * from member;

M_ID NAME NO_OF_BOOK M_LIMIT STATUS

---------- ---------- ---------- -----------------------------------------
1 LAXMI 1 4
2 BALAJI 1 4
3 RAMU 2 4
4 RAJU 1 4
5 BABU 4 4
6 BHEEM 0 2

SQL> SELECT * from book_issue;

AC_NO M_ID D_O_ISSUE

---------- ---------- ----------------------

TRIGGER:

CREATE OR REPLACE Trigger trg11 AFTER insert or update on book_issue

DECLARE
CURSOR c1 is SELECT d_o_issue,m_id from book_issue;
Begin
for i in c1
loop
if ADD_MONTHS(i.d_o_issue,12)<=sysdate then
UPDATE member set status='back listed' where m_id=i.m_id;
end if;
end loop;
End;
/
PL/SQL PROCEDURE with name pro1:
CREATE OR REPLACE PROCEDURE pro1 AS
acno book_issue.ac_no%type :='&accssion_no';
mid book_issue.m_id%type :='&member_id';
doi book_issue.d_o_issue%type:='&date_of_issue';
m number;
n number;

BEGIN
SELECT no_of_book,m_limit into n,m from member WHERE m_id=mid;
if m>n then
INSERT into book_issue VALUES(acno,mid,doi);
dbms_output.put_line('VALUE SUCCESSFULLY INSERTED');
n:=n+1;
else
dbms_output.put_line('MAX LIMIT REACHED FOR THIS MEMBER');
end if;
UPDATE member set no_of_book=n WHERE m_id=mid;
END pro1;
/

CREATION:
SQL> @pro1
Enter value for accssion_no: 115
Enter value for member_id: 1
Enter value for date_of_issue: 23-sep-2008
old 5: Insert into book_issue values(&accssion_no,&member_id,'&date_of_issue')
new 5: Insert into book_issue values(115,1,'23-sep-2008');

Procedure created.
EXECUTION:

SQL> EXECUTE pro1

VALUE SUCCESSFULLY INSERTED

PL/SQL procedure successfully completed.

OUTPUT:

SQL> SELECT * FROM BOOK_ISSUE;

AC_NO M_ID D_O_ISSUE

---------- ---------- ---------------------
115 1 23-SEP-08
114 2 19-SEP-09

SQL> SELECT * FROM MEMBER;

M_ID NAME NO_OF_BOOK M_LIMIT STATUS

---------- ---------- ---------- ---------- --------------------------------
1 LAXMI 2 4 back listed
2 BALAJI 2 4
3 RAMU 2 4
4 RAJU 1 4
5 BABU 4 4
6 RAKHI 1 2

6 rows selected.
12. Create the following tables :
Book(accession-no, title, publisher, year, date-of-purchase, status)
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-issue)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write a PL/SQL procedure to issue the book.
Write a trigger to set the status of a book neither to "lost" which is neither
issued nor in the library.
TABLE CREATION:
SQL> CREATE table BOOK(AC_NO number PRIMARY
KEY,TITLE varchar2(8),PUBLISHER varchar2(8),YEARnumber,D_O_PUR date,STATUS varchar2(
17));
Table created.
SQL> CREATE table member(m_id number PRIMARY KEY,name varchar2(10),no_of_book number,m_limit
number,status varchar2(10));
Table created.
SQL> CREATE table book_issue(ac_no number REFERENCES book1(ac_no),m_id number
REFERENCES member(m_id),d_o_issue date);
Table created.
SQL> CREATE table book_place(ac_no number REFERENCES book(ac_no),r_id number,r_position
varchar2(10));
Table created.
VALUE INSERTION:
SQL> INSERT into book_place VALUES('&ac_no','&rack_id','&rack_position');
Enter value for ac_no: 123
Enter value for rack_id: 5
Enter value for rack_position: left
old 1: INSERT into book_place VALUES('&ac_no','&rack_id','&rack_position')
new 1: INSERT into book_place VALUES('123','5','left')
1 row created.

SQL> INSERT into member VALUES('&m_id','&name','&no_of_book_issued','&max_limit',’&status’);

Enter value for m_id: 1
Enter value for name: LAXMI
Enter value for no_of_book_issued: 1
Enter value for max_limit: 4
Enter value for status:
old 1: INSERT into member VALUES('&m_id','&name','&no_of_book_issued','&maxlimit',’’)
new 1: INSERT into member VALUES('1','LAXMI','1','4',’’)

TABLE VALUES:
SQL> SELECT * FROM BOOK_PLACE;
AC_NO R_ID R_POSITION
---------- ---------- --------------------
123 5 left
SQL> SELECT * FROM MEMBER;
M_ID NAME NO_OF_BOOK M_LIMIT STATUS
---------- ---------- ---------- ---------- -----------------------------
1 LAXMI 1 4
2 BALAJI 0 4
3 RAMU 0 4
PL/SQL PROCEDURE with name pro1:
CREATE OR REPLACE PROCEDURE pro1 AS
acno book_issue.ac_no%type :='&accssion_no';
mid book_issue.m_id%type :='&member_id';
doi book_issue.d_o_issue%type:='&date_of_issue';
m number;
n number;
BEGIN
SELECT no_of_book,m_limit into n,m from member WHERE m_id=mid;
if m>n then
INSERT into book_issue VALUES(acno,mid,doi);
dbms_output.put_line('VALUE SUCCESSFULLY INSERTED');
n:=n+1;
else
dbms_output.put_line('MAX LIMIT REACHED FOR THIS MEMBER');
end if;
UPDATE member set no_of_book=n WHERE m_id=mid;
END pro1;
/
CREATION:
SQL> @pro1
Enter value for accssion_no: 115
Enter value for member_id: 1
Enter value for date_of_issue: 23-sep-2008
old 5: Insert into book_issue values(&accssion_no,&member_id,'&date_of_issue')
new 5: Insert into book_issue values(115,1,'23-sep-2008');
Procedure created.
EXECUTION:
SQL> EXECUTE pro1
VALUE SUCCESSFULLY INSERTED
PL/SQL procedure successfully completed.
OUTPUT:
SQL> SELECT * FROM BOOK_ISSUE;

AC_NO M_ID D_O_ISSUE

---------- ---------- ---------------------
115 1 23-SEP-08
114 2 19-SEP-09
TRIGGER:
CREATE OR REPLACE trigger trg12 BEFORE insert or update ON book for each row
declare
m number:=0;
n number:=0;
BEGIN
SELECT count(*) into m from book_issue WHERE ac_no=:new.ac_no;
SELECT count(*) into n from book_place WHERE ac_no=:new.ac_no;
if m=0 and n=0 then
:new.status:='lost';
end if;
dbms_output.put_line('SUCCESSFULLY UPDATED');
END;
/
VALUE INSERTION:
SQL>INSERT into book values(‘&ac_no’,’&title’,’&publisher’,’&year’,’&d_o_pur’,’&status’);
Enter value for ac_no: 126
Enter value for title:JAVA
Enter value for publisher: SMITH
Enter value for year:1988
Enter value for d_o_pur:23-sep-2009
Enter value for status:’’
old 1: INSERT into book values(‘&ac_no’,’&title’,’&publisher’,’&year’,’&d_o_pur’,’&status’);
new 1: INSERT into book values('126','JAVA','SMITH',’1988’,’23-SEP-2009’,’’)

1 row created.
TABLE VALUES:
AC_NO TITLE PUBLISHER YEAR D_O_PUR STSTUS
---------- ---------- ---------- ---------- ----------------- -----------------------------
126 JAVA SMITH 1988 23-SEP-2009 lost
125 DBMS ALEX 1985 17-JUL-2009
123 ORACLE JONES 1999 28-MAR-2009

13. Create the following tables :

Book(accession-no, title, publisher, year, date-of-purchase, status)
Member(member-id, name, number-of-books-issued, max-limit, status)
Book-issue(accession-no, member-id, date-of-issue, due-date)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write a PL/SQL to list all those students who are defaulters. A student is
considerer to be a defaulter if he has not returned a book even after due-date.
Write a trigger to set the status of students to "back listed" if they have taken book but not
returned even after one year.
TABLE CREATION:

SQL> CREATE table BOOK1(AC_NO number PRIMARY

KEY,TITLE varchar2(8),PUBLISHERvarchar2(8),YEAR number,d_o_pur
date,STATUS varchar2(17));
Table created.
SQL> CREATE table MEMBER(M_ID number PRIMARY
KEY,NAME varchar2(10),NO_OF_BOOKnumber,M_LIMIT number,STATUS varchar2(10));
Table created.

SQL> CREATE table BOOK_ISSUE(AC_NO number REFERENCES BOOK1(ac_no),M_ID number

REFERENCES member(m_id),D_O_ISSUE date,DUE_DATE date);
Table created.
TABLE VALUES:

SQL> SELECT * from book1;

AC_NO TITLE PUBLISHE YEAR D_O_PUR STATUS
---------- -------- -------- ---------- --------- --------------------------------------------
111 JAVA ALEX 1985 20-DEC-99 ISSUED
112 ORACLE SMITH 1999 12-APR-01 ISSUED
113 C CLARK 1986 16-MAR-88 ISSUED
114 C++ PONTING 1995 23-SEP-88 ISSUED
115 DBMS SYMONDS 2005 17-JUL-06 ISSUED
SQL> SELECT * from member;
M_ID NAME NO_OF_BOOK M_LIMIT STATUS
---------- ---------- ---------- -----------------------------------------
1 LAXMI 1 4
2 BALAJI 1 4
3 RAMU 2 4
4 RAJU 1 4
5 BABU 4 4
6 BHEEM 0 2

SQL> SELECT * from book_issue;

AC_NO M_ID D_O_ISSUE DUE_DATE
---------- ---------- ----------------------------------------

TRIGGER:
CREATE OR REPLACE Trigger trg11 AFTER insert or update on book_issue
DECLARE
CURSOR c1 is SELECT d_o_issue,m_id from book_issue;
Begin
for i in c1
loop
if ADD_MONTHS(i.d_o_issue,12)<=sysdate then
UPDATE member set status='back listed' where m_id=i.m_id;
end if;
end loop;
End;
/
PL/SQL BLOCK with name pr13:
DECLARE
CURSOR x is
SELECT M.* from member M,book_issue B WHERE
M.m_id=B.m_id and B.due_date<sysdate;

BEGIN
dbms_output.put_line(rpad('-',40,'-'));
dbms_output.put_line(rpad('M_ID',10)||rpad('NAME',15)||'STATUS');
dbms_output.put_line(rpad('-',40,'-'));
for i in x
loop
dbms_output.put_line(rpad(i.m_id,10)||rpad(i.name,15)||i.status);
end loop;
END;
/
INSERTION INTO book_issue:
SQL> INSERT into book_issue VALUES('&ac_no','&m_id','&d_o_issue','&due_date');
Enter value for ac_no: 111
Enter value for m_id: 1
Enter value for d_o_issue: 23-sep-2008
Enter value for due_date: 10-oct-2008
old 1: INSERT into book_issue VALUES('&ac_no','&m_id','&d_o_issue','&due_date')
new 1: INSERT into book_issue VALUES('111','1','23-sep-2008','10-oct-2008')

1 row created.
OUTPUT:
SQL> SELECT * FROM book_issue;

AC_NO M_ID D_O_ISSUE DUE_DATE

---------- ---------- --------- --------------------------------
111 1 23-SEP-08 10-OCT-08
112 5 15-SEP-09 01-OCT-09
113 4 12-OCT-09 01-NOV-09
114 3 17-JUL-08 17-AUG-08

SQL> SELECT * from member;

M_ID NAME NO_OF_BOOK M_LIMIT STATUS

---------- ---------- ---------- ----------------------------------------------
1 LAXMI 1 4 back listed
2 BALAJI 1 4
3 RAMU 2 4 back listed
4 RAJU 1 4
5 BABU 4 4
6 BHEEM 0 2

SQL> @pr13
----------------------------------------
M_ID NAME STATUS
----------------------------------------
1 LAXMI back listed
3 RAMU back listed
5 BABU

PL/SQL procedure successfully completed.

14. Create the following tables :

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 more than one branch.

TABLE CREATION:

SQL> CREATE table BRANCH(B_ID number PRIMARY KEY,B_NAME varchar2(15),B_CIty varchar2(10));

Table created.

SQL> CREATE
table CUSTOMER(C_ID number ,C_NAME varchar2(10),C_CITY varchar2(10),B_ID number
REFERENCES branch(b_id),PRIMARY KEY(c_id,b_id));

Table created.
VALUE INSERTION:

SQL> INSERT into branch VALUES('&b_id','&b_name','&b_city');

Enter value for b_id: 123
Enter value for b_name: SBH
Enter value for b_city: HYD
old 1: INSERT into branch VALUES('&b_id','&b_name','&b_city')
new 1: INSERT into branch VALUES('123','SBH','HYD')

1 row created.

SQL> INSERT into customer VALUES('&c_id','&c_name','&c_city','&b_id');

Enter value for c_id: 1111
Enter value for c_name: MILLER
Enter value for c_city: HYD
Enter value for b_id: 123
old 1: INSERT into customer VALUES('&c_id','&c_name','&c_city','&b_id')
new 1: INSERT into customer VALUES('1111','MILLER','HYD','123')

1 row created.

TABLE VALUES:

SQL> SELECT * from branch;

B_ID B_NAME B_CITY
---------- --------------- ----------
123 SBH HYD
124 SBI SEC
125 ICICI VIZAG

SQL> SELECT * from customer;

C_ID C_NAME C_CITY B_ID
---------- ---------- ---------- -----------------------
1111 MILLER HYD 123
1112 SCOTT HYD 125
1113 KING VIZAG 125
1114 SMITH SEC 124
1115 CLARK HYD 124
1113 KING VIZAG 123

QUERIES:
(I)SQL> SELECT distinct C.* from customer C,branch B WHERE B.b_id=C.b_id and B.b_city=C.c_city;
C_ID C_NAME C_CITY B_ID
---------- ---------- ---------- ------------------------
1114 SMITH SEC 124
1111 MILLER HYD 123
1113 KING VIZAG 125

(II)SQL> SELECT distinct C1.c_id,C1.c_name,C1.c_city from customer C1,customer C2 WHERE

C1.c_id=C2.c_id and NOT C1.b_id=C2.b_id;
C_ID C_NAME C_CITY
---------- ---------- ---------------------
1113 KING VIZAG
15. Create the following tables :
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 Branches who have more than 5 customer.
(ii) List all those customers who have an account in more than one branch

SQL> CREATE table BRANCH(B_ID number PRIMARY KEY,B_NAME varchar2(15),B_CITY varchar2(10));

Table created.

SQL> CREATE
table CUSTOMER(C_ID number ,C_NAME varchar2(10),C_CITY varchar2(10),B_ID number
REFERENCES branch(B_ID),PRIMARY KEY(C_ID,B_ID));

Table created.

VALUE INSERTION:

SQL> INSERT into branch VALUES('&b_id','&b_name','&b_city');

Enter value for b_id: 123
Enter value for b_name: SBH
Enter value for b_city: HYD
old 1: INSERT into branch VALUES('&b_id','&b_name','&b_city')
new 1: INSERT into branch VALUES('123','SBH','HYD')

1 row created.

SQL> INSERT into customer VALUES('&c_id','&c_name','&c_city','&b_id');

Enter value for c_id: 1111
Enter value for c_name: MILLER
Enter value for c_city: HYD
Enter value for b_id: 123
old 1: INSERT into customer VALUES('&c_id','&c_name','&c_city','&b_id')
new 1: INSERT into customer VALUES('1111','MILLER','HYD','123')

1 row created.

TABLE VALUES:

SQL> SELECT * from branch;

B_ID B_NAME B_CITY

---------- --------------- ----------
123 SBH HYD
124 SBI SEC
125 ICICI VIZAG
SQL> SELECT * from customer;

C_ID C_NAME C_CITY B_ID

---------- ---------- ---------- -----------------------
1111 MILLER HYD 123
1112 SCOTT HYD 125
1113 KING VIZAG 125
1114 SMITH SEC 124
1115 CLARK HYD 124
1113 KING VIZAG 123
1116 MARTIN VIZAG 124
1117 PONTING HYD 124
1118 SYMO SEC 124

QUERIES:
(I)

SQL> SELECT B.* from branch B WHERE 5<=(SELECT count(*) from customer C WHERE
C.b_id=B.b_id);

B_ID B_NAME B_CITY

---------- --------------- ----------
124 SBI SEC
(II)

SQL> SELECT distinct C1.c_id,C1.c_name,C1.c_city from customer C1,customer C2 WHERE

C1.c_id=C2.c_id and NOT C1.b_id=C2.b_id;
(OR)
SQL> SELECT distinct C1.c_id,C1.c_name from customer C1 WHERE 1<=(SELECT count(*) from
customer C2 where C2.c_id=C1.c_id and c2.b_id<>c1.b_id);

C_ID C_NAME C_CITY

---------- ---------- ---------------------
1113 KING VIZAG

16. Create the following table :

Student (roll-no, name, 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) Generate queries to do the following :
(i) List names of the students who are having same rank but they should
reside in different districts.
(ii) List details of students they belongs to same category with same rank

TABLE CREATION:

SQL> CREATE table STUDENT2(R_NO number PRIMARY

KEY,NAME varchar2(15),CATEGORYvarchar2(4),DIST varchar2(10),STATE varchar2(2));
Table created.
SQL> CREATE table STD_RANK(R_NO number
REFERENCES STUDENT2(R_NO),MARKS number,RANKnumber );
Table created.
VALUE INSERTION:

SQL> INSERT into student2 values('&r_no','&name','&category','&dist','&state');

Enter value for r_no: 1
Enter value for name: RAMU
Enter value for category: BC-D
Enter value for dist: ADB
Enter value for state: AP
old 1: INSERT into student2 values('&r_no','&name','&category','&dist','&state')
new 1: INSERT into student2 values('1','RAMU','BC-D','ADB','AP')
1 row created.

SQL> INSERT into std_rank values('&r_no','&marks','&rank');

Enter value for r_no: 1
Enter value for marks: 90
Enter value for rank: 1000
old 1: INSERT into std_rank values('&r_no','&marks','&rank')
new 1: INSERT into std_rank values('1','90','1000')
1 row created.

TABLE VALUES:

SQL> SELECT * FROM STUDENT2;

R_NO NAME CATE DIST ST
---------- --------------- ---- ---------- ---------------
1 RAMU BC-D ADB AP
2 BHEEM ST DGF TM
3 SOMU OC HYD AP
4 SUMAN BC-A KMM AP
5 RAMULU BC-D WRG AP

SQL> SELECT * FROM STD_RANK;

R_NO MARKS RANK
---------- ---------- ----------
1 90 1000
2 85 900
3 99 50
5 90 1000
4 90 1000

QUERIES:
(I)
SQL> SELECT distinct S1.name FROM student2 S1,student2 S2,std_rank R1,std_rank R2 WHERE
S1.r_no=R1.r_no AND S2.r_no=R2.r_no AND R1.rank=R2.rank AND NOT S1.dist=S2.dist;

NAME
---------------
RAMULU
SUMAN
RAMU
(II)SQL> SELECT S1.*,R1.rank from student2 S1,student2 S2,std_rank R1,std_rank R2 WHERE
S1.r_no=R1.r_no AND S2.r_no=R2.r_no AND R1.rank=R2.rank AND NOT S1.dist=S2.dist AND
S1.category=S2.category;

R_NO NAME CATE DIST ST RANK

---------- --------------- ---- ---------- -- -------------------------------
1 RAMU BC-D ADB AP 1000
5 RAMULU BC-D WRG AP 1000

17. Create the following tables :

Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration)
(a) Create a form to accept the data from the user with appropriate
validation checks.
(b) Generate queries to do the following :
(i) List all those students who are between 18-19 years of age and have opted
for MCA course.
(iii)List all those courses in which number of students are less than 10.

TABLE CREATION:

SQL> CREATE table COURSE(C_ID number(3) PRIMARY

KEY,NAME varchar2(15),FEE number(5),DURATIONnumber(2));

Table created.

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),D_O_BIRTH date,C_IDnumber(3) references COURSE(C_ID));

Table created.

VALUE INSERTION:

SQL> INSERT into Course VALUES('&c_id','&name','&fee','&duration');

Enter value for c_id: 11
Enter value for name: MCA
Enter value for fee: 27500
Enter value for duration: 3
old 1: INSERT into Course VALUES('&c_id','&name','&fee','&duration')
new 1: INSERT into Course VALUES('11','MCA','27500','3')

1 row created.

SQL> INSERT into student VALUES('&r_no','&name','&d_o_birth','&c_id');

Enter value for r_no: 1
Enter value for name: KING
Enter value for d_o_birth: 23-SEP-91
Enter value for c_id: 11
old 1: INSERT into student VALUES('&r_no','&name','&d_o_birth','&c_id')
new 1: INSERT into student VALUES('1','KING','23-SEP-91','11')

1 row created.
TABLE VALUES:

SQL> select * from student;

R_NO NAME D_O_BIRTH C_ID
---------- --------------- --------- ---------------------
1 KING 23-SEP-91 11
2 FORD 25-MAR-93 13
3 LANGER 17-JUL-90 11
4 PONTING 07-APR-75 11
5 SYMONDS 28-JUL-91 12

SQL> SELECT * FROM course;

C_ID NAME FEE DURATION
---------- --------------- ---------- -----------------------
11 MCA 27500 3
12 BSC(COMPS) 11000 3
13 BTECH 31500 4

QUERIES:
(I)
SQL> SELECT S.* from student S,course C WHERE S.c_id=C.c_id and C.name='MCA' and
months_between(sysdate,d_o_birth)/12 between 18 and 19;

R_NO NAME D_O_BIRTH C_ID

---------- --------------- --------- --------------------
1 KING 23-SEP-91 11
(II)
SQL> SELECT distinct C.* from course C,student S WHERE 3>(SELECT count(*) from student S where
S.c_id=C.c_id);

C_ID NAME FEE DURATION

---------- --------------- ---------- ----------------------
13 BTECH 31500 4
12 BSC(COMPS) 11000 3

18&19. Create the following tables :

Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration, status)
(a) Create a form to accept the data from the user with appropriate
validation checks.
(b) Write PL/SQL procedure to do the following :
Set the status of course to "not offered" in which the number of
candidates is less than 5.

TABLE CREATION:
SQL> CREATE table COURSE(C_ID number(3) PRIMARY
KEY,NAME varchar2(15),FEE number(5),DURATIONnumber(2),STATUS varchar2(15));
Table created.

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),D_O_BIRTH date,C_IDnumber(3) references COURSE(C_ID));
Table created.
VALUE INSERTION:
SQL> INSERT into Course VALUES('&c_id','&name','&fee','&duration');
Enter value for c_id: 11
Enter value for name: MCA
Enter value for fee: 27500
Enter value for duration: 3
old 1: INSERT into Course VALUES('&c_id','&name','&fee','&duration')
new 1: INSERT into Course VALUES('11','MCA','27500','3')

1 row created.

SQL> INSERT into student VALUES('&r_no','&name','&d_o_birth','&c_id');

Enter value for r_no: 1
Enter value for name: KING
Enter value for d_o_birth: 23-SEP-91
Enter value for c_id: 11
old 1: INSERT into student VALUES('&r_no','&name','&d_o_birth','&c_id')
new 1: INSERT into student VALUES('1','KING','23-SEP-91','11')

1 row created.
TABLE VALUES:
SQL> select * from student;

R_NO NAME D_O_BIRTH C_ID

---------- --------------- --------- ---------------------
1 KING 23-SEP-91 11
2 FORD 25-MAR-93 13
3 LANGER 17-JUL-90 11
4 PONTING 07-APR-75 11
5 SYMONDS 28-JUL-91 12

SQL> SELECT * FROM course;

C_ID NAME FEE DURATION STATUS

---------- --------------- ---------- ------------------------------------
11 MCA 27500 3
12 BSC(COMPS) 11000 3
13 BTECH 31500 4

PL/SQL PROCEDURE:
CREATE OR REPLACE procedure pr18
as
CURSOR c1 is SELECT distinct C.* from course C,student S WHERE 2>(SELECT count(*) from student S
where S.c_id=C.c_id);
BEGIN
for i in c1
loop
UPDATE course set status='not offered' WHERE c_id=i.c_id;
end loop;
dbms_output.put_line('SUCCESSFULLY UPDATED');

END pr18;
/
CREATION:
SQL> @PR18

Procedure created.

EXECUTION:
SQL> EXECUTE PR18
SUCCESSFULLY UPDATED

PL/SQL procedure successfully completed.

SQL> SELECT * FROM COURSE;

C_ID NAME FEE DURATION STATUS
---------- --------------- ---------- ---------- ----------------------------
11 MCA 27500 3
12 BSC(COMPS) 11000 3 not offered
13 BTECH 31500 4 not offered

20. Create the following tables :

Student(roll-no, name, date-of-birth, course-id)
Course (Course-id, name, fee, duration, status)
(a)Create a form to accept the data from the user with appropriate validation
checks.
(b)Write PL/SQL procedure to do the following :
Set the status of course to "offered" in which the number of candidates is
at least 10 otherwise set it to "not offered".
TABLE CREATION:
SQL> CREATE table COURSE(C_ID number(3) PRIMARY
KEY,NAME varchar2(15),FEE number(5),DURATIONnumber(2),STATUS varchar2(15));
Table created.

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),D_O_BIRTH date,C_IDnumber(3) references COURSE(C_ID));
Table created.
VALUE INSERTION:
SQL> INSERT into Course VALUES('&c_id','&name','&fee','&duration');
Enter value for c_id: 11
Enter value for name: MCA
Enter value for fee: 27500
Enter value for duration: 3
old 1: INSERT into Course VALUES('&c_id','&name','&fee','&duration')
new 1: INSERT into Course VALUES('11','MCA','27500','3')

1 row created.
SQL> INSERT into student VALUES('&r_no','&name','&d_o_birth','&c_id');
Enter value for r_no: 1
Enter value for name: KING
Enter value for d_o_birth: 23-SEP-91
Enter value for c_id: 11
old 1: INSERT into student VALUES('&r_no','&name','&d_o_birth','&c_id')
new 1: INSERT into student VALUES('1','KING','23-SEP-91','11')
1 row created.
TABLE VALUES:
SQL> SELECT * from student;
R_NO NAME D_O_BIRTH C_ID
---------- --------------- --------- ---------------------
1 KING 23-SEP-91 11
2 FORD 25-MAR-93 13
3 LANGER 17-JUL-90 11
4 PONTING 07-APR-75 11
5 SYMONDS 28-JUL-91 12

SQL> SELECT * FROM course;

C_ID NAME FEE DURATION STATUS
---------- --------------- ---------- ------------------------------------
11 MCA 27500 3
12 BSC(COMPS) 11000 3
13 BTECH 31500 4

PL/SQL PROCEDURE:
CREATE OR REPLACE procedure pr20 as
CURSOR c1 is SELECT C.* from course C,student S WHERE C.c_id=S.c_id and 2>(SELECT count(*) from
student S where S.c_id=C.c_id);
CURSOR c2 is SELECT C.* from course C,student S WHERE C.c_id=S.c_id and 2<=(SELECT count(*)
from student S where S.c_id=C.c_id);

BEGIN
for i in c1
loop
UPDATE course set status='not offered' WHERE c_id=i.c_id;
end loop;
for j in c2
loop
UPDATE course set status='offered' WHERE c_id=j.c_id;
end loop;
dbms_output.put_line('SUCCESSFULLY UPDATED');

END pr20;
/
CREATION:
SQL> @PR20

Procedure created.
EXECUTION:
SQL> EXECUTE PR20
SUCCESSFULLY UPDATED
PL/SQL procedure successfully completed.

SQL> SELECT * FROM COURSE;

C_ID NAME FEE DURATION STATUS
---------- --------------- ---------- ---------- -------------------------
11 MCA 27500 3 offered
12 BSC(COMPS) 11000 3 not offered
13 BTECH 31500 4 not offered
21. 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) Create a form to accept the data from the user with appropriate validation checks.
(b) Write PL/SQL procedure to do the following :
Generate a report to list the items whose qty-in-stock is less than or equal to their reorder-levels.

TABLE CREATION:

SQL> CREATE table ITEM(I_CODE number PRIMARY

KEY,I_NAME varchar2(10),QTYnumber,REORDER number);

Table created.

SQL> CREATE table SUPPLIER(S_CODE number PRIMARY

KEY,S_NAME varchar2(15),ADDRESSvarchar2(15));

Table created.

SQL> CREATE table CAN_SUPPLY(S_CODE number

REFERENCES SUPPLIER(S_CODE),I_CODEnumber REFERENCES ITEM(I_CODE));

Table created.

TABLE VALUES:

SQL> SELECT * FROM item;

I_CODE I_NAME QTY REORDER

---------- ---------- ---------- -----------------------
11 BOOK 55 10
12 PEN 100 120
13 PENCIL 250 15
14 BAG 125 20
15 PAPER 1000 2000

SQL> SELECT * FROM supplier;

S_CODE S_NAME ADDRESS
---------- --------------- ---------------
111 SMITH SEC
112 ALEX HYD
113 PONTING HYD
114 CLARK SEC

SQL> SELECT * FROM can_supply;

S_CODE I_CODE
----- ---------- ----------
111 13
112 11
113 11
PL/SQL procedure:

CREATE OR REPLACE procedure pr21

as
CURSOR c1 is SELECT * from item WHERE qty<=reorder;

BEGIN
dbms_output.put_line(rpad('-',40,'-'));
dbms_output.put_line(rpad('I_CODE',10)||'I_NAME');
dbms_output.put_line(rpad('-',40,'-'));
for i in c1
loop
dbms_output.put_line(rpad(i.i_code,10)||i.i_name);
end loop;
END pr21;
/
CREATION:
SQL> @PR21
Procedure created.

EXECUTION:
SQL> EXECUTE PR21
----------------------------------------
I_CODE I_NAME
----------------------------------------
12 PEN
15 PAPER

PL/SQL procedure successfully completed.

22. Create the following table :

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 :
Set the status of the supplier to "important" if the supplier can supply
More than five items.
TABLE CREATION:
SQL> CREATE table ITEM(I_CODE number PRIMARY
KEY,I_NAME varchar2(10),QTYnumber,REORDER number);
Table created.

SQL> CREATE table SUPPLIER(S_CODE number PRIMARY

KEY,S_NAME varchar2(15),ADDRESSvarchar2(15),STATUS varchar2(15));
Table created.

SQL> CREATE table CAN_SUPPLY(S_CODE number

REFERENCES SUPPLIER(S_CODE),I_CODEnumber REFERENCES ITEM(I_CODE));

Table created.
TABLE VALUES:
SQL> SELECT * FROM item;
I_CODE I_NAME QTY REORDER
---------- ---------- ---------- -----------------------
11 BOOK 55 10
12 PEN 100 120
13 PENCIL 250 15
14 BAG 125 20
15 PAPER 1000 2000
SQL> SELECT * FROM supplier;
S_CODE S_NAME ADDRESS STATUS
---------- --------------- ----------------------------------
111 SMITH SEC
112 ALEX HYD
113 PONTING HYD
114 CLARK SEC

SQL> SELECT * FROM can_supply;

S_CODE I_CODE
---------------- ----------
111 13
112 11
113 11
112 14
112 12
112 15
PL/SQL procedure:
CREATE OR REPLACE procedure pr22 AS
CURSOR c1 is SELECT distinct S.* from supplier S,can_supply C WHERE C.s_code=S.s_code and
4<=(SELECT count(*) from can_supply C where S.s_code=C.s_code);
BEGIN
for i in c1
loop
UPDATE supplier set status='important' WHERE s_code=i.s_code;
end loop;
dbms_output.put_line('SUCCESSFULLY UPDATED');
END pr22;
/
CREATION:
SQL> @PR22
Procedure created.
EXECUTION:
SQL> EXECUTE PR22
SUCCESSFULLY UPDATED
PL/SQL procedure successfully completed.
OUTPUT:
SQL> SELECT * FROM supplier;
S_CODE S_NAME ADDRESS STATUS
---------- --------------- --------------- ----------------------
111 SMITH SEC
112 ALEX HYD important
113 PONTING HYD
114 CLARK SEC
23. 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 :
Generate a report of those items that are supplied by those suppliers whose
status is "important".

TABLE CREATION:

SQL> CREATE table ITEM(I_CODE number PRIMARY

KEY,I_NAME varchar2(10),QTYnumber,REORDER number);
Table created.

SQL> CREATE table SUPPLIER(S_CODE number PRIMARY

KEY,S_NAME varchar2(15),ADDRESSvarchar2(15),STATUS varchar2(15));
Table created.

SQL> CREATE table CAN_SUPPLY(S_CODE number

REFERENCES SUPPLIER(S_CODE),I_CODEnumber REFERENCES ITEM(I_CODE));
Table created.

TABLE VALUES:
SQL> SELECT * FROM item;
I_CODE I_NAME QTY REORDER
---------- ---------- ---------- -----------------------
11 BOOK 55 10
12 PEN 100 120
13 PENCIL 250 15
14 BAG 125 20
15 PAPER 1000 2000

SQL> SELECT * FROM supplier;

S_CODE S_NAME ADDRESS STATUS
---------- --------------- ----------------------------------
111 SMITH SEC
112 ALEX HYD important
113 PONTING HYD
114 CLARK SEC

SQL> SELECT * FROM can_supply;

S_CODE I_CODE
---------- ----------
111 13
112 11
113 11
112 14
112 12
112 15

6 rows selected.
PL/SQL procedure:
CREATE OR REPLACE procedure pr23
as
CURSOR c1 is SELECT I.* FROM ITEM I,SUPPLIER S,CAN_SUPPLY C WHERE I.I_CODE=C.I_CODE
AND S.S_CODE=C.S_CODE AND S.STATUS='important';
BEGIN
dbms_output.put_line(rpad('-',40,'-'));
dbms_output.put_line(rpad('I_CODE',10)||'I_NAME');
dbms_output.put_line(rpad('-',40,'-'));
for i in c1
loop
dbms_output.put_line(rpad(i.i_code,10)||i.i_name);
end loop;
END pr23;
/
CREATION:
SQL> @PR23

Procedure created.
EXECUTION:
SQL> EXECUTE PR23
----------------------------------------
I_CODE I_NAME
----------------------------------------
11 BOOK
14 BAG
12 PEN
15 PAPER

PL/SQL procedure successfully completed.

24. Create the following tables :

Student (roll-no, name, 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 PL/SQL procedure to the following :
Generate a report to list of those districts from which the first hundred rankers
come from.
TABLE CREATION:

SQL> CREATE table STUDENT2(R_NO number PRIMARY

KEY,NAME varchar2(15),CATEGORYvarchar2(4),DIST varchar2(10),STATE varchar2(2));

Table created.

SQL> CREATE table STD_RANK(R_NO number

REFERENCES STUDENT2(R_NO),MARKSnumber,RANK number PRIMARY KEY);

Table created.
VALUE INSERTION:

SQL> INSERT into student2 VALUES('&r_no','&name','&category','&dist','&state');

Enter value for r_no: 1
Enter value for name: RAMU
Enter value for category: BC-D
Enter value for dist: ADB
Enter value for state: AP
old 1: INSERT into student2 VALUES('&r_no','&name','&category','&dist','&state')
new 1: INSERT into student2 VALUES('1','RAMU','BC-D','ADB','AP')
1 row created.

SQL> INSERT into std_rank VALUES('&r_no','&marks','&rank');

Enter value for r_no: 1
Enter value for marks: 90
Enter value for rank: 5
old 1: INSERT into std_rank VALUES('&r_no','&marks','&rank')
new 1: INSERT into std_rank VALUES('1','90','5')
1 row created.

TABLE VALUES:
SQL> SELECT * FROM student2;
R_NO NAME CATE DIST ST
---------- --------------- ---- ---------- ---------------------
1 RAMU BC-D ADB AP
2 BHEEM ST DGF TM
3 SOMU OC HYD AP
4 SUMAN BC-A KMM AP
5 RAMULU BC-D WRG AP
6 RAJU BC-C RR AP
7 RANI OC NZB AP
8 RAGHU SC ADB AP
9 AMAR BC-A MDK AP
10 SRINU BC-D ADB AP
10 rows selected.

SQL> SELECT * FROM std_rank;

R_NO MARKS RANK

---------- ---------- -------------------
1 90 5
2 85 9
3 99 1
6 98 2
7 87 8
5 90 10
4 90 3
8 97 4
9 88 7
10 89 6

10 rows selected.
PL/SQL procedure:

CREATE OR REPLACE procedure pr24

as
CURSOR c1 is SELECT S.R_NO,S.DIST,R.RANK FROM STUDENT2 S,STD_RANK R WHERE
S.R_NO=R.R_NO AND R.RANK BETWEEN 1 AND 5;
BEGIN
dbms_output.put_line(rpad('-',40,'-'));
dbms_output.put_line(rpad('R_NO',10)||rpad('DISTRICT',10)||'RANK');
dbms_output.put_line(rpad('-',40,'-'));
for i in c1
loop
dbms_output.put_line(rpad(i.r_no,10)||rpad(i.dist,10)||i.rank);
end loop;
END pr24;
/

CREATION:

SQL> @PR24

Procedure created.

EXEXUTION:

SQL> EXECUTE PR24

----------------------------------------
R_NO DISTRICT RANK
----------------------------------------
1 ADB 5
3 HYD 1
4 KMM 3
6 RR 2
8 ADB 4

PL/SQL procedure successfully completed.

25. Create the following tables :

Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code, specialization)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write PL/SQL procedure to the following :
Set the status of the subject to "not offered" if the subject is not opted by at
least 1 students.

TABLE CREATION:

SQL> CREATE table STUDENT1(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),S_OPTEDvarchar2(10));

Table created.
SQL> CREATE table FACULTY(F_CODE number(2) PRIMARY
KEY,F_NAME varchar2(15),SPECvarchar2(10));
Table created.

SQL> CREATE table SUB_RANK(S_CODE number(2) PRIMARY

KEY,S_NAME varchar2(13),F_CODEnumber(2) REFERENCES FACULTY(F_CODE),STATUS
varchar2(15));
Table created.

TABLE VALUES:

SQL> SELECT * FROM student1 2 ;

R_NO NAME S_NAME S_OPTED

---------- --------------- ---------- ----------
1 CLARK DBMS JAVA
2 SMITH DBMS DBMS
3 SCOTT JAVA DBMS

SQL> SELECT * FROM sub_rank;

S_CODE S_NAME F_CODE STATUS

---------- ------------- -----------------------------------
21 DBMS 12
22 JAVA 11
23 C++ 14

SQL> SELECT * FROM faculty;

F_CODE F_NAME SPEC

---------- ------------- -------------------
11 ALEX JAVA
12 LAKE DBMS
13 PONTING C
14 SYMANDS C++
15 MILLER ORACLE

PL/SQL procedure:
CREATE OR REPLACE procedure pr25 as
CURSOR c1 is SELECT distinct r.* from sub_rank r,student1 s WHERE 1>(SELECT count(*) from student1
s WHERE upper(r.s_name)=s.s_opted);

BEGIN

for i in c1
loop
UPDATE sub_rank set status='not offered' WHERE s_code=i.s_code;
end loop;
dbms_output.put_line('SUCCESSFULLY UPDATED');

END pr25;
/
CREATION:
SQL> @PR25

Procedure created.
EXECUTION:
SQL> EXECUTE PR25
SUCCESSFULLY UPDATED

PL/SQL procedure successfully completed.

OUTPUT:
SQL> SELECT * FROM SUB_RANK;

S_CODE S_NAME F_CODE STATUS

---------- ------------- ---------- --------------------------
21 DBMS 12
22 JAVA 11
23 C++ 14 NOT OFFERED

26. Create the following tables :

Student (roll-no, name, subject-opted)
Subject –rank (subject-code, subject-name, faculty-code, specialization)
Faculty (faculty-code, faculty-name, specialization)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Write 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.
TABLE CREATION:

SQL> CREATE table STUDENT1(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),S_OPTEDvarchar2(10));
Table created.

SQL> CREATE table FACULTY(F_CODE number(2) PRIMARY

KEY,F_NAME varchar2(15),SPECvarchar2(10));
Table created.

SQL> CREATE table SUB_RANK(S_CODE number(2) PRIMARY

KEY,S_NAME varchar2(13),F_CODEnumber(2) REFERENCES FACULTY(F_CODE),STATUS
varchar2(15));
Table created.

TABLE VALUES:
SQL> SELECT * FROM student1
2 ;

R_NO NAME S_NAME S_OPTED

---------- --------------- ---------- ----------
1 CLARK DBMS JAVA
2 SMITH DBMS DBMS
3 SCOTT JAVA DBMS
SQL> SELECT * FROM sub_rank;

S_CODE S_NAME F_CODE STATUS

---------- ------------- -----------------------------------
21 DBMS 12
22 JAVA 11
23 C++ 14
24 ICG
25 OR
SQL> SELECT * FROM faculty;

F_CODE F_NAME SPEC

---------- ------------- -------------------
11 ALEX OS
12 LAKE DBMS
13 PONTING C
14 SYMANDS C
15 MILLER ORACLE

PL/SQL procedure:
CREATE OR REPLACE procedure pr26
as
CURSOR c1 is SELECT * FROM sub_rank WHERE f_code IS NULL;

BEGIN
for i in c1
loop
UPDATE sub_rank set status='not offered' WHERE s_code=i.s_code;
end loop;
dbms_output.put_line('SUCCESSFULLY UPDATED');

END pr26;
/
CREATION:
SQL> @PR26

Procedure created.
EXECUTION:
SQL> EXECUTE PR26
SUCCESSFULLY UPDATED

PL/SQL procedure successfully completed.

OUTPUT:
SQL> SELECT * FROM sub_rank;

S_CODE S_NAME F_CODE STATUS

---------- ------------- -----------------------------------
21 DBMS 12
22 JAVA 11
23 C++ 14
24 ICG not offered
25 OR not offered
27.&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)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate 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 not offered by any faculty members.
(iii) Find all those subjects which are offered by more than one faculty member.

TABLE CREATION:

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),S_OPTEDvarchar2(10));
Table created.

SQL> CREATE table FACULTY(F_CODE number(2) PRIMARY

KEY,F_NAME varchar2(15),SPECvarchar2(10));
Table created.

SQL> CREATE table SUB_RANK(S_CODE number(2) ,S_NAME varchar2(13),F_CODE number(2)

REFERENCES FACULTY(F_CODE));
Table created.

VALUE INSERTION:

SQL> INSERT into student VALUES('&r_no','&name','&s_opted');

Enter value for r_no: 1
Enter value for name: CLARK
Enter value for s_opted: DBMS
old 1: INSERT into student VALUES('&r_no','&name','&s_opted')
new 1: INSERT into student VALUES('1','CLARK','DBMS')
1 row created.

SQL> INSERT into Faculty VALUES('&f_code','&f_name','&specialization');

Enter value for f_code: 11
Enter value for f_name: ALEX
Enter value for specialization: JAVA
old 1: INSERT into Faculty VALUES('&f_code','&f_name','&specialization')
new 1: INSERT into Faculty VALUES('11','ALEX','JAVA')
1 row created.

TABLE VALUES:

SQL> SELECT * FROM student;

R_NO NAME S_OPTED
---------- --------------- ---------- --------------------
1 CLARK DBMS
2 SMITH JAVA
3 SCOTT DBMS
SQL> SELECT * FROM Faculty;

F_CODE F_NAME SPEC

---------- --------------- ------ ---------
11 ALEX JAVA
12 LAKE DBMS
13 PONTING JAVA
14 SYMANDS C++
15 MILLER OS

SQL> SELECT * FROM sub_rank;

S_CODE S_NAME F_CODE

---------- ------------- -------------------
21 DBMS 12
22 JAVA 11
23 C++ 14
24 ICG
25 OR
21 JAVA 13

QUERIES:

(I)
SQL> SELECT count(*) NO_OF_STUDENTS from student WHERE s_opted='DBMS';

NO_OF_STUDENTS
----------------------------
2
(II)
SQL> SELECT * FROM SUB_RANK WHERE F_CODE IS NULL;

S_CODE S_NAME F_CODE

---------- ------------- -------------------
24 ICG
25 OR
(III)
SQL> SELECT distinct S1.s_code,S1.s_name FROM sub_rank S1 WHERE 1<(SELECT count(*) from
sub_rank S2 WHERE S1.s_code=S2.s_code);

S_CODE S_NAME
------- -------------------
21 JAVA
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)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate queries to do the following :
(i) Find the number of students who have enrolled for the subject "OS"
(ii) Find all those students who opted for more than 5 subjects.

TABLE CREATION:

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),S_OPTEDvarchar2(10));

Table created.

SQL> CREATE table FACULTY(F_CODE number(2) PRIMARY

KEY,F_NAME varchar2(15),SPECvarchar2(10));

Table created.

SQL> CREATE table SUB_RANK(S_CODE number(2) ,S_NAME varchar2(13),F_CODE number(2)

REFERENCES FACULTY(F_CODE));

Table created.

QUERIES:
(I)

SQL> SELECT count(*) NO_OF_STUDENTS from student WHERE s_opted='OS';

NO_OF_STUDENTS
----------------------------
1
(II)

SELECT S1.* from student S1 WHERE 5<(SELECT count(*) from student S2 WHERE S1.r_no=S2.r_no)

no rows selected
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)
(a) Create a form to accept the data from the user with appropriate validation
checks.
(b) Generate 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.

TABLE CREATION:

SQL> CREATE table STUDENT(R_NO number(2) PRIMARY

KEY,NAME varchar2(15),S_OPTEDvarchar2(10));

Table created.

SQL> CREATE table FACULTY(F_CODE number(2) PRIMARY

KEY,F_NAME varchar2(15),SPECvarchar2(10));

Table created.

SQL> CREATE table SUB_RANK(S_CODE number(2) ,S_NAME varchar2(13),F_CODE number(2)

REFERENCES FACULTY(F_CODE));

Table created.

QUERIES:
(I).

SQL>SELECT count(*) NO_OF_STDS FROM student1 WHERE NOT s_opted='DBMS'

NO_OF_STDS
-------------------
1
(II).
SQL> SELECT distinct S1.s_code,S1.s_name FROM sub_rank S1 WHERE 1<(SELECT count(*) from
sub_rank S2 WHERE S1.s_code=S2.s_code);

S_CODE S_NAME
------- -------------------
21 JAVA