Help me with PL/SQL please!!! Following is the script: Please help us using SQL

Question

Help me with PL/SQL please!!! Following is the script:

Please help us using SQL on:

1. Give the max number of unique parts supplied by every supplier

2. Give the subtotals of the total part weights of the all the parts supplied by a supplier and give the

final total weight

And using PL/SQL:

1. Give the top 2 and bottom 2 parts based on their quantity.

5. Give the supplier and part that come from the same city

10. Give the suppliers that are not from the city from which smith is.

15. Give the total quantity supplied by each supplier and his name

20. Give the supplier name that supplies the minimum quantity of parts

25. Give the max number of unique parts supplied by every supplier

30. Give the subtotals of the total part weights of the all the parts supplied by a supplier and give the

final total weight

Thank you very much

Explanation / Answer

Q)Please help us using SQL on:

1. Give the max number of unique parts supplied by every supplier

select max(distinct QTY), S# from SP group by S#;

Output:

max(distinct QTY) S#

400 S1

400 S2

200 S3

400 S4

Counting the max number of unique parts supplied by every supplier using distinct and max.

2. Give the subtotals of the total part weights of the all the parts supplied by a supplier and give the final total weight.

select (P.weight*SP.qty),P.P#, SP.S#

from P inner join SP where P.P#=Sp.P# group by P.P#, SP.S#;

We listed the subtotals of the total part weights of the all the parts supplied by a supplier:

Output:

(P.weight*SP.qty) P# S#

3600 P1 S1

3600 P1 S2

3400 P2 S1

6800 P2 S2

3400 P2 S3

3400 P2 S4

6800 P3 S1

2800 P4 S1

4200 P4 S4

1200 P5 S1

4800 P5 S4

1900 P6 S1

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.