Oracle 10g Lab Simple

DBMS Lab Oracle 10g
Introduction
Database
A database can be defined as a collection of coherent, meaningful data.
RDBMS
A Data Base Management System or DBMS consists of a collection of interrelated data and a set of programs to access those data. A Relational Database Management System is a program that lets you create, update and administrator a relational database. The primary rule for RDBMS is that the Data should be stored in the form of tables. Most of the RDBMSs use the Structured Query Language to access the database. Different products of RDBMS are: ORACLE SQL Server DB2 UDB MySQL Sybase Teradata Oracle Corporation. Microsoft Corporation. IBM MySQL Sybase NCR
REVIEWED
By Jerrin at 8:58:32 PM, 23/6/2013
PROMOTIONAL COPY
ORACLE
In June 1970, Dr. E.F. Codd published a paper entitled A Relational Model of Data for Large Shared Data Banks. This relational model, sponsored by IBM, then came to be accepted as the definitive model for RDBMS. The language developed by IBM to manipulate the data stored within Codds model was originally called Structured English Query Language (SEQUEL) with word English later being dropped in favor Structured Query Language (SQL). In 1979, Oracle Corp. released the first commercially available implementation of SQL.
We are using ORACLE from Oracle Corp. in this Lab.
SQL (Structured Query Language)

This is a common language through which we can interact with the database SQL is classified mainly into following categories. 1. DDL (Data Definition Language) CREATE ALTER DROP TRUNCATE COMMENT To create objects in the database. Alters the structure of the database. Delete objects from the database. Remove all records from a table, including all spaces allocated for the records are removed. Add comments to the data dictionary.
1|University College of Applied Sciences, Pathanamthitta
DBMS Lab Oracle 10g
GRANT REVOKE
Gives users access privileges to database. Withdraw access privileges given with GRANT command.
2. DML (Data Manipulation Language) INSERT UPDATE DELETE CALL Insert data into a table. Updates existing data within a table. Deletes all records from a table, the space for the records remain. Call a PL/SQL or Java subprogram.
3. TCL (Transaction Control Language) or DCL (Data Control Language) COMMIT SAVEPOINT ROLLBACK SET TRANSACTION GRANT/REVOKE Save work done. Identify a point in a transaction to which you can later roll back. Restore database to original since the last COMMIT Change transaction options like what rollback segment to use. Grant or take back permissions to or from the oracle users.
PROMOTIONAL COPY
4. DQL (Data Query Language statement) SELECT Retrieve data from a database.
Oracle Data Types

Data Type Description CHAR(size) To store character strings values of fixed length. (Max. 255 characters). VARCHAR(size)/ Store variable length alphanumeric data. Its a more flexible form of the CHAR type. VARCHAR2(size) (Max. 4000 characters). Oracle compares VARCHAR values using non-padded comparison semantics i.e. the inserted values will not be padded with spaces. It also represents data of type String, yet stores this data in variable length format. DATE To represent date and time. The standard format is DD-MON-YY. Date Time stores date in the 24-hour format. NUMBER(P,S) To store numbers (fixed or floating point). Numbers of virtually any magnitude may be stored up to 38 digits of precision. The precision (P), determines the maximum length of the data, whereas the scale (S), determines the number of places to the right of the decimal. LONG Store variable length character strings containing up to 2 GB. Can be used to store arrays of binary data in ASCII format. Only one LONG can be used per table. RAW/ To store binary data, such as digitized picture or image. LONG RAW
DBMS Lab Oracle 10g
Constraints
Constraint restricts the values that the table can store. We can declare integrity constraints at the table level or column level. There are 5 constraints: 1. Not Null If a column in a table is specified as Not Null, then its not possible to insert a null in such a column. It can be implemented with create and alter commands. 2. Unique Key It doesnt allow duplicate values in a column. 3. Check Used to restrict the values before inserting into a table. 4. Primary Key The key column with which we can identify the entire Table is called as a primary key column. A PK is a combination of Unique and Not Null constraint; it will not allow null and duplicate values. A table can have only one primary key. A primary key can be declared on two or more columns as a Composite Primary Key.
PROMOTIONAL COPY
Columns defined as foreign keys refer the Primary Key of other tables. The Foreign Key points to a primary key of another table, guaranteeing that you cant enter data into a table unless the referenced table has the data already which enforces the REFERENTIAL INTEGRITY.
5. Foreign Key
Data Definition Language Syntax CREATE

CREATE TABLE <table name> ( <column_name1> <data type>(<size>) [<constraint>], <column_name2> <data type>(<size>), . <column_nameN> <data type>(<size>), );
DBMS Lab Oracle 10g
ALTER
Adding New Columns:
ALTER TABLE <Table Name> ADD(<New Column Name> <Data type>(<Size>), <New Column Name> <Data type>(<Size>),);
Dropping a column from a table:

ALTER TABLE <Table Name> DROP COLUMN <Column Name>;
Modifying Existing Columns:

ALTER TABLE <Table Name> MODIFY (<Column Name> <New Data type>(<New Size>));
RENAME
RENAME <Table Name> TO <New Table Name>;
TRUNCATE
TRUNCATE TABLE <Table Name>;
PROMOTIONAL COPY
DROP
DROP TABLE <Table Name>;
Data Manipulation Language Syntax INSERT

INSERT INTO <Table Name> (<col_name1>,<col_name2>) VALUES(<expression1>,<expression2>);
DELETE
Removal of all rows:
DELETE FROM <Table Name>;
Removal of specific row(s):

DELETE FROM <Table Name> WHERE <Condition>;
DBMS Lab Oracle 10g
UPDATE
Updating all rows:
UPDATE <Table Name> SET <Col_name1> = <Exp1>, <Col_name2> = <Exp2>;
Updating Records conditionally:

UPDATE <Table Name> SET <Col_name1> = <Exp1>, <Col_name2> =<Exp2> WHERE <Condition>;
Data Query Language Syntax

SELECT
PROMOTIONAL COPY
Figure: Syntax of Select.
DBMS Lab Oracle 10g
Transaction Control Language Syntax

COMMIT
COMMIT;
SAVE POINT
SAVEPOINT <Save point name>;
ROLLBACK
ROLLBACK TO <Already defined save point name>;
Aggregate Functions
AVG MIN COUNT(exp) COUNT(*) MAX SUM Returns an average value of n, ignoring null values in a column Return a minimum values of expression Return the number of rows where expression is not null Returns the no. of rows in the table including duplicate and those with nulls Returns the maximum values of expression Return the sum of the values.
PROMOTIONAL COPY
Oracle Operator Precedence
Operator +, *, / +, -, || =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN NOT AND OR
Operation Identity, Negation Multiplication, Division Addition, Subtraction, Concatenation Comparison Exponentiation, Logical Negation Conjunction Disjunction
DBMS Lab Oracle 10g
Sub Queries
A sub query is a query within a query. The results of the sub query are used by the DBMS to determine the results of the higher-level query that contains the sub query. In the simplest forms of a sub query, the sub query appears within the WHERE or HAVING clause of another SQL statement. Sub queries provide an efficient, natural way to handle query requests that are themselves expressed in terms of the results of other queries.
Pattern Matching
The comparison operator usually compared one value exactly to one another value. This precision may not always we decide or necessary. For this purpose oracle provides like predicate. It allows comparison of one string value with another string value which is not identical. This is achieved by use of wild card character. The wild card characters available are modulus (%) which allows to match any strings of any length and (_) which allows to match a single character. Example:
Select * from emp where ename likea%; Select * from emp where ename like_a%;
Oracle 10g
PROMOTIONAL COPY
Oracle Database 10g transforms data centers from collections of separate, monolithic systems configured to handle individual applications to a smaller number of consolidated, shared pools of server and storage resources called infrastructure grids. Oracles unique scale-out architecture enables applications of all types to dynamically provision additional servers and storage resources as needed to meet their changing processing demands. It delivers dramatic cost savings and equally dramatic advancements in quality of service. Cost savings come from multiple sources. Oracle reduces hardware costs significantly by enabling even the largest systems to be built out of small, low-cost servers and modular storage arrays. New automation capabilities in Oracle Database 10g dramatically decrease the cost of database administration. Also, Oracle full stack solutions eliminate the need for costly 3rd party management tools and utilities. The advancements in quality of service are across the board. Particularly significant is the automation of database administration functions that raise usability to a revolutionary new level. Not only are DBAs vastly more productive, they are empowered to deliver new levels of consistently optimized performance, high availability, and more. Oracle Database 10g further increases Oracles lead in virtually every area of database capability. This includes high availability, performance, business intelligence, data movement, security, application development, and more.
Oracle Database 10g is revolutionary. It is Oracles largest introduction of new functionality ever, beginning with Release 1 of Oracle Database 10g in January 2004 and now Release 2 in 2005. It is Oracles most innovative release, leading the database industry into entirely new ground in clustering, automation, high availability, and more. And, it is having the largest impact on computing of any database software release. Oracle Database 10g fundamentally changes the way data centers look and operate
DBMS Lab Oracle 10g
Oracle 10g Installation and Configuration

Follow these steps to install and configure Oracle 10g Enterprise Edition. Run the Installation Setup (Setup.exe). Select Advanced Installation from the Welcome window.
PROMOTIONAL COPY
The File Locations window opens. Change the Destination Name to: OraHome10 Change the Destination Path to c:\oracle\product\10.1.0\OraHome10 Click the Next button.
DBMS Lab Oracle 10g
Select Personal Edition (804 MB) from the Select Installation Type windows that appears. Click Next
PROMOTIONAL COPY
The Select Database Configuration window opens. Select General Purpose and click Next.
DBMS Lab Oracle 10g
The Specify Database Configuration Options window opens. Enter: Global Database Name: DB1 SID: DB1 Select Create database with samples schemas and click Next.
PROMOTIONAL COPY
The Select Database Management Option window opens; simply click Next. The Specify Database File Storage Option window opens. Keep the default or select a different directory for your database files and click Next.
10 | U n i v e r s i t y C o l l e g e o f A p p l i e d S c i e n c e s , P a t h a n a m t h i t t a
DBMS Lab Oracle 10g
The Specify Backup and Recovery Options opens. Keep Default Settings and click Next. Then, the Specify Database Schema Passwords window opens. Select Use the same password for all the accounts. In the Enter Password and Confirm Password fields type your password. Click Next.
PROMOTIONAL COPY
The Summary window opens; click Install.
DBMS Lab Oracle 10g
The Install opens. The time to install depends upon the speed and memory of your computer and may take more than 45 minutes. Dont be alarmed if the installer seems to be stuck at a certain percentage during the installation. You can also ignore additional windows that open during installation.
PROMOTIONAL COPY
After some time, End of Installation window will appear. Click Exit and confirm exit. You have successfully installed Oracle 10g.
DBMS Lab Oracle 10g
Now, weve installed Oracle 10g. Then we can configure the database as our wish by going to the Database Home Page. We can log in with the SYSTEM account, with username system and password that we set during installation.
For Example, to unlock the HR user, Select Manage Users.
PROMOTIONAL COPY
Click HR user, and provide a password, select Unlocked from Account status and click Alter User. We can also set storage settings, add users, create tables etc from the Home Page.
DBMS Lab Oracle 10g
Table Design using Foreign Key and Normalization

Normalization
Normalization is a process that helps analysis or database designers to design table structures for an application. The focus of normalization is to attempt to reduce redundant table data to the very minimum. Through the normalization process, the collection of data in a single table is replaced, by the same data being distributed over multiple tables with a specific relationship being setup between the tables. By this process RDBMS schema designers try their best to reduce table data to the very minimum. Normalization is carried out for the following reasons: To structure the data between tables so that data maintenance is simplified. To allow data retrieval at optimal speed. To simplify data maintenance through updates, inserts and deletes. To reduce the need to restructure tables as new application requirements arise. To improve the quality of design for an application by rationalization of table data.
Normalization is a technique that: Decomposes data into two-dimensional tables. Eliminates any relationships in which table data does fully depend upon the primary key of a record. Eliminates any relationship that contains transitive dependencies.
PROMOTIONAL COPY
First Normal Form
When a table is decomposed into two-dimensional tables with all repeating groups of data eliminated, the table data is said to be in its first normal form. The repetitive portion of data belonging to the record is termed as repeating groups. Example: Table(Structure): EmpProj Field Key Type Project number -Project name -Employee number -1-n Employee name -1-n Rate category -1-n Hourly rate -1-n Hint: 1-n indicates that there are many occurrence of this field.
DBMS Lab Oracle 10g
Data help in the above table structure: Project number P001 P001 P001 P002 P002 Project name Using MySQL On Linux Using MySL On Linux Using MySQL On Linux Using Star Office On Linux Using Star Office On Linux Employee number E001 E002 E006 E001 E007 Employee name Sharanam Shah Vaishali Shah Hansel Colaco Sharanam Shah Chhaya Bankar Rate category A B C A B Hourly rate 7000 6500 4500 7000 4000
In the above data there are a few problems: The Project name in the second record is misspelled. This can be solved by removing duplicates. Do this using normalization. Data is repeated and thus occupies more space. A table is in First Normal Form if: There are no repeating groups. All the key attributes are defined. All attributes are depend on Primary Key. So far there are no keys, and there are repeating groups. So remove the repeating groups, and define the primary key.
PROMOTIONAL COPY
The unnormalized data i n the first table is the entire table. A key that will uniquely identify each record should be assigned to the table. This key has to be unique because it should be capable of identifying any specific row from the table for extracting information for use. This key is called the tables Primary Key. This table is in First Normal Form. Field Project number Project name Employee number Employee name Rate category Hourly rate Key Primary Key -Primary Key ----
To convert to First Normal Form:
Second Normal Form

A table is said to be in its second normal form when each record in the table is in the first normal form and each column in the record is fully dependent on its primary key. It includes no partial dependencies (where an attribute is dependent on only a part of a primary key). Steps to convert a table to its Second Normal Form: Find and remove fields that are related to the only part of the key. Group the removed items in another table. Assign the new table with the key i.e. part of a whole composite key. In the above example: Project name is only dependent on Project number. Employee name, Rate category and Hourly rate are dependent only on Employee number.
DBMS Lab Oracle 10g
To convert, remove and place these fields in a separate table, with the key being that part of the original key they are depend on. This leads to the following 3 tables: Table: EmpProj Field Project number Employee number Table: Proj Field Key Project number Primary Key Project name -nd Table is now in 2 normal form. Key Primary Key Primary Key Table: Emp Field Employee number Employee name Rate category Hourly rate Key Primary Key ----
Third Normal Form

Table data is said to be in third normal form when all transitive dependencies (where a nonkey attribute is dependent on another non-key attribute) are removed from this data.
PROMOTIONAL COPY
A, B, C are three columns in table. If C is related to B, If B is related to A, Then C is indirectly related to A This is when transitive dependency exists. To convert such data to its 3rd normal form, remove this transitive dependency by splitting each relation in two separate relations. This means that data in columns A, B, C must be placed in three separate tables, which are linked using a Foreign Key. In the above example: Employee table is the only one with more than one non-key attribute. Employee name is not dependent on either Rate category or Hourly rate. Hourly rate is dependent on Rate category.
A general case of transitive dependencies is as follows:
To convert the table into the 3rd normal form, remove and place these fields in a separate table, with the attribute it was dependent on as key, as follows:
DBMS Lab Oracle 10g
This leads to the following 4 tables: Table: EmpProj Field Project number Employee number Table: Emp Field Employee number Employee name Rate category Table: Rate Field Rate category Hourly rate Table: Proj Key Primary Key -Key Primary Key --Key Primary Key Primary Key
PROMOTIONAL COPY
These tables are all now in their 3rd normal form, and ready to be implemented. There are other normal forms such as Boyce-Codd Normal Form, Fourth, Fifth Normal forms, but these are rarely used for business applications. In most cases, tables are in their 3rd normal form are already conform to these type of tables formats anyway.
Field Project number Project name
Key Primary Key --
DBMS Lab Oracle 10g
Working with SQL

Table Creation and Alteration
Create two tables using primary key and connect them using foreign key.
Populate the tables using Insert statement and display them using Select.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
Alter the table using ALTER command.
Adding new column:
Modifying a column:
PROMOTIONAL COPY
The structures of the tables are:
Defining Integrity constraints:
DBMS Lab Oracle 10g
Now, we alter it to add Integrity Constraints.
Check Constraints
PROMOTIONAL COPY
Create a table studdetail with CHECK constraints.
A check constraint is a search condition, like the search condition in a WHERE clause, that produces a true or false value. When a check constraint is specified for a column, the DBMS automatically checks the value of that column each time a new row is inserted or a row is updated to ensure that the search condition is true. If not, the INSERT or UPDATE statement fails.
DBMS Lab Oracle 10g
Insertion
Create and maintain a table customers. Illustrate insert, delete and update queries.
Insert query, standard form:
Insert query, alternate method:
PROMOTIONAL COPY
Now, we insert all values we need.
DBMS Lab Oracle 10g
Data Retrieval
We use the SELECT query to retrieve data from a table. Retrieve all rows from table customers.
PROMOTIONAL COPY
Find customers whose city is cochi and date of join is before 5th May 2012.
Select names of customers whose state is Kerala.
Find customers from Tamilnadu and Karnataka.
DBMS Lab Oracle 10g
Find customers from cochi,Trivandrum and Banglore.
Find customers who are not from Kerala or Tamilnadu.
Updating
PROMOTIONAL COPY
Change balance of customer number c01 to 60000. Change name of customer to James whose customer number is c05.
DBMS Lab Oracle 10g
Deletion
Delete customers whose balance is less than 60000.
Group By
Use group by clause to the following table.
PROMOTIONAL COPY
Select number of employees in each branch.
Having Clause
Display customer number and number of accounts (both SB and CA) from following table.
DBMS Lab Oracle 10g
Aggregate Functions
Table accmaster is used to demonstrate aggregate functions.
Find average balance of customers.
Find minimum balance from the table.
Find maximum balance from the table.
PROMOTIONAL COPY
Find the number of accounts by counting accno. Find the number of accounts by counting rows.
Calculate the total balance.
DBMS Lab Oracle 10g
Numeric Functions
Absolute
Power
Round
Square Root
PROMOTIONAL COPY
Greatest Least
Modulus
DBMS Lab Oracle 10g
Truncate
Floor
Ceil
String Functions
LOWER
PROMOTIONAL COPY
UPPER SUBSTR ASCII
INITCAP
INSTR
DBMS Lab Oracle 10g
LENGTH
LTRIM
RTRIM
LPAD
RPAD
PROMOTIONAL COPY
Conversion Functions
Table oldprice is used to demonstrate conversion functions.
TO_NUMBER
DBMS Lab Oracle 10g
TO_CHAR
TO_DATE
Date Functions Display date after 4 months from current date.
PROMOTIONAL COPY
Display the last date of current month. Find date of next Friday from current date.
Find the number of months between two dates.
ROUND Date.
DBMS Lab Oracle 10g
SET Operators
UNION UNION INTERSECT MINUS
Multiple queries can be put together and their output can be combined using union. The union clause merges the output of two or more queries into a single set of rows and column. Tables used: Employee Address Customer
PROMOTIONAL COPY
INTERSECT The intersect clause outputs only rows produced by the queries intersected i.e., it will include only those rows that are retrieved common to both the queries. Table used: account
DBMS Lab Oracle 10g
MINUS It outputs the rows produced by the first query after filtering the rows retrieved by second query. Table account (listed above) is used to illustrate minus.
Sorting Table
Consider the following table:
Sort the table on the basis of BRANCH_NO.
PROMOTIONAL COPY
Sort the table on the basis of BRANCH_NO in descending order.
Sort the table on the basis of BRANCH_NAME.
DBMS Lab Oracle 10g
Sub Query
Three tables are used: movie
customers
PROMOTIONAL COPY
transaction The table transaction have reference to both movie and customer tables. After inserting values:
DBMS Lab Oracle 10g
Using Sub Queries, retrieve data connecting above tables.
Display the transaction details of the movie FAST AND FURIOUS.
Find the name of the customer who has the movie FAST AND FURIOUS.
Find the language of the movie which is with V!5HNU.
PROMOTIONAL COPY
Details of movie which is with R3NJ1TH. Details of customers who has the movie HARRY POTTER.
DBMS Lab Oracle 10g
Joining Tables
The process of forming pairs of rows by matching the contents of related columns is called joining the tables. The resulting table (containing data from both of the original tables) is called a join between the two tables. A join based on an exact match between two columns is more precisely called an equi-join.
Single Join
Create two tables as follows and insert values:
After insertion:
PROMOTIONAL COPY
Joining both tables:
DBMS Lab Oracle 10g
Self Join
In some situations, it is necessary to join a table to itself, as though joining two separate tables. This is referred to as a self-join. In a self-join, two rows from the same table combine to form a result row.
Outer Join
The SQL join operation combines information from two tables by forming pairs of related rows from the two tables where the matching columns in each of the tables have the same values. Create two tables:
PROMOTIONAL COPY
Joining:
DBMS Lab Oracle 10g
Data/Transaction Control Language

Oracle Transactions
A series of one or more SQL statements that are logically related or a series of operations performed on Oracle table data is termed as a Transaction. First, the changes requested are done, to make these changes permanent a COMMIT statement has to be given at the SQL prompt. A ROLLBACK statement given at the SQL prompt can be used to undo a part of or the entire transaction.
Security Management
Depending on a users status and responsibility, appropriate rights on Oracles resources can be assigned to the user by the DBA. The rights that allow the use of some or all of Oracles resources on the Server are called Privileges. If a user wishes to access any of the objects belonging to another user, the owner of the object will have to give permissions for such access. This is called Granting of Privileges. Privileges once given can be taken back by the owner of the object. This is called Revoking of Privileges. Working with Control commands: Connect using the SYSTEM account.
PROMOTIONAL COPY
Create a user temp and grant necessary privileges. Grant all privileges on table clientmaster to the user temp.
Connect using temp account.
We can access the table clientmaster of SYSTEM account now.
DBMS Lab Oracle 10g
Revoke the create view privilege from the user temp (By logging in as SYSTEM).
Trying to create a view by logging in as temp:
Revoke all privileges from the user temp (By logging in as SYSTEM).
Trying to access the table clientmaster from user temp.
Saving work till now.
PROMOTIONAL COPY
Creating a save point. We have 4 records in clientmaster. We create a save point to this point. We then insert a row to clientmaster.
We didnt find it good to insert such a row, or we want to undo anything that has happened after the save point, we rollback to that point.
Now, the newly created row will be deleted.
DBMS Lab Oracle 10g
Views
A view is a virtual table in the database whose contents are defined by a query. To the database user, the view appears just like a real table, with a set of named columns and rows of data. But unlike a real table, a view does not exist in the database as a stored set of data values. Instead, the rows and columns of data visible through the view are the query results produced by the query that defines the view. SQL creates the illusion of the view by giving the view a name like a table name and storing the definition of the view in the database.
Create a view which contains nomno, acctno and name from nominee table.
Display nomview.
PROMOTIONAL COPY
Work with nomview view as with a table.
Destroy nomview.
DBMS Lab Oracle 10g
Indexes
Indexing a table is an access strategy, i.e., a way to sort and search records in the table. Indexes are essential to improve the speed with which record(s) can be located and retrieved from a table. An index is an ordered list of the contents of a column, (or a group of columns) of a table. When data is inserted in the table, the oracle engine automatically insert the data value in the index. For every data value held in the index, the oracle engine inserts a unique ROWID value; which indicates exactly where the record is stored in the table. The records in the index are stored in the ascending order of the index column. The value of ROWID cannot be set or deleted using the insert or update. The address field of an index is called ROWID. The information in the ROWID column provides the oracle engine about the location of the table and a specific record in the oracle database.
Simple Index
An index created on a single column of a table is called a Simple Index.
Create a simple index on cno of table customer.
PROMOTIONAL COPY
Display ROWID of the records in table customer.
Create simple index on cname.
DBMS Lab Oracle 10g
Composite Index
An index created on more than one column is called a Composite Index.
Create composite index on table acc.
PROMOTIONAL COPY
Creating a reverse key index, reverses each byte of column being indexed while keeping the column order. Such an arrangement can avoid performance degradation in indexes where modifications to the index are concentrated on a small set of blocks. By reversing the keys of the index, the insertions become distributed all over the index.
Reverse Index
Create a reverse key index on accno of acc table.
Drop Index
DBMS Lab Oracle 10g
Clusters
Clustering is an important concept for improving Oracle performance. The concept of a cluster is where member records are stored physically near parent records. For Oracle, clusters can be used to define common, one-to-many access paths, and the member rows can be stored on the same database block as their owner row. Clusters are used to store data from different tables in the same physical data blocks. Each cluster stores the tables data, as well as maintains the cluster index that is used to sort table data. The Cluster key: The columns within the cluster index are called the cluster key (i.e. the set of columns that the tables in the cluster have in common). After the cluster has been created, the cluster index is created on the cluster key columns. After the cluster key index has been created, data can be entered into the tables stored in the cluster. As rows are inserted the database will store a cluster key and its associated rows in each of the clusters blocks. Syntax:
CREATE CLUSTER <Cluster Name> (<Column> <Data Type> [, <Column> <Data Type>]) [<Other Options>];
PROMOTIONAL COPY
Example:
CREATE CLUSTER DBA_BANKSYS.BRANCH INFO(BRANCH_NO VARCHAR2(10)); CREATE TABLE DBA_BANKSYS.BRANCH_MSTR( BRANCH_NO VARCHAR2(10) PRIMARY KEY,NAME VARCHAR2(25)) CLUSTER BRANCH_INFO(BRANCH_NO); CREATE TABLE DBA_BANKSYS.ADDR_DTLS( ADDR_NO NUMBER(6) PRIMARY KEY,CODE_NO VARCHAR2(10), ADDR_TYPE VARCHAR2(1),ADDR1 VARCHAR2(50), ADDR2 VARCHAR2(25), CITY VARCHAR2(25), STATE VARCHAR2(25), PINCODE VARCHAR2(6)); CLUSTER BRANCH_INFO(BRANCH_NO);
The cluster name follows the table naming conventions, also column and data type is the name and data type used as cluster key. The column name may be same as one of the columns of a table or it may be any other valid name.
Advantages: Disk I/O is reduced and access time improves for joins of clustered tables. In a cluster, a cluster key value is the values of the cluster key columns for a particular row. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value. Since all rows in clustered tables use the same columns as the common primary key, the columns are stored only once for all tables, yielding some storage benefit.
DBMS Lab Oracle 10g
Sequences
Oracle provides an object called a Sequence that can generate numeric values. The value generated can have a maximum of 38 digits. A sequence can be defined to: Generate numbers in ascending or descending order. Provide intervals between numbers. Caching of sequence numbers in memory to speed up their availability.
A sequence is an independent object and can be used with any table that requires its output.
Creating a Sequence
Syntax:
CREATE SEQUENCE <Sequence Name> [INCREMENT BY <Integer Value> START WITH <Integer Value> MAXVALUE <Integer Value> / NOMAXVALUE MINVALUE <Integer Value> / NOMINVALUE
PROMOTIONAL COPY
CACHE <Integer Value> / NOCACHE ORDER / NOORDER ]
CYCLE / NOCYCLE
Create a sequence.
Get the next value of the sequence.
DBMS Lab Oracle 10g
Create a table addrsdetail and use sequence to generate its primary keys.
To get the current value of a sequence use CurrVal.
Altering a Sequence
Syntax:
ALTER SEQUENCE <SequenceName>
PROMOTIONAL COPY
[INCREMENT BY <IntegerValue> MAXVALUE <IntegerValue> / NOMAXVALUE MINVALUE <IntegerValue> / NOMINVALUE CYCLE / NOCYCLE CACHE <IntegerValue> / NOCACHE ORDER / NOORDER]
Alter addrseqe.
Dropping a Sequence
DBMS Lab Oracle 10g
Synonym
A synonym is an alias or alternate name for a table, view, sequence, or other schema object. They are used mainly to make it easy for users to access database objects owned by other users. They hide the underlying object's identity and make it harder for a malicious program or user to target the underlying object. Because a synonym is just an alternate name for an object, it requires no storage other than its definition. When an application uses a synonym, the DBMS forwards the request to the synonym's underlying base object. By coding your programs to use synonyms instead of table names, you insulate yourself from any changes in the name, ownership, or table locations. If you frequently refer to a table that has a long name, you might appreciate being able to refer to it with a shorter name without having to rename the table and alter the code referring to that table. Example: Were trying to access a table in the SYSTEM account from temp account, and we find it lengthy.
PROMOTIONAL COPY
We can change the system.clientmaster to cmaster to make it short. For that we use a synonym.
Once you have defined a synonym, you can use it just like a table name in SQL queries.
Dropping a Synonym
DBMS Lab Oracle 10g
PL/SQL Programming
PL/SQL permits the creation of structured logical blocks of code that describe processes, which have to be applied to data. A single PL/SQL code block consists of a set of SQL statements, clubbed together, and passed to the Oracle engine entirely. This block has to be logically grouped together for the engine to recognize it as a singular code block. A PL/SQL block has a definite structure, which can be divided into sections. The sections of a PL/SQL block are: 1. The Declare Section Code blocks start with a declaration section, in which, memory variables and other Oracle objects can be declared, and if required initialized. Once declared, they can be used in SQL statements for data manipulation. 2. The Begin Section It consists of a set of SQL and PL/SQL statements, which describe processes that have to be applied to table data. Actual data manipulation, retrieval, looping and branching constructs are specified in this section. 3. The Exception Section (Optional) This section deals with handling of errors that arise during execution of the data manipulation statements, which make up the PL/SQL code block. Errors can arise due to syntax, logic and/or validation rule violation. 4. The End Section
PROMOTIONAL COPY
This marks the end of a PL/SQL block. Displaying User Messages On The VDU Screen
DBMS_OUTPUT.PUT_LINE(Message to be displayed);
To display messages, the SETSERVEROUTPUT should be set to ON.

SET SERVEROUTPUT [ON/OFF]
Control Structure. Conditional Control

IF <Condition> THEN <Action> ELSIF <Condition> THEN <Action> ELSE <Action> END IF;
Simple Loop
LOOP <Seq. of Stmt.> END LOOP;
Iterative While Loop

WHILE <Condition> LOOP <Action> END LOOP;
For Loop
FOR variable IN start..end LOOP <Action> END LOOP;
DBMS Lab Oracle 10g
Factorial up to a Limit
Write a PL/SQL code block to print Factorial of numbers up to a given limit.
Algorithm:
Step1: Start Step2: Declare the variables limit, l, fact and i as number. Step3: Initialize fact:= 1 & l:= 2 Step4: Read value for limit Step5: Repeat for i=1 to limit by 1 Repeat while l<=i f:= f*l l:=l+1 [End of inner loop] [End of outer loop] Step6: Print the result Step7: End
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
Generate Fibonacci Series
Write a PL/SQL code block to print Fibonacci series up to a given limit.
Algorithm:
Step1: Start Step2: Declare variables f1, f2, f3, n, i as number. Step3: Initialize f1:=0, f2:=1. Step4: Read the limit n. Step5: If n=0, then Print f1 [End of if structure] Step6: If n=1, then Print f2 [End of if structure] Step7: If n>1, then Print f1, f2 Repeat for i:= 3 to n by 1 f3:=f1+f2 Print f3
PROMOTIONAL COPY
Set f1:=f2 Set f2:=f3 [End of loop] [End of if structure] Step8: End
DBMS Lab Oracle 10g
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
Quadratic Equation
Write a PL/SQL code block to solve Quadratic Equation.
Algorithm:
Step1: Start Step2: Declare the variables a, b, c, d, r1, r2, root as number. Step3: Read the input values for variables a, b & c Step4: Set d:= b*b-4*a*c. Step5: If d=0, Then (a) Set root=-b/2*a. (b) Print the root Else if d>0, then (a) Set r1=(-b+sqrt(d))/(2*a) (b) Set r2=(-b-sqrt(d))/(2*a) (c) Print the results
PROMOTIONAL COPY
Else Print 'Roots are imaginary'. [End of If structure] Step6: End
DBMS Lab Oracle 10g
Program:
Output:
PROMOTIONAL COPY
DBMS Lab Oracle 10g
Check Prime or Not
Write a PL/SQL code block for checking a number to find whether it is Prime or Not.
Algorithm:
Step1: Start Step2: Declare variables n, c as number. Step3: Read the number n Step4: Set c:=0 Step5: Repeat for i=2 to n by 1 If n%i=0,Then c:= c+1 [End of if structure] [End of loop] Step6: If c=1, Then Print 'IS PRIME'. Else Print 'NOT PRIME'. [End of if structure] Step7: End.
PROMOTIONAL COPY
Program:
Output:
DBMS Lab Oracle 10g
Multiplication Table
Write a PL/SQL a code block to print Multiplication Table for given number.
Algorithm:
Step1: Start Step2: Declare variables n, limit & i as number. Step3: Read the values for n and limit Step4: Set i:= 1,r:=0 Step5: Repeat for i=1 to limit by 1 Print i*n [End of loop] Step6: End
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
Check Balance in Account Table
Write a PL/SQL code block that accept accno and check if user's balance is less than the minimum balance only then reduce Rs. 100 from it.
Algorithm:
Step1: Start Step2: Create a table account with fields acctno and curbal and populate it with records. Step3: Declare acno, temp, minbal as number. Step4: Set minbal:=1000,as constant Step5: Read the acno and assign the curbal field value of that account to variable temp Step6: If temp<minbal, Then Update the table account, Set curbal=curbal-100. Print 'Rs.100/- deducted from your account' Else
PROMOTIONAL COPY
[End of if structure] Step7: End
Print 'You have sufficient balance'
Program:
DBMS Lab Oracle 10g
Output:
Table contents before executing the code block.
Table contents after executing the code block.
PROMOTIONAL COPY
DBMS Lab Oracle 10g
Circle Area
Calculate the area of circle for radius varying from 2 to 6 and store them into a table (circle).
Algorithm:
Step1: Start Step2: Create table circle with fields radius, area. Step3: Define pi as constant having value 3.14 Step4: Declare variables i, ar as number Step5: Repeat for i=2 to 6 by 1 ar:=pi*power(i,2) Insert these values to table circle [End of loop] Step6: End
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
PL/SQL Functions and Procedures

A Procedure or Function is logically grouped set of SQL and PL/SQL statements that perform a specific task. A Stored Procedure or function is a named PL/SQL code block that has been compiled and stored in one of the Oracle Engines system tables.
Creating a Stored Procedure:

CREATE OR REPLACE PROCEDURE <Procedure Name> (<Argument> {IN,OUT,IN OUT} <Data type>, ) {IS, AS} <Variable> declarations; <Constant> declarations; BEGIN <PL/SQL subprogram body>; EXCEPTION <Exception PL/SQL block>; END;
PROMOTIONAL COPY
Creating a Function
CREATE OR REPLACE FUNCTION <Function Name> (<Argument> IN <Data type>,) RETURN <Data type> (IS, AS) <Variable> declarations; <Constant> declarations; BEGIN <PL/SQL subprogram body>; EXCEPTION <Exception PL/SQL block>; END;
DBMS Lab Oracle 10g
Add two numbers using Function
Create and call a PL/SQL Function to add two numbers.
Algorithm:
Step1: Start Step2: (a) Create a function addno() accepting 2 parameters a and b (b) Declare a variable result inside the function definition (c) Set result:= a+b (d) Return the result Step3: Define the main code declaring 2 variables a, b as number. Step4: Read the values for a and b Step5: Call the function addno(),by passing a and b as parameters Step6: Print the result Step7: End
PROMOTIONAL COPY
Program:
Output:
DBMS Lab Oracle 10g
Factorial using Function
Use Function to find factorial of any number by calling it.
Algorithm:
Step1: Start Step2:(a) Create a function named fact() with parameter (b) Declare variables f, i, l as number (c) Set f:=1,l:=2 (d) Repeat for i=1 to a by 1 Repeat while l<=i f:=f*l l:=l+1 [End of inner loop] [End of outer loop] (e) Return f Step3: Define the main code, declaring a variable n. Step4: Read the value for n Step5: Call the function fact() passing n as parameter Step6: Print the result Step7: End
PROMOTIONAL COPY
Program:
Output:
DBMS Lab Oracle 10g
Prime or Not Function
Create a function to check whether a number is Prime or Not.
Algorithm:
Step1: Start Step2: (a) Create a function chkprime with a parameter n. (b) Declare a variable c and set c:=0 (c) Repeat for i=2 to n by 1 If n % i =0, then c:=c+1 [End of if structure] [End of loop] (d) If c=1, then return 1 Else
PROMOTIONAL COPY
return 0 [End of if structure] Step3: Define the main code, declaring a variable n Step4: Read the value for n Step5: Call the function chkprime() Step6: If return value is 1 Print IS PRIME Else Print NOT PRIME Step7: End
DBMS Lab Oracle 10g
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
Fibonacci Series using Procedure
Create a procedure to generate Fibonacci Series.
Algorithm:
Step1: Start Step2: (a) Create a procedure fib with parameter n (b) Declare variables f1, f2, f3, i as number. (c) Initialize f1:=0, f2:=1. (d) If n=0, then Print f1 [End of if structure] (e) If n=1, then Print f2 [End of if structure] (f) If n>1, then Print f1, f2
PROMOTIONAL COPY
Repeat for i:= 3 to n by 1 f3:=f1+f2 Print f3 Set f1:=f2 Set f2:=f3 [End of loop] [End of if structure] [End of procedure] Step3: Define the main code declaring a variable x Step4: Read the value for x Step5: Call the procedure. Step6: End
DBMS Lab Oracle 10g
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
Quadratic Equation using Procedure
Create a procedure to solve a Quadratic Equation.
Algorithm:
Step1: Start Step2: (a) Create a procedure quad with parameters a, b, c (b) Declare variables d, r1, r2, root as number (c) Set d:=b*b-4*a*c. (d) If d=0, then Set root=-b/2*a. Print the root Else if d>0, then Set r1=(-b+sqrt(d))/(2*a) Set r2=(-b-sqrt(d))/(2*a) Print the results
PROMOTIONAL COPY
Else Print 'Roots are imaginary'. [End of If structure] [End of procedure] Step3: Define the main code having variables a, b & c Step4: Read the values for a, b & c Step5: Call the procedure Step6: End
DBMS Lab Oracle 10g
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
Employee details from Table
Create a procedure to access emp table and display its contents. Procedure should accept accno.
Algorithm:
Step1: Start Step2: Create a procedure empdetails (a) Declare variables id, name, sal, depno (b) Select the field values of emp with eno=no and assign to id, name, sal, depno. (c) Display the details. [End of Procedure]. Step3: Define the main code declaring a variable empno Step4: Read the value for empno Step5: Call the procedure empdetails with empno as parameter Step6: End
Program:
PROMOTIONAL COPY
Output:
DBMS Lab Oracle 10g
Triggers
The Oracle engine allows the definition of procedures that are implicitly executed, when an insert, update or delete is issued against a table. These procedures are called Database Triggers. Trigger has 3 basic parts: A triggering event or statement, a trigger restriction, and a trigger action. Triggering Event or Statement: It is a SQL statement that causes a trigger to be fired. It can be INSERT, UPDATE or DELETE statement for a specific table. Trigger Restriction: A trigger restriction specifies a Boolean (Logical) expression that must be TRUE for the trigger to fire. Trigger Action: A trigger action is the PL/SQL code to be executed when a triggering statement is encountered and any trigger restriction evaluates to TRUE.
Types of Triggers:
Row Triggers Statement Triggers Before Triggers After Triggers Syntax: A row trigger is fired each time a row in the table is affected by the triggering statement. A statement trigger is fired once on behalf of the triggering statement, independent of the no. of rows the triggering statement affects. Executes the trigger action before the triggering statement. Executes the trigger action after the triggering statement is executed.
PROMOTIONAL COPY
CREATE OR REPLACE TRIGGER <Trigger Name> {BEFORE, AFTER} {DELETE, INSERT, UPDATE [OF Column, ..]} ON <Table Name> [REFERENCING {OLD AS old, NEW AS new}] [FOR EACH ROW [WHEN Condition]] DECLARE <Variable declarations>; <Constant declarations>; BEGIN <PL/SQL subprogram body>; EXCEPTION <Exception PL/SQL block>; END;
DBMS Lab Oracle 10g
Create a trigger auditclient to set operation to manage audit1 table when an update is occurred.
We use contents of table clientmaster to copy to audit1 table.
Program:
PROMOTIONAL COPY
We fire an update query to clientmaster.
Output:
DBMS Lab Oracle 10g
ODBC Connectivity
To use the Oracle database with any Front End Application Programs, we use ODBC (Open Database Connection). Using ODBC, we can connect we can connect to oracle tables and can be used in Programming Languages like ASP.NET, VB, Java etc. The following steps illustrate how to set an ODBC connection with Oracle 10g and use it with Visual Basic 6.0. Environment: Database Operating System : Oracle 10g Express Edition : Microsoft Windows XP (32 bit) or Microsoft Windows 7 (32 bit only).
Follow these steps to configure a Data Source Name (DSN):

Open Data Sources in Control Panel. (Control Panel Administrative Tools Data Sources (ODBC)). In Windows 7, go to Control Panel; change the view to Large Icons and select Administrative Tools.
PROMOTIONAL COPY
Select the System DSN tab from the Data Sources window.
Click Add button.
DBMS Lab Oracle 10g
The Create New Data Source appears. Select Oracle in XE and click Finish.
The Oracle ODBC Driver Configuration Appears. Provide following information: Data Source Name : payroll (Anything you wish, but this is used later to connect). TNS Service Name : XE User ID : system (User ID of the account you want to connect). And then click Test Connection.
PROMOTIONAL COPY
The connection window appears; type in the Password of your account (Usually system). And click OK. If everything goes well, youll get a Connection Successful message. Click OK.
DBMS Lab Oracle 10g
After the configuration is complete, we can find the DSN we created listed.
Now, we have configured DSN. We can now use this in any Programming Language which supports Database Programming.
Database Programming with Visual Basic (and Oracle)

Follow these steps to make database application with VB as Front end and Oracle as Back end:
PROMOTIONAL COPY
Open Visual Basic 6.0, start a new Standard EXE application. We use Microsoft ADO to work with database. So, Enable Microsoft ActiveX Data Objects 2.8 Library Project References (Project References).
DBMS Lab Oracle 10g
Then, design the form with necessary controls and objects.
Declare Command, Connection and Record Set variables. To make the connection, type following code in Form Load() event:
con.Open "payroll", "system", "system" Here, payroll is the Data Source Name that weve configured; system and system are Username and Password of the database user which has the required table.
PROMOTIONAL COPY
rs.CursorLocation = adUseClient cmd.CommandText = "select * from paytab" Here, paytab is the actual table that we are accessing. rs.Open cmd, , adOpenStatic, adLockBatchOptimistic After the above code will be executed, the first row (complete) in the table paytab will be available in rs variable. We can get value of each column by rs(Column Name).
cmd.ActiveConnection = con
Further operations such as INSERT, DELETE, SEARCH etc. can be done by executing SQL queries with Command Variable through CommandText property.
DBMS Lab Oracle 10g
Payroll Information System
Create a Payroll Information application in Visual Basic with Oracle as Back End.
Table used: payroll
First, set up a System DSN as mentioned before with DSN name as pay. Then, create a Standard EXE application in VB and design the form with following controls and necessary labels. Controls Used: Controls enotxt enametxt dojtxt bsaltxt hratxt datxt tatxt grsaltxt cmdcalc cmdnew cmdsave cmdfirst cmdprev cmdnxt cmdlast Name Properties Text
PROMOTIONAL COPY
Textboxes Name Command Buttons Calculate New Save << < > >> Caption
DBMS Lab Oracle 10g
Code:
Dim con As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset _________________________________________________________ Private Sub Form_Load() con.Open "pay", "system", "system" cmd.ActiveConnection = con rs.CursorLocation = adUseClient cmd.CommandText = "select * from payroll" rs.Open cmd, , adOpenStatic, adLockBatchOptimistic Call Disp End Sub _________________________________________________________ Sub Disp()
PROMOTIONAL COPY
MsgBox ("Sorry, No Records found in Database!") rs.MoveFirst ElseIf (rs.EOF = True) Then MsgBox ("Sorry, EOF!") rs.MoveLast End If enotxt.Text = rs("eno") enametxt.Text = rs("ename") bsaltxt.Text = rs("basic_sal") hratxt.Text = rs("hra") datxt.Text = rs("da") tatxt.Text = rs("ta") grsaltxt.Text = rs("grsal") dojtxt.Text = rs("doj") End Sub _________________________________________________________
If (rs.BOF = True) Then
DBMS Lab Oracle 10g
Private Sub cmdnew_Click() enotxt.Text = "" enametxt.Text = "" bsaltxt.Text = "" hratxt.Text = "" datxt.Text = "" tatxt.Text = "" grsaltxt.Text = "" dojtxt.Text = "" End Sub _________________________________________________________ Private Sub cmdsave_Click() cmd.CommandText = "insert into payroll(eno,ename,basic_sal,hra,da,ta,grsal,doj) values(" & enotxt.Text & ",'" & enametxt.Text & "'," & bsaltxt.Text & "," & hratxt.Text & "," & datxt.Text & "," & tatxt.Text & "," & grsaltxt.Text & ",'" & dojtxt.Text & "')"
PROMOTIONAL COPY
cmd.Execute MsgBox ("One Row Inserted!") cmd.CommandText = "select * from payroll" rs.Requery cmdfirst_Click End Sub _________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub _________________________________________________________ Private Sub cmdprev_Click() rs.MovePrevious Call Disp End Sub _________________________________________________________
DBMS Lab Oracle 10g
Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub _________________________________________________________ Private Sub cmdlast_Click() rs.MoveLast Call Disp End Sub _________________________________________________________ Private Sub cmdcalc_Click() Dim h As Integer Dim d As Integer Dim basic As Integer h = hratxt.Text d = datxt.Text
PROMOTIONAL COPY
basic = bsaltxt.Text If ((h <= 100) And (d <= 100)) Then grsaltxt.Text = basic + (basic * (h / 100)) + (basic * (d / 100)) + tatxt.Text Else MsgBox ("HRA and/or DA Exceeds the limit!") End If End Sub _________________________________________________________
DBMS Lab Oracle 10g
OUTPUT
PROMOTIONAL COPY
DBMS Lab Oracle 10g
Student Information System
Create a Student Information System in VB with Oracle as Back End. : stud : studtab DSN Table used
Controls rnotxt nametxt dobtxt m1txt m2txt m3txt tottxt pertxt percent grdtxt cbo cmdcalc cmdclear cmdsave cmdfirst cmdprev cmdnxt cmdlast cmdsrch
Name
Properties
Text
PROMOTIONAL COPY
% Name List Combo Box Name Caption Calculate Clear Save << < > >> Search
Textboxes
Command Buttons
Code:
Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim cmd2 As New ADODB.Command
DBMS Lab Oracle 10g
Dim rs As New ADODB.Recordset Dim rss As New ADODB.Recordset ___________________________________________________________________ Private Sub cmdclear_Click() rnotxt.Text = "" nametxt.Text = "" dobtxt.Text = "" cbo.ListIndex = 0 'To set default value to the first list item m1txt.Text = "" m2txt.Text = "" m3txt.Text = "" tottxt.Text = "" pertxt.Text = "" grdtxt.Text = "" End Sub ___________________________________________________________________
PROMOTIONAL COPY
Private Sub Form_Load() cnn.Open "stud", "system", "system" cmd.ActiveConnection = cnn cmd.CommandText = "select * from studtab" rs.CursorLocation = adUseServer rs.Open cmd, , adOpenStatic, adLockBatchOptimistic Call Disp End Sub ___________________________________________________________________ Sub Disp() If (rs.BOF = True) Then MsgBox ("Sorry, No Records found in Database!") rs.MoveFirst ElseIf (rs.EOF = True) Then MsgBox ("Sorry, EOF!") rs.MoveLast End If
DBMS Lab Oracle 10g
rnotxt.Text = rs("rollno") nametxt.Text = rs("name") dobtxt.Text = rs("dob") cbo.Text = rs("course") m1txt.Text = rs("m1") m2txt.Text = rs("m2") m3txt.Text = rs("m3") tottxt.Text = rs("tot") pertxt.Text = rs("percent") grdtxt.Text = rs("grade") End Sub ___________________________________________________________________ Private Sub cmdcalc_Click() Dim m1 As Integer Dim m2 As Integer Dim m3 As Integer
PROMOTIONAL COPY
Dim tot As Integer Dim p As Integer Dim g As String If (IsNumeric((m1txt.Text) And (m2txt.Text) And (m3txt.Text)) = True) And (m1txt.Text <= 100) And (m2txt.Text <= 100) And (m3txt.Text <= 100) Then m1 = m1txt.Text m2 = m2txt.Text m3 = m3txt.Text tot = m1 + m2 + m3 p = tot / 3 If (p >= 80) Then g = "A" ElseIf (p >= 70) Then g = "B" ElseIf (p >= 60) Then g = "C" ElseIf (p >= 50) Then
DBMS Lab Oracle 10g
g = "D" Else g = "E" End If tottxt.Text = tot pertxt.Text = p grdtxt.Text = g Else MsgBox ("Enter Valid Marks") End If End Sub ___________________________________________________________________ Private Sub cmdsave_Click() cmd.CommandText = "insert into studtab(rollno,name,dob,course,m1,m2,m3,tot,percent,grade) values(" & rnotxt.Text & ",'" & nametxt.Text & "','" & dobtxt.Text & "','" & cbo.Text & "'," & m1txt.Text & "," & m2txt.Text & "," & m3txt.Text & "," & tottxt.Text & "," & pertxt.Text & ",'" & grdtxt.Text & "')" cmd.Execute
PROMOTIONAL COPY
MsgBox ("One Row Inserted!") cmd.CommandText = "select * from studtab" rs.Requery cmdfirst_Click End Sub ___________________________________________________________________ Private Sub tottxt_GotFocus() cmdcalc_Click End Sub ___________________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub Private Sub cmdprev_Click()
DBMS Lab Oracle 10g
rs.MovePrevious Call Disp End Sub Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub Private Sub cmdlast_Click() rs.MoveLast Call Disp End Sub ___________________________________________________________________ Private Sub cmdsrch_Click() cmd2.ActiveConnection = cnn cmd2.CommandText = "select * from studtab where rollno=" & InputBox("Enter Roll Number to search!", Search) & ""
PROMOTIONAL COPY
If (rss.EOF = True) Then MsgBox "No Records!" Else rnotxt.Text = rss("rollno") nametxt.Text = rss("name") dobtxt.Text = rss("dob") cbo.Text = rss("course") m1txt.Text = rss("m1") m2txt.Text = rss("m2") m3txt.Text = rss("m3") tottxt.Text = rss("tot") pertxt.Text = rss("percent") grdtxt.Text = rss("grade") End If End Sub ___________________________________________________________________
rss.Open cmd2, , adOpenStatic, adLockBatchOptimistic
DBMS Lab Oracle 10g
OUTPUT
PROMOTIONAL COPY
DBMS Lab Oracle 10g
Banking Application
Create a Banking Application to store and retrieve customer details. : bankdsn : bank DSN Table used
Controls Textboxes acnotxt cnametxt addrstxt placetxt baltxt sbopt caopt cmddel cmdclr cmdsave cmdfirst cmdprev cmdnxt cmdlast cmdsrch
Name
Properties
Text
PROMOTIONAL COPY
Option Button Name Name Command Buttons Caption SB CA Caption Delete Clear Save First Previous Next Last Search
(Multiline = True) (Max Length = 50)
Code: Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim cmd1 As New ADODB.Command Dim rs As New ADODB.Recordset Dim rss As New ADODB.Recordset
DBMS Lab Oracle 10g
___________________________________________________________________ Private Sub Form_Load() cnn.Open "bankdsn", "system", "j" cmd.ActiveConnection = cnn cmd.CommandText = "select * from bank" rs.CursorLocation = adUseClient rs.Open cmd, , adOpenStatic, adLockBatchOptimistic Call Disp End Sub ___________________________________________________________________ Sub Disp() If (rs.BOF = True) Then MsgBox ("Sorry, BOF!") rs.MoveFirst
PROMOTIONAL COPY
MsgBox ("Sorry, EOF!") rs.MoveLast End If acnotxt.Text = rs("acno") If (rs("actype") = "SB") Then sbopt.Value = True Else caopt.Value = True End If cnametxt.Text = rs("cname") addrstxt.Text = rs("addrs") placetxt.Text = rs("place") baltxt.Text = rs("balance") End Sub ___________________________________________________________________
ElseIf (rs.EOF = True) Then
DBMS Lab Oracle 10g
Private Sub cmdclr_Click() acnotxt.Text = "" sbopt.Value = False caopt.Value = False cnametxt.Text = "" addrstxt.Text = "" placetxt.Text = "" baltxt.Text = "" End Sub ___________________________________________________________________ Private Sub cmdsave_Click() Dim atype As String If (sbopt.Value = False) And (caopt.Value = False) Then MsgBox ("Please Select an Account Type!")
PROMOTIONAL COPY
If (sbopt.Value = True) Then cmd.CommandText = "insert into bank(acno,actype,cname,addrs,place,balance) values( " & acnotxt.Text & ", '" & sbopt.Caption & "' ,'" & cnametxt.Text & "','" & addrstxt.Text & "','" & placetxt.Text & "'," & baltxt.Text & ")" Else cmd.CommandText = "insert into bank(acno,actype,cname,addrs,place,balance) values( " & acnotxt.Text & ", '" & caopt.Caption & "' ,'" & cnametxt.Text & "','" & addrstxt.Text & "','" & placetxt.Text & "'," & baltxt.Text & ")" End If cmd.Execute MsgBox ("One Row Inserted!") cmd.CommandText = "select * from bank" rs.Requery cmdfirst_Click End If
Else
DBMS Lab Oracle 10g
End Sub ___________________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub ___________________________________________________________________ Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub ___________________________________________________________________ Private Sub cmdprev_Click() rs.MovePrevious
PROMOTIONAL COPY
End Sub ___________________________________________________________________ Private Sub cmdlast_Click() rs.MoveLast Call Disp End Sub ___________________________________________________________________ Private Sub cmdsrch_Click() cmd1.ActiveConnection = cnn cmd1.CommandText = "select * from bank where acno=" & InputBox("Enter Account Number to search!", Search) & "" rss.Open cmd1, , adOpenStatic, adLockBatchOptimistic If (rss.EOF = True) Then MsgBox "No Records!" Else acnotxt.Text = rss("acno")
Call Disp
DBMS Lab Oracle 10g
If (rs("actype") = "SB") Then sbopt.Value = True Else caopt.Value = True End If cnametxt.Text = rss("cname") addrstxt.Text = rss("addrs") placetxt.Text = rss("place") baltxt.Text = rss("balance") End If rss.Close End Sub ___________________________________________________________________ Private Sub cmddel_Click()
PROMOTIONAL COPY
d = InputBox("Enter Account Number to Delete") cmd1.ActiveConnection = cnn cmd1.CommandText = "select * from bank where acno=" & d & "" rss.Open cmd1, , adOpenStatic, adLockBatchOptimistic If rss.EOF = True Then MsgBox "No Records" Else cmd1.CommandText = "delete from bank where acno=" & d & "" cmd1.Execute MsgBox "Deleted" End If rss.Close rs.Requery End Sub ___________________________________________________________________
Dim d As Integer
DBMS Lab Oracle 10g
OUTPUT
PROMOTIONAL COPY
DBMS Lab Oracle 10g
Library Information System
DSN Table Create a Library Information application which can manage, details of books in a library, in VB with Oracle as Back End. : library : libtab Controls Textboxes bidtxt titletxt authtxt pubtxt pricetxt cmdclr cmdsave cmdfirst cmdprev cmdnxt cmdlast cmdbidsrch cmdtitsrch Properties
Name
Text
Name
Command Buttons
PROMOTIONAL COPY
Code:
Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim rss As New ADODB.Recordset Dim cmds As New ADODB.Command ___________________________________________________________________ Private Sub Form_Load() cnn.Open "library", "system", "j" cmd.ActiveConnection = cnn cmd.CommandText = "select * from libtab" rs.CursorLocation = adUseClient rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
Caption Clear Save << < > >> By Book ID By Title
DBMS Lab Oracle 10g
Call Disp End Sub ___________________________________________________________________ Sub Disp() If (rs.BOF = True) Then MsgBox ("Sorry, BOF!") rs.MoveFirst ElseIf (rs.EOF = True) Then MsgBox ("Sorry, EOF!") rs.MoveLast End If bidtxt.Text = rs("bid") titletxt.Text = rs("title") authtxt.Text = rs("author")
PROMOTIONAL COPY
pricetxt.Text = rs("price") End Sub ___________________________________________________________________ Private Sub cmdclr_Click() bidtxt.Text = "" titletxt.Text = "" authtxt.Text = "" pubtxt.Text = "" pricetxt.Text = "" End Sub ___________________________________________________________________ Private Sub cmdsave_Click() cmd.CommandText = "insert into libtab(bid,title,author,pub,price) values(" & bidtxt.Text & ",'" & titletxt.Text & "','" & authtxt.Text & "','" & pubtxt.Text & "'," & pricetxt.Text & ")" cmd.Execute
pubtxt.Text = rs("pub")
DBMS Lab Oracle 10g
MsgBox ("Data Saved") cmd.CommandText = "select * from libtab" rs.Requery cmdclr_Click End Sub ___________________________________________________________________ Private Sub cmdfirst_Click() rs.MoveFirst Call Disp End Sub ___________________________________________________________________ Private Sub cmdlast_Click() rs.MoveLast Call Disp
PROMOTIONAL COPY
___________________________________________________________________ Private Sub cmdprev_Click() rs.MovePrevious Call Disp End Sub ___________________________________________________________________ Private Sub cmdnxt_Click() rs.MoveNext Call Disp End Sub ___________________________________________________________________ Private Sub cmdbidsrch_Click() cmds.ActiveConnection = cnn cmds.CommandText = "select * from libtab where bid=" & InputBox("Enter Book ID Number to search!", Search) & "" rss.Open cmds, , adOpenStatic, adLockBatchOptimistic
End Sub
DBMS Lab Oracle 10g
If (rss.EOF = True) Then MsgBox "No Records!" Else bidtxt.Text = rss("bid") titletxt.Text = rss("title") authtxt.Text = rss("author") pubtxt.Text = rss("pub") pricetxt.Text = rss("price") End If rss.Close End Sub ___________________________________________________________________ Private Sub cmdtitsrch_Click() cmds.ActiveConnection = cnn
PROMOTIONAL COPY
rss.Open cmds, , adOpenStatic, adLockBatchOptimistic If (rss.EOF = True) Then MsgBox "No Records!" Else bidtxt.Text = rss("bid") titletxt.Text = rss("title") authtxt.Text = rss("author") pubtxt.Text = rss("pub") pricetxt.Text = rss("price") End If rss.Close End Sub ___________________________________________________________________
cmds.CommandText = "select * from libtab where title='" & InputBox("Enter Book Title to search!", Search) & "'"
DBMS Lab Oracle 10g
OUTPUT
PROMOTIONAL COPY

Oracle 10g Lab Simple

Uploaded by

Copyright:

Available Formats

Oracle 10g Lab Simple

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle 10g Lab Simple

Uploaded by

Copyright:

Available Formats

DBMS Lab Oracle 10g

We are using ORACLE from Oracle Corp. in this Lab.

SQL (Structured Query Language)

1|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

Oracle Data Types

2|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

Data Definition Language Syntax CREATE

3|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

Dropping a column from a table:

Modifying Existing Columns:

Data Manipulation Language Syntax INSERT

Removal of specific row(s):

4|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

Updating Records conditionally:

Data Query Language Syntax

Figure: Syntax of Select.

5|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

Transaction Control Language Syntax

6|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

7|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

Oracle 10g Installation and Configuration

8|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

9|University College of Applied Sciences, Pathanamthitta

DBMS Lab Oracle 10g

DBMS Lab Oracle 10g

DBMS Lab Oracle 10g

DBMS Lab Oracle 10g

For Example, to unlock the HR user, Select Manage Users.

DBMS Lab Oracle 10g

Table Design using Foreign Key and Normalization

DBMS Lab Oracle 10g

To convert to First Normal Form:

Second Normal Form

DBMS Lab Oracle 10g

Third Normal Form

A general case of transitive dependencies is as follows:

DBMS Lab Oracle 10g

Field Project number Project name

Key Primary Key --

DBMS Lab Oracle 10g

Working with SQL

DBMS Lab Oracle 10g

Alter the table using ALTER command.

Adding new column:

Defining Integrity constraints:

DBMS Lab Oracle 10g

Now, we alter it to add Integrity Constraints.

DBMS Lab Oracle 10g

Insert query, standard form:

Insert query, alternate method:

DBMS Lab Oracle 10g

Select names of customers whose state is Kerala.