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:
 

1.png

Department Table:

2.png

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:
 

3.png

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:

4.png

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:


5.png

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:


6.png

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:

7.png

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 :

8.png

 

Next Recommended Readings