dbms day 6 exercise 62 code completion

[Kshitij-Bajpai]

alias will make more sense in the final query

step1: useful columns

actual price of single unit = price - price discount/100 total price of unit grouped = sum((price - price discount/100) * quantity)

step 2: Average sales amount

select avg(sum((s6.price - s6.price s6.discount/100) s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid

step 3: Table for those saleid who have more than avg sale amount

select s3.saleid,sum((s4.price - s4.price s4.discount/100) s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid group by s3.saleid having sum((s4.price - s4.price s4.discount/100) s3.quantity) > ( select avg(sum((s6.price - s6.price s6.discount/100) s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid )

step 4: selecting saleid from table

select saleid from ( select s3.saleid,sum((s4.price - s4.price s4.discount/100) s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid group by s3.saleid having sum((s4.price - s4.price s4.discount/100) s3.quantity) > ( select avg(sum((s6.price - s6.price s6.discount/100) s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid ))

step 5: selecting sid according to saleid

select s2.sid from sale s2 where s2.saleid in( select saleid from ( select s3.saleid,sum((s4.price - s4.price s4.discount/100) s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid group by s3.saleid having sum((s4.price - s4.price s4.discount/100) s3.quantity) > ( select avg(sum((s6.price - s6.price s6.discount/100) s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid )))

step6: final query with all the data asked

select s1.sid,s1.sname,s1.location from salesman s1 where s1.sid in ( select s2.sid from sale s2 where s2.saleid in( select saleid from ( select s3.saleid,sum((s4.price - s4.price s4.discount/100) s3.quantity) from saledetail s3 inner join product s4 on s3.prodid = s4.prodid group by s3.saleid having sum((s4.price - s4.price s4.discount/100) s3.quantity) > ( select avg(sum((s6.price - s6.price s6.discount/100) s5.quantity)) from saledetail s5 inner join product s6 on s5.prodid = s6.prodid group by s5.saleid ))))

[github-actions[bot]]

Message that will be displayed on users' first issue