DBMS Programs

experiment1.sql

CREATE TABLE employee(empno int PRIMARY KEY,empname varchar(50),empaddress varchar(50),salary int,dept varchar(50));
		
		
INSERT into employee values(2001,"Hari","Palakkad",30000,"Research");
INSERT into employee values(2002,"Devi","Kasaragod",40000,"Network");
INSERT into employee values(2003,"Gupta","Kannur",50000,"System Admin");
INSERT into employee values(2004,"Ravi","Calicut",60000,"HR");
INSERT into employee values(2005,"Rani","Calicut",70000,"HR");
select * from employee;
select SUM(salary) as Total_Salary from employee;
select count(*) from employee where salary<50000;

select * from employee where salary=(select max(salary) from employee);

ALTER TABLE employee add(title varchar(50) DEFAULT("junior software engineer"));
select * from employee;

UPDATE employee set title="Senior software engineer" where empno = 2004;
select * from employee;

DELETE from employee where empno=2005;
select * from employee;


DROP table employee;

experiment2.sql

CREATE TABLE student(studentNo int PRIMARY KEY, studentName varchar(50), maths int, physics int, chemistry int, cProgramming int, department varchar(50), address varchar(50));
INSERT INTO student values(100,"Hari",50,60,45,75,"CSE","Kasaragod");
INSERT INTO student values(101,"Devi",60,55,78,40,"CSE","Kasaragod");
INSERT INTO student values(102,"Sam",45,77,88,45,"IT","Kannur");
INSERT INTO student values(103,"SreeHari",90,75,77,60,"IT","Calicut");
INSERT INTO student values(104,"Rani",91,98,89,52,"ECE","Kannur");
INSERT INTO student values(105,"Raj",88,77,67,48,"CSE","Palakkad");
select * from student;

select studentNo,studentName,cProgramming from student where(cProgramming<50);

select studentNo,studentName,department from student ORDER BY department DESC, studentName ASC;

select * from student where studentName LIKE "S%";

select studentName from student where cProgramming in
    (SELECT MAX(cProgramming) from student where cProgramming not in
        (select MAX(cProgramming) from student));

select COUNT(*), AVG(maths), department from student GROUP BY department;
select COUNT(*) from student where physics>70 AND physics<80;


DROP table student;

experiment3.sql

CREATE TABLE dept(deptNO int PRIMARY KEY,deptName varchar(50),deptLocation varchar(50));
INSERT into dept values(1001,"HR","Calicut");
INSERT into dept values(1002,"Marketing","Cochin");
INSERT into dept values(1003,"Sales","Trivandrum");
INSERT into dept values(1004,"Testing","Bangalore");
INSERT into dept values(1005,"Development","Bangalore");
select * from dept;

CREATE TABLE emp(empId int PRIMARY KEY,empName varchar(50),deptNo int,salary int);
INSERT into emp values(2001,"Hari",1006,30000);
INSERT into emp values(2002,"Devi",1002,40000);
INSERT into emp values(2003,"Gupta",1003,50000);
INSERT into emp values(2004,"Ravi",1004,60000);
INSERT into emp values(2005,"Rani",1007,70000);
INSERT into emp values(2006,"John",1003,40000);
select * from emp;


select empName,deptName from emp,dept where dept.deptNo=emp.deptNo;

select empName,empId,deptNo from emp where deptNo not in (select deptNO from dept);

(select deptNo from dept) union (select deptNo from emp);

select deptNO,deptName from dept where deptNO not in (select deptNo from emp);

select emp.deptNo, empId,empName from emp,dept where emp.deptNo=dept.deptNO AND deptLocation="Cochin";

select * from emp where salary>(select AVG(salary) from emp,dept where emp.deptNo=dept.deptNO AND deptName="Sales");


--select empName from emp where salary in (select MAX(salary) from emp where deptNo=1003);
select empName from emp where salary> ALL(select salary from emp where deptNo=1003)

update emp,dept set salary=salary+salary*.2 where deptName="Sales" AND emp.deptNo=dept.deptNo;
select * from emp;

experiment4.sql

CREATE TABLE student(studentNo int PRIMARY KEY, studentName varchar(50), maths int, physics int, chemistry int, cProgramming int, department varchar(50), address varchar(50));
INSERT INTO student values(100,"Hari",50,60,45,75,"CSE","Kasaragod");
INSERT INTO student values(101,"Devi",60,55,78,40,"CSE","Kasaragod");
INSERT INTO student values(102,"Sam",45,77,88,45,"IT","Kannur");
INSERT INTO student values(103,"SreeHari",90,75,77,60,"IT","Calicut");
INSERT INTO student values(104,"Rani",91,98,89,52,"ECE","Kannur");
INSERT INTO student values(105,"Raj",88,77,67,48,"CSE","Palakkad");

CREATE TABLE dept(deptNO int PRIMARY KEY,deptName varchar(50),deptLocation varchar(50));
INSERT into dept values(1001,"HR","Calicut");
INSERT into dept values(1002,"Marketing","Cochin");
INSERT into dept values(1003,"Sales","Trivandrum");
INSERT into dept values(1004,"Testing","Bangalore");
INSERT into dept values(1005,"Development","Bangalore");

CREATE TABLE emp(empId int PRIMARY KEY,empName varchar(50),deptNo int,salary int);
INSERT into emp values(2001,"Hari",1006,30000);
INSERT into emp values(2002,"Devi",1002,40000);
INSERT into emp values(2003,"Gupta",1003,50000);
INSERT into emp values(2004,"Ravi",1004,60000);
INSERT into emp values(2005,"Rani",1007,70000);
INSERT into emp values(2006,"John",1003,40000);

CREATE VIEW student_info as select studentNo,studentName,department,address from student;
select * from student_info;

CREATE VIEW student_mark as select studentNo,studentName, maths, physics, chemistry, cProgramming from student;
select * from student_mark;

select student_info.studentNo, student_info.studentName, maths+physics+chemistry+cProgramming as TotalMarks, address from student_info, student_mark where student_info.studentNo=student_mark.studentNo;


CREATE VIEW emp_dept as (SELECT emp.empName, dept.deptName FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno);
select * from emp_dept;


select empName from emp_dept where deptName IS NULL;


DROP TABLE student;
DROP TABLE dept;
DROP TABLE emp;

DROP VIEW student_info;
DROP VIEW student_mark;
DROP VIEW emp_dept;

experiment5.sql

CREATE TABLE customer(cust_id int PRIMARY KEY, cust_name varchar(50), cust_phone int);

CREATE TABLE author(auth_name varchar(50) PRIMARY KEY, country varchar(50));

CREATE TABLE book(book_id int PRIMARY KEY, title varchar(50), auth_name varchar(50), unit_price int,  pub_name varchar(50), pub_year int, FOREIGN KEY (auth_name) REFERENCES author(auth_name));

CREATE TABLE sale (
    sale_id int PRIMARY KEY,
    cust_id int,
    book_id int,
    order_date date,
    quantity int,
    FOREIGN KEY (book_id) REFERENCES book(book_id)
);

CREATE TABLE publisher(pub_name varchar(50) PRIMARY KEY, pub_addr varchar(50));


INSERT INTO customer values(100,"Hari",12345);
INSERT INTO customer values(101,"John",23456);
INSERT INTO customer values(102,"Ali",34567);


INSERT INTO author values("Brown","US");
INSERT INTO author values("Forouzan","US");
INSERT INTO author values("Navathe","India");
INSERT INTO author values("Samantha","India");
INSERT INTO author values("Tanenbaum","US");
INSERT INTO author values("V Prasad","India");

INSERT INTO publisher values("Pearson","London");
INSERT INTO publisher values("PHI","Delhi");
INSERT INTO publisher values("TMH","UP");


INSERT INTO book values(2001,"DS","Samantha",500,"PHI",2020);
INSERT INTO book values(2002,"CP","V Prasad",300,"TMH",2019);
INSERT INTO book values(2003,"DBMS","Navathe",900,"Pearson",2018);
INSERT INTO book values(2004,"DC","Forouzan",700,"TMH",2021);
INSERT INTO book values(2005,"CN","Tanenbaum",800,"Pearson",2018);
INSERT INTO book values(2006,"Python","Brown",800,"TMH",2021);


INSERT INTO sale values(301,100,2001,"2021-01-02",3);
INSERT INTO sale values(302,100,2002,"2019-04-02",4);
INSERT INTO sale values(303,100,2003,"2019-04-02",2);
INSERT INTO sale values(304,101,2002,"2021-05-02",2);
INSERT INTO sale values(305,101,2005,"2021-05-02",1);
INSERT INTO sale values(306,102,2006,"2022-01-02",2);



select SUM(unit_price*quantity) as totalsale from sale,book WHERE order_date >= '2021-04-01' AND order_date <= '2022-03-31' AND sale.book_id = book.book_id;

select pub_name,SUM(unit_price) from book where pub_name !="PHI" group by pub_name;

select pub_name,SUM(unit_price) from book group by pub_name;

select cust_name,title from customer,sale,book where customer.cust_id=sale.cust_id && sale.book_id = book.book_id;

select title,book.auth_name,country from book,author where pub_year=2021 and book.auth_name=author.auth_name; 

select title,book.auth_name from book,author where book.auth_name = author.auth_name && country!="India"; 

select customer.cust_id,SUM(quantity) from customer,sale,book where customer.cust_id=sale.cust_id && sale.book_id = book.book_id group by cust_id ORDER BY SUM(quantity) DESC;


DROP TABLE customer;
DROP TABLE sale;
DROP TABLE book;
DROP TABLE author;
DROP TABLE publisher;

experiment6.sql

CREATE TABLE Item(
    item_id int PRIMARY KEY,
    item_desc varchar(100),
    qty_on_hand int,
    unit_price int,
    category varchar(100)
);

CREATE TABLE Sales(
    sale_id int PRIMARY KEY,
    item_id int,
    qty_sold int,
    unit_price int,
    total_price int,
    tax_amount int,
    grand_total int,
    FOREIGN KEY (item_id) REFERENCES Item(item_id)
);

INSERT INTO Item values(100,"TextBook",20,20,"book");
INSERT INTO Item values(101,"milkybar",30,5,"chocolate");
INSERT INTO Item values(102,"apple",50,10,"fruit");
INSERT INTO Item values(103,"pen",100,8,"stationary");

DELIMITER //
CREATE TRIGGER CalculatePrice BEFORE INSERT ON Sales
FOR EACH ROW
    BEGIN
    DECLARE taxrate integer default 5;
    DECLARE category_value varchar(100);
    DECLARE unit_price_value int;

    select category into category_value from Item where item_id=new.item_id;
    select unit_price into unit_price_value from Item where item_id=new.item_id;
    set new.unit_price=unit_price_value;
    set new.total_price=new.qty_sold*new.unit_price;
    if(category_value='book') THEN
    SET taxrate=10;
    elseif (category_value='stationary') THEN
    SET taxrate=20;
    elseif(category_value='chocolate') THEN
    SET taxrate=30;
    end if;
    SET new.tax_amount=new.total_price*(taxrate/100);
    SET new.grand_total=new.total_price+new.tax_amount;
END//
DELIMITER ;

-- ii

DELIMITER //
CREATE TRIGGER CalculateStock AFTER INSERT ON Sales
FOR EACH ROW
    BEGIN
    DECLARE qoh integer;

    select qty_on_hand into qoh from Item where item_id = new.item_id;
    if(qoh-new.qty_sold < 0) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT='Insufficient stock';
    else
    UPDATE Item set qty_on_hand = (qty_on_hand-new.qty_sold) where item_id=new.item_id;
    end if;
END //
DELIMITER ;

select * from Item;

insert into Sales(sale_id,item_id,qty_sold) values(200,100,10);
insert into Sales(sale_id,item_id,qty_sold) values(201,101,25);
insert into Sales(sale_id,item_id,qty_sold) values(202,102,10);
insert into Sales(sale_id,item_id,qty_sold) values(203,103,20);

select * from Sales;
select * from Item;

experiment7.sql

CREATE TABLE bank_customer(
    cust_id int PRIMARY KEY AUTO_INCREMENT,
    cust_name varchar(50),
    email varchar(50)
);

INSERT into bank_customer(cust_name,email) values("hari","hari@gmail.com");
INSERT into bank_customer(cust_name,email) values("sam","sam@gmail.com");
INSERT into bank_customer(cust_name,email) values("sree","sree@lbscek.ac.in");
INSERT into bank_customer(cust_name,email) values("ali","ali@lbscek.ac.in");
INSERT into bank_customer(cust_name,email) values("john","john@yahoo.co.in");


CREATE TABLE account(
    acc_no int PRIMARY KEY,
    acc_type VARCHAR(20) CHECK(acc_type IN ('saving','current')),
    cust_id int,
    balance int,
    FOREIGN KEY(cust_id) REFERENCES bank_customer(cust_id)
);

INSERT into account values(1001,"saving",1,10000);
INSERT into account values(1002,"saving",2,20000);
INSERT into account values(1003,"saving",3,30000);
INSERT into account values(2001,"current",4,10000);
INSERT into account values(2002,"current",5,20000);

-- credit(acc_no,amount)
DELIMITER //
CREATE PROCEDURE credit(IN cred_acc_no INT, IN amount INT) 
BEGIN
IF(cred_acc_no IN (SELECT acc_no from account)) THEN
    UPDATE account set balance = balance + amount WHERE acc_no = cred_acc_no;
ELSE
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid Account number';
END IF;
END //
DELIMITER ;

-- debit(acc_no,amount)
DELIMITER //
CREATE PROCEDURE debit(IN deb_acc_no INT, IN amount INT) 
BEGIN

DECLARE current_balance integer;
DECLARE current_acc_type varchar(10);

IF(deb_acc_no IN (SELECT acc_no from account)) THEN
    Select balance into current_balance from account where deb_acc_no = acc_no;
    Select acc_type into current_acc_type from account where deb_acc_no = acc_no;
    IF (current_balance-amount < 1000 AND current_acc_type='saving') THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Minimum balance not met';
    ELSEIF(current_balance-amount < 0) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Insufficient Balance';   
    ELSE 
        UPDATE account set balance = balance - amount WHERE acc_no = deb_acc_no;
    END IF;
ELSE
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid Account number';
END IF;

END //
DELIMITER ;

-- getBalance(arg_acc_no)
DELIMITER //
CREATE FUNCTION getBalance(arg_acc_no int(10)) RETURNS int DETERMINISTIC
BEGIN
DECLARE total INT;

IF(arg_acc_no IN (SELECT acc_no from account)) THEN
    SELECT balance into total FROM account WHERE arg_acc_no = acc_no;
    RETURN total;
ELSE
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid Account number';
END IF;
END //
DELIMITER ;

call credit(1001,5000);
call debit(1001,5000);
select getBalance(1001) as currentBalance;

-- createEmailList() Procedure

DELIMITER //
CREATE PROCEDURE createEmailList(INOUT emailList varchar(4000))
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE emailAddress varchar(100) DEFAULT "";


	DEClARE curEmail 
		CURSOR FOR 
			SELECT email FROM bank_customer;


	DECLARE CONTINUE HANDLER 
	FOR NOT FOUND SET finished = 1;

	OPEN curEmail;

	getEmail: LOOP
			FETCH curEmail INTO emailAddress;
			IF finished = 1 THEN 
				LEAVE getEmail;
			END IF;
			-- build email list
			SET emailList = CONCAT(emailAddress,";",emailList);
		END LOOP getEmail;
		CLOSE curEmail;

END//
DELIMITER ;

SET @emailList = ""; 
CALL createEmailList(@emailList); 
SELECT @emailList;

proceduredemo.sql

CREATE TABLE employee(empno int PRIMARY KEY,empname varchar(50),empaddress varchar(50),salary int,dept varchar(50));	
INSERT into employee values(2001,"Hari","Palakkad",30000,"Research");
INSERT into employee values(2002,"Devi","Kasaragod",40000,"Network");
INSERT into employee values(2003,"Gupta","Kannur",50000,"System Admin");
INSERT into employee values(2004,"Ravi","Calicut",60000,"HR");
INSERT into employee values(2005,"Rani","Calicut",70000,"HR");
select * from employee;


DELIMITER //
CREATE PROCEDURE GetNameByID(IN employeeId int(4))
BEGIN
SELECT * FROM employee WHERE empNo = employeeId;
END //
DELIMITER ;
call GetNameByID(2001);


-- Count the employees in a particular dept and store the value in total variable
DELIMITER //
CREATE PROCEDURE CountByDept(IN deptName VARCHAR(25),OUT total INT)
BEGIN
SELECT count(empNo) INTO total FROM employee WHERE dept = deptName;
END //
DELIMITER ;
call CountByDept("HR",@total);
select @total;

-- Increment the provided count variable by an amount

DELIMITER //
CREATE PROCEDURE set_counter(INOUT count INT(4), IN inc INT(4))
BEGIN
SET count = count + inc;
END //
DELIMITER ;

set @counter = 1;
CALL set_counter(@counter,1); 
CALL set_counter(@counter,1); 
CALL set_counter(@counter,5);
SELECT @counter;


DROP FUNCTION IF EXISTS countByDeptFn;

DELIMITER //
CREATE FUNCTION countByDeptFn(deptName VARCHAR(25)) RETURNS int DETERMINISTIC
BEGIN
DECLARE total INT;

SELECT count(empNo) into total FROM employee WHERE dept = deptName;
RETURN total;
END //

DELIMITER ;

select countByDeptFn('HR') as EmpCount; 



DROP TABLE employee;
DROP PROCEDURE GetNameByID;
DROP PROCEDURE CountByDept;
DROP PROCEDURE set_counter;

triggerdemo.sql

-- Sum Trigger
create table table1(var1 int(3) NOT NULL,var2 int(3) NOT NULL,sum int(3)
NULL);

Create trigger updateSumTable1
    Before insert
    On table1
    For each row
    Set new.sum=new.var1+new.var2;
    
    
insert into table1 values(3,6,null);

select * from table1;

-- Delete Trigger
    
create table table2(var1 int(3) NOT NULL,var2 int(3) NOT NULL,sum int(3)
NULL);


Create trigger updateTable2
	After delete On table1
	For each row
	insert into table2 values(old.var1,old.var2,old.sum);
	
select * from table2;
delete from table1 where var1=3;
select * from table1;
select * from table2;


-- Update Trigger

create table table3(var int NOT NULL,sum int NULL);

DELIMITER //
Create trigger updateSumTable3
	Before update
	On table3
	For each row
	begin
	if(new.var>old.var) then
	Set new.sum=old.sum+new.var;
	end if;
END //

DELIMITER ;
insert into table3 values(3,3);
update table3 set var=5 where var=3;
select * from table3;
update table3 set var=2 where var=5;
select * from table3;

triggerdemo2.sql

-- Creating department table
CREATE TABLE department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL
);

-- Creating employ table
CREATE TABLE employ (
    employ_id INT PRIMARY KEY,
    employ_name VARCHAR(255) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    department_id INT,
    category VARCHAR(50),
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

-- Trigger to update employ category
DELIMITER //
CREATE TRIGGER update_category
AFTER INSERT ON employ
FOR EACH ROW
BEGIN
    DECLARE employ_count INT;

    -- Count the number of employees in the department
    SELECT COUNT(*) INTO employ_count
    FROM employ
    WHERE department_id = NEW.department_id;

    -- Update category based on salary and employee count
    IF NEW.salary > 50000 AND employ_count > 2 THEN
        UPDATE employ
        SET category = 'fte'
        WHERE employ_id = NEW.employ_id;
    ELSE
        UPDATE employ
        SET category = 'contract'
        WHERE employ_id = NEW.employ_id;
    END IF;
END;
//
DELIMITER ;