SQL Queries

Write a query to fetch top N records.

By using the TOP command in SQL Server: SELECT TOP N * FROM EmployeePosition ORDER BY Salary DESC; By using the LIMIT command in MySQL: SELECT * FROM EmpPosition ORDER BY Salary DESC LIMIT N;

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 EmpFname desc, Department asc;

Write a query to find the names of employees that begin with 'S'

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

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

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

SELECT * FROM EmployeeInfo WHERE EmpLname LIKE '____a';

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

SELECT * FROM EmployeePosition WHERE Salary BETWEEN '50000' AND '100000';

Write a query to print all variables for Sports Department.

SELECT * FROM employees WHERE department = 'Sports'

Query all employee information and their divisions of the department.

SELECT * FROM employees eINNER JOIN departments dON e.department = d.department

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;

Write a query to fetch the number of employees working in the department 'HR'.

SELECT COUNT(*) FROM EmployeeInfo WHERE Department = 'HR';

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

SELECT Department, count(EmpID) AS EmpDeptCount FROM EmployeeInfo GROUP BY Department ORDER BY EmpDeptCount ASC;

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

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

What is the total salary for each department?

SELECT SUM(salary) as total_salary,department FROM employeesGROUP BY department

Find total salary by each department and sort descending by total salary column.

SELECT SUM(salary) as total_salary,department FROM employeesGROUP BY departmentORDER BY total_salary desc

Write a query to find the Nth highest salary from the table without using TOP/limit keyword.

SELECT Salary FROM EmployeePosition E1 WHERE N-1 = ( SELECT COUNT( DISTINCT ( E2.Salary ) ) FROM EmployeePosition E2 WHEREE2.Salary > E1.Salary );

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

SELECT TOP 1 salary FROM( SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC) AS emp ORDERBYsalary ASC;

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;

Which departments have more than 50 employees?

SELECT count(*) as total_employee,department FROM employeesGROUP BY departmentHAVING COUNT(*) > 50

Write a query that prints all departments from employees and matches departments from the department table.

SELECT e.department,d.department FROM employees eLEFT JOIN departments dON e.department = d.department

Write a query that finds the first 5 employees with their first_name, department and salary and sorted by their first_name.

SELECT first_name,department,salary from employeesORDER BY first_nameLIMIT 5

Query first_name, department, and salary of each employee and also maximum salary given.

SELECT first_name,department,salary,(SELECT max(salary) FROM employees)FROM employees

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

To retrieve two minimum salaries, you can write a query as below: SELECT DISTINCT Salary FROM EmployeePosition E1 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary)FROM EmployeePosition E2 WHERE E1.Salary >= E2.Salary) ORDER BY E1.Salary DESC; To retrieve two maximum salaries, you can write a query as below: SELECT DISTINCT Salary FROM EmployeePosition E1 WHERE 2 >= (SELECTCOUNT(DISTINCT Salary) FROM EmployeePosition E2 WHERE E1.Salary <= E2.Salary) ORDER BY E1.Salary DESC;

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

Using the MID function in MySQL: SELECT MID(Address, 0, LOCATE('(',Address)) FROM EmployeeInfo; Using SUBSTRING: SELECT SUBSTRING(Address, 1, CHARINDEX('(',Address)) FROM EmployeeInfo;

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

Using the SELECT INTO command: SELECT * INTO NewTable FROM EmployeeInfo WHERE 1 = 0; Using the CREATE command in MySQL: CREATE TABLE NewTable AS SELECT * FROM EmployeeInfo;

Write a query to get the current date.

You can write a query as follows in SQL Server: SELECT GETDATE(); You can write a query as follows in MySQL: SELECT SYSTDATE();