/**********QUERIES**********/
#1. Select all records from each table - Label Q1a-Q1i
select * from Agents;
select * from Bikes;
select * from Events;
select * from Participation;
select * from Races;
select * from Rider_Sponsorship;
select * from Riders;
select * from Sponsors;
select * from Teams;

#2. List all of your constraints in the database one table at a time. - Label Q2a-Q2i
select * from information_schema.table_constraints
where table_name = 'Agents';
select * from information_schema.table_constraints
where table_name = 'Bikes';
select * from information_schema.table_constraints
where table_name = 'Events';
select * from information_schema.table_constraints
where table_name = 'Participation';
select * from information_schema.table_constraints
where table_name = 'Races';
select * from information_schema.table_constraints
where table_name = 'Rider_Sponsorship';
select * from information_schema.table_constraints
where table_name = 'Riders';
select * from information_schema.table_constraints
where table_name = 'Sponsors';
select * from information_schema.table_constraints
where table_name = 'Teams';

#3. List all of your table names in the database. - Label Q3
show tables from EMBRL;

#4. List the Columns and data types of each table - Label Q4a-Q4i
describe Agents;
describe Bikes;
describe Events;
describe Participation;
describe Races;
describe Rider_Sponsorship;
describe Riders;
describe Sponsors;
describe Teams;

#5. RIDER - POINT TOTALS BY RACE LEVEL LISTING List the Rider's Name, RaceLevel as
#   Race_Level and the total number of all points based on their placement. Make sure that you don't
#   list any riders who have not raced in any races yet (not placed yet). Sort the data from highest to
#   lowest total points. I need a listing for the easy races, one for the intermediate races and one for
#   the advanced races. - Label as Q5a-Q5c.
select Riders.FirstName, Riders.LastName, Races.RaceLevel as Race_Level, sum(Participation.Placement) as Points
from Riders, Races, Participation
where Riders.RiderID = Participation.RiderID
    and Races.RaceID = Participation.RaceID
    and Races.RaceLevel = 'Easy'
    and Participation.Placement is not null
group by Riders.RiderID
order by Points desc;

select Riders.FirstName, Riders.LastName, Races.RaceLevel as Race_Level, sum(Participation.Placement) as Points
from Riders, Races, Participation
where Riders.RiderID = Participation.RiderID
    and Races.RaceID = Participation.RaceID
    and Races.RaceLevel = 'Intermediate'
    and Participation.Placement is not null
group by Riders.RiderID
order by Points desc;

select Riders.FirstName, Riders.LastName, Races.RaceLevel as Race_Level, sum(Participation.Placement) as Points
from Riders, Races, Participation
where Riders.RiderID = Participation.RiderID
    and Races.RaceID = Participation.RaceID
    and Races.RaceLevel = 'Advanced'
    and Participation.Placement is not null
group by Riders.RiderID
order by Points desc;

#6. SEARCHING FOR A RUN
#   List the EventName and the RaceName of any records that have the word "Run" in the Race
#   Name. – Label as Q6
select Events.EventName, Races.RaceName
from Events, Races
where Races.RaceName like '%run%'
    and Events.EventID = Races.EventID;

#7. RIDER - TEAM - AGENT BY RACE DATE LISTING
#   List the Rider's Full Name AS "Rider_Name", The Rider's Team Name and their Agent's Full
#   Name listed AS "Agent_Name" .Of those riders only list those who have or are schedule to
#   participate in any races during April 2008 and, make sure that the riders information is only listed
#   once. - Label as Q7
select concat_ws(' ',Riders.FirstName, Riders.LastName) as Rider_Name, Teams.TeamName,
        concat_ws(' ', Agents.FirstName, Agents.LastName) as Agent_Name
from Riders, Teams, Agents, Participation, Races
where Riders.TeamID = Teams.TeamID
    and Riders.AgentID = Agents.AgentID
    and Riders.RiderID = Participation.RiderID
    and Races.RaceID = Participation.RaceID
    and month(Races.RaceDate) = 4
    and year(Races.RaceDate) = 2008
group by Riders.RiderID;

#8. SUBQUERY
#   List the Sponsor Name of any sponsors who sponsored both Riders and Events. – Label as Q8
select Sponsors.SponsorName
from Sponsors, Rider_Sponsorship
where Sponsors.SponsorID = Rider_Sponsorship.SponsorID
and Sponsors.SponsorName in (select Sponsors.SponsorName
                             from Sponsors, Events
                             where Sponsors.SponsorID = Events.SponsorID)
group by Sponsors.SponsorID;