CSE 154

Lecture 21: Introduction to SQL

CSE 154 Modules: Where We're At

1. Webpage structure and appearance with HTML5 and CSS.
2. Client-side interactivity with JS DOM and events.
3. Using web services (API's) as a client with JS.
4. Writing JSON-based web services with PHP.
5. Storing and retreiving information in a database with MySQL and server-side programs.

A Warmup

(there's no such thing as too much Pokémon...)

A Solution

function filterJSON(pokemonJSON) {
  let pokemon = pokemonJSON["pokemon"];
  let filtered = [];

  // filter out only the Pokemon we want
  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"]});
    }
  }

  // Sort (descending by name alphabetically)!
  for (let i = filtered.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;
      }
    }
  }
  let result = { "pokemon" : filtered };
  return result;
}

JS

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

function filterJSON(pokemonJSON) {
  let pokemon = pokemonJSON["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;
      }
    }
  }
  let result = { "pokemon" : filtered };
  return result;
}

JS

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

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 more programmatic capabilities.

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

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

You can also now appreciate some of this...

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-user: Concurrency 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/MAMP/WAMP" (Linux/Windows/Mac, Apache, MySQL, and PHP)
• Wikipedia is run on PHP and MySQL
• We will use MySQL in this course with phpMyAdmin

Today's Example Database

Games

You can test queries with this Games table here (use username of 'cse154_readonly' and password 'webgoose'

Note: We'll be practicing basic queries from this dataset today, but you'll learn how to setup your own MySQL database with MAMP/phpMyAdmin next week!

If you're eager to get started, you can also visit Sunday OH for any help get it setup :)

SQL Basics

SELECT name FROM Games WHERE id=23;

INSERT into Games VALUES
  (9999,
  'CSE 154 Pokemon',
  'PC',
  '2017',
  'Card',
  'Melissa Hovik/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 variations). Generally case-insensitive.

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

SQL Statements We'll Cover Today

• SELECT
• DISTINCT
• WHERE
• LIKE
• ORDER BY
• LIMIT

These are the basic "building-blocks" of forming "questions" (queries) in SQL

The SQL SELECT Statement

Syntax:

SELECT column(s) FROM table;

Example:

SELECT name, release_year FROM Games;

Example output:

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;

SQL

The DISTINCT eliminates duplicates from the result set.

SELECT release_year
FROM Games;

SELECT DISTINCT release_year
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;

Check your Understanding

Write a SQL query that returns the `name` and `platform` of all games with a 'release_year' before 2000.

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.

Check your Understanding

Write a SQL query that returns the `name` and `genre` of all games with the name 'Mario' or 'Spyro'.

Approximate Matches with LIKE

Syntax:

WHERE column LIKE pattern

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

Note: In MySQL, the text in the LIKE string is case-insensitive.

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.

Check your understanding:

1. Write a SQL query that returns the `name` and `genre` of all games that have the word `dragon` in them, ordered by `release_year`.

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