PROGRAM
N0:01 DRAW ER DIAGRAMS FOR TRAIN SERVICES IN A RAILWAY STATION
PROGRAM
NO.2: DRAW ER DIAGRAM FOR HOSPITAL ADMINISTRATION
PROGRAM
NO.3: CREATION OF COLLEGE DATABASE AND ESTABLISH RELATIONSHIPS BETWEEN TABLES
CREATION OF TABLES
--------------------------------
SQL> Create table Department (Deptname char(15) primary key, Location char(10));
SQL> Create table Courses (CourseId number(3) primary key,CourseName char(10), Duration number(3),Deptname char (15) References Department(Deptname));
SQL> Create table Student (StudentId number(3) primary key,First_Name char(10), last_Name char(10), phone number(10), courseId number(3) references Courses(CourseId));
SQL> Create table Instructor (InstructorID number(3) primary key,First_Name char(10), last_Name char(10), phone number(10), courseId number(3) references Courses(CourseId), Deptname char (15) References Department(Deptname));
Note :
Insert any 5 records in each table.
Select * from Department;
Select * from Courses;
Select * from Student;
Select * from instructor;
PROGRAM NO. 04: WRITE A VIEW TO EXTRACT DETAILS FROM TWO OR MORE TABLES
AIM: To execute and verify the SQL commands for Views.
CREATION OF TABLES
--------------------------------
SQL> CREATE TABLE ST(SID INT,SNAME VARCHAR(10));
TABLE CREATED
SQL> CREATE TABLE MARKS (SID INT, SUB1 INT ,SUB2 INT, SUB3 INT);
TABLE CREATED
TABLE DESCRIPTION
-------------------------------
SQL> DESC ST;
SQL> DESC MARKS;
INSERTION OF DATA INTO TABLES
-----------------------------------------------
SQL> INSERT INTO ST VALUES(1,’SHAIK’);
1 ROW INSERTED
SQL> INSERT INTO ST VALUES(2,’AYAAN’);
1 ROW INSERTED
SQL> INSERT INTO MARKS VALUES(1,50,62,89);
1 ROW INSERTED
SQL> INSERT INTO MARKS VALUES(2,90,99,89);
1 ROW INSERTED
SQL> INSERT INTO MARKS VALUES(3,10,59,69);
1 ROW INSERTED
SYNTAX FOR CREATION OF VIEW
--------------------------------------------------
SQL> CREATE <VIEW> <VIEW NAME> AS SELECT
<COLUMN_NAME_1>, <COLUMN_NAME_2> FROM <TABLE NAME>;
CREATION OF VIEW
------------------------------
SQL> CREATE VIEW ABC AS SELECT ST.SID,ST.SNAME,MARKS.SUB1,MARKS.SUB2,MARKS.SUB3
FROM ST , MARKS WHERE ST.SID=MARKS.SID;
VIEW CREATED
DESCRIPTION OF VIEW
--------------------------------
SQL> DESC EMPVIEW;
NAME NULL? TYPE
----------------------------------------- -------- ----------------------------
SID NUMBER(38)
SNAME CHAR(10)
SUB1 NUMBER(38)
SUB2 NUMBER(38)
SUB3 NUMBER(38)
DISPLAY VIEW:
----------------------
SQL> SELECT * FROM ABC;
SYNTAX FOR DROP A VIEW:
---------------------------------
SQL> DROP VIEW <VIEW_NAME>
DROP A VIEW
-----------------------
SQL>DROP VIEW ABC;
VIEW DROPED
PROGRAM No. 05 : WRITE A PROGRAM TO DEMONSTRATE OF AGGREGATE FUNCTIONS
CREATION OF TABLE
--------------------------------
SQL> CREATE TABLE PRODUCT (PNO INT ,PNAME VARCHAR(30),PRICE FLOAT,QUANTITY INT);
TABLE CREATED.
INSERTION OF DATA INTO TABLE
-----------------------------------------------
SQL> INSERT INTO PRODUCT VALUES(1,'DAIRY MILK',60,2);
1 ROW CREATED.
SQL> INSERT INTO PRODUCT VALUES(2,'GOOD DAY',25,4);
1 ROW CREATED.
SQL> INSERT INTO PRODUCT VALUES(3,'BOOST',10,6);
1 ROW CREATED.
SQL> INSERT INTO PRODUCT VALUES(4,'MAGGI',5,10);
1 ROW CREATED.
SQL> INSERT INTO PRODUCT VALUES(5,'BOOK',20,20);
1 ROW CREATED.
DISPLAY OF DATA FROM TABLE:
-----------------------------------------------
SQL> SELECT * FROM PRODUCT;
PNO PNAME PRICE QUANTITY
---------- ------------------------------ ---------- ----------
1 DAIRY MILK 60 2
2 GOOD DAY 25 4
3 BOOST 10 6
4 MAGGI 5 10
5 BOOK 20 20
PERFORMING COUNT FUNCTION
------------------------------------------------
SQL> SELECT COUNT(PRICE) FROM PRODUCT;
COUNT(PRICE)
------------
5
PERFORMING SUM FUNCTION
------------------------------------------------
SQL> SELECT SUM(PRICE) FROM PRODUCT;
SUM(PRICE)
----------
120
PERFORMING AVG FUNCTION
------------------------------------------------
SQL> SELECT AVG(QUANTITY) FROM PRODUCT;
AVG(QUANTITY)
-------------
8.4
PERFORMING MAX FUNCTION
------------------------------------------------
SQL> SELECT MAX(PRICE) FROM PRODUCT;
MAX(PRICE)
----------
60
PERFORMING MIN FUNCTION
------------------------------------------------
SQL> SELECT MIN(PRICE) FROM PRODUCT;
MIN(PRICE)
----------
5
PERFORMING GROUP BY FUNCTION
------------------------------------------------
CREATE TABLE EMPLOY (SID INT,NAME VARCHAR(20),DEPT VARCHAR(10),SAL FLOAT);
TABLE CREATED
SELECT * FROM EMPLOY
SID NAME DEPT SAL
1 AYISHA ECE 6000
2 SINDHU IT 50000
3 SAI IT 80000
4 LALLI ECE 8000
SQL>SELECT DEPT,SUM(SAL) FROM EMPLOY GROUP BY DEPT;
DEPT SUM(SAL)
IT 130000
ECE 14000
PERFORMING HAVING FUNCTION
------------------------------------------------
SQL>SELECT DEPT,SUM(SAL) FROM EMPLOY GROUP BY DEPT HAVING SUM(SAL)>25000;
DEPT SUM(SAL)
IT 130000
PROGRAM NO.06: WRITE A STORED PROCEDURE TO PROCESS STUDENTS RESULTS
CREATION OF TABLE
--------------------------------
SQL> CREATE TABLE STU1(SNO INT,NAME CHAR(10),SUB1 INT,SUB2 INT, SUB3 INT);
TABLE CREATED.
INSERTION OF DATA INTO TABLE
-----------------------------------------------
SQL> INSERT INTO STU1 VALUES(100,'WASIM',70,75,89);
1 ROW CREATED.
SQL> INSERT INTO STU1 VALUES(200,'SIVA',75,69,88);
1 ROW CREATED.
CREATION OF STORED PROCEDURE:
------------------------------------------------------
CREATE OR REPLACE PROCEDURE STUDENT_RESULT
IS
STU STU1%ROWTYPE;
TOTAL NUMBER(4);
RESULT VARCHAR(4);
CURSOR C IS SELECT * FROM STU1;
BEGIN
FOR STU IN C
LOOP
DBMS_OUTPUT.PUT_LINE('STUDENT MARKLIST');
DBMS_OUTPUT.PUT_LINE('----------------------------');
DBMS_OUTPUT.PUT_LINE('SNO:'||STU.SNO||' SNAME:'||STU.NAME);
TOTAL:=STU.SUB1+STU.SUB2+STU.SUB3;
IF STU.SUB1>35 AND STU.SUB2>35 AND STU.SUB3>35
THEN
RESULT:='PASS';
ELSE
RESULT:='FAIL';
END IF;
DBMS_OUTPUT.PUT_LINE('M1:'||STU.SUB1||' M2:'||STU.SUB2||' M3:'||STU.SUB3);
DBMS_OUTPUT.PUT_LINE('TOTAL:'||TOTAL||' RESULT:'||RESULT);
END LOOP;
END;
/
PROCEDURE CREATED.
EXECUTION PROCEDURE:
-------------------------------
SQL>SET SERVEROUTPUT ON;
SQL> EXECUTE STUDENT_RESULT;
STUDENT MARKLIST
----------------------------
SNO:100 SNAME:WASIM
M1:70 M2:75 M3:89
TOTAL:234 RESULT:PASS
STUDENT MARKLIST
----------------------------
SNO:200 SNAME:SIVA
M1:75 M2:69 M3:88
TOTAL:232 RESULT:PASS
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
PROGRAM NO.7 : WRITE A PROGRAM TO DEMONSTRATE A FUNCTION
PROCEDURE:
DECLARE
NUM NUMBER;
FACTORIAL NUMBER;
FUNCTION FACT(X NUMBER) RETURN NUMBER IS
F NUMBER;
BEGIN
IF X=0 THEN
F := 1;
ELSE
F := X * FACT(X-1);
END IF;
RETURN F;
END;
BEGIN
NUM:= &NUM;
FACTORIAL := FACT(NUM);
DBMS_OUTPUT.PUT_LINE(‘ FACTORIAL ‘|| NUM ||’ IS ‘ || FACTORIAL);
END;
/
EXECUTION:
SQL>EXECUTE FUN.SQL;
ENTER VALUE FOR NUM: 3
FACTORIAL 3 IS 6
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
PROGRAM NO.8 :WRITE A PROGRAM TO DEMONSTRATE DATABASE TRIGGERS.
PROCEDURE:
SQL>CREATE TABLE EMPLOYEE_SALARY(EMP_NO NUMBER(2) PRIMARY KEY, BASIC NUMBER(8), HRA NUMBER(3), DA NUMBER(3), TOTAL_DEDUCTION NUMBER(3), NET_SALARY NUMBER(10), GROSS_SALARY NUMBER(10));
SQL> SELECT * FROM EMPLOYEE_SALARY;
EMP_NO BASIC HRA DA TOTAL_DEDUCTION NET_SALARY GROSS_SALARY
---------- ---------- ---------- ---------- --------------- ----------
2 15000 4000 1000 5000 15000 20000
1 31000 8000 1000 5000 35000 40000
3 14000 4000 1000 5000 15000 19000
4 14000 4000 1000 5000 15000 19000
5 13000 4000 1000 5000 15000 18000
SQL> ED PROG8.SQL;
CREATE OR REPLACE TRIGGER T AFTER UPDATE ON EMPLOYEE_SALARY
FOR EACH ROW
BEGIN
INSERT INTO BACKUP VALUES (:OLD.EMP_NO,:OLD.GROSS_SALARY,:NEW.GROSS_SALARY);
END;
/
EXECUTION:
SQL> @ PROG8.SQL;
TRIGGER CREATED.
SQL> UPDATE EMPLOYEE_SALARY SET GROSS_SALARY=44000 WHERE EMP_NO=1;
SQL> SELECT * FROM BACKUP;
EMPNO OLD_GROSS_SALARY NEW_GROSS_SALARY
1 40000 44000
SQL> UPDATE EMPLOYEE_SALARY SET GROSS_SALARY=20000 WHERE EMP_NO=2;
SQL> SELECT * FROM BACKUP;
EMPNO OLD_GROSS_SALARY NEW_GROSS_SALARY
1 40000 44000
2 17600 20000
SQL> UPDATE EMPLOYEE_SALARY SET GROSS_SALARY=48000 WHERE EMP_NO=1;
SQL> SELECT * FROM BACKUP;
EMPNO OLD_GROSS_SALARY NEW_GROSS_SALARY
1 40000 44000
2 17600 20000
1 44000 48000
PROGRAM NO.9 :WRITE A PROGRAM TO DEMONSTRATE DATABASE CURSORS.
PROCEDURE:
SQL> CREATE TABLE STUDENT(RNO NUMBER(10),S1 NUMBER(10),S2 NUMBER(10),S3 NUMBER(10),S4 NUMBER(10),TOTAL NUMBER(20),PERCENTAGE NUMBER(6));
TABLE CREATED.
SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10011,56,78,79,56);
SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10012,45,67,34,58);
SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10013,76,86,94,58);
SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10014,57,48,39,92);
SQL> SELECT * FROM STUDENT;
RNO S1 S2 S3 S4 TOTAL PERCENTAGE
10011 56 78 79 56
10012 45 67 34 58
10013 76 86 94 58
10014 57 48 39 92
SQL>ED PROG9.SQL;
DECLARE
T STUDENT.TOTAL%TYPE;
P STUDENT.PERCENTAGE%TYPE;
CURSOR STU IS SELECT * FROM STUDENT;
RW STU%ROWTYPE;
BEGIN
OPEN STU;
LOOP
FETCH STU INTO RW;
EXIT WHEN STU%NOTFOUND;
T:=RW.S1+RW.S2+RW.S3+RW.S4;
P:=T*0.25;
UPDATE STUDENT SET TOTAL=T, PERCENTAGE=P WHERE RNO=RW.RNO;
END LOOP;
CLOSE STU;
END;
/
SQL> /
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT * FROM STUDENT;
RNO S1 S2 S3 S4 TOTAL PERCENTAGE
10011 56 78 79 56 269 67
10012 45 67 34 58 204 51
10013 76 86 94 58 314 79
10014 57 48 39 92 236 59
PROGRAM NO.10: WRITE A PROGRAM TO DEMONSTRATE JOINS
SQL>CREATE TABLE EMP1(ENAME CHAR(10),ENO NUMBER(3),MANGERNO NUMBER(3));
TABLE CREATED.
SQL> INSERT INTO EMPS1 VALUES('&ENAME',&ENO,&EMANGERNO);
ENTER VALUE FOR ENAME: NAGAENTER VALUE FOR ENO: 101
ENTER VALUE FOR EMANGERNO: 121
OLD 1: INSERT INTO EMPS1 VALUES('&ENAME',&ENO,&EMANGERNO)
NEW 1: INSERT INTO EMPS1 VALUES('NAGA',101,121)
1 ROW CREATED.
SQL> SELECT * FROM EMPS1;
ENAME ENO MANGERNO
---------- ---------- ----------
NAGA 101 121
NAVIN 111 121
RAM 121 131
RAJ 141 131
SELF JOINS:
----------------------
SQL> SELECT EMPS1.ENAME,EMPS1.ENO,EMPS1.MANGERNO FROM EMPS1 WHEREEMPS1.ENO=EMPS1.MANGERNO;
NO ROWS SELECTED
SQL> SELECT * FROM CUSTOMERS1;
ENO SALARY LOCATION
-------------------- ----------
101 10000 CHENNAI
111 10500 MADURAI
131 8000 PONDY
141 5000 AVADI
151 9800 LOCO
EQUI JOINS:
SQL> SELECTEMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.ENO,CUSTOMERS1.SALARY FROMEMPS1,CUSTOMERS1 WHERE EMPS1.ENO=CUSTOMERS1.ENO;
ENAME ENO ENO SALARY
---------- ---------- ---------- ----------
NAGA 101 101 10000
NAVIN 111 111 10500
RAJ 141 141 5000
NON EQUI JOINS:
SQL> SELECT EMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.SALARY,CUSTOMERS1.ENO,CUSTOMERS.LOCATIONFROM EMPS1,CUSTOMERS1.ENO<>CUSTOMERS1.ENO;
ENAME ENO SALARY ENO LOCATION
---------- ---------- ---------- ---------- --------------------
NAVIN 111 10000 101 CHENNAI
RAM 121 10000 101 CHENNAI
RAJ 141 10000 101 CHENNAI
NAGA 101 10500 111 PONDY
RAM 121 10500 111 PONDY
RAJ 141 10500 111 PONDY
NAGA 101 8000 131 AVADI
NAVIN 111 8000 131 AVAD
IRAM 121 8000 131 AVADI
RAJ 141 8000 131 AVADI
NAGA 101 5000 141 LOCO
NAVIN 111 5000 141 LOCO
RAM 121 5000 141 LOCO
NAGA 101 9800 151 CHOOLAI
NAVIN 111 9800 151 CHOOLAI
RAM 121 9800 151 CHOOLAI
RAJ 141 9800 151 CHOOLAI
17 ROWS SELECTED.
LEFT OUTER JOINS:
SQL>SELECT EMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.SALARY,CUSTOMERS1.ENO FROMEMPS1,CUSTOMERS1 WHERE EMPS1.ENO(+)=CUSTOMERS1.ENO;
ENAME ENO SALARY ENO
---------- ---------- ---------- --------
NAGA 101 10000 101
NAVIN 111 10500 111
8000 131
RAJ 141 5000 141
9800 151
RIGHT OUTER JOINS:
SQL> SELECT EMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.ENO,CUSTOMERS1.SALARY FROMEMPS1,CUSTOMERS1 WHERE EMPS1.ENO=CUSTOMERS1.ENO(+);
ENAME ENO ENO SALARY
---------- ---------- ---------- ----------
NAGA 101 101 10000
NAVIN 111 111 10500
RAM 121
RAJ 141 141 5000
