The LIKE operator is used with the WHERE clause and searches for a string pattern in a column. The statement below will return only the rows where the "Major" is "Accounting".

The statement below uses "%" as wildcard. So any row with a "Major" that begins with "A" will be returned--Accounting, Agriculture, Art, etc.

Use "LIMIT" at the end to return only a defined number of rows from the top of the result. It is often used with "ORDER BY" like below.

SELECT Major, StartingMedianSalary FROM Degrees ORDER BY StartingMedianSalary DESC LIMIT 3

The "ORDER BY" keyword simply sorts the rows of the table based on the column name provided. You can follow the ORDER BY columnName with "ASC" or "DESC" (for ascending or descending) to dictate the direction of the sort.

SELECT Major, StartingMedianSalary FROM Degrees ORDER BY StartingMedianSalary DESC

Structured Query Language (SQL) is a standard language for accessing and manipulating databases. Databases are just a group of data tables. Below is an example of a table called "Degrees" that could be in a database.

We can then use SQL to "filter" the table. For example the following SQL statement..

SELECT Major, StartingMedianSalary FROM Degrees

would generate the following table.

We simply SELECT columns FROM a table. Above we selected the columns "Major" and "StartingMedianSalary" from the "Degrees" table.

We can also use * (a wild card) for the columns to get all the columns. So, SELECT * FROM Degrees will result in the full table.

Try It OUT

1 - Find the following Javascript in your code.js file.

viz.chart({
    sql:"SELECT * FROM Degrees",
    chartType:"Table",
    containerId:"chart2"
});

Notice how this code generates the table in your web view, where the table has a lot of columns (actually, all of them).

2 - Now change the SQL to the following to select only two columns.

SELECT Major, StartingMedianSalary FROM Degrees

Run your web app and notice how the table has changed.

3 - Now try the following SQL to get all the columns from a different table.

SELECT * FROM States

This gets a different table, "States", that can be found in our example database.

4 - Go to this Google Sheet to see the example database and notice how each sheet (tab) is a table with a unique name.

You will use SQL to get the right data from a database (Google Sheets file) for the charts you want to display. Next, let's look at producing the actual charts.

The WHERE clause is used to filter records based on a condition. The following SQL statement will return a table that only contains rows where the "StartingMedianSalary" is greater than 60000.

SELECT Major, StartingMedianSalary FROM Degrees WHERE StartingMedianSalary > 60000

You can use the following operators for the comparison.

1 - Create a Google Sheets File and Add Data

Create a Google Sheet file then create a simple table, like below, that could be used for a Scatter Chart. Also, name your individual sheet (the tab at the bottom) something like "myData".

2 - Publish Your Google Sheets File

Create a Google Sheets file and publish it by selection file>Publish to the web... then click the Publish button.

3 - Get the Public Link for a Table

4 - Load Your Table in Javascript

Copy the code below and paste it just after this line. Now replace the link with your table link copied in the previous step and change the tab label (e.g. 'myData') to match your sheet tab label. You can change the composeMyCharts function name as you wish or keep it the same.

viz.loadTable('https://docs.google.com/spreadsheets/d/e/2PACX-1vQUwF7K2lCH8CxQPJW-X7NqENwuwUM4eAeNUKw3j6yppO0tipC6yUB2hQPtRBZf19mmVbM5TdkkengZ/pub?gid=1030028695&single=true&output=csv', 'myData')
    .then(composeMyCharts);

Now you can access your database in your SQL statements. Remember that your individual sheet names are the tables you can select from in SQL. So if you want to get all the columns from a sheet (table) called "myData", then you would use the following:SELECT * FROM myData

5 - Create Charts from Your New Table

Create a function in your Javascript that will then define the charts for your new table. You can use the example below as a template, but you want the ensure the function name used in step 3 (e.g. composeMyCharts) matches this function name. You will also want to change the SQL and ensure that you have HTML elements with ID's to display the charts.

function composeMyCharts() {
  viz.chart({
    sql:"SELECT x, y FROM myData",
    chartType:"ScatterChart",
    containerId:"chart1"
  });

  viz.chart({
    sql:"SELECT * FROM myData",
    chartType:"Table",
    containerId:"chart2"
  });
}

The "options" parameter included below demonstrates many style changes you can make to a Histogram chart.

The dashboard template provides the HTML/CSS/JS needed to get started with web-based data visualization. The template uses the following tools to make things easier for us.

• Google Sheets - This will function as our "database" of tables.

• Structured Query Language (SQL) - SQL will be used to pull the right data from the database for visualizing.

• - This library will allow us to create charts in our web app.

Instructions

1 - Create files in a new Repl.it or other code environment.

Use the code from to create your HTML, Javascript, and CSS files. Verify that the dashboard displays an example bar chart and table.

2 - Review the HTML

Notice the basic page structure for the "cards" that contain elements for the charts. See below for reference.

3 - Review the CSS

Notice the "card" class that creates the shadow effect you see with the charts.

4 - Review the Javascript

• The function composeDegreeCharts() runs automatically once the data is loaded.

• In composeDegreeCharts() we create our charts by specifying SQL (more on this later), the chart type, and the HTML element it should be placed.

That's the quick overview of the template. We will dig into the details and how to customize things next.

The "options" parameter included below demonstrates many style changes you can make to a Column chart.

The "options" parameter included below demonstrates many style changes you can make to a Bubble Chart.

  viz.chart({
    sql: "SELECT State, PercentTechJobs, EdSpendingOverGdpPercent, 'Source', GDP FROM States", 
    chartType: "BubbleChart",
    containerId: "chart3",
    options: {
      title: "Trend in STEM Jobs",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000"],
      vAxis: {
        title: "Percent of Spending\non Education Spending",
        format: "percent"
      },
      hAxis: {
        title: "Percent of Jobs in STEM",
        format: "percent"
      }
    }
  });

The "options" parameter included below demonstrates many style changes you can make to a Line Chart.

viz.chart({
    sql: "SELECT Year, Total FROM Trend", 
    chartType: "LineChart",
    containerId: "chart3",
    options: {
      title: "Trend in Tech Jobs",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000"],
      vAxis: {
        title: "Jobs in US \n(in Thousands)"
      },
      hAxis: {
        title: "Year",
        format: "####"
      }
    }
  });

Making basic charts with the "viz" library is pretty easy. Let's get started.

Try It Out

1 - Find the following Javascript in your code.js file.

  viz.chart({
    sql:"SELECT Major, StartingMedianSalary FROM Degrees",
    chartType:"BarChart",
    containerId:"chart1"
  });

This generates a bar chart (chartType: "BarChart") using data specified by the SQL statement(sql: "SELECT Major, StartingMedianSalary FROM Degrees") and places the chart in an HTML element with an id of "chart1" (containerId:"chart1"). Notice that you should have a <div> tag with that id in your HTML file.

2 - Change the chartType to "ColumnChart"

In your Javascript, change the chart type to "ColumnChart". Run the web app and notice how the chart changes. It may not look good since there are so many columns squished together. Choosing the right way to visualize the data will be part of the project challenge.

3 - Change the chartType to "Histogram"

In your Javascript, change the chart type to "Histogram". Run the web app and notice how the chart changes.

4 - Add Another Chart that Uses a Different Data Table

Copy and add the following Javascript into your composeCharts( ) function.

viz.chart({
    sql:"SELECT Year, Total FROM Trend",
    chartType:"ScatterChart",
    containerId:"chart3"
 });

You will also want to add the following HTML to your index.html file to provide a place for your chart.

<div class="card">
   <div id="chart3"></div>
</div>

Run your web app and you should see your new chart. This chart uses data from the "Trend" table in our database (Example Google Sheets File) to create a scatter plot (also called scatter chart).

More Charts and More Options

See the Code Examples section for different charts and options you may want to use in your final dashboard, like titles and colors.

The "options" parameter included below demonstrates many style changes you can make to a bar chart.

viz.chart({
    sql: "SELECT Major, StartingMedianSalary, MidCareerMedianSalary FROM Degrees",
    chartType: "BarChart",
    containerId: "chart1",
    options: {
      title: "Salaries by Degree",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
      legend: {
        position: "right"
      },
      colors: ["#bb0000", "#ffaa00"],
      hAxis: {
        title: "Annual Salary",
        format: "currency"
      }
    }
});

Create the HTML, CSS, and Javascript files below and you are off to the races. Click on the file name to get the code content for each.

<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width">
	<title>Dashboard Template</title>
	<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body>
	<h1>Data Dashboard</h1>
	<div id='page'>
    	<div class="card">
			<div id="chart1"></div>
		</div>
		<div class="card">
			<div id="chart2"></div>
		</div>
	</div>

	<!--Javascript-->
	<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>
	<script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js'></script>
	<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
	<script src="https://cdn.jsdelivr.net/alasql/0.2/alasql.min.js"></script>
	<script src="https://cdn.jsdelivr.net/gh/idewcomputing/code-dataviz-dashboard@master/src/viz.js"></script>
	<script src="code.js"></script>
</body>

</html>

function setup() {
  var degreesLink = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQUwF7K2lCH8CxQPJW-X7NqENwuwUM4eAeNUKw3j6yppO0tipC6yUB2hQPtRBZf19mmVbM5TdkkengZ/pub?gid=1030028695&single=true&output=csv';
  viz.loadTable(degreesLink,"Degrees").then(composeDegreeCharts);
  window.onresize = composeDegreeCharts;
}

function composeDegreeCharts() {
  viz.chart({
    sql:"SELECT Major, StartingMedianSalary FROM Degrees",
    chartType:"BarChart",
    containerId:"chart1"
  });

  viz.chart({
    sql:"SELECT * FROM Degrees",
    chartType:"Table",
    containerId:"chart2"
  });
}

window.onload = setup;

body {
  padding: 15px;
  margin: 0;
  background: #eee;
}

#page {
  display: grid;
  /* grid-template-columns: repeat(auto-fill, minmax(400px, 1fr)); */
  grid-gap: 15px;
}

h1 {
  font-family: 'Arial';
  text-align: center;
}

.card {
  box-shadow: 0 4px 8px 0 rgba(0, 0, 0, 0.4), 0 6px 20px 0 rgba(0, 0, 0, 0.4);
  overflow: hidden;
  background: white;
}

.description {
  padding: 15px 7px;
}

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

Copyright and License

Copyright © 2018-2019 Jim Lyst and Michael Frontz, Indiana University School of Informatics and Computing at IUPUI

This material is part of the Computing by Design high school computer science curriculum developed for the Informatics Diversity-Enhanced Workforce (iDEW) program, an award-winning community partnership in central Indiana that is broadening student participation in computing and helping students develop the skills essential for success in the 21st century workplace. The iDEW program is managed by the Indiana University School of Informatics and Computing at IUPUI.

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. You are free to use, share, or adapt this material for noncommercial purposes as long as you provide proper attribution and distribute any copies or adaptations under this same license.

The "options" parameter included below demonstrates many style changes you can make to a Scatter Chart.

To create a grid layout like the sketch above, adjust your HTML and CSS like the following examples. But be careful not to simply copy and paste everything here.

HTML

Notice that the "card" for the first chart also has a class called "full-width". We will use this class to make a chart span the full width of the page, while the others will fall into a grid with 3 charts per row.

	<div id='page'>
    	<div class="card full-width">
			<div id="chart1"></div>
		</div>
		<div class="card">
			<div id="chart2"></div>
		</div>
		<div class="card">
			<div id="chart3"></div>
		</div>
		<div class="card">
			<div id="chart4"></div>
		</div>
	</div>

CSS

First, notice the id="page" (#page) style defines the normal grid design of 3 elements (or 3 columns) per row. grid-template-columns: 1fr 1fr 1fr; This applies to all the grid elements inside the id = "page" element.

Next, any element with the class "full-width" will start at the first position and span 3 columns. grid-column: 1 / span 3;

We can also add a style (look for @media ... below ) that makes our dashboard mobile-friendly where all the charts just stack on top of each other if the screen is less than 700px wide.

/* This is not the complete CSS file contents.*/ 
/* It simply demonstrates the important styles for this layout example. */

#page {
  display: grid;
  grid-template-columns: 1fr 1fr 1fr;
  grid-gap: 15px;
}

.full-width {
  grid-column: 1 / span 3;
}

@media only screen and (max-width: 700px) {
  .card {
    grid-column: 1 / span 3;
  }
}

This example demonstrates how you can use a dropdown menu to allow a user to select, or filter, the data displayed on a column chart.

HTML

The select element will be used to filter the data for a column chart that will be inserted in the "chart2" div.

<div class="card">
  <select id="salary-select">
    <option value="StartingMedianSalary" selected>Starting Salary</option>
    <option value="MidCareerMedianSalary">Mid-Career Salary</option>
  </select>
  <div id="chart2"></div>
</div>

Javascript

Add the following code to the bottom of the setup() function. This will "listen" for changes to the dropdown selector and re-run the composeCharts() function when it is changed.

$("#salary-select").change(function () {
    composeCharts();
  });

The following "viz.chart" function defines a column chart that will use the value selected by the user in the SQL to retrieve the right column of data to chart.

viz.chart({
    sql: `SELECT Major, ${$("#salary-select").val()} FROM Degrees`,
    chartType: "ColumnChart",
    containerId: "chart2"
});