Consider the following problem.

 

You are interested in organizing a chess tournament. This is a big money opportunity

so if you do it right you can retire a wealthy person. You're also in for the art of it, so you

decide to make it a memorable eventby inviting big names, and scheduling the events across

the nation, in its finest venues, biggest halls, most exquisite establishments.

 

Here's the kind of data you will have to store:

 

Round one:

 

Jordan – Barkley 1-0 (Soldier Field)

Miller – Bird 0-1 (Madison Square Garden)

Kukoc – Duncan ½ - ½ (Wrigley Field)

 

Statistics for round one:

 

Soldier Field’s capacity is: 2,800 seats. The game between Jordan and Barkley was watched

By 100 students from IU Bloomington and 120 from the University of Illinois. The price of the ticket was $5.50 and the cost of renting Soldier Field per game is $800.

 

Madison Square Garden’s capacity is: 4,000 seats. It rents for $1,200 per game. For the Miller vs. Bird game, the price of the ticket was $8.00 and in audience were 140 students from the University of Illinois and 60 from Purdue University. (Purdue is a little known University in West Lafayette, IN).

 

Wrigley Field tickets were $4.25 for the Kukoc – Duncan game. The lease was $800.00 and capacity is 1,200. There were 20 students from the University of Minnesota and 220 from the University of Michigan.

 

Round two:

 

Duncan – Barkley ½ - ½ (Woodburn Hall)

Kukoc – Bird 0-1 (Staples Center)

Miller – Jordan ½ - ½ (RCA Dome)

 

Woodburn Hall has a capacity of 2,000 and for the Duncan – Barkley game 80 tickets have been sold to the University of Minnesota and 300 to the University of Michigan. Ticket price at this game was $6.50 and the rent is $600.00 per game.

 

The Staples Center rents 3,200 seats for $500.00 per game and the tickets at the Kukoc Bird game were $5.50 each. University of Iowa was the only one to buy tickets to this game, 180 of them.  

 

The RCA Dome has 1,800 seats and rents for $230.00. Tickets at the Miller-Jordan game were $5.75 each. Purdue bought 120, and IU bought 210.

 

Round three:

 

Bird – Jordan 1- 0 (Rawles Arena)

Kukoc – Barkley 1-0 (Soldier Field)

Duncan – Miller ½ - ½  (Madison Square Garden)

 

Rawles Arena rents for $740.00 and has a capacity of 3,400. 150 tickets have been sold to IU Bloomington and 200 to Iowa. Tickets were $7.35 at the Bird-Jordan game.

 

180 tickets ($8.00 apiece) have been sold to the University of Illinois for the Kukoc – Barkley game.

 

 

Tickets at the Duncan – Miller game were $9.99 each and were sold as follows: 120 to the University of Minnesota and 40 to the University of Illinois.

 

Round four:

 

Miller – Barkley ½ - ½ (Wrigley Field)

Duncan – Bird 0-1 (Woodburn Hall)

Jordan – Kukoc 1-0 (Staples Center)

 

The ticket at the Miller – Barkley match was $9.99 and Purdue bought 200 tickets.

 

The ticket at the Duncan – Bird match was $8.50 (120 – Iowa, and 80 – Purdue).

 

Jordan – Kukoc sold tickets worth $12.00 each (60 went to IU, 60 to the University of Minnesota, and 240 to the University of Michigan).

 

You need to design a database to store this data in it.

 

You need to store addresses for the players, venues, and customers, as well.

 

Example:

 

players

username

first name

last name

address

city

state

rate per match

cbarkley

Charles

Barkley

Sir Charles Avenue

Phoenix

AZ

$450.00

lbird

Larry

Bird

Freedom Lick Rd.

Naples

FL

$350.00

mjordan

Michael

Jordan

Jordan Air Way

Chicago

IL

$500.00

rmiller

Reggie

Miller

Miller Time Road

Indianapolis

IN

$150.00

tduncan

Tim

Duncan

Duncan at the Tim

Houston

TX

$210.00

tkukoc

Toni

Kukoc

Petar Zrinski Street

New Zagreb

PA

$50.00

 

 

spectators

customer id

logo (picture)

address

city

state

Illinois

 

 

 

IL

Iowa

 

 

 

IO

IU Bloomington

 

 

 

IN

Michigan

 

 

 

MI

Purdue University

 

 

 

IN

Stanford University

 

 

 

CA

U of Minnesota

 

 

 

MN

 

 

venues

location

address

city

state

leasing rate

capacity

Madison Square Garden

Garden Center at the City

New York

NY

$1,200.00

4000

Rawles Arena

Math Magic Alley

Las Vegas

NV

$740.00

3400

RCA Dome

Hoosier Center Avenue

Indianapolis

IN

$230.00

1800

Soldier Field

One Soldier Field Avenue

Chicago

IL

$800.00

2800

Staples Center

One Big Office Supplies Ave.

Los Angeles

LA

$500.00

3200

Woodburn Hall

Woodburn Avenue

Atlanta

GA

$600.00

2000

Wrigley Field

Wrigley Shore Drive

Chicago

IL

$800.00

1200

 

 

You then need to write the following queries in SQL:

 

1. List all the players and their hometowns and rates.

2. List all the locations available for all events with the leasing rate.

List locations even if no games are scheduled at that location yet.

3. List all the players that charge more than $250 per match.

4. List all the matches whose ticket prices are above $7.00

5. List all the matches with number of tickets sold (per match).

6. List the most expensive player(s)?

7. What's the venue with the smallest capacity?

8. List all the matches, number of tickets sold, and the capacity of the venue for each game.

9. List all the matches and the percentage of seats they sold.

What's the match that had the least percent of seats sold?

10. What is the total cost of the players (per round)?

11. What's the average cost of player (per match)?

(Does it matter that the roster could be bigger than the invited players?)

12. Which players cost above average?

(Take the average from the prices listed in players).

13. List the number of players that cost above average.

14. List all universities and the number of games they bought tickets to.

(How would you identify the universities that bought tickets to ALL/NONE of the games?)

15. List the games attended by Indiana University students.

16. List the average attendance of Indiana Univ. students per game.

17. List the total amount of money IU students paid for tickets.

(Watch out, the query I gave you might just be slightly wrong.)

18. Calculate the entire amount spent on players up to (and including) round four (not five).

19. Calculate the entire amount collected on tickets sold to matches up to (and including) round four.

(What if the question were to ask up to an earlier round, or selected rounds?)

20. Calculate the total amount spent on leasing the rooms.

21. Calculate the net profit up to (and including) round four.

(Difference between income from tickets and the sum of expenses with players and rent).

22-23. Calculate the profit per match and round (two queries).

24-25. Calculate the average profit per match and round (two queries).

26-27. List the matches and rounds with an above average profit (two queries).

28. How many students saw Bird playing?

(How many tickets were sold for games he was in?)

29. Who was the most watched player?

30. How much money did each player bring?

Sort the result of this query descending by amount and ascending by player's name (alphabetically, that is).

31. List the standings after the fifth round.

32. Which matches only had IN students in the audience?

(Purdue is in Indiana as is IU).

33. Which University did not purchase tickets to any game?

34. Which University did not purchase tickets to the Bird-Jordan game?

35. List the Universities sorted by the number of games they purchased tickets to.