Year 12 Online

SQL Practice Activity - Nominations Database

1.  Import the emailed tables that have been emailed to you.

2. Answer the following questions in SQL. Test in phpMyAdmin.

• 1.       Display a count next to each sport name of how many students have nominated (all year levels)

• 2.       Display a count next to each sport name of how many Year 7 students have nominated for each sport

• 3.       Display a count next to each sport of how many Year 8 boys across all sports have nominated

• 4.       Sum up all the subscriptions for each sport and display grouped by sport

• 5.       Sum up all subscriptions for all sports

• 6.       Count how many boys and how many girls have nominated for each sport

3. Create a basic graph in JavaScript. See videos below.

1. 

select sports.activity_name,count(sports.activity_id) as COUNT

from nominations

inner join sports on nominations.activity_id = sports.activity_id

group by sports.activity_id

order by sports.activity_name

2.

select sports.activity_name,count(sports.activity_id) as COUNT

from nominations

inner join sports on nominations.activity_id = sports.activity_id

where nominations.year_level = 7

group by sports.activity_id

order by sports.activity_name

3.

select sports.activity_name,count(sports.activity_id) as COUNT

from nominations

inner join sports on nominations.activity_id = sports.activity_id

where nominations.year_level = 8 and nominations.gender like 'male'

group by sports.activity_id

order by sports.activity_name

4.

select sports.activity_name,sum(nominations.subscription) as sum

from nominations

inner join sports on nominations.activity_id = sports.activity_id

group by sports.activity_id

order by sports.activity_name

5.

select sum(nominations.subscription) as total from nominations 

6.

select sports.activity_name,count(nominations.gender) as count

from nominations

inner join sports on nominations.activity_id = sports.activity_id

where nominations.gender like 'female'

group by sports.activity_id

order by sports.activity_name

select sports.activity_name,count(nominations.gender) as count

from nominations

inner join sports on nominations.activity_id = sports.activity_id

where nominations.gender like 'male'

group by sports.activity_id

order by sports.activity_name

<?php

include('connect.php');

//global vars

$year = 0;

//two arrays for graph data

$sports =[];

$count =[];

$title = "<h1>Graph - All</h1>";

//check to see if form submitted and if value is all or a specific year level

if(isset($_REQUEST['year'])&&intval($_REQUEST['year'])!=0){

    $year = intval($_REQUEST['year']); // get year in variable convert to number

    //update title

    $title = "<h1>Graph - ".$year."</h1>";

    //sql with inserted variable year

    $sql = "select sports.activity_name, count(nominations.activity_id) as count

        from nominations

        inner join sports on nominations.activity_id = sports.activity_id

        where nominations.year_level = {$year}

        group by sports.activity_name";

//anything else search all years - no criteria

}else{

    //update title

    $title = "<h1>Graph - All Years</h1>";

    //search all year levels

    $sql = "select sports.activity_name, count(nominations.activity_id) as count

        from nominations

        inner join sports on nominations.activity_id = sports.activity_id

        group by sports.activity_name";

}      

//run the sql query based on sql search above

$results = $conn->query($sql);

while($row=mysqli_fetch_assoc($results)){

    array_push($sports, $row["activity_name"]);

    array_push($count, $row["count"]);

}

//Convert php array into JS array formatting. Needs a php array and output type I=int S= String

//Note that this is a custom function in replacement of using echo json_encode($arr) 

function printArray($arr,$type){

    $output ='[';

    for($i =0;$i<count($arr);$i++){

        if($type == 'I'){

            $output.=$arr[$i].',';

        }else{

            $output.="'".$arr[$i]."',";

        }

    }

    $output.="]";  

    echo $output; //return output to where the function was called

}

?>

<html>

<head>

    <!--Insert to chart.js  -->

<script src="https://cdn.jsdelivr.net/npm/chart.js@2.9.3/dist/Chart.min.js"></script>

</head>

<style>

    /* Set layout area in style for each graph */

    #layout {

        width: 400px;

        height: 400px;

    }

</style>

<body>

<!-- Update the title based in form input -->

<?php echo $title;?>

<!-- Set up a form that submits to the same page we are on using php call server php self -->

<!-- Using a get fucntion which will allow us of directly linking to a graph search -->

<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="get">

<label for ="year">Select Year Level</label>

<select name = "year">

    <!-- These values could be a dynamic drop down list but static in this example -->

    <option value=0 selected>All Year Levels</option>    

    <option value=7>Year 7</option>

    <option value=8>Year 8</option>

    <option value=9>Year 9</option>

    <option value=10>Year 10</option>

    <option value=11>Year 11</option>

    <option value=12>Year 12</option>

</select>

<button type="submit">Search Year Level</button>

</form>

<!-- Graph Area -->

<div id="layout">

        <canvas id="myChart" width="400" height="400"></canvas>

    </div>

</body>

</html>

<script>

    //example of array format needed to chart.js object

    var labels = ['Red', 'Blue', 'Yellow', 'Green', 'Purple', 'Orange'];

    var counts = [12, 19, 3, 5, 6, 6];

    var ctx = document.getElementById('myChart').getContext('2d');

    var myChart = new Chart(ctx, {

        type: 'bar', //pie,line

        data: {

            // run the print array on PHP variable sport names - return in string format with ' '

            labels: <?php printArray($sports, 'S');?>, ////OR <?php echo json_encode($sports)?>

            datasets: [{

                label: 'Number of Student Nominations',

                // run the print array on PHP varibale count - return in integer format

                data: <?php printArray($count, 'I');?>, //OR <?php echo json_encode($count)?>

                backgroundColor: [

                    'rgba(255, 99, 132, 0.2)',

                    'rgba(54, 162, 235, 0.2)',

                    'rgba(255, 206, 86, 0.2)',

                    'rgba(75, 192, 192, 0.2)',

                    'rgba(153, 102, 255, 0.2)',

                    'rgba(255, 159, 64, 0.2)'

                ],

                borderColor: [

                    'rgba(255, 99, 132, 1)',

                    'rgba(54, 162, 235, 1)',

                    'rgba(255, 206, 86, 1)',

                    'rgba(75, 192, 192, 1)',

                    'rgba(153, 102, 255, 1)',

                    'rgba(255, 159, 64, 1)'

                ],

                borderWidth: 1

            }]

        },

        options: {

            responsive: true,

            maintainAspectRatio: true,

            scales: {

                yAxes: [{

                    scaleLabel: {

                        display: true,

                        labelString: 'Student Count'

                    }

                }],

                xAxes: [{

                    scaleLabel: {

                        display: true,

                        labelString: 'Activities'

                    }

                }]

            }

        }

    });

</script>

<!-- Example of using links or buttons - replace form with links -->

<a href="links.php">All Years</a> |

<a href="links.php?year=7">Year 7</a> |

<a href="links.php?year=8">Year 8</a> |

<a href="links.php?year=9">Year 9</a> |

<a href="links.php?year=10">Year 10</a> |

<a href="links.php?year=11">Year 11</a> |

<a href="links.php?year=12">Year 12</a>

The more successful responses commonly:

• ·  clearly showed deconstruction of problem and computational thinking before solution was developed

• ·  emphasised key parts of code rather than speaking through what each type of variable was and what it held  

•   demonstrated iterative development and testing

• ·          had completed prototypes/products which were shown to be working

• ·          had open ended tasks which allowed the students to define their own problem

• ·          had a clear outline of what the individual role students had in the collaborative project

• ·          provided evidence of collaborating with, and presenting to, a target audience

• ·          videos and evidence presented by individual student

• ·          individual student videos clearly identify and demonstrate effectiveness and own role in the collaborative project

• ·          students included discussions about how they worked with their group and provided documentation in forms such as: meeting minutes, file sharing directory screenshots, team timelines, program version commits (GitHub)

• ·          well organised groups were able to allocate a specific part of the program to each individual student which enabled them to meet all required performance standards

• ·          addressed all assessed performance standards

• ·          differentiated between explanation of code and demonstration of the program

• ·          had a clear evaluation section

• ·          students were able to clearly refer to a client they were producing a product for in their presentation.

The less successful responses commonly:

• ·          showed limited computational thinking

• ·          collaboration roles of students not clear in the evidence provided

• ·          no supporting evidence of collaboration

• ·          did not clearly identify student’s own roles and contributions and did not stick to the prescribed time limit stated in the subject outline of maximum 5 minutes

• ·          multiple students in the same video evidence provided. Evidence should be individualised

• ·          not contributing in any way to creating code (computational thinking or actual software programming), hence student could not show evidence of CT4: Application of skills and processes to develop solutions to problems of interest and DE3: Evaluation of the effectiveness of a digital solution or prototype

• ·          had a pre-defined problem as a part of the task which limited the scope for students to define and solve issues – no client conversation and/or group discussion demonstrated to identify scope and issues to then refine a problem

• ·          did not mention, or provide evidence of, having worked with a target audience

• ·          provided very little, if any, evidence of Computational Thinking Skills; the focus was purely on Development and Evaluation.