SQL & PL/SQL Lab Assignment Answers (Full Set
1–20)
SQL & PL/SQL LAB ASSIGNMENT ANSWERS (Oracle Version)
=====================================================
------------------------------------------------------------
1. Create EMP table and perform operations
------------------------------------------------------------
CREATE TABLE EMP (
EMPNO NUMBER(6) PRIMARY KEY,
ENAME VARCHAR2(20) NOT NULL,
JOB VARCHAR2(10) NOT NULL,
DEPTNO NUMBER(3),
SAL NUMBER(7,2)
);
ALTER TABLE EMP ADD (COMPETENCE VARCHAR2(50));
ALTER TABLE EMP MODIFY (JOB VARCHAR2(20));
ALTER TABLE EMP DROP COLUMN EXPERIENCE;
TRUNCATE TABLE EMP;
------------------------------------------------------------
2. Create EMP2 table with constraints
------------------------------------------------------------
CREATE TABLE DEPT (
DEPTNO NUMBER(3) PRIMARY KEY,
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);
CREATE TABLE EMP2 (
EMPNO NUMBER(6) PRIMARY KEY,
ENAME VARCHAR2(20) NOT NULL,
JOB VARCHAR2(20) NOT NULL,
DEPTNO NUMBER(3),
SAL NUMBER(7,2),
CONSTRAINT fk_emp_dept2 FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
ALTER TABLE EMP2 ADD CONSTRAINT uq_ename_dept UNIQUE (ENAME, DEPTNO);
------------------------------------------------------------
3. ROLL_NO Table
------------------------------------------------------------
CREATE TABLE ROLL_NO (
ROLL_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
CGPA FLOAT
);
INSERT INTO ROLL_NO VALUES (1, 'Alice', 8.5);
INSERT INTO ROLL_NO VALUES (2, 'Bob', 7.2);
INSERT INTO ROLL_NO VALUES (3, 'Cara', 9.1);
INSERT INTO ROLL_NO VALUES (4, 'Dan', 6.8);
INSERT INTO ROLL_NO VALUES (5, 'Eve', 8.0);
------------------------------------------------------------
4. PL/SQL Program to swap two numbers
------------------------------------------------------------
DECLARE
a NUMBER := 10;
b NUMBER := 20;
temp NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before swap: a=' || a || ', b=' || b);
temp := a;
a := b;
b := temp;
DBMS_OUTPUT.PUT_LINE('After swap: a=' || a || ', b=' || b);
END;
/
------------------------------------------------------------
5. PL/SQL Program using IF...ELSE
------------------------------------------------------------
DECLARE
num NUMBER := -5;
BEGIN
IF num > 0 THEN
DBMS_OUTPUT.PUT_LINE('Positive');
ELSIF num < 0 THEN
DBMS_OUTPUT.PUT_LINE('Negative');
ELSE
DBMS_OUTPUT.PUT_LINE('Zero');
END IF;
END;
/
------------------------------------------------------------
6. PL/SQL Program to find the largest of three numbers
------------------------------------------------------------
DECLARE
a NUMBER := 15;
b NUMBER := 9;
c NUMBER := 27;
largest NUMBER;
BEGIN
largest := a;
IF b > largest THEN largest := b; END IF;
IF c > largest THEN largest := c; END IF;
DBMS_OUTPUT.PUT_LINE('Largest is: ' || largest);
END;
/
------------------------------------------------------------
7. PL/SQL Program to reverse a number
------------------------------------------------------------
DECLARE
n NUMBER := 12345;
rev NUMBER := 0;
digit NUMBER;
temp NUMBER := n;
BEGIN
WHILE temp > 0 LOOP
digit := MOD(temp, 10);
rev := rev * 10 + digit;
temp := TRUNC(temp/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Original: ' || n || ' Reversed: ' || rev);
END;
/
------------------------------------------------------------
8. PL/SQL Program for factorial
------------------------------------------------------------
DECLARE
n NUMBER := 6;
fact NUMBER := 1;
BEGIN
IF n < 0 THEN
DBMS_OUTPUT.PUT_LINE('Factorial not defined');
ELSE
FOR i IN 1..n LOOP
fact := fact * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial of ' || n || ' is ' || fact);
END IF;
END;
/
------------------------------------------------------------
9. Create a procedure to display "Hello World"
------------------------------------------------------------
CREATE OR REPLACE PROCEDURE say_hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/
------------------------------------------------------------
10. Procedure to find minimum of two values & function to compute square
------------------------------------------------------------
CREATE OR REPLACE PROCEDURE proc_min_of_two(a IN NUMBER, b IN NUMBER, min_val
OUT NUMBER) IS
BEGIN
IF a < b THEN min_val := a; ELSE min_val := b; END IF;
END;
/
CREATE OR REPLACE FUNCTION fn_square(x IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN x * x;
END;
/
------------------------------------------------------------
11. Function to find maximum of two numbers
------------------------------------------------------------
CREATE OR REPLACE FUNCTION fn_max_of_two(x IN NUMBER, y IN NUMBER) RETURN
NUMBER IS
BEGIN
IF x > y THEN RETURN x; ELSE RETURN y; END IF;
END;
/
------------------------------------------------------------
12. CUSTOMERS and ORDERS tables with joins
------------------------------------------------------------
CREATE TABLE CUSTOMERS (
ID VARCHAR2(10) PRIMARY KEY,
NAME VARCHAR2(50),
AGE NUMBER,
ADDRESS VARCHAR2(100),
SALARY NUMBER
);
CREATE TABLE ORDERS (
OID NUMBER PRIMARY KEY,
DATE DATE,
CUSTOMER_ID VARCHAR2(10),
AMOUNT NUMBER,
CONSTRAINT fk_orders_customer FOREIGN KEY (CUSTOMER_ID) REFERENCES
CUSTOMERS(ID)
);
-- INNER JOIN
SELECT [Link], [Link], [Link], [Link] FROM CUSTOMERS c JOIN ORDERS o ON [Link] =
o.CUSTOMER_ID;
-- LEFT JOIN
SELECT [Link], [Link], [Link], [Link] FROM CUSTOMERS c LEFT JOIN ORDERS o ON
[Link] = o.CUSTOMER_ID;
-- RIGHT JOIN
SELECT [Link], [Link], [Link], [Link] FROM CUSTOMERS c RIGHT JOIN ORDERS o ON
[Link] = o.CUSTOMER_ID;
-- FULL OUTER JOIN
SELECT [Link], [Link], [Link], [Link] FROM CUSTOMERS c FULL OUTER JOIN ORDERS
o ON [Link] = o.CUSTOMER_ID;
------------------------------------------------------------
13. Outer Join Variations (repeated demonstration)
------------------------------------------------------------
-- Same queries as above for LEFT, RIGHT, FULL joins
------------------------------------------------------------
14. STUD1 and STUD2 tables with joins
------------------------------------------------------------
CREATE TABLE STUD1 (
REG_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
MARK_1 NUMBER,
MARK_2 NUMBER,
RESULT VARCHAR2(10)
);
CREATE TABLE STUD2 (
NAME VARCHAR2(50),
GRADE VARCHAR2(5)
);
-- LEFT OUTER JOIN
SELECT s1.REG_NO, [Link], [Link] FROM STUD1 s1 LEFT JOIN STUD2 s2 ON
[Link] = [Link];
-- RIGHT OUTER JOIN
SELECT s1.REG_NO, [Link], [Link] FROM STUD1 s1 RIGHT JOIN STUD2 s2 ON
[Link] = [Link];
-- FULL OUTER JOIN
SELECT s1.REG_NO, [Link], [Link] FROM STUD1 s1 FULL OUTER JOIN STUD2 s2 ON
[Link] = [Link];
------------------------------------------------------------
15. Employee Management System (Visual Studio)
------------------------------------------------------------
Steps:
1. Create [Link] Core MVC or Windows Forms project.
2. Use Oracle/SQL Server DB.
3. Implement CRUD for Employee table.
4. Create UI forms for Add/Edit/Delete.
5. Add validations and authentication.
------------------------------------------------------------
16. UML Diagram for Library Management System
------------------------------------------------------------
Classes: Book, Member, Loan, Author, Librarian.
Relationships: Book–Author (1..*), Member–Loan (1..*).
Attributes: Book(ISBN, Title, Publisher, Year).
Methods: [Link](), [Link]().
------------------------------------------------------------
17. UML Diagram for Employee Management System
------------------------------------------------------------
Classes: Employee, Department, Attendance, Payroll.
Relationships: Employee–Department(1..*), Employee–Payroll(1..*).
------------------------------------------------------------
18. PL/SQL Program for Armstrong Number
------------------------------------------------------------
DECLARE
n NUMBER := 153;
temp NUMBER;
sum_pow NUMBER := 0;
digit NUMBER;
digits NUMBER := 0;
BEGIN
temp := n;
WHILE temp > 0 LOOP
digits := digits + 1;
temp := TRUNC(temp/10);
END LOOP;
temp := n;
WHILE temp > 0 LOOP
digit := MOD(temp, 10);
sum_pow := sum_pow + POWER(digit, digits);
temp := TRUNC(temp/10);
END LOOP;
IF sum_pow = n THEN
DBMS_OUTPUT.PUT_LINE(n || ' is Armstrong');
ELSE
DBMS_OUTPUT.PUT_LINE(n || ' is NOT Armstrong');
END IF;
END;
/
------------------------------------------------------------
19. Library Management System (Visual Studio)
------------------------------------------------------------
Steps:
1. Create tables: Books, Members, Loans, Authors.
2. Implement CRUD and issue/return system.
3. Add fine and due date tracking.
4. UI using [Link] or WPF.
------------------------------------------------------------
20. PROPERTY Table (1NF)
------------------------------------------------------------
CREATE TABLE PROPERTY (
PROPERTY_ID NUMBER PRIMARY KEY,
COUNTRY VARCHAR2(50),
PAID VARCHAR2(3),
ARREARS NUMBER(12,2),
PRICE NUMBER(12,2),
TAX NUMBER(12,2),
TRANS_DATE DATE
);
INSERT INTO PROPERTY VALUES (1, 'USA', 'YES', 0, 250000, 12500,
TO_DATE('2024-01-10','YYYY-MM-DD'));
INSERT INTO PROPERTY VALUES (2, 'India', 'NO', 5000, 120000, 6000,
TO_DATE('2024-03-05','YYYY-MM-DD'));