SQL AVANCE Flashcards
Quelle est la différence entre INNER JOIN et LEFT JOIN ?
Un INNER JOIN renvoie uniquement les enregistrements qui ont des correspondances dans les deux tables. Un LEFT JOIN renvoie tous les enregistrements de la table de gauche (celle du FROM) et les enregistrements correspondants de la table de droite (après le JOIN). Si aucune correspondance n’est trouvée, les colonnes de la table de droite contiendront NULL.
Expliquez la différence entre WHERE et HAVING.
WHERE filtre les lignes avant tout regroupement (GROUP BY), tandis que HAVING filtre les groupes après le regroupement. HAVING nécessite une fonction d’agrégation et doit être utilisé avec GROUP BY, alors que WHERE filtre des enregistrements individuels.
Qu’est-ce que DISTINCT et dans quel cas l’utiliser ?
DISTINCT élimine les doublons dans les résultats d’une requête. On l’utilise quand on souhaite obtenir des valeurs uniques d’une colonne ou d’une combinaison de colonnes, par exemple pour connaître la liste des villes distinctes où habitent des clients.
Comment utiliseriez-vous LIKE pour rechercher un motif dans une chaîne ?
LIKE permet de rechercher un motif avec des caractères joker: % remplace zéro ou plusieurs caractères, _ remplace exactement un caractère. Exemple: WHERE firstname LIKE ‘A%’ trouve tous les prénoms commençant par A.
Quelles sont les principales fonctions d’agrégation en SQL ?
Les principales fonctions d’agrégation sont COUNT() (compte le nombre de lignes), SUM() (calcule la somme), AVG() (calcule la moyenne), MIN() (trouve la valeur minimale) et MAX() (trouve la valeur maximale).
Expliquez l’utilité de GROUP BY en SQL.
GROUP BY permet de regrouper les lignes qui ont les mêmes valeurs dans une ou plusieurs colonnes spécifiées. Il est généralement utilisé avec des fonctions d’agrégation pour effectuer des calculs sur chaque groupe, comme calculer la somme des ventes par région.
Comment peut-on combiner les résultats de plusieurs requêtes SELECT ?
On utilise UNION pour combiner les résultats de plusieurs requêtes SELECT. UNION élimine les doublons, tandis que UNION ALL conserve tous les enregistrements, y compris les doublons. Les requêtes doivent avoir le même nombre de colonnes et des types de données compatibles.
Comment sécuriseriez-vous vos requêtes SQL contre les injections SQL dans une application web ?
Pour sécuriser contre les injections SQL, j’utiliserais des requêtes préparées avec des paramètres liés plutôt que de construire des requêtes par concaténation. J’utiliserais également des ORM ou des bibliothèques qui échappent automatiquement les entrées utilisateur, et je validerais toutes les entrées côté serveur.
Dans quel cas utiliseriez-vous une jointure dans une application web e-commerce ?
Dans une application e-commerce, j’utiliserais des jointures pour relier les tables de produits, de catégories, de commandes et de clients. Par exemple, pour afficher le détail d’une commande avec les informations client et les produits achetés: SELECT o.id, c.name, p.title FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id.
Comment optimiseriez-vous une requête SQL qui devient lente dans votre application ?
Pour optimiser une requête lente, je vérifierais d’abord son plan d’exécution pour identifier les goulots d’étranglement. Je créerais des index sur les colonnes fréquemment utilisées dans les clauses WHERE, JOIN et ORDER BY. Je limiterais le nombre de jointures et éviterais d’utiliser SELECT * en sélectionnant uniquement les colonnes nécessaires. Je considérerais également la réécriture de requêtes complexes ou l’utilisation de vues materialisées.
Comment géreriez-vous les transactions dans une application web qui manipule des données sensibles ?
Pour les données sensibles, j’utiliserais des transactions pour garantir l’intégrité des données. Chaque opération qui modifie plusieurs tables serait encapsulée dans une transaction avec BEGIN TRANSACTION, COMMIT et ROLLBACK en cas d’erreur. J’implémenterais également des mécanismes de journalisation pour tracer toutes les modifications de données sensibles.
Dans le cadre d’une application web, comment implémenteriez-vous une fonctionnalité de recherche avec SQL ?
Pour une fonctionnalité de recherche, j’utiliserais LIKE avec des jokers (%) pour les recherches simples, ou des opérateurs de recherche plein texte comme MATCH AGAINST pour les recherches plus complexes. J’ajouterais des index de recherche plein texte sur les colonnes concernées. Pour les grandes bases de données, j’envisagerais une solution de recherche dédiée comme Elasticsearch en complément de SQL.
Comment utiliseriez-vous les fonctions de date dans une application de gestion d’événements ?
Dans une application de gestion d’événements, j’utiliserais NOW() pour horodater les créations et modifications, DATEDIFF() pour calculer la durée jusqu’à un événement, DATE_ADD() pour définir des rappels, et des fonctions comme YEAR(), MONTH(), DAY() pour regrouper les événements par période dans des rapports statistiques.
Expliquez comment vous utiliseriez les requêtes préparées dans une application PHP/MySQL.
Dans une application PHP/MySQL, j’utiliserais PDO ou MySQLi avec des requêtes préparées. Exemple avec PDO :
php
Copier
$stmt = $pdo->prepare(“SELECT * FROM users WHERE email = :email AND status = :status”);
$stmt->execute([‘email’ => $userEmail, ‘status’ => ‘active’]);
$user = $stmt->fetch();
Cela évite les injections SQL et améliore les performances pour les requêtes exécutées plusieurs fois.
Dans le contexte d’une application web DWWM, comment géreriez-vous les droits d’accès à la base de données ?
Je créerais différents utilisateurs de base de données avec des privilèges spécifiques selon le principe du moindre privilège. Pour l’application web, j’utiliserais un utilisateur avec uniquement les droits nécessaires (SELECT, INSERT, UPDATE, DELETE sur des tables spécifiques). Pour l’administration, un utilisateur séparé avec plus de privilèges. Je ne stockerais jamais les identifiants en clair dans le code source mais dans des fichiers de configuration sécurisés ou des variables d’environnement.
