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 (
pno INT PRIMARY KEY,
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);
-- Inserting values into 'proj' table
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);
INSERT INTO workson (eno, pno, responsibility, hours) VALUES
(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);
1.
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 (employee_name) REFERENCES Employee(employee_name),
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,
Salary, Department_Id)
1.
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
average_marks
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
SET student_grade = 'B';
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:
DELIMITER //
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:
3. Create a function:
DELIMITER //
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 ;
