ex no1 aim: procedure: 1. 2. 3. 4
Post on 12-Nov-2014
275 Views
Preview:
DESCRIPTION
TRANSCRIPT
EX No1 Study of ER Diagram
Aim:
Procedure:
1. Identify entities2. Identify relationships3. Identify cardinalities4. Draw the E-R diagram for Banking, Employee, Hospital
databases.
Entity-Relationship Diagram:
This diagram can express the overall logical structure of a database graphically. ER diagram consists of the following major components: This diagram provides a global quick reference to an
organization’s data structures. This can be used individually to design an Information System’s
(IS) data structure
ER diagram consists of the following major components:Rectangles: It represents entity sets.
Entity an aggregation of a number of data elements each data element is an attribute of the entity
Entity type a class of entities with the same attributes
Ellipses: It represents attributes.
Diamonds: It represents relationship sets. Relationship
an association between two or more entities that is of particular interest
Lines: It provide the link between attributes to entity sets & entity sets to relationship sets.
Double ellipses: It represents multivalued attributes. Multivalued attributes may be indicated in some manner.
o Means attribute can have more than one value. o E.g. hobbies.
Dashed ellipses: It denote derived attributes.
1
Double lines: It represents total participation of an entity in a relationship set.
Double rectangles: It represents weak entity sets. A weak entity set is indicated by a doubly-outlined box.
Development process of ER diagram: Identify the entities Determine the attributes for each entity Select the primary key for each entity Establish the relationships between the entities Draw an entity model Test the relationships and the keys
Simple example: STUDENTs attend COURSEs that consist of many SUBJECTs. A single SUBJECT (i.e. English) can be studied in many different
COURSEs. Each STUDENT may only attend one COURSE.
Identify the entities:
Any entity can be classified in one of the following categories: Regular :
any physical object, event, or abstract concept that we can record facts about.
Weak : any entity that depends on another entity for its existence.
Determine the Attributes: Every Entity has attributes. Attributes are characteristics that allow us to classify/describe an
entity e.g., entity STUDENT has the attributes:
student number name date of birth course number
Key Attributes: Certain attributes identify particular facts within an entity, these
are known as KEY attributes. The different types of KEY attribute are:
Primary Key Composite Primary Key
Foreign Key
2
Key Definitions:
Primary Key: One attributes whose value can uniquely identify a
complete record (one row of data) within an entity. Composite Primary Key
A primary key that consists of two or more attribute within an entity.
Foreign Key A copy of a primary key that exists in another entity for the
purpose of forming a relationship between the entities involved.
Degrees of a Relationship:One to one (1:1)
1 1 1
One to many (1:n)
1 N M
Many to many (n:m)
N M
Every many to many relationship consists of two one to many relationships working in opposite directions
3
MAN WOMEN
CUSTOMER ORDER
COURSE SUBJECT
ER diagram for banking system
Depositor
Acc Branch
Borrower
Loan Branch
Result:
4
Customer
nameaddress
phoneno
street city
pincode
D.O.B
age
Loan
Loanno
Amount
Account
Accno Balance
Branch
Branch_city
AssetsBranch_Name
EX No: 2 Study of Normalization
Aim:
Procedure:
Normalization is a method for organizing data elements in a database into tables.
Normalization is a method of converting un-normalized tables into normalized tables. Normalization AvoidsDuplication of Data – The same data is listed in multiple lines of the database.Insert Anomaly – A record about an entity cannot be inserted into the table without first inserting information about another entity – Cannot enter a customer without a sales order.
Delete Anomaly – A record cannot be deleted without deleting a record about a related entity. Cannot delete a sales order without deleting all of the customer’s information.
Update Anomaly – Cannot update information without changing information in many places. To update customer information, it must be updated for each sales order the customer has placed.
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.
Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.
What is an "inconsistent dependency"? While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee's salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access; the path to find the data may be missing or broken.
5
There are a few rules for database normalization. Each rule is called a "normal form." If the first rule is observed, the database is said to be in "first normal form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.
As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependencies.
First Normal Form
Eliminate repeating groups in individual tables.Create a separate table for each set of related data.Identify each set of related data with a primary key.Do not use multiple fields in a single table to store similar data.
Second Normal Form
Create separate tables for sets of values that apply to multiple records.Relate these tables with a foreign key.Records should not depend on anything other than a table's primary key (a compound key, if necessary). For example, consider a customer's address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer's address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.
Third Normal Form
Eliminate fields that do not depend on the key. Values in a record that are not part of that record's key do not belong in the table.
Other Normalization FormsFourth normal form, also called Boyce Codd Normal Form (BCNF),
and fifth normal form do exist, but are rarely considered in practical design. Disregarding these rules may result in less than perfect database design, but should not affect functionality.
6
**********************************Examples of Normalization**********************************
Un-normalized table:
Student# Advisor Adv-Room Class1 Class2 Class31022 Jones 412 101-07 143-01 159-024123 Smith 216 201-01 211-02 214-01
First Normal Form: No repeating groups.Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, & Class3 in the above record are indications of design trouble. Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem: with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:
Student# Advisor Adv-Room Class#1022 Jones 412 101-071022 Jones 412 143-011022 Jones 412 159-024123 Smith 216 201-014123 Smith 216 211-024123 Smith 216 214-01
Second Normal Form: Eliminate Redundant DataNote the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form. The following two tables demonstrate second normal form:Students:
Student# Advisor Adv-Room1022 Jones 4124123 Smith 216
Registration:
Student# Class#1022 101-071022 143-011022 159-024123 201-014123 211-02
7
4123 214-01
Third Normal Form: ELIMINATE DATA NOT DEPENDENT ON KEY In the last example, Adv-Room (the advisor's office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:Students:
Student# Advisor1022 Jones4123 SmithFaculty:
Name Room DeptJones 412 42Smith 216 42
Normalized Tables are:Registration:
Student# Class#1022 101-071022 143-011022 159-024123 201-014123 211-024123 214-01
Students:
Student# Advisor1022 Jones4123 SmithFaculty:
Name Room DeptJones 412 42Smith 216 42
8
Result:
EX. NO: 3 DDL COMMANDS
Aim:
Data Definition Language commands are:
Create Table Alter Table Drop Table Rename
TO CREATE TABLE . Create table command is used to create a new table.Syntax for create table:Create table tablename(field1 datatype1, field2 datatype2, field3 datatype3…….); SQL> create table depart(dno number(10), dname varchar2(10), primary key(dno)); Table created.
SQL> desc depart;
Name Null? Type ----------------------------------- -------- ------------------------- DNO NOT NULL NUMBER(10) DNAME VARCHAR2(10)
SQL> create table emp(eno number(10),ename varchar2(10),dno number(10),sal number(10),jobid varchar2(10),mangerid varchar2(10),foreign key(dno) references depart(dno));Table created
SQL> desc emp;
Name Null? Type ----------------------------------------- -------------------- ENO NUMBER(10) ENAME VARCHAR2(10) DNO NUMBER(10) SAL NUMBER(10) JOBID VARCHAR2(10) MANAGERID VARCHAR2(10)
9
TO ALTER TABLE ADDAlter table with add command is used to add new field in existing table.Syntax:Alter table tablename add(fieldname datatype);SQL> alter table emp add(primary key(eno), addr varchar2(10));Table altered.SQL> desc emp;
Name Null? Type ----------------- -------- -------------------------- ENO NOT NULL NUMBER(10) ENAME VARCHAR2(10) DNO NUMBER(10) SAL NUMBER(10) JOBID VARCHAR2(10) MANAGERID VARCHAR2(10) ADDR VARCHAR2(10)
SQL> alter table emp add(phno number(5));Table altered.SQL> desc emp;
Name Null? Type ------------------------ -------- ---------------------------- ENO NOT NULL NUMBER(10) ENAME VARCHAR2(10) DNO NUMBER(10) SAL NUMBER(10) JOBID CHAR(20) MANAGERID VARCHAR2(10) ADDR VARCHAR2(10) PHNO NUMBER(5)
MODIFY
Alter table with modify command is used to change the existing fields in table.Syntax:Alter table tablename modify(fieldname datatype); SQL> alter table emp modify(jobid char);Table altered.SQL> desc emp; Name Null? Type
10
----------------------- -------- -------------------- ENO NOT NULL NUMBER(10) ENAME VARCHAR2(10) DNO NUMBER(10) SAL NUMBER(10) JOBID CHAR(20) MANAGERID VARCHAR2(10) ADDR VARCHAR2(10) PHNO VARCHAR2(10)
SQL> alter table emp modify(jobid char(20));Table altered.SQL> desc emp;
Name Null? Type--------------------------- -------- ---------------------------- ENO NOT NULL NUMBER(10) ENAME VARCHAR2(10) DNO NUMBER(10) SAL NUMBER(10) JOBID CHAR(20) MGRID VARCHAR2(10) ADDR VARCHAR2(10) PHNO VARCHAR2(10)
SQL> alter table emp modify(jobid char(5));alter table emp modify(jobid char(5))ERROR at line 1:ORA-01441: cannot decrease column length because some value is too big
TO DROP THE TABLE
Drop table command is used to delete the table from database.Syntax:Delete table tablename;SQL> drop table emp;Table dropped.SQL> desc emp;ERROR:ORA-04043: object emp does not existRenaming a TableRename command is used to change the table name.Syntax:
Rename old_tablename to new_tablename;Eg)SQL>Rename student to stud_details;Table renamed.Here student table renamed as stud_details.
11
Result:
12
EX. NO: 4 DML COMMANDS
Aim:
Data Manipulation Language commands are:
Insert Update Delete Truncate
SQL > Create Table Cust(cname varchar2(15),cid number(5),caddr char(10), caccno number(5),cacctype varchar2(10),cbalance float,Primary key(cid),unique(cname),unique(caccno),check(cbalance>=1000));
SQL> desc cust; Name Null? Type ----------------------------- -------- ---------------------------- CNAME VARCHAR2(15) CID NOT NULL NUMBER(5) CADDR CHAR(10) CACCNO NUMBER(5) CACCTYPE VARCHAR2(10) CBALANCE FLOAT(126)
SQL> insert into cust values('Anitha',01,'Chennai',1001,'savings',15000);1 row created.SQL> insert into cust values('Shriram',02,'Pondy',1002,'savings',25000);1 row created.SQL> insert into cust values('Chamundi',03,'Salem',1003,'fd',36200);1 row created.SQL> insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance);Enter value for cname: SubhaEnter value for cid: 04Enter value for caddr: SalemEnter value for caccno: 1009Enter value for cacctype: 5000Enter value for cbalance: 5000Old 1: insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance)New 1: insert into cust values('Subha',04,'Salem',1009,'RD',5000)1 row created.
13
SQL> insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance);Enter value for cname: MadhanEnter value for cid: 4Enter value for caddr: SalemEnter value for caccno: 1004Enter value for cacctype: checkingsEnter value for cbalance: 5000old 1: insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance)new 1: insert into cust values('Madhan',4,'Salem',1004,'checkings',5000)1 row created.
SQL> insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance);Enter value for cname: SubhaEnter value for cid: 5Enter value for caddr: TrichyEnter value for caccno: 1005Enter value for cacctype: checkingsEnter value for cbalance: 10000old 1: insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance)new 1: insert into cust values('Subha',5,'Trichy',1005,'checkings',10000)1 row created.
SQL> insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance);Enter value for cname: JayashreeEnter value for cid: 6Enter value for caddr: PondyEnter value for caccno: 1006Enter value for cacctype: fdEnter value for cbalance: 15000old 1: insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance)new 1: insert into cust values('Jayashree',6,'Pondy',1006,'fd',15000)1 row created.
SQL> insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance);Enter value for cname: SridharanEnter value for cid: 7Enter value for caddr: KanchiEnter value for caccno: 1007Enter value for cacctype: fdEnter value for cbalance: 22000
14
old 1: insert into cust values('&cname',&cid,'&caddr',&caccno,'&cacctype',&cbalance)new 1: insert into cust values('Sridharan',7,'Kanchi',1007,'fd',22000)1 row created.
SQL> select * from cust;CNAME CID CADDR CACCNO CACCTYPE CBALANCE
Hari 1 Chennai 1001 savings 15000Shriram 2 Pondy 1002 savings 25000Chamundi 3 Salem 1003 fd 36200Madhan 4 Salem 1004 checkings 5000Subha 5 Trichy 1005 checkings 10000Jayashree 6 Pondy 1006 fd 15000Sridharan 7 Kanchi 1007 fd 22000
7 rows selected.SQL>update cust set caccno=1111 where cname='Chamundi';1 row updatedSQL> select * from cust;CNAME CID CADDR CACCNO CACCTYPE CBALANCE
Hari 1 Chennai 1001 savings 15000Shriram 2 Pondy 1002 savings 25000Chamundi 3 Salem 1111 fd 36200Madhan 4 Salem 1004 checkings 5000Subha 5 Trichy 1005 checkings 10000Jayashree 6 Pondy 1006 fd 15000Sridharan 7 Kanchi 1007 fd 22000
7 rows selected.SQL>delete from cust where cacctype='fd’;3 row deletedSQL> select * from cust;CNAME CID CADDR CACCNO CACCTYPE CBALANCE
Hari 1 Chennai 1001 savings 15000Shriram 2 Pondy 1002 savings 25000Madhan 4 Salem 1004 checkings 5000Subha 5 Trichy 1005 checkings 10000
4 rows selected.Truncate table
This command is used to delete records from table. It only remove the table contents but table structure is in database.Syntax:Truncate table <table name>;
15
ExampleTruncate table stud;Table deleted.
Result:
16
EX. NO: 5 DCL COMMANDS
Aim:
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
REVOKE – Withdraws user’s access privileges to database given with the GRANT command
grant revoke
GRANT – Gives user’s access privileges to database
You can grant privileges in the following ways: Grant a specific privilege on one object in a single statement Grant a list of privileges Grant privileges over a list of objects Grant ALL, for all the privileges of accessing a single table, or for
all privileges that are associated with a specific package
GRANT privilege TO PUBLIC AT ALL LOCATIONS:
You can grant SELECT, INSERT, UPDATE, and DELETE table privileges.If you grant a privilege to PUBLIC AT ALL LOCATIONS, the grantee is PUBLIC*. Because PUBLIC* is a special identifier that is used by DB2 internally, you should not use PUBLIC* as a primary or secondary authorization ID. When a privilege is revoked from PUBLIC AT ALL LOCATIONS, authorization IDs to which the privilege was specifically granted still retains the privilege.
Some differences exist in the privileges for a query that uses system-directed access:
Although the query can use privileges granted TO PUBLIC AT ALL LOCATIONS, it cannot use privileges granted TO PUBLIC.
The query can exercise only the SELECT, INSERT, UPDATE, and DELETE privileges at the remote location.
GRANT syntax
GRANT privileges ON database_object TO ( PUBLIC | user_list )[ WITH GRANT OPTION ]privileges ::= priv_item1, priv_item2, ...priv_item ::= ALL [ PRIVILEGES ] | SELECT | INSERT | UPDATE | DELETE | REFERENCES | USAGE
17
database_object ::= [ TABLE ] table_name | SCHEMA schema_nameuser_list ::= PUBLIC | username1, username2, ... New user creation:Create user username identified by password;Example:
Create user sam identified by svcoe;
If you want to connect the user sam, then give the following syntax:
Grant connect to sam;
Grant Privileges on Tables
You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.
Privilege Description
Select Ability to query the table with a select statement.
Insert Ability to add new rows to the table with the insert statement.
Update Ability to update rows in the table with the update statement.
Delete Ability to delete rows from the table with the delete statement.
References
Ability to create a constraint that refers to the table.
Alter Ability to change the table definition with the alter table statement.
Index Ability to create an index on the table with the create index statement.
The syntax for granting privileges on a table is:
grant privileges on object to user; - For example, if you wanted to grant select, insert, update, and delete privileges on a table called student to a user name sam, you would execute the following statement:grant select, insert, update, delete on student to sam;
18
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
grant all on student to sam;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
grant select on sam to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
revoke privileges on object from user;
For example, if you wanted to revoke delete privileges on a table called student from a user named sam, you would execute the following statement:
revoke delete on student from sam;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
revoke all on student from sam; If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
revoke all on student from sam;
Result:
19
EX. NO: 6 USE OF SELECT STATEMENT
Aim:
ENO ENAME SAL AGE
----------------------------------------------------
1 sayee 2890 192 rama 5000 203 meera 6000 204 sayee 4536 205 bala 7780 20
SELECT SINGLE COLUMN:SQL> select ename from employee;
ENAME
-----------------------------------------------sayeeramameerasayeebala
SELECT MULTICOLUMN:
SQL> select eno,ename,sal from employee;
ENO ENAME SAL
1 sayee 28902 rama 50003 meera 60004 sayee 45365 bala 7780
SELECT ALL:SQL> select * from employee;
ENO ENAME SAL AGE
1 sayee 2890 192 rama 5000 203 meera 6000 204 sayee 4536 205 bala 7780 20
SELECTION WITH ARITHMETIC OPERATION:SQL> select eno,ename,sal+1000 from employee;
20
ENO ENAME SAL+1000
1 sayee 3890 2 rama 6000 3 meera 7000 4 sayee 5536 5 bala 8780
SELECTION WITH ALIAS:
SQL> select eno,ename ,sal+1000 withbonus from employee;
ENO ENAME WITHBONUS
1 sayee 3890 2 rama 6000 3 meera 7000 4 sayee 5536 5 bala 8780
SQL> select eno,ename,sal+1000 “withbonus” from employee;
ENO ENAME withbonus
1 sayee 3890 2 rama 6000 3 meera 7000 4 sayee 5536 5 bala 8780
SQL> select ename, eno, sal+1000 as withbonus from employee;
ENAME ENO WITHBONUS
sayee 1 3890rama 2 6000meera 3 7000sayee 4 5536
bala 5 8780
RENAME:SQL> select sal “salary” from employee;
salary28905000600045367780
SELECTION WITH CONCATENATION OPERATOR:SQL> select eno || ename from employee;
21
ENO||ENAME
1sayee2rama3meera4sayee5bala
SELECTION WITH LITERAL CHARACTER STRINGS:SQL> select ename || ‘ ‘ || ‘getting’ || ‘ ‘ || sal from employee;
ENAME|| ‘ ‘ || ‘GETTING’ || ‘ ‘ ||SAL
sayeegetting2890ramagetting5000meeragetting6000sayeegetting4536balagetting7780
SELECT DISTINCT:SQL> select distinct ename from employee;
ENAME
meerabalasayeerama
SQL> select distinct eno from employee;
ENO
12453
SQL> select * from cust;
CNAME CID CADDR CACCNO CACCTYPE CBALANCE
Hari 1 Chennai 1001 savings 15000Shriram 2 Pondy 1002 savings 25000Chamundi 3 Salem 1003 fd 36200Madhan 4 Salem 1004 checkings 5000Subha 5 Trichy 1005 checkings 10000Jayashree 6 Pondy 1006 fd 15000Sridharan 7 Kanchi 1007 fd 22000
7 rows selected.
22
SQL> select cname,caddr from cust where cbalance between 1000 and 10000;
CNAME CADDR--------------- ----------Madhan SalemSubha Trichy
SQL> > select cname,cid,cacctype from cust where cacctype in ('savings','checkings','fd'); CNAME CID CACCTYPE--------------- ---------- ----------Hari 1 savingsShriram 2 savingsChamundi 3 fdMadhan 4 checkingsSubha 5 checkingsJayashree 6 fd
7 rows selected.
SQL> select cname,cid,cacctype from cust where cacctype not in ('savings','checkings');
CNAME CID CACCTYPE--------------- ---------- ----------Chamundi 3 fdJayashree 6 fdSridharan 7 fd
SQL> select cname,cbalance from cust where cname like '_a%an';
CNAME CBALANCE--------------- ----------Madhan 5000
SQL> select cname,cbalance from cust where cbalance>=15000;
CNAME CBALANCE--------------- ----------Hari 15000Shriram 25000Chamundi 36200Jayashree 15000Sridharan 22000
5 rows selected.
23
SQL> select cname,cacctype,cbalance from cust where cbalance>20000 and cacctype=’fd’;;vCpe=’fd’;;
CNAME CACCTYPE CBALANCE
Chamundi fd 36200Sridharan fd 22000
2 rows selected.
Result:
24
EX. NO: 7 JOIN OPERATIONS
Aim:
SQL>create table locn (lid number(5),city varchar(10),area varchar(5),primary key(lid));Table created;SQL>desc locn; Name Null? Type ----------------------------- -------- ---------------------- LID NOT NULL NUMBER(5) CITY VARCHAR2(10) AREA VARCHAR2(5)
SQL>create table dep(dno number(5),dname varchar(10),lid number(5),primary key(dno),foreign key(lid) references locn(lid));Table created;SQL>desc dep; Name Null? Type ----------------------- -------- -------------------- DNO NOT NULL NUMBER(5) DNAME VARCHAR2(10) LID NUMBER(5)
SQL>create table emp(eid number(5),ename varchar(10),dno number(5),esal number(10),jobid number(5),mgrid varchar(5),primary key(eid),foreign key(dno) references dep(dno));Table created;SQL>desc emp;
Name Null? Type -------------------------------- -------- ----------------------- EID NOT NULL NUMBER(5) ENAME VARCHAR2(10) DNO NUMBER(5) ESAL NUMBER(10) JOBID VARCHAR2(5) MGRID NUMBER(3)SQL>create table grade(gno number(5),ls number(8),hs number(8));
Table created;SQL>desc grade;Name Null? Type GNO NOT NULL NUMBER(5)
25
LS NUMBER(8) HS NUMBER(8)
SQL>1 row created insert into locn values(&lid,'&city','&area');enter lid:1enter city:chennaienter area:aaaold 1: insert into locn values(&lid,'&city','&area')new 1: insert into locn values(1,'chennai','aaa')SQL>insert into dip values(&dno,'&dname',&lid);enter dno:1enter dname:adminenter lid:2old 1: insert into dep values(&dno,'&dname',&lid)new 1: insert into dep values(1,'admin',2)1 row createdSQL>insert into emp10 values(&eid,'&ename',&dno,&esal, &jobid,&mgr id);enter eid:3enter ename:zzzenter dno:3enter esal :3500enter jobid:2enter mgr id:2old 1: insert into emp values(&eid,'&ename',&dno,&esal,&jobid,&mgrid)new 1: insert into emp values(1,'zzz',3500,2,2)1 row created
SQL>insert into grade1values(&gno,&ls,&hs);enter gno:1enter ls:1000enter hs:2000old 1: insert into grade values(&gno,&ls,&hs)new 1: insert into grade values(1,1000,2000)1 row created
SQL> select * from dip; DNO DNAME LID ---------- ---------- ----------- 1 admin 2 2 Finance 3 3 hr 1 4 market 3 5 sales 1
26
SQL> select * from locn;
LID CITY AREA
1 chennai aaa 2 bombay bbb 3 calcutta ccc
SQL> select * from grade;
GNO LS HS 1 1000 2000 2 2001 3000 3 3001 4000 4 4001 5000
SQL> select * from emp;
EID ENAME DNO ESAL JOBID MGRID
5 bbc 4700 2 1 xxx 1 4000 1 2 yyy 2 2000 2 1 3 zzz 3 3500 2 2 4 abc 2 4500
EQUI-JOIN~~~~~~~~~SQL>select e.ename,d.dname from emp e,dep d where e.dno=d.dno;
ENAME DNAME
xxx adminyyy financezzz hrabc finance
NON-EQUIJOIN~~~~~~~~~~~~SQL> select e.ename,e.esal,g.gno from emp e,grade g where e.esal between g.ls and g.hs;ENAME ESAL GNO---------- ---------- ----------bbc 4700 4xxx 4000 3yyy 2000 1zzz 3500 3abc 4500 4
27
LEFTOUT-JOIN~~~~~~~~~~~~SQL> select e.ename,d.dname from emp e,dep d where e.dno(+)=d.dno;
ENAME DNAME---------- ----------------xxx adminyyy financeabc financezzz hr market salesRIGHTOUTER-JOINSQL> select e.ename,d.dname from emp e,dep d where e.dno=d.dno(+);
ENAME DNAME---------- ---------------bbcxxx adminyyy financezzz hrabc finance
FULLOUTER-JOINSQL>select e.ename,d,dname from emp e,dep d where e.dno(+)=(+)d.dno;
ENAME DNAME-------- ---------------bbc xxx adminyyy financezzz hrabc finance market sales
SELFJOIN----TO DISPLAY ENAME & THEIR MANAGER NAMESSQL> select e.ename,m.ename from emp e,emp m where e.mgrid=m.eid;
ENAME ENAME---------- ----------bbc yyyyyy xxxzzz yyy
28
SELFJOIN----TO DISPLAY MANAGER'S SALARY FOR EVERY EMPLOYEESQL> select e.ename,m.esal from emp e,emp m where e.mgrid=m.eid;
ENAME ESAL---------- ----------------bbc 2000yyy 4000zzz 2000
29
Result:
EX. NO: 8 SINGLE ROW FUNCTIONS
Aim:.
Types of single row functions are: Character Functions Number Functions Date Functions
SQL> DESC EMPLOYEE22; Name Null? Type ----------------------- -------- ----------------------- ENO NOT NULL NUMBER(5) ENAME VARCHAR2(50) DEPTID NUMBER(3) SALARY NUMBER(7) JOB VARCHAR2(5) MGRID NUMBER(3) COM VARCHAR2(6)SQL> select * from employee22;
ENO ENAME DEPTID SALARY JOB MGRID COM
1 x 10 100000 MD 0 15 2 y 20 50000 Mgr 1 10 3 z 20 25000 Mgr 2 2
ENO ENAME DEPTID SALARY JOB MGRID COM
4 a 30 15000 Mgr 2 1 5 b 40 10000 Emp 3 5
SQL> select * from employee22 where LOWER(ename)='x'; ENO ENAME DEPTID SALARY JOB MGRID COM 1 X 10 100000 MD 0 15
SQL> select * from employee22 where UPPER(ename)='y';no rows selectedSQL> select * from employee22 where UPPER(ename)='Y';ENO ENAME DEPTID SALARY JOB MGRID COM 2 y 20 50000 Mgr 1 10
SQL> select 'Salary of ' || ename || ' is ' || salary from employee22;
30
'SALARYOF'||ENAME||'IS'||SALARY--------------------------------------------------------------------------Salary of X is 100000Salary of y is 50000Salary of z is 25000Salary of a is 15000Salary of b is 10000
SQL> select * from employee22 where LOWER(ename)='x';ENO ENAME DEPTID SALARY JOB MGRID COM 1 X 10 100000 MD 0 15
SQL> select * from employee22 where UPPER(ename)='y';no rows selected
SQL> select * from employee22 where UPPER(ename)='Y';
ENO ENAME DEPTID SALARY JOB MGRID COM 2 y 20 50000 Mgr 1 10
SQL> select 'Salary of ' || ename || ' is ' || salary from employee22;
'SALARYOF'||ENAME||'IS'||SALARY--------------------------------------------------------------------------------Salary of X is 100000Salary of y is 50000Salary of z is 25000Salary of a is 15000Salary of b is 10000
SQL> create table sample(name varchar2(30),fname varchar2(10),lname varchar2(10));Table Created.SQL> desc sample Name Null? Type ------------------- -------- ---------------------------- NAME VARCHAR2(30) FNAME VARCHAR2(10) LNAME VARCHAR2(10)
SQL> alter table sample add(fno float(10));Table altered.SQL> desc sample Name Null? Type---------------------- -------- ---------------------------- NAME VARCHAR2(30) FNAME VARCHAR2(10) LNAME VARCHAR2(10) FNO FLOAT(10)
31
SQL> insert into sample values('Hello world','Lovelyn','Divya',120.856);1 row created.SQL> insert into sample values('Welcome everyone','Dhivyaa','Meens',340.15);1 row created.SQL> insert into sample values('Hi everybody','Harsha','Innanji',280.59);1 row created.SQL> select * from sample;NAME FNAME LNAME FNO--------------------- ---------- ---------- -------------------Hello world Lovelyn Divya 120.9Welcome everyone Dhivyaa Meens 340.2Hi everybody Harsha Innanji 280.6SQL> select concat(fname,lname),fno from sample;CONCAT(FNAME,LNAME) FNO-------------------- ------------------------LovelynDivya 120.9DhivyaaMeens 340.2HarshaInnanji 280.6SQL> select upper(name) from sample;
UPPER(NAME)------------------------------HELLO WORLDWELCOME EVERYONEHI EVERYBODYSQL> select lower(lname) from sample;LOWER(LNAME)----------divyameensinnanjiSQL> select substr(fname,1,5) from sample;
SUBST-------------LovelDhivyHarsh
SQL> select length(name) from sample;LENGTH(NAME)------------------------- 11 16 12
32
SQL> select length(lname) from sample;LENGTH(LNAME)------------- 5 5 7
SQL> select name,instr(name,1) from sample;NAME INSTR(NAME,1)------------------------- ----------------------Hello world 0Welcome everyone 0Hi everybody 0
SQL> select name,instr(name,'e',1) from sample;NAME INSTR(NAME,'E',1)------------------------- -----------------Hello world 2Welcome everyone 2Hi everybody 4
SQL> select fname,instr(fname,'a') from sample;FNAME INSTR(FNAME,'A')---------- -----------------------------------Lovelyn 0Dhivyaa 6Harsha 2SQL> select lpad(lname,10,'*') from sample;LPAD(LNAME)------------------*****Divya*****Meens***InnanjiSQL> select rpad(fname,10,'*')from sample;RPAD(FNAME)----------Lovelyn***Dhivyaa***Harsha****SQL> select trim('d' from 'dhivyaa')from sample;TRIM('------hivyaahivyaahivyaaSQL> select trim('d' from 'dhivyaa')from sample where fno=340.2;TRIM('------
33
hivyaaSQL> select trim('a' from fname)from sample;TRIM('A'FR----------LovelynDhivyHarshNUMBER FUNCTIONS:SQL> select ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) from DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
45.92 46 50
SQL> select trunc(45.923,2),trunc(45.923),trunc(45.923,-2) from DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-2)--------------- ------------- ------------------------------------------------------- 45.92 45 0
SQL> desc sample Name Null? Type ----------------------- -------- ---------------------------- NAME VARCHAR2(30) FNAME VARCHAR2(10) LNAME VARCHAR2(10) FNO FLOAT(10) HIREDATE DATESQL> insert into sample values('REC CSE','Anitha','Anu',320.7,'21-mar-88');
1 row created.SQL> insert into sample values('Hi chennai','aishwarya','sukumar',170,'30-oct-87');1 row created.SQL> select * from sample;NAME FNAME LNAME FNO HIREDATEHello world Lovelyn Divya 120.9Welcome everyone Dhivyaa Meens 340.2Hi everybody Harsha Innanji 280.6REC CSE Anitha Anu 320.7 21-MAR88Hi chennai aishwarya sukumar 170 30-OCT-87
SQL> select lname,hiredate from sample where lname like 'a%';no rows selectedSQL> select lname,hiredate from sample where lname like 'A%';LNAME HIREDATE---------- -----------------------
34
Anu 21-MAR-88SQL> select sysdate from DUAL;SYSDATE-----------------22-AUG-07
SQL> select fname,(sysdate-hiredate)/7 as weeks from sample where fno=170;FNAME WEEKS---------- ----------aishwarya 1033.78013
SQL> select fname,hiredate,months_between(sysdate,hiredate) from sample where fno=320.7;
FNAME HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)Anitha 21-MAR-88 233.047329
SQL> select fname,hiredate,add_months(hiredate,6),next_day(hiredate,'friday'), last_day (hiredate) from sample;
FNAME HIREDATE ADD_MONTH NEXT_DAY( LAST_DAY(LovelynDhivyaaHarshaAnitha 21-MAR-88 21-SEP-88 25-MAR-88 31-MAR-88aishwarya 30-OCT-87 30-APR-88 06-NOV-87 31-OCT-87
SQL> select fname ,hiredate,round(hiredate,'Month'),trunc(hiredate,'month') from sample where fno=170;
FNAME HIREDATE ROUND(HIR TRUNC(HIR---------- --------- --------- -----------------------------------------Aishwarya 30-OCT-87 01-NOV-87 01-OCT-87
35
Result:
EX. NO: 9 MULTIROW FUNCTIONS
Aim:
Types of multi row functions are:
Order By Group By Aggregate Functions
SQL> select * from cust;
CNAME CID CADDR CACCNO CACCTYPE CBALANCEHari 1 Chennai 1001 Savings 15000Shriram 2 Pondy 1002 savings 25000Chamundi 3 Salem 1111 fd 36200Madhan 4 Salem 1004 checkings 5000Subha 5 Trichy 1005 checkings 10000Jayashree 6 Pondy 1006 fd 15000Sridharan 7 Kanchi 1007 fd 22000
ORDER BY========SQL> select cname from cust order by cname desc;
CNAME---------------SubhaSridharanShriramMadhanJayashreeChamundiHari7 rows selected.
SQL> select cname,caccno,cbalance from cust order by cbalance desc,cname asc;
CNAME CACCNO CBALANCE--------------- ---------- --------------------
36
Chamundi 1003 36200Shriram 1002 25000Sridharan 1007 22000Hari 1001 15000Jayashree 1006 15000Subha 1005 10000Madhan 1004 50007 rows selected.GROUP BY=========
SQL> select max(cbalance),cacctype from cust group by cacctype;
MAX(CBALANCE) CACCTYPE
25000 savings 10000 checkings 36200 fdAGGREGATE FUNCTIONS=======================SQL> select count(*) from cust; COUNT(*) 7SQL> select count(distinct cname) from cust;COUNT(CNAME) 7SQL>select sum(cbalance) as totbal,avg(sum(cbalance)) as average from cust where cacctype='savings';
TOTBAL AVERAGE
40000 20000
SQL> select max(cbalance),min(cbalance) from cust; MAX(CBALANCE) MIN(CBALANCE)
36200 5000
37
Result:
EX. NO: 10 PROCEDURES
Aim:
Procedures:1. Create a table using create command.
SQL> create table stud(rno number(2),mark1 number(3),mark2 number(3),total number(3),primary key(rno));
Table created.
SQL> desc stud;
Name Null? Type
RNO NOT NULL NUMBER(2)MARK1 NUMBER(3)MARK2 NUMBER(3)TOTAL NUMBER(3)
SQL> select * from stud;
RNO MARK1 MARK2 TOTAL 1 80 85 0 2 75 84 0 3 65 80 0 4 90 85 0
2. Write a code for creating a procedure
SQL> create or replace procedure studd(rnum number) is 2 m1 number; 3 m2 number; 4 total number; 5 begin 6 select mark1,mark2 into m1,m2 from stud where rno=rnum; 7 if m1<m2 then 8 update stud set total=m1+m2 where rno=rnum; 9 end if; 10 end; 11 /
38
Procedure created.
3. Execute a procedure
SQL> exec studd(1);
PL/SQL procedure successfully completed.SQL> select * from stud;
RNO MARK1 MARK2 TOTAL 1 80 85 165 2 75 84 0 3 65 80 0 4 90 85 0
SQL> exec studd(4);
PL/SQL procedure successfully completed.
SQL> select * from stud;
RNO MARK1 MARK2 TOTAL 1 80 85 165 2 75 84 0 3 65 80 0 4 90 85 0
SQL> exec studd(2);
PL/SQL procedure successfully completed.
SQL> exec studd(3);
PL/SQL procedure successfully completed.
SQL> select * from stud;
RNO MARK1 MARK2 TOTAL
1 80 85 165 2 75 84 159 3 65 80 145 4 90 85 0SQL> desc emp17;Name Null? Type ENO NOT NULL NUMBER(2)
39
ENAME NOT NULL VARCHAR2(18) DNO NOT NULL NUMBER(3) SAL NUMBER(8) MID NUMBER(3)
SQL> select * from emp17; ENO ENAME DNO SAL MID 1 Akshaya 102 50000 1 2 Srikantan 105 12000 1 3 Banupriya 100 32000 1 4 Chamundi 100 28000 3 5 Janani 101 24000 3 6 Subha 100 20000 4 7 Sridhar 105 35000 1 8 Shree 105 10000 2 9 Krithi 103 29000 39 rows selected.SQL> create or replace procedure dnsal(enum number) is 2 s1 number; 3 sal number; 4 begin 5 select sal into s1 from emp17 where eno=enum; 6 if s1>30000 then 7 update emp17 set sal=s1+500 where eno=enum; 8 end if; 9 if s1<30000 then 10 update emp17 set sal=s1+250 where eno=enum; 11 end if; 12 end; Procedure created.SQL> exec dnsal(8);PL/SQL procedure successfully completed.SQL> select * from emp17 where eno=8;
ENO ENAME DNO SAL MID
8 Shree 105 10250 2
SQL> exec dnsal(1);PL/SQL procedure successfully completed.SQL> select * from emp17 where eno=1;
ENO ENAME DNO SAL MID 1 Akshaya 102 50500 1
ADDITION
SQL> set serveroutput onSQL> declare
40
2 a number; 3 b number; 4 c number; 5 begin 6 a:=&a; 7 b:=&b; 8 c:=a+b; 9 dbms_output.put_line('sum of'||a||'and'||b||'is'||c); 10 end;
Enter value for a: 5old 6: a:=&a;new 6: a:=5;Enter value for b: 5old 7: b:=&b;new 7: b:=5;sum of5and5is10PL/SQL procedure successfully completed.MAXIMUM NOSQL> set serveroutput onSQL> declare 2 a number; 3 b number; 4 c number; 5 d number; 6 begin 7 dbms_output.put_line('enter a:'); 8 a:=&a; 9 dbms_output.put_line('enter b:'); 10 b:=&b; 11 dbms_output.put_line('enter c:'); 12 c:=&b; 13 if(a>b)and(a>c) then 14 dbms_output.put_line('A is maximum'); 15 elsif(b>a)and(b>c)then 16 dbms_output.put_line('B is maximum'); 17 else 18 dbms_output.put_line('C is maximum'); 19 end if; 20* end;
Enter value for a: 9old 8: a:=&a;new 8: a:=9;Enter value for b: 6old 10: b:=&b;new 10: b:=6;Enter value for c: 10old 12: c:=&b;
41
new 12: c:=10;enter a:enter b:enter c:C is maximumPL/SQL procedure successfully completed.REVERSING THE NO. SQL> set serveroutput onSQL> declare 2 given_number varchar(5):='1234'; 3 str_length number(2); 4 inverted_number varchar(5); 5 begin 6 str_length:=length(given_number); 7 for cntr in reverse 1..str_length 8 loop 9 inverted_number:=inverted_number||substr(given_number,cntr,1); 10 end loop; 11 dbms_output.put_line('the given no is'||given_number); 12 dbms_output.put_line('the inverted number is'|| inverted_number); 13* end;The given no is1234The inverted number is4321PL/SQL procedure successfully completed.SUM OF 100 NO. SQL> set serveroutput onSQL> declare 2 a number; 3 s1 number default 0; 4 begin 5 a:=1; 6 loop 7 s1:=s1+a; 8 exit when (a=100); 9 a:=a+1; 10 end loop; 11 dbms_output.put_line('sum bt 1 to 100 is'|| s1); 12 end;Sum of 1 to 100 is 5050PL/SQL procedure successfully completed.SUM OF ODD NO.USING USER I/PSQL> set serveroutput onSQL> declare 2 n number; 3 sum1 number default 0; 4 endvalue number; 5 begin 6 endvalue:=&endvalue;
42
7 n:=1; 8 for n in 1..endvalue 9 loop 10 if mod(n,2)=1 11 then 12 sum1:=sum1+n; 13 end if; 14 end loop; 15 dbms_output.put_line('sum ='||sum1); 16* end;SQL> Enter value for endvalue: 5old 6: endvalue:=&endvalue;new 6: endvalue:=5;sum =9PL/SQL procedure successfully completed.SUM OF ODD NO USING WHILE LOOP SQL> set serveroutput onSQL> declare 2 n number; 3 sum1 number default 0; 4 endvalue number; 5 begin 6 endvalue:=&endvalue; 7 n:=1; 8 while(n<endvalue) 9 loop 10 sum1:=sum1+n; 11 n:=n+2; 12 end loop; 13 dbms_output.put_line('sum of odd no. bt 1 and' ||endvalue||'is'||sum1); 14 end;Enter value for endvalue: 5old 6: endvalue:=&endvalue;new 6: endvalue:=5;sum of odd no. bt 1 and5is4PL/SQL procedure successfully completed.SQL> /Enter value for endvalue: 7old 6: endvalue:=&endvalue;new 6: endvalue:=7;sum of odd no. bt 1 and7is9PL/SQL procedure successfully completed.SALARYSQL> set serveroutput onSQL> declare 2 ename varchar2(15); 3 basic number;
43
4 da number; 5 hra number; 6 pf number; 7 netsalary number; 8 begin 9 ename:=&ename;10 basic:=&basic;11 da:=basic*(41/100);12 hra:=basic*(15/100);13 if(basic<3000)14 then15 pf:=basic*(5/100);16 elsif(basic>=3000 and basic<=5000)17 then18 pf:=basic*(7/100);19 elsif(basic>=5000 and basic<=8000)20 then21 pf:=basic*(8/100);22 else23 pf:=basic*(10/100);24 end if;25 netsalary:=basic+da+hra-pf;26 dbms_output.put_line('employee name:'||ename);27 dbms_output.put_line('providend fund:'||pf);28 dbms_output.put_line('net salary:'||netsalary);29* end;enter value for ename: 'ice'old 9: ename:=&ename;new 9: ename:='ice';enter value for basic: 1000old 10: basic:=&basic;new 10: basic:=1000;employee name:iceprovidend fund:50net salary:1510PL/SQL procedure successfully completed.EXAMPLE FOR LOOPSQL> set serveroutput onSQL> declare 2 begin 3 for i in 1..10 4 loop 5 dbms_output.put_line(to_char(i)); 6 end loop; 7* end;
123
44
45678910PL/SQL procedure successfully completed.EXAMPLE FOR WHILESQL> set serveroutput onSQL> declare 2 i number:=0; 3 j number:=0; 4 begin 5 while i<=100 loop 6 j:=j+1; 7 i:=i+2; 8 end loop; 9 dbms_output.put_line(to_char(i)); 10 end;
102PL/SQL procedure successfully completed.EXAMPLE FOR LOOP USING EXITSQL> set serveroutput onSQL> declare 2 a number:=100; 3 begin 4 loop 5 a:=a+25; 6 exit when a=250; 7 end loop; 8 dbms_output.put_line(to_char(a)); 9 end;250PL/SQL procedure successfully completed.PRIME OR NOTSQL> set serveroutput onSQL> declare 2 no number(3):=&no; 3 a number(4); 4 b number(2); 5 begin 6 for i in 2..no-1 7 loop 8 a:=no MOD i; 9 if a=0 10 then 11 GOTO out;
45
12 end if; 13 end loop; 14 <<out>> 15 if a=1 16 then 17 dbms_output.put_line(no||'is a prime'); 18 else 19 dbms_output.put_line(no||'is not a prime'); 20 end if; 21* end;Enter value for no: 7old 2: no number(3):=&no;new 2: no number(3):=7;7is a primePL/SQL procedure successfully completed.AREA CALCULATIONSQL> set serveroutput onSQL> declare 2 pi constant number(4,2):=3.14; 3 radius number(5); 4 area number(14,2); 5 begin 6 radius:=3; 7 while radius<=7 8 loop 9 area:=pi*power(radius,2); 10 insert into areas values(radius,area); 11 radius:=radius+1; 12 end loop; 13 end;PL/SQL procedure successfully completed.SQL> select* from areas; RADIUS AREA ---------- ---------- 3 28.26 4 50.24 5 78.5 6 113.04 7 153.86 7 154 5 78.54 3 28.26 4 50.24 5 78.5 6 113.04
RADIUS AREA ---------- ---------- 7 153.86
46
12 rows selected.
Result:
47
EX. NO: 11 FUNCTIONS
Aim:
SQL> create table stud(rno number(2),mark1 number(3),mark2 number(3),total number(3),primary key(rno));
Table created.
SQL> desc stud; Name Null? Type ---------------------- ------- - ------------------- RNO NOT NULL NUMBER(2) MARK1 NUMBER(3) MARK2 NUMBER(3) TOTAL NUMBER(3)
SQL> select * from stud;
RNO MARK1 MARK2 TOTAL ---------- ---------- ---------- -------------- 1 80 85 0 2 75 84 0 3 65 80 0 4 90 85 0
SQL> create or replace function stude(rnum number) return number is 2 total number; 3 m1 number; 4 m2 number; 5 begin 6 select mark1,mark2 into m1,m2 from stud where rno=rnum; 7 total:=m1+m2; 8 return total; 9 end; 10 /
Function created.
SQL> select stude(2) from dual;
STUDE(2) ---------- 159SQL> create table purchase (icode number(3),iname varchar2(13),price number(6),quantity number(3),rate number(8),primary key(icode),unique(iname));
48
Table created.
SQL> desc purchase;
Name Null? Type
ICODE NOT NULL NUMBER(3)INAME VARCHAR2(13)PRICE NUMBER(6)QUANTITY NUMBER(3)RATE NUMBER(8)
SQL> select * from purchase;
ICODE INAME PRICE QUANTITY RATE 100 PenSet 20 10 0 101 ParkerPen 60 10 0 102 180pg Note 24 10 0 103 80pg Note 10 25 0 104 StickFile 10 20 0
SQL> create or replace function pur(itmcd number) return number is 2 qt number; 3 pr number; 4 rate number; 5 begin 6 select price,quantity into pr,qt from purchase where icode=itmcd; 7 rate:=qt*pr; 8 return rate; 9 end;
Function created.SQL> select pur(102) from dual;
PUR(102) ----------- 240
Result:
49
EX. NO: 12 CURSOR
Aim:
SQL> create table ssss(cname varchar2(20),sal number);Table created.
SQL> desc ssss;
Name Null? TypeCNAME VARCHAR2(20) SAL NUMBER
SQL> insert into ssss values('&cname',&sal);
Enter value for cname: vijiEnter value for sal: 17000old 1: insert into ssss values('&cname',&sal)new 1: insert into ssss values('viji',17000)
1 row created.
SQL> /Enter value for cname: sreeEnter value for sal: 12000old 1: insert into ssss values('&cname',&sal)new 1: insert into ssss values('sree',12000)
1 row created.
SQL> /Enter value for cname: santhiEnter value for sal: 30000old 1: insert into ssss values('&cname',&sal)new 1: insert into ssss values('santhi',30000)
1 row created.
SQL> /Enter value for cname: babuEnter value for sal: 20000old 1: insert into ssss values('&cname',&sal)new 1: insert into ssss values('babu',20000)
1 row created.SQL> select * from ssss;CNAME SAL
50
viji 17000sree 12000santhi 30000babu 20000SQL> set serveroutput on;SQL> declare 2 cursor emp_cur is select cname,sal from ssss; 3 name ssss.cname%type; 4 salary ssss.sal%type; 5 begin 6 open emp_cur; 7 dbms_output.put_line('name salary'); 8 dbms_output.put_line('-----------'); 9 loop 10 fetch emp_cur into name,salary; 11 exit when(emp_cur%notfound); 12 dbms_output.put_line(name||' '||salary); 13 end loop; 14 close emp_cur; 15 end; 16 /
name salary
viji 17000sree 12000santhi 30000babu 20000
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE FUNCTION FIND 2 (cnam in varchar2) 3 RETURN number 4 is 5 cid number; 6 cursor c1 7 is 8 select cno from ttt where cname=cnam; 9 begin 10 open c1; 11 fetch c1 into cid; 12 if c1%notfound then 13 cid:=9999; 14 end if; 15 close c1; 16 return cid; 17 end;
51
18 /Function created.
SQL> select * from ttt;
CNO CNAME MARK
1 cts 99 2 tcs 55 3 ibm 89
SQL> select FIND('tcs') from dual;FIND('TCS') 2SQL> select FIND('ibm') from dual;FIND('IBM') 3SQL> select FIND('hcl') from dual;FIND('HCL') 9999
Result:
52
EX NO: 13 TRIGGER
Aim:
TABLE
TRIGGER WITH BEFORE UPDATE
SQL> create table orders(order_id number(5),quantity number(4),cost_per_item number(6,2),total_cost number(8,2),updated_date date,updated_by varchar2(10));
Table created.INSERT----------SQL> insert into orders(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_item);Enter value for order_id: 1Enter value for quantity: 4Enter value for cost_per_item: 20old 1: insert into orders(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_itnew 1: insert into orders(order_id,quantity,cost_per_item) values(1,4,20)1 row created.
SQL> /Enter value for order_id: 2Enter value for quantity: 5Enter value for cost_per_item: 30old 1: insert into orders(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_itnew 1: insert into orders(order_id,quantity,cost_per_item) values(2,5,30)1 row created.SQL> /Enter value for order_id: 3Enter value for quantity: 6Enter value for cost_per_item: 25old 1: insert into orders(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_itnew 1: insert into orders(order_id,quantity,cost_per_item) values(3,6,25)1 row created.SQL> select * from orders;
ORDER_ID QUANTITY COST_PER_ITEM TOTAL_COST UPDATED_D UPDATED_BY
53
1 4 20 2 5 30 3 6 25TRIGGER SCRIPT------------------------
SQL> create or replace trigger orders_before_update 2 before update 3 on orders 4 for each row 5 declare 6 v_username varchar2(10); 7 begin 8 select user into v_username from dual; 9 :new.updated_date:=sysdate; 10 :new.updated_by:=v_username; 11 end; 12 /
Trigger created.SQL> update orders set total_cost=3000 where order_id=2;1 row updated.
TRIGGER WITH AFTER UPDATETABLE----------SQL> create table orders30(order_id number(5),quantity number(4),cost_per_item number(6,2),total_cost number(8,2));
Table created.
SQL> create table orders_audit(order_id number,quantity_before number,quantity_after number,username varchar2(20));
Table created.
SQL> insert into orders30(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_item);Enter value for order_id: 100Enter value for quantity: 5Enter value for cost_per_item: 10old 1: insert into orders30(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_new 1: insert into orders30(order_id,quantity,cost_per_item) values(100,5,10)
1 row created.
SQL> /
54
Enter value for order_id: 101Enter value for quantity: 4Enter value for cost_per_item: 20old 1: insert into orders30(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_new 1: insert into orders30(order_id,quantity,cost_per_item) values(101,4,20)1 row created.SQL> /Enter value for order_id: 102Enter value for quantity: 5Enter value for cost_per_item: 30old 1: insert into orders30(order_id,quantity,cost_per_item) values(&order_id,&quantity,&cost_per_new 1: insert into orders30(order_id,quantity,cost_per_item) values(102,5,30)
1 row created.
SQL> create or replace trigger orders_after_update 2 AFTER UPDATE 3 ON orders30 4 for each row 5 declare 6 v_username varchar2(10); 7 begin 8 select user into v_username 9 from dual; 10 insert into orders_audit 11 (order_id, 12 quantity_before, 13 quantity_after, 14 username) 15 values 16 (:new.order_id, 17 :old.quantity, 18 :new.quantity, 19 v_username); 20 end; 21 /
Trigger created.SQL> update orders30 set quantity=25 where order_id=101;
1 row updated.SQL> select *from orders_audit;ORDER_ID QUANTITY_BEFORE QUANTITY_AFTER USERNAME 101 4 25 CSE3090
55
Result:
EX NO: 14 Embedded SQL
Aim:
Procedure:
SQL can be embedded within procedural programming languages. These language (sometimes referred to as 3GLs) include C/C++, Cobol, Fortran, and Ada. Thus the embedded SQL provides the 3GL with a way to manipulate a database, supporting:
highly customized applications background applications running without user intervention database manipulation which exceeds the abilities of
simple SQL applications linking to Oracle packages, e.g. forms and
reports applications which need customized window interfaces
SQL precompiler
A precompiler is used to translate SQL statements embedded in a host language into DBMS library calls which can be implemented in the host language.
Editor
Precompiler
Linker
Compiler
host program + embedded SQL
executable program
object (binary) program
host program + translated SQL
DBMS and other libraries
VariablesVariables to be shared between the embedded SQL code and the 3GL have to be specified in the program.
EXEC SQL begin declare section; varchar userid[10],password[10],cname[15]; int cno; EXEC SQL end declare section;
56
We also should declare a link to the DBMS so that database status information can be accessed. EXEC SQL include sqlca;This allows access to a structure sqlca, of which the most common elementsqlca.sqlcode has the value 0 (operation OK), >0 (no data found), and <0 (an error).Connecting to the DBMSBefore operations can be performed on the database, a valid connection has to be established. EXEC SQL connect :userid identified by :password;
In all SQL statements, variables with the ‘:’ prefix refer to shared host variables, as opposed to database variables (e.g. row or column identifiers).
This assumes that userid and password have been properly declared and initialised.
When the program is finished using the DBMS, it should disconnect using: EXEC SQL commit release;Queries producing a single rowA single piece of data (or row) can be queried from the database so that the result is accessible from the host program.
EXEC SQL SELECT custnameINTO :cnameFROM customersWHERE cno = :cno;
Thus the custname with the unique identifier :cno is stored in :cname.However, a selection query may generate many rows, and a way is needed for the host program to access results one row at a time.
Select with a single result
57
Result: EX NO: 15 Banking
Aim:
Design Window
Adding LibraryProject = > References => Microsoft ActiveX Data Objects 2.0 LibraryCodingDim Con As New ADODB.ConnectionDim Rs As New ADODB.Recordset
Private Sub CmdAddNew_Click()Rs.AddNewtxtClear
58
Text1.SetFocusEnd Sub
Private Sub CmdDelete_Click()On Error Resume NextIf Rs.EOF ThenMsgBox "No Records"ElseRs.DeleteRs.MoveNextDisplayTextMsgBox "Record Deleted"End IfEnd Sub
Private Sub CmdFirst_Click()Rs.MoveFirstDisplayTextEnd Sub
Private Sub CmdNext_Click()Rs.MoveNextIf Not Rs.EOF ThenDisplayTextElseMsgBox "End Of The Record"Rs.MovePreviousEnd IfEnd Sub
Private Sub CmdUpdate_Click()Rs(0).Value = Val(Text1.Text)Rs(1).Value = Text2.TextRs(2).Value = Text3.TextRs(3).Value = Val(Text4.Text)Rs.UpdateMsgBox "Record Updated"End Sub
Private Sub Form_Load()Con.Open "Provider=MSDAORA.1;User ID=scott;Password=tiger;Data Source=rst;Persist Security Info=False"Con.CursorLocation = adUseClientRs.Open "select * from Bank", Con, adOpenKeyset, adLockOptimisticEnd Sub
Sub txtClear()Text1.Text = ""Text2.Text = ""
59
Text3.Text = ""Text4.Text = ""End Sub
Sub DisplayText()Text1.Text = Rs(0).ValueText2.Text = Rs(1).ValueText3.Text = Rs(2).ValueText4.Text = Rs(3).ValueEnd Sub
Private Sub CmdTransact_Click()Form2.ShowEnd Sub
Transaction WindowDesign
Coding
Dim Con As New ADODB.ConnectionDim Rs As New ADODB.Recordset
Private Sub CmdDeposit_Click()Dim AccNo As IntegerAccNo = Val(Text1.Text)Rs.MoveFirstDo While Not Rs.EOF If Rs(0).Value = AccNo Then Rs(3).Value = Rs(3).Value + Val(Text2.Text) Rs.Update MsgBox "Amount Deposited"
60
End If Rs.MoveNext Loop
End Sub
Private Sub CmdWithdraw_Click()Dim AccNo As Integer, Bal As DoubleRs.MoveFirstAccNo = Val(Text1.Text)Do While Not Rs.EOF If Rs(0).Value = AccNo Then Bal = Rs(3).Value - Val(Text2.Text) If Bal >= 500 Then Rs(3).Value = Rs(3).Value - Val(Text2.Text) Rs.Update MsgBox "Amount Withdrew" Else MsgBox "Balance Problem" End If End If Rs.MoveNextLoopEnd SubPrivate Sub Form_Load()Con.Open "Provider=MSDAORA.1;User ID=cse101;Password=cse101;Data Source=ibmrec;Persist Security Info=False"Con.CursorLocation = adUseClientRs.Open "select * from Bank", Con, adOpenKeyset, adLockOptimisticEnd Sub
61
Result:
EX NO: 16 Payroll Processing
Aim:
Dim Con As ADODB.ConnectionDim rs As ADODB.RecordsetPrivate Sub Command4_Click()Form2.ShowEnd SubPrivate Sub Command1_Click()rs.AddNewtxtClearText1.SetFocusEnd SubPrivate Sub Command2_Click()rs(0).Value = Val(Text1.Text)rs(1).Value = Text2.Textrs(2).Value = Text3.Textrs(3).Value = Text4.Textrs(4).Value = Text5.Textrs(5).Value = Text6.Text
62
rs(6).Value = Text7.Textrs.UpdateMsgBox "Record Updated"End SubPrivate Sub Command3_Click()On Error Resume NextIf rs.EOF ThenMsgBox "No Records"Elsers.Deleters.MoveNextDisplayTextMsgBox "Record Deleted"End IfEnd SubPrivate Sub Form_Load()Set Con = New ADODB.ConnectionSet rs = New ADODB.RecordsetCon.Open "Provider=MSDAORA.1;User ID=scott;Password=tiger;DataSource=employees;Persist Security Info=False"Con.CursorLocation = adUseClientrs.Open "select * from employees", Con, adOpenKeyset, adLockPessimisticEnd SubSub txtClear()Text1.Text = ""Text2.Text = ""Text3.Text = ""Text4.Text = ""Text5.Text = ""Text6.Text = ""Text7.Text = ""End SubSub DisplayText()Text1.Text = rs(0).ValueText2.Text = rs(1).ValueText3.Text = rs(2).ValueText4.Text = rs(3).ValueText5.Text = rs(4).ValueText6.Text = rs(5).ValueText7.Text = rs(6).ValueEnd SubPrivate Sub command5_click()Text3.Text = Val(Text2.Text * 29 / 100)Text4.Text = Val(Text2.Text * 60 / 100)Text5.Text = Val(Text2.Text * 6 / 100)If (Text2.Text >= 1500) ThenText6.Text = 1600
63
ElseText6.Text = 780End IfText7.Text = Val(Text2.Text) + Val(Text3.Text) + Val(Text4.Text) + Val(Text5.Text) - Val(Text6.Text)End Sub
EmployeesDesign Window
Adding Library
Project = > References => Microsoft ActiveX Data Objects 2.0 LibraryCodingDim Con As New ADODB.ConnectionDim Rs As New ADODB.RecordsetPrivate Sub CmdAddNew_Click()Rs.AddNewtxtClearText1.SetFocusEnd SubPrivate Sub CmdDelete_Click()On Error Resume NextIf Rs.EOF Then
64
MsgBox "No Records"ElseRs.DeleteRs.MoveNextDisplayTextMsgBox "Record Deleted"End IfEnd SubPrivate Sub CmdMoveFirst_Click()Rs.MoveFirstDisplayTextEnd SubPrivate Sub CmdMoveNext_Click()Rs.MoveNextIf Not Rs.EOF ThenDisplayTextElseMsgBox "End Of The Record"End IfEnd SubPrivate Sub CmdUpdate_Click()Rs(0).Value = Val(Text1.Text)Rs(1).Value = Text2.TextRs(2).Value = Text3.TextRs(3).Value = Text4.TextRs.UpdateMsgBox "Record Updated"End SubPrivate Sub Form_Load()Con.Open "Provider=MSDAORA.1;User ID=scott;Password=tiger;Data Source=employees;Persist Security Info=False"Con.CursorLocation = adUseClientRs.Open "select * from employees", Con, adOpenKeyset, adLockOptimisticEnd SubSub txtClear()Text1.Text = ""Text2.Text = ""Text3.Text = ""Text4.Text = ""End SubSub DisplayText()Text1.Text = Rs(0).ValueText2.Text = Rs(1).ValueText3.Text = Rs(2).ValueText4.Text = Rs(3).ValueEnd Sub
65
Result:
66
EX NO: 17 Library Information System
Aim:
Dim rs As New ADODB.RecordsetDim Con As New ADODB.ConnectionPrivate Sub Command1_Click()rs.AddNewtxtClearText1.SetFocusEnd SubPrivate Sub Command2_Click()On Error Resume NextIf rs.EOF ThenMsgBox "No Records"Elsers.Deleters.MoveNextDisplayTextMsgBox "Record Deleted"End IfEnd SubPrivate Sub Command3_Click()rs(0).Value = Val(Text1.Text)rs(1).Value = Text2.Text
67
rs(2).Value = Text3.Textrs(3).Value = Text4.Textrs(4).Value = Text5.Textrs.UpdateMsgBox "Record Updated"End SubPrivate Sub Command4_Click()rs.MoveFirstDisplayTextEnd SubPrivate Sub Command5_Click()rs.MoveNextIf Not rs.EOF ThenDisplayTextElseMsgBox "End of the Record"End IfEnd SubPrivate Sub Form_Load()Con.Open "Provider = MSDAORA.1;User Id=scott;Password=tiger;DataSource=lib;Persist Security Info=False"Con.CursorLocation = adUseClientrs.Open "select * from lib", Con, adOpenKeyset, adLockPessimisticEnd SubSub txtClear()Text1.Text = ""Text2.Text = ""Text3.Text = ""Text4.Text = ""Text5.Text = ""End SubSub DisplayText()Text1.Text = rs(0).ValueText2.Text = rs(1).ValueText3.Text = rs(2).ValueText4.Text = rs(3).ValueText5.Text = rs(4).ValueEnd Sub
Result:
68
top related