How many universities have no less than 20,000 female students in 2016? Identify how many of the said universities are located in the United States of America.
have no less than 20,000 female students refers to DIVIDE(MULTIPLY(pct_female_students, num_students), 100) > 20000; in 2016 refers to year = 2016; located in the United States of America refers to country_name = 'United States of America'
SELECT COUNT(*) , SUM(CASE WHEN T3.country_name = 'United States of America' THEN 1 ELSE 0 END) AS nums_in_usa FROM university AS T1 INNER JOIN university_year AS T2 ON T1.id = T2.university_id INNER JOIN country AS T3 ON T3.id = T1.country_id WHERE T2.year = 2016 AND T2.num_students * T2.pct_female_students / 100 > 20000
CREATE TABLE country
(
    id           INTEGER not null
            primary key,
    country_name TEXT default NULL
);
CREATE TABLE ranking_system
(
    id          INTEGER not null
            primary key,
    system_name TEXT default NULL
);
CREATE TABLE ranking_criteria
(
    id                INTEGER not null
            primary key,
    ranking_system_id INTEGER          default NULL,
    criteria_name     TEXT default NULL,
    foreign key (ranking_system_id) references ranking_system(id)
);
CREATE TABLE university
(
    id              INTEGER not null
            primary key,
    country_id      INTEGER          default NULL,
    university_name TEXT default NULL,
    foreign key (country_id) references country(id)
);
CREATE TABLE university_ranking_year
(
    university_id       INTEGER default NULL,
    ranking_criteria_id INTEGER default NULL,
    year                INTEGER default NULL,
    score               INTEGER default NULL,
    foreign key (ranking_criteria_id) references ranking_criteria(id),
    foreign key (university_id) references university(id)
);
CREATE TABLE university_year
(
    university_id              INTEGER           default NULL,
    year                       INTEGER           default NULL,
    num_students               INTEGER           default NULL,
    student_staff_ratio        REAL default NULL,
    pct_international_students INTEGER           default NULL,
    pct_female_students        INTEGER           default NULL,
    foreign key (university_id) references university(id)
);
