Commonly Asked SQL Queries

In this article I am sharing some of the SQL queries that are commonly asked. I was practicing with such queries and wanted to know more from people. Please reply with your suggestions/corrections.

There are two tables, Employee and Department, as follows:

Employee (Eid,Ename,Salary,DeptId)

Department(Deptid,Dname)

Assignment: Try the following queries.

Query 1: List the employee name and salary having the 2nd highest salary

Query 2: List all employees with there departments. Employees not related to any department should also display.

Query 3: List records with duplicate employee name.

Query 4: List all departments with total salary of the department.

Query 5: List all departments with their employees.

Query 6: List the name of employees but show only the first 4 characters of the name

Solution

Queries to create the tables:

CREATE TABLE Department(DeptId INT PRIMARY KEY,DeptName VARCHAR(50))CREATE TABLE Employee (Eid int,EName VARCHAR(20),Salary NUMERIC(18,0) CONSTRAINT pk_Employee_Eid PRIMARY KEY (Eid) , DeptId int CONSTRAINT fk_Employee_DeptId FOREIGN KEY(DeptId) REFERENCES Department(DeptId))

Insert data in the tables

INSERT INTO Department (DeptId,DeptName) VALUES (1,'Development') INSERT INTO Department (DeptId,DeptName) VALUES (2,'Marketing') INSERT INTO Department (DeptId,DeptName) VALUES (3,'Testing') INSERT INTO Employee(Eid,EName,Salary,DeptId) values(1,'Akash',35000,1) INSERT INTO Employee(Eid,EName,Salary,DeptId) values(2,'Amit',18000,2) INSERT INTO Employee(Eid,EName,Salary,DeptId) values(3,'Amar',21500,3) INSERT INTO Employee(Eid,EName,Salary) values(4,'Pradip',28000) INSERT INTO Employee(Eid,EName,Salary,DeptId) values(5,'Pradip',16900,1)

So the tables are created and sample data was inserted.

Employee Table:
 

Department Table:

Query 1: List the employee name and salary having the 2nd highest salary.

--Query 1: To get Empyoee Name and salary who is having 2nd highest salary SELECT Ename,Salary FROM Employee WHERE SALARY =(Select MIN(Salary) FROM Employee WHERE Salary in( SELECT TOP(2) Salary FROM Employee ORDER BY salary DESC))

Result:
 

Query 2: List all employees with there departments. Employees not related to any department should also display.

--Query 2 : List All employees with there departments.Employees not related to any department should
also display SELECT e.Ename,d.DeptName FROM Employee e LEFT OUTER JOIN Department d ON e.DeptId=d.DeptId

Result:

Query 3: List records with duplicate employee name.

--Query 3 : List records with duplicate employee name SELECT e.Ename FROM Employee e GROUP BY e.Ename HAVING COUNT (e.Ename)>1

Result:

Query 4: List all departments with the total salary of the department.

--Query 4 : List all departments with total salary of the depatment.
 
 SELECT d.DeptName,SUM(e.Salary) [Total Salary] FROM Department d
 INNER JOIN Employee e
 ON e.DeptId=d.DeptId
 GROUP BY d.DeptName

Result:

Query 5: List all departments with their employees.

--Query 5 : List all departments with there employees SELECT d.DeptName,e.Ename FROM Department d INNER JOIN Employee e ON e.DeptId=d.DeptId GROUP BY d.Deptname,e.Ename

Result:

Query 6: List the name of employees but show only the first 4 characters of the name.
 

--Query 6 : List Name of employees but Show only first 4 characters of name SELECT CONVERT(VARCHAR(4),Ename) AS Ename FROM Employee

                                       
OR
 

--Query 6 : List Name of employees but Show only first 4 characters of name SELECT CONVERT(VARCHAR(4),Ename) AS Ename FROM Employee

Result :