DB(general syntaxes)

What are the 3 main SQL language categories?

DDL (structure), DML (data manipulation), DCL (access control)

What is DDL (LDD)?

Language used to create and modify database structure (tables, schema)

What is DML (LMD)?

Language used to insert, update, delete, and query data

What is DCL (LCA)?

Language used to manage user permissions and privileges

What does CREATE DATABASE do?

Creates a new database

SQL syntax to create a database

CREATE DATABASE nom_base;

Why use USE command in SQL?

To select the database to work on

SQL syntax to select a database

USE nom_base;

What does DROP DATABASE do?

Deletes a database and all its data permanently

SQL syntax to delete a database

DROP DATABASE nom_base;

What does SHOW DATABASES do?

Lists all available databases

What is a table in SQL?

A structure used to store data in rows and columns

SQL syntax to create a table

CREATE TABLE nom_table (colonne TYPE contraintes);

What is INT type?

Integer number

What is VARCHAR?

Short text (variable length string)

What is TEXT?

Long text

What is DATE?

Date in format YYYY-MM-DD

What is DATETIME?

Date and time (YYYY-MM-DD HH:MM:SS)

What is DECIMAL(p,s)?

Decimal number with precision and scale

What is BOOLEAN?

TRUE or FALSE values

What is FLOAT?

Floating-point number

What is a PRIMARY KEY?

Unique identifier for each row in a table

Two ways to define PRIMARY KEY

Inline in column OR at end of table definition

Why is PRIMARY KEY important?

Ensures uniqueness and prevents duplicate rows

What is a FOREIGN KEY?

A foreign key is a column in one table that points to the primary key of another table.(It creates a link (relationship) between two tables.)

Purpose of FOREIGN KEY

Maintains referential integrity between tables

SQL syntax for FOREIGN KEY

FOREIGN KEY (col) REFERENCES table(col)

What does NOT NULL do?

Ensures a column cannot have NULL values

SQL syntax for NOT NULL

colonne TYPE NOT NULL

What does UNIQUE constraint do?

Ensures all values in a column are different

Two ways to define UNIQUE

inline definition OR after table definition

What is CHECK constraint?

Enforces a condition on column values

SQL syntax for CHECK

CHECK (condition)

What is DEFAULT constraint?

Sets a default value if none is provided

SQL syntax for DEFAULT

colonne TYPE DEFAULT valeur

What does ALTER TABLE do?

Modifies an existing table

SQL syntax to add a column

ALTER TABLE nom_table ADD colonne TYPE;

SQL syntax to modify column type

ALTER TABLE nom_table MODIFY colonne TYPE;

SQL syntax to rename a column

ALTER TABLE nom_table RENAME COLUMN old TO new;

SQL syntax to add a constraint

ALTER TABLE nom_table ADD CONSTRAINT name TYPE (col);

SQL syntax to drop a column

ALTER TABLE nom_table DROP COLUMN colonne;

SQL syntax to drop a constraint

ALTER TABLE nom_table DROP CONSTRAINT name;

SQL syntax to delete a table

DROP TABLE nom_table;

What does SHOW TABLES do?

Lists all tables in the current database

What does DESCRIBE or DESC do?

Shows table structure (columns, types, constraints)

What is referential integrity?

Ensures consistency between related tables

What happens with ON DELETE CASCADE?

Deletes child rows automatically

What happens with ON UPDATE CASCADE

Updates child rows automatically

What happens with ON DELETE SET NULL

Sets foreign key to NULL when parent is deleted

What happens with ON UPDATE SET NULL

Sets foreign key to NULL when parent is updated

What does RESTRICT do?

Prevents deletion/update if referenced

What does NO ACTION do?

Like RESTRICT but checked at end of transaction