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.

Related Posts with Thumbnails