EXP3:
-- Create BranchTable
CREATE TABLE BranchTable (
BranchID INT PRIMARY KEY,
BranchName VARCHAR(100) NOT NULL UNIQUE,
Location VARCHAR(100) NOT NULL
);
-- Create Employee Table
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DOB DATE NOT NULL,
Gender CHAR(1),
HireDate DATE NOT NULL,
BranchID INT,
Email VARCHAR(100) UNIQUE,
PhoneNumber VARCHAR(15) UNIQUE,
FOREIGN KEY (BranchID) REFERENCES BranchTable(BranchID)
);
-- Add CHECK constraint for Gender
ALTER TABLE Employee
ADD CONSTRAINT chk_gender CHECK (Gender IN ('M', 'F', 'O'));
-- Create Salary Table
CREATE TABLE Salary (
SalaryID INT PRIMARY KEY,
EmpID INT UNIQUE,
BasicSalary DECIMAL(10,2) NOT NULL,
Bonus DECIMAL(10,2) DEFAULT 0,
Deductions DECIMAL(10,2) DEFAULT 0,
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID) ON DELETE CASCADE
);
-- Add CHECK constraints for Salary separately
ALTER TABLE Salary
ADD CONSTRAINT chk_basic_salary CHECK (BasicSalary > 0);
ALTER TABLE Salary
ADD CONSTRAINT chk_bonus CHECK (Bonus >= 0);
ALTER TABLE Salary
ADD CONSTRAINT chk_deductions CHECK (Deductions >= 0);
-- Rename a Table
RENAME TABLE Salary TO EmployeeSalary;
-- Rename a Column (Example: Change PhoneNumber to MobileNumber)
ALTER TABLE Employee
CHANGE COLUMN PhoneNumber MobileNumber VARCHAR(15) UNIQUE;
-- Rename a Table
INSERT INTO BranchTable (BranchID, BranchName, Location)
VALUES
(101, 'Head Office', 'New York'),
(102, 'Branch A', 'Los Angeles'),
(103, 'Branch B', 'Chicago');
-- Insert into Employee
INSERT INTO Employee (EmpID, FirstName, LastName, DOB, Gender, HireDate, BranchID,
Email,MobileNumber)
VALUES
(1, 'John', 'Doe', '1998-05-12', 'M', '2024-04-01', 101, 'john.doe@example.com',
'1234567890'),
(2, 'Jane', 'Smith', '1995-07-20', 'F', '2024-04-05', 102,
'jane.smith@example.com', '0987654321');
-- Insert into Salary
INSERT INTO EmployeeSalary (SalaryID, EmpID, BasicSalary, Bonus, Deductions)
VALUES
(1, 1, 50000, 5000, 2000),
(2, 2, 60000, 6000, 3000);
-- Drop a Column
ALTER TABLE Employee
DROP COLUMN Email;
-- Drop a Constraint (Example: Gender Check Constraint)
ALTER TABLE Employee
DROP CONSTRAINT chk_gender;
-- Drop a Table
DROP TABLE EmployeeSalary;
-- Truncate a Table (Delete all rows but keep structure)
TRUNCATE TABLE Employee;
SELECT * FROM BranchTable;
SELECT * FROM Employee;
exp5:
-- Create a temporary table
CREATE TEMPORARY TABLE EmployeeTemp (
EmpID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2),
Email VARCHAR(100)
);
-- Insert sample data
INSERT INTO EmployeeTemp VALUES
(1, 'John', 'Doe', 'Finance', 50000, 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'Marketing', 60000, 'jane.smith@example.com');
SELECT
-- Basic String Joining
CONCAT(FirstName,SPACE(5), LastName) AS FullName,
CONCAT_WS('-', FirstName, LastName, Department) AS JoinedData,
-- Case Changes
UPPER(FirstName) AS FirstNameUpper,
LOWER(Department) AS DepartmentLower,
-- Length Information
LENGTH(Email) AS EmailLength,
CHAR_LENGTH(Email) AS EmailCharLength,
-- Trimming
TRIM(' Hello World ') AS TrimmedExample,
LTRIM(' Hello') AS LeftTrimmed,
RTRIM('Hello ') AS RightTrimmed,
-- Substring Extraction
SUBSTRING(Email, 1, 5) AS EmailStart,
LEFT(FirstName, 2) AS FirstNameLeft,
RIGHT(LastName, 2) AS LastNameRight,
MID(Email, 6, 4) AS MidEmail, -- MID = SUBSTRING
-- Replace / Reverse / Insert
REPLACE(Department, 'Finance', 'Accounts') AS DepartmentReplaced,
REVERSE(FirstName) AS FirstNameReversed,
INSERT(FirstName, 2, 3, 'XYZ') AS InsertExample,
-- Repeating and Spaces
REPEAT('A', 5) AS RepeatedA,
CONCAT('Hi', SPACE(5), 'There') AS SpaceExample,
-- ASCII / ORD
ASCII('A') AS AsciiA,
ORD('A') AS OrdA,
-- Searching / Finding
FIND_IN_SET('Finance', 'HR,Marketing,Finance,IT') AS FindSetExample,
LOCATE('example', Email) AS LocateExample,
POSITION('example' IN Email) AS PositionExample,
INSTR(Email, 'example') AS InstrExample,
-- Number Formatting
FORMAT(Salary, 2) AS FormattedSalary,
-- Padding
LPAD(EmpID, 5, '0') AS EmpIDPaddedLeft,
RPAD(FirstName, 10, '*') AS FirstNamePaddedRight,
-- String Comparison
STRCMP(FirstName, LastName) AS CompareFirstLast
FROM EmployeeTemp;
aggregate :
-- Create a temporary Employee table
CREATE TEMPORARY TABLE EmployeeTemp (
EmpID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2),
Email VARCHAR(100)
);
-- Insert sample data
INSERT INTO EmployeeTemp VALUES
(1, 'John', 'Doe', 'Finance', 50000, 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'Marketing', 60000, 'jane.smith@example.com'),
(3, 'David', 'Clark', 'Finance', 55000, 'david.clark@example.com'),
(4, 'Emily', 'Jones', 'Marketing', 58000, 'emily.jones@example.com'),
(5, 'Michael', 'Brown', 'IT', 62000, 'michael.brown@example.com');
-- Now all aggregate functions in a single query
SELECT
COUNT(*) AS TotalEmployees, -- Count all employees
SUM(Salary) AS TotalSalaryPaid, -- Total of all salaries
AVG(Salary) AS AverageSalary, -- Average salary
MIN(Salary) AS LowestSalary, -- Minimum salary
MAX(Salary) AS HighestSalary -- Maximum salary
FROM EmployeeTemp;
EXP 7:
-- Step 1: Create EmployeeTemp Table
CREATE TEMPORARY TABLE EmployeeTemp (
EmpID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2),
Email VARCHAR(100)
);
-- Step 2: Insert Sample Data
INSERT INTO EmployeeTemp VALUES
(1, 'John', 'Doe', 'Finance', 50000, 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'Marketing', 60000, 'jane.smith@example.com'),
(3, 'David', 'Clark', 'Finance', 55000, 'david.clark@example.com'),
(4, 'Emily', 'Jones', 'Marketing', 58000, 'emily.jones@example.com'),
(5, 'Michael', 'Brown', 'IT', 62000, 'michael.brown@example.com');
-- Step 3: Create a separate temp table for safe subqueries
CREATE TEMPORARY TABLE EmployeeTempCopy AS
SELECT * FROM EmployeeTemp;
-- ✅ 1. Subquery with SELECT (Find employees earning more than average salary)
SELECT *
FROM EmployeeTemp
WHERE Salary > (SELECT AVG(Salary) FROM EmployeeTempCopy);
-- ✅ 2. Subquery with INSERT (Insert highest salary employee into TopEmployee)
CREATE TEMPORARY TABLE TopEmployee AS
SELECT *
FROM EmployeeTemp
WHERE Salary = (SELECT MAX(Salary) FROM EmployeeTempCopy);
-- ✅ 3. Subquery with UPDATE (Increase salary 10% for employees below average
salary)
UPDATE EmployeeTemp
SET Salary = Salary * 1.10
WHERE Salary < (SELECT AVG(Salary) FROM EmployeeTempCopy);
-- ✅ 4. Subquery with DELETE (Delete employees earning less than minimum Marketing
salary)
DELETE FROM EmployeeTemp
WHERE Salary < (
SELECT MIN(Salary)
FROM (SELECT Salary FROM EmployeeTempCopy WHERE Department = 'Marketing') AS
TempMinSalary
);
-- ✅ 5. Complex GROUP BY and HAVING (Departments with total salary > 100000)
SELECT
Department,
COUNT(*) AS NumEmployees,
SUM(Salary) AS TotalSalary
FROM
EmployeeTemp
GROUP BY
Department
HAVING
SUM(Salary) > 100000;
-- ✅ 6. Complex DELETE based on GROUP BY + HAVING
DELETE FROM EmployeeTemp
WHERE Department IN (
SELECT Department
FROM (
SELECT Department
FROM EmployeeTempCopy
GROUP BY Department
HAVING SUM(Salary) < 110000
) AS LowSalaryDepartments
);
-- ✅ 7. Final output: Remaining employees
SELECT * FROM EmployeeTemp;
EXP: 9
-- Step 1: Create Temporary Table for Employees
CREATE TEMPORARY TABLE EmployeeTemp (
EmpID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2),
Email VARCHAR(100)
);
-- Step 2: Insert Sample Data
INSERT INTO EmployeeTemp VALUES
(1, 'John', 'Doe', 'Finance', 50000, 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'Marketing', 60000, 'jane.smith@example.com'),
(3, 'David', 'Clark', 'Finance', 55000, 'david.clark@example.com'),
(4, 'Emily', 'Jones', 'Marketing', 58000, 'emily.jones@example.com'),
(5, 'Michael', 'Brown', 'IT', 62000, 'michael.brown@example.com');
-- Step 3: Create Stored Procedure to Increase Salary
DELIMITER $$
CREATE PROCEDURE IncreaseSalaryByDepartment(
IN DeptName VARCHAR(50),
IN IncreasePercent DECIMAL(5,2)
)
BEGIN
UPDATE EmployeeTemp
SET Salary = Salary * (1 + IncreasePercent / 100)
WHERE Department = DeptName;
END $$
DELIMITER ;
-- Step 4: Create Stored Procedure to Get Average Salary (using OUT parameter)
DELIMITER $$
CREATE PROCEDURE GetAvgSalaryByDepartment(
IN DeptName VARCHAR(50),
OUT AvgSalary DECIMAL(10,2)
)
BEGIN
SELECT AVG(Salary)
INTO AvgSalary
FROM EmployeeTemp
WHERE Department = DeptName;
END $$
DELIMITER ;
-- Step 5: Call Stored Procedure to Increase Salary
CALL IncreaseSalaryByDepartment('Finance', 10); -- Increase Finance salaries by 10%
-- Step 6: Call Stored Procedure to Get Average Salary
-- Declare a session variable to hold output
SET @AvgFinanceSalary = 0;
CALL GetAvgSalaryByDepartment('Finance', @AvgFinanceSalary);
-- Show the Average Salary result
SELECT @AvgFinanceSalary AS Finance_Department_Average_Salary;
-- Step 7: See Final Table After Changes
SELECT * FROM EmployeeTemp;
