SUTEX BANK COLLEGE OF COMPUTER APPLICATIONS AND SCIENCE
SUBJECT- 303 RDBMS
CLASS- S.Y. BCA (SEM III)
ACADEMIC YEAR 2019-2020
ASSIGNMENT 5
Using Client_Master, Product_Master, SalesMan_Master, Sales_Order Create views,
Index and Sequence
Views:
1) Create a view (Salesman_no, Salesman_name, City, Sal_amt,Tgt_to_get) on the
Salesman_master table for the Sales Department.
CREATE TABLE SALESMAN_MASTER1
(
SALESMANNO VARCHAR(6) PRIMARY KEY CHECK(SALESMANNO LIKE'S%'),
SALESMANNAME VARCHAR(20) NOT NULL,
ADDRESS1 VARCHAR(30),
ADDRESS2 VARCHAR(30),
CITY VARCHAR(20),
PINCODE NUMBER(8),
STATE VARCHAR(20),
SALAMT NUMBER(8,2) NOT NULL CHECK(SALAMT>0),
TGTTOGET NUMBER(6,2) NOT NULL CHECK(TGTTOGET>0),
YTDSALES NUMBER(6,2) NOT NULL,
REMARKS VARCHAR(30)
);
2) Create a view (Client_no, Client_name, City, State, Bal_due) on The
Client_master table for the Administration Department.
CREATE TABLE CLIENT_MASTER1
(
CLIENTNO VARCHAR(6) PRIMARY KEY CHECK(CLIENTNO LIKE'C%'),
NAME VARCHAR(20) NOT NULL,
ADDRESS1 VARCHAR(30),
ADDRESS2 VARCHAR(30),
PINCODE NUMBER(8),
STATE VARCHAR(10),
BALDUE NUMBER(10,2));
3) Create a view vw_prod (Product_no, Description, Profit_percent, Unit_measure,
Sell_price) on the product_master table. Perform the operations such as Insert,
Update and Delete on that view.
4) Create a Read only view on product_master.
5) Create a view vw_vw_prod(Product_no, Description) on view vw_prod.
Index:
1) Create a simple index on client_no column of the client_master table.
CREATE INDEX CLIENTINDEX
ON CLIENT_MASTER(NAME);
2) Create a composite index on the sales_order_details table on columns order_no
and product_no.
CREATE INDEX COMPOSITE
ON SALES_ORDER_DETAILS(ORDERNO,PRODUCTNO);
3) Create a unique index named idx_client_no on client_no column of the
client_master table.
CREATE UNIQUE INDEX INX_CLIENT_NO
ON CLIENT_MASTER(NAME);
4) Remove index idx_client_no created for the table client_master.
DROP INDEX IDX_CLIENT_NO;
5) Retrieve first seven rows by using ROWNUM. Sort the data in the ascending order
of name.
SELECT ROWNUM,NAME FROM CLIENT_MASTER WHERE ROWNUM<=3 ORDER BY NAME;
Sequence:
1)Create a sequence by the name order_seq, which will generate numbers from 1 up to
999 in ascending order with an interval of 1. The sequence must restart from the
number 1 after generating number 999.
CREATE SEQUENCE ORDER_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 999 CYCLE
CACHE 2;
2) Insert values for order_no, order_date, client_no in the sales_order table. The
order_seq sequence must be used to generate order_no and order_date must be set to
system date.
INSERT INTO SALES_ORDERS VALUES('O'||SEQ_ORDER_NO.NEXTVAL,SYSDATE,'COO1');
3)Alter a sequence order_seq created on sales_order , change the Cache value of the
sequence order_seq to 30 and interval between two numbers as 2.
ALTER SEQUENCE SEQ_ORDER_NO INCREMENT BY 2 CYCLE CACHE 30;
4)Insert data of Product_no(P_JUL2019P001) into Product_Master table using
Sequence (Write Sequence and Insert query).
INSERT INTO PROD_MASt PRODUCTNO VALUES (TO_CHAR(SYSDATE,'MM','YYYY')||'P'||
LPADE(ORDER_SEQ.NEXTVAL,4,'0'));
5)The Clientno stored in Client_Master table is a concatenation 5 of the letter 'C'
and the number generated through sequence.For example employee no D05 is generated
with c and05 (Here 05 is sequence generated value). Insert values in the ClientNo
column of a Client_Master table using the above concatenation
