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 fname, lname 
FROM employees 
WHERE dept IN (
    SELECT dept 
    FROM employees 
    GROUP BY dept 
    HAVING SUM(salary) > 26000
);

SELECT fname FROM employees where salary > 26000;
SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

#use by groups by dept each
SELECT dept, COUNT(emp_id) ,SUM(salary) from employees GROUP BY dept;