Scenario:

ABC Book Store is a leading book seller in the Sultanate of Oman.  They have large 

collection of books in their store.  The data of books purchased and available stock

in the store is in the BOOKS table of the database.  Some of the books they have 

purchased are unsold for a long time.  They have decided to segregate the 

information of older books with the newer one.   

Based on the above scenario perform the following task:Programming With PL SQL (COMP 0340)-SPRING-15-CW2 (ASSMNT)-QP

Programming with PL/SQL (COMP 0340) Page 2 of 6

a) Create the BOOKS  and OLD_BOOKS  tables  with appropriate columns, 

relevant data types and integrity constraints. 

Create a PL/SQL block using cursor and while/simple loop to scan through 

the BOOKS table to copy records of books which are unsold for the last 

four years or more into another table named OLD_BOOKS.  The program 

should also display the total amount of OLD_BOOKS available at the ABC 

book store.

2

8

3 Create a stored procedure to display the following output from the books data 

available in BOOKS table of ABC Book Store as given in the scenario of task-2:

 Total amount of books purchased by the ABC store.

 Total amount of books sold by the ABC store.

 Total number of books which remain unsold as of the current date.

7

4 Consider the scenario given in  task-2 and create any relevant function with a 

parameter to return a value.   Use an appropriate exception to handle the error if 

the given parameter value is invalid.

 

بس اذا ممكن اخواني تردو علي بهالايام اريد الحل

 

ضرووووووووووووووووووي

 

ياجماعة لو احد يرد علي ويساعدني لو تكرمتو

بليز والله ضروري ما اريد اخسر درجاتي فالمادة

السلام عليكم ورحمة الله 

 

يمكن إفتراض الجداول المطلوبه  حسب الغرض من الحالة الدراسية ( بشكل مختصر ) وهي 

- جدول اسماء الكتب 

- جدول مشتريات الكتب رئيسي 

- جدول مشتريات الكتب تفصيلي ( فاتورة الشراء تحتوي كتاب واحد أو أكثر ) 

- جدول مبيعات الكتب رئيسي 

- جدول مبيعات الكتب تفصيلي ( فاتورة البيع تحتوي كتاب واحد أو أكثر ) 

- جدول الكتب الغير مباعة 

 

ويمكن إعداد المطلوب من خلال التعامل مع هذة الجداول الإفتراضية 

 

والرسم والعلاقات كالتالي 

/*==============================================================*/
/* Table: BOOKS_CODE                                          */
/*==============================================================*/
CREATE TABLE BOOKS_CODE 
(
   BOOK_ID            VARCHAR2(50)         NOT NULL,
   BOOK_NAME          VARCHAR2(100),
   CONSTRAINT PK_BOOKS_CODE PRIMARY KEY (BOOK_ID)
);


/*==============================================================*/
/* Table: OLD_BOOKS                                           */
/*==============================================================*/
CREATE TABLE OLD_BOOKS 
(
   BOOK_ID            VARCHAR2(50)         NOT NULL,
   STORE_ID           VARCHAR2(20)         NOT NULL,
   QUANTITY_UNSOLD    NUMBER,
   CONSTRAINT PK_OLD_BOOKS PRIMARY KEY (BOOK_ID, STORE_ID)
);


/*==============================================================*/
/* Table: PURCHASE                                            */
/*==============================================================*/
CREATE TABLE PURCHASE 
(
   PURCHASE_NO        NUMBER               NOT NULL,
   PURCHASE_DATE      DATE,
   STORE_ID           VARCHAR2(20),
   VENDOR_NUM         NUMBER,
   CONSTRAINT PK_PURCHASE PRIMARY KEY (PURCHASE_NO)
);


/*==============================================================*/
/* Table: PURCHASE_DETAILS                                    */
/*==============================================================*/
CREATE TABLE PURCHASE_DETAILS 
(
   PURCHASE_NO        NUMBER               NOT NULL,
   PURCHASE_BOOK_ID   VARCHAR2(50)         NOT NULL,
   PURCHASE_QUANTITY  NUMBER,
   CONSTRAINT PK_PURCHASE_DETAILS PRIMARY KEY (PURCHASE_NO, PURCHASE_BOOK_ID)
);


/*==============================================================*/
/* Table: SALES                                               */
/*==============================================================*/
CREATE TABLE SALES 
(
   INVOICE_NO         NUMBER               NOT NULL,
   INVOICE_DATE       DATE,
   STORE_ID           VARCHAR2(20),
   CUSTOMER_NUM       NUMBER,
   CONSTRAINT PK_SALES PRIMARY KEY (INVOICE_NO)
);


/*==============================================================*/
/* Table: SALES_DETAILS                                       */
/*==============================================================*/
CREATE TABLE SALES_DETAILS 
(
   INVOICE_NO         NUMBER               NOT NULL,
   INVOICE_BOOK_ID    VARCHAR2(50)         NOT NULL,
   SALES_QUANTITY     NUMBER,
   SALES_PRICE        NUMBER,
   CONSTRAINT PK_SALES_DETAILS PRIMARY KEY (INVOICE_NO, INVOICE_BOOK_ID)
);


ALTER TABLE OLD_BOOKS
   ADD CONSTRAINT FK_OLD_BOOK_REFERENCE_BOOKS_CO FOREIGN KEY (BOOK_ID)
      REFERENCES BOOKS_CODE (BOOK_ID);


ALTER TABLE PURCHASE_DETAILS
   ADD CONSTRAINT FK_PURCHASE_REFERENCE_PURCHASE FOREIGN KEY (PURCHASE_NO)
      REFERENCES PURCHASE (PURCHASE_NO);


ALTER TABLE PURCHASE_DETAILS
   ADD CONSTRAINT FK_PURCHASE_REFERENCE_BOOKS_CO FOREIGN KEY (PURCHASE_BOOK_ID)
      REFERENCES BOOKS_CODE (BOOK_ID);


ALTER TABLE SALES_DETAILS
   ADD CONSTRAINT FK_SALES_DE_REFERENCE_SALES FOREIGN KEY (INVOICE_NO)
      REFERENCES SALES (INVOICE_NO);


ALTER TABLE SALES_DETAILS
   ADD CONSTRAINT FK_SALES_DE_REFERENCE_BOOKS_CO FOREIGN KEY (INVOICE_BOOK_ID)
      REFERENCES BOOKS_CODE (BOOK_ID);

أشكرك كثير 

جزاك الله خير عن اجتهادك