DBMS assignment - 4

https://docs.google.com/document/d/1MgkoGE3DD0Pg5XtUIqeB3Awxdxt93mdzNCjxYnAMvxo/edit#

Techno India College of Technology

                  Name: ​

                  Roll: 314012210 ​

                  Registration:  213141001210

                  Year: B.C.A 2nd 4th sem​

                  Subject: Database Management System                                                 Lab [BCAC491]​

Assignment - 4

1. Create an index on the table client_master, field clientno.

create index index_clientmaster on Clientmaster (Clientno);

2. Create an index on the sales_order, field s_order_no.

create index index_salesorder on Salesorder (S_orderno);

3. Create a composite index on the sales_order_details table for the columns s_order_no and product_no.

create index index_sales_order_details on Sales_order_details (S_orderno, Productno);

4. Create a composite index ch_index on challan_header table for the columns challan no and s_order_no.

create index ch_index on Challan_header (ChallanNo, S_orderNo);

5. Create a unique index on the table salesman_master, field salesman_no.

create unique index index_salesman_master on Salesman_master (Salesman_no);

6. Drop index ch_index on table challan_header.

drop index ch_index;

7. Create view on salesman_master whose sal_amt is less than 3500.

create view sal_amtLessThan3500 as

select * from Salesman_master

where Salamt < 3500;

8. Create a view client_view on client_master and rename the columns as name, city, pcode, state respectively.

create view client_view as

select Name as name, City as city, Pincode as pcode, State as state from Clientmaster;

9. Select the client names from client_view who lives in city ‘Bombay’.

select name from client_view where city='Bombay';

NAME

Ivan

Pramada

Basu

10. Drop the view client_view.

drop view client_view;

11. Create sequence inv_seq with the following parameters, increment by 3, cycle, cache 4 and which will generate numbers from 1 to 9999 in ascending order.

create sequence inv_seq

start with 1

increment by 3

minvalue 1

maxvalue 9999

cycle

cache 4;

12. Give another name for Sales_order_details table.

alter table Sales_order_details rename to new_sales_order_details;