1)Customer table Orders table. Customer table has Customer details. Orders have orders by customer.
Query to list of customer who hasn't placed any orders using Joins
select a.customername, isnull(count(*),0)
from cutsomers a left join orders b
on a.customersid = b.customerid
2)A table has students. Another has subjects. List all subjects applicable to each student.
select a.studentname, b.subjactname
from students a right join subjects b
on a.studentid = b.studentid
3)SQL Query
input: 0, 0, 0, 1, 1, 1
output: 0, 1, 0, 1, 0, 1
ANS- with cte as(
select id, row_number() over (partition by id order by id) rn from table)
select id from cte order by rn,id
4)How to read json file?
Ans- df = spark.read.format("json").load("filepath")
5) SCD TYPE 2 code --
we have emp_full.csv with eno,name,sal,location
emp_incr.csv with same schema
write both .csvs to empt_tgt delta table using SCDtype2