Database

if we have two dataset 1-First dataset contains population data of cities in the United States of America. Each record in the dataset has the following format:
{
     "city": "ACMAR",
     "loc": [
         -86.51557,
         33.584132
     ],
     "pop": 6055,
     "state": "AL",
     "_id": "35004"
}

Second dataset contains log data from a geo-locational service. Each record in the dataset has the following format:
{
    "_id": {
        "$oid": "5191f53b1b76a5666a8cbd64"
    },
    "status": 200,
    "requrest_time": 504,   // in milliseconds
    "type": "stationboard",
    "datetime": {
        "$date": "2014-02-19T07:03:32.000Z"
    }
}

1-how we can Get all the states that have at least 2 cities with population > 1000000 (1M).and we need to  Remember that city can be splitted into multiple records, and we should aggregate its population first. (zips dataset)

2- we need Find a day (date) with the largest number of HTTP 500 errors (status=500).

if we have two dataset 1-First dataset contains population data of cities in the United States of America. Each record in the dataset has the following format:
{
     "city": "ACMAR",
     "loc": [
         -86.51557,
         33.584132
     ],
     "pop": 6055,
     "state": "AL",
     "_id": "35004"
}

Second dataset contains log data from a geo-locational service. Each record in the dataset has the following format:
{
    "_id": {
        "$oid": "5191f53b1b76a5666a8cbd64"
    },
    "status": 200,
    "requrest_time": 504,   // in milliseconds
    "type": "stationboard",
    "datetime": {
        "$date": "2014-02-19T07:03:32.000Z"
    }
}

1-how we can Get all the states that have at least 2 cities with population > 1000000 (1M).and we need to  Remember that city can be splitted into multiple records, and we should aggregate its population first. (zips dataset)

2- we need Find a day (date) with the largest number of HTTP 500 errors (status=500).

select c.customer_id ,c.father_name ,c.address,c.phone_number ,c.mobile_number ,c.id_proof ,c.area ,c.ip_address as 'IP Address',c.mac_address as 'MAC Address',c.package_type as 'Package Type',c.name as Name,c.activation_date as 'Activation Date',c.status as 'Status',c.installation_cost as 'Installation Cost',c.totalamount_paid as 'Total Amount Paid',c.monthly_amount as 'Monthly Amount',c.lastpaid_date as 'Last Paid Date',c.lastpaid_amount as 'Last Paid Amount',c.nextpay_date as 'Next Pay Date',c.totaldue_amount as 'Total Due Amount',t.agent_id as 'Agent ID',t.token_number as 'Token Number',t.machine_id as 'Machine ID' from customer c,transaction t where c.customer_id=t.customer_id and DATE(t.paid_date)BETWEEN '2014-02-02'AND '2014-04-02' ;