Tables and basic queries
What a database is, how to create tables and add rows, and how to choose, sort and de-duplicate the rows a query returns.
About 9 hours of study
Learning objectives
- Create tables with suitable types
- Insert rows and understand NULL
- Filter rows with WHERE
- Sort, limit and de-duplicate results
Lessons
- 1
Databases and tables
What a database is, why programs keep data in one instead of plain files, and how a table holds data in rows and columns.
- 2
Creating tables and adding rows
CREATE TABLE with column types and defaults, INSERT one row or many, NULL for a missing value, and ALTER TABLE and DROP TABLE.
- 3
Choosing rows and columns: SELECT and WHERE
Pick columns and calculate new ones with SELECT; keep only the rows you want with WHERE, AND, OR, BETWEEN, IN and LIKE; and handle NULL with IS NULL.
- 4
Sorting, limiting and DISTINCT
ORDER BY one or more columns, LIMIT and OFFSET for the top rows and for pages, and DISTINCT to remove repeated rows.
Module practice
- Every book in the tableEasy
- Books from 2000 onwardsMedium
- Add the new arrivalsHard
- Three new studentsEasy
- Design the products tableMedium
- Change the design of a live tableHard
- Good marks in DhakaEasy
- Repair the name searchMedium
- A contact for everyone outside the big citiesHard
- The top threeEasy
- Every city onceEasy
- The second page of resultsMedium
- Each city and department once, unknown cities lastHard