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', 'LOAN', 23000),
('Ravi', 'Kumar', 'Developer', 'IT', 35000),
('Mina', 'Patel', 'Accountant', 'Finance', 29000),
('Sam', 'Wilson', 'HR Specialist', 'HR', 32000),
('Tina', 'Jones', 'Sales Executive', 'loan', 26000),
('Alex', 'Morris', 'Team Lead', 'IT', 42000),
('Victor', 'Lee', 'Marketing Head', 'Marketing', 45000);
#FIND TOTAL NO. OF EMPLOYEES IN DATABASE
SELECT COUNT(emp_id) from employees;
#FINF NO. OF EMPLOYEES IN EACH DEPARTMENT
SELECT COUNT(emp_id),dept from employees GROUP BY dept;

#FIND LOWESR SALARY PAYING
SELECT emp_id,fname,lname,desig,dept,salary from employees WHERE salary =( SELECT MIN(salary) from employees);

#find highest salry
SELECT emp_id,fname,lname,desig,dept,salary froM employees where salary =(SELECT MAX(salary) from employees);

#FIND TOTAL SALRY PAYING IN LOAN DEPARMENT
SELECT SUM(salary)  AS TOTAL_SALARY from employees where dept = 'loan';

#FIND AVG SALARY paying in each dept;
SELECT AVG(salary) AS AVERAGE_SALARY FROM employees group by dept;