Exp 15 - Part 3

Create the following tables ( primary keys are underlined.).

Sales_order(s_order_no,s_order_date)
Client(client_no, name, address)

A clinet can give on e or more sales_orders , but a sales_order belongs to exactly one client.
Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).

a) insert 2 client records into client table
b) insert 3 sales records for each client
c) change order date of client_no ’C004’ to 12/4/08
d) delete all sale records having order date before 10th feb. 08
e) delete the record of client “joshi”

ans.

create table client(
cno int primary key,
name text,
address text
);

create  table osales(
cno int ,
ono integer primary key ,
odate date,
foreign key (cno) references client (cno)on delete cascade on update cascade
);

a) insert 2 client records into client table
insert into client values(1,'abc','pune');
insert into client values(2,'pqr','pune');

b) insert 3 sales records for each client
insert into osales values(1,101,'2-2-2015');
insert into osales values(1,102,'2-4-2014');
insert into osales values(1,103,'2-6-2013');
insert into osales values(2,201,'2-7-2012');
insert into osales values(2,202,'2-2-2011');
insert into osales values(2,203,'2-2-1017');

select *from client;
select *from osales;

c) change order date of client_no ’C004’ to 12/4/08

update  osales set odate='12/4/8' where cno='1';

d) delete all sale records having order date before 10th feb. 08

delete from client where name='joshi';