S1)

1.

2.

3.

4.
5.

S2)

1.

2.

3.
4.

5.
 S3)

1.

2.

3.
4.

5.

S5)

1.
2.

3.

4.
5.

S6)

-- Creating the 'dept' table

CREATE TABLE dept (

deptno INT PRIMARY KEY,

dname VARCHAR(50),

mgreno INT

);

-- Creating the 'emp' table

CREATE TABLE emp (

eno INT PRIMARY KEY,

ename VARCHAR(50),

bdate DATE,

title VARCHAR(50),

salary DECIMAL(10,2),

deptno INT,

FOREIGN KEY (deptno) REFERENCES dept(deptno)

);

-- Creating the 'proj' table

CREATE TABLE proj (

pname VARCHAR(50),

budget DECIMAL(10,2),

deptno INT,

FOREIGN KEY (deptno) REFERENCES dept(deptno)

);

-- Creating the 'workson' table

CREATE TABLE workson (

eno INT,

pno INT,

responsibility VARCHAR(50),

hours INT,

FOREIGN KEY (eno) REFERENCES emp(eno),

FOREIGN KEY (pno) REFERENCES proj(pno)

);

-- Inserting values into 'dept' table

INSERT INTO dept (deptno, dname, mgreno) VALUES

(1, 'D1', 100),

(2, 'D2', 200),

(3, 'D3', 300);

-- Inserting values into 'emp' table

INSERT INTO emp (eno, ename, bdate, title, salary, deptno) VALUES

(1, 'John', '1990-05-15', 'EE', 40000, 1),

(2, 'Jane', '1995-10-20', 'SA', 45000, 1),

(3, 'Mike', '1992-08-08', 'EE', 55000, 2),

(4, 'Sarah', '1998-03-01', 'Manager', 60000, 2),

(5, 'David', '1993-06-25', 'SA', 38000, 3),

(6, 'Emily', '1997-12-12', 'Manager', 70000, 3);

INSERT INTO proj (pno, pname, budget, deptno) VALUES

(1, 'Project A', 150000, 1),

(2, 'Project B', 90000, 2),

(3, 'Project C', 120000, 2),

(4, 'Project D', 80000, 3),

(5, 'Project E', 200000, 3);

-- Inserting values into 'workson' table

INSERT INTO workson (eno, pno, responsibility, hours) VALUES

(1, 1, 'Developer', 40),

(2, 1, 'Manager', 30),

(3, 2, 'Developer', 20),

(4, 3, 'Manager', 25),

(5, 4, 'Developer', 15),

(6, 5, 'Manager', 35);

1.
2.

3.

4.
5.

S7}

CREATE TABLE dept (

dno INT PRIMARY KEY,

dname VARCHAR(50),

mgreno INT

);

CREATE TABLE emp (

eno INT PRIMARY KEY,

ename VARCHAR(50),

bdate DATE,

title VARCHAR(50),

salary DECIMAL(10, 2),

dno INT,

FOREIGN KEY (dno) REFERENCES dept(dno)

);

CREATE TABLE proj (

pno INT PRIMARY KEY,

pname VARCHAR(50),
 budget DECIMAL(10, 2),

dno INT,

FOREIGN KEY (dno) REFERENCES dept(dno)

);

CREATE TABLE workson (

eno INT,

pno INT,

responsibility VARCHAR(50),

hours INT,

FOREIGN KEY (eno) REFERENCES emp(eno),

FOREIGN KEY (pno) REFERENCES proj(pno)

);

INSERT INTO dept (dno, dname, mgreno) VALUES

(1, 'Department A', 1001),

(2, 'Department B', 1002),

(3, 'Department C', 1003);

INSERT INTO emp (eno, ename, bdate, title, salary, dno) VALUES

(10001, 'John Doe', '1990-01-01', 'Manager', 5000, 1),

(10002, 'Jane Smith', '1995-05-10', 'Developer', 3000, 1),

(10003, 'Michael Johnson', '1992-08-15', 'Analyst', 4000, 2),

(10004, 'Emily Davis', '1997-03-20', 'Designer', 3500, 2),

(10005, 'Robert Brown', '1988-12-05', 'Tester', 3200, 3);

INSERT INTO proj (pno, pname, budget, dno) VALUES

(1, 'Project X', 80000, 1),

(2, 'Project Y', 60000, 2),

(3, 'Project Z', 40000, 3);

(10002, 1, 'Coding', 20),

(10003, 1, 'Testing', 15),

(10004, 2, 'Designing', 10),

(10005, 2, 'Documentation', 8),

(10001, 3, 'Management', 40);

1.

2.

3.
4.

5.

S8)

-- Create the 'emp' table

CREATE TABLE emp (

eno INT PRIMARY KEY,

ename VARCHAR(50),

bdate DATE,
 title VARCHAR(50),

salary DECIMAL(10, 2),

dno INT,

FOREIGN KEY (dno) REFERENCES dept(dno)

);

-- Create the 'proj' table

CREATE TABLE proj (

pno INT PRIMARY KEY,

pname VARCHAR(50),

budget DECIMAL(10, 2),

dno INT,

FOREIGN KEY (dno) REFERENCES dept(dno)

);

-- Create the 'dept' table

CREATE TABLE dept (

dno INT PRIMARY KEY,

dname VARCHAR(50),

mgreno INT

);

-- Create the 'workson' table

CREATE TABLE workson (

eno INT,

pno INT,

resp VARCHAR(50),

hours INT,

FOREIGN KEY (eno) REFERENCES emp(eno),

FOREIGN KEY (pno) REFERENCES proj(pno)

);
-- Insert values into 'emp' table

INSERT INTO emp (eno, ename, bdate, title, salary, dno)

VALUES

(1, 'John Doe', '1990-05-10', 'Manager', 5000.00, 1),

(2, 'Jane Smith', '1995-02-15', 'Consultant', 4000.00, 1),

(3, 'Michael Johnson', '1988-09-20', 'Analyst', 3000.00, 2),

(4, 'Emily Brown', '1992-11-30', 'Consultant', 4500.00, 2);

-- Insert values into 'proj' table

INSERT INTO proj (pno, pname, budget, dno)

VALUES

(1, 'Project A', 10000.00, 1),

(2, 'Project B', 15000.00, 2);

-- Insert values into 'dept' table

INSERT INTO dept (dno, dname, mgreno)

VALUES

(1, 'Consulting', 1),

(2, 'Production', 2);

-- Insert values into 'workson' table

INSERT INTO workson (eno, pno, resp, hours)

VALUES

(1, 1, 'Lead', 40),

(2, 1, 'Developer', 30),

(3, 2, 'Designer', 20),

(4, 2, 'Tester', 35);

2.

3.
4.

5.

SELECT emp.*

FROM emp

JOIN workson ON emp.eno = workson.eno

JOIN proj ON workson.pno = proj.pno

JOIN dept ON proj.dno = dept.dno

WHERE proj.pname = 'Banking' AND dept.dname = 'Production' AND workson.hours = 120;

S9)

CREATE TABLE Employee (

employee_name VARCHAR(50) PRIMARY KEY,

street VARCHAR(100),

city VARCHAR(100)

);

CREATE TABLE Company (

company_name VARCHAR(100) PRIMARY KEY,

city VARCHAR(100)

);

CREATE TABLE Works (

employee_name VARCHAR(50),

company_name VARCHAR(100),

salary DECIMAL(10, 2),

FOREIGN KEY (company_name) REFERENCES Company(company_name)

);

CREATE TABLE Manages (

employee_name VARCHAR(50),

manager_name VARCHAR(50),

FOREIGN KEY (employee_name) REFERENCES Employee(employee_name),

FOREIGN KEY (manager_name) REFERENCES Employee(employee_name)

);

1.

2.
3,

4.

5.

S10)

1.
2.

3.

5.
4.

S11)

-- 1) Create the Employee table

CREATE TABLE Employee (

EmployerId INT,

Lastname VARCHAR(50),

Firstname VARCHAR(50),

Middlename VARCHAR(50),

JobId INT,

ManagerId INT,

Hiredate DATE,

Salary INT,

DepartmentId INT

);

-- 2) Insert the records into the Employee table

INSERT INTO Employee (EmployerId, Lastname, Firstname, Middlename, JobId, ManagerId, Hiredate,
Salary, DepartmentId)

VALUES
 (7369, 'Smith', 'Jon', 'Q', 667, 7902, '2021-12-17', 800, 10),

(7499, 'Allen', 'Kevin', '1', 670, 7698, '2022-02-20', 1600, 20),

(7505, 'Doyle', 'Jean', 'K', 671, 7839, '2023-04-04', 2850, 20),

(7505, 'Doyle', 'Lynn', 'S', 671, 7839, '2023-05-15', 2750, 30),

(7505, 'Doyle', 'Leslie', 'D', 671, 7839, '2023-06-10', 2200, 40),

(7505, 'Doyle', 'Cynthia', 'D', 670, 7698, '2022-02-22', 1250, 10),

(7506, 'Dennis', 'Baker', NULL, 7521, NULL, NULL, NULL, NULL);

1)

2.

3.
4.

CREATE VIEW Salary_Increment_View AS

SELECT EmployerId, Lastname, Firstname, Middlename, JobJd, ManagerJd, Hiredate, Salar * 1.1 AS
Salar, Department_id

FROM Employee;

5.

S12)

1. CREATE TABLE Animal (

id INT AUTO_INCREMENT,

name VARCHAR(50),

PRIMARY KEY (id)

);

INSERT INTO Animal (name) VALUES ('Cat'), ('Dog'), ('Elephant'), ('Lion');

2.
3.

4.

5.

S13)

Location (Location_Id, Regional_Group)

Department (Department_Id, Name, Location_Id)

Job (Job_Id, Function)

Employee (Employee_Id, Lastname, Firstname, Middlename, Job_Id, Manager_Id, Hiredate,

2.

3.

4.

5.
S14)

-- Create 'product' table

CREATE TABLE product (

product_id INT PRIMARY KEY,

product_name VARCHAR(255),

supplier_name VARCHAR(255),

unit_price DECIMAL(10, 2)

);

-- Create 'product_price_history' table

CREATE TABLE product_price_history (

product_id INT,

product_name VARCHAR(255),

supplier_name VARCHAR(255),

unit_price DECIMAL(10, 2),

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

-- Create trigger to update 'product_price_history' table

CREATE TRIGGER update_price_history

AFTER UPDATE ON product

FOR EACH ROW

BEGIN

INSERT INTO product_price_history (product_id, product_name, supplier_name, unit_price)

VALUES (NEW.product_id, NEW.product_name, NEW.supplier_name, NEW.unit_price);

END;
S15)

create a table called "account" with columns "accno" and "amount" in SQL:

create a trigger on the "account" table that will check the new inserted amount before an
update and perform certain actions based on its value:

CREATE TRIGGER before_account_update

BEFORE UPDATE ON account

FOR EACH ROW

BEGIN

IF NEW.amount < 0 THEN

SET NEW.amount = 0;

ELSEIF NEW.amount > 100 THEN

SET NEW.amount = 100;

END IF;

END;

S16)

Procedure to find the maximum number from given three numbers:

PROCEDURE FindMaximumNumber(num1 INT, num2 INT, num3 INT)

BEGIN

DECLARE maxNum INT;

IF num1 >= num2 AND num1 >= num3 THEN

SET maxNum = num1;

ELSEIF num2 >= num1 AND num2 >= num3 THEN

SET maxNum = num2;

ELSE
 SET maxNum = num3;

END IF;

SELECT maxNum AS MaximumNumber;

END;

create a table and insert values:

CREATE TABLE studentdata (

mark1 INT,

mark2 INT,

mark3 INT,

mark4 INT,

student_name VARCHAR(50)

);

INSERT INTO studentdata (mark1, mark2, mark3, mark4, student_name)

VALUES

(90, 85, 95, 92, 'John Doe'),

(78, 82, 80, 85, 'Jane Smith'),

(88, 90, 92, 87, 'Alice Johnson');

To list the average marks of each student, you can use the following query:

SELECT student_name, CalculateAverageMarks(mark1, mark2, mark3, mark4) AS

FROM studentdata;
Function to calculate the average of marks:

S17)

-- Create a table to store student information

CREATE TABLE students (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50),

marks INT

);

-- Insert sample data

INSERT INTO students (name, marks) VALUES

('John', 85),

('Alice', 92),

('Bob', 78),

('Emily', 68),

('David', 95),
 ('Sarah', 87),

('Michael', 72),

('Emma', 90),

('Daniel', 83),

('Olivia', 79);

-- Create a procedure to calculate the grade

DELIMITER //

CREATE PROCEDURE calculate_grade()

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE student_id INT;

DECLARE student_name VARCHAR(50);

DECLARE student_marks INT;

DECLARE cur CURSOR FOR SELECT id, name, marks FROM students;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO student_id, student_name, student_marks;

IF done THEN

LEAVE read_loop;

END IF;

-- Calculate the grade based on marks

DECLARE student_grade CHAR(1);

IF student_marks >= 90 THEN

SET student_grade = 'A';

ELSEIF student_marks >= 80 THEN

ELSEIF student_marks >= 70 THEN

SET student_grade = 'C';

ELSEIF student_marks >= 60 THEN

SET student_grade = 'D';

ELSE

SET student_grade = 'F';

END IF;

-- Display the grade for each student

SELECT CONCAT('Student Name: ', student_name, ', Grade: ', student_grade) AS result;

END LOOP;

CLOSE cur;

END //

DELIMITER ;

-- Call the procedure to calculate grades

CALL calculate_grade();

S18)

-- Create the student table

CREATE TABLE student (

RollNo INT,

mark1 INT,

mark2 INT,

mark3 INT,

mark4 INT

);
-- Insert sample values

INSERT INTO student (RollNo, mark1, mark2, mark3, mark4)

VALUES (1, 80, 90, 75, 85),

(2, 70, 65, 80, 90),

(3, 85, 95, 70, 80);

-- Create the procedure

DELIMITER //

CREATE PROCEDURE CalculateAverage()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE rollNo INT;

DECLARE m1, m2, m3, m4 INT;

DECLARE cur CURSOR FOR SELECT RollNo, mark1, mark2, mark3, mark4 FROM student;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

read_loop: LOOP

FETCH cur INTO rollNo, m1, m2, m3, m4;

IF done = 1 THEN

LEAVE read_loop;

END IF;

-- Calculate average marks

DECLARE average DECIMAL(5,2);

SET average = (m1 + m2 + m3 + m4) / 4;

-- Display the result

SELECT CONCAT('Roll No: ', rollNo, ', Average Marks: ', average) AS Result;

END LOOP;
 CLOSE cur;

END //

DELIMITER ;

-- Call the procedure

CALL CalculateAverage();

S20)

1. Create a stored procedure:

CREATE PROCEDURE FindOddEven(IN num INT)

BEGIN

DECLARE result VARCHAR(10);

IF num % 2 = 0 THEN

SET result = 'Even';

ELSE

SET result = 'Odd';

END IF;

SELECT result AS `Number Type`;

END //

DELIMITER ;

2. Call the stored procedure:

CREATE FUNCTION IsEven(num INT) RETURNS VARCHAR(10)

BEGIN

DECLARE result VARCHAR(10);

IF num % 2 = 0 THEN

SET result = 'Even';

ELSE

SET result = 'Odd';

END IF;

RETURN result;

END //

DELIMITER ;