EXPERIMENT NO 1
CREATE USER 'emp_user'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON *.* TO 'emp_user'@'localhost' WITH
GRANT OPTION;
{OR}
CREATE USER emp_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, DBA TO emp_user;
CONNECT emp_user/password
CREATE TABLE Employee ( EMPNO INT,ENAME VARCHAR(255), JOB
VARCHAR(255), MANAGER_NO INT, SAL INT,COMMISSION INT);
START TRANSACTION;
INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,
COMMISSION) VALUES (101, 'Alice', 'Manager', NULL, 75000.00,
5000.00), (102, 'Bob', 'Developer', 101, 60000.00, 3000.00), (103,
'Charlie', 'Analyst', 101, 55000.00, 2500.00);
ROLLBACK;
SELECT * FROM Employee;
ALTER TABLE Employee MODIFY EMPNO INT PRIMARY KEY, MODIFY
ENAME VARCHAR(255) NOT NULL, MODIFY JOB VARCHAR(255) NOT
NULL;
INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,
COMMISSION) VALUES (104, NULL, 'HR', NULL, 50000.00, 2000.00);
EXPERIMENT 2
CREATE TABLE Employee1 ( EMPNO INT PRIMARY KEY,
ENAME VARCHAR(50), JOB VARCHAR(50), MGR INT, SAL
DECIMAL(10,2) );
ALTER TABLE Employee1 ADD COMMISSION DECIMAL(10,2);
INSERT INTO Employee1 (EMPNO, ENAME, JOB, MGR, SAL,
COMMISSION) VALUES (101, 'Alice', 'Manager', NULL, 60000,
5000), (102, 'Bob', 'Analyst', 101, 50000, 3000), (103,
'Charlie', 'Clerk', 102, 30000, NULL), (104, 'David', 'Salesman',
101, 40000, 2000), (105, 'Eve', 'Developer', 102, 55000,
2500);
UPDATE Employee1 SET JOB = 'Analyst' WHERE JOB = 'Clerk';
ALTER TABLE Employee1 RENAME COLUMN ENAME TO
EMP_NAME;
DELETE FROM Employee1 WHERE EMPNO = 105;
EXPERIMENT 3
CREATE TABLE Employee (
E_id INT,E_name VARCHAR(100),Age INT, Salary INT);
INSERT INTO Employee VALUES (1, 'Braham Kumar', 30, 50000);
INSERT INTO Employee VALUES (2, 'Shubham Kumar', 25,
60000);
INSERT INTO Employee VALUES (3, 'Anjali Kumari', 35, 55000);
INSERT INTO Employee VALUES (4, 'Aman Kumar', 28, 62000);
INSERT INTO Employee VALUES (5, 'Shoaib Akhtar', 40, 70000);
SELECT COUNT(E_NAME) AS "NUMBER OF
EMPLOYEES"
FROM EMPLOYEE;
SELECT MAX(AGE) AS "MAXIMUM AGE"
FROM EMPLOYEE;
SELECT MIN(AGE) AS "MINIMUM AGE"
FROM EMPLOYEE;
SELECT E_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY ASC;
SELECT Age, SUM(SALARY) AS "TOTAL SALARY"
FROM EMPLOYEE
GROUP BY AGE;
EXPERIMENT 4
CREATE TABLE CUSTOMERS (
ID INT PRIMARY KEY,
NAME VARCHAR(255),
AGE INT,ADDRESS VARCHAR(255),SALARY DECIMAL(10, 2) );
DELIMITER //
CREATE TRIGGER after_insert_salary_difference
AFTER INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
SET @my_sal_diff = CONCAT('Salary inserted is: ', NEW.SALARY);
END; //
DELIMITER ;
DELIMITER //
CREATE TRIGGER after_update_salary_difference
AFTER UPDATE ON CUSTOMERS
FOR EACH ROW
BEGIN
DECLARE old_salary DECIMAL(10, 2);
DECLARE new_salary DECIMAL(10, 2);
SET old_salary = OLD.SALARY;
SET new_salary = NEW.SALARY;
SET @my_sal_diff = CONCAT('Salary difference after update is: ',
new_salary - old_salary);
END;
//
DELIMITER ;
DELIMITER //
CREATE TRIGGER after_delete_salary_difference
AFTER DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
SET @my_sal_diff = CONCAT('Salary deleted is: ', OLD.SALARY);
END;
//
DELIMITER ;
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'Shankara', 35, '123 Main St', 50000.00);
SELECT @my_sal_diff AS SAL_DIFF;
UPDATE CUSTOMERS SET SALARY = 55000.00 WHERE ID = 1;
SELECT @my_sal_diff AS SAL_DIFF;
DELETE FROM CUSTOMERS WHERE ID = 1;
SELECT @my_sal_diff AS SAL_DIFF;
EXPERIMENT 5
CREATE TABLE Employee (E_id INT, E_name
VARCHAR(255),Age INT, Salary DECIMAL(10, 2));
INSERT INTO Employee (E_id, E_name, Age, Salary)VALUES
(1, 'Samarth', 30, 50000.00), (2, 'Ramesh Kumar', 25,
45000.00), (3, 'Seema Banu', 35, 62000.00), (4, 'Dennis
Anil', 28, 52000.00), (5, 'Rehman Khan', 32, 58000.00);
DELIMITER //
CREATE PROCEDURE fetch_employee_data()
BEGIN
DECLARE emp_id INT;
DECLARE emp_name VARCHAR(255);
DECLARE emp_age INT;
DECLARE emp_salary DECIMAL(10, 2);
DECLARE emp_cursor CURSOR FOR
SELECT E_id, E_name, Age, Salary
FROM Employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET @finished = 1;
OPEN emp_cursor;
SET @finished = 0;
cursor_loop: LOOP
FETCH emp_cursor INTO emp_id, emp_name, emp_age,
emp_salary;
IF @finished = 1 THEN
LEAVE cursor_loop;
END IF;
SELECT CONCAT('Employee ID: ', emp_id, ', Name: ',
emp_name, ', Age: ', emp_age, ', Salary: ', emp_salary) AS
Employee_Info;
END LOOP;
CLOSE emp_cursor;
END//
DELIMITER ;
TO SEE THE RESULT:
CALL fetch_employee_data();
EXPERIMENT 6
CREATE DATABASE ROLLCALL;
USE ROLLCALL;
CREATE TABLE N_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);
CREATE TABLE O_RollCall (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);
INSERT INTO O_RollCall (student_id, student_name,
birth_date)
VALUES
(1, 'Shivanna', '1995-08-15'),
(3, 'Cheluva', '1990-12-10');
INSERT INTO N_RollCall (student_id, student_name,
birth_date)
VALUES
(1, 'Shivanna', '1995-08-15'),
(2, 'Bhadramma', '1998-03-22'),
(3, 'Cheluva', '1990-12-10'),
(4, 'Devendra', '2000-05-18'),
(5, 'Eshwar', '1997-09-03');
DELIMITER //
CREATE PROCEDURE merge_rollcall_data()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE n_id INT;
DECLARE n_name VARCHAR(255);
DECLARE n_birth_date DATE;
DECLARE n_cursor CURSOR FOR
SELECT student_id, student_name, birth_date FROM
N_RollCall;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done
= TRUE;
OPEN n_cursor;
cursor_loop: LOOP
FETCH n_cursor INTO n_id, n_name, n_birth_date;
IF done THEN
LEAVE cursor_loop;
END IF;
IF NOT EXISTS (
SELECT 1 FROM O_RollCall WHERE student_id = n_id
) THEN
INSERT INTO O_RollCall (student_id, student_name,
birth_date)
VALUES (n_id, n_name, n_birth_date);
END IF;
END LOOP;
CLOSE n_cursor;
END //
DELIMITER ;
CALL merge_rollcall_data();
SELECT * FROM O_RollCall;
EXPERIMENT 7
Create a new database:
use studentDB
Create a collection:
db.createCollection("students")
Perform CRUD Operations
db.students.insertOne({ name: "Alice", age: 22,
course: "Computer Science" })
db.students.insertMany([
{ name: "Bob", age: 23, course: "IT" },
{ name: "Charlie", age: 21, course: "ECE" }
])
Read – Query documents
db.students.find()
db.students.find({ name: "Alice" })
Update – Modify documents
db.students.updateOne(
{ name: "Bob" },
{ $set: { age: 24 } }
)
db.students.updateMany(
{ course: "IT" },
{ $set: { course: "Information Technology" } }
)
To see changes
db.students.find()
Delete – Remove documents
db.students.deleteOne({ name: "Charlie" })
To see changes
db.students.find()
