Administrivia

Pokedex is due today (last day to turn in tomorrow)

Make sure to join the Slack if you haven't already - there has been some good discussions!

Extra office hours?

HW 6 (Bestreads) will be released this afternoon - due next Friday

Today's Agenda

HW 6 Introduction

Review SQL Basics (click on a keyword to jump to slide)

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

Database manipulation (create/insert)

• CREATE TABLE
• INSERT INTO TABLE

Review SQL Basics

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

A standard syntax that is used by all database software (with minor incompatibilities). Generally case-insensitive (e.g., select is treated the same as SELECT).

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

Review SQL Basics

SQL databases contain tables which have rows (records) and columns (attributes)

Games

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!

CREATE TABLE

CREATE TABLE is used to create a new table.

Syntax:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns)
);

CREATE TABLE demo

INSERT

INSERT is used to insert a new record into an existing table.

Syntax:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00);

INSERT demo