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');