Skip to main content
Techlogia — AI and Web Development Berlin

Practise this module for free on a real VM

Sign up for free
 All courses
Free to read – no sign-up

SQL Basics

SELECT, WHERE, INSERT, JOIN, GROUP BY. Write 5 SQL statements into a .sql file. Schema is in the task.

Duration: 30 minLevel: BeginnerExercises: 5

Your first SQL queries

SQL basics: your first queries

SQL (Structured Query Language) is the language for querying and changing databases. Data lives in tables (rows = records, columns = fields). Almost every application stores its data in an SQL database.

Your database

Your VM has a real SQLite database ~/schule.db with two tables:

  • schueler (id, name, klasse) — 6 students
  • noten (id, schueler_id, fach, note) — 12 grades

Have a look: sqlite3 ~/schule.db opens the database, .tables lists the tables, .schema schueler shows the columns, .quit exits. (Right after start the VM is still installing sqlite3 — if the console says "command not found", wait a moment.)

Key commands

  • SELECT: query data ("show me …").
  • WHERE: filters rows by a condition.
  • INSERT: adds a new record.
  • JOIN: connects two tables via a shared column.
  • GROUP BY + AVG(): groups rows and computes e.g. averages.

You write each query into its own file under /home/student/sql/ and run it with sqlite3 ~/schule.db < ~/sql/file.sql — you immediately see the real result.

How the checker works

It runs your file against a fresh copy of the database and compares the result. So your experiments in ~/schule.db do not get in the way. Broke your database? cp /var/lib/lab/sql/schule.db ~/schule.db restores it.

Your goal

You write five queries — from a simple selection to an analysis across multiple tables.

Exercises

  1. 1. Query all students

    Concept: SELECT. SELECT * FROM table fetches all columns (*) of all rows of a table. Query all students:

    mkdir -p /home/student/sql
echo "SELECT * FROM schueler;" > /home/student/sql/alle.sql
sqlite3 ~/schule.db < /home/student/sql/alle.sql

    You see six lines like 1|Anna|7a.

    Check: alle.sql returns all 6 students with all columns (id, name, klasse). The checker runs your file against the database itself.

  2. 2. Students of class 7a

    Concept: WHERE. With WHERE you filter rows by a condition. Fetch only the students of class 7a:

    echo "SELECT * FROM schueler WHERE klasse = '7a';" > /home/student/sql/klasse-7a.sql
sqlite3 ~/schule.db < /home/student/sql/klasse-7a.sql

    Text values are in single quotes ('7a').

    Check: klasse-7a.sql returns exactly the three students of 7a — with all columns.

  3. 3. Insert a new student

    Concept: INSERT. INSERT INTO adds a new record: first the columns, then the values. The database assigns the id itself. Add the new student Lisa from class 7b and check the result:

    echo "INSERT INTO schueler (name, klasse) VALUES ('Lisa', '7b');" > /home/student/sql/neu.sql
sqlite3 ~/schule.db < /home/student/sql/neu.sql
sqlite3 ~/schule.db "SELECT * FROM schueler;"

    If you run the file twice, Lisa appears twice in your database — no problem: the checker always runs neu.sql on a fresh copy.

    Check: After neu.sql, schueler contains the 6 existing students plus Lisa from 7b.

  4. 4. Grades with student name

    Concept: JOIN. A JOIN connects two tables via a shared column — noten.schueler_id holds the id from schueler. This is how you show grades with the name:

    echo "SELECT schueler.name, noten.fach, noten.note FROM schueler JOIN noten ON schueler.id = noten.schueler_id;" > /home/student/sql/noten-mit-namen.sql
sqlite3 ~/schule.db < /home/student/sql/noten-mit-namen.sql

    Check: noten-mit-namen.sql returns all 12 grades, each with name, fach and note (in this order).

  5. 5. Average grade per subject

    Concept: aggregation (AVG + GROUP BY). Aggregate functions combine several rows into one value — AVG() computes the average. GROUP BY fach does it per subject:

    echo "SELECT fach, AVG(note) FROM noten GROUP BY fach;" > /home/student/sql/schnitt.sql
sqlite3 ~/schule.db < /home/student/sql/schnitt.sql

    Check: schnitt.sql returns one line per subject with the average grade — for Deutsch, Englisch and Mathe.

Knowledge check: SQL basics

No VM, about 3 minutes. You recall what you practised in this module – wrong answers come back once more at the end.

Related courses

SSH HardeningYou harden your server VM's SSH configuration against the most common attack vectors — disable root login, switch off password auth, limit login attempts, detect dead connections with keep-alive. 7 tasks, about 60 minutes.Hello WorldYour start in the lab: in a few minutes you learn how the automatic check verifies your solutions on the real VM — and you finish by blocking root login via SSH. 4 short tasks.Deploy a live website with HTTPSWithin an hour you'll get your own website online — with a valid HTTPS certificate from Let's Encrypt, your own subdomain, and real nginx. You'll edit HTML, check nginx config, and trigger certbot. At the end you can share the link — it stays live as long as the lab session runs (max. 60 minutes).UFW + fail2banYou set up a basic server firewall with UFW (default deny + whitelist) and protect SSH against brute-force attempts using fail2ban. 6 tasks, about 55 minutes.

Now practice it yourself

Reading is good – doing is better. Practise this module on a real Linux VM, right in your browser. A free account is all it takes.

Start for free

Lab content under CC BY 4.0 – free to use with attribution (© TechLogia).

How do you like this page?