DBMS Lab Manual

-- Creating Table 1: Departments
CREATE TABLE departments (
    dept_id NUMBER(4) PRIMARY KEY,
    dept_name VARCHAR2(50) NOT NULL,
    location VARCHAR2(50)
);
 
-- Creating Table 2: Employees
CREATE TABLE employees (
    emp_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(30),
    last_name VARCHAR2(30) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(8, 2),
    dept_id NUMBER(4),
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
 
-- Creating Table 3: Projects
CREATE TABLE projects (
    proj_id NUMBER(5) PRIMARY KEY,
    proj_name VARCHAR2(100) NOT NULL,
    budget NUMBER(12, 2),
    start_date DATE
);
 
-- Creating Table 4: Clients
CREATE TABLE clients (
    client_id NUMBER(5) PRIMARY KEY,
    client_name VARCHAR2(100) NOT NULL,
    contact_email VARCHAR2(100) UNIQUE,
    phone_no CHAR(10)
);
 
-- Creating Table 5: Assignments
CREATE TABLE assignments (
    emp_id NUMBER(6),
    proj_id NUMBER(5),
    role VARCHAR2(30),
    hours_allocated NUMBER(4, 1),
    PRIMARY KEY (emp_id, proj_id),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
    FOREIGN KEY (proj_id) REFERENCES projects(proj_id)
);
 
-- ALTER Commands: Modify, Add, and Rename columns
ALTER TABLE employees ADD (email VARCHAR2(100));
ALTER TABLE employees MODIFY (salary NUMBER(10, 2));
ALTER TABLE clients RENAME COLUMN phone_no TO mobile_no;
 
-- TRUNCATE Command
TRUNCATE TABLE assignments;
 
-- DROP Command
DROP TABLE clients;

-- Method 1: Standard Insert specifying column list
INSERT INTO departments (dept_id, dept_name, location) VALUES (10, 'IT', 'New York');
INSERT INTO departments (dept_id, dept_name, location) VALUES (20, 'HR', 'Chicago');
INSERT INTO departments (dept_id, dept_name, location) VALUES (30, 'Finance', 'Boston');
 
-- Method 2: Standard Insert without column list (all columns)
INSERT INTO employees VALUES (101, 'John', 'Doe', TO_DATE('2022-01-15', 'YYYY-MM-DD'), 60000.00, 10, 'john.doe@company.com');
INSERT INTO employees VALUES (102, 'Jane', 'Smith', TO_DATE('2021-03-20', 'YYYY-MM-DD'), 55000.00, 10, 'jane.smith@company.com');
INSERT INTO employees VALUES (103, 'Robert', 'Johnson', TO_DATE('2020-07-10', 'YYYY-MM-DD'), 70000.00, 20, 'robert.j@company.com');
 
-- Method 3: Inserting rows with NULL values
INSERT INTO employees (emp_id, first_name, last_name, hire_date, salary, dept_id)
VALUES (104, 'Emily', 'Davis', SYSDATE, 50000.00, 20);
 
-- Method 4: Multi-row insert using INSERT ALL
INSERT ALL
    INTO employees (emp_id, first_name, last_name, hire_date, salary, dept_id, email) VALUES (105, 'Michael', 'Brown', SYSDATE, 48000.00, 30, 'mbrown@company.com')
    INTO employees (emp_id, first_name, last_name, hire_date, salary, dept_id, email) VALUES (106, 'Sarah', 'Wilson', SYSDATE, 65000.00, 10, 'swilson@company.com')
    INTO employees (emp_id, first_name, last_name, hire_date, salary, dept_id, email) VALUES (107, 'David', 'Taylor', SYSDATE, 52000.00, 30, 'dtaylor@company.com')
    INTO employees (emp_id, first_name, last_name, hire_date, salary, dept_id, email) VALUES (108, 'Laura', 'Anderson', SYSDATE, 58000.00, 20, 'landerson@company.com')
    INTO employees (emp_id, first_name, last_name, hire_date, salary, dept_id, email) VALUES (109, 'James', 'Thomas', SYSDATE, 72000.00, 10, 'jthomas@company.com')
    INTO employees (emp_id, first_name, last_name, hire_date, salary, dept_id, email) VALUES (110, 'Emma', 'White', SYSDATE, 45000.00, 30, 'ewhite@company.com')
SELECT * FROM dual;
 
-- UPDATE Commands
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 10;
UPDATE employees SET email = 'emily.davis@company.com' WHERE emp_id = 104;
 
-- DELETE Commands
DELETE FROM employees WHERE emp_id = 110;
 

-- 1. Simple SELECT and Relational Operators
SELECT emp_id, first_name, last_name, salary FROM employees WHERE salary > 60000;
 
-- 2. Logical Operators (AND, OR, NOT)
SELECT emp_id, first_name, last_name, dept_id, salary
FROM employees
WHERE dept_id = 10 AND salary >= 65000;
 
SELECT emp_id, first_name, last_name, dept_id
FROM employees
WHERE dept_id = 20 OR dept_id = 30;
 
-- 3. BETWEEN ... AND Operator
SELECT emp_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 65000;
 
-- 4. IN Operator
SELECT emp_id, first_name, last_name, dept_id
FROM employees
WHERE dept_id IN (10, 30);
 
-- 5. LIKE Operator (Pattern Matching)
SELECT emp_id, first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';
 
SELECT emp_id, first_name, last_name
FROM employees
WHERE first_name LIKE '_a%';
 
-- 6. IS NULL / IS NOT NULL Operators
SELECT emp_id, first_name, last_name, email
FROM employees
WHERE email IS NULL;
 
SELECT emp_id, first_name, last_name, email
FROM employees
WHERE email IS NOT NULL;

-- 1. ADD_MONTHS
SELECT SYSDATE AS current_date,
       ADD_MONTHS(SYSDATE, 6) AS after_6_months
FROM dual;
 
-- 2. MONTHS_BETWEEN
SELECT SYSDATE AS current_date,
       ADD_MONTHS(SYSDATE, 6) AS future_date,
       MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, 6), SYSDATE) AS months_between
FROM dual;
 
-- 3. ROUND
SELECT SYSDATE AS current_date,
       ROUND(SYSDATE, 'YEAR') AS rounded_year
FROM dual;
 
-- 4. NEXT_DAY
SELECT SYSDATE AS current_date,
       NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday
FROM dual;
 
-- 5. TRUNC
SELECT SYSDATE AS current_date,
       TRUNC(SYSDATE, 'YEAR') AS year_start
FROM dual;
 
-- 6. GREATEST
SELECT GREATEST(SYSDATE, ADD_MONTHS(SYSDATE, 6)) AS greatest_date
FROM dual;
 
-- 7. NEW_TIME
SELECT SYSDATE AS GMT_TIME,
       NEW_TIME(SYSDATE, 'GMT', 'IST') AS IST_TIME
FROM dual;

-- Comprehensive execution of Oracle SQL Numeric Functions
SELECT
    ABS(-45.75) AS abs_val,
    CEIL(14.2) AS ceil_val,
    FLOOR(14.8) AS floor_val,
    COS(0) AS cos_val,
    COSH(0) AS cosh_val,
    EXP(2) AS exp_val,
    POWER(2, 5) AS power_val,
    MOD(17, 5) AS mod_val,
    SQRT(64) AS sqrt_val
FROM DUAL;
 
-- Demonstration of ROUND and TRUNC on numeric values
SELECT
    ROUND(125.678, 2) AS round_2dec,
    ROUND(125.678, -1) AS round_tens,
    TRUNC(125.678, 2) AS trunc_2dec,
    TRUNC(125.678, 0) AS trunc_zero
FROM DUAL;

-- String Case Conversion Functions
SELECT
    INITCAP('silver oak university') AS initcap_str,
    LOWER('SILVER OAK UNIVERSITY') AS lower_str,
    UPPER('silver oak university') AS upper_str
FROM DUAL;
 
-- Trimming and Padding Functions
SELECT
    LTRIM('   Oracle', ' ') AS ltrim_str,
    RTRIM('Database***', '*') AS rtrim_str,
    LPAD('SQL', 8, '*') AS lpad_str,
    RPAD('SQL', 8, '#') AS rpad_str
FROM DUAL;
 
-- String Substring, Translation, and Replacement Functions
SELECT
    SUBSTR('Silver Oak University', 1, 6) AS substr_str,
    REPLACE('Welcome to Java', 'Java', 'Oracle SQL') AS replace_str,
    TRANSLATE('12345 Hello', '12345', 'ABCDE') AS translate_str,
    LENGTH('Oracle Database') AS str_len
FROM DUAL;

-- 1. Using Aggregate Group Functions on EMPLOYEES table
SELECT
    COUNT(*) AS total_employees,
    COUNT(email) AS emp_with_email,
    SUM(salary) AS total_salary_expense,
    AVG(salary) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees;
 
-- 2. Combining Group Functions with DECODE conditional evaluation
SELECT
    dept_id,
    COUNT(*) AS dept_count,
    SUM(DECODE(dept_id, 10, salary, 0)) AS it_salary_total,
    SUM(DECODE(dept_id, 20, salary, 0)) AS hr_salary_total,
    SUM(DECODE(dept_id, 30, salary, 0)) AS fin_salary_total
FROM employees
GROUP BY dept_id;

-- 1. GROUP BY
SELECT dept_id, COUNT(*) AS employee_count
FROM employees
GROUP BY dept_id;
 
-- 2. HAVING
SELECT dept_id, MAX(salary) AS highest_salary
FROM employees
GROUP BY dept_id
HAVING MAX(salary) > 65000;
 
-- 3. ORDER BY
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
 
-- 4. GROUP BY with HAVING and ORDER BY
SELECT dept_id, AVG(salary) AS average_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 55000
ORDER BY average_salary DESC;

-- 1. UNION (Combines and eliminates duplicate rows)
SELECT emp_name FROM tech_team
UNION
SELECT emp_name FROM management_team;
 
-- 2. UNION ALL (Combines including duplicate rows)
SELECT emp_name FROM tech_team
UNION ALL
SELECT emp_name FROM management_team;
 
-- 3. INTERSECT (Returns common rows in both queries)
SELECT emp_name FROM tech_team
INTERSECT
SELECT emp_name FROM management_team;
 
-- 4. MINUS (Returns rows in first query but not in second query)
SELECT emp_name FROM tech_team
MINUS
SELECT emp_name FROM management_team;

-- 1. INNER JOIN (Retrieves records with matching values in both tables)
SELECT
    e.emp_id,
    e.first_name || ' ' || e.last_name AS employee_name,
    e.salary,
    d.dept_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
 
-- 2. CROSS JOIN (Generates Cartesian product of two tables)
SELECT
    d.dept_name,
    p.proj_name
FROM departments d
CROSS JOIN projects p;

-- 1. LEFT OUTER JOIN (Returns all rows from left table and matched rows from right)
SELECT
    e.emp_id,
    e.first_name,
    d.dept_id,
    d.dept_name
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;
 
-- 2. RIGHT OUTER JOIN (Returns all rows from right table and matched rows from left)
SELECT
    e.emp_id,
    e.first_name,
    d.dept_id,
    d.dept_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.dept_id = d.dept_id;
 
-- 3. FULL OUTER JOIN (Returns all rows when there is a match in left or right table)
SELECT
    e.emp_id,
    e.first_name,
    d.dept_id,
    d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;
 
-- 4. CROSS JOIN (Cartesian product)
SELECT
    e.first_name,
    d.dept_name
FROM (SELECT first_name FROM employees WHERE ROWNUM <= 2) e
CROSS JOIN departments d;

-- 1. Creating a Simple View
CREATE VIEW high_salary_emps AS
SELECT emp_id, first_name, last_name, salary, dept_id
FROM employees
WHERE salary >= 60000;
 
-- 2. Creating a Complex View using Join
CREATE VIEW emp_department_summary AS
SELECT
    e.emp_id,
    e.first_name || ' ' || e.last_name AS full_name,
    e.salary,
    d.dept_name,
    d.location
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
 
-- 3. Querying Views
SELECT * FROM high_salary_emps;
SELECT * FROM emp_department_summary WHERE location = 'New York';
 
-- 4. Dropping a View
DROP VIEW high_salary_emps;

View HIGH_SALARY_EMPS created.
View EMP_DEPARTMENT_SUMMARY created.
View HIGH_SALARY_EMPS dropped.

DECLARE
    num NUMBER := 24;
BEGIN
    IF MOD(num, 2) = 0 THEN
        DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is Even.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is Odd.');
    END IF;
END;

The number 24 is Even.

DECLARE
    num NUMBER := 17;
    i NUMBER := 2;
    flag NUMBER := 1;
BEGIN
    FOR i IN 2..num/2 LOOP
    IF MOD(num, i) = 0 THEN
        flag := 0;
        EXIT;
    END IF;
    IF flag = 1 THEN
        DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is Prime.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is NOT Prime.');
    END IF;
END;

The number 17 is Prime.

DECLARE
    num NUMBER := 45;
BEGIN
    IF MOD(num, 5) = 0 THEN
        DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is divisible by 5.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is NOT divisible by 5.');
    END IF;
END;

The number 45 is divisible by 5.

DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE('Printing numbers from 1 to 10:');
    FOR counter IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Number: ' || counter);
    END LOOP;
END;

Printing numbers from 1 to 10:
Number: 1
Number: 2
Number: 3
Number: 4
Number: 5
Number: 6
Number: 7
Number: 8
Number: 9
Number: 10

-- Using Conditional Logic
DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE('Even numbers series up to 50:');
    FOR num IN 1..50 LOOP
        IF MOD(num, 2) = 0 THEN
            DBMS_OUTPUT.PUT_LINE(num);
        END IF;
    END LOOP;
END;

Even numbers series up to 50:
2
4
6
8
.
.
.
46
48
50

DECLARE
    num NUMBER := 7;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Multiplication Table of ' || num || ':');
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(num || ' x ' || i || ' = ' || (num * i));
    END LOOP;
END;

Multiplication Table of 7:
7 x 1 = 7
7 x 2 = 14
7 x 3 = 21
7 x 4 = 28
7 x 5 = 35
7 x 6 = 42
7 x 7 = 49
7 x 8 = 56
7 x 9 = 63
7 x 10 = 70

DECLARE
    num NUMBER := 1;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Odd Numbers Series up to 50:');
    WHILE num <= 50 LOOP
        DBMS_OUTPUT.PUT_LINE(num);
        num := num + 2;
    END LOOP;
END;

Odd Numbers Series up to 50:
1
3
5
7
.
.
.
43
45
47
49

DECLARE
    i NUMBER := 1;
    total NUMBER := 0;
BEGIN
    WHILE i <= 100 LOOP
        total := total + i;
        i := i + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('The sum of numbers from 1 to 100 is: ' || total);
END;

The sum of numbers from 1 to 100 is: 5050

DECLARE
    first_term NUMBER := 0;
    second_term NUMBER := 1;
    next_term NUMBER;
    term_count NUMBER := 1;
    total_terms NUMBER := 10;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Fibonacci Series up to ' || total_terms || ' terms:');
    WHILE term_count <= total_terms LOOP
        DBMS_OUTPUT.PUT_LINE(first_term);
        next_term := first_term + second_term;
        first_term := second_term;
        second_term := next_term;
        term_count := term_count + 1;
    END LOOP;
END;

Fibonacci Series up to 10 terms:
0
1
1
2
3
5
8
13
21
34

-- Part 1: Implicit Cursor Example
DECLARE
    v_rows_affected NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary * 1.05
    WHERE dept_id = 10;
 
    IF SQL%FOUND THEN
        v_rows_affected := SQL%ROWCOUNT;
        DBMS_OUTPUT.PUT_LINE('Implicit Cursor: Successfully updated ' || v_rows_affected || ' rows.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Implicit Cursor: No rows were updated.');
    END IF;
END;
 
-- Part 2: Explicit Cursor Example
DECLARE
    CURSOR emp_cursor IS
        SELECT emp_id, first_name, last_name, salary FROM employees WHERE dept_id = 10;
 
    v_emp_id employees.emp_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('--- Explicit Cursor Processing ---');
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ' | Name: ' || v_first_name || ' ' || v_last_name || ' | Salary: $' || v_salary);
    END LOOP;
    CLOSE emp_cursor;
END;

Implicit Cursor: Successfully updated 4 rows.
 
--- Explicit Cursor Processing ---
ID: 101 | Name: John Doe | Salary: $69300
ID: 102 | Name: Jane Smith | Salary: $63525
ID: 106 | Name: Sarah Wilson | Salary: $75075
ID: 109 | Name: James Thomas | Salary: $83160

-- 1. Predefined Exception: ZERO_DIVIDE
DECLARE
    num_a NUMBER := 100;
    num_b NUMBER := 0;
    result NUMBER;
BEGIN
    result := num_a / num_b;
    DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Predefined Exception Caught: Division by zero is undefined.');
END;
 
-- 2. Predefined Exception: NO_DATA_FOUND
DECLARE
    v_emp_name employees.first_name%TYPE;
BEGIN
    SELECT first_name INTO v_emp_name FROM employees WHERE emp_id = 999;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Predefined Exception Caught: No employee found with ID 999.');
END;
 
-- 3. User-Defined Exception
DECLARE
    account_balance NUMBER := 500;
    withdraw_amount NUMBER := 1000;
    insufficient_funds EXCEPTION;
BEGIN
    IF withdraw_amount > account_balance THEN
        RAISE insufficient_funds;
    ELSE
        account_balance := account_balance - withdraw_amount;
        DBMS_OUTPUT.PUT_LINE('Withdrawal successful.');
    END IF;
EXCEPTION
    WHEN insufficient_funds THEN
        DBMS_OUTPUT.PUT_LINE('User-Defined Exception Caught: Insufficient funds in account balance.');
END;

Predefined Exception Caught: Division by zero is undefined.
 
Predefined Exception Caught: No employee found with ID 999.
 
User-Defined Exception Caught: Insufficient funds in account balance.