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]()