DATABASE / THEORY.

Que fait SELECT DISTINCT ?

Supprime les doublons

Que signifie BETWEEN en SQL ?

Utilisé pour faire des comparaisons entre des valeurs. (et pas >=)

Que représente un masque en SQL ?

Définit une famille de chaînes de caractères comme 'B_' ou '%SAPIN%'.

Que retourne la commande SELECT NOM, ADRESSE, COMPTE

WHERE COMPTE > 0 AND (CAT = 'C1' OR LOCALITÉ = 'Paris') ?

Affiche les clients créditeurs dans la catégorie 'C1' ou situés à 'Paris'.

Que fait la fonction TRIM ?

Utilisée pour effacer les espaces blancs.

Que fait la commande CAST en SQL ?

Effectue une conversion de type.

Comment soustraire la 2ème et 3ème lettre de la catégorie ?

Utiliser 'from 2' jusqu'à la 3ème lettre, sans mettre 'for 3' pour éviter d'inclure la 4ème lettre.

SELECT SUM(QSTOCK*PRIX)

FROM PRODUIT

WHERE LIBELLE LIKE '%SAPIN%'; ?

Calcule le total du montant en stock des produits ayant le libellé 'SAPIN'.

SELECT COUNT(DISTINCT NCLI)

FROM COMMANDE ?

Affiche le nombre de clients distincts dans la colonne COMMANDE.

Donnée dérivée ?

Donnée qu’on peut trouver en la calculant (ex:sous-total).

SQL ?

Standard Query Language, langage permettant de communiquer une BDD ou avec les données d’un site web.

Clé primaire ?

= Identifiant unique dans une table, permettant de distinguer les enregistrements.

Mode no-action ?

Mode de suppression qui refuse la suppression si des dépendances existent. Fais en sorte qu’on ne perde pas les données cruciales.

Mode cascade ?

supprime toutes les commandes associées à un client. (→ meilleur mode)

Mode set null ?

crée un client fictif à valeur nulle lors de la suppression d'un client.

Mode set default ?

similaire à set null, mais permet de définir les données du client. Il permet de définir une valeur par défaut pour une colonne lorsque les données ne sont pas spécifiées lors de l'insertion.

Redondances internes ?

Gaspillage d’espace dû à des données répétées dans une base de données.

Dépendance fonctionnelle anormale ?

C’est quand le déterminant n'est pas une clé primaire → problèmes de redondances. Pour la normaliser, décomposition de la table.

Comment créer une table et ses colonnes ?

create table CLIENT (NCLI char(10)

Numérique exact ?

Smallint, integer, decimal, numeric

Numérique approché ?

Real, float, double

Temps instant ?

time

Temps instant daté ?

timestamp

Temps intervalle ?

Interval

Comment créer un domaine ?

create domain MONTANT

Ou create domain MATRICULE,

…

Valeur par défaut d’une colonne ?

Create domain MONTANT default

Clés étrangères ?

Foreign key (NCLI) reference CLIENT);

Suppression d’une table ?

Drop table COMMANDE;

→ attention à ce que la table soit plus référencée par aucune clé, il ne doit plus avoir de traces.

Ajouter une colonne ?

Alter table PRODUIT add column POIDS small int;

supprimer une colonne ?

Alter table PRODUIT drop column PRIX;

Modifier valeur par défaut ?

Alter table CLIENT modify column CAT set ‘00’;

Supprimer valeur par défaut ?

Alter table CLIENT modify column CAT drop default;

Ajouter contrainte ?

Alter table PROSPECT add primary key (NCLI);

Enlever contrainte ?

Alter table CLIENT modify CAT not null;

Ajouter contraintes nommées ?

Alter table PROSPECT

add constraint PROPK primary key (NCLI);

Suppression d’une contrainte nommée ?

Alter table PROSPECT

Drop constraint PROPK;

Comment ajouter un index ?

create unique index XCLI_NCLI on CLIENT;

Comment créer un espace de stockage ?

create dbspace CLI_PRO_DAT;

Comment enlever un espace de stockage ?

drop dbspace CLI_PRO_DAT;

Est ce que c’est mieux de disperser les données dans différents espaces de stockage ?

Oui, ça permet de réduire la consommation de puissance du CPU.

Fichier log ?

Enregistre toutes les transactions de la base de donnée → permet de revenir en arrière si erreur.

Domaine de valeur ?

> c’est un domaine qu’on crée.

-> permet de gagner du temps si l’on doit changer une valeur dans pleins de données, on aura juste à le changer 1fois et pas 100fois.

Il vaut mieux nommer mes contraintes, mais comment ?

-> constraint COMPK primary key (NOM);

-> plus simple si jamais on doit supprimer qqch.

La jointure ?

→ permet de produire une table constituée de données extraites de plusieurs tables :
where COMMANDE.NCLI = CLIENT.NCLI;

La sous-requête permet de formuler ?

·   une condition d'association (in)
·   une condition de non-association (not in)

La jointure permet de formuler ?

une condition d'association

union ?

→ ∪ → combine les résultats de deux ou + éléments SÉLECT et enlève n’importe quelles lignes duppliquées

intersection ?

→ ∩

différence ?

→ - → retourne les lignes qui sont dans le premier SÉLECT mais pas dans le deuxième

Comment lire (écrire) une requête ?

6 :  select NCLI, count(*), sum(QCOM)
1 :  from   COMMANDE M, DETAIL D
2 :  where  M.NCOM = D.NCOM
3 :  and    NPRO = 'PA60'
4 :  group by NCLI
5 :  having count(*) >= 2
7 :  order by NCLI

Insérer une ligne ?

→ insert into … ();

Insérer plusieurs lignes ?

Insert into CLIENT_TOULOUSE

Suppression de lignes ?

delete from …

Modifier des lignes ?

update …

Une jointure peux dans tous les cas être remplacée par une sous-requête ?

Oui, car les sous requêtes permettent de formuler un ‘in’ (condition d’association) ainsi qu’un ‘not in’ (condition de non association)

Les jointures permettent quant à elles de formuler une condition d’association, on peut donc affirmer qu’une jointure puisse dans tous les cas être remplacée par une sous-requête mais pas le contraire.

Commandes qui ne spécifient pas le produit PA60 ?

SELECT NCOM, DATECOM, NCLI

FROM COMMANDE,

WHERE NCOM in (SELECT NCOM FROM Detail WHERE NPRO <> “PA60”);

Une clé étrangère référence … ?

→ l’identifiant primaire de la table cible

Pour modifier contrainte obligatoire ?

• insérer une ligne → valeur doit être nulle

• Supprimer une ligne → OK

• Modifier → nouvelle valeur doit être nulle

Pour modifier contrainte d’unicité - identifiant ?

• insérer → valeur doit pas être en double

• Supprimer → OK

• Modifier → nouvelle valeur doit pas être en double

Pour modifier contrainte d’intégrité référentielle - clé étrangère ?

• insérer → valeur doit être présente dans l’ID auquel NCLI fait référence

• Supprimer → OK

• Modifier → nouvelle valeur doit être présente dans l’ID qui est lié.

On mets toujours un index sur ?

→ clé primaire et clé étrangère

Quand est ce qu’on supprime une contrainte?

Lors de migration

Avant de supprimer une table, il faut vérifier qu’elle ne soit plus ?

référentiée

Étapes SQL ?

1. Créer base de donnée → create schema … ;

2. Créer espace stockage → create dbspace … ;

3. Créer les tables → create table … ;

4. Créer les indexes → create index … ;

DML ?

Insert

Delete

Update

Select

CAT in ?

→ Choix, soit … soit … (pas la même chose que <>)

LOCALITE not in ?

→ choix, ni … ni …

COMPTE between ?

→ intervalle ouvert (→ COMPTE between 1000 and 4000;)(→ compris)

CAT like ?

→ commence par B suivie de n’importe quelle chaîne de ch. (→ Where CAT like ‘B_’;)

B_ ?

→ 1 seul ch. après B

B__ ?

→ 2 ch. après B

’%SAPIN%’

Chaîne quelquonque

Comment créer des alias SELECT ?

= renommer virtuellement des colonnes → select NPRO as Produit … ;

TRIM ?

3 arg : both, left, right → regarde si il y a un espace blanc avant ou après

// ?

Concaténation → s’applique qu’à des chaînes de caractères.

Upper ?

Affiche toutes les localités en maj.

Lower ?

Affiche toutes les localités en minuscule.

Position ?

Regarde si le mot recherché est dans le nom, et à quelle position. Si oui, nombre indiquant la position, si non, 0.

Comment convertir le format date en format char ?

Select nom // ‘’ // cast (date de naissance as char (12))

Extraire l’année d’une date ?

Extract (year from Datecom) +1

Extract (hour from current_time) > 18

Afficher prix total stock de produits mais que ceux libellés ‘sapin’ ?

→ select sum (QTOCK*PRIX)

From PRODUIT

Where LIBELLÉ like ‘%SAPIN%’;

En SQL, on peut pas exprimer directement … ?

→ le quantificateur pour tout, pour tout n’existe pas. → donc on utilise exists.

Peut on remplacer une sous requête par une jointure ?

Si la sous requête exprime une condition d’association, OUI. MAIS, si la sous requête exprime une condition de non-association, NON.

Si on veut que les doublons s’affichent ?

On ajoute all.

Jointure externe ?

Jointure qui cherche les externalités de la jointure

Structure directe ?

1 table, 1 clé primaire, des colonnes et 1 de ces colonnes est une clé étrangère qui pointe sur la même table

<> ?

Différent de, vérifie que 2 valeurs sont bien inégales

Dans quel cas est ce que c’est obligé d’utiliser le having ?

avec les fonctions d’agrégations, et lorsqu’on a déjà un group by, et qu’on veut le restreindre.

Dans quel cas est ce obligé d’utiliser un group by ?

Lorsque dans un select il y a plusieurs fonctions d’agrégations et une colonne sans agrégation.

Différence entre une union et une jointure externe totale entre 2 tables ?

Une jointure liera les colonnes tandis que l’union liera des lignes.

Jointure externe totale ?

Lorsque la condition est vraie sur min. 1 table, retournera les résultats.

Where ?

c’est mettre une condition sur l’affichage, “afficher qqch dont … , qqch qui est …

Having ?

Agit comme un filtre aussi, (comme le where) mais la différence, c’est qu’on peut utiliser les fonctions agrégatives avec cette méthode, souvent utilisé avec un Group By.

Différence entre full outer join et union ?

Une union va permettre de fusionner 2 tables sans critère en supprimant les doublons et en conservant la structure d’origine des tables. Alors qu’une Full Outer Join, va mettre en relation 2 tables selon un critère d’égalité à respecter pour obtenir les infos.

Jointure externe ?

Fait le lien entre 2 tables afin d’en retirer toutes leurs infos. Elle est “externe” dès que l’on cherche à obtenir les infos qui sont communes aux 2 tables mais qu’on souhaite aussi obtenir ses infos externes, les données d’une des 2 tables.

Index unique ?

Permet de spécifier qu’1 ou plusieurs colonnes doivent contenir des valeurs uniques à chaque enregistrement. (→ pour rendre un index unique, ajouter UNIQUE dans la ligne.)

Principe indépendance physique ?

tous les aspects matériels de la base de données n’apparaissent pas pour l’user, c’est juste une structure transparente de représentation des infos.

Principe d’indépendance logique ?

L’admin de la base doit pouvoir faire évoluer sans que cela gène les users.

Objectif clé étrangère ?

D’assurer l’intégrité référentielle des données.

Contrainte d’intégrité obligatoire ?

Colonnes qui doivent absolument contenir une info, cette contrainte est obtenue en la spécifiant comme : not null.