Sub : Oracle Assignment - 4

CREATE FOLLOWING TABLES:

[1] CLIENT _MASTER_23:

SQL> create table client_master_23

2 (clientno varchar(6)primary key,
3 name varchar(15)not null,
4 address1 varchar(5),
5 address2 varchar(5),
6 city varchar(15),
7 pincode number(6),
8 state varchar(15),
9 baldue number(10,2),
10 constraint chk_client check (clientno like 'C%'));

Table created.

SQL> desc client_master_23;

Name Null? Type

CLIENTNO NOT NULL VARCHAR2(6)

NAME NOT NULL VARCHAR2(15)
ADDRESS1 VARCHAR2(5)
ADDRESS2 VARCHAR2(5)
CITY VARCHAR2(15)
PINCODE NUMBER(6)
STATE VARCHAR2(15)
BALDUE NUMBER(10,2)

*INSERTING THE FOLLOWING DATA INTO THEIR RESPACTIVE TABLES:

SQL> select * from client_master_23;

CLIENT NAME ADDRE ADDRE CITY PINCODE STATE BALDUE

C00001 Rakesh Joshi Mumbai 400054 Maharashtra 15000

C00002 Mayur Patel Madras 780001 Tamilnadu 0
C00003 Ishita Mehata Mumbai 400057 Maharashtra 5000
C00004 Amit Solanki Bangalore 560001 Karnataka 0
C00005 Hiren Pandya Mumbai 400060 Maharashtra 2000
C00006 Dipak Sharma Mangalore 560050 Karnataka 0

6 rows selected.
[2]PRODUCT _MASTER_23:

SQL> create table product_master_23

2 (productno varchar(6)primary key,
3 description varchar(15)not null,
4 profitpercent number(4,2)not null,
5 unitmaster varchar(7)not null,
6 qtyonhand number(4)not null,
7 reorderlvl number(3)not null,
8 sellprice number(5,2)not null,
9 costprice number(5,2)not null,
10 constraint chk_productno check (productno like 'P%'),
11 constraint chk_sellprice check (sellprice!=0),
12 constraint chk_costprice check (costprice!=0));

Table created.

SQL> desc product_master_23;

Name Null? Type

PRODUCTNO NOT NULL VARCHAR2(6)

DESCRIPTION NOT NULL VARCHAR2(15)
PROFITPERCENT NOT NULL NUMBER(4,2)
UNITMASTER NOT NULL VARCHAR2(7)
QTYONHAND NOT NULL NUMBER(4)
REORDERLVL NOT NULL NUMBER(3)
SELLPRICE NOT NULL NUMBER(5,2)
COSTPRICE NOT NULL NUMBER(5,2)

*INSERTING THE FOLLOWING DATA INTO THEIR RESPACTIVE TABLES:

SQL> select * from product_master_23;

[3]SALESMAN _MASTER_23:

SQL> create table salesman_master_23

2 (salesmanno varchar(6)primary key,
3 salesmanname varchar(15)not null,
4 address1 varchar(10)not null,
5 address2 varchar(10),
6 city varchar(10),
7 pincode number(6),
8 state varchar(12),
9 salamt number(8,2)not null,
10 tgttoget number(6,2)not null,
11 ytdsales number(6,2)not null,
12 remarks varchar(10),
13 constraint chk_salesmanno check(salesmanno like 'S%'),
14 constraint chk_salamt check(salamt!=0),
15 constraint chk_tgttoget check(tgttoget!=0),
16 constraint chk_ytdsales check(ytdsales!=0));

Table created.

SQL> desc salesman_master_23;

Name Null? Type

SALESMANNO NOT NULL VARCHAR2(6)

SALESMANNAME NOT NULL VARCHAR2(15)
ADDRESS1 NOT NULL VARCHAR2(10)
ADDRESS2 VARCHAR2(10)
CITY VARCHAR2(10)
PINCODE NUMBER(6)
STATE VARCHAR2(12)
SALAMT NOT NULL NUMBER(8,2)
TGTTOGET NOT NULL NUMBER(6,2)
YTDSALES NOT NULL NUMBER(6,2)
REMARKS VARCHAR2(10)

*INSERTING THE FOLLOWING DATA INTO THEIR RESPACTIVE TABLES:

SQL> select * from salesman_master_23;

 [4] SALES _ORDER_23:

SQL> create table sales_order_23

2 (orderno varchar(6)primary key,
3 clientno varchar(6) references client_master_23,
4 orderdate date not null,
5 delyaddr varchar(20),
6 salesmanno varchar(6) references salesman_master_23,
7 delytype char(1),
8 billyn char(1),
9 delydate date,
10 orderstatus varchar(10),
11 constraint chk_orderno check (orderno like 'O%'),
12 constraint chk_delytype check (delytype in('p','f')),
13 constraint chk_delydate check (delydate>orderdate),
14 constraint chk_orderstatus check (orderstatus in('in process','fulfilled','backorder','cancelled')));

Table created.

SQL> desc sales_order_23;

Name Null? Type

ORDERNO NOT NULL VARCHAR2(6)

CLIENTNO VARCHAR2(6)
ORDERDATE NOT NULL DATE
DELYADDR VARCHAR2(20)
SALESMANNO VARCHAR2(6)
DELYTYPE CHAR(1)
BILLYN CHAR(1)
DELYDATE DATE
ORDERSTATUS VARCHAR2(10)

*INSERTING THE FOLLOWING DATA INTO THEIR RESPACTIVE TABLES:

SQL> select * from sales_order_23;

 [5] SALES_ ORDER _DEATILS_23:

SQL> create table sales_order_details_23

2 (orderno varchar(6) references sales_order_23,
3 productno varchar(6) references product_master_23,
4 qtyordered number(8),
5 qtydisp number(8),
6 productrate number(10,2));

Table created.

SQL> desc sales_order_details_23;

Name Null? Type

ORDERNO VARCHAR2(6)
PRODUCTNO VARCHAR2(6)
QTYORDERED NUMBER(8)
QTYDISP NUMBER(8)
PRODUCTRATE NUMBER(10,2)

* INSERTING THE FOLLOWING DATA INTO THEIR RESPACTIVE TABLES:

SQL> select * from sales_order_details_23;