SQL Interview Questions

In this article, I will be sharing a few SQL interview questions that are commonly asked in SQL interviews.

Script for table creation

TABLE:-

CREATE TABLE Employee
(ID INT PRIMARY KEY,
Name VARCHAR(50) NULL,
RoleName VARCHAR(10) NULL,
Salary NUMERIC(18,2) NULL
);

DATA:-

INSERT INTO Employee(id,name,rolename,salary)VALUES
( 1,'Alex', 'SuperAdmin', 90000 ),
( 2,'Thomas', 'Admin', 80000 ),
( 3,'Peter', 'SuperAdmin', 95000 ),
( 4,'Sid', 'SuperAdmin', 70000 ),
( 5, 'Maria', 'Admin', 60000 ),
( 6,'Andrew', 'Manager', 50000 ),
( 7,'Fedric', 'SuperAdmin', 45000 ),
( 8,'Anton', 'Supervisor', 93000 ),
( 9,'Mandy', 'Manager', 30000 );

select * from employee

Problem Statement: Write an SQL query to print the details of an employee whose salary is greater than the average salary in their role name.

Solution:

-- Using SubQuery
select * from employee e where e.salary > (
select avg(salary) from employee
where rolename = e.rolename);

-- Using PartitionBy
select * from (
select *,avg(salary)over(partition by rolename ) from employee
)as a where salary > avg;