SQL useful queries

Few useful queries

to fetch only the first name(string before space) from the FullName column of EmployeeDetailstable

mySQL- SELECT MID(FullName, 0, LOCATE(' ',FullName)) FROM EmployeeDetails;

SQL Server - SELECT SUBSTRING(FullName, 0,CHARINDEX(' ',FullName)) FROM EmployeeDetails;

SELECT LEFT(FullName, CHARINDEX(' ',FullName) - 1) FROM EmployeeDetails

SQL query to fetch duplicate records from a table.

SELECT EmpId, Project, Salary, COUNT(*) FROMEmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1;

remove duplicates from a table without using a temporary table.

DELETE FROM EmployeeSalary  WHERE EmpId IN(SELECT EmpId FROM EmployeeSalary GROUP BYEmpId, Project, Salary HAVING COUNT(*) > 1));

query to fetch only odd rows from the table

SELECT E.EmpId, E.Project, E.Salary FROM(SELECT *, Row_Number() OVER(ORDER BYEmpId) AS RowNumber FROM EmployeeSalary) E WHERE E.RowNumber % 2 = 1;

SQL query to fetch only even rows from the table

SELECT E.EmpId, E.Project, E.Salary FROM(SELECT *, Row_Number() OVER(ORDER BYEmpId) AS RowNumber FROM EmployeeSalary) E WHERE E.RowNumber % 2 = 0;

SQL query to create a new table with data and structure copied from another table SELECT * INTO newTable FROM EmployeeDetails;

SQL query to create an empty table with the same structure as some other table. SELECT * INTO newTable FROM EmployeeDetails WHERE 1 = 0;

SQL query to fetch common records between two tables SELECT * FROM EmployeeSalaryINTERSECT SELECT * FROM ManagerSalary;

SQL query to fetch records that are present in one table but not in another table. SELECT * FROM EmployeeSalary MINUS SELECT * FROMManagerSalary;

SQL query to fetch top n records

MYSQL- SELECT * FROM EmployeeSalary ORDERBY Salary DESC LIMIT N;

SQL Server -SELECT TOP N * FROM EmployeeSalaryORDER BY Salary DESC;

Oracle -SELECT * FROM(SELECT * FROMEmployeeSalary ORDER BY Salary DESC)WHEREROWNUM <= 3;

query to find the nth highest salary from table

SQL server- SELECT TOP 1 Salary FROM (SELECTDISTINCT TOP N Salary FROM Employee ORDERBY Salary DESC) ORDER BY Salary ASC

MYSQL-SELECT Salary FROM Employee ORDERBY Salary DESC LIMIT N-1,1

Write SQL query to find the 3rd highest salary from table without using TOP/limit keyword

SELECT Salary FROM EmployeeSalary Emp1 WHERE2 = (SELECT COUNT( DISTINCT ( Emp2.Salary ) )FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary)

For nth highest salary-

SELECT Salary FROM EmployeeSalary Emp1 WHEREN-1 = (SELECT COUNT( DISTINCT ( Emp2.Salary ) )FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary)

Aggregate functions in SQL

• Count()
• Sum()
• Min()
• Max()
• Avg()

Case example

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

LIKE example % - The percent sign represents zero, one, or multiple characters, - The underscore represents a single character

Select * from table where empname like “%a”;

Select * from table where empname like “a__”; return name start with a and having 3 characters