68. Examples - 1

1.Create a table 'employee' with the following fields

empno          varchar2           6(primary key)

ename          varchar2           20(not null)

job            char               10

hiredate       date             

basic_sal      number             (9,2)

comm           number             (7,2)

dept_no        varchar2           2

Q:

create table employee(empno varchar2(6) primary key,

                      ename varchar2(20) not null,

                      job char(10),

                      hiredate date,

                      basic_sal number(9,2),

                      comm number(7,2),

                      dept_no varchar2(2));

2.Describe the table 'employee'

Q: desc employee

3.Insert the following data into the above 'employee' table

empno	ename	job	hiredate	basic_sal	comm	dept_no
E0001	kim	Manager	15-dec-02	5000	500	D001
E0002	Bruce	Analyst	24-Apr-99	4000	400	D002
E0003	Arnold	Clerk	10-jan-01	2500	250	D004
E0004	Holyfield	Tester	10-oct-01	3000	300	D002
E0005	kelly	Admin	11-apr-99	2000	200	D003

Q:

 insert into employee values('E0001','Kim','Manager',

                  '15-Dec-02','5000.00','500','D001');   

 insert into employee values('E0002','Bruce','Analyst',

                  '24-Apr-99','4000','400','D002');

 insert into employee values('E0003','Arnold','Clerk',

                  '10-jan-01','2500','250','D004');

 insert into employee values('E0004','Holyfield','Tester',

                  '10-oct-01','3000','300','D002');

 insert into employee values('E0005','Kelly','Admin',

                  '11-Apr-99','2000','200','D003');

4.Create a table 'depttable' with the following fields

       deptno          varchar2           8

       dname           varchar2           20

       loc             varchar2           20
 

Q: create table depttable(deptno varchar2(8),

                   dname varchar2(20),

                   loc varchar2(20));

5. Describe the table 'depttable'

Q: desc depttable

6. insert the following data into the above 'depttable' table
 

deptno	dname	loc
d001	Development	Noida
d002	R & D	Rocky Creek
d003	Admin	Seattle
d004	Accounts	Seattle
d005	Production	Noida

Q:  insert into depttable values('D001','Development','Noida');

    insert into depttable values('D002','R And D',

                                 'Rocky Creek');

    insert into depttable values('D003','Admin','Seattle');

    insert into depttable values('D004','Accounts','Seattle');

    insert into depttable values('D005','Production','Noida');

7. Create a table 'client_mast' with the following fields

client_no      varchar2           6(primary key)

name           varchar2           20(not null)

address        varchar2           25

city           varchar2           20  

pincode        number             6

state          varchar2           15

bal_due        number             (8,2)

Q:

create table client_mast(client_no varchar2(6) primary key,

                        name varchar2(20) not null,

                         address varchar2(25),

                         city varchar2(20),

                         pincode number(6),

                         state varchar2(15),

                         bal_due number(8,2));

8. Describe table client_mast

Q: desc client_mast

9. insert the following data into the above 'client_mast' table

client_no	name	address	city	pincode	state	bal_due
B001	Procurez	12,Sunbay street	Gainesville	1233	Florida	3500
B002	BMW	6, Rocky creek	Jacksonville	1234	Florida	3488
B003	Takenote	7,Hudsonbay	Puria	6454	Illinois	4555
B004	Teoco	1243,Princiton circle	Fairfax	3433	Virginia	4433
B005	ASAP	23,North city	Puria	4354	Illinois	3600

Q:  insert into client_mast values('B001','Procurez','12,sunbay street',

                   'Gainesville','1233','Florida','3500');

    insert into client_mast values('B002','BMW','6,Rocky creek',

                   'Jacksonville','1234','Florida','3488');

    insert into client_mast values('B003','Takenote','7,Hudsonbay',

                  'Puria','6454','Illinois','4555'); 

    insert into client_mast values('B004','Teoco','1243,Princiton circle',

                  'Fair fax','3433','Virginia','4433');

    insert into client_mast values('B005','ASAP','23,North city',

                 'Puria','4354','Illinois','3600');

10. Create a table 'product_mast' with the following fields

prodcut_no        varchar2           6

description       varchar2           14

profit_perc       number             (5,2)

units             varchar2           10  

qty_available     number             6

sell_price        number             (7,2)

cost_price        number             (7,2)

Q: create table product_mast(product_no varchar2(10),

                             description varchar2(20),

                             profit_perc number(5,2),

                             units varchar2(10),

                             qty_available number(6),

                             sell_price number(7,2),

                             cost_price number(7,2));

11. Describe table product_mast

Q: desc product_mast

12. insert the following data into the above

   'product_mast' table

prodcut_no	description	profit_perc	units	qty_available	sell_price	cost_price
pro23	CD Player	5	Piece	100	210	200
pro45	Television	10	Piece	35	1320	1200
pro55	Refrigerator	15	piece	60	920	800
pro65	Oven	12	Piece	40	504	450
pro75	Microwave	20	piece	100	900	750

Q: insert into product_mast values('&product_no',

            '&description','&profit_perc','&units',

            '&qty_available','&sell_price',

            '&cost_price');

13. Create a table 'salesman_mast' with the following fields

salesman_no        varchar2           10

name               varchar2           20(not null)

address            varchar2           20

city               varchar2           18  

pincode            number             6

state              varchar2           15

sal_amount         number             (8,2)

target             number             (5,2)

Q: create table salesman_mast(salesman_no varchar2(10),

                           name varchar2(20) not null,

                           address varchar2(20),

                           city varchar2(18),

                           pincode number(6),

                           state varchar2(15),

                           sal_amount number(8,2),

                           target number(5,2));

14. Describe table salesman_mast

Q: desc salesman_mast

15. insert the following data into the above 'salesman_mast' table

salesman_no	name	address	city	pincode	state	sal_amount	target
sa001	Sammy	43,NW	Jacksonville	1234	Florida	2500	200
sa002	aron	2, Avenue	Gainesville	1233	Florida	2500	200
sa003	Quincy	2,moon App	Janesville	1235	Florida	2500	200
sa004	Teddy	3,Silver-spoons	Harryville	1233	Tennessee	2300	200
sa005	Byron	4,st.street	Puria	12	Illinois	3500	100

Q:  insert into salesman_mast values('&salesman_no','&name',

      '&address','&city','&pincode','&state','&sal_amount',

      '&target');