DBMS | CSH-214

exp1.2q3.sql

-- Create tables for the college database

BEGIN;


CREATE TABLE IF NOT EXISTS public.departments
(
    id integer NOT NULL,
    name character varying NOT NULL,
    hod character varying NOT NULL,
    hod_id integer NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.faculty
(
    id integer NOT NULL,
    dept_id integer,
    name character varying NOT NULL,
    salary numeric NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.students
(
    id integer NOT NULL,
    dob date NOT NULL,
    name character varying NOT NULL,
    dept_id integer,
    mentor integer,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.faculty
    ADD FOREIGN KEY (dept_id)
    REFERENCES public.departments (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.students
    ADD FOREIGN KEY (dept_id)
    REFERENCES public.departments (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.students
    ADD FOREIGN KEY (mentor)
    REFERENCES public.faculty (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;

-- Insert data

INSERT INTO departments
	(id, name, hod, hod_id)
VALUES 
	(0, 'CSE', 'Dr. Alan Turing', 0),
	(1, 'AIML', 'Dr. John McCarthy', 1),
	(2, 'ECE', 'Mr. Michael Faraday', 2),
	(3, 'BBA', 'Mr. Peter Drucker', 3),
	(4, 'BCA', 'Dr. Alan Turing', 0)
;

INSERT INTO faculty
	(id, dept_id, name, salary)
VALUES
	(0, 0, 'Dr. Alan Turing', 30000),
	(1, 1, 'Dr. John McCarthy', 15000),
	(2, 2, 'Mr. Michael Faraday', 32000),
	(3, 3, 'Mr. Peter Drucker', 23000),
	(4, 4, 'Mr. Ram Kumar', 18000),
	(5, 0, 'Dr. Abdur Rehman', 24300),
	(6, 1, 'Dr. Shital Purohit', 90000),
	(7, 2, 'Er. Kamlesh Gupta', 19000),
	(8, 3, 'Mr. Warren Buffet', 250),
	(9, 4, 'Er. Steve Wozniak', 100000)
;

INSERT INTO students
	(id, dob, name, dept_id, mentor)
VALUES 
	(0, '2003-02-15', 'Hemant Bhandari', 0, 0),
	(1, '2003-06-17', 'Kamlesh Kandari', 0, 5),
	(2, '2003-01-22', 'Gamlesh Gandari', 1, 6),
	(3, '2003-03-01', 'Damlesh Dandari', 1, 1),
	(4, '2003-09-04', 'Tamlesh Tandari', 2, 2),
	(5, '2002-01-19', 'Jamlesh Jandari', 3, 3),
	(6, '2001-11-20', 'Pamlesh Pandari', 3, 8),
	(7, '2004-01-01', 'Mamlesh Mandari', 4, 4)
;

exp1.2q4.sql

BEGIN;


CREATE TABLE IF NOT EXISTS public.branches
(
    ifsc character varying NOT NULL,
    city text NOT NULL,
    state text NOT NULL,
    PRIMARY KEY (ifsc)
);

CREATE TABLE IF NOT EXISTS public.deposit_types
(
    code character varying NOT NULL,
    name text NOT NULL,
    PRIMARY KEY (code)
);

CREATE TABLE IF NOT EXISTS public.customers
(
    account_number bigint NOT NULL,
    name text NOT NULL,
    deposit_type character varying NOT NULL,
    ifsc character varying NOT NULL,
    PRIMARY KEY (account_number)
);

ALTER TABLE IF EXISTS public.customers
    ADD FOREIGN KEY (deposit_type)
    REFERENCES public.deposit_types (code) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.customers
    ADD FOREIGN KEY (ifsc)
    REFERENCES public.branches (ifsc) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;

INSERT INTO branches
	(ifsc, city, state)
VALUES 
	('SBIN0012981', 'Jankipuram Extension', 'Uttar Pradesh'),
	('SBIN0031506', 'Lucknow', 'Uttar Pradesh'),
	('SBIN0070603', 'Lucknow', 'Uttar Pradesh'),
	('SBIN0051249', 'Aayakar Bhawan', 'Punjab'),
	('SBIN0007599', 'Abhun', 'Punjab'),
	('SBIN0051084', 'Ablu', 'Punjab'),
	('SBIN0006406', 'Ibrampur', 'Goa'),
	('SBIN0000513', 'Mapuca', 'Goa'),
	('SBIN0070705', 'Panaji', 'Goa'),
	('SBIN0071102', 'Mumbai', 'Maharashtra'),
	('SBIN0000539', 'Mumbai', 'Maharashtra'),
	('SBIN0051262', 'Mumbai', 'Maharashtra'),
	('SBIN0006794', 'Kolkata', 'West Bengal'),
	('SBIN0000050', 'Kolkata', 'West Bengal'),
	('SBIN0001971', 'Kolkata', 'West Bengal')
;

INSERT INTO deposit_types
	(code, name)
VALUES 
	('SVA', 'Savings Bank Account'),
	('CDA', 'Current Deposit Account'),
	('FDA', 'Fixed Deposit Account'),
	('RDA', 'Recurring Deposit, Account')
;

INSERT INTO customers
	(account_number, name, deposit_type, ifsc)
VALUES
	(12345678901, 'Hemant Bhandari', 'SVA', 'SBIN0012981'),
	(21345678911, 'Kamlesh Kandari', 'CDA', 'SBIN0031506'),
	(12312534755, 'Jamlesh Jandari', 'FDA', 'SBIN0070603'),
	(86556345223, 'Damlesh Dandari', 'RDA', 'SBIN0051249'),
	(93156475354, 'Tamlesh Tandari', 'SVA', 'SBIN0007599'),
	(48623678522, 'Mamlesh Mandari', 'CDA', 'SBIN0051084'),
	(37863463586, 'Gamlesh Gandari', 'FDA', 'SBIN0006406'),
	(90234426473, 'Pamlesh Pandari', 'RDA', 'SBIN0000513'),
	(78631678542, 'Namlesh Nandari', 'CDA', 'SBIN0070705'),
	(67531475423, 'Hemant Bhandari', 'SVA', 'SBIN0071102'),
	(51232524745, 'Kamlesh Kandari', 'CDA', 'SBIN0000539'),
	(32426454574, 'Jamlesh Jandari', 'FDA', 'SBIN0051262'),
	(90526315361, 'Damlesh Dandari', 'RDA', 'SBIN0006794'),
	(89324230623, 'Tamlesh Tandari', 'SVA', 'SBIN0000050'),
	(70322424234, 'Mamlesh Mandari', 'CDA', 'SBIN0001971')
;

exp1.3.sql

DBMS_OUTPUT.ENABLE(1000000); -- Using live oracle | postgres is a pain in the ass for now

-- q1
DECLARE
    n NUMBER := 45; -- given number
    s NUMBER := 0;  -- sum of digits
    n0 NUMBER := n;
BEGIN
    WHILE n != 0 LOOP
        s := s + MOD(n, 10);
        n := TRUNC(n / 10);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Sum of digits of ' || n0 || ' is = ' || s);
END; -- Hemant Bhandari | 21BCS3268 | Live SQL

-- q2
DECLARE
    a NUMBER := 50;
    b NUMBER := 20;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Original values:');
    DBMS_OUTPUT.PUT_LINE('a b');
    DBMS_OUTPUT.PUT_LINE(a || ' ' || b);
    
    a := a + b; --  = 70
    b := a - b; --  = 50
    a := a - b; --  = 20

    DBMS_OUTPUT.PUT_LINE('New values:');
    DBMS_OUTPUT.PUT_LINE('a b');
    DBMS_OUTPUT.PUT_LINE(a || ' ' || b);
END;

-- q3
DECLARE
    n NUMBER := 1234;
    n0 NUMBER := n;
    n1 NUMBER := 0;
BEGIN
    WHILE n != 0 LOOP
        n1 := n1 * 10 + MOD(n, 10);
        n := TRUNC(n / 10);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Reverse of ' || n0 || ' is ' || n1);
END;

-- q4
DECLARE
    greet VARCHAR2(50) := 'Hello!';
    name VARCHAR2(50) := 'Hemant Bhandari 21BCS3268';
    age NUMBER := 19;
BEGIN
    DBMS_OUTPUT.PUT_LINE(greet);
    DBMS_OUTPUT.PUT_LINE('I am ' || name);
    DBMS_OUTPUT.PUT_LINE('I am ' || age || ' years old');
END;

-- q5
BEGIN
    -- Hemant Bhandari | 21BCS3268
    
    /*The above comment is a single-line comment
      Unlike this comment. This comment is multi-line.
    */
    DBMS_OUTPUT.PUT_LINE('A plsql block must have at least on statement');
END;

-- q6
CREATE OR REPLACE PROCEDURE prnt_rect_area_param(l NUMBER, b NUMBER) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Area = ' || l * b);
    DBMS_OUTPUT.PUT_LINE('Parameter = ' || 2 * (l + b));
END;
/

BEGIN
    prnt_rect_area_param(20, 30);
END;

-- q7
CREATE OR REPLACE PROCEDURE prnt_circle_area(r NUMBER) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Area = ' || 22 * r * r / 7);
END;
/

BEGIN
    prnt_cricle_area(7);
END;

-- q8
CREATE OR REPLACE PROCEDURE is_even (x NUMBER) IS
BEGIN
    IF MOD(x, 2) = 0
    THEN
        DBMS_OUTPUT.PUT_LINE('EVEN');
    ELSE
        DBMS_OUTPUT.PUT_LINE('ODD');
    END IF;
END;
/

BEGIN
    is_even(7);
    is_even(98);
END;

exp2.3q6.sql

BEGIN
DBMS_OUTPUT.PUT_LINE('Departments At: ');
DBMS_OUTPUT.PUT_LINE('---------------');
DBMS_OUTPUT.PUT_LINE(rpad('Department', 35) || rpad('Manager', 15) || 'City');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(rpad('Accountig', 35) || rpad('John', 15) || 'Silicon Valley');
DBMS_OUTPUT.PUT_LINE(rpad('IT', 35) || rpad('Gosling', 15) || 'Silicon Valley');
DBMS_OUTPUT.PUT_LINE(rpad('Finance', 35) || rpad('Reynolds', 15) || 'Silicon Valley');
END;

oracledbcode.sql

create table emp (empno number(1), ename varchar2(50));
INSERT INTO emp VALUES (1, 'Hemant');
INSERT INTO emp VALUES (2, 'Shivam');
INSERT INTO emp VALUES (3, 'Shivain');
INSERT INTO emp VALUES (4, 'Kivain');
INSERT INTO emp VALUES (5, 'Trivain');

DECLARE 
    CURSOR c1 IS SELECT * FROM emp;
    v_empno emp.empno% Type;
    v_ename emp.ename% Type;
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO v_empno, v_ename;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
    END LOOP;
    CLOSE c1;
END;
        

I spent an embarrassingly long time on exp1.3.sql 😓️

create table emp (ename varchar2(50), hiredate varchar2(20));
INSERT INTO emp VALUES ('Hemant', '19-02-2001');
INSERT INTO emp VALUES ('Shivam', '20-09-2011');
INSERT INTO emp VALUES ('Shivain', '10-12-2004');
INSERT INTO emp VALUES ('Kivain', '30-10-2021');
INSERT INTO emp VALUES ('Trivain', '02-09-2011');