QUIZ: SQL Data Design & Data Tables

quiz, sql, queries, data, design, tables
  1. List the amount pledged per lap in one fundraiser, broken down by classroom and student. The output should list {classroom, student, amount} (Remember; pledge per flat donation can be easily transformed into pledge per lap by dividing the amount / 35.)

Solution:

Select  Fundraiser_Unique_Code , Room#  ,Student_ First_Name From Fundraiserdim Table Have Inner Join With Pledge Fact  And Get Pledge_Amount  ,     Inner Join With Participant Fact Where There Exists Classroomid , Student_Id

Participantfact Have Inner Join Classromdim    And Studentdim  Using Student_Id 

  1. List the amount pledged per lap and the pledge amount paid for all fundraiser in a particular school for the years 2013 to 2015. The output should list {year, amount pledged per lap}

Solution:

select pledge amount , pledge paid from  FUNDRAISERDIM inner join  pledge_fact  JOIN and   where   TIMEDIM. time_year between 2013 >= 2015 && 2015 <= 2013

 

  1. List the average number of pledges students received in 2015, broken down by school as well as grade level {school, classroom grade, average number of pledges}

Solution:

Select Classroom_Number , Avg(Count(*)) From   Pledgefact  Inner Join  Participant Fact  , Inner Join   ,Participant Fact ,  Inner Join   Classroomdim  ,

Participantfact Inner Join Timedim Where Time_Year =’2015′

  1. List the amount pledged to students in the years 2013 to 2015, broken down by state, district, and year. The output should list {state, district, year, amount}

Solution:

Select State, District, Year, Amount  From Pledgefact     Inner Join   , Timedim Innerjoin Participationfact Inner Join   Fundraiserdim Inner Join   School_Fact   Inner Join Districtdim Where Timedim . Time_Year Between 2013 >= 2015 && 2015 <= 2013

 

  1. List the amount pledge to students for one school for the years 2013 to 2015, broken down by a sponsor. The output should list {sponsor, amount pledged} and be ordered by amount pledged.

Solution:

Select Pledge Amount , Sponser_First_Name From Pledgefact Inner Join Sponsordim On Sponser Key Inner Join Timedim Where Timedim . Time_Year Between 2013 >= 2015 && 2015 <= 2013 Order By Amount_Pledge

 

  1. List the number of students participating in a fundraiser, broken down by classroom. The output should list {classroom, number of student participation}

Solution:

Select Classroom, Number Of Student Participation From Fundraiserdim Inner Join  Participantfact Inner Join Classromdim

 

  1. List the average number of lapses run of participating students, broken down by year, month. The output should list {year, month, average number of lapses run}

Solution:

 

Select Year, Month, Avg( Number Of Lapses Run)  From Participationdim Inner Join With Participation Fact Innerjoin With Timedim ;

LEAVE A REPLY

Please enter your comment!
Please enter your name here