Please do it with PL/SQL, not regular SQL Please help us using PL/SQL: 1. Give t
ID: 3915187 • Letter: P
Question
Please do it with PL/SQL, not regular SQL
Please help us 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
Explanation / Answer
1 Ans:
BEGIN
DECLARE @temp TABLE(
[P#] varchar(2) NOT NULL,
[PNAME] [varchar](5) NULL,
[COLOR] [varchar](5) NULL,
[WEIGHT] [float] NULL,
[CITY] [varchar](6) NULL,
QTY VARCHAR(10))
INSERT INTO @temp SELECT DISTINCT TOP 2 P.P#,P.PNAME,P.COLOR,P.WEIGHT,P.CITY,SP.QTY FROM SP JOIN P ON P.P#=SP.P# ORDER BY QTY DESC
INSERT INTO @temp SELECT DISTINCT TOP 2 P.P#,P.PNAME,P.COLOR,P.WEIGHT,P.CITY,SP.QTY FROM SP JOIN P ON P.P#=SP.P# ORDER BY QTY ASC
SELECT * FROM @temp
END
5. ANS:
BEGIN
SELECT P.P#,p.PNAME,P.CITY FROM SP JOIN P ON SP.P#=P.P# GROUP BY P.CITY,P.P#,p.PNAME
END
10. Ans:
BEGIN
SELECT SNAME,QTY,CITY from sp join s on sp.S#=s.S# AND S.CITY<>(SELECT TOP 1 CITY FROM S WHERE SNAME='Smith') group by CITY,SNAME,QTY
END
15. ANS :
BEGIN
SELECT SNAME,SUM(QTY) AS QTY from sp join s on sp.S#=s.S# group by SNAME
END
20. ANS:
BEGIN
SELECT DISTINCT S.S#,S.SNAME,S.STATUS,S.CITY FROM SP JOIN P ON P.P#=SP.P# JOIN S ON SP.S#=S.S# WHERE SP.QTY IN(SELECT TOP 1 QTY FROM SP ORDER BY QTY ASC)
END
25. AND:
BEGIN
SELECT TOP 1 * FROM (SELECT SP.S#,SUM(SP.QTY)AS QTY FROM SP JOIN P ON SP.P#=P.P# GROUP BY SP.S#) AS TEMP ORDER BY QTY DESC
END
30. ANS:
BEGIN
DECLARE @temp1 TABLE(
S# VARCHAR(50),
[WEIGHT] [float] NULL)
INSERT INTO @temp1 SELECT SP.S#,SUM(P.WEIGHT)AS WEIGHT FROM P JOIN SP ON SP.P#=P.P# GROUP BY SP.S#
INSERT INTO @temp1 SELECT 'GRAND TOTAL ',SUM(WEIGHT) FROM @temp1
SELECT * FROM @temp1
END