Study guide: SQL Data Entry and Queries

Course Outline

  1. Creating the Database and Tables
  2. Adding Table Constraints
  3. Modifying Table Structure
  4. Inserting and Validating Data
  5. Selecting and Sorting Data

Key Dates

  1. 15 September 2026Yassine registered for swimming and karate.
  2. 20 September 2026Salma registered for fitness.
  3. 1 October 2026Ines registered for fitness and swimming.

1. Creating the Database and Tables

★ Must-know

  • Create a table with CREATE TABLE, listing each column and its type; declare a single-column primary key by adding PRIMARY KEY to that column.

📌 A composite primary key is declared once at the end of the table, as PRIMARY KEY (cin, codeAct); writing PRIMARY KEY separately for each column is an error.

  • The adherents table contains:
    • cin CHAR(8) as the primary key
    • nom VARCHAR(30)
    • prenom VARCHAR(30)
    • email VARCHAR(40)
    • age INT
    • adresse VARCHAR(50)

Further detail

  • Create and select a database with CREATE DATABASE name; followed by USE name;.

  • The activites table contains:

    • codeAct CHAR(4) as the primary key
    • libelle VARCHAR(30)
    • tarif DECIMAL(6,3)
    • places INT

2. Adding Table Constraints

★ Must-know

  • Use ALTER TABLE ... MODIFY column TYPE NOT NULL to make a column mandatory; MODIFY requires rewriting the column’s type.

  • Add foreign keys from inscriptions.cin to adherents.cin and from inscriptions.codeAct to activites.codeAct; ON DELETE CASCADE on the first key deletes an adherent’s inscriptions when that adherent is deleted.

  • A unique constraint such as uqEmail on adherents.email prevents two adherents from having the same email; a duplicate value is rejected with error 1062.

  • The age constraint CHECK (age BETWEEN **12** AND **70**) accepts ages from 12 through 70, including both endpoints.

  • The activites.libelle column is mandatory, and the named constraint ckTarif requires tarif > 0; these rules are added separately using MODIFY ... NOT NULL and ADD CONSTRAINT ... CHECK.

Further detail

  • A CHECK condition email LIKE '%@%' requires the email value to contain @; LIKE with % matches any sequence of characters.

  • Setting activites.places to DEFAULT 20 assigns 20 places when an insertion omits that column.

  • The inscriptions.dateInsc column can be made mandatory and default to the current date in one statement: MODIFY dateInsc DATE NOT NULL DEFAULT (CURRENT_DATE).

  • A foreign-key value must already exist in its referenced parent table, so adherents and activites must be filled before inscriptions.

3. Modifying Table Structure

★ Must-know

  • To change a named constraint such as ckAge, drop it with ALTER TABLE table DROP CONSTRAINT name, then add it again with the updated condition; the requested lower age limit is 10 while the upper limit remains 70.

📌 Add a column with ALTER TABLE table ADD column TYPE; adding telephone CHAR(8) stores an eight-digit phone number as a character string rather than a number for calculations.

📌 Rename a column and set its type with ALTER TABLE table CHANGE oldName newName TYPE; the adresse column can thereby become ville VARCHAR(30).

Further detail

📌 The genre CHAR(1) column can be restricted to H or F with the named constraint ckGenre using CHECK (genre IN ('H', 'F')).

📌 Change a column’s type with ALTER TABLE table MODIFY column newType; changing email to **VARCHAR(50)** preserves its separately declared uqEmail constraint.

4. Inserting and Validating Data

★ Must-know

📌 An unknown value is entered as NULL without apostrophes, while text values are enclosed in apostrophes and numbers are not.

  • The relevant constraint types are:

    • CHECK
    • UNIQUE
    • primary key
    • foreign key
  • When a column is omitted from an INSERT column list, the inserted row receives that column's default value.

Further detail

  • The adherents table contains cin, nom, prenom, email, age, ville, telephone, and genre.

  • The activity TEN1 is inserted without a places value and receives the default of 20 places.

  • When an inscription's date is omitted, the date of the day is supplied by the default value.

  • An adherent aged 8 is rejected by ckAge because the permitted age range is 10 to 70.

  • An INSERT using the already-existing email [email protected] is rejected by uqEmail.

  • An inscription referring to activity YOG1 is rejected by fkInscAct when YOG1 does not yet exist in activites.

  • An activity with a tariff of 0 is rejected by ckTarif because the tariff must be greater than 0.

  • An inscription for an existing adherent and activity is accepted when its date is omitted, and the date defaults to the current day.

5. Selecting and Sorting Data

★ Must-know

  • SELECT * FROM table_name returns all columns of the chosen table.

  • A SELECT query uses WHERE with comparison operators =, <>, <, >, <=, or >= to filter rows.

  • AND selects rows only when both conditions are true, while OR selects rows when at least one condition is true.

  • ORDER BY sorts results in ascending order by default, and ORDER BY column DESC sorts them in descending order.

Further detail

📌 In an OR filter comparing one column with multiple values, each condition must repeat the column, such as ville = 'Sousse' OR ville = 'Sfax'.

  • The query SELECT libelle, tarif FROM activites WHERE tarif < 40 returns Fitness at 35.000 and Karaté at 30.000.

  • To find an adherent's activity registrations, query inscriptions, which contains both cin and codeAct; for example, cin '44556677' has registrations for FIT1 and NAT1 on 2026-10-01.

  • To find the CINs registered for NAT1, query inscriptions with codeAct = 'NAT1'; the result contains '11223344' and '44556677'.

Test yourself

Take a 17-question practice quiz on SQL Data Entry and Queries, with every answer explained.

1. An `age` check uses `CHECK (age BETWEEN 12 AND 70)`. Which age is accepted by the constraint?

2. Which definition correctly specifies the `adherents` table’s columns and primary key?

Take the quiz →

Study with flashcards

Memorize the key concepts of SQL Data Entry and Queries with 39 flashcards.

How do you declare a single-column primary key when creating a table?

Add `PRIMARY KEY` to that column’s definition.

Which column is the primary key of the `adherents` table?

`cin CHAR(8)`.

Which fields and types make up the `adherents` table besides its primary key?

`nom VARCHAR(30)`, `prenom VARCHAR(30)`, `email VARCHAR(40)`, `age INT`, and `adresse VARCHAR(50)`.

See the flashcards →

Related classes

Make your own study guides

Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.

Study guide maker