CREATE USER Students IDENTIFIED BY ADBMS
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO Students;
drop table book;
create table author(a_id number(10)primary key, a_name varchar2(20));
create table book(b_id number(10),b_name varchar2(20), isbn varchar2(20),edition varchar2(20), c_id number(10),a_id number(10), primary key(b_id,edition) );
create table category(c_id number(10)primary key, c_name varchar2(20));
alter table book add constraint fk_category foreign key (c_id) references category(c_id);
alter table book add constraint fk_author foreign key (a_id) references author(a_id);
insert into author values(‘1′,’J.K. Rowling’);
insert into author values(‘2′,’Stephenie Meyer’);
insert into author values(‘3′,’Dan Brown’);
insert into author values(‘4′,’Humayun Ahmed’);
insert into author values(‘5′,’Zafar Iqbal’);
insert into category values(’11’,’Fantasy’);
insert into category values(’22’,’Romance’);
insert into category values(’33’,’Thriller’);
insert into category values(’44’,’Anti-logic’);
insert into category values(’55’,’Science Fiction’);
insert into book values(‘111′,’HP…Deathly Hallows’,’978-3-16-148410-0′,’10’,’11’,’1′);
insert into book values(‘222′,’Breaking Dawn’,’979-3-16-148410-0′,’10’,’22’,’2′);
insert into book values(‘333′,’Origin’,’980-3-16-148410-0′,’10’,’33’,’3′);
insert into book values(‘444′,’Holud HimuKalo RAB’,’981-3-16-148410-0′,’10’,’44’,’4′);
insert into book values(‘555′,’Obonil’,’982-3-16-148410-0′,’10’,’55’,’5′);
CREATE OR REPLACE FUNCTION total_books
RETURN NUMBER
IS
total NUMBER;
BEGIN
SELECT COUNT(*) INTO total FROM book;
RETURN total;
END;
/
CREATE OR REPLACE PROCEDURE update_edition AS
BEGIN
UPDATE book SET edition = ’20’ WHERE edition = ’10’;
DBMS_OUTPUT.PUT_LINE(‘Edition updated successfully!’);
END;
/
DECLARE
v_book_name book.b_name%TYPE;
TYPE book_rec IS RECORD (book_name VARCHAR2(20));
v_book book_rec;
BEGIN
SELECT b_name INTO v_book_name FROM book WHERE b_id = ‘111’;
v_book.book_name := v_book_name;
DBMS_OUTPUT.PUT_LINE(‘Book Name: ‘ || v_book.book_name);
END;
/
DECLARE
TYPE book_record_type IS RECORD (
b_id NUMBER(10),
b_name VARCHAR2(20)
);
book_record book_record_type;
BEGIN
FOR book IN (SELECT b_id, b_name FROM book) LOOP
book_record.b_id := book.b_id;
book_record.b_name := book.b_name;
DBMS_OUTPUT.PUT_LINE(‘Book ID: ‘ || book_record.b_id || ‘, Book Name: ‘ || book_record.b_name);
END LOOP;
END;
DECLARE
v_category_id category.c_id%TYPE;
v_category_name category.c_name%TYPE;
CURSOR c_categories IS SELECT c_id, c_name FROM category;
BEGIN
OPEN c_categories;
LOOP
FETCH c_categories INTO v_category_id, v_category_name;
EXIT WHEN c_categories%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Category ID: ‘ || v_category_id || ‘, Category Name: ‘ || v_category_name);
END LOOP;
CLOSE c_categories;
END;
/
CREATE OR REPLACE TRIGGER category_insert_trigger
AFTER INSERT ON category
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘New Category Added’);
END;
/
CREATE OR REPLACE TRIGGER category_delete_trigger
AFTER DELETE ON category
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘A Category Deleted’);
END;
/
CREATE OR REPLACE PACKAGE book_utility AS
PROCEDURE display_book_name(p_book_id IN book.b_id%TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY book_utility AS
PROCEDURE display_book_name(p_book_id IN book.b_id%TYPE) IS
v_book_name book.b_name%TYPE;
BEGIN
SELECT b_name INTO v_book_name
FROM book
WHERE b_id = p_book_id;
DBMS_OUTPUT.PUT_LINE(‘Book Name: ‘ || v_book_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Book not found for ID: ‘ || p_book_id);
END display_book_name;
END;
/
CREATE OR REPLACE PACKAGE book_utility IS
PROCEDURE display_book_name(p_book_id IN book.b_id%TYPE);
END book_utility;
CREATE OR REPLACE PACKAGE BODY book_utility AS
PROCEDURE display_book_name(p_book_id IN book.b_id%TYPE) IS
v_book_name book.b_name%TYPE;
BEGIN
SELECT b_name INTO v_book_name FROM book WHERE b_id = p_book_id;
DBMS_OUTPUT.PUT_LINE(‘Book Name: ‘ || v_book_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Book Not Found’);
END display_book_name;
END book_utility;
