Topics

• Getting started with SQL
• SELECT
• JOIN
• Example Databases and Tables
• UPDATE
• Indexes
• CREATE TABLE
• ALTER TABLE
• CASE
• INSERT
• Primary Keys
• ORDER BY
• Filter results using WHERE and HAVING
• Common Table Expressions
• Views
• LIKE operator
• SQLite
• Functions (Aggregate)
• DELETE
• String Functions
• TRUNCATE
• MERGE
• Foreign Keys
• Comments
• Subqueries
• Getting started with sqlite
• DROP Table
• Row number
• Transactions
• SKIP TAKE (Pagination)
• IN clause
• NULL
• SQL Injection
• Command line dot-commands
• Getting started with android-sqlite
• PRAGMA Statements
• Data types
• sqlite3_stmt: Prepared Statement (C API)
• Functions (Scalar/Single Row)
• Relational Algebra
• Functions (Analytic)
• Identifier
• Clean Code in SQL

Examples

• Using the wildcard character to select all columns in a query.
• Implicit Join
• Left Outer Join
• Auto Shop Database
• Selecting with Condition
• Select Individual Columns
• Updating All Rows
• Updating Specified Rows
• Modifying existing values
• Creating an Index
• Create a New Table
• Using Auto Increment
• Use ORDER BY with TOP to return the top x rows based on a column's value
• Sorting by multiple columns
• INSERT data from another table using SELECT
• UPDATE with data from another table
• Temporary query
• recursively going up in a tree
• generating values
• recursively enumerating a subtree
• Match open-ended pattern
• Sorting by column number (instead of name)
• SUM
• Select columns which are named after reserved keywords
• Concatenate
• Selecting specified number of records
• Creating a table with a foreign key
• Single-line comments
• Multi-line comments
• Oracle CONNECT BY functionality with recursive CTEs
• Installation
• Check for existence before dropping
• List Concatenation
• Dropping an Index, or Disabling and Rebuilding it
• Simple Transaction
• Performing a Transaction
• REGEXP
• Skipping some rows from result
• Limiting amount of results
• Skipping then taking some results (Pagination)
• Create a Temporary or In-Memory Table
• Selecting with CASE
• Capturing Updated records
• Delete All But Last Record (1 to Many Table)
• Simple IN clause
• Recursive JOINs
• Updating fields to NULL
• SQL injection sample
• Get aggregated result for row groups
• Example of a complex SQL with Common Table Expression
• Selecting with more than 1 condition.
• Exporting and importing a table as an SQL script
• Differences between inner/outer joins
• Overview
• Basic usage
• PRAGMAs with permanent effects
• TYPEOF function
• Using booleans
• Enforcing column types
• Date/time types
• Executing a Statement
• Reading Data from a Cursor
• simple injection sample
• Documentation
• Executing a prepared statement multiple times
• Character modifications
• Partial or Filtered Index
• Overview
• Customizeed sorting order
• FIRST_VALUE
• LAST_VALUE
• LAG and LEAD
• PERCENT_RANK and CUME_DIST
• PERCENTILE_DISC and PERCENTILE_CONT
• MySQL: counting users by name
• PostgreSQL: counting users by name
• Foreign Keys explained
• Unquoted identifiers
• Formatting and Spelling of Keywords and Names
• SELECT *
• Indenting
• Joins
• CASE in ORDER BY clause to sort records by lowest value of 2 columns