Programming Open textbook

SQL for beginners: statements, table design and foreign keys

Built automatically from early chapters of the open textbook "Structured Query Language" on Wikibooks: language elements, creating tables, handling data and an example database. Not reviewed by a person.

3 lessons, 20 steps

Get it on Google Play

Reading and writing SQL, step by step

SQL is the language for working with relational databases, and the basics fit into a small set of statements. This course helps you learn SQL basics by answering questions about real snippets: which clause comes first, what a command does to a table, which column holds the key.

Lesson 1 is a tour of the language elements: how a statement ends, the || operator for joining strings, the order of SELECT, FROM, WHERE, GROUP BY and ORDER BY, the set operations UNION, INTERSECT and EXCEPT (and Oracle's MINUS), and what TRUNCATE leaves in place. Lesson 2 creates and changes a table: CHAR versus VARCHAR, the NOT NULL constraint, SELECT with an asterisk, the SET keyword in UPDATE, and what DELETE without WHERE leaves behind. Lesson 3 moves to database design: why contact details go into their own table, how a foreign key such as person_id models a one-to-many relationship, a table in the middle for many-to-many, and an INSERT with a SELECT inside that doubles the rows of a table.

Lessons begin with a short explanation and continue with five to eight steps: multiple choice, putting clauses in order, or typing a keyword or a number. When an answer is off, the AI tutor asks a pointed question first, then gives a hint, then part of the solution. Show solution is always there if you want it, and a short quiz before and after each lesson shows what you picked up.

The course was generated automatically from the first chapters of the open textbook "Structured Query Language" on Wikibooks, without review by a person. The book is a volunteer project and may contain errors, and the three lessons cover only its beginning.

What you'll learn

  • Identify fundamental SQL language elements, predicates, and operators.
  • Construct CREATE TABLE and DROP TABLE statements with appropriate column types and constraints.
  • Design relational schemas modeling one-to-many and many-to-many relationships using foreign keys.

Lessons

  1. SQL Language Elements and Syntax Snippets

    8 steps, 1 min intro

  2. Defining Tables and Basic Data Manipulation

    5 steps, 1 min intro

  3. Relational Database Design and Sample Data

    7 steps, 1 min intro

One step from lesson 1

Tutor: What happens to the table structure when a TRUNCATE TABLE command runs?

You: The whole table structure, indices included, is thrown away.

Tutor: Which command completely throws away the structure inclusive of indices?

You: That's DROP TABLE. TRUNCATE only deletes the rows, so the structure remains.

Tutor: Exactly. After TRUNCATE the table is still there, just empty.

Based on a real step from lesson 1. The tutor's wording varies, and AI can be wrong: the tutor answers from the course material and labels a reply it could not check.

Who should take it

  • You are new to databases and want to learn SQL from the statements up.
  • You write report or analytics queries and want firmer ground on table design and keys.
  • You know SELECT but get unsure about TRUNCATE, DELETE and DROP.
  • You are about to start a course or job that uses SQL and want practice questions first.

SQL course FAQ

Which database system does the course use?

The lessons teach standard SQL and point out a few differences between systems, for example MINUS in Oracle in place of EXCEPT, and LONGTEXT in MySQL for very long strings. You answer the questions in the app, so you do not need a database installed.

Does the course cover JOINs?

No. The three lessons cover statements, table definitions, keys and sample data. Joins are not part of them.

Is the course free?

One lesson in Ai Teacher is free. After that, a subscription opens all lessons. You can pay monthly or yearly in Google Play, and usage limits apply.

Source and license

This course is adapted from Structured Query Language, an open textbook on Wikibooks written by Wikibooks contributors (see the page history of each chapter below). The textbook is licensed under CC BY-SA 4.0.

This course was built automatically from the open textbook "Structured Query Language" on Wikibooks. No person has reviewed it. Changes: Adapted by AI into lessons and questions; shortened. Written by volunteers; it may contain errors.

Chapters used

  1. Language Elements revision 3698562, authors
  2. Snippets revision 3698566, authors
  3. Create a simple Table revision 4627191, authors
  4. Handle Data revision 4660930, authors
  5. Example Database Structure revision 3698876, authors
  6. Example Database Data revision 4069437, authors

Start Structured Query Language in Ai Teacher

One lesson in the app is free. Ai Teacher is available for Android on Google Play.

Get it on Google Play