DataBaseManagementSystems LAB MANUAL
How to Write and execute sql, pl/sql commands/programs:
1). Open your oracle application by the following navigation
Start->all programs->Oracle Database 10g Express Edition
->Run SQL Command Line
2). You will be asked for user name, password.
You have to enter user name, pass word.
3). Upon successful login you will get SQL prompt (SQL>).
In two ways you can write your programs:
a) directly at SQL prompt (or)
b) in sql editor.
If you type your programs at sql prompt then screen will look like follow:
SQL> SELECT ename,empno,
2 sal from
3 emp;
where 2 and 3 are the line numbers and rest is the command
/program……
to execute above program/command you have to press ‘/’ then enter.
Here editing the program is somewhat difficult; if you want to edit theprevious command then you have to open sql editor (by default it displays the sql buffer contents). By giving ‘ed’ at sql prompt.(this is what I mentioned as a second method to type/enter the program).
in the sql editor you can do all the formatting/editing/file operations directly by selecting menu options provided by it.
To execute the program which saved; do the following
SQL> @ programname.sql (or)
SQL> Run programname.sql
Then press ‘\’ key and enter.
To save the day`s session ;do the following
SQL>commit;
This how we can write, edit and execute the sql command and
programs.
Always you have to save your programs in your own logins.
Background Theory
Oracle workgroup or server is the largest selling RDBMS product.it is
estimated that the combined sales of both these oracle database product
account for aroud 80% of the RDBMSsystems sold worldwide.
These products are constantly undergoing change and evolving. The
natural language of this RDBMS product is ANSI SQL,PL/SQL a superset
of ANSI SQL.oracle 8i and 9i also under stand SQLJ.
Oracle corp has also incorporated a full-fledged java virtual machine
into its database engine.since both executable share the same memory
space the JVM can communicate With the database engine with ease
and has direct access to oracle tables and their data.
SQL is structure query language.SQL contains different data types those
are
1. char(size)
2. varchar(size)
3. varchar2(size)
4. date
5. number(p,s) //** P-PRECISION S-SCALE **//
6. number(size)
7. raw(size)
8. raw/long raw(size)
Different types of commands in SQL:
A).DDL commands: - To create a database objects
B).DML commands: - To manipulate data of a database objects
C).DQL command: - To retrieve the data from a database.
D).DCL/DTL commands: - To control the data of a database…
DDL commands:
1. The Create Table Command: - it defines each column of the table
uniquely. Each column has minimum of three attributes, a name , data
type and size.
Syntax:
Create table <table name> (<col1> <datatype>(<size>),<col2>
<datatype<size>));
Ex:
create table emp(empno number(4) primary key, ename char(10));
2. Modifying the structure of tables.
a)add new columns
Syntax:
Alter table <tablename> add(<new col<datatype(size),<new
col>datatype(size));
Ex:
alter table emp add(sal number(7,2));
3. Dropping a column from a table.
Syntax:
Alter table <tablename> drop column <col>;
Ex:
alter table emp drop column sal;
4. Modifying existing columns.
Syntax:
Alter table <tablename> modify(<col<newdatatype>(<newsize>));
Ex:
alter table emp modify(ename varchar2(15));
5. Renaming the tables
Syntax:
Rename <oldtable> to <new table>;
Ex:
rename emp to emp1;
6. truncating the tables.
Syntax:
Truncate table <tablename>;
Ex:
trunc table emp1;
7. Destroying tables.
Syntax:
Drop table <tablename>;
Ex:
drop table emp;
DML commands:
8. Inserting Data into Tables: - once a table is created the most
natural thing to do is load this table with data to be manipulated later.
Syntax 1:
insert into <tablename> (<col1>,<col2>…..<col n>) values(<val 1>,
<val 2>…….<val n>);
Syntax 2:
insert into <tablename> values(<col1>,<col2>……,<col n>);
Syntax 3:
insert into <tablename> values(<val 1>,<val 2>…….,<val n>);
Ex 1:
Insert into skc (sname,rollno,class,dob,fee_paid)
values(‘sri’,’104B’,’cse’,’27-feb-05’,10000.00);
Ex 2:
insert into skc values(&sname,&roll no,&class);
enter sname:’sri’
enter roll no:’104B’
enter class:’cse’
1 row created.
Ex 3:
insert into skc values(‘sri’,’104B’,cse’,’27-feb-05’,10000.00);
9. Delete operations.
a) remove all rows
Syntax:
delete from <tablename>;
b) removal of a specified row/s
Syntax:
delete from <tablename> where <condition>;
10. Updating the contents of a table.
a) updating all rows
Syntax:
Update <tablename> set <col>=<exp>,<col>=<exp>;
b) updating seleted records.
Syntax:
Update <tablename> set <col>=<exp>,<col>=<exp>
where <condition>;
11. Types of data constrains.
a) not null constraint at column level.
Syntax:
<col<datatype>(size)not null
b) unique constraint
Syntax:
Unique constraint at column level.
<col<datatype>(size)unique;
c) unique constraint at table level:
Syntax:
Create table
tablename(col=format,col=format,unique(<col1>,<col2>);
d) primary key constraint at column level
Syntax:
<col<datatype>(size)primary key;
e) primary key constraint at table level.
Syntax:
Create table tablename(col=format,col=format
primary key(col1>,<col2>);
f) foreign key constraint at column level.
Syntax:
<col<datatype>(size>) references <tablename>[<col>];
g) foreign key constraint at table level
Syntax:
foreign key(<col>[,<col>]) references
<tablename>[(<col>,<col>)
h) check constraint
check constraint constraint at column level.
Syntax: <col<datatype>(size) check(<logical expression>)
i) check constraint constraint at table level.
Syntax: check(<logical expression>)
DQL Commands:
12. Viewing data in the tables: - once data has been inserted into a
table, the next most logical operation would be to view what has been
inserted.
a) all rows and all columns
Syntax:
Select <col> to <col n> from tablename;
Select * from tablename;
13. Filtering table data: - while viewing data from a table, it is rare
that all the data from table will be required each time. Hence, sql must
give us a method of filtering out data that is not required data.
a) Selected columns and all rows:
Syntax:
select <col1>,<col2> from <tablename>;
b) selected rows and all columns:
Syntax:
select * from <tablename> where <condition>;
c) selected columns and selected rows
Syntax:
select <col1>,<col2> from <tablename> where<condition>;
14. Sorting data in a table.
Syntax:
Select * from <tablename> order by <col1>,<col2> <[sortorder]>;
DCL commands:
Oracle provides extensive feature in order to safeguard information
stored in its tables from unauthoraised viewing and damage.The rights
that allow the user of some or all oracle resources on the server are
called privileges.
a) Grant privileges using the GRANT statement
The grant statement provides various types of access to database
objects such as tables,views and sequences and so on.
Syntax:
GRANT <object privileges>
ON <objectname>
TO<username>
[WITH GRANT OPTION];
b) Reoke permissions using the REVOKE statement:
The REVOKE statement is used to deny the Grant given on an object.
Syntax:
REVOKE<object privilege>
ON
FROM<user name>;
CREATING,ALTERING AND DROPPING TABLES AND INSERTING ROWS INTO A TABLE (USE CONSTRAINTS WHILE CREATING TABLES) EXAMPLES USING
SELECT COMMAND .
EXAMPLE 1:
CREATING A STUDENT RELATION TABLE WITH ALL DATATYPES:
SQL> create table student252(
sid number(5),
sname varchar(20),
sbranch char(5),
dob date,
spercent number(3,2));
Table created.
RELATIONAL SCHEMA FOR STUDENT RELATION :
SQL> desc student252;
Name Null? Type
------
SID NUMBER(5)
SNAME VARCHAR2(20)
SBRANCH CHAR(5)
DOB DATE
SPERCENT NUMBER(5,2)
INSERT THE RECORDS INTO STUDENT RELATION:
METHOD 1:
SQL>Insert into
Student252(sid,sname,sbranch,dob,spercent) values(104,‘sri’,,’cse’,’27-
feb-05’,70);
1 row created.
METHOD 2:
SQL>Insert into
Student252 values(104,‘sri’,,’cse’,’27-feb-05’,70);
1 row created.
METHOD 3:
SQL>Insert into
Student252(sid,sname,sbranch,dob,spercent)
values(&sid, &sname,&sbranch,&dob,&spercent);
1 row created.
METHOD 4:
SQL>Insert into
Student252(sid,sname,sbranch,dob,spercent)
values(&sid, ‘&sname’,’&sbranch’,’&dob’,&spercent);
1 row created.
QUERY THE TABLE VALUES:
ALL ROWS AND ALL COLUMNS:
SQL> select * from student252;
SID SNAME SBRANCH DOB SPERCENT
------
130 ravi it 30-1-95 60
131 teja cse 21-07-87 55
129 kiran mech 12-05-92 60
104 sri cse 30-07-90 70
133 sajith eee 12-06-89 55
137 ram ece 07-07-85 40
2 (cont…1)
1) Creation, altering and dropping tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.
MODIFYING THE STRUCTURE OF TABLE
ADDING A NEW COLUMN
SQL> ALTER TABLE Emp252
ADD (age number(3),
phno number(10));
Table altered.
MODIFYING EXISTING COLUMN
SQL> ALTER TABLE Emp252
MODIFY (phno varchar(20));
Table altered.
DROPING A COLUMN
SQL> ALTER TABLE Emp252
DROP COLUMN phno;
Table altered.
QUERY FOR THE TABLE VALUES
SQL> SELECT *
FROM Emp252;
ENO ENAME ESAL DEPTNO AGE
------
30 ravi 51000 3
31 teja 31000 2
29 kiran 31200 1
45 allen 41000 3
33 sajith 51000 4
46 geetha 11000 4
90 veena 16000 3
85 pragna 61000 1
84 harsha 91000 3
40 sanjeev 1500 13
10 rows selected.
UPDATING ENTIRE COLUMN
SQL> UPDATEEmp252
SET age=18;
10 rows updated.
QUERY THE TABLE VALUES
SQL> SELECT *
FROMEmp252;
ENO ENAME ESAL DEPTNO AGE
------
30 ravi 51000 3 18
31 teja 31000 2 18
29 kiran 31200 1 18
45 allen 41000 3 18
33 sajith 51000 4 18
46 geetha 11000 4 18
90 veena 16000 3 18
85 pragna 61000 1 18
84 harsha 91000 3 18
40 sanjeev 1500 13 18
10 rows selected.
RENAMING THE TABLE:
SQL> RENAMEEmp252
TOEmp1252;
Table renamed.
SELECTING THE TABLE VALUES
SQL> SELECT *
FROMEmp1252;
Example 3
CREATING A DEPARTMENT RELATION TABLE
CREATING A DEPARTMENT TABLE
SQL> CREATE TABLEDept252(
dname VARCHAR(10),
dno CHAR(5),
dloc VARCHAR(25));
Table created.
DESCRIBE A STUDENT TABLE
SQL> desc Dept252;
Name Null? Type
------
DNAME VARCHAR2(10)
DNO CHAR(5)
DLOC VARCHAR2(25)
DROPING THE TABLE
SQL> DROP TABLE Dept252;
Table dropped.
3 (cont…1)
1) Creation, altering and dropping tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.
CREATING A TABLE WITH KEY CONSTRAINTS
Example 1
CREATING A TABLE WITH ‘UNIQUE ‘, ‘NOT NULL’, ‘CHECK’ AND ‘DEFAULT’ CONSTRAINT:
SQL> CREATE TABLE emp252
(eid NUMBER(5) UNIQUE,
ename VARCHAR(10) DEFAULT(‘UNKNOWN’),
age NUMBER(3) NOT NULL,
esal NUMBER(7) CHECK(esal > 1000));
Table created.
INSERTING RECORDS INTO TABLE:
SQL> INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal);
Enter value for eid: 1
Enter value for ename: 'ravi'
Enter value for age: 18
Enter value for esal: 10000
old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new 1: INSERT INTO emp252 VALUES (1, 'ravi', 18, 10000)
1 row created.
SQL> /
Enter value for eid: 2
Enter value for ename: 'teja'
Enter value for age: 18
Enter value for esal: 20000
old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new 1: INSERT INTO emp252 VALUES (2, 'teja', 18, 20000)
1 row created.
SQL> /
Enter value for eid: 3
Enter value for ename: 'kiran'
Enter value for age: 19
Enter value for esal: 25000
old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new 1: INSERT INTO emp252 VALUES (3, 'kiran', 19, 25000)
1 row created.
SQL> /
Enter value for eid: 4
Enter value for ename: 'srinivas'
Enter value for age: 19
Enter value for esal: 30000
old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new 1: INSERT INTO emp252 VALUES (4, 'srinivas', 19, 30000)
1 row created.
SQL> /
Enter value for eid: 1
Enter value for ename: 'alan'
Enter value for age: 19
Enter value for esal: 29000
old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new 1: INSERT INTO emp252 VALUES (1, 'alan', 19, 29000)
INSERT INTO emp252 VALUES (1, 'alan', 19, 29000)
[SHOWING AN ERROR WHILE VIOLATING UNIQUE KEY CONSTRAINT]
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C003875) violated
SQL> /
Enter value for eid: 7
Enter value for ename: 'dravid'
Enter value for age: null
Enter value for esal: 100000
old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new 1: INSERT INTO emp252 VALUES (7, 'dravid', null, 100000)
INSERT INTO emp252 VALUES (7, 'dravid', null, 100000)
[SHOWING AN ERROR AS NOT NULL KEY CONSTRAINT IS VIOLATED] *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMP230"."AGE")
SQL> /
Enter value for eid: 8
Enter value for ename: 'sachin'
Enter value for age: 35
Enter value for esal: 100
old 1: INSERT INTO emp252 VALUES (&eid, &ename, &age, &esal)
new 1: INSERT INTO emp252 VALUES (8, 'sachin', 35, 100)
INSERT INTO emp252 VALUES (8, 'sachin', 35, 100)
*
[NOT ALLOWING AS IT VOILATES CHECK CONSTRAINT FOR esal > 1000 VALUE]
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C003874) violated
Example 2
CREATING A TABLE WITH ‘PRIMARY KEY’ CONSTRAINT:
SQL> CREATE TABLE mdept252
(dno NUMBER(5),
dname CHAR(10),
dloc VARCHAR(10),
PRIMARY KEY (dno));
Table created.
SQL> desc mdept252;
Name Null? Type
------
DNO NOT NULL NUMBER(5)
DNAME CHAR(10)
DLOC VARCHAR2(10)
INSERTING RECORDS INTO MASTER DEPARTMENT TABLE:
SQL> INSERT INTO mdept252 VALUES (&dno, &dname, &dloc);
Enter value for dno: 1
Enter value for dname: 'ravi'
Enter value for dloc: 'hyd'
old 1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
new 1: INSERT INTO mdept252 VALUES (1, 'ravi', 'hyd')
1 row created.
SQL> /
Enter value for dno: 1
Enter value for dname: 'teja'
Enter value for dloc: 'sec'
old 1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
new 1: INSERT INTO mdept252 VALUES (1, 'teja', 'sec')
INSERT INTO mdept252 VALUES (1, 'teja', 'sec')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C003876) violated
SQL> /
Enter value for dno: null
Enter value for dname: 'sajithulhuq'
Enter value for dloc: 'kmm'
old 1: INSERT INTO mdept252 VALUES (&dno, &dname, &dloc)
new 1: INSERT INTO mdept252 VALUES (null, 'sajithulhuq', 'kmm')
INSERT INTO mdept252 VALUES (null, 'sajithulhuq', 'kmm')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."MDEPT230"."DNO")
ADDING A PRIMARY KEY TO AN EXISTING TABLE:
SQL> ALTER TABLE student252 ADD PRIMARY KEY (sid);
Table altered.
SQL> ALTER TABLE emp252 ADD PRIMARY KEY (eid);
ALTER TABLE emp252 ADD PRIMARY KEY (eid)
*
[ GIVING AN ERROR AS ONE TABLE CAN HAVE A SINGLE PRIMARY KEY AT COLUMN LAVEL]
ERROR at line 1:
ORA-02261: such unique or primary key already exists in the table
Example 3
CREATING A TABLE WITH ‘FORIEGN KEY’ CONSTRAINT:
SQL> CREATE TABLE detailemp252
(eid NUMBER(5) REFERENCES mdept230 (dno),
ename VARCHAR(10),
esal NUMBER(7));
Table created.
INSERING RECORDS INTO DETAIL EMPLOYEE TABLE:
SQL> INSERT INTO detailemp252 VALUES (2, 'ravi', 50000);
INSERT INTO detailemp252 VALUES (2, 'ravi', 50000)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.SYS_C003877) violated - parent key not
found
SQL> INSERT INTO detailemp252 VALUES (1, 'teja', 60000);
1 row created.
SQL> DELETE FROM mdept252 where dno=1;
DELETE FROM mdept252 where dno=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.SYS_C003877) violated - child record
found
SQL> SELECT * FROM detailemp252;
EID ENAME ESAL
------
1 teja 60000
SQL> SELECT * FROM mdept252;
DNO DNAME DLOC
------
1 ravi hyd
Exercise
CREATING A CUSTOMER TABLE USING CONSTRAINTS :
SQL> CREATE TABLE cust252
(cnum NUMBER(5),
cname VARCHAR(10),
state VARCHAR(10) DEFAULT ('ap'),
phno NUMBER(5),
CONSTRAINT cnum_pkkey PRIMARY KEY (cnum));
Table created.
SQL> INSERT INTO cust252 VALUES (&cnum, &cname, &state, &phno);
Enter value for cnum: 1
Enter value for cname: 'ravi'
Enter value for state: 'bihar'
Enter value for phno: 001
old 2: (&cnum, &cname, &state, &phno)
new 2: (1, 'ravi', 'bihar', 001)
1 row created.
SQL> /
Enter value for cnum: 2
Enter value for cname: 'teja'
Enter value for state: 'up'
Enter value for phno: 007
old 2: (&cnum, &cname, &state, &phno)
new 2: (2, 'teja', 'up', 007)
1 row created.
SQL> /
Enter value for cnum: 2
Enter value for cname: 'yama'
Enter value for state: 'ap'
Enter value for phno: 006
old 2: (&cnum, &cname, &state, &phno)
new 2: (2, 'yama', 'ap', 006)
INSERT INTO cust252 VALUES
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.CNUM_PKKEY) violated
SQL> /
Enter value for cnum: 4
Enter value for cname: 'huu'
Enter value for state: 'ap'
Enter value for phno: 101
old 2: (&cnum, &cname, &state, &phno)
new 2: (4, 'huu', 'ap', 101)
1 row created.
SQL> SELECT * FROM cust252;
CNUM CNAME STATE PHNO
------
1 ravi bihar 1
2 teja up 7
4 huu ap 101
CREATING AN ITEM TABLE USING CONSTRAINTS:
SQL> CREATE TABLE itm252
(ino NUMBER(3),
iname VARCHAR(10),
iprice NUMBER(4,3),
qtyonhand VARCHAR(5),
CONSTRAINT itm252_ino_pkkey PRIMARY KEY (ino),
CONSTRAINT itm230_qtyoh_chk CHECK (qtyonhand>1));
Table created.
SQL> INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand);
Enter value for ino: 1
Enter value for iname: 'rubber'
Enter value for iprice: 3.50
Enter value for qtyonhand: 3
old 1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand)
new 1: INSERT INTO itm252 VALUES (1, 'rubber', 3.50, 3)
1 row created.
SQL> /
Enter value for ino: 1
Enter value for iname: 'pencil'
Enter value for iprice: 1.00
Enter value for qtyonhand: 3
old 1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand)
new 1: INSERT INTO itm252 VALUES (1, 'pencil', 1.00, 3)
INSERT INTO itm252 VALUES (1, 'pencil', 1.00, 3)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.ITM230_INO_PKKEY) violated
SQL> /
Enter value for ino: 2
Enter value for iname: 'powder'
Enter value for iprice: 3.00
Enter value for qtyonhand: 0
old 1: INSERT INTO itm252 VALUES (&ino, &iname, &iprice, &qtyonhand)
new 1: INSERT INTO itm252 VALUES (2, 'powder', 3.00, 0)
INSERT INTO itm252 VALUES (2, 'powder', 3.00, 0)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.ITM230_QTYOH_CHK) violated
1* CREATE TABLE invoice252(ivnno NUMBER(5), itemno NUMBER(5), qty NOT NULL, CONSTRAINT invoice252_ivnno_pkkey PRIMARY KEY(ivnno), CONSTRAINT FOREIGN KEY(itemno) REFERENCES cust252)
SQL> desc cust252;
Name Null? Type
------
CNUM NOT NULL NUMBER(5)
CNAME VARCHAR2(10)
STATE VARCHAR2(10)
PHNO NUMBER(5)
CREATING A INVOICE TABLE USING CONSTRAINTS:
SQL> CREATE TABLE invoice252
(ivnno NUMBER(5),
itemno NUMBER(5),
qty NUMBER(5) NOT NULL,
CONSTRAINT invoice252_ivnno_pkkey PRIMARY KEY (ivnno),
CONSTRAINT fk_inv252 FOREIGN KEY (itemno) REFERENCES cust252 (cnum))
Table created.
SQL> CREATE TABLE invitm252
(invno NUMBER(5),
itmno NUMBER(5),
qty NUMBER(5) NOT NULL,