DATABASE PROGRAMMING LAB REPORT

1. DML Queries:
a) INSERT INTO EMP VALUES (105, 'Brihas', 55000, 10);
b) UPDATE EMP SET salary = salary * 1.1 WHERE name = 'Brihas';
c) DELETE FROM EMP WHERE salary < 25000;
2. SELECT [Link], [Link], d.dept_name FROM EMP e INNER JOIN DEPT d ON e.dept_id =
d.dept_id;
3. SELECT [Link], d.dept_name FROM EMP e LEFT JOIN DEPT d ON e.dept_id = d.dept_id;
4. SELECT [Link], d.dept_name, [Link] FROM EMP e JOIN DEPT d ON e.dept_id =
d.dept_id;
5. SELECT [Link], [Link] AS manager FROM EMP e JOIN EMP m ON e.manager_id =
m.emp_id;
6. DECLARE a NUMBER:=10; b NUMBER:=20; BEGIN DBMS_OUTPUT.PUT_LINE(a+b);
DBMS_OUTPUT.PUT_LINE(a-b); DBMS_OUTPUT.PUT_LINE(a*b);
DBMS_OUTPUT.PUT_LINE(a/b); END;
7. DECLARE m NUMBER:=75; BEGIN IF m>=80 THEN DBMS_OUTPUT.PUT_LINE('Distinction');
ELSIF m>=60 THEN DBMS_OUTPUT.PUT_LINE('First Division'); ELSE
DBMS_OUTPUT.PUT_LINE('Pass'); END IF; END;
8. DECLARE n NUMBER:=5; i NUMBER:=1; BEGIN WHILE i<=10 LOOP
DBMS_OUTPUT.PUT_LINE(n||' x '||i||' = '||(n*i)); i:=i+1; END LOOP; END;
9. DECLARE TYPE empRec IS RECORD(name VARCHAR2(20), salary NUMBER, dept
VARCHAR2(20)); e empRec; BEGIN [Link]:='Ram'; [Link]:=50000; [Link]:='IT';
DBMS_OUTPUT.PUT_LINE([Link]||' '||[Link]||' '||[Link]); END;
10. BEGIN DECLARE a NUMBER:=10; b NUMBER:=0; c NUMBER; BEGIN c:=a/b; EXCEPTION
WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('Division by zero'); WHEN
NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data'); WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error'); END; END;
11. CREATE OR REPLACE PROCEDURE calc_sal(basic IN NUMBER, allowance IN NUMBER,
total OUT NUMBER) AS BEGIN total := basic + allowance; END;
12. CREATE OR REPLACE FUNCTION annual_salary(monthly NUMBER) RETURN NUMBER AS
BEGIN RETURN monthly*12; END;
13. DECLARE CURSOR c IS SELECT * FROM EMP WHERE salary>50000; r EMP%ROWTYPE;
BEGIN OPEN c; LOOP FETCH c INTO r; EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE([Link]||' '||[Link]); END LOOP; CLOSE c; END;
14. DECLARE CURSOR d IS SELECT * FROM DEPT; CURSOR e(deptid NUMBER) IS SELECT
name FROM EMP WHERE dept_id=deptid; r1 DEPT%ROWTYPE; r2 EMP%ROWTYPE; BEGIN
OPEN d; LOOP FETCH d INTO r1; EXIT WHEN d%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department: '||r1.dept_name); OPEN e(r1.dept_id); LOOP FETCH e
INTO r2; EXIT WHEN e%NOTFOUND; DBMS_OUTPUT.PUT_LINE('- '||[Link]); END LOOP;
CLOSE e; END LOOP; CLOSE d; END;
15. CREATE OR REPLACE TRIGGER gen_roll BEFORE INSERT ON STUDENT FOR EACH
ROW BEGIN SELECT NVL(MAX(rollno),0)+1 INTO :[Link] FROM STUDENT; END;