Advanced SQL Operations and Constraints
This page covers more advanced SQL operations including data modification commands and table constraints, which are important topics for SQL NSI Terminale and base de données - nsi terminale studies.
Data Modification Commands
INSERT INTO
The INSERT INTO command is used to add new records to a table.
Example:
INSERT INTO newTable (attribut1, attribut2)
VALUES ('donnée1', 'donnée2');
DELETE FROM
DELETE FROM is used to remove records from a table based on specified conditions.
Example:
DELETE FROM table1
WHERE condition;
UPDATE
UPDATE modifies existing records in a table.
Example:
UPDATE table1
SET attribut1 = 'nouvelle_valeur'
WHERE condition;
Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single result.
COUNT
COUNT returns the number of rows that match the specified criteria.
Example:
SELECT COUNT(attribut1)
AVG
AVG calculates the average value of a numeric column.
Example:
SELECT AVG(attribut1)
SUM
SUM calculates the total sum of a set of values.
Example:
SELECT SUM(attribut1)
Table Constraints
Constraints are used to specify rules for the data in a table, ensuring data integrity.
Domain Constraints
Domain constraints limit the type of data that can be stored in a column.
Example:
CREATE TABLE "Clients"(
"nom" TEXT,
"prenom" TEXT CHECK(length(prenom) <= 30),
"age" INTEGER CHECK(age > 0 AND age < 200),
"ville" TEXT CHECK(ville IN ('Niort', 'Aiffres'))
);
Relation Constraints
Relation constraints define relationships between tables.
Example:
CREATE TABLE "Clients"(
"id_client" INTEGER PRIMARY KEY AUTOINCREMENT,
"nom" TEXT,
"prenom" TEXT CHECK(length(prenom) <= 30),
"age" INTEGER CHECK(age > 0 AND age < 200),
"ville" TEXT CHECK(ville IN ('Niort', 'Aiffres'))
);
Reference Constraints
Reference constraints, also known as foreign key constraints, ensure that relationships between tables remain consistent.
Example:
CREATE TABLE "Livres" (
"id_livre" INTEGER PRIMARY KEY AUTOINCREMENT,
"nom" TEXT,
"prix" REAL,
"id_client" INTEGER,
FOREIGN KEY("id_client_fk") REFERENCES "Clients" ("id_client")
);
These advanced SQL concepts and constraints are crucial for maintaining data integrity and are often featured in sujet bac NSI SQL and NSI épreuve bac examinations. Understanding these topics thoroughly will greatly benefit students preparing for their NSI révision bac.
