Introduction to SQL
About This Course
SQL is how you ask a database a question, and it has been the way to do that
for forty years. This course takes you from your first SELECT to queries
that span several tables, summarise data for reporting, and write results
back. You work in DBeaver, a database manager that connects to most
relational databases you are likely to meet at work.
Day one is relational databases, and most of it is spent writing queries. The
examples run against a live database holding real open data, including tables
large enough that a careless query is a slow one, so you meet the judgement
that scale forces on you. You count rows before you pull them. You learn why a
LIKE pattern that starts with a wildcard reads every value in the column.
Along the way we are explicit about what is standard SQL and what belongs to
one vendor, which matters if your employer runs Oracle or SQL Server.
The parts of SQL that quietly produce wrong answers get their own time. A
comparison against a missing value is neither true nor false, and we work
through what that does to a WHERE clause. Sorting without an explicit
order gives you a different set of rows each run. You also get a writable
schema of your own, so creating tables, inserting and updating rows, and
rolling a change back after a mistake are all done for real rather than
described.
Day two covers the databases that do not fit neatly into tables. You work through the NoSQL models you are most likely to meet: what each one stores, and the shape of problem it was built for. The point of the day is judgement: which model suits a given problem, and what you give up by choosing it.
Who This Course Is For
Analysts, reporting and BI staff, engineers, scientists and developers who need to read or write data held in a relational database. It suits people who currently ask a colleague or a ticketing queue for every extract, and who would rather answer the question themselves.
It also suits teams where the data everyone reports on sits in a database that only one or two people can query, and teams moving reporting off spreadsheets onto the database that already holds the source data.
Prerequisites
Some familiarity with programming concepts in any language is helpful, but prior programming experience is not required. You do not need to have written Python before.
You should be comfortable working with tabular data — rows, columns and the idea that a value can be missing. No prior SQL is assumed, and you will not administer a database: no installation, backups, users or tuning.
What You'll Learn
- Connect to a database and find your way around a schema you did not design, including how its tables relate to each other.
- Write SQL queries that return exactly the rows you need, including the cases where a value is missing.
- Combine data held across several tables with the join that suits the question.
- Summarise and aggregate data to answer reporting questions.
- Create and modify tables, and insert, update and delete rows without fear of an irreversible mistake.
- Store a query as a view so colleagues can reuse it.
Course Syllabus
Day 1: relational databases and SQL
- Relational databases, where SQL fits, and what a database is not for
- Connecting with DBeaver, and exploring tables, views and relationships
- Querying and filtering: SELECT, WHERE, and how NULL changes the answer
- Partial matching with LIKE and regular expressions, and what it costs
- Sorting, limiting and paging through large results
- Aggregation, grouping and filtering on aggregate results
- Working across multiple tables, and the four kinds of join
- Creating tables, writing data, and undoing it with ROLLBACK
- Views and materialised views for storing queries
Day 2: NoSQL
- NoSQL databases and when to use them
- The main NoSQL models — wide-column stores, document stores, search indexes and key-value stores — and the problems each one suits
- Choosing between relational and non-relational storage