SQL DB Assignment

DATABASE NAME: EMPLOYEE

Table Name : EmployeeInfo , EmployeePosition

• Create Database name as EMPLOYE

  • list of database before creating employee database:

• Create Database name as EMPLOYEE :

create database employee;

• List of Dat
  abase After Creating Employee Database:

• Create Table In Database

Before Tables in Database :

Create table EmployeeInfo:

create table EmployeeInfo (

EmpID SERIAL primary key,

EmpFname varchar(50) not null,

EmpLname varchar(50) not null,

Department varchar(20) not null,

Project varchar(10) not null,

Address varchar(100) not null,

DOB DATE not null,

Gender char(1) not null

);

Create table EmployeePosition :

create table EmployeePosition (

EmpID int references EmployeeInfo(EmpID) UNIQUE,

EmpPosition varchar(20),

DateOfJoing DATE not null,

Salary int )

Both table in Database :

• Insert Data Into EmployeeInfo table:

insert into EmployeeInfo (EmpFname, EmpLname, Department, Project, Address, DOB, Gender)

values

('Sanjay', 'Mehra', 'HR', 'P1', 'Hyderabad(HYD)', '1976-12-01', 'M'),

('Ananya', 'Mishra', 'Admin', 'P2', 'Delhi(DEL)', '1968-05-02', 'F'),

('Rohan', 'Diwan', 'Account', 'P3', 'Mumbai(BOM)', '1980-01-01', 'M'),

('Sonia', 'Kulkarni', 'HR', 'P1', 'Hyderabad(HYD)', '1992-05-02', 'F'),

('Ankit', 'Kapoor', 'Admin', 'P2', 'Delhi(DEL)', '1994-07-03', 'M');

Display Data of EmployeeInfo :

select * from employeeInfo;

• Insert Data in EmployeePosition :

insert into EmployeePosition (EmpID, EmpPosition, DateOfJoining, Salary )

values

(1, 'Manager', '2022-05-01',500000),

(2, 'Executive', '2022-05-02',75000),

(3, 'Manager', '2022-05-01',90000),

(4, 'Lead', '2022-05-02',85000),

(5, 'Executive', '2022-05-01',300000);

Display inserted data :

select * from EmployeePosition;

• Display Tables Details :

Employeeinfo table :

EmployeePosition table :

write queries for this:

1. Write a query to fetch the number of employees working in the department ‘Admin’

select count(*) as "No of employee in Admin" from EmployeeInfo where department='Admin';

2. Write a query to retrieve the first four characters of  EmpLname from the EmployeeInfo table.

select substring(emplname,1,4) as "Emplname " from EmployeeInfo;

3. Write q query to find all the employees whose salary is between 50000 to 100000.

select * from EmployeeInfo ei join EmployeePosition ep on ei.EmpID=ep.EmpID where ep.salary between 50000 and 100000;

4. Write q query to find all the employees whose salary is between 50000 to 100000.

select empfname from EmployeeInfo where empfname like 'S%';

5. Write a query to fetch top N records order by salary. (ex. top 5 records)

select * from EmployeeInfo limit 3;

6. Write a query to fetch details of all employees excluding the employees with first names, “Sanjay” and “Sonia” from the EmployeeInfo table.

select * from EmployeeInfo where empfname not in ('Sanjay','Sonia');

7. Write a query to fetch the department-wise count of employees sorted by department’s count in ascending order.

select Department , count(*) as "NoOfEmployee" from EmployeeInfo group by Department order by count(*);

8. Create indexing for any particular field and show the difference in data fetching before and after indexing

query before indexing :

creating index on emplname:

create index idx_emp_last_name on EmployeeInfo(emplname);

now same query after indexing on emplname :