Attribution

This SQL 101 guide is based on our notes from the Google Cybersecurity course.

Introduction to SQL and Databases

Databases allow us to store large amounts of data while keeping it quick and easy to access.

What is a database?

  • An organized collection of information or data
  • Can be accessed by multiple people simultaneously
  • Can store massive amounts of data
  • Can perform complex tasks while accessing data

Spreadsheets work well for single users or small teams with smaller datasets. Databases handle larger scale.

Relational database

A structured database containing tables that are related to each other. Each table contains fields of information – these are the columns of the table:

-----------------
employees (table)
-----------------
employee_id (field)
device_id (field)
username (field)
department (field)
office (field)
-----------------

Rows are called records. They contain specific data related to the columns in the table. Databases often contain multiple tables. Tables can connect to each other when they share a common column called a key.

Primary key

  • A column where every row has a unique entry
  • Must not have duplicate values, null values, or empty values
  • Allows for unique identification of each row
  • A table can have only one primary key

Foreign key

  • A column in a table that is a primary key in another table
  • Can have empty values and duplicates
  • Allows two tables to connect
  • A table can have multiple foreign keys

SQL (Structured Query Language)

A programming language used to create, interact with, and request information from a database.

Query

A request for data from a database table or combination of tables.

Log

A record of events that occur within an organization’s systems.

Accessing SQL from Linux

From the Linux command line with sqlite3.

Basic Queries

Keywords are not case-sensitive: SELECT works the same as select. Every query ends with a semicolon (;).

SELECT employee_id, device_id FROM employees;
SELECT * FROM employees;

SELECT

  • Indicates which columns to return
  • SELECT * returns all columns (same as SELECT all)
  • Always used with the FROM keyword

FROM

Indicates which table to query.

Syntax

The rules that determine what is correctly structured in a computing language.

Querying a Database

ORDER BY

Sorts the records returned by a query based on a specified column. Place it at the end of your query and specify which column to sort by.

By default, sorting is in ascending order. Numeric columns sort from smallest to largest. Text columns sort alphabetically from A to Z.

-- Sorting in ascending order (default)
SELECT customerid, city, country
FROM customers
ORDER BY city;

-- Sorting in descending order
-- Sorts numbers from largest to smallest, or text from Z to A
SELECT customerid, city, country
FROM customers
ORDER BY city DESC;

-- Sorting by multiple columns
-- Sorts first by country, then by city for rows with the same country
SELECT customerid, city, country
FROM customers
ORDER BY country, city;

Basic Filters on SQL Queries

Filtering

Selecting data that matches a certain condition.

Operator

A symbol or keyword that represents an operation.

-- Equal to symbol (=)
country = 'USA'

WHERE

Indicates the condition for a filter. List the specific condition after the WHERE keyword using operators.

WHERE country = 'USA'

Filtering for Patterns

Use the percentage sign (%) as a wildcard for unspecified characters. When using wildcards, use the LIKE operator instead of the equals sign (=).

-- Returns all records that start with "East"
East%

Wildcard

A special character that can be substituted with any other character. Can be placed after a string, before a string, or both.

  • % (percentage sign): Substitutes for any number of characters
  • _ (underscore): Substitutes for exactly one character

LIKE

Used with WHERE to search for a pattern in a column.

WHERE office LIKE 'East%'

SELECT * FROM log_in_attempts WHERE country LIKE 'US%';

More SQL Filters

Filtering Dates and Numbers

SELECT * FROM log_in_attempts WHERE time > '18:00';

String Data

Data consisting of an ordered sequence of characters. Characters can be numbers, letters, or symbols.

Numeric Data

Data consisting of numbers. We can perform mathematical operations on numeric data like multiplication or addition.

Date and Time Data

Data representing a date and/or time.

Common Operators

  • = (equal to)
  • > (greater than)
  • < (less than)
  • <> or != (not equal to)
  • >= (greater than or equal to)
  • <= (less than or equal to)

BETWEEN

An operator that filters for numbers or dates within a range. Works with numeric data and date/time data. It is inclusive, meaning it includes the boundary values.

SELECT * FROM machines WHERE OS_patch_date BETWEEN '2021-03-01' AND '2021-09-01';

Operators for Filtering Dates and Numbers

Exclusive Operator

An operator that does not include the comparison value, such as >.

Inclusive Operator

An operator that includes the comparison value, such as >=.

Filters with AND, OR, and NOT

AND – Both conditions must be met simultaneously.

SELECT * FROM machines WHERE operating_system = 'OS 1' AND email_client = 'Email Client 1';

OR – Either condition can be met.

SELECT * FROM machines WHERE operating_system = 'OS 1' OR operating_system = 'OS 3';

NOT – Negates a condition. Works on a single condition only.

-- Using NOT
SELECT * FROM machines WHERE NOT operating_system = 'OS 3';

-- Using <> (not equal)
SELECT * FROM machines WHERE operating_system <> 'OS 3';

-- Using != (not equal)
SELECT * FROM machines WHERE operating_system != 'OS 3';

Combining Logical Operators

Logical operators can be combined in filters.

SELECT firstname, lastname, email, country 
FROM customers 
WHERE NOT country = 'Canada' AND NOT country = 'USA';

SQL Joins

Joins help when we need information from two different tables. SQL needs to know which column we are referring to. We specify this by writing the table name, a period, then the column name: employees.employee_id and machines.employee_id.

NULL represents a missing value.

INNER JOIN

Returns rows that match on a specified column existing in both tables. It only returns records that share a value in the specified columns.

SELECT username, office, operating_system 
FROM employees 
INNER JOIN machines ON employees.employee_id = machines.employee_id;

Types of Joins

Sometimes we need all entries from one or both tables. These are called outer joins. They use the same syntax as inner joins:

<JOIN_TYPE> table ON employees.employee_id = machines.employee_id;

Types of Outer Joins

  • LEFT JOIN – Returns all records from the first table and only matching records from the second table
  • RIGHT JOIN – Returns all records from the second table and only matching records from the first table
  • FULL OUTER JOIN – Returns all records from both tables

LEFT JOIN

Returns all records from the first table, but only returns rows from the second table that match on the specified column. Non-matching rows from the first table are still returned, with NULL values in columns from the second table.

RIGHT JOIN

Returns all records from the second table, but only returns rows from the first table that match on the specified column.

FULL OUTER JOIN

Returns all records from both tables. If a row lacks a value for a particular column, it returns NULL.

Aggregate Functions

Functions that perform a calculation over multiple data points and return the result. The actual data is not returned – only the calculation result.

  • COUNT: Returns the number of rows returned by the query
  • AVG: Returns the average of the numerical data in a column
  • SUM: Returns the sum of the numerical data in a column (excludes NULL values)

Aggregate Function Syntax

Place the function after SELECT, with the column name in parentheses.

SELECT COUNT(firstname) FROM customers;
-- Returns a table with one column titled 'COUNT(firstname)' 
-- and one row showing the count

SELECT COUNT(firstname) FROM customers WHERE country = 'USA';