Using the schema and data below:
| GiftOptions | ||
| ID | Name | Cost |
| 1 | Ghirardelli Chocolates | 15 |
| 2 | Roses | 100 |
| 3 | Card | 5 |
| 4 | Hershey Chocolates | 10 |
| 5 | Tulips | 50 |
| People | |
| ID | Name |
| 1 | Emily Dickinson |
| 2 | Edgar Allan Poe |
| 3 | Walt Whitman |
| 4 | Maya Angelou |
| 5 | William Shakespeare |
| GiftHistory | |||
| FromPersonID | ToPersonID | GiftID | Year |
| 1 | 4 | 3 | 2019 |
| 2 | 4 | 3 | 2019 |
| 3 | 1 | 1 | 2018 |
| 4 | 5 | 5 | 2018 |
| 5 | 3 | 2 | 2019 |
| 5 | 1 | 3 | 2019 |
Provide the data that answers the question AND the SQL used to answer the question. Each query should include at least one join. (HINT: Create a database to test your code works and it will give you the data that answers the question)
- What are the Names of everyone that gave a gift in 2019?
SELECT distinct Name from People join GiftHistory on ID=FromPersonID where Year=2019
- List the Names of all the gifts and the Person ID for who received the gift if that given has been given. Not all gifts have been given, but all gift should be displayed in the results. select distinct Name, GiftHistory.ToPersonID from giftOptions left join
GiftHistory on GiftHistory.GiftID=GiftOptions.ID
- List all the gifts that cost less than 40 and were given by people with an ID that is not equal to 1.
select Name as Gifts from GiftHistory join GiftOptions on GiftID=ID where
Cost < 40 group by GiftID having FromPersonID != 1
- Who has never received a gift? select Name from People where People.ID not in (select distinct ToPersonID from GiftHistory)
- What gift has never been given? select Name from GiftOptions where ID not in (select distinct GiftID from
GiftHistory)
- How much did the person with and ID = 5 spend in year = 2019?
SELECT SUM(Cost) FROM [GiftHistory] join GiftOptions on
GiftHistory.GiftID=GiftOptions.ID where Year=2019 and FromPersonID=5
- How many gifts has Maya Angelou received? select count(*) from GiftHistory join People on ToPersonID=People.ID group by People.Name having Name=Maya Angelou
Reviews
There are no reviews yet.