10 Replies Latest reply on Dec 21, 2018 5:30 PM by robiin234

    Help understanding question and what is needed

    robiin234

      Hello i have some sample data and have a question that i need help understanding

       

      7.PNG

       

      I need to create a query using this sample data. The query needs to display country_id and region_id of every country,including the number of locations each has. This should then be outputted in descending order of country_id.

       

      Can someone help me see how my table would look and how i would create this query

       

      Here is the sample data

       

      Tables created

       

      CREATE TABLE  "OEHR_LOCATIONS"

         ( "LOCATION_ID" NUMBER(4,0),

      "STREET_ADDRESS" VARCHAR2(40),

      "POSTAL_CODE" VARCHAR2(12),

      "CITY" VARCHAR2(30) CONSTRAINT "OEHR_LOC_CITY_NN" NOT NULL ENABLE,

      "STATE_PROVINCE" VARCHAR2(25),

      "COUNTRY_ID" CHAR(2),

      CONSTRAINT "OEHR_LOC_ID_PK" PRIMARY KEY ("LOCATION_ID") ENABLE

         )

       

      CREATE TABLE  "OEHR_COUNTRIES"

         ( "COUNTRY_ID" CHAR(2) CONSTRAINT "OEHR_COUNTRY_ID_NN" NOT NULL ENABLE,

      "COUNTRY_NAME" VARCHAR2(40),

      "REGION_ID" NUMBER,

      CONSTRAINT "OEHR_COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE

         ) ORGANIZATION INDEX NOCOMPRESS

      /

      Data inserted into

       

      REM INSERTING into OEHR_LOCATIONS

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1000,'1297 Via Cola di Rie','00989','Roma',null,'IT')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1100,'93091 Calle della Testa','10934','Venice',null,'IT')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1300,'9450 Kamiya-cho','6823','Hiroshima',null,'JP')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1700,'2004 Charade Rd','98199','Seattle','Washington','US')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2000,'40-5-12 Laogianggen','190518','Beijing',null,'CN')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2300,'198 Clementi North','540198','Singapore',null,'SG')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2400,'8204 Arthur St',null,'London',null,'UK')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (3000,'Murtenstrasse 921','3095','Bern','BE','CH')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL')

      /

       

       

      Insert into OEHR_LOCATIONS (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) values (3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX')

      /

       

       

       

       

       

       

       

       

       

       

       

       

      REM INSERTING into OEHR_COUNTRIES

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('AR','Argentina',2)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('AU','Australia',3)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('BE','Belgium',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('BR','Brazil',2)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('CA','Canada',2)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('CH','Switzerland',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('CN','China',3)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('DE','Germany',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('DK','Denmark',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('EG','Egypt',4)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('FR','France',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('HK','HongKong',3)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('IL','Israel',4)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('IN','India',3)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('IT','Italy',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('JP','Japan',3)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('KW','Kuwait',4)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('MX','Mexico',2)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('NG','Nigeria',4)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('NL','Netherlands',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('SG','Singapore',3)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('UK','United Kingdom',1)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('US','United States of America',2)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('ZM','Zambia',4)

      /

       

       

      Insert into OEHR_COUNTRIES (COUNTRY_ID,COUNTRY_NAME,REGION_ID) values ('ZW','Zimbabwe',4)

      /

       

      Pictures of data in tables sql developer latest,  11g express edition

       

      LOL1.PNG2.PNG

       

      Thanks for any help guys, can you help me understand the question and how i create this query with the sample data given.