Help with the query write out please! Really need 100% to pass my class!

The goal of the project is to give you a limited experience in designing and implementing a database application.

City Boomers is a professional soccer team. City Boomers Database will keep track of the following:

• For each game, gameid (unique), as well as game date and game opponent
• For each sales transaction, stid (unique) and the payment method
• For each sales person, spid (unique) and spname
• For each merchandise, mid (unique), mtype and mprice
• For each ticket, ticketid (unique), price, seat type, and payment method
• For each agent, aid (unique), and agent name
• For each advertiser, companyid (unique), and company name
• Each sales transaction involves one sales person
• Each sales person participates in between zero and many sales transactions
• Each sales transaction includes one or more merchandise
• Each merchandise is included in one or more transactions
• For every instance of a merchandise included in a transaction, we keep track of the quantity of that merchandise in that transaction
• Each sale transaction occurs in one game
• Each game has one or more sales transactions occurring during it
• Each ticket is sold for exactly one game
• Each game has between zero and many tickets sold
• We keep track of the number of tickets sold for each game
• Each ticket is sold by one or no agents
• Each agent sells between zero and many tickets
• Each game has between zero and many advertisers promoting at it
• Each advertiser promotes at between zero and many games
• For each instance of an advertiser promoting at a game, we keep track of the fee charged to that advertiser to promote at that game
• Each advertiser is a competitor with between zero and many other advertisers
• Each advertiser has between zero and many other advertisers as competitors

Data for the City Boomers Database:

Two salespersons:

• Mallory
• Patrick

Two agents:

• Bryce
• Olivia

Five different types of merchandise for sale:

• Jersey, cost: $100.00 each
• Souvenir Ball, cost: $40.00 each
• Water Bottle, cost: $15.00 each
• T-Shirt, cost: $25.00 each
• Autographed Picture, cost: $75.00 each

Five different advertisers:

• Koma Cola, pays $200.00 per game
• City Hardware, pays $250.00 per game
• Showtime Grill, pays $150.00 per game
• NAC Beverages, pays $300.00 per game
• Day Street Diner, pays $100.00 per game

Payment method, two types:

• Credit Card
• Cash

Two games:

• 07-Mar-20 the home team Zoomers play the Loomers
  • The fllowing advertisers pay for advertisement at this game:
    • Koma Cola
    • Showtime Grill
    • NAC Beverages
  • The fllowing merchandise and the number of items were sold at this game by Mallory
    • Souvenir Ball, 2, paid by Credit Card
    • Water Bottle, 3, paid by Credit Card
    • Autographed Picture, 1, paid by Credit Card
  • The fllowing merchandise and the number of items were sold at this game by Patrick
    • Souvenir Ball, 2, paid by Credit Card
• 14-Mar-20 the home team Loomers play the Zoomers
  • The fllowing advertisers pay for advertisement at this game:
    • Koma Cola
    • NAC Beverages
    • Day Street Diner
  • The fllowing merchandise and the amount were sold at this game by Patrick
    • T-Shirt, 5, paid by cash
    • Souvenir Ball, 1, paid by cash

Nine tickets sold:

• Sideline for $150.00, Credit Card, Game on the 07-Mar-20, Agent is Bryce
• Lower Bowl for $100.00, Cash, Game on the 07-Mar-20
• Upper Deck for $50.00, Credit Card, Game on the 07-Mar-20, Agent is Bryce
• Upper Deck for $50.00, Credit Card, Game on the 07-Mar-20
• Suite for $250.00, Cash, Game on the 14-Mar-20, Agent is Bryce
• Sideline for $150.00, Credit Card, Game on the 14-Mar-20, Agent is Olivia
• Lower Bowl for $100.00, Credit Card, Game on the 14-Mar-20
• Lower Bowl for $100.00, Cash, Game on the 14-Mar-20
• Upper Deck for $50.00, Credit Card, Game on the 14-Mar-20, Agent is Olivia

Implementation:

The database design should be implemented using draw.io or similar diagraming program and MS SQL Server.

The deliverables include:

1. Normalization Diagram (20%) Create a 1NF, 2NF and 3NF diagram. A 2NF diagram may not be needed.

2. E-R diagram (20%) Create an ERD or EERD from the Normalization Diagram in step 1.

3. Data Definition Language (DDL)/Data Manipulation Language (DML) (20%):

• Create tables statements and insert statements.

4. SQL reports (40%) SQL code and copies of the output for each of the following situations:

17. 1. What is the sum of ticket sales for both games sold by agent Bryce?

Query goes here

Screen Shot goes here

17. 2. What is the sum of ticket sales for both games sold by neither Bryce nor Olivia?

Query goes here

Screen Shot goes here

17. 3. List the location of seats that were not sold for the game on March 7, 2020.

Query goes here

Screen Shot goes here

17. 4. Write a query to list the type of merchandise and price for all merchandise with a price lower than the average price of all the merchandise.

Query goes here

Screen Shot goes here

17. 5. List the game date once and the total amount of merchandise sold at each game, include the total amount in a separate row.

Query goes here

Screen Shot goes here

17. 6. Building on 5 add the amount spent on advertising for each game and the total in a separate row. (EXTRA CREDIT + 10 POINTS)

Query goes here

Screen Shot goes here