Column name
|
Datatype
|
Size
|
Attributes
|
Order_no
|
Varchar2
|
6
|
Primary/first
letter must be 0
|
Order_date
|
Date
|
6
|
|
Client no
|
Varchar2
|
25
|
Foreign key
references customer_no in customer table
|
Salesman_no
|
Varchar2
|
6
|
Foreign key
references salesman_no of salesman_master table
|
Del_type
|
Char
|
1
|
Delivery
part(p)/full(f)
|
Del_date
|
Date
|
Cannot be less
than order_date
|
|
Order_status
|
Varchar2
|
10
|
Values(‘Inprocess’;’fulfilled’;’backorder’;’canceled’
|
Create Table:
create table order_t5
(
order_no varchar2(6) CHECK (Order_no like 'o%') UNIQUE,
order_date date,
client_no varchar2(6),
constraint pk_cli_ord_t5 primary key (client_no,order_no),
constraint pk_client_t5 foreign key (client_no) references
customer_t5 (cust_no),
foreign key (salesman_no) references sales_mans_t5 (salesman_no),
salesman_no varchar2(6),
del_type char(1),
del_date date,
order_status varchar2(10)
);
Insert Query
insert into order_t5 values('o00001','12-jan-96','001','s00001',
'F','20-jan-96','Ip');
insert into order_t5 values('o00002','25-jan-96','002','s00002',
'P','27-jan-96','C');
insert into order_t5 values('o00003','18-feb-96','003','s00003',
'F','20-feb-96','F');
insert into order_t5 values('o00004','03-apr-96','004','s00004',
'F','07-apr-96','F');
Select Query:
select * from order_t5;
Output:
orber_no order_date client_no salesman_no del_type del_date order_state
o00001 12-JAN-96 001 s00001 F 20-JAN-96 Ip
o00002 25-JAN-96 002 s00002 P 27-JAN-96 C
o00003 18-FEB-96 003 s00003 F 20-FEB-96 F
o00004 03-APR-96 004 s00004 F 07-APR-96 F
Order_t5 table is not create any problem then you first create customer_t5 table, given below.
Create table customer_t5
create table customer_t5 ( cust_no varchar2(6), CONSTRAINT cust_no_pk_t5 PRIMARY KEY(cust_no), name varchar2(20), address1 varchar2(30), address varchar2(30), city varchar2(15), state varchar2(15), pincode number(6), bal_code number(10,2) );
Insert Query for customer_t5:
insert into customer_t5 values ('001','pratik','reti mohalla',
'kankroli','rajsamand','rajsathan','31334','10000');
insert into customer_t5 values ('002','aman','jk mohalla',
'kankroli','rajsamand','rajsathan','313324','10000');
insert into customer_t5 values ('003','dilkhush','dil',
'pratapgard','kankroli','rajsathan','313324','10000');
insert into customer_t5 values ('004','Ajay','x mohalla',
'kankroli','chennai','tamilnadu','123224','20000');
insert into customer_t5 values ('005','shiva','xyz mohalla',
'kankroli','chennai','tamilnadu','21124','30000');
insert into customer_t5 values ('006','dilkhush','my house,
new mohalla','kankroli','bangalore','karnatak','123224','20000');
insert into customer_t5 values ('007','jayesh','k road,
new mohalla','kankroli','bangalore','karnatak','233224','30000');
