Voici une cheatsheet d'entretien technique SQL en français :
Questions théoriques fréquentes
Q: Quelle est la différence entre un JOIN et une UNION ? La clé est dans les mots : JOIN permet de combiner des lignes de deux tables sur une condition commune, tandis que UNION combine le résultat de plusieurs SELECT en une seule table sans doublon.
Q: Qu'est-ce qu'un index et pourquoi l'utiliser-t-on ? Un index est une structure de données qui améliore les performances de la recherche. En ajoutant un index sur une colonne fréquemment utilisée dans des WHERE, ON ou ORDER BY, on accélère considérablement la vitesse de récupération des données.
Q: Expliquez le principe du dénormalisation. La dénormalisation consiste à stocker les mêmes données plusieurs fois dans différentes tables pour améliorer les performances de lecture, en évitant ainsi les jointures complexes et les requêtes lentes.
Q: Qu'est-ce qu'un sous-requête ? Une sous-requête est une requête imbriquée qui retourne un ensemble de résultats utilisé par la requête principale. Elle peut être utilisée dans WHERE, FROM ou HAVING.
Q: Expliquez le principe de l'ACID. ACID signifie Atomicity (atomicité), Consistency (cohérence), Isolation (isolation) et Durability (permanence). C'est un ensemble de propriétés essentielles pour une base de données transactionnelle qui garantit l'intégrité des données.
Q: Qu'est-ce qu'uneNormalization ? La normalisation est le processus de structurer les données en tables pour éviter la redondance et les anomalies. Cela permet de respecter les règles d'ACID et d'améliorer les performances des requêtes.
Q: Expliquez la différence entre SELECT DISTINCT et GROUP BY. SELECT DISTINCT retourne toutes les lignes uniques, alors que GROUP BY regroupe les lignes en fonction d'une ou plusieurs colonnes, appliquant une fonction d'agrégation comme COUNT, SUM, AVG, etc.
Q: Qu'est-ce qu'une clé étrangère et pourquoi l'utiliser-t-on ? Une clé étrangère est une colonne dans une table qui fait référence à la clé primaire d'une autre table. Elle permet de maintenir les relations entre les tables et d'assurer l'intégrité référentielle.
Q: Expliquez le principe du cache en base de données. Le cache en base de données stocke temporairement des données pour accélérer les requêtes futures. Cela réduit la charge sur les systèmes de stockage et améliore les performances globales.
Q: Qu'est-ce qu'un trigger ? Un trigger est une procédure déclenchée automatiquement lors d'une action spécifiée (INSERT, UPDATE, DELETE) sur une table. Ils sont utilisés pour appliquer des règles métier, vérifier l'intégrité des données ou effectuer des opérations secondaires.
Exercices de code classiques
Exercice 1 : Trouver les employés qui gagnent plus que la moyenne
SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Exercice 2 : Calculer le nombre de clients par ville
SELECT city, COUNT(*) AS number_of_customers
FROM customers
GROUP BY city;
Exercice 3 : Trouver les produits qui n'ont pas été vendus
SELECT product_name
FROM products
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE order_id = product_id);
Exercice 4 : Calculer le total des ventes par produit
SELECT product_name, SUM(quantity * price) AS total_sales
FROM products JOIN sales ON products.product_id = sales.product_id
GROUP BY product_name;
Exercice 5 : Trouver les employés avec une salaire supérieur à la moyenne et qui ont plus de 10 ans d'ancienneté
SELECT employee_name, years_of_service
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
AND years_of_service > 10;
Pieges courants en entretien
Piege 1 : Ne pas utiliser l'index pour une requête simple
-- Mauvaise pratique
SELECT * FROM large_table WHERE id = 1;
-- Bonne pratique
CREATE INDEX idx_id ON large_table(id);
Piege 2 : Utiliser SELECT * sans raison
-- Mauvaise pratique
SELECT * FROM employees WHERE department = 'Sales';
-- Bonne pratique
SELECT employee_name, salary FROM employees WHERE department = 'Sales';
Piege 3 : Ne pas utiliser les jointures efficaces
-- Mauvaise pratique
SELECT * FROM a JOIN b ON a.id = b.a_id;
-- Bonne pratique
CREATE INDEX idx_a_id ON a(id);
CREATE INDEX idx_b_a_id ON b(a_id);
Piege 4 : Ne pas utiliser les sous-requêtes efficaces
-- Mauvaise pratique
SELECT * FROM employees WHERE id IN (SELECT employee_id FROM projects WHERE project_name = 'Project X');
-- Bonne pratique
SELECT e.* FROM employees e JOIN projects p ON e.id = p.employee_id WHERE p.project_name = 'Project X';
Piege 5 : Ne pas utiliser les triggers efficaces
-- Mauvaise pratique
CREATE TRIGGER update_customer_age BEFORE UPDATE ON customers FOR EACH ROW SET age = EXTRACT(YEAR FROM AGE(current_date, birthdate));
-- Bonne pratique
CREATE TRIGGER update_customer_age BEFORE INSERT OR UPDATE ON customers FOR EACH ROW BEGIN
IF NEW.birthdate IS NOT NULL THEN
SET NEW.age = EXTRACT(YEAR FROM AGE(CURRENT_DATE, NEW.birthdate));
END IF;
END;
Complexité algorithmique
Q: Quelle est la complexité de SELECT DISTINCT ? La complexité de SELECT DISTINCT est O(n log n) en moyenne, où n est le nombre de lignes dans le résultat. Cela se produit parce que les résultats doivent être triés pour éviter les doublons.
Q: Quelle est la complexité de GROUP BY ? La complexité de GROUP BY dépend des opérations d'agrégation utilisées et de la taille du groupe. En général, elle est O(n log n) en moyenne, où n est le nombre total de lignes à regrouper.
Q: Quelle est la complexité de JOIN ? La complexité de JOIN dépend du type de jointure (INNER, LEFT, RIGHT, FULL). Elle est généralement O(m * n), où m et n sont les tailles des deux tables à joindre. Cependant, avec un index approprié sur les colonnes utilisées dans la condition de jointure, cette complexité peut être améliorée.
Q: Quelle est la complexité de CREATE INDEX ? La complexité de CREATE INDEX dépend du nombre de lignes et de l'efficacité de l'algorithme d'indexation utilisé. Elle est généralement O(n log n), où n est le nombre de lignes à indexer.
Q: Quelle est la complexité de DELETE ? La complexité de DELETE dépend du nombre de lignes à supprimer et des index existants sur les tables concernées. Elle est généralement O(n log n), où n est le nombre de lignes à supprimer.
Concepts avancés à connaître
Q: Qu'est-ce qu'une base de données distribuée ? Une base de données distribuée est une collection de bases de données réparties sur plusieurs machines pour offrir des performances et des capacités de stockage accrues, ainsi que la tolérance aux pannes.
Q: Qu'est-ce qu'un OLAP (Online Analytical Processing) ? OLAP est un type de traitement des données qui permet de réaliser des analyses rapides et des modèles complexes sur les données en temps réel. Il utilise souvent des cubes multidimensionnels pour stocker et analyser les données.
Q: Qu'est-ce qu'une base de données NoSQL ? Une base de données NoSQL est une alternative aux bases relationnelles traditionnelles, conçue pour gérer des volumes massifs de données non structurées ou semi-structurées. Elles sont souvent orientées vers la performance et l'éscalabilité.
Q: Qu'est-ce qu'une partitioning ? La partitioning est une technique consistant à diviser une grande table en plusieurs petites tables logiques appelées partitions. Cela permet d'améliorer les performances des requêtes, de simplifier le gestion et l'administration des données, et de faciliter la réplication et la sauvegarde.
Q: Qu'est-ce qu'une base de données élastique ? Une base de données élastique est une technologie qui permet d'échapper les limites du stockage et des performances fixes. Elle peut auto-ajuster sa capacité en fonction du trafic et des besoins de l'application, sans nécessiter de redimensionnement manuel.
Conseils pratiques
Conseil 1 : Optimiser vos requêtes Utilisez les outils d'analyse de performance pour identifier les requêtes inefficaces et optimisez-les en utilisant des indices, des jointures efficaces et des sous-requêtes appropriées.
Conseil 2 : Gérer votre cache judicieusement Le cache peut améliorer les performances, mais il doit être utilisé avec prudence pour éviter la mauvaise utilisation de la mémoire et les problèmes de cohérence des données.
Conseil 3 : Utiliser des transactions efficaces Les transactions sont essentielles pour assurer l'intégrité des données, mais elles doivent être utilisées judicieusement pour éviter les performances lentes. Assurez-vous d'utiliser des transactions minimales et de les committez rapidement après leur utilisation.
Conseil 4 : Planifier votre maintenance La maintenance régulière est essentielle pour la performance et la stabilité de votre base de données. Assurez-vous de planifier des tâches telles que l'optimisation des index, la vidange des journaux de transaction et la compression des données.
Conseil 5 : Restez à jour avec les dernières technologies La technologie évolue rapidement dans le domaine des bases de données. Restez à jour avec les dernières tendances et techniques pour rester compétent dans votre domaine.