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