dbms lab manual for iv sem11
TRANSCRIPT
-
8/7/2019 Dbms Lab Manual for IV Sem11
1/61
-
8/7/2019 Dbms Lab Manual for IV Sem11
2/61
6. Add primary key constraint for the field ENAME in the table new_Emp.
7. Drop primary key constraint of the field ENAME in the table new_Emp.
8. Rename the table name new_Emp into new_Emp2.
9. Drop the table new_Emp1.
SQL QUERIES: TABLE CREATION
Syntax:
CREATE TABLE tablename (column_name data_ type constraints, );
SQL> CREATE TABLE EmpDetails ( EmpNo Number(5) CONSTRAINT PKey PRIMARY
KEY, EName VarChar(15), Job Char(10) CONSTRAINT Unik1 UNIQUE, Mgr Number(5)
CONSTRAINT FKey1 REFERENCES EMP (EmpNo), Hiredate Date, DeptNo Number(3)CONSTRAINT FKey2 REFERENCES DEPT(DeptNo));
SQL> descEmpDetails;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
HIREDATE DATE
DEPTNO NUMBER (3)
CREATE TABLE FROM AN EXISTING TABLE
Page 2 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
3/61
Syntax:
Create table newtablename as select * from oldtablename;
SQL> create table new_Emp as select * from EmpDetails;
Table created.SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
HIREDATE DATE
DEPTNO NUMBER (3)
SQL> create table new_Emp1 as select empno, ename, job, deptno from EmpDetails;
Table created.
SQL> desc new_Emp1;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
DEPTNO NUMBER (3)
ALTER TABLE
Page 3 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
4/61
ALTER COLUMNS
Syntax:
Alter table tablename Add / Modify / Drop (column_name data_ type constraints,
);
ADD COLUMN(S)
Before adding columns in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
HIREDATE DATE
DEPTNO NUMBER (3)
SQL> alter table new_Emp add (Address varchar2 (20), Contactno number (11));
Table altered.
After adding address and contactno columns in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
Page 4 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
5/61
MGR NUMBER (5)
HIREDATE DATE
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (20)
CONTACTNO NUMBER (11)
MODIFY COLUMN(S)
Before modifying columns in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
HIREDATE DATE
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (20)
CONTACTNO NUMBER (11)
SQL> alter table new_Emp modify(address varchar2(50));
Table altered.
After modifying address columns in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
Page 5 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
6/61
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
HIREDATE DATE
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (50)
CONTACTNO NUMBER (11)
DROP COLUMN(S)
Before droping columns in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
HIREDATE DATE
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (50)
CONTACTNO NUMBER (11)
SQL> alter table new_Emp drop(hiredate);
Table altered.
After droping hiredate columns in new_Emp table
SQL> desc new_Emp;
Name Null? Type
Page 6 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
7/61
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (50)
CONTACTNO NUMBER (11)
ALTER CONSTRAINT(S)
Syntax:
Alter table tablename add / Modify / Drop (constraint constraintname [constrainttype]
[(column name)]);
ADD CONSTRAINT(S)
Before adding constraint in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (50)
CONTACTNO NUMBER (11)
Page 7 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
8/61
SQL> alter table new_Emp add(constraint p_key primary key (ename));
Table altered.
After adding constraint in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME NOT NULL VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (50)
CONTACTNO NUMBER (11)
DROP CONSTRAINTS
Before dropping constraint in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME NOT NULL VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
DEPTNO NUMBER (3)
Page 8 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
9/61
ADDRESS VARCHAR2 (50)
CONTACTNO NUMBER (11)
SQL> alter table new_Emp drop constraint p_key;
Table altered.
After adding constraint in new_Emp table
SQL> desc new_Emp;
Name Null? Type
-------------------- -------------- ----------------------
EMPNO NOT NULL NUMBER (5)
ENAME VARCHAR2 (15)
JOB CHAR (10)
MGR NUMBER (5)
DEPTNO NUMBER (3)
ADDRESS VARCHAR2 (50)
CONTACTNO NUMBER (11)
RENAME TABLE
SYNTAX:
Alter table oldtablename rename to newtablename;
Page 9 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
10/61
DROP TABLE
Syntax:
Drop table tablename;
SQL> drop table new_Emp1;Table dropped.
SQL> desc new_Emp1;
ERROR:
ORA-04043: object new_Emp1 does not exist
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------------- ----------------
MARKLIST TABLE
NEW_STAFF1 TABLE
PRIYA TABLE
S TABLE
EMPDETAILS TABLE
Page 10 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
11/61
Result:
Thus the basic Data Definition Commands are executed and verified successfully.
Page 11 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
12/61
EX.NO:2
DATE:
DML COMMANDS
Aim:
To learn about basic Data Manipulation Commands (Insert, Update, Delete and Select).
Create the following table, insert some rows of information and execute the following queries.
Table: EmpDetails
COLUMN NAME DATA TYPE(SIZE)
EMPNO Number (5)
ENAME Varchar2(15)
JOB Char(10)
MGR Number (5)
DEPTNO Number(3)
ADDRESS Varchar2(50)
CONTACTNO Number(10)
Insert the following records in the EmpDetails:
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
Page 12 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
13/61
1001 Saran PA 1000 101 SALEM 9955248792
10001 ram manager 99 101 chennai 9988765643
1002 viji PAT 1000 101 bangalore 9977678768
QUESTIONS:
1. Update employee number as 1000 of the employee 10001 in the table EmpDetails.
2. Update job as MANAGER of the employee 1000 in the table EmpDetails.
3. Delete employee details who are all resides at Bangalore in the table EmpDetails.
4. Select employee details such as empno, ename, deptno who are all resides at Salem in thetable EmpDetails.
5. Select empno, ename of the employee whose employee number between 1000 and 1002 in
the table EmpDetails.
6. Select empno, ename of the employee whose address is Chennai or Bangalore in the tableEmpDetails.
7. Select empno, ename of the employee whose employee number is greater then 1002 orderby employee name in descending in the table EmpDetails.
8. Select all the details of the employee whose employee number is greater then 1002 orderby address is ascending and employee name in descending in the table EmpDetails.
9. Select all the details of the employee whose employee name is starts withs in the tableEmpDetails.
10. Select all the details of the employee whose employee name has a in the table
EmpDetails.
11. Count the no. of employees in the EmpDetails.
12. Count the no. of distinct department in the EmpDetails.
13. Count the no. of employees based on address in the EmpDetails.
Page 13 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
14/61
INSERT COMMAND
Syntax:
SQL> INSERT INTO tablename (columnname1, columnname2 ) VALUES (value1, value2
);
SQL> INSERT INTO tablename values(&fieldname1, &fieldname2 .);
SQL> /
Enter value for column1: value1
Enter value for column2: value2
..
SQL> insert into empdetails (empno, ename, job, mgr, deptno, address, contactno) values(1001,
'Saran
', 'PA', 1000, 101, 'SALEM', 9955248792);
1 row created.
SQL> insert into empdetails values(&empno, '&ename', '&job', &mgr, &deptno, '&address',&contactno);
Enter value for empno: 10001
Enter value for ename: ram
Enter value for job: manager
Enter value for mgr: 99
Enter value for deptno: 101
Enter value for address: chennai
Enter value for contactno: 9988765643
Page 14 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
15/61
old 1: insert into empdetails values(&empno, '&ename', '&job', &mgr, &deptno, '&address',
&contactno)
new 1: insert into empdetails values(10001, 'ram', 'manager', 99, 101, 'chennai', 9988765643)
1 row created.
SQL> /
Enter value for empno: 1002
Enter value for ename: viji
Enter value for job: PAT
Enter value for mgr: 1000
Enter value for deptno: 101
Enter value for address: bangalore
Enter value for contactno: 9977678768
old 1: insert into empdetails values(&empno, '&ename', '&job', &mgr, &deptno, '&address',
&contactno)
new 1: insert into empdetails values(1002, 'viji', 'PAT', 1000, 101, 'bangalore', 9977678768)
1 row created.
SQL> select * from empdetails;
Page 15 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
16/61
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
10001 ram manager 99 101 chennai 9988765643
1002 viji PAT 1000 101 bangalore 9977678768
SQL>commit;
Commit complete.
UPDATE COMMAND
Syntax:
SQL> update table_name set col_name1=value where predicates;
SQL> update empdetails set empno=1000 where empno =10001;
1 row updated.
SQL> select * from empdetails;
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
1000 ram manager 99 101 chennai 9988765643
Page 16 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
17/61
1002 viji PAT 1000 101 bangalore 9977678768
SQL> update e empdetails set job=&job where empno=&empno;
Enter value for job: MANAGER
Enter value for empno: 1000
old 1: update emp_demo set job=&job where empno=1000
new 1: update emp_demo set job=MANAGER where empno =102
1 row updated.
SQL> select * from empdetails;
EMPNO ENAME JOB MGR DEPTNO ADDRESSCONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
1000 ram MANAGER 99 101 chennai 9988765643
1002 viji PAT 1000 101 bangalore 9977678768
DELETE COMMAND
Syntax:
DELETE FROM table_name where predicates;
SQL> select * from empdetails;
Page 17 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
18/61
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
1000 ram MANAGER 99 101 chennai 9988765643
1002 viji PAT 1000 101 bangalore 9977678768
SQL> delete from empdetails where address=bangalore;
1 row deleted.
SQL> select * from empdetails;
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
1000 ram MANAGER 99 101 chennai 9988765643
SELECT COMMAND
Syntax:
SELECT columns FROM tables WHERE predicates;
Page 18 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
19/61
-
8/7/2019 Dbms Lab Manual for IV Sem11
20/61
EMPNO ENAME
---------- ----------
1000 ram
1002 Arun
1003 Sudha
ORDERBY CLAUSE
Syntax:
SELECT columns FROM tables WHERE predicates order by columns asc or desc;
SQL> select empno, ename from empdetails where empno>=1002 order by ename desc;
EMPNO ENAME
---------- ----------
1001 Saran
1000 ram
1002 Arun
SQL>select * from empdetails where empno>=1003 order by address desc, ename asc;
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
Page 20 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
21/61
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
1000 ram MANAGER 1000 101 Chennai 9988765643
1003 Sudha Admin 1000 101 Chennai 9790865432
1002 Arun PAT 1000 101 Bangolore 9870984353
LIKE OPERATOR
Syntax:
SELECT columns FROM tables WHERE predicates like pattern;
SQL> select * from empdetails where ename like 's%';
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
1003 Sudha Admin 1000 101 Chennai 9790865432
SQL> select * from empdetails where ename like '%a_';
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
Page 21 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
22/61
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 101 SALEM 9955248792
1000 ram MANAGER 1000 101 Chennai 9988765643
AGGREGATE FUNCTION
COUNT
Syntax:
SQL> select count(column_name) from table_name;
SQL> select count(ename) from empdetails;
COUNT(ENAME)
------------------------
4
SQL>select count(distinct deptno) from empdetails;
COUNT(DISTINCTDEPTNO)
--------------------------------
1
Syntax:
SQL> select column_name, count(column_name) from table_name group by column_name;
SQL> select address,count(ename) from empdetailsgroup by address;
ADDRESS COUNT(ENAME)
------------- ------------------------
Page 22 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
23/61
SALEM 1
Chennai 2
Bangalore 1
Result:
Thus the basic DML and DCL are executed and verified successfully.
Ex.No: 3
DATE:
NESTED QUERIES AND JOIN QUERIESPage 23 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
24/61
Aim:
To learn about nested queries and join queries. Create the following tables and execute the
following queries.
Table: DeptDetails
COLUMN NAME
DATA TYPE(SIZE)
DEPTNO Number(3)
DNAME Varchar2(20)
DLOCATION Varchar2(30)
MGR Number (5)
DEPTNO DNAME DLOCATION MGR
----------- ------------- ----------------- ---------
100 Healthcare Chennai 99
101 Manlog Delhi 1000
102 IME Bangalore 1001
Table: EmpDetails
COLUMN NAME DATA TYPE(SIZE)
EMPNO Number (5)
Page 24 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
25/61
ENAME Varchar2(15)
JOB Char(10)
MGR Number (5)
DEPTNO Number(3)
ADDRESS Varchar2(50)CONTACTNO Number(10)
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 100 SALEM 9955248792
1000 ram MANAGER 1000 100 Chennai 9988765643
1002 Arun PAT 1000 100 Bangolore 9870984353
1003 Sudha Admin 1000 100 Chennai 9790865432
QUESTIONS:
1. Select Deptno and Dname of managers who are residing in the same city of their
department location.
2. Select all the employee details who are residing in the same city of their department
location.
3. Select all the employee details whose job is not PA and employee name is not end with
n.
4. Select empno, ename, deptno,dlocation of the all employees.
NESTED QUERIES
SQL> Select * from deptdetails;
DEPTNO DNAME DLOCATION MGR
Page 25 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
26/61
----------- ------------- ----------------- ---------
100 Healthcare Chennai 99
101 Manlog Delhi 1000
102 IME Bangalore 1001
SQL> select * from empdetails;
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 100 SALEM 9955248792
1000 ram MANAGER 1000 100 Chennai 9988765643
1002 Arun PAT 1000 100 Bangolore 9870984353
1003 Sudha Admin 1000 100 Chennai 9790865432
SQL> select deptno, dname from deptdetails where dlocation=(select address from empdetails
where job
=' MANAGER');
DEPTNO DNAME
---------- --------------------
100 Healthcare
SQL>select * from empdetails where address=(select dlocation from deptdetails where
deptdetails.deptno=empdetails.deptno);
Page 26 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
27/61
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1000 ram MANAGER 1000 100 Chennai 9988765643
1003 Sudha Admin 1000 100 Chennai 9790865432
SQL>select * from empdetails where job'PA' and ename in(select ename from empdetails
where ename not like'%n');
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1000 ram MANAGER 1000 100 Chennai 9988765643
1003 Sudha Admin 1000 100 Chennai 9790865432
SQL> select e.empno,e.ename, d.deptno,d.dlocation from empdetails e, deptdetails d where
e.deptno=d.deptno;
EMPNO ENAME DEPTNO DLOCATION
---------- --------------- ------------ --------------------
1001 Saran 100 chennai
Page 27 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
28/61
1002 Arun 100 chennai
1000 RAM 100 chennai
1003 Sudha 100 chennai
Result:
Thus the nested and join queries are executed and verified successfully.
Ex.No:4
DATE:
Page 28 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
29/61
VIEW
Aim:
To learn about view in SQL (create, replace, update, drop, delete). Create the following
tables and execute the following queries.
Table: DeptDetails
COLUMN NAME DATA TYPE(SIZE)
DEPTNO Number (3)
DNAME Varchar2(20)
DLOCATION Varchar2(30)
MGR Number (5)
DEPTNO DNAME DLOCATION MGR
----------- ------------- ----------------- ---------
100 Healthcare Chennai 99
101 Manlog Delhi 1000
102 IME Bangalore 1001
Table: EmpDetails
COLUMN NAME DATA TYPE(SIZE)
Page 29 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
30/61
EMPNO Number (5)
ENAME Varchar2(15)
JOB Char(10)
MGR Number (5)
DEPTNO Number(3)ADDRESS Varchar2(50)
CONTACTNO Number(10)
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 100 SALEM 9955248792
1000 ram MANAGER 1000 100 Chennai 9988765643
1002 Arun PAT 1000 100 Bangolore 9870984353
1003 Sudha Admin 1000 100 Chennai 9790865432
QUESTIONS:
1. Create empview view from EmpDetails table.
2. Create empview1 view with empno, ename, and address fields from EmpDetails table.
3. Create empview2 view with empno, ename, and address fields who are residing in
Chennai by using EmpDetails table.
4. Create empdeptview view with empno, ename, address and deptno fields who are residing
in Chennai by using EmpDetails, DeptDetails tables.
5. Drop the empdeptview. Update employee name of Sudha as Sudhakar in the
empdeptview.Delete the empdeptview.
Page 30 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
31/61
CREATE OR REPLACE VIEW
Syntax:
SQL>CREATE OR REPLACE VIEW view_name AS SELECT columns FROM table WHERE
predicates;
SQL> select * from empdetails;
EMPNO ENAME JOB MGR DEPTNO ADDRESSCONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
1001 Saran PA 1000 100 SALEM 9955248792
1000 ram MANAGER 1000 100 Chennai 9988765643
1002 Arun PAT 1000 100 Bangalore 9870984353
1003 Sudha Admin 1000 100 Chennai 9790865432
SQL>Create or replace view empview as select * from empdetails;
View Created.
SQL>select * from empview;
EMPNO ENAME JOB MGR DEPTNO ADDRESS
CONTACTNO
---------- ---------- ---------- -------- ----------- -------------- -----------------
Page 31 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
32/61
1001 Saran PA 1000 100 SALEM 9955248792
1000 ram MANAGER 1000 100 Chennai 9988765643
1002 Arun PAT 1000 100 Bangalore 9870984353
1003 Sudha Admin 1000 100 Chennai 9790865432
SQL> create or replace view empview1 as select empno, ename, address from empdetails;
View Created.
SQL> select * from empview1;
EMPNO ENAME ADDRESS
---------- ---------- --------------
1001 Saran SALEM
1000 ram Chennai
1002 Arun Bangalore
1003 Sudha Chennai
SQL> create or replace view empview2 as select empno, ename, address from empdetails where
address=Chennai;
View Created.
SQL> select * from empview2;
EMPNO ENAME ADDRESS
---------- ---------- --------------
1000 ram Chennai
Page 32 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
33/61
1003 Sudha Chennai
SQL> create or replace view empdeptview as select e.empno, e.ename,e.address,d.deptno from
empdetails e, deptdetails d where e.deptno=d.deptno and e.address=Chennai;
View Created.
SQL>select * from empdeptview;
EMPNO ENAME ADDRESS DEPTNO
---------- --------------- -------------- ----------
1000 ram Chennai 100
1003 Sudha Chennai 100
UPDATE VIEW
Syntax:
SQL>update viewname set columnname=values where predicates;
SQL>update empdeptview set ename='Sudhakar' where ename='Sudha';
View Updated.
SQL> select * from empdeptview;
EMPNO ENAME ADDRESS DEPTNO
---------- --------------- -------------- ----------
1000 ram Chennai 100
1003 Sudhakar Chennai 100
Page 33 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
34/61
-
8/7/2019 Dbms Lab Manual for IV Sem11
35/61
Result:
Thus the operations on view are executed and verified successfully.
Ex. No: 5
DATE:
IMPLEMENTATION OF PROCEDURE AND FUNCTION
Aim:To write a PL/SQL program to calculate total and average of student by using procedure
and function.
Page 35 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
36/61
PROGRAM
Table Creation
SQL> create table student (regno number(10),name varchar2(20),dbms number(3),os
number(3),maths number(3), total number(3),avg number(5,2));
SQL> desc student;
Name Null? Type
------------------------------ -------- ----------------------------
REGNO NUMBER(10)
NAME VARCHAR2(20)
DBMS NUMBER(3)
OS NUMBER(3)
MATHS NUMBER(3)
TOTAL NUMBER(3)
AVG NUMBER(5,2)
SQL> insert into student(regno,name,dbms,os,maths)values(®no,'&name',&dbms, &os,
&maths);
(Insert the following values)
SQL> select * from student;
REGNO NAME DBMS OS MATHS TOTAL AVG
--------- --------- -------- ------ ---------- ---------- -------
1 raja 77 88 57
2 ramu 88 64 95
3 jeeva 77 88 99
4 jothi 54 68 59
Page 36 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
37/61
5 suresh 99 99 99
PROCEDURE
SQL>create or replace procedure p_total
2 ( dbms in number, os in number, maths in number,3 tot out number) as
4 begin5
6 tot:=dbms+os+maths;
7 end;
8 /
Procedure created.
FUNCTION
SQL> create or replace function f_avg(tot in number ) return number is
2 begin
3 return (tot/3);
4 end;
5 /
Function created.
PL/SQL PROGRAM
SQL> declare
2 cursor c is select * from student;
3 tot number(3);
4 avg1 number(5,2);
5 begin
6 for cur in c loop
7 p_total(cur.dbms,cur.os,cur.maths,tot);8 avg1:=f_avg(tot);
9 update student set total=tot,avg=avg1 where student.regno=cur.regno;
10 end loop;
11 end;
12 /
Page 37 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
38/61
PL/SQL procedure successfully completed.
OUTPUT
SQL> select * from student;
REGNO NAME DBMS OS MATHS TOTAL AVG
--------- --------- -------- ------ ---------- ---------- -------
1 raja 77 88 57 222 74
2 ramu 88 64 94 246 823 jeeva 77 88 99 264 88
4 jothi 54 68 58 180 60
5 suresh 99 99 99 297 99
SQL>
Page 38 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
39/61
Result:
Thus the PL/SQL program to calculate total and average of student by using procedure and
function is created and executed successfully.
Ex. No: 6
DATE:
IMPLEMENTATION OF TRIGGER
Aim:
To write a PL/SQL program to raise trigger before update data on student table.
Page 39 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
40/61
PROGRAM
Table Creation
SQL> create table student (regno number(10),name varchar2(20),dbms number(3),os
number(3),maths number(3), total number(3),avg number(5,2));
SQL> desc student;
Name Null? Type
------------------------------ -------- ----------------------------
REGNO NUMBER(10)
NAME VARCHAR2(20)
DBMS NUMBER(3)
OS NUMBER(3)
MATHS NUMBER(3)
TOTAL NUMBER(3)
AVG NUMBER(5,2)
SQL> insert into student(regno,name,dbms,os,maths)values(®no,'&name',&dbms, &os,
&maths);
(Insert the following values)
SQL> select * from student;
REGNO NAME DBMS OS MATHS
--------- --------- -------- ------ ----------
1 raja 77 88 57
2 ramu 88 64 95
3 jeeva 77 88 99
4 jothi 54 68 59
5 suresh 99 99 99
Page 40 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
41/61
TRIGGER
create or replace trigger stud before update on student
for each row
when (new.dbms100)
begin
raise_application_error(-20000,'Must enter marks between 0 and 100');
end;
/
PL/SQL PROGRAM
declare
dbms number(3);
os number(3);
maths number(3);
regno number(10);
begin
update student set dbms=&dbms,os=&os,maths=&maths where regno=®no;
end;
/
OUTPUT
Enter value for dbms: -5
Page 41 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
42/61
Enter value for os: 56
Enter value for maths: 78
Enter value for regno: 8
old 7: update student set dbms=&dbms,os=&os,maths=&maths where regno=®no;
new 7: update student set dbms=-5,os=56,maths=78 where regno=8;
declare
*
ERROR at line 1:
ORA-20000: Must enter marks between 0 and 100
ORA-06512: at "SURESH.STUD", line 2
ORA-04088: error during execution of trigger 'SURESH.STUD'
ORA-06512: at line 7
Page 42 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
43/61
Result:
Thus the PL/SQL program to raise trigger before update data on student table is created
and executed successfully.
EXNO:
DATE:
MENU DESIGNS
Aim:To perform menu designs using visual c++
Procedure:
Page 43 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
44/61
1. ClickVisual C++ and choose the project tab and select win32 application.
2. Give the project name in selected location (eg c:\....) and click ok.3. Select an empty project and click finish.4. Empty application is created.5. Select Project menu ->Add to project ->new.6. In new dialog box, choose C++ source file and give the file name.7. File view and Class view are created.
8. In File view choose source file and double click on the C++ sourcefile with extension .cpp and enter the code in the correspondingwindow.
9. From Insert menu -> Resource-> menu then click new.10.Menu editor is displayed. Design the menus and submenus in the
menu editor.11.From Insert menu -> Resource-> icon and click new, then design your
own icon12.and click File->save all in your correct project folder (default name of
file is Script1).
13.Select Project->Insert Project into workspace. A dialog boxappears in that choose filetype as all files then select Script1.rc. Nowclick OK/YES for all the popup windows.
14.Select Project->Insert Project into workspace. A dialog boxappears in that choose filetype as all files then select Resource.h.Now click OK/YES for all the popup windows.
15.Select the Script1.rc from script2 files and drag it to place it into thesource files. Repeat the same for Resource.h (drag and place it to theheader files).
16.Delete script2 files and resource1 files.
Finally build and execute the program.
Coding:
#include
#include"resource.h"
const char g_szClassName[]="My Window Class";
LRESULT CALLBACK WndProc(HWND hwnd,UINT msg,WPARAMwParam,LPARAM lParam)
Page 44 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
45/61
{
switch(msg)
{
case WM_COMMAND:
switch(LOWORD(wParam))
{
case ID_FILE_EXIT:
MessageBox(hwnd,"you clicked exit","Woo!",MB_OK);
PostMessage(hwnd,WM_CLOSE,0,0);
break;
case ID_EDIT_GO:
MessageBox(hwnd,"You Clicked Go","Woo!",MB_OK);
break;
}
case WM_CLOSE:
DestroyWindow(hwnd);
break;
case WM_DESTROY:
PostQuitMessage(0);
break;
default:
return DefWindowProc(hwnd,msg,wParam,lParam);
}
return 0;
}
Page 45 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
46/61
int WINAPI WinMain(HINSTANCE hInstance,HINSTANCE hPrevInstance,LPSTRlpCmdLine,int nCmdShow)
{
WNDCLASSEX wc;
HWND hwnd;
MSG msg;
wc.cbSize=sizeof(WNDCLASSEX);
wc.style=0;
wc.lpfnWndProc=WndProc;
wc.cbClsExtra=0;
wc.cbWndExtra=0;
wc.hInstance=hInstance;
wc.hIcon=LoadIcon(GetModuleHandle(NULL),MAKEINTRESOURCE(IDR_MENU1));
wc.hCursor=LoadCursor(NULL,IDC_ARROW);
wc.hbrBackground=(HBRUSH)(COLOR_WINDOW+1);
wc.lpszMenuName=MAKEINTRESOURCE(IDR_MENU1);
wc.lpszClassName=g_szClassName;
wc.hIconSm=(HICON)LoadImage(GetModuleHandle(NULL),MAKEINTRESOURCE(IDI_ICON1),IMAGE_ICON,16,16,0);
if(!RegisterClassEx(&wc))
{MessageBox(NULL,"Window Registration
Failed","error",MB_ICONEXCLAMATION|MB_OK);
return 0;
Page 46 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
47/61
} hwnd=CreateWindowEx(WS_EX_CLIENTEDGE,g_szClassName,"AMenu",WS_OVERLAPPEDWINDOW,CW_USEDEFAULT,CW_USEDEFAULT,240,20,NULL,NULL,hInstance,NULL);
if(hwnd==NULL)
{
MessageBox(NULL,"WindowCreationFailed","error",MB_ICONEXCLAMATION|MB_OK);
return 0;
}
ShowWindow(hwnd,nCmdShow);
UpdateWindow(hwnd);
while(GetMessage(&msg,NULL,0,0))
{
TranslateMessage(&msg);
DispatchMessage(&msg);
}
return msg.wParam;
}
Page 47 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
48/61
Page 48 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
49/61
Page 49 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
50/61
Page 50 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
51/61
Page 51 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
52/61
Result:
Thus the program for menu creation is designed and executed
successfully.
Ex:No:8
Date:
LIBRARY INFORMATION SYSTEM
Page 52 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
53/61
Aim:
To implement the library information system using Visual Basic as front-
end and Oracle as backend.
Coding:
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub cmdadd_Click()
Rs.AddNew
Private Sub cmdadd_Click()
rs.AddNew
Label5.Caption= No of Books
Text1.Text=
Text2.Text=
Text3.Text=
Text4.Text=
Text5.Text=
Text1.SetFocus
End Sub
Private Sub cmdborrow_Click()
rs(0)=Text1.text
rs(1)=Text2.text
rs(2)=Text3.text
Page 53 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
54/61
rs(3)=Text4.text
rs(5)=rs(5)+1
If rs(5)>rs(4) Then
rs.CancelUpdate
MsgBox No books are available for borrowing
Else
rs.Update
MsgBox Book borrowed
Endif
rs.MoveFrist
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmdcancel_Click()
rs.CancelUpdate
Label5.Caption=Available books
rs.MoveFirst
Text1.Text=rs(0)
Page 54 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
55/61
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmddelete_Click()
rs.Delete
MsgBoxrecord deleted
rs.MoveFirst
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmdexit_Click()
rs.Close
Con.Close
Unload Me
End Sub
Page 55 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
56/61
Private Sub cmdfirst_Click()
If Not rs.BOF Then
rs.MoveFirst
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmdlast_Click()
If Not rs.BOF Then
rs.MoveLast
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmdnext_Click()
rs.MoveFirst
If rs.EOF Then
Page 56 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
57/61
rs.MoveLast
MsgBoxLast Record
End if
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmdreturn_Click()
rs(0)=Text1.text
rs(1)=Text2.text
rs(2)=Text3.text
rs(3)=Text4.text
rs(5)=rs(5)-1
if rs(5)
-
8/7/2019 Dbms Lab Manual for IV Sem11
58/61
rs.MoveFirst
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmdprevious_Click()
rs.MovePrevious
If rs.BOF Then
rs.MoveLast
MsgBoxFirst Record
End if
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End Sub
Private Sub cmdsave_Click()
rs(0)=Text1.text
Page 58 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
59/61
rs(1)=Text2.text
rs(2)=Text3.text
rs(3)=Text4.text
rs(4)=val(text5.text).
rs(5)=0
rs.Update
MsgBox Record Update
rs.MoveFirst
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
Label5.Caption=available Books
End Sub
Private Sub Form_Activate()
Con.CursorLocation=adUseClient
Con.OpenProvider=MSDAORA.1;Password=cse;UserID=mukkani;Data
Source=mce
rs.Open Select * from book,con,adOpenDynamic,adLockOptimistic
rs.MoveFirst
If rs.RecordCount=0 Then
Page 59 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
60/61
MsgBoxNo records in table
Else
Text1.Text=rs(0)
Text2.Text=rs(1)
Text3.Text=rs(2)
Text4.Text=rs(3)
Text5.Text=rs(4)-rs(5)
End If
End Sub
Page 60 of61
-
8/7/2019 Dbms Lab Manual for IV Sem11
61/61
Result:
Thus the library information system is implemented using Visual Basic
with Oracle.
s