CSE 154

Lecture 17

Administrivia

Pokedex is due Wednesday

Make sure to re-read the spec before submitting to make sure you cover any missed details!

Review: What We've Learned So Far

How to write content for a webpage using HTML

How to add styles to a webpage using CSS and linking a CSS file to an HTML file

How to inspect the HTML and CSS of web pages in the browser

How to write behavior for the page and handle user events using JS

How to fetch and process data from a server using AJAX with JS

How to write a server-side program and your own API in PHP

A Warmup

(sorry, but it's best dataset I had...)

A Solution

function filterJSON(pokemon) {
  let result = { "pokemon" : []};
  let filtered = [];
  for (let i = 0; i < pokemon.length; i++) {
    let data = pokemon[i];
    if (data.weakness === "ground" &&
      data.name.indexOf("a") !== -1) &&
      data.id < 150) {
        filtered.push(
          { "name" : data.name, "type" : data.type,
            "id" : data.id, "weakness" : data.weakness});
    }
  }

  let length = filtered.length;
  for (let i = length - 1; i >= 0; i--) {
    for (let j = 1; j <= i; j++) {
      let firstPokemon = filtered[j - 1];
      let secondPokemon = filtered[j];
      if (firstPokemon.type > secondPokemon.type ||
          firstPokemon.type == secondPokemon.type &&
          firstPokemon.name < secondPokemn.name) {
        let temp = firstPokemon;
        filtered[j - 1] = secondPokemon;
        filtered[j] = temp;
      }
    }
  }
  result.pokemon = filtered;
  return result;
}

Discussion

What are some limitations of filtering out the data in JSON?

Discussion

What are some limitations of filtering out the data in JSON?

• Efficiency: Expensive looping for large datasets, various if/else checks, and extra loops for sorting
• Long, complex code just to get a subset of the desired data - this code also will need to be manually edited to a good degree when you want a different subset/sorting of data. This also adds more room for programming errors.
• This code was written on the client-side (JS) - could take a relatively long time to finish when the page is also working on other tasks (e.g. user events, other AJAX requests)

Today, you'll learn how to write a clean solution to this problem in SQL

          SELECT name, id, type, weakness
          FROM Pokemon
          WHERE id < 150 AND name LIKE %a% AND weakness = 'ground'
          ORDER BY type, name DESC;
          

SQL

The above code does everything (without JSON/JS) asked in the warmup problem!
(Toggle highlighting)

function filterJSON(pokemon) {
  let result = { "pokemon" : []};
  let filtered = [];
  for (let i = 0; i < pokemon.length; i++) {
    let data = pokemon[i];
    if (data.weakness === "ground" &&
      data.name.indexOf("a") !== -1) &&
      data.id < 150) {
        filtered.push(
          { "name" : data.name, "type" : data.type,
            "id" : data.id, "weakness" : data.weakness});
    }
  }

  let length = filtered.length;
  for (let i = length - 1; i >= 0; i--) {
    for (let j = 1; j <= i; j++) {
      let firstPokemon = filtered[j - 1];
      let secondPokemon = filtered[j];
      if (firstPokemon.type > secondPokemon.type ||
          firstPokemon.type == secondPokemon.type &&
          firstPokemon.name < secondPokemn.name) {
        let temp = firstPokemon;
        filtered[j - 1] = secondPokemon;
        filtered[j] = temp;
      }
    }
  }
  result.pokemon = filtered;
  return result;
}

SQL

SQL: a Relational Database Language

Relational Database: A method of structuring data as tables associated by shared attributes

A table row corresponds to a unit of data called a record; a column corresponds to an attribute of that record

In Excel speak:

In the above image, the cells highlighted blue are the first "column" and the cells highlighted green are of the first "row", or "record"

SQL tables can be visualized just like an Excel sheet, just with different terminology, and much more programmatic capabilities.

Guess What? We've Reached the Last Language of 154!

(Insert happy/sad face here depending on your feels)

Why Learn SQL/Databases in this Class?

Databases give us a great improvement in the way we can build, process, and retrieve large datasets. Most software companies will have a large group dedicated to database management.

Advantages of a database:

• Powerful: Can search it, filter data, combine data from multiple sources
• Fast: Can search/filter a database very quickly compared to a file/JSON
• Big: Scale well up to very large data sizes
• Safe: Built-in mechanisms for failure recovery (e.g. transactions
• Multi-userConcurrency feature let many users view/edit data at the same time
• Abstract: Provides layer of abstraction between stored data and app(s) - many database programs understand the same SQL commands

Database Software

Oracle

Microsoft SQL Server (powerful) and Microsoft Access (simple)

PostgreSQL (powerful/complex free open-source database system)

SQLite (transportable, lightweight free open-source database system)

MySQL (simple free open-source database system)

• Many servers run "LAMP (Linux, Apache, MySQL, and PHP)
• Wikipedia is run on PHP and MySQL
• We will use MySQL in this course (supported by Cloud9)

Today's Example Database

Games

SQL Basics

            SELECT name FROM Games WHERE id=23;
          

INSERT into Games VALUES 
  (514,
  'CSE 154 Pokemon',
  'PC',
  '2017',
  'Card',
  'Melissa Medsker/Whitaker Brand',
  'UW CSE',
  'E');
          

SQL (Games DB)

Structured Query Language (SQL): A language for searching/updating a database.

A standard syntax that is used by all database software (with minor compatibilities)

• Generally case-insensitive

A declarative language: describes what data you are seeking, not exactly how to find it.

The SQL SELECT Statement

Syntax:

SELECT column(s) FROM table;

Example:

SELECT name, release_year FROM Games;

The SELECT statement is used to select data from a database and returns the data in a result table containing the row data for column name(s) written after SELECT filter

Table and column names are case-sensitive

The DISTINCT Modifier

Syntax:

SELECT DISTINCT column(s) FROM table;

The DISTINCT eliminates duplicates from the result set.

SELECT releaseYear
FROM Games;

SELECT DISTINCT releaseYear
FROM Games;

The SQL WHERE Statement

Syntax:

SELECT column(s) FROM table WHERE condition(s);

Example:

SELECT name, release_year FROM Games WHERE genre = 'puzzle';

Example result:

The WHERE clause filters out rows based on their columns' data values. In large databases, it's critical to use a WHERE clause to reduce the result set in size.

Suggestion: When trying to write a query, think of the FROM part first, then the WHERE part, and lastly the SELECT part.

More about the WHERE Clause

Syntax:

WHERE column operator value(s)

SELECT name, release_year 
FROM Games 
WHERE release_year < 1990;

Multiple WHERE Clauses: AND, OR

Example:

SELECT name, release_year FROM Games 
WHERE release_year < 1990 AND genre='puzzle';

Example result:

Multiple WHERE conditions can be combined using AND or OR.

Approximate Matches with LIKE

Syntax:

WHERE column LIKE pattern

SELECT name, release_year FROM Games 
WHERE name LIKE 'Spyro%'

Sorting By a Column: ORDER BY

Syntax:

SELECT column(s) FROM table
ORDER BY column(s) ASC|DESC;

SELECT name FROM Games 
ORDER BY name

SELECT name FROM Games 
ORDER BY name DESC

The ORDER BY keyword is used to sort the result set in ascending or descending order (ascending if not specified)

Limiting Rows with LIMIT

Syntax:

LIMIT number

Example:

SELECT name FROM Games 
WHERE genre='puzzle'
ORDER BY name
LIMIT 3;

Example result:

LIMIT can be used to get the top-N of a given category. It can also be useful as a sanity check to make sure you query doesn't return 100000 rows.

Additional Practice with SQL Queries

SQLZoo has multiple exercises (with built-in databases you don't need to worry about setting up) for practicing SELECT, WHERE, ORDER BY, LIMIT, LIKE, etc. We recommend you go through these for additional practice!

GalaXQL: Build/destroy/find galaxies with SQL