DDL & DML COMMANDS 4

SQL> create table sales_order_details(s_order_no number(4) primary key,product_no number(4),description varchar(10),qty_ordered number(5),qty_disp number(5),product_rate number(5,2),profit_percent number(2),sell_price number(5,2),supplier_name varchar(20));

Table created.

SQL> desc sales_order_details;

Name	Null?	Type
S_ORDER_NO	NOT NULL	NUMBER(4)
PRODUCT_NO		NUMBER(4)
DESCRIPTION		VARCHAR2(10)
QTY_ORDERED		NUMBER(5)
QTY_DISP		NUMBER(5)
PRODUCT_RATE		NUMBER(5,2)
PROFIT_PERCENT		NUMBER(2)
SELL_PRICE		NUMBER(5,2)
SUPPLIER_NAME		VARCHAR2(20)

SQL> insert into sales_order_details values(1,25,'watch',50,60,100,25,24.5,'xrx');

1 row created.

SQL> insert into sales_order_details values(2,36,'pen',60,70,100,50,56.6,'yyy');

1 row created.

SQL> insert into sales_order_details values(3,38,'box',28,23,243,12,122.5,'zhz');

1 row created.

SQL> select * from sales_order_details;

S_ORDER_NO	PRODUCT_NO	DESCRIPTION	QTY_ORDERED	QTY_DISP	PRODUCT_RATE	PROFIT_PERCENT	SELL_PRICE	SUPPLIER_NAME
1	25	Watch	50	60	100	25	24.5	xrx
2	36	Pen	60	70	100	50	56.6	yyy
3	38	Box	28	23	243	12	125.5	zhz

SQL> select product_no,description,sell_price*0.50,sell_price*1.50 from sales_order_details;

PRODUCT_NO	DESCRIPTION	SELL_PRICE*0.50	SELL_PRICE*1.50
25	Watch	12.25	36.75
36	Pen	28.3	84.9
38	box	61.25	183.75

SQL> select product_no,profit_percent from sales_order_details  where profit_percent not between 10 and 20;

_ORDER_NO

PRODUCT_NO	PROFIT_PERCENT	SELL_PRICE
25	25	24.5
36	50	56.6

SQL> select product_no,profit_percent,sell_price from sales_order_details where profit_percent not between 20 and 30;

PRODUCT_NO	DESCRIPTION	PROFIT_PERCENT	SELL_PRICE
36	Pen	50	56.6
38	Box	12	125.5

SQL> select supplier_name,product_no from sales_order_details where supplier_name like  '_r%' or supplier_name  like '_h%';

SUPPLIER_NAME	PRODUCT_NO
xrx	25
zhz	38

DDL & DML COMMANDS 3

SQL> create table sales_order(s_order_no number(4) primary key,product_no number(4),client_no number(4),del_add varchar(20),del_date date,order_status varchar(20));

Table created.

SQL> desc sales_order;

Name	Null?	Type
S_ORDER_NO	NOT NULL	NUMBER(4)
PRODUCT_NO		NUMBER(4)
CLIENT_NO		NUMBER(4)
DEL_ADD		VARCHAR2(20)
DEL_DATE		DATE
ORDER_STATUS		VARCHAR2(20)

SQL> alter table sales_order add salesman_no number(4);

Table altered.

SQL>  desc sales_order;

Name	Null?	Type
S_ORDER_NO	NOT NULL	NUMBER(4)
PRODUCT_NO		NUMBER(4)
CLIENT_NO		NUMBER(4)
DEL_ADD		VARCHAR2(20)
DEL_DATE		DATE
ORDER_STATUS		VARCHAR2(20)
SALESMAN_NO		NUMBER(4)

SQL> insert into sales_order values (1,205,101,'mn street','22-oct-2010','delivered',260);

1 row created.

SQL>  insert into sales_order values (2,203,102,'ab street','28-nov-1989','pending',500);

1 row created.

SQL> insert into sales_order values (3,33,123,'qw street','24-jun-1991','delivered',500);

1 row created.

SQL> create table sales_order_cc(s_order_no number(4) references sales_order(s_order_no),product_no

number(5), qty_order number(5),product_rate number(10,2),primary key(s_order_no,product_no));

Table created.

SQL> desc sales_order_cc;

Name	Null?	Type
S_ORDER_NO	NOT NULL	NUMBER(4)
PRODUCT_NO	NOT NULL	NUMBER(5)
QTY_ORDER		NUMBER(5)
PRODUCT_RATE		NUMBER(10,2)

SQL> create table sales_order_tc(s_order_no number(4),product_no number(4),qty_order number(5),product_rate number(5,2),primary key(s_order_no ,product_no),foreign key(s_order_no) references sales_ord

er);

Table created.

SQL> desc sales_order_tc;

Name	Null?	Type
S_ORDER_NO	NOT NULL	NUMBER(4)
PRODUCT_NO	NOT NULL	NUMBER(5)
QTY_ORDER		NUMBER(5)
PRODUCT_RATE		NUMBER(10,2)

SQL> alter table sales_order add check(product_no >0);

Table altered.