Top 10 des questions d'entretien SQL sur la science des données avancées auxquelles vous devez savoir répondre

Republié par Platon

Suiveurs: 0

Top 10 des questions d'entretien SQL avancées en science des données auxquelles vous devez savoir répondre
Image par auteur

SQL (Structured Query Language) est un langage de programmation standard utilisé pour gérer et manipuler des bases de données. C'est une compétence essentielle pour tout professionnel des données, car elle leur permet de récupérer et d'analyser efficacement les données stockées dans une base de données. Par conséquent, SQL est un sujet courant dans les entretiens techniques pour les postes qui impliquent de travailler avec des données, comme les analystes de données, les ingénieurs de données et les administrateurs de bases de données.

Requête SQL pour trouver le nième salaire/paiement le plus élevé ou le 3e salaire/paiement le plus élevé

Pour trouver le nième salaire le plus élevé, vous pouvez utiliser une sous-requête avec la fonction DENSE_RANK() pour calculer le rang dense de chaque salaire, puis filtrer les résultats pour n'inclure que la ligne dont le classement est égal à n.

SELECT * FROM ( SELECT name, salary, DENSE_RANK() OVER ( ORDER BY salary DESC ) as salary_rank FROM employees ) subquery WHERE salary_rank = n;

Vous pouvez également utiliser les clauses LIMIT et OFFSET pour trouver le nième salaire le plus élevé, comme suit :

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET (n - 1);

Par exemple, pour trouver le 3ème salaire le plus élevé, vous utiliseriez la requête suivante :

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

Comment optimiser les requêtes SQL pour les performances ?

Il existe plusieurs façons d'optimiser les requêtes SQL pour de meilleures performances, y compris

Indexage

La création d'un index sur une colonne ou un groupe de colonnes peut considérablement améliorer la vitesse des requêtes qui filtrent sur ces colonnes.

Partitionnement

Le partitionnement d'une grande table en parties plus petites peut améliorer les performances des requêtes qui n'ont besoin d'accéder qu'à un sous-ensemble de données.

Normalisation

La normalisation consiste à organiser les données dans une base de données afin que chaque élément de données soit stocké à un seul endroit, réduisant la redondance et perfectionnant l'intégrité des données.

Utilisation de types de données appropriés

L'utilisation du type de données correct pour chaque colonne peut améliorer les performances des requêtes qui filtrent ou trient sur ces colonnes.

Utilisation des types JOIN appropriés

L'utilisation du type JOIN correct (par exemple, INNER JOIN, OUTER JOIN, CROSS JOIN) peut améliorer les performances des requêtes qui joignent plusieurs tables.

Utilisation de fonctions d'agrégation appropriées

L'utilisation de fonctions d'agrégation appropriées (par exemple, SUM, AVG, MIN, MAX) peut améliorer les performances des requêtes qui effectuent des calculs sur de grands ensembles de données. Certaines fonctions d'agrégation, similaires à COUNT, sont plus efficaces que d'autres, il est donc important de choisir la fonction applicable à votre requête.

Comment utilisez-vous les fonctions LAG et LEAD en SQL ? Pouvez-vous donner un exemple de leur utilisation ?

Les fonctions LAG() et LEAD() sont des fonctions de fenêtre dans SQL qui vous permettent de comparer les valeurs d'une ligne avec les valeurs d'une ligne précédente ou suivante, respectivement. Ils sont utiles pour calculer les totaux cumulés ou pour comparer les valeurs d'un tableau avec les valeurs d'une ligne précédente ou suivante.

La Fonction LAG() prend deux arguments : la colonne à retourner et le nombre de lignes à retourner. Par example

SELECT name, salary, LAG(salary, 1) OVER ( ORDER BY salary DESC ) as prev_salary FROM employees;

La Fonction LEAD() fonctionne de la même manière, mais va vers l'avant plutôt que vers l'arrière. Par example

SELECT name, salary, LEAD(salary, 1) OVER ( ORDER BY salary DESC ) as next_salary FROM employees

Expliquer le concept ETL et ELT en SQL

ETL (Extract, Transform, Load) est un processus utilisé en SQL pour extraire des données d'une ou plusieurs sources, transformer les données dans un format adapté à l'analyse ou à d'autres utilisations, puis charger les données dans un système cible, tel qu'un système de données. entrepôt ou lac de données.

ELT (Extract, Load, Transform) est similaire à ETL, mais la phase de transformation est effectuée après le chargement des données dans le système cible, plutôt qu'avant. Cela permet au système cible d'effectuer les transformations, ce qui peut être plus efficace et évolutif que d'effectuer les transformations dans un outil ETL. L'ELT est souvent utilisé dans les infrastructures de données modernes, qui utilisent de puissants moteurs de traitement de données (tels qu'Apache Spark ou Apache Flink) pour effectuer la phase de transformation.

Pouvez-vous expliquer la différence entre les clauses WHERE et HAVING en SQL

Les clauses WHERE et HAVING sont toutes deux utilisées pour filtrer les lignes d'une instruction SELECT. La principale différence entre les deux est que la clause WHERE est utilisée pour filtrer les lignes avant l'opération group by, tandis que la clause HAVING est utilisée pour filtrer les lignes après l'opération group by.

SELECT department, SUM(salary) FROM employees GROUP BY department HAVING SUM(salary) > 100000;

Dans cet exemple, la clause HAVING est utilisée pour filtrer tous les départements où la somme des salaires des employés de ce département est inférieure à 100000 XNUMX. Cette opération est effectuée après l'opération group by, de sorte qu'elle n'affecte que les lignes qui représentent chaque département.

SELECT * FROM employees WHERE salary > 50000;

Dans cet exemple, la clause WHERE est utilisée pour filtrer tous les employés dont le salaire est inférieur à 50000 XNUMX. Cette opération est effectuée avant toute opération de regroupement, de sorte qu'elle affecte toutes les lignes de la table des employés.

Expliquer la différence entre les opérations TRUNCATE, DROP et DELETE dans SQL

TRONQUER

L'opération TRUNCATE supprime toutes les lignes d'une table, mais elle n'affecte pas la structure de la table. Il est plus rapide que DELETE, car il ne génère aucun journal d'annulation ou de rétablissement et ne déclenche aucun déclencheur de suppression.

Voici un exemple d'utilisation de l'instruction TRUNCATE

TRUNCATE TABLE employees;

Cette instruction supprime toutes les lignes de la table des employés, mais la structure de la table, y compris les noms de colonnes et les types de données, reste inchangée.

GOUTTE

L'opération DROP supprime une table de la base de données et supprime toutes les données de la table. Il supprime également tous les index, déclencheurs et contraintes associés à la table.

Voici un exemple d'utilisation de l'instruction DROP

DROP TABLE employees;

Cette instruction supprime la table des employés de la base de données et toutes les données de la table sont définitivement supprimées. La structure du tableau est également supprimée.

EFFACER

L'opération DELETE supprime une ou plusieurs lignes d'une table. Il vous permet de spécifier une clause WHERE pour sélectionner les lignes à supprimer. Il génère également des journaux d'annulation et de rétablissement et déclenche des déclencheurs d'annulation.

Voici un exemple d'utilisation de l'instruction DELETE

DELETE FROM employees WHERE salary & lt;
50000;

Cette instruction supprime toutes les lignes de la table des employés dont le salaire est inférieur à 50000. La structure de la table reste inchangée et les lignes supprimées peuvent être récupérées à l'aide des journaux d'annulation.

Quelle est la jointure ou la sous-requête la plus efficace ?

Il est généralement plus efficace d'utiliser un JOIN plutôt qu'une sous-requête lors de la combinaison de données provenant de plusieurs tables. En effet, un JOIN permet à la base de données d'exécuter la requête plus efficacement en utilisant des index sur les tables jointes.

Par exemple, considérons les deux requêtes suivantes qui renvoient les mêmes résultats :

SELECT * FROM orders o WHERE o.customer_id IN ( SELECT customer_id FROM customers WHERE country = 'US' );

SELECT * FROM orders o WHERE o.customer_id IN ( SELECT customer_id FROM customers WHERE country = 'US' );

La première requête utilise un JOIN pour combiner les tables des commandes et des clients, puis filtre les résultats à l'aide d'une clause WHERE. La deuxième requête utilise une sous-requête pour sélectionner les ID client pertinents dans la table des clients, puis utilise l'opérateur IN pour filtrer la table des commandes en fonction de ces ID.

Comment utilisez-vous les fonctions de fenêtre en SQL ?

En SQL, une fonction de fenêtre est une fonction qui opère sur un ensemble de lignes, ou une "fenêtre", définie par une spécification de fenêtre. Les fonctions de fenêtre sont utilisées pour effectuer des calculs sur plusieurs lignes et peuvent être utilisées dans les instructions SELECT, UPDATE et DELETE, ainsi que dans les clauses WHERE et HAVING d'une instruction SELECT.

Voici un exemple d'utilisation d'une fonction de fenêtre dans une instruction SELECT :

SELECT name, salary, AVG(salary) OVER (PARTITION BY department_id) as avg_salary_by_department FROM employees

Cette instruction renvoie un ensemble de résultats avec trois colonnes : nom, salaire et avg_salary_by_department. La colonne avg_salary_by_department est calculée à l'aide de la fonction de fenêtre AVG, qui calcule le salaire moyen pour chaque département. La clause PARTITION BY spécifie que la fenêtre est partitionnée par department_id, ce qui signifie que le salaire moyen est calculé séparément pour chaque département.

Expliquer la normalisation

La normalisation est le processus d'organisation d'une base de données de manière à réduire la redondance et la dépendance. Il s'agit d'une approche systématique de décomposition des tables pour éliminer la redondance des données et améliorer l'intégrité des données. Il existe plusieurs formes normales qui peuvent être utilisées pour normaliser une base de données. Les formes normales les plus courantes sont :

Première forme normale (1NF)

Chaque cellule du tableau contient une seule valeur et non une liste de valeurs
Chaque colonne du tableau a un nom unique
Le tableau ne contient aucun groupe de colonnes répétitif

Deuxième forme normale (2NF)

Il est en première forme normale
Il n'a pas de dépendances partielles (c'est-à-dire qu'un attribut non premier dépend d'une partie d'une clé primaire composite)

Troisième forme normale (3NF)

Il est en deuxième forme normale
Il n'a pas de dépendances transitives (c'est-à-dire qu'un attribut non premier dépend d'un autre attribut non premier)

Forme normale de Boyce-Codd (BCNF)

Il est en troisième forme normale
Chaque déterminant (un attribut qui détermine la valeur d'un autre attribut) est une clé candidate (une colonne ou un ensemble de colonnes qui peut être utilisé comme clé primaire)

Expliquer le verrouillage exclusif et le verrouillage de mise à jour dans SQL

Un verrou exclusif est un verrou qui empêche d'autres transactions de lire ou d'écrire dans les lignes verrouillées. Ce type de verrou est généralement utilisé lorsqu'une transaction doit modifier les données d'une table et qu'elle veut s'assurer qu'aucune autre transaction ne peut accéder à la table en même temps.

Un verrou de mise à jour est un verrou qui permet à d'autres transactions de lire les lignes verrouillées, mais il les empêche de mettre à jour ou d'écrire dans les lignes verrouillées. Ce type de verrou est généralement utilisé lorsqu'une transaction doit lire les données d'une table, mais elle veut s'assurer que les données ne sont pas modifiées par d'autres transactions jusqu'à ce que la transaction en cours soit terminée.

Sonia Jamil est actuellement employé en tant qu'analyste de base de données dans l'une des plus grandes sociétés de télécommunications du Pakistan. En plus de son emploi à temps plein, elle travaille également en tant qu'indépendante. Son expérience comprend une expertise dans l'administration de bases de données et une expérience avec les environnements SQL Server sur site et dans le cloud. Elle maîtrise les dernières technologies SQL Server et a un fort intérêt pour la gestion et l'analyse des données.