Nth highest salary
With limit clause
SELECT salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1
select min(salary) from(select distinct salary from emp order by salary desc) where rownum <3;
Write SQL Query to display the current date.
SELECT GetDate();
Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;
Write an SQL Query to find name of employee whose name Start with ‘M’
SELECT * FROM Employees WHERE EmpName like 'M%';
Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)
Write a query to fetch only the first name(string before space) from the FullName column of EmployeeDetails table.
SELECT MID(FullName, 0, LOCATE(' ',FullName)) FROM EmployeeDetails;
Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.
SELECT E.FullName, S.Salary
FROM EmployeeDetails E LEFT JOIN EmployeeSalary S
ON E.EmpId = S.EmpId;
Write a SQL query to fetch only odd rows from table.
SELECT * FROM table
WHERE Column_ID % 2 = 1
Write a SQL query to fetch only even rows from table.
SELECT * FROM table
WHERE Column_id % 2 = 0
Write a SQL query to fetch common records between two tables.
SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary
Delete duplicate data from table only first data remains constant.
DELETE M1
From managers M1, managers M2
Where M2.Name = M1.Name AND M1.Id>M2.Id;
Write An SQL Query To Fetch Unique Values Of DEPARTMENT From Worker Table.
Select distinct DEPARTMENT from Worker;
Write An SQL Query To Print The First Three Characters Of FIRST_NAME From Worker Table.
Select substring(FIRST_NAME,1,3) from Worker;
Write An SQL Query To Find The Position Of The Alphabet (‘A’) In The First Name Column ‘Amitabh’ From Worker Table.
Select INSTR(FIRST_NAME, BINARY'a') from Worker where FIRST_NAME = 'Amitabh';
Write An SQL Query To Print The FIRST_NAME From Worker Table After Removing White Spaces From The Right Side.
Select RTRIM(FIRST_NAME) from Worker;
Write An SQL Query To Print The FIRST_NAME From Worker Table After Replacing ‘A’ With ‘A’.
Select REPLACE(FIRST_NAME,'a','A') from Worker;
Write An SQL Query To Print Details For Workers With The First Name As “Vipul” And “Satish” From Worker Table.
Select * from Worker where FIRST_NAME in ('Vipul','Satish');
Write An SQL Query To Print Details Of Workers With DEPARTMENT Name As “Admin”.
Select * from Worker where DEPARTMENT like 'Admin%';
Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Contains ‘A’.
Select * from Worker where FIRST_NAME like '%a%';
Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Ends With ‘A’.
Select * from Worker where FIRST_NAME like '%a';
Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Ends With ‘H’ And Contains Six Alphabets.
Select * from Worker where FIRST_NAME like '_____h';
Write An SQL Query To Print Details Of The Workers Whose SALARY Lies Between 100000 And 500000.
Select * from Worker where SALARY between 100000 and 500000;
Write An SQL Query To Fetch The Count Of Employees Working In The Department ‘Admin’.
SELECT COUNT(*) FROM worker WHERE DEPARTMENT = 'Admin';
Write An SQL Query To Print Details Of The Workers Who Have Joined In Feb’2014.
Select * from Worker where year(JOINING_DATE) = 2014 and month(JOINING_DATE) = 2;
Write An SQL Query To Fetch Worker Names With Salaries >= 50000 And <= 100000.
SELECT FIRST_NAME, LAST_NAME, Salary
FROM worker
WHERE Salary BETWEEN 50000 AND 100000;
Write An SQL Query To Show The Second Highest Salary From A Table.
Select max(Salary) from Worker
where Salary < (Select max(Salary) from Worker);
Write An SQL Query To Fetch The Departments That Have Less Than Five People In It.
SELECT DEPARTMENT, COUNT(WORKER_ID) as 'Number of Workers' FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) < 5;
Write An SQL Query To Print The Name Of Employees Having The Highest Salary In Each Department.
SELECT t.DEPARTMENT,t.FIRST_NAME,t.Salary from(SELECT max(Salary) as TotalSalary,DEPARTMENT from Worker group by DEPARTMENT) as TempNew
Inner Join Worker t on TempNew.DEPARTMENT=t.DEPARTMENT
and TempNew.TotalSalary=t.Salary;
Write An SQL Query To Fetch Departments Along With The Total Salaries Paid For Each Of Them.
SELECT DEPARTMENT, sum(Salary) from worker group by DEPARTMENT;
Write An SQL Query To Fetch The Names Of Workers Who Earn The Highest Salary.
SELECT FIRST_NAME, SALARY from Worker WHERE SALARY=(SELECT max(SALARY) from Worker);
Get the department name and number of employees in the department
SELECT department_name AS 'Department Name',
COUNT(*) AS 'No of Employees'
FROM departments
INNER JOIN employees
ON employees.department_id = departments.department_id
GROUP BY departments.department_id, department_name
ORDER BY department_name;
