SELECT City With Most Students-CodePudding

I need to show which city has the most students. Display city and state, and the number of students. I have this so far, which gives me the maximum number of students. I cannot figure out how to add the city and state to the SELECT statement as I feel like I'm going in circles when I add another group by in the outer select.

SELECT MAX(num_student)
  FROM ( SELECT z.City, z.State, COUNT(s.Student_Id) AS num_student
           FROM Student s
          INNER JOIN ZipCode z ON s.Zip = z.Zip
          GROUP BY z.City, z.State
          ORDER BY num_student DESC);

with the folllowing DDL and DML statements :

CREATE TABLE STUDENT
 (STUDENT_ID NUMBER(8,0) 
 ,SALUTATION VARCHAR2(5)
 ,FIRST_NAME VARCHAR2(25)
 ,LAST_NAME VARCHAR2(25) 
 ,STREET_ADDRESS VARCHAR2(50)
 ,ZIP VARCHAR2(5) 
 ,PHONE VARCHAR2(15)
 ,EMPLOYER VARCHAR2(50)
 ,REGISTRATION_DATE DATE 
 ,CREATED_BY VARCHAR2(30) 
 ,CREATED_DATE DATE 
 ,MODIFIED_BY VARCHAR2(30) 
 ,MODIFIED_DATE DATE 
 );

CREATE TABLE ZIPCODE
 (ZIP VARCHAR2(5) 
 ,CITY VARCHAR2(25)
 ,STATE VARCHAR2(2)
 ,CREATED_BY VARCHAR2(30) 
 ,CREATED_DATE DATE 
 ,MODIFIED_BY VARCHAR2(30) 
 ,MODIFIED_DATE DATE 
 );

BEGIN
INSERT INTO student VALUES (102,'Mr.','Fred','Crocitto','101-09 120th St.','11419','718-555-5555','Albert Hildegard Co.','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (103,'Ms.','J.','Landry','7435 Boulevard East #45','07047','201-555-5555','Albert Hildegard Co.','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO student VALUES (104,'Ms.','Laetia','Enison','144-61 87th Ave','11435','718-555-5555','Albert Hildegard Co.','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO student VALUES (105,'Mr.','Angel','Moskowitz','320 John St.','07024','201-555-5555','Alex. & Alexander','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO student VALUES (106,'Ms.','Judith','Olvsade','29 Elmwood Ave.','07042','201-555-5555','Allied Corp.','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO student VALUES (107,'Ms.','Catherine','Mierzwa','22-70 41st St.','11105','718-555-5555','Amer.Contract Desgn.','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO student VALUES (108,'Ms.','Judy','Sethi','Stratton Hall','02155','617-555-5555','Amer.Contract Desgn.','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (109,'Mr.','Larry','Walter','38 Bay 26th ST. #2A','11214','718-555-5555','Amer.Health Found.','22-JAN-07','BROSENZWEIG',TO_DATE('19-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (110,'Ms.','Maria','Martin','1674 Woodbine St.','11385','718-555-5555','The Stock Exchange','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO student VALUES (111,'Ms.','Peggy','Noviello','155 Union Ave #211','07070',NULL,'The Stock Exchange','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO student VALUES (112,'Mr.','Thomas','Thomas','501 W Elm St.','07036','201-555-5555','The Stock Exchange','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO student VALUES (113,'Mr.','Anil','Kulina','43-44 Kissena Blvd. #155','11355','718-555-5555','ARFBO','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO student VALUES (114,'Ms.','Winsome','Laporte','268 E. 3rd St','11226','718-555-5555','ARFBO','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (117,'Mr.','N','Kuehn','44-25 59th St.','11377','718-555-5555','Beauty Products','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO student VALUES (118,'Ms.','Hiedi','Lopez','168 Rowayton Ave','06853','203-555-5555','Banque de Paris','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO student VALUES (119,'Mr.','Mardig','Abdou','160-04 32nd Ave.','11358','718-555-5555','Raymond Capital','25-JAN-07','BROSENZWEIG',TO_DATE('22-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('25-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (120,'Mr.','Ralph','Alexander','2054 73rd St','11214','718-555-5555','Raymond Capital','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO student VALUES (121,'Ms.','Sean','Pineda','3 Salem Rd.','10956','212-555-5555','Burke & Co.','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (122,'Ms.','Julita','Lippen','51-76 Van Kleeck St.','11373','718-555-5555','Burke & Co.','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO student VALUES (123,'Mr.','Pierre','Radicola','322 Atkins Ave.','11208','718-555-5555','Burke & Co.','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO student VALUES (124,'Mr.','Daniel','Wicelinski','27 Brookdale Gdns.','07003','201-555-5555','Burke & Co.','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO student VALUES (127,'Mr.','Gary','Aung','135-32 Louis Blvd','11413','718-555-5555','New York Pop','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (128,'Mr.','Jeff','Runyan','109-15 Queens Blvd.','11375','718-555-5555','New York Pop','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO student VALUES (129,'Mr.','Omaira','Grant','1065 Vermont St. 7F.','11207','718-555-5555','New York Pop','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO student VALUES (130,'Ms.','Lula','Oates','11A Emory St.','07304','201-555-5555','New York Pop','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO student VALUES (133,'Mr.','James','Reed','109-62 196th St','11412','718-555-5555','New York Pop','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO student VALUES (134,'Ms.','Angela','Torres','509 2nd St #4L','11215','718-555-5555','New York Pop','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO student VALUES (135,'Ms.','Michelle','Masser','379 Ovington Ave','11209','718-555-5555','New York Pop','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO student VALUES (136,'Ms.','Hazel','Lasseter','9720 57th Ave #10G','11368','718-555-5555','DUCCA','27-JAN-07','BROSENZWEIG',TO_DATE('24-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'BROSENZW',TO_DATE('27-JAN-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  

INSERT INTO zipcode VALUES ('00914','Santurce','PR','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('01247','North Adams','MA','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO zipcode VALUES ('02124','Dorchester','MA','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO zipcode VALUES ('02155','Tufts Univ. Bedford','MA','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO zipcode VALUES ('02189','Weymouth','MA','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('02563','Sandwich','MA','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06401','Ansonia','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06455','Middlefield','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO zipcode VALUES ('06483','Oxford','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO zipcode VALUES ('06520','New Haven','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO zipcode VALUES ('06605','Bridgeport','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO zipcode VALUES ('06798','Woodbury','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06820','Georgetown','WV','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO zipcode VALUES ('06830','Greenwich','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO zipcode VALUES ('06850','Norwalk','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06851','Norwalk','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06853','Rowayton','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06870','Old Greenwich','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06877','Ridgefield','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));   
INSERT INTO zipcode VALUES ('06880','Westport','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06883','Weston','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO zipcode VALUES ('06897','Wilton','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS')); 
INSERT INTO zipcode VALUES ('06902','Stamford','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06903','Stamford','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06905','Stamford','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('06907','Stamford','CT','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('24-NOV-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07002','Bayonne','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07003','Bloomfiel','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO zipcode VALUES ('07006','North Caldwell','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO zipcode VALUES ('07008','Carteret','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07009','Cedar Grove','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO zipcode VALUES ('07010','Cliffside Park','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));    
INSERT INTO zipcode VALUES ('07011','Clifton','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07012','Clifton','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07014','Clifton','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07016','Cranford','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07021','Essex Fells','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));  
INSERT INTO zipcode VALUES ('07023','Fanwood','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07024','Ft. Lee','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07029','Harrison','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07030','Hoboken','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07034','Lake Hiawatha','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO zipcode VALUES ('07035','Lincoln Pk.','NJ','AMORRISO',TO_DATE('03-AUG-2007 00:00:00','DD-MON-YYYY HH24:MI:SS'),'ARISCHER',TO_DATE('18-NOV-2007 02:30:00','DD-MON-YYYY HH24:MI:SS'));    
END;
/

CodePudding user response：

You don't need a subquery, just a FETCH clause at the bottom of the statement, if your DB is at least of version 12c by using such a statement

SELECT z.City, z.State, COUNT(s.Student_Id) AS num_student
  FROM Student s
  JOIN ZipCode z
    ON s.Zip = z.Zip
 GROUP BY z.City, z.State
 ORDER BY num_student DESC
 FETCH FIRST 1 ROW WITH TIES

WITH TIES option brings more than one row if there are multiple cities with equal population while they have the maximum number of students.

Edit : If you don't want to use FETCH clause as commented or DB's version is prior to 12(provided at least enough to use DENSE_RANK()), then use DENSE_RANK() analytic function to bring results while catching the ties such as

SELECT City, State, num_student
  FROM (SELECT z.City, z.State, 
               COUNT(s.Student_Id) AS num_student,
               DENSE_RANK() OVER (ORDER BY COUNT(s.Student_Id) DESC) AS dr
          FROM Student s
          JOIN ZipCode z
            ON s.Zip = z.Zip
         GROUP BY City, State) 
 WHERE dr  = 1