0

I have written a standard SQL Select Query to select the zip code in which the largest number of sales were. I now need to convert it to an anonymous PL/SQL block, however I'm still very "green" with PL/SQL and really don't have much of an idea as to how to accomplish this. Also, I need to incorporate a LIMIT into the PL/SQL anonymous block that will only display the lowest numeric zip code in the event of a tie.

Here are the tables w/some data:

CREATE TABLE CUSTOMERS
(customerID     INT     PRIMARY KEY,
customerZip     VARCHAR(15) NOT NULL); 

CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
customerID      INT,
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID));

INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (1, '20636');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (2, '20619');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (3, '20670');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (4, '20670');
INSERT INTO CUSTOMERS (customerID, customerZIP) VALUES (5, '20636');

INSERT INTO SALES (saleID, customerID) VALUES (1, 1);
INSERT INTO SALES (saleID, customerID) VALUES (2, 2);
INSERT INTO SALES (saleID, customerID) VALUES (3, 3);
INSERT INTO SALES (saleID, customerID) VALUES (4, 4);
INSERT INTO SALES (saleID, customerID) VALUES (5, 5);

And here's the SQL query I wrote:

SELECT C.customerZip, COUNT (*) AS "MOST_SALES_byZIP"
FROM SALES S
    INNER JOIN CUSTOMERS C
        ON S.customerID = C.customerID
GROUP BY C.customerZip
HAVING COUNT (*) >= ALL
    (SELECT COUNT(*)
        FROM SALES S
            INNER JOIN CUSTOMERS C
                    ON S.customerID = C.customerID
        GROUP BY C.customerZip)
        ORDER BY C.customerZip;

Basically, I first need to know how to "convert" this into a PL/SQL anonymous block. Then, I need to know how I can limit the results to only show the lowest numeric zip code if there is a tie between two or more.

I have an SQL fiddle Schema built here, if it helps: http://sqlfiddle.com/#!4/ca18bf/2

Thank you!

Improve this question
7
  • What do you want the PL/SQL block to do? Commented Feb 8, 2018 at 14:38
  • Basically the same thing that the standard SQL query that I wrote does, but with added functionality to limit the results to the lowest numerical zip code in the event of a tie. I know the query above works fine, but I've been asked to accomplish this via PL/SQL and don't really know how. Commented Feb 8, 2018 at 14:39
  • So you want it to return a ref cursor maybe? Also there won't be any tie because you are already grouping by zip code. You can limit the overall result set with a fetch first n clause. Commented Feb 8, 2018 at 14:45
  • This is where I get lost, as I don't really know the syntax of PL/SQL too well yet. As it stands now, there is a tie w/the standard SQL statement (see SQL fiddle link for an example). Could you provide an example by chance of how you would accomplish this so I have an idea of what you're thoughts are? Sorry for my ignorance... Commented Feb 8, 2018 at 15:00
  • 1
    Asked to accomplish via PL/SQL? Who asked you to do it via PL/SQL, and why? This is easy in plain SQL, and if you need the result in PL/SQL, it should still use the most efficient SQL query to get the value and pass it to PL/SQL; it is wrong, wrong, wrong to do in PS/SQL those parts of a task that properly belong in SQL. Commented Feb 8, 2018 at 15:13

3 Answers 3

Reset to default
1

80% of good PL/SQL programming is good SQL coding.

In your problem: first, in SQL, to select the lowest numeric zip code from among those tied for most sales, you can do a join followed by aggregation by zip code, as you did already - and then use the aggregate LAST function. Like so:

select   min(customerzip) keep (dense_rank last order by count(*)) as selected_zip
from     sales inner join customers using (customerid)
group by customerzip
;

SELECTED_ZIP  
---------------
20636

Now it is easy to use this in an anonymous block (if you have to - for whatever reason). SET SERVEROUTPUT ON is not part of the PL/SQL code; it is a command to the interface program, to instruct it to display the content of the output buffer on screen.

set serveroutput on

declare
  selected_zip integer;
begin
  select   min(customerzip) keep (dense_rank last order by count(*))
    INTO   selected_zip                              -- this is the PL/SQL part!
  from     sales inner join customers using (customerid)
  group by customerzip
  ;
  dbms_output.put_line('Selected zip is: ' || selected_zip);
end;
/

PL/SQL procedure successfully completed.

Selected zip is: 20636
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. I will work off of your feedback to accomplish what I'm after. I'm very thankful for the advice here - and very appreciative of your help in specific. I know you've been guiding many of my posts, providing thoughtful and helpful feedback...Thank goodness for Stack Overflow!
1

Here's an option. Create a function since an anonymous block can only print to STDOUT, it can't return something into a variable

The having clause is remove and simply order by count,zip so that top count wins then top count + top zip based on the order. Added in fetch first 1 rows ONLY to only get the 1 row then returned it from the function.

SQL> CREATE OR REPLACE FUNCTION getlowest RETURN NUMBER AS
        l_ret   NUMBER;
    BEGIN
        FOR r IN (
            SELECT
                c.customerzip,
                COUNT(*) AS "MOST_SALES_byZIP"
            FROM
                sales s
               INNER JOIN customers c ON s.customerid = c.customerid
           GROUP BY
               c.customerzip
           order by
               COUNT(*), c.customerzip
           fetch first 1 rows ONLY
       ) LOOP
           l_ret := r.customerzip;
       END LOOP;

       RETURN l_ret;
   END;
   /
SQL> show errors;
SQL> 
SQL> select getlowest from dual
  2  /
20619
SQL>
Improve this answer

Comments

0

If the aim is to return a result set, then the PL/SQL block to do that would be

-- [Declare the host ref cursor according to the calling tool/language]
-- e.g. in SQL*Plus
var resultset refcursor

begin
    open :resultset for
        select c.customerzip, count(*) as most_sales_byzip
        from   sales s
               join customers c on s.customerid = c.customerid
        group  by c.customerzip
        having count(*) >= all
               ( select count(*) from sales s
                        join customers c on s.customerid = c.customerid
                 group by c.customerzip )
        order by c.customerzip;
end;

From Oracle 12.1 onwards you can use implicit result sets:

declare
    rc sys_refcursor;
begin
    open rc for
        open :resultset for
            select c.customerzip, count(*) as most_sales_byzip
            from   sales s
                   join customers c on s.customerid = c.customerid
            group  by c.customerzip
            having count(*) >= all
                   ( select count(*) from sales s
                            join customers c on s.customerid = c.customerid
                     group by c.customerzip )
            order by c.customerzip;

    dbms_sql.return_result(rc);
end;

However we already have SQL to do this so it seems a bit pointless.

Improve this answer

2 Comments

Thanks! I'll give it a shot. Very helpful - Hopefully I can get this PL/SQL down. It makes it 10x harder when I'm asked to do something that is not logical, but in this circumstance, unfortunately I have no choice.
Thinking about it though, these coursework questions tend to love their loops and dbms_output.put_lines. Perhaps what they are looking for is for r in (select...) loop dbms_output.put_line(r.customerzip); end loop;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.