-- Create the EmployeeInfo table
CREATE TABLE EmployeeInfo (
    EmpID INTEGER PRIMARY KEY,
    EmpFname VARCHAR(50),
    EmpLname VARCHAR(50),
    Department VARCHAR(50),
    Project VARCHAR(50),
    Address VARCHAR(100),
    DOB DATE,
    Gender VARCHAR(20)
);

-- Create the EmployeePosition table
CREATE TABLE EmployeePosition (
    EmpID INTEGER PRIMARY KEY,
    EmpPosition VARCHAR(50),
    DateOfJoining DATE,
    Salary INTEGER
);


-- Insert data into the EmployeeInfo table
INSERT INTO EmployeeInfo (EmpID, EmpFname, EmpLname, Department, Project, Address, DOB, Gender)
VALUES
(1, 'Sanjay', 'Mehra', 'HR', 'P1', 'Hyderabad(HYD)', '1976-01-12', 'M'),
(2, 'Ananya', 'Mishra', 'Admin', 'P2', 'Delhi(DEL)', '1968-02-05', 'F'),
(3, 'Rohan', 'Diwan', 'Account', 'P3', 'Mumbai(BOM)', '1980-01-01', 'M'),
(4, 'Sonia', 'Kulkarni', 'HR', 'P1', 'Hyderabad(HYD)', '1992-02-05', 'F'),
(5, 'Ankit', 'Kapoor', 'Admin', 'P2', 'Delhi(DEL)', '1994-03-07', 'M');



-- -- Insert data into the EmployeePosition table
INSERT INTO EmployeePosition (EmpID, EmpPosition, DateOfJoining, Salary)
VALUES
(1, 'Manager', '2022-01-05', 500000),
(2, 'Executive', '2022-02-05', 75000),
(3, 'Manager', '2022-01-05', 90000),
(4, 'Lead', '2022-02-05', 85000),
(5, 'Executive', '2022-01-05', 300000);

-- 1) Write a query to fetch the EmpFname from the EmployeeInfo table in the upper case and use the ALIAS name as EmpName.

select upper(EmpFname) as EmpName from EmployeeInfo ;


-- 2) Write a query to fetch the number of employees working in the department ‘HR’.

select count(*) from EmployeeInfo 
where Department='HR';

-- 3) Write a query to get the current date.

select current_Date;
SELECT CURRENT_TIME;

-- 4) Write a query to retrieve the first four characters of  EmpLname from the EmployeeInfo table.

select  substring(EmpLname, 1, 4) from EmployeeInfo;

-- 5) Write a query to fetch only the place name(string before brackets) from the Address column of EmployeeInfo table.

-- SELECT SUBSTRING(Address, 1, CHARINDEX('(',Address)) FROM EmployeeInfo;

-- 6) Write a query to create a new table that consists of data and structure copied from the other table.

select * into newtable from EmployeeInfo;

select * from newtable;

-- 7) Write q query to find all the employees whose salary is between 50000 to 100000.

select EmpFname, EmpLname from EmployeeInfo inner join EmployeePosition
on EmployeeInfo.EmpID = EmployeePosition.EmpID
where salary between '50000' and '100000';
-- 8) Write a query to find the names of employees that begin with ‘S’

select EmpFname from EmployeeInfo 
where EmpFname like 'S%';
-- 9) Write a query to fetch top N records.
select * from EmployeeInfo limit 3; 


-- 10) Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. The first name and the last name must be separated with space.

select concat(EmpFname , ' ',  EmpLname) as Fullname from EmployeeInfo;

--11 Write a query find number of employees whose DOB is between 02/05/1970 to 31/12/1975 and are grouped according to gender

-- select count(*) , Gender from EmployeeInfo
-- where DOB between '02-05-1970' and '31-12-1975' 
-- group by Gender;

-- 12 Write a query to fetch all the records from the EmployeeInfo table ordered by EmpLname in descending order and Department in the ascending order.

select * from EmployeeInfo 
order by EmpLname desc ,Department asc;

-- 13 Write a query to fetch details of employees whose EmpLname ends with an alphabet ‘A’ and contains five alphabets.

select EmpLname from EmployeeInfo  where EmpLname like '    a%'
and length(EmpLname)=5;

--14  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');

--15 Write a query to fetch details of employees with the address as “DELHI(DEL)”.


select * from EmployeeInfo where Address = 'Delhi(DEL)';


-- 16 Write a query to fetch all employees who also hold the managerial position.


select * from EmployeeInfo inner join EmployeePosition
on EmployeeInfo.EmpID = EmployeePosition.EmpID
where EmpPosition='Manager';

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

select Department, count(EmpID) as Dept_Count FROM EmployeeInfo
group by Department
order by Dept_Count asc ;

-- 18 Write a query to find the 4th highest salary from the EmployeePosition table.

SELECT DISTINCT Salary
FROM EmployeePosition
ORDER BY Salary DESC
LIMIT 1 OFFSET 3;

--  Q24. Write a query to retrieve the last 3 records from the EmployeeInfo table.

SELECT *
FROM EmployeeInfo
ORDER BY EmpID DESC
LIMIT 3;

--  Q26 Write a query to display the first and the last record from the EmployeeInfo table.

SELECT * FROM EmployeeInfo
WHERE EmpID = (SELECT MIN(EmpID) FROM EmployeeInfo)
UNION
SELECT * FROM EmployeeInfo
WHERE EmpID = (SELECT MAX(EmpID) FROM EmployeeInfo);

--  28 Write a query to retrieve Departments who have less than 2 employees working in it.

select Department, Count(EmpID) as deptcount from EmployeeInfo group by Department having count(EmpID)<2;

--  29 Write a query to retrieve EmpPostion along with total salaries paid for each of them.

select EmpPosition, sum(Salary)  as Total_Sum from EmployeePosition
group by EmpPosition;

-- 30  Write a query to fetch 50% records from the EmployeeInfo table.




create table emp(
id integer,
name varchar,
city varchar);

-- Insert 1000 rows into your_table (adjust table and column names accordingly)
INSERT INTO emp (id, name, city)
SELECT
  generate_series(1, 20),  -- This generates values from 1 to 1000
 'Name' || generate_series(1, 20) AS name,                 -- Example data for name
  'Delhi';                  -- Example data for column3


select * from emp;



SELECT *
FROM EmployeePosition
ORDER BY Salary DESC
LIMIT 4;