MySQL - Program to create employee table

1. Create a Employee Table with eno,ename,department,designation,gender and salary.
2. Department must be only in sales, It and market.

  program:

  Create table employee (

  Empid number(5) primary key,

  name varchar(20) not null,

  department varchar(20) not null,

  designation varchar(20) not null,

  gender char(1) not null,

  salary number(10,2),

  primary key(Empid),

  check (department in ('Sales','IT','Accounts')),

  check (designation in ('Manager','Supervisor','Clerk','Analyst','Accountant')),

  check (gender in ('M','F')));

Queries

1. Display empno, name & designation of all employees whose name ends with 'raj'

select empid, name, designation

from employee

where name like '%raj';

2. Display the details of all female employees who are earning salary within the range 30000 and 40000 in ‘IT’ or ‘Sales’

select * from employee

where gender = 'F'

and salary between 30000 and 40000

and department in ('IT','Sales');

3. List the different departments with designation in that department

select distinct department, designation

from employee;

4.List the department which have more than 5 employees

select department 

from employee 

group by department 

having count(*) > 5;

6. Display avg salary, min salary, max salary, sum salary of each department having sum(salary) > 30000

select department, 

 sum(salary) as "Total Salary", 

avg(salary) as "Avg Salary", 

 min(salary) as "Min Salary", 

max(salary) as "Max Salary" 

from employee 

group by department 

having sum(salary) > 30000;