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.

SQL Language Fundamentals

The SQL language is essential for managing and querying databases, a key component of the NSI Terminale curriculum. This page covers fundamental SQL commands and their syntax.

SELECT Command

The SELECT command is used to retrieve data from one or more database tables.

Definition: SELECT allows you to choose specific columns or all columns (*) to display from a table.

Syntax examples:

SELECT attribut1, attribut2;
SELECT *;

FROM Clause

The FROM clause specifies which table to query.

Example:

SELECT attribut1, attribut2
FROM nomTable;

WHERE Clause

WHERE is used to filter results based on specific criteria.

Example:

SELECT attribut1, attribut2
FROM nomTable
WHERE annee >= '1903';

Combining Criteria

Multiple conditions can be combined using AND, OR, and NOT operators.

Example:

SELECT attribut1, attributt2
FROM nomTable
WHERE annee >= '1903'
AND nom = 'Bray';

LIKE Operator

LIKE is used for pattern matching in string comparisons.

Vocabulary:

• 'A%' matches strings starting with 'A'
• '%A' matches strings ending with 'A'

Example:

SELECT attribut1, attribut2
FROM nomTable
WHERE nom LIKE 'N%';

ORDER BY Clause

ORDER BY is used to sort the result set.

Vocabulary:

• DESC: Descending order
• ASC: Ascending order (default)

Example:

SELECT attribut1, attribut2
FROM nomTable
WHERE annee >= '1903'
ORDER BY DESC;

DISTINCT Keyword

DISTINCT eliminates duplicate rows from the result set.

Example:

SELECT DISTINCT attribut1, attribut2;

INNER JOIN

INNER JOIN combines rows from two or more tables based on a related column between them.

Example:

SELECT *
FROM table1
INNER JOIN table2
WHERE table1.id = table2.fk_id;

This comprehensive overview of SQL basics is crucial for NSI révision bac and exercice SQL NSI practice.