Task2_Employee.sql

use task2;

--1.Creates the table with name Employee with columns id,name,salary,department,city
create table employees(
Id int,
Employee_name varchar(255),
salary int,
Department varchar(255),
city varchar(255),
);

--2.Inserts the different records in Employee table
insert into employees(id,Employee_name,salary,department,city)  
values(1,'Nayyab',20000,'Computer Science','Faisalabad');

insert into employees(id,Employee_name,salary,department,city)  
values(2,'Laiba',31000,'Computer Science','Faisalabad');

insert into employees(id,Employee_name,salary,department,city)  
values(3,'Abeera',24000,'Software Engineering','Sialkot');

insert into employees(id,Employee_name,salary,department,city)  
values(4,'Fatima',40000,'Computer Science','Faisalabad');

insert into employees(id,Employee_name,salary,department,city)  
values(5,'Rubab',50000,'Computer Science','Gujranwala');

insert into employees(id,Employee_name,salary,department,city)  
values(6,'Nayyab',20000,'Computer Science','Faisalabad');

insert into employees(id,Employee_name,salary,department,city)  
values(7,'Hamza',31000,'Computer Science','Faisalabad');

insert into employees(id,Employee_name,salary,department,city)  
values(8,'Ahmed',24000,'Software Engineering','Sialkot');

insert into employees(id,Employee_name,salary,department,city)  
values(9,'Musfirah',60000,'Computer Science','Faisalabad');

insert into employees(id,Employee_name,salary,department,city)  
values(10,'Abrish',70000,'Computer Science','Gujranwala');

--3.Sql query to retrieve/select all columns from Employee table
select *from employees;

--4.like keyword is used to show the data starting from the alphabet in brackets
select *from employees
where Employee_name LIKE 'N%'

--5.like keyword is used to show the data ending on the alphabet in brackets
select *from employees
where Employee_name LIKE '%a';

--6.like keyword is used to show the data containing b in second place
select *from employees
where Employee_name LIKE '_b%';

--7.like keyword is used to not show the data ending on the alphabet in brackets or any command that we give with not like is shown
select *from employees
where Employee_name NOT LIKE '%a';

--8.max keyword is used to display max value in specified column
select max(salary) As Max_salary from employees ;

--9.min keyword is used to display min value in specified column
select min(salary) As Min_salary from employees ;

--10.Sum keyword is used to display the sum of all the value in specified column according to condition
select SUM(salary) from employees
where salary >= 50000;

--11.AVG keyword is used to display the average of all the value in specified column according to condition
select AVG(salary) from employees
where salary >= 50000;

--12.Count keyword is used to display number of entries in the table/column
select count (*) from employees;

--13.Counts the number of employees who have salary greater than condition 
select count (Employee_name) from employees
where salary >= 30000;