#The MAX function in SQL returns the largest value from a selected column, while the MIN function returns the smallest value from a selected column. Both are often used with the GROUP BY clause to find the highest or lowest values within groups.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    desig VARCHAR(50) NOT NULL DEFAULT 'Probation',
    dept VARCHAR(50) NOT NULL,
    salary INT NOT NULL DEFAULT 25000
);

-- Inserting 10 tuples into the employees table
INSERT INTO employees (fname, lname, desig, dept, salary) VALUES 
('John', 'Doe', 'Manager', 'HR', 30000),
('Jane', 'Smith', 'Developer', 'IT', 40000),
('Paul', 'Adams', 'Associate', 'Sales', 27000),
('Lucy', 'Brown', 'Cashier', 'Finance', 23000),
('Ravi', 'Kumar', 'Developer', 'IT', 35000),
('Mina', 'Patel', 'Accountant', 'Finance', 29000),
('Sam', 'Wilson', 'HR Specialist', 'HR', 32000),
('Tina', 'Jones', 'Sales Executive', 'Sales', 26000),
('Alex', 'Morris', 'Team Lead', 'IT', 42000),
('Victor', 'Lee', 'Marketing Head', 'Marketing', 45000);

SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;

#advance approach
#Q is max with emp_id and dept # use of subquires
SELECT emp_id, UPPER(fname),REVERSE(lname),salary FROM employees WHERE salary=(SELECT MAX(salary) FROM employees);

SELECT MAX(fname) from employees;

SELECT MIN(fname) from employees;