SQL Interview Questions

In this article, I will be sharing a few SQL interview questions which 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(name,rolename,salary)VALUES
( ‘Alex’, ‘SuperAdmin’, 90000 ),
( ‘Thomas’, ‘Admin’, 80000 ),
( ‘Peter’, ‘SuperAdmin’, 95000 ),
( ‘Sid’, ‘SuperAdmin’, 70000 ),
( ‘Maria’, ‘Admin’, 60000 ),
( ‘Andrew’, ‘Manager’, 50000 ),
( ‘Fedric’, ‘SuperAdmin’, 45000 ),
( ‘Anton’, ‘Supervisor’, 93000 ),
( ‘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;

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

Solution:

select * from employee e where e.salary > ALL(
select avg(salary) from employee
group by rolename);

Problem Statement: Write an SQL query to print the top max salaries of 3 employees by each rolename.

Solution:

With using window functions

select name, rolename, salary from (
select *,row_number()over(partition by rolename order by salary desc) from employee
) as n where row_number =1
order by salary desc limit 3;

Without using window functions

select * from employee where salary in (
select MAX(salary) from employee GROUP BY rolename
order by 1 desc limit 3) order by 4 desc;

Problem Statement: Write an SQL query to count null values from each column.

CREATE TABLE value_check
( id bigint,
val1 text ,
val2 text
);

select * from value_check;

Solution:

select sum(case when val1 is null then 1 else 0 end) as null_val1_count,
sum(case when val2 is null then 1 else 0 end) as null_val2_count
from value_check;

Problem Statement: Write an SQL query to print logged-in and logged-out users.

select * from users;

Solution:

select userid,min(case when tbl_nm=’Log_in’ then creat_ts end) as login,
max(case when tbl_nm=’Log_out’ then creat_ts end) as logout from users
group by userid;

Thanks for the read🙂.Do clap if find it useful.👏👏

“Keep learning and keep sharing knowledge”