★ Must-know
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); writingPRIMARY KEYseparately for each column is an error.
adherents table contains:
cin CHAR(8) as the primary keynom VARCHAR(30)prenom VARCHAR(30)email VARCHAR(40)age INTadresse 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 keylibelle VARCHAR(30)tarif DECIMAL(6,3)places INT★ 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.
★ Must-know
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; addingtelephone 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; theadressecolumn can thereby becomeville VARCHAR(30).
Further detail
📌 The
genre CHAR(1)column can be restricted toHorFwith the named constraintckGenreusingCHECK (genre IN ('H', 'F')).
📌 Change a column’s type with
ALTER TABLE table MODIFY column newType; changing**VARCHAR(50)**preserves its separately declareduqEmailconstraint.
★ 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:
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.
★ 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'.
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?
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)`.
Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.
Study guide maker