SQL basic
Budget: $30 – $250 USD
5 basic sql questions
1) How many employees are there in SFCO with non-refunded contributions assigned to the
account named ‘Pool’?
2) How much has Amazon ever contributed to the program in non-refunded contributions, and for
how many employees?
3) The Data Management team has created a new mailing list in SQL server called MailingList, so
that SF City Option can send a contribution statement to each program participant who has
funds in the account named ‘Pool.’
a. The mailing list is supposed to include, on one row per-participant: first and last names;
the most recent, valid address; the names of their employers who have contributed
funds in the Pool account; and the sum of contributions made to Pool for the
participant, by employer.
b. The three employers should be in the ‘Employer 1’, ‘Employer 2’ and ‘Employer 3’
columns, in descending order of the sum of contributions they have made; and the sums
of contributions should be in the same order. See the MailingList table excerpt below.
Write a query to check whether MailingList meets all these requirements. (If it turns out not to
be correct, you do not need to identify why in this query.)
4) How much money in Pool has been contributed for people whose first Pool contribution was
a. in the last year,
b. between 1 and 2 years ago, or
c. more than 2 years ago?
5) Identify all the Unique Participants who have received no contributions in the last 36 months, or
whose total contributions to date are less than $250.
1) How many employees are there in SFCO with non-refunded contributions assigned to the
account named ‘Pool’?
2) How much has Amazon ever contributed to the program in non-refunded contributions, and for
how many employees?
3) The Data Management team has created a new mailing list in SQL server called MailingList, so
that SF City Option can send a contribution statement to each program participant who has
funds in the account named ‘Pool.’
a. The mailing list is supposed to include, on one row per-participant: first and last names;
the most recent, valid address; the names of their employers who have contributed
funds in the Pool account; and the sum of contributions made to Pool for the
participant, by employer.
b. The three employers should be in the ‘Employer 1’, ‘Employer 2’ and ‘Employer 3’
columns, in descending order of the sum of contributions they have made; and the sums
of contributions should be in the same order. See the MailingList table excerpt below.
Write a query to check whether MailingList meets all these requirements. (If it turns out not to
be correct, you do not need to identify why in this query.)
4) How much money in Pool has been contributed for people whose first Pool contribution was
a. in the last year,
b. between 1 and 2 years ago, or
c. more than 2 years ago?
5) Identify all the Unique Participants who have received no contributions in the last 36 months, or
whose total contributions to date are less than $250.