Oracle Project. Data Base.

ORACLE PROJECT. Data Base. A logically collected data is called data bade. For example our phone directory our address book . Data base is very important part of management the account system .Today number of software’s are used for data base management but the best software use for data base management that is oracle. We are maintain our number of data on it. Today it is used on a large scale.

ORACLE. Oracle is a software development program. Oracle is best software use for data base management. Today in Pakistan oracle use approximately 90%. It is vastly use in our industries and department stores. Oracle is very important part of our data base management.

1. SQL. (Structure Query Language). 2. Form. 3. Report. SQL. (Structure Query Language). SQL is the first step of oracle. It is use for table creation. It tells us how to create a table. SQL Structure Query Language is an important part of oracle. In SQL we can add table and delete the table. All enter in the SQL in the form of rows and columns.

Form. Form is the second step of oracle. Use for data base management. In form we can enter the data. All the data entries we can enter in the form. For

this purpose we make a complete structure for making a full form. We can enter a number of data in form. Data we are entering in form always save in the SQL.

Report. Report is the third step of oracle. By use of report we can get the feed back of data we enter in the form and in the SQL. It is use for data base presentation. It is also an important step of oracle.

AFTER INSTALATION. After installation of oracle first of all we open the SQL from start menu. Here we give it a password and user name that is make by default in it.

User Name: Scott Password: Tiger COMMANDS. Select * from tab; This command is use for table show made in the user Scott. This command show all the table of scott . For example. Emp. Salgrade. Bounus. Dept. These are the tables made in the user scott .

Select * from emp; This command is use for specific table show. We can see all the rows and columns of the table of emp. For example. Eno Ename Edate 124 ALI 01102009 128 Aqib 02102009 This is a table of emp in scott.

Mgr 100 150

Sal 200 300

Commission 150 270

Here * means all the columns and rows of the table. Select * from emp Where eno=22; This command is use for specific columns show. This command show the data of eno 22. For example. Eno Ename Edate Mgr 124 Ali 01102009 100

Sal 200

Commission 120

Select * from emp Where ename=Ali; Eno 123

Ename Ali

Edate 01102009

Mgr 112

Sal 200

Commission 321

This command show the data by name this command show the data of Ali.

Select * from emp Where sal= 200; This command is use for sal show. For Greater then 500 Sal. Select * from emp Where sal>500; This command is use for greater the 500 salary show. For Example. Eno 123

Ename Edate Mar Sal Commission Ali 01102009 200 100 150 For less then 500 Sal. Select * from emp Where sal<500;

This command is used for less then 500 salary show. For Example. Eno 123

Ename Edate Mar Ali 01102009 100

For equal salary show. Select * from emp Where sal=500;

Sal 300

Commission 250

For Example. Eno Ename Edate Mar Sal Commission 122 Ali 01102009 100 112 331 For greater then or equal to salary show. Select * from emp Where sal >=500; For Example. Eno Ename Edate Mar 123 Ali 01102009 149

Sal 123

Commission 500

This command is used for greater then or equal to salary show. For less then or equal to salary show. Select * from emp Where sal <=500; For Example. Eno 123

Ename Edate Mar Ali 01102009 200

Sal Commission 100 150

For not equal to salary show. Select * from emp Where sal<>500; For Example. Eno 122

Ename Edate Ali 02102009

Mar 100

Sal 200

Commission 150

These are some main commands which we can use these conditional operations. These conditional operators are used for data show from a table in different theories.

ED. By using this command we can changes the old command. SQL>ed After changing the command save him and close it. And press / for taking the result. SQL>/.

Data Types. There are three types of data enter in the oracle. 1. Number. (1,2,3,4…….). 2. Varchar2 (ali,1,2,3,4…..). 3. Date (Date). For table structure show. SQL>desc emp; This command show the table structure. For Example.

Structure

Data Types

Eno Ename Job Mgr Edate Sal Commission Deptno

Number(5) Varchar2(7) Varchar2(6) Number(6) Date Number(60) Number(44) Number(22)

For 300 t0 500 salary show Select * from emp Where sal between 300 and 500; This command show the salary of all the employ that between the 300 and 500. For Example. Eno 1 2

Ename Edate Ali 01102009 Aqib 02102009

Sal 100 200

Comm 50 100

Deptno stitching stitching

For Maximum Salary Show. Select max(sal) from emp; This command show the maximum salary in the table of emp. For Example. Eno 2

Ename Edate Mgr Ali 01102009 110

Sal Comm 100 500

Deptno Stitching

For Minimum Salary show. Select min(sal) from emp; This command show the minimum salary in the table of emp. For Example. Eno 1

Ename Edate Ali 01102009

Mgr 200

Sal 329

Comm 234

Deptno Stitching

For Average salary show. Select avg(sal) from emp; This command show the average of all salary in the table of emp; For Example. 200+300=500/2=250. This is average salary of a emp.

Keys Types. There are three types of keys. 1. Primary Keys. 2. Unique Keys. 3. Foreign Keys. 1). Primary Keys. A field that is never repeated, not matched and not null called the primary keys.

2). Unique Keys. A field that never repeated but may be null. 3).Foreign Keys. The key that meets the tow tables called the foreign keys.

How To Create A User. We connect with user name (system). Password(manager).

For new user connection. Create user Ali Identified by Ali; User is created and for giving rights. Grant dba to Ali; Grant succeeded. After giving rights close the SQL and open it again now give it your new user name Ali and password Ali. How Create A Table. Create table dept( Dcode number(6), Dname varchar2(6)); Table created.

For enter values from SQL. Insert into dept values (1, ‘us’); For Example. Dcode Dname 1 us Character and varchar2 should always written in single quotation(‘ ‘).

Original Project. We create 7 tables in our original project. 1. Rcptmst. 2. Rcptdtl. 3. Issuemst. 4. Issuedtl. 5. Dept. 6. Item. 7. Supp. For delete table. Drop table rcptmst; For specific row or column delete. Delete from table Where dcode=1.

For Example. Dcode 1 2

Dname Ali Aqib

For specific row or column watching. Select * from dept Where dcode in (1,2) For Example. Dcode 1 2

Dname Ali Aqib Table of receipt.

Rno= 1, Rdate= date, Supplier= Ali, Dept= us, We keep the table name is Rcptmst. We called the master portion. Rno= Item= Qty= Rate=

1. pen, 20, 10,

We keep the table name is Rcptdtl. We called the detail portion.

Table of Issue. Issue no= 1, Issue date= date, Dept= us, Person= Ali, We keep the table name is Issuemst. We called the master portion. Issue no= 1, Item= pen, Qty= 20, We keep the table name is Issuedtl. We called the detail portion.

Tables. Create table Rcptmst( Rno number(5), Rdate date, Scode number(5), Dcode number(6)); Create table Rcptdtl( Rno number(5), Item number(5), Qty number(6), Rate number(5)); Create table Issuemst( Issueno number(5), Issuedate date, Person varchar2(5), Dcode number(5));

Create table Issuedtl( Issueno number(5), Icode number(6), Qty number(6)); Create table Dept( Dcode number(6), Dname varchar2(6)); Create table Item( Icode number(5), Iname varchar2(6)); Create table Supplier( Scode number(5), Sname varchar2(6));