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 );
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
);
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.
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;
Problem Statement: write an SQL to get the highest sold product(quantity*price) on both days.
Solution:
with cte as(
select *,(quantity * price) as amount,row_number()over(partition by order_day order by (quantity * price)) as row_number from Order_Tbl),
cte1 as(select order_day,max(row_number) as max from cte group by order_day)
select cte.order_day,cte.product_id,cte.amount from cte ,cte1 where cte.order_day=cte1.order_day
and cte.row_number=max;
Thanks for the read🙂.Do clap if find it useful.👏👏
“Keep learning and keep sharing knowledge”
