DBS lab 1

Lab 1

1. client_master

>create table client_master(client_no varchar2(6), name varchar2(20), address1 varchar2(30), address2 varchar2(30), city varchar2(15), state varchar2(15), pincode number(6), bal_due number(10,2));

>insert into client_master
(client_no, name, city, pincode, state, bal_due)
 values('0001', 'Ivan', 'Bombay', '400054', 'Maharashtra', 15000);

>insert into client_master
(client_no, name, city, pincode, state, bal_due)
 values('0002', 'Vandana', 'Madras', '780001', 'tamilnadu', 0);

>insert into client_master
(client_no, name, city, pincode, state, bal_due)
 values('0003', 'Pramada', 'Bombay', '400057', 'Maharashtra', 5000);

>insert into client_master
(client_no, name, city, pincode, state, bal_due)
 values('0004', 'Basu', 'Bombay', '400056', 'Maharashtra', 0);

>insert into client_master
(client_no, name, city, pincode, bal_due)
 values('0005', 'Ravi', 'Delhi', '100001',2000);

>insert into client_master
(client_no, name, city, pincode, state, bal_due)
 values('0006', 'Rukmini', 'Bombay', '400050', 'Maharashtra', 0);





2. product_master

>create

>insert into product_master
(product_no, description, Profit_percent, Unit_measure, Qty_on_hand, Reoder_lvl, Sell_price, cost_price)
 values('&product_no', '&description', &profit_percent, '&unit_measure', &qty_on_hand, &reorder_lvl, &sell_price, &cost_price);

>insert into product_master
values('P07965','540HDD',4,'piece',10,3,8400,8000);

>insert into product_master
values('P07975','1.44 Drive',5,'piece',10,3,1050,1000);

>insert into product_master
values('P08868','1.22 Drive',5,'piece',2,3,1050,1000);


3. Questions:

1>select name from client_master;

2>select name,city from client_master;

3>select description from product_master;

4>select name from client_master where city='Bombay';
 >select name from client_master where lower(city)='bombay';
 >select name from client_master where lower(city) in('bombay','mumbai');

5>select * from client_master where client_no in('0001','0002');

6>select * from product
9>price between 2000 and 5000
10> <> //not equal

DISTINCT keyword..

>select distinct city from client_master;
NOTES:

spool; // logging and all
truncate; // to remove all records from table
drop; // to remove table
load from csv
sqlldr;