...gment course programing\SQL_practice top 30 questions.

sql 1
use ORG;
CREATE TABLE EmployeeInfo (
EMP_ID INT NOT NULL PRIMARY KEY,
EMPF_NAME CHAR(25),
EMPL_NAME CHAR(25),
DEPARTMENT VARCHAR(50),
PROJECT VARCHAR(25),
ADDRES VARCHAR(25),
DOB DATE,
GENDER VARCHAR(25)
);
INSERT INTO EmployeeInfo
(EMP_ID, EMPF_NAME, EMPL_NAME, DEPARTMENT,PROJECT, ADDRES,DOB,GENDER)
VALUES

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

(2, 'Anaya', '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');
select * from EmployeeInfo;

CREATE TABLE EmployeePosition (

EMP_ID INT NOT NULL ,
EmpPosition CHAR(25),
Dateofjoining DATE,
Salary int
);

INSERT INTO EmployeePosition

(EMP_ID, EmpPosition, Dateofjoining, Salary)
VALUES

(1, 'Manager','2022-05-01',500000),
(2, 'Exicutive','2022-05-02',750000),
(3, 'Manager','2022-05-01',900000),
(2, 'Lead','2022-05-02',850000),
(1, 'Exicutive','2022-05-01',300000);

select * from EmployeeInfo;

select * from EmployeePosition;
--Q1. Write a query to fetch the EmpFname from the EmployeeInfo table in upper case
and use the ALIAS name as EmpName.

select upper(EMPF_NAME) as EmpName from EmployeeInfo;

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

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

--Q3. Write a query to get the current date.

...gment course programing\SQL_practice top 30 questions.sql 2
SELECT GETDATE();

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

SELECT SUBSTRING(EMPL_NAME,1,4) FROM EmployeeInfo;

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

SELECT SUBSTRING(ADDRES,1,CHARINDEX('(',ADDRES)) FROM EmployeeInfo;

--Q6. Write a query to create a new table which consists of data and structure copied
from the other table.

SELECT*INTO NEWTABLE FROM EmployeeInfo WHERE 1=0;

SELECT*FROM NEWTABLE;

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

SELECT * FROM EmployeePosition WHERE SALARY BETWEEN 500000 AND 1000000;

--Q8. Write a query to find the names of employees that begin with ‘S’.

SELECT * FROM EmployeeInfo WHERE EMPF_NAME LIKE 'S%';

--Q9. Write a query to fetch top N records.

SELECT TOP 3 * FROM EmployeeInfo ORDER BY EMPF_NAME;

SELECT TOP 2 * FROM EmployeePosition ORDER BY Salary DESC;

--Q.10 Q10. 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(EMPF_NAME,' ',EMPL_NAME) AS FULLNAME FROM EmployeeInfo;

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

SELECT COUNT(*) ,GENDER FROM EmployeeInfo WHERE DOB BETWEEN '1965-05-02' AND
'1975-12-31' GROUP BY GENDER;

--Q12. 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 EMPL_NAME DESC , DEPARTMENT ASC ;

--Q13. Write a query to fetch details of employees whose EmpLname ends with an
alphabet ‘A’ and contains five alphabets.
...gment course programing\SQL_practice top 30 questions.sql 3
SELECT * FROM EmployeeInfo WHERE EMPF_NAME LIKE '%____A';

--Q14. 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 EMPF_NAME NOT IN ('Sanjay' ,'Sonia');

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

SELECT * FROM EmployeeInfo WHERE ADDRES LIKE 'DELHI%';

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

SELECT E.EMPF_NAME , E.DEPARTMENT,E.PROJECT, E.EMP_ID ,P.EMP_ID,P.EmpPosition from

EmployeeInfo AS E inner join EmployeePosition as P on E.EMP_ID=P.EMP_ID AND
P.EmpPosition='MANAGER';

--Q17. Write a query to fetch the department-wise count of employees sorted by

department’s count in ascending order.

select count(*), DEPARTMENT FROM EmployeeInfo GROUP BY DEPARTMENT ORDER BY DEPARTMENT

ASC;

--Q18. Write a query to calculate the even and odd records from a table.

SELECT * FROM EmployeeInfo WHERE EMP_ID % 2=1; --FOR ODDS

SELECT * FROM EmployeeInfo WHERE EMP_ID % 2=0; --FOR EVEN

--Q19. Write a SQL query to retrieve employee details from EmployeeInfo table who have
a date of joining in the EmployeePosition table.

SELECT * FROM EmployeeInfo;

SELECT * FROM EmployeePosition;

SELECT * FROM EmployeeInfo E

WHERE EXISTS
(SELECT * FROM EmployeePosition P WHERE E.EMP_ID = P.EMP_ID);

--Q20. Write a query to retrieve two minimum and maximum salaries from the
EmployeePosition table.

SELECT DISTINCT SALARY FROM EmployeePosition E1 WHERE 2>=(SELECT DISTINCT COUNT

(SALARY) FROM EmployeePosition E2 WHERE E1.Salary>=E2.Salary); --TWO MINMUM SALARY

SELECT DISTINCT SALARY FROM EmployeePosition E1 WHERE 2>=(SELECT DISTINCT COUNT

(SALARY) FROM EmployeePosition E2 WHERE E1.Salary<=E2.Salary); -- TWO MAXIMUM SALARY

--Q21. Write a query to find the Nth highest salary from the table without using TOP/
limit keyword.
...gment course programing\SQL_practice top 30 questions.sql 4
SELECT DISTINCT SALARY,EmpPosition FROM EmployeePosition E1 WHERE 1>=(SELECT DISTINCT
COUNT(SALARY) FROM EmployeePosition E2 WHERE E1.Salary<=E2.Salary);

--Q22. Write a query to retrieve duplicate records from a table.

SELECT COUNT(*) ,EMP_ID,EMPF_NAME,EMPL_NAME,DEPARTMENT FROM EmployeeInfo GROUP BY

EMP_ID,EMPF_NAME,EMPL_NAME,DEPARTMENT HAVING COUNT(*)>1;

SELECT COUNT(*),EmpPosition ,salary from EmployeePosition group by EmpPosition ,salary

having COUNT(*)>1;

--Q23. Write a query to retrieve the list of employees working in the same department.

SELECT DISTINCT E.EMPF_NAME,E.EMPL_NAME,E.DEPARTMENT FROM EmployeeInfo E JOIN

EmployeeInfo E1 ON E.EMP_ID !=E1.EMP_ID WHERE E.DEPARTMENT=E1.DEPARTMENT;

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

select * from EmployeeInfo where EMP_ID>=3 ;

--Q25. Write a query to find the third-highest salary from the EmpPosition table.

select * from EmployeePosition;

select top 1 Salary from(select top 3 Salary from EmployeePosition order by salary
desc) as emp order by salary asc;

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

select top 1* from EmployeeInfo order by EMP_ID asc;

select top 1* from EmployeeInfo order by EMP_ID desc;

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

SELECT DEPARTMENT ,COUNT(EMP_ID) FROM EmployeeInfo GROUP BY DEPARTMENT HAVING COUNT

(EMP_ID)<2;

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

SELECT * FROM EmployeePosition;

SELECT EmpPosition,sum(Salary) as total_salary from EmployeePosition group by

EmpPosition ;

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

select * from EmployeeInfo where EMP_ID in (select (EMP_ID/2) from EmployeeInfo);