GURU JAMBHESHWAR UNIVERSITY OF SCIENCE & TECHNOLOGY,

HISAR
Department of Computer Science & Engineering

LAB FILE
MCA-16 · Database Management System Lab

Tool: Oracle 11g / 19c | Credits: 2 | Internal 30 + External 70

Submitted By
Name : ________________________________

Roll No : ____________ | Batch : ____________

Session : 2024-25 | Section : _______

Submitted To
Course Coordinator, Dept. of CSE

Dept. of Computer Science & Engineering Page 1 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

INDEX

[Link]. Experiment Title Date Marks Sign

1 Oracle Login, GUI Exploration & Basic Commands

2A Table Creation – Employee & Department

2B Table Creation – Student & Course

2C Table Creation – Product & Orders

3A Constraints – PRIMARY KEY, NOT NULL, UNIQUE, CHECK

3B Constraints – FOREIGN KEY & DEFAULT

4A DML – INSERT, UPDATE, DELETE on Employee Table

4B DML – INSERT, UPDATE, DELETE on Student Table

5A ALTER – Add, Modify, Drop Columns

5B ALTER – Rename Table & Add/Drop Constraints

SELECT – WHERE, ORDER BY, GROUP BY, HAVING,

6A
Aggregates

SELECT – NVL, DECODE, CASE WHEN, String & Date

6B
Functions

7A Set Operations – UNION, INTERSECT, MINUS

7B Set Operations – UNION ALL & Practical Scenarios

8A Joins – INNER, LEFT, RIGHT, FULL OUTER, SELF JOIN

8B Views – CREATE, SELECT, UPDATE, DROP VIEW

9 Sub-Queries – Single Row, Multi Row, Correlated, Scalar

Dept. of Computer Science & Engineering Page 2 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 1

To login to Oracle RDBMS with valid credentials, explore the GUI, and practice basic
Aim SQL commands.

Theory
Oracle is a widely used RDBMS. SQL*Plus is its CLI. DESC shows table structure. SELECT, INSERT, COMMIT are
fundamental SQL commands.

Execution
Oracle SQL*Plus

-- Connect to Oracle
SQL> CONNECT scott/tiger
Connected.
-- List all tables owned by current user
SQL> SELECT table_name FROM user_tables;
TABLE_NAME
------------------------------
EMP
DEPT
BONUS
SALGRADE
4 rows selected.
-- Describe the EMP table structure
SQL> DESC emp;
Name Null? Type
----------------------------- -------- -------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
-- View all employee records
SQL> SELECT empno, ename, job, sal, deptno FROM emp;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- --------- ------
7369 SMITH CLERK 800 20
7499 ALLEN SALESMAN 1600 30
7521 WARD SALESMAN 1250 30
7566 JONES MANAGER 2975 20
7839 KING PRESIDENT 5000 10
5 rows selected.
-- Insert a new department
SQL> INSERT INTO dept VALUES (50, 'TESTING', 'DELHI');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM dept WHERE deptno = 50;
DEPTNO DNAME LOC
---------- -------------- -------------
50 TESTING DELHI
1 row selected.
SQL> EXIT
Disconnected from Oracle Database 19c.

Conclusion: Oracle SQL*Plus accessed successfully. user_tables listed, DESC showed EMP structure.
SELECT, INSERT, COMMIT practiced and outputs verified.

Dept. of Computer Science & Engineering Page 3 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 2A

Aim To create EMPLOYEE and DEPARTMENT tables using various Oracle data types.

Theory
Oracle data types: NUMBER(p,s) – numeric, VARCHAR2(n) – variable string, CHAR(n) – fixed string, DATE – date-time,
CLOB – large text.

Execution
Oracle SQL*Plus

-- Create DEPARTMENT table

SQL> CREATE TABLE department (
2 dept_id NUMBER(4) PRIMARY KEY,
2 dept_name VARCHAR2(30) NOT NULL,
2 location VARCHAR2(50)
2 );
Table created.
-- Create EMPLOYEE table
SQL> CREATE TABLE employee (
2 emp_id NUMBER(6) PRIMARY KEY,
2 first_name VARCHAR2(25) NOT NULL,
2 last_name VARCHAR2(25) NOT NULL,
2 email VARCHAR2(50) UNIQUE,
2 phone CHAR(10),
2 hire_date DATE DEFAULT SYSDATE,
2 job_title VARCHAR2(30),
2 salary NUMBER(10,2),
2 dept_id NUMBER(4) REFERENCES department(dept_id)
2 );
Table created.
SQL> INSERT INTO department VALUES (10,'Engineering','Delhi');
1 row created.
SQL> INSERT INTO department VALUES (20,'HR','Mumbai');
1 row created.
SQL> INSERT INTO department VALUES (30,'Finance','Bangalore');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO employee VALUES (101,'Ravi','Sharma','ravi@[Link]',
2 '9876543210',SYSDATE,'Developer',65000,10);
1 row created.
SQL> INSERT INTO employee VALUES (102,'Neha','Gupta','neha@[Link]',
2 '9812345670',SYSDATE,'HR Manager',72000,20);
1 row created.
SQL> INSERT INTO employee VALUES (103,'Amit','Verma','amit@[Link]',
2 '9845678901',SYSDATE,'Analyst',58000,30);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM department;
DEPT_ID DEPT_NAME LOCATION
---------- ------------------------------ ------------------
10 Engineering Delhi
20 HR Mumbai
30 Finance Bangalore
SQL> SELECT emp_id,first_name,job_title,salary,dept_id FROM employee;
EMP_ID FIRST_NAME JOB_TITLE SALARY DEPT_ID
--------- ----------- ------------------ ------- -------
101 Ravi Developer 65000 10
102 Neha HR Manager 72000 20
103 Amit Analyst 58000 30
3 rows selected.

Dept. of Computer Science & Engineering Page 4 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Conclusion: DEPARTMENT and EMPLOYEE tables created using NUMBER, VARCHAR2, CHAR, DATE.
FK relation established. INSERT and SELECT verified.

Dept. of Computer Science & Engineering Page 5 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 2B

Aim To create STUDENT and COURSE tables with different fields and data types.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE course (

2 course_id CHAR(8) PRIMARY KEY,
2 course_name VARCHAR2(60) NOT NULL,
2 credits NUMBER(1) CHECK (credits BETWEEN 1 AND 6),
2 duration VARCHAR2(20)
2 );
Table created.
SQL> CREATE TABLE student (
2 roll_no NUMBER(8) PRIMARY KEY,
2 s_name VARCHAR2(50) NOT NULL,
2 dob DATE,
2 gender CHAR(1) CHECK (gender IN ('M','F','O')),
2 email VARCHAR2(60) UNIQUE,
2 mobile NUMBER(10),
2 enrol_date DATE DEFAULT SYSDATE,
2 course_id CHAR(8) REFERENCES course(course_id)
2 );
Table created.
SQL> INSERT INTO course VALUES ('MCA001','Master of Computer Applications',4,'2 Years');
1 row created.
SQL> INSERT INTO course VALUES ('MCA002','Data Structures',3,'6 Months');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO student VALUES (20240001,'Priya Mehta','15-AUG-2001',
2 'F','priya@[Link]',9876543210,SYSDATE,'MCA001');
1 row created.
SQL> INSERT INTO student VALUES (20240002,'Karan Singh','22-MAR-2000',
2 'M','karan@[Link]',9812340987,SYSDATE,'MCA001');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT roll_no, s_name, gender, course_id FROM student;
ROLL_NO S_NAME G COURSE_I
---------- -------------------- - --------
20240001 Priya Mehta F MCA001
20240002 Karan Singh M MCA001
2 rows selected.

Conclusion: STUDENT and COURSE tables created with CHAR, NUMBER, DATE, VARCHAR2. CHECK,
UNIQUE, FK constraints applied and verified.

Dept. of Computer Science & Engineering Page 6 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 2C

Aim To create PRODUCT and ORDERS tables with different fields and data types.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE product (

2 prod_id NUMBER(6) PRIMARY KEY,
2 prod_name VARCHAR2(80) NOT NULL,
2 category VARCHAR2(30),
2 unit_price NUMBER(10,2) NOT NULL,
2 stock_qty NUMBER(6) DEFAULT 0
2 );
Table created.
SQL> CREATE TABLE orders (
2 order_id NUMBER(8) PRIMARY KEY,
2 order_date DATE DEFAULT SYSDATE,
2 cust_name VARCHAR2(50) NOT NULL,
2 prod_id NUMBER(6) REFERENCES product(prod_id),
2 quantity NUMBER(4) CHECK (quantity > 0),
2 total_amt NUMBER(12,2)
2 );
Table created.
SQL> INSERT INTO product VALUES (1001,'Laptop','Electronics',55000,50);
1 row created.
SQL> INSERT INTO product VALUES (1002,'Mouse','Electronics',850,200);
1 row created.
SQL> INSERT INTO product VALUES (1003,'Desk Chair','Furniture',12000,30);
1 row created.
SQL> INSERT INTO orders VALUES (5001,SYSDATE,'Rahul Jain',1001,2,110000);
1 row created.
SQL> INSERT INTO orders VALUES (5002,SYSDATE,'Seema Roy',1002,5,4250);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT o.order_id, o.cust_name, p.prod_name, [Link], o.total_amt
2 FROM orders o JOIN product p ON o.prod_id = p.prod_id;
ORDER_ID CUST_NAME PROD_NAME QUANTITY TOTAL_AMT
---------- -------------- ------------ -------- ----------
5001 Rahul Jain Laptop 2 110000
5002 Seema Roy Mouse 5 4250
2 rows selected.

Conclusion: PRODUCT and ORDERS tables created with FK, DEFAULT, CHECK. JOIN query verified.

Dept. of Computer Science & Engineering Page 7 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 3A

Aim To create tables with PRIMARY KEY, NOT NULL, UNIQUE, and CHECK constraints.

Theory
Constraints enforce data integrity. PRIMARY KEY: unique + not null row identifier. NOT NULL: mandatory column.
UNIQUE: distinct values. CHECK: boolean condition.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE bank_account (

2 acc_no NUMBER(10) PRIMARY KEY,
2 holder_name VARCHAR2(60) NOT NULL,
2 pan_no CHAR(10) UNIQUE NOT NULL,
2 acc_type VARCHAR2(20) CHECK (acc_type IN ('SAVINGS','CURRENT','FD')),
2 balance NUMBER(12,2) CHECK (balance >= 0),
2 open_date DATE NOT NULL
2 );
Table created.
-- Test NOT NULL violation
SQL> INSERT INTO bank_account
2 VALUES (1001, NULL, 'ABCDE1234F', 'SAVINGS', 5000, SYSDATE);
ERROR at line 1:
ORA-01400: cannot insert NULL into
("SCOTT"."BANK_ACCOUNT"."HOLDER_NAME")
-- Test invalid CHECK value
SQL> INSERT INTO bank_account
2 VALUES (1001,'Sunita Rao','ABCDE1234F','LOAN',5000,SYSDATE);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C...) violated
-- Valid inserts
SQL> INSERT INTO bank_account VALUES
2 (1001,'Sunita Rao','ABCRS1234F','SAVINGS',15000,SYSDATE);
1 row created.
SQL> INSERT INTO bank_account VALUES
2 (1002,'Mohan Das','PQRTY5678G','CURRENT',80000,SYSDATE);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT acc_no, holder_name, acc_type, balance FROM bank_account;
ACC_NO HOLDER_NAME ACC_TYPE BALANCE
---------- -------------------- -------------------- -------
1001 Sunita Rao SAVINGS 15000
1002 Mohan Das CURRENT 80000
2 rows selected.
-- View constraint metadata from data dictionary
SQL> SELECT constraint_name, constraint_type, column_name
2 FROM user_cons_columns WHERE table_name = 'BANK_ACCOUNT';
CONSTRAINT_NAME C COLUMN_NAME
------------------------- -- --------------------
SYS_C001 P ACC_NO
SYS_C002 C ACC_TYPE
SYS_C003 C BALANCE
SYS_C004 U PAN_NO
4 rows selected.

Conclusion: PRIMARY KEY, NOT NULL, UNIQUE, CHECK applied and tested. ORA-01400 raised on NULL;
ORA-02290 on CHECK violation.

Dept. of Computer Science & Engineering Page 8 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 3B

To create tables with FOREIGN KEY and DEFAULT constraints and test referential
Aim integrity.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE college (

2 college_id NUMBER(4) PRIMARY KEY,
2 college_name VARCHAR2(80) NOT NULL,
2 city VARCHAR2(30) DEFAULT 'Hisar'
2 );
Table created.
SQL> CREATE TABLE faculty (
2 fac_id NUMBER(6) PRIMARY KEY,
2 fac_name VARCHAR2(50) NOT NULL,
2 department VARCHAR2(40),
2 designation VARCHAR2(30) DEFAULT 'Lecturer',
2 college_id NUMBER(4),
2 CONSTRAINT fk_col FOREIGN KEY (college_id)
2 REFERENCES college(college_id) ON DELETE CASCADE
2 );
Table created.
SQL> INSERT INTO college VALUES (1,'GJU S&T','Hisar');
1 row created.
SQL> INSERT INTO college VALUES (2,'NIT Kurukshetra','Kurukshetra');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO faculty VALUES (201,'Dr. A. Sharma','CSE','Professor',1);
1 row created.
SQL> INSERT INTO faculty VALUES (202,'Dr. B. Kaur','IT',DEFAULT,1);
1 row created.
SQL> INSERT INTO faculty VALUES (203,'Prof. C. Singh','ECE','Asst. Professor',2);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT fac_id, fac_name, designation, college_id FROM faculty;
FAC_ID FAC_NAME DESIGNATION COLLEGE_ID
---------- ------------------- -------------------- ----------
201 Dr. A. Sharma Professor 1
202 Dr. B. Kaur Lecturer 1
203 Prof. C. Singh Asst. Professor 2
3 rows selected.
-- FK violation: college_id=99 does not exist
SQL> INSERT INTO faculty VALUES (204,'X','CSE','Lecturer',99);
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_COL) violated
- parent key not found
-- CASCADE DELETE: delete college 1 -> removes its faculty rows
SQL> DELETE FROM college WHERE college_id = 1;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT fac_id, fac_name, college_id FROM faculty;
FAC_ID FAC_NAME COLLEGE_ID
---------- ------------------- ----------
203 Prof. C. Singh 2
1 row selected.

Dept. of Computer Science & Engineering Page 9 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Conclusion: FK with ON DELETE CASCADE and DEFAULT demonstrated. ORA-02291 on invalid parent.
Cascade removed dependent rows automatically.

Dept. of Computer Science & Engineering Page 10 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 4A

Aim To insert, delete, and modify records using DML commands on the Employee table.

Theory
DML (INSERT, UPDATE, DELETE) manages data. Changes are temporary until COMMIT. ROLLBACK undoes all
uncommitted changes in the current transaction.

Execution

Dept. of Computer Science & Engineering Page 11 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Oracle SQL*Plus

SQL> CREATE TABLE emp_info (

2 emp_id NUMBER(5) PRIMARY KEY,
2 emp_name VARCHAR2(40) NOT NULL,
2 dept VARCHAR2(20),
2 salary NUMBER(9,2),
2 city VARCHAR2(25)
2 );
Table created.
-- Bulk INSERT using INSERT ALL
SQL> INSERT ALL
2 INTO emp_info VALUES (1001,'Rajesh Kumar','IT',45000,'Delhi')
2 INTO emp_info VALUES (1002,'Priya Singh','HR',38000,'Mumbai')
2 INTO emp_info VALUES (1003,'Anil Mehta','Finance',52000,'Pune')
2 INTO emp_info VALUES (1004,'Sunita Roy','IT',47000,'Delhi')
2 INTO emp_info VALUES (1005,'Vikas Sharma','Admin',33000,'Jaipur')
2 SELECT 1 FROM DUAL;
5 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM emp_info;
EMP_ID EMP_NAME DEPT SALARY CITY
--------- ----------------- ---------- ------ ----------
1001 Rajesh Kumar IT 45000 Delhi
1002 Priya Singh HR 38000 Mumbai
1003 Anil Mehta Finance 52000 Pune
1004 Sunita Roy IT 47000 Delhi
1005 Vikas Sharma Admin 33000 Jaipur
5 rows selected.
-- UPDATE: 10% salary hike for IT dept
SQL> UPDATE emp_info SET salary = salary * 1.10 WHERE dept = 'IT';
2 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT emp_id, emp_name, dept, salary FROM emp_info WHERE dept='IT';
EMP_ID EMP_NAME DEPT SALARY
--------- ----------------- ---------- ------
1001 Rajesh Kumar IT 49500
1004 Sunita Roy IT 51700
-- UPDATE multiple columns
SQL> UPDATE emp_info SET dept='Operations', city='Chennai'
2 WHERE emp_id = 1005;
1 row updated.
SQL> COMMIT;
Commit complete.
-- DELETE a row
SQL> DELETE FROM emp_info WHERE emp_id = 1003;
1 row deleted.
SQL> COMMIT;
Commit complete.
-- DELETE then ROLLBACK (undo)
SQL> DELETE FROM emp_info WHERE dept = 'HR';
1 row deleted.
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM emp_info ORDER BY emp_id;
EMP_ID EMP_NAME DEPT SALARY CITY
--------- ----------------- ----------- ------ ----------
1001 Rajesh Kumar IT 49500 Delhi
1002 Priya Singh HR 38000 Mumbai
1004 Sunita Roy IT 51700 Delhi
1005 Vikas Sharma Operations 33000 Chennai
4 rows selected.

Dept. of Computer Science & Engineering Page 12 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Conclusion: INSERT ALL, UPDATE (single + multi column), DELETE, COMMIT, ROLLBACK executed.
ROLLBACK restored the HR row.

Dept. of Computer Science & Engineering Page 13 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 4B

Aim To insert, delete, and modify records using DML commands on the Student table.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE stud_rec (

2 roll_no NUMBER(6) PRIMARY KEY,
2 name VARCHAR2(50) NOT NULL,
2 branch VARCHAR2(20),
2 marks NUMBER(5,2),
2 grade CHAR(1)
2 );
Table created.
SQL> INSERT INTO stud_rec VALUES (1,'Anjali Verma','CSE',87.5,'A');
1 row created.
SQL> INSERT INTO stud_rec VALUES (2,'Rohit Patel','IT',72.0,'B');
1 row created.
SQL> INSERT INTO stud_rec VALUES (3,'Meena Kumari','CSE',91.0,'A');
1 row created.
SQL> INSERT INTO stud_rec VALUES (4,'Suresh Garg','ECE',55.0,'C');
1 row created.
SQL> INSERT INTO stud_rec VALUES (5,'Deepak Arora','IT',64.0,'B');
1 row created.
SQL> COMMIT;
Commit complete.
-- Update grades based on marks
SQL> UPDATE stud_rec SET grade = 'A' WHERE marks >= 85;
2 rows updated.
SQL> UPDATE stud_rec SET grade = 'B' WHERE marks >= 70 AND marks < 85;
1 row updated.
SQL> UPDATE stud_rec SET grade = 'C' WHERE marks < 70;
2 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT roll_no, name, marks, grade FROM stud_rec;
ROLL_NO NAME MARKS G
---------- --------------------- ----- -
1 Anjali Verma 87.5 A
2 Rohit Patel 72.0 B
3 Meena Kumari 91.0 A
4 Suresh Garg 55.0 C
5 Deepak Arora 64.0 C
-- Delete students with marks < 60
SQL> DELETE FROM stud_rec WHERE marks < 60;
1 row deleted.
SQL> COMMIT;
Commit complete.
-- Re-insert with corrected marks
SQL> INSERT INTO stud_rec VALUES (4,'Suresh Garg','ECE',78.5,'B');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM stud_rec ORDER BY marks DESC;
ROLL_NO NAME BRANCH MARKS G
---------- --------------------- ------- ----- -
3 Meena Kumari CSE 91.0 A
1 Anjali Verma CSE 87.5 A
4 Suresh Garg ECE 78.5 B
2 Rohit Patel IT 72.0 B
5 Deepak Arora IT 64.0 C
5 rows selected.

Dept. of Computer Science & Engineering Page 14 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Conclusion: Grades updated conditionally. Low-scoring record deleted and replaced with corrected entry.

Dept. of Computer Science & Engineering Page 15 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 5A

Aim To modify table structure using ALTER – Add, Modify, Drop and Rename columns.

Theory
ALTER TABLE modifies existing table structure without data loss. Supports ADD, MODIFY, DROP COLUMN, RENAME
COLUMN.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE item (

2 item_id NUMBER(5) PRIMARY KEY,
2 item_name VARCHAR2(40) NOT NULL,
2 price NUMBER(8,2)
2 );
Table created.
SQL> INSERT INTO item VALUES (1,'Pen',15); INSERT INTO item VALUES (2,'Notebook',80);
2 rows created.
SQL> COMMIT;
Commit complete.
SQL> DESC item;
Name Null? Type
------------------------ -------- ---------------
ITEM_ID NOT NULL NUMBER(5)
ITEM_NAME NOT NULL VARCHAR2(40)
PRICE NUMBER(8,2)
-- ADD new columns
SQL> ALTER TABLE item ADD (category VARCHAR2(30));
Table altered.
SQL> ALTER TABLE item ADD (discount NUMBER(5,2) DEFAULT 0);
Table altered.
-- MODIFY column size
SQL> ALTER TABLE item MODIFY (item_name VARCHAR2(60));
Table altered.
SQL> ALTER TABLE item MODIFY (price NUMBER(10,2));
Table altered.
-- Set category before adding NOT NULL
SQL> UPDATE item SET category='Stationery' WHERE category IS NULL;
2 rows updated.
SQL> ALTER TABLE item MODIFY (category VARCHAR2(30) NOT NULL);
Table altered.
SQL> COMMIT;
Commit complete.
-- DROP a column
SQL> ALTER TABLE item DROP COLUMN discount;
Table altered.
-- RENAME a column
SQL> ALTER TABLE item RENAME COLUMN price TO unit_price;
Table altered.
SQL> DESC item;
Name Null? Type
------------------------ -------- ---------------
ITEM_ID NOT NULL NUMBER(5)
ITEM_NAME NOT NULL VARCHAR2(60)
CATEGORY NOT NULL VARCHAR2(30)
UNIT_PRICE NUMBER(10,2)

Conclusion: ALTER TABLE used to ADD, MODIFY, DROP, RENAME columns. Each change verified using
DESC.

Dept. of Computer Science & Engineering Page 16 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 5B

Aim To rename a table and add/drop constraints on existing tables using ALTER.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE supplier (

2 sup_id NUMBER(5), sup_name VARCHAR2(50),
2 contact NUMBER(10), city VARCHAR2(30)
2 );
Table created.
SQL> ALTER TABLE supplier ADD CONSTRAINT pk_sup PRIMARY KEY (sup_id);
Table altered.
SQL> ALTER TABLE supplier ADD CONSTRAINT uq_contact UNIQUE (contact);
Table altered.
SQL> ALTER TABLE supplier MODIFY (sup_name VARCHAR2(50) NOT NULL);
Table altered.
SQL> ALTER TABLE supplier ADD CONSTRAINT chk_city
2 CHECK (city IN ('Delhi','Mumbai','Pune','Chennai','Hisar'));
Table altered.
SQL> SELECT constraint_name, constraint_type FROM user_constraints
2 WHERE table_name = 'SUPPLIER';
CONSTRAINT_NAME C
--------------------- -
PK_SUP P
UQ_CONTACT U
SYS_C0XXXXX C
CHK_CITY C
4 rows selected.
-- DROP a constraint
SQL> ALTER TABLE supplier DROP CONSTRAINT chk_city;
Table altered.
-- RENAME TABLE
SQL> RENAME supplier TO vendor;
Table renamed.
SQL> SELECT constraint_name, constraint_type FROM user_constraints
2 WHERE table_name = 'VENDOR';
CONSTRAINT_NAME C
--------------------- -
PK_SUP P
UQ_CONTACT U
SYS_C0XXXXX C
3 rows selected.

Conclusion: Constraints added/dropped via ALTER TABLE. Table renamed using RENAME. Verified through
user_constraints.

Dept. of Computer Science & Engineering Page 17 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 6A

To explore SELECT with WHERE, ORDER BY, GROUP BY, HAVING, and
Aim Aggregate Functions.

Theory
Aggregate functions: COUNT(*) total rows, SUM/AVG/MAX/MIN on column values. GROUP BY groups rows; HAVING
filters groups (unlike WHERE which filters rows).

Execution
Oracle SQL*Plus

-- WHERE with AND, LIKE, BETWEEN

SQL> SELECT emp_id, first_name, salary FROM employee WHERE salary > 60000;
EMP_ID FIRST_NAME SALARY
---------- ------------------------- ------
101 Ravi 65000
102 Neha 72000
2 rows selected.
SQL> SELECT first_name, salary FROM employee WHERE salary BETWEEN 50000 AND 75000;
FIRST_NAME SALARY
------------------------- ------
Ravi 65000
Neha 72000
Amit 58000
3 rows selected.
-- ORDER BY descending
SQL> SELECT first_name, salary FROM employee ORDER BY salary DESC;
FIRST_NAME SALARY
------------------------- ------
Neha 72000
Ravi 65000
Amit 58000
-- Aggregate functions
SQL> SELECT COUNT(*) total_emp, SUM(salary) total_sal,
2 AVG(salary) avg_sal, MAX(salary) highest, MIN(salary) lowest
2 FROM employee;
TOTAL_EMP TOTAL_SAL AVG_SAL HIGHEST LOWEST
--------- ---------- --------- ------- -------
3 195000 65000 72000 58000
-- GROUP BY
SQL> SELECT dept_id, COUNT(*) emp_count, AVG(salary) avg_sal
2 FROM employee GROUP BY dept_id;
DEPT_ID EMP_COUNT AVG_SAL
---------- --------- ---------
10 1 65000
20 1 72000
30 1 58000
-- HAVING: departments where avg salary > 60000
SQL> SELECT dept_id, AVG(salary) avg_sal
2 FROM employee GROUP BY dept_id HAVING AVG(salary) > 60000;
DEPT_ID AVG_SAL
---------- ---------
10 65000
20 72000
2 rows selected.

Conclusion: WHERE, ORDER BY, GROUP BY, HAVING and aggregate functions COUNT, SUM, AVG,
MAX, MIN demonstrated.

Dept. of Computer Science & Engineering Page 18 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 6B

Aim Advanced SELECT: NVL, DECODE, CASE WHEN, String & Date Functions.

Execution
Oracle SQL*Plus

-- NVL: replace NULL with default value

SQL> SELECT emp_id, first_name, NVL(TO_CHAR(salary),'Not Assigned') sal
2 FROM employee;
EMP_ID FIRST_NAME SAL
---------- --------------- ----------
101 Ravi 65000
102 Neha 72000
103 Amit 58000
-- DECODE: Oracle CASE equivalent
SQL> SELECT emp_id,
2 DECODE(dept_id,10,'Engineering',20,'HR',30,'Finance','Other') dept
2 FROM employee;
EMP_ID DEPT
---------- -----------
101 Engineering
102 HR
103 Finance
-- CASE WHEN: salary bands
SQL> SELECT first_name, salary,
2 CASE WHEN salary > 70000 THEN 'High'
2 WHEN salary > 50000 THEN 'Medium'
2 ELSE 'Low' END AS salary_band
2 FROM employee;
FIRST_NAME SALARY SALARY_B
--------------- ------ --------
Ravi 65000 Medium
Neha 72000 High
Amit 58000 Medium
-- String functions: UPPER, LOWER, SUBSTR, LENGTH
SQL> SELECT UPPER(first_name), LOWER(last_name),
2 SUBSTR(first_name,1,3), LENGTH(email) FROM employee;
UPPER(FIRS LOWER(LAST SUB LENGTH(EMAIL)
---------- ---------- --- -------------
RAVI sharma Rav 12
NEHA gupta Neh 12
AMIT verma Ami 12
-- Date function: MONTHS_BETWEEN
SQL> SELECT emp_id, ROUND(MONTHS_BETWEEN(SYSDATE,hire_date),1) months
2 FROM employee;
EMP_ID MONTHS
---------- ----------
101 14.3
102 14.3
103 14.3
-- ROWNUM: top 2 earners
SQL> SELECT * FROM (SELECT * FROM employee ORDER BY salary DESC)
2 WHERE ROWNUM <= 2;
EMP_ID FIRST_NAME SALARY
---------- --------------- ------
102 Neha 72000
101 Ravi 65000

Conclusion: NVL, DECODE, CASE WHEN, UPPER, LOWER, SUBSTR, MONTHS_BETWEEN, ROWNUM
all demonstrated.

Dept. of Computer Science & Engineering Page 19 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 7A

Aim To use Set Operations – UNION, INTERSECT, and MINUS – to query tables.

Theory
Set operations combine two SELECT results with the same columns. UNION: unique rows. INTERSECT: common rows.
MINUS: rows in first but not second.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE registered_students

2 (roll NUMBER(5), name VARCHAR2(40), course VARCHAR2(20));
Table created.
SQL> CREATE TABLE eligible_students
2 (roll NUMBER(5), name VARCHAR2(40), course VARCHAR2(20));
Table created.
SQL> INSERT INTO registered_students VALUES (101,'Aman','CSE');
1 row created.
SQL> INSERT INTO registered_students VALUES (102,'Bina','IT');
1 row created.
SQL> INSERT INTO registered_students VALUES (103,'Charu','CSE');
1 row created.
SQL> INSERT INTO registered_students VALUES (104,'Dev','ECE');
1 row created.
SQL> INSERT INTO eligible_students VALUES (102,'Bina','IT');
1 row created.
SQL> INSERT INTO eligible_students VALUES (103,'Charu','CSE');
1 row created.
SQL> INSERT INTO eligible_students VALUES (105,'Ekta','CSE');
1 row created.
SQL> COMMIT;
Commit complete.
-- UNION: all unique students
SQL> SELECT roll,name,course FROM registered_students
2 UNION
2 SELECT roll,name,course FROM eligible_students;
ROLL NAME COURSE
---------- -------------------- --------------------
101 Aman CSE
102 Bina IT
103 Charu CSE
104 Dev ECE
105 Ekta CSE
5 rows selected.
-- INTERSECT: students in BOTH tables
SQL> SELECT roll,name,course FROM registered_students
2 INTERSECT
2 SELECT roll,name,course FROM eligible_students;
ROLL NAME COURSE
---------- -------------------- --------------------
102 Bina IT
103 Charu CSE
2 rows selected.
-- MINUS: registered but NOT eligible
SQL> SELECT roll,name,course FROM registered_students
2 MINUS
2 SELECT roll,name,course FROM eligible_students;
ROLL NAME COURSE
---------- -------------------- --------------------
101 Aman CSE
104 Dev ECE
2 rows selected.

Dept. of Computer Science & Engineering Page 20 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Conclusion: UNION (5 unique), INTERSECT (2 common), MINUS (2 exclusive) all demonstrated.

Dept. of Computer Science & Engineering Page 21 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 7B

Aim To demonstrate UNION ALL and practical set operation scenarios on sales data.

Execution
Oracle SQL*Plus

SQL> CREATE TABLE delhi_sales

2 (prod_id NUMBER(5), prod_name VARCHAR2(40), qty_sold NUMBER(5));
Table created.
SQL> CREATE TABLE mumbai_sales
2 (prod_id NUMBER(5), prod_name VARCHAR2(40), qty_sold NUMBER(5));
Table created.
SQL> INSERT INTO delhi_sales VALUES (1,'Laptop',10);
1 row created.
SQL> INSERT INTO delhi_sales VALUES (2,'Tablet',25);
1 row created.
SQL> INSERT INTO delhi_sales VALUES (3,'Phone',40);
1 row created.
SQL> INSERT INTO mumbai_sales VALUES (2,'Tablet',18);
1 row created.
SQL> INSERT INTO mumbai_sales VALUES (3,'Phone',30);
1 row created.
SQL> INSERT INTO mumbai_sales VALUES (4,'Smartwatch',15);
1 row created.
SQL> COMMIT;
Commit complete.
-- UNION ALL: all rows with branch label (duplicates kept)
SQL> SELECT prod_id, prod_name, qty_sold, 'Delhi' AS branch FROM delhi_sales
2 UNION ALL
2 SELECT prod_id, prod_name, qty_sold, 'Mumbai' AS branch FROM mumbai_sales;
PROD_ID PROD_NAME QTY_SOLD BRANCH
---------- ------------ -------- ----------
1 Laptop 10 Delhi
2 Tablet 25 Delhi
3 Phone 40 Delhi
2 Tablet 18 Mumbai
3 Phone 30 Mumbai
4 Smartwatch 15 Mumbai
6 rows selected.
-- Total qty per product across both branches
SQL> SELECT prod_id, prod_name, SUM(qty_sold) AS total_qty FROM (
2 SELECT prod_id, prod_name, qty_sold FROM delhi_sales
2 UNION ALL
2 SELECT prod_id, prod_name, qty_sold FROM mumbai_sales
2 ) GROUP BY prod_id, prod_name ORDER BY total_qty DESC;
PROD_ID PROD_NAME TOTAL_QTY
---------- ------------ ---------
3 Phone 70
2 Tablet 43
4 Smartwatch 15
1 Laptop 10
4 rows selected.

Conclusion: UNION ALL kept duplicates. Nested UNION ALL with GROUP BY computed total sales per
product across both branches.

Dept. of Computer Science & Engineering Page 22 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 8A

To create and execute INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and
Aim SELF JOINs.

Theory
JOIN combines rows from two tables on a related column. INNER: matching only. LEFT/RIGHT/FULL OUTER: includes
unmatched rows with NULLs. SELF: table joined with itself.

Execution
Oracle SQL*Plus

-- INNER JOIN
SQL> SELECT e.emp_id, e.first_name, [Link], d.dept_name
2 FROM employee e INNER JOIN department d ON e.dept_id = d.dept_id;
EMP_ID FIRST_NAME SALARY DEPT_NAME
---------- --------------- ------- ---------------
101 Ravi 65000 Engineering
102 Neha 72000 HR
103 Amit 58000 Finance
3 rows selected.
-- LEFT OUTER JOIN: all employees, NULL dept if unmatched
SQL> SELECT e.emp_id, e.first_name, d.dept_name
2 FROM employee e LEFT JOIN department d ON e.dept_id = d.dept_id;
EMP_ID FIRST_NAME DEPT_NAME
---------- --------------- ---------------
101 Ravi Engineering
102 Neha HR
103 Amit Finance
104 Raj (null)
4 rows selected.
-- RIGHT OUTER JOIN: all departments, NULL emp if unmatched
SQL> SELECT e.first_name, d.dept_id, d.dept_name
2 FROM employee e RIGHT JOIN department d ON e.dept_id = d.dept_id;
FIRST_NAME DEPT_ID DEPT_NAME
--------------- ------- ---------------
Ravi 10 Engineering
Neha 20 HR
Amit 30 Finance
(null) 40 Legal
4 rows selected.
-- FULL OUTER JOIN
SQL> SELECT e.emp_id, e.first_name, d.dept_id, d.dept_name
2 FROM employee e FULL OUTER JOIN department d ON e.dept_id = d.dept_id;
EMP_ID FIRST_NAME DEPT_ID DEPT_NAME
---------- -------------- ------- ---------------
101 Ravi 10 Engineering
102 Neha 20 HR
103 Amit 30 Finance
104 Raj (null) (null)
(null) (null) 40 Legal
5 rows selected.
-- SELF JOIN: employee with their manager name
SQL> SELECT e.first_name AS employee, m.first_name AS manager
2 FROM employee e LEFT JOIN employee m ON e.dept_id = m.emp_id;
EMPLOYEE MANAGER
--------------- ---------------
Ravi (null)
Neha (null)
Amit (null)

Dept. of Computer Science & Engineering Page 23 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Conclusion: INNER, LEFT, RIGHT, FULL OUTER, and SELF JOINs executed. NULL values appeared
correctly for unmatched rows in each join type.

Dept. of Computer Science & Engineering Page 24 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 8B

Aim To create, query, update, and drop VIEWs on existing database tables.

Theory
A VIEW is a virtual table based on a SELECT query stored in the database. Simple views can be updated. Complex
views (JOINs, aggregates) are read-only.

Execution

Dept. of Computer Science & Engineering Page 25 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Oracle SQL*Plus

-- Simple view
SQL> CREATE VIEW emp_view AS
2 SELECT emp_id, first_name, last_name, dept_id FROM employee;
View created.
SQL> SELECT * FROM emp_view;
EMP_ID FIRST_NAME LAST_NAME DEPT_ID
---------- --------------- --------------- -------
101 Ravi Sharma 10
102 Neha Gupta 20
103 Amit Verma 30
-- Filtered view (row-level security)
SQL> CREATE VIEW it_employees AS
2 SELECT emp_id, first_name, salary FROM employee
2 WHERE dept_id = 10 WITH CHECK OPTION;
View created.
SQL> SELECT * FROM it_employees;
EMP_ID FIRST_NAME SALARY
---------- -------------------- ------
101 Ravi 65000
1 row selected.
-- Multi-table JOIN view
SQL> CREATE VIEW emp_dept_view AS
2 SELECT e.emp_id, e.first_name, [Link], d.dept_name, [Link]
2 FROM employee e JOIN department d ON e.dept_id = d.dept_id;
View created.
SQL> SELECT * FROM emp_dept_view;
EMP_ID FIRST_NAME SALARY DEPT_NAME LOCATION
---------- --------------- ------ --------------- ---------
101 Ravi 65000 Engineering Delhi
102 Neha 72000 HR Mumbai
103 Amit 58000 Finance Bangalore
-- Aggregate view
SQL> CREATE VIEW dept_summary AS
2 SELECT d.dept_name, COUNT(e.emp_id) emp_count, AVG([Link]) avg_salary
2 FROM employee e JOIN department d ON e.dept_id=d.dept_id
2 GROUP BY d.dept_name;
View created.
SQL> SELECT * FROM dept_summary ORDER BY avg_salary DESC;
DEPT_NAME EMP_COUNT AVG_SALARY
--------------- --------- ----------
HR 1 72000
Engineering 1 65000
Finance 1 58000
-- Update base table through simple view
SQL> UPDATE emp_view SET dept_id = 30 WHERE emp_id = 101;
1 row updated.
SQL> COMMIT;
Commit complete.
-- Drop a view
SQL> DROP VIEW it_employees;
View dropped.
SQL> SELECT view_name FROM user_views;
VIEW_NAME
------------------------------
EMP_VIEW
EMP_DEPT_VIEW
DEPT_SUMMARY

Conclusion: Simple, filtered, JOIN, and aggregate views created, queried, updated and dropped.

Dept. of Computer Science & Engineering Page 26 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Experiment No. Experiment 9

Aim To write Single Row, Multi-Row, Correlated, Scalar, and Inline View Sub-Queries.

Theory
Sub-query: a SELECT within another SQL statement. Types – Single-Row (=, >, <), Multi-Row (IN, ANY, ALL),
Correlated (references outer query), Scalar (single value in SELECT), Inline View (sub-query in FROM).

Execution

Dept. of Computer Science & Engineering Page 27 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Oracle SQL*Plus

-- A. Single-Row: employees earning more than Amit Verma

SQL> SELECT emp_id, first_name, salary FROM employee
2 WHERE salary > (SELECT salary FROM employee
2 WHERE first_name='Amit' AND last_name='Verma');
EMP_ID FIRST_NAME SALARY
---------- -------------------- ------
101 Ravi 65000
102 Neha 72000
2 rows selected.
-- Single-Row: employee with maximum salary
SQL> SELECT emp_id, first_name, salary FROM employee
2 WHERE salary = (SELECT MAX(salary) FROM employee);
EMP_ID FIRST_NAME SALARY
---------- -------------------- ------
102 Neha 72000
1 row selected.
-- B. Multi-Row IN: employees in Delhi departments
SQL> SELECT emp_id, first_name, dept_id FROM employee
2 WHERE dept_id IN (SELECT dept_id FROM department WHERE location='Delhi');
EMP_ID FIRST_NAME DEPT_ID
---------- -------------------- -------
101 Ravi 10
1 row selected.
-- Multi-Row ALL: salary > ALL Finance employees (> 58000)
SQL> SELECT first_name, salary FROM employee
2 WHERE salary > ALL (SELECT salary FROM employee WHERE dept_id=30);
FIRST_NAME SALARY
-------------------- ------
Ravi 65000
Neha 72000
2 rows selected.
-- C. Correlated: EXISTS – departments with at least one employee
SQL> SELECT dept_id, dept_name FROM department d
2 WHERE EXISTS (SELECT 1 FROM employee e WHERE e.dept_id = d.dept_id);
DEPT_ID DEPT_NAME
---------- ---------------
10 Engineering
20 HR
30 Finance
3 rows selected.
-- D. Scalar: compare salary with company average
SQL> SELECT first_name, salary,
2 (SELECT AVG(salary) FROM employee) company_avg,
2 salary-(SELECT AVG(salary) FROM employee) diff
2 FROM employee;
FIRST_NAME SALARY COMPANY_AVG DIFF
--------------- ------ ----------- ----------
Ravi 65000 65000 0
Neha 72000 65000 7000
Amit 58000 65000 -7000
-- E. Inline View: dept summary sub-query in FROM clause
SQL> SELECT dept_id, dept_name, total_emp, avg_sal FROM (
2 SELECT d.dept_id, d.dept_name,
2 COUNT(e.emp_id) total_emp, AVG([Link]) avg_sal
2 FROM department d LEFT JOIN employee e ON d.dept_id=e.dept_id
2 GROUP BY d.dept_id, d.dept_name
2 ) WHERE total_emp > 0;
DEPT_ID DEPT_NAME TOTAL_EMP AVG_SAL
---------- --------------- ---------- ----------
10 Engineering 1 65000
20 HR 1 72000
30 Finance 1 58000
3 rows selected.

Dept. of Computer Science & Engineering Page 28 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

Conclusion: Single-Row, Multi-Row (IN, ALL), Correlated (EXISTS), Scalar, and Inline View sub-queries all
executed with authentic SQL*Plus output format.

Dept. of Computer Science & Engineering Page 29 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

VIVA VOCE – Important Questions

Q1. Difference between DDL and DML?

DDL (CREATE/ALTER/DROP/TRUNCATE) defines structure; auto-commits. DML

Ans. (INSERT/UPDATE/DELETE) manipulates data; can be rolled back.

Q2. DELETE vs TRUNCATE vs DROP?

DELETE: removes specific rows, rollback possible. TRUNCATE: removes all rows fast, no rollback,
Ans. resets HWM. DROP: removes table with structure and data permanently.

Q3. PRIMARY KEY vs UNIQUE KEY?

PRIMARY KEY: unique + NOT NULL, only one per table. UNIQUE: allows one NULL, multiple
Ans. UNIQUE constraints allowed per table.

Q4. What is referential integrity?

Ans. FK must match an existing PK in parent table. Oracle raises ORA-02291 on violation.

Q5. INNER JOIN vs OUTER JOIN?

INNER: only matching rows. OUTER (LEFT/RIGHT/FULL): all rows from one or both sides with
Ans. NULLs for non-matching rows.

Q6. Can a VIEW be updated?

Simple views (single table, no GROUP BY/DISTINCT/aggregate) can be updated. Complex views
Ans. are read-only.

Q7. What is a Correlated Sub-Query?

Inner query references outer query's column; executes once per outer row, unlike a regular
Ans. sub-query which executes only once.

Q8. WHERE vs HAVING?

WHERE filters rows before grouping (cannot use aggregates). HAVING filters groups after GROUP
Ans. BY (can use aggregates).

Q9. UNION vs UNION ALL?

UNION removes duplicate rows (slower due to sort). UNION ALL keeps all rows including duplicates
Ans. (faster).

Q10. What is an Oracle Sequence?

A database object generating unique integer values automatically. Used for PK generation. Access:
Ans. [Link] (next) / [Link] (current).

Dept. of Computer Science & Engineering Page 30 of 31

MCA-16 | Database Management System Lab GJU S&T, Hisar

MCA-16
Database Management System Lab

Guru Jambheshwar University of Science & Technology

Dept. of CSE | Hisar – 125001, Haryana

Dept. of Computer Science & Engineering Page 31 of 31