Top 10 Advanced Data Science SQL-interviewvragen die u moet weten te beantwoorden

Heruitgegeven door Plato

volgers: 0

Top 10 Advanced Data Science SQL-interviewvragen die u moet weten te beantwoorden
Afbeelding door auteur

SQL (Structured Query Language) is een standaard programmeertaal die wordt gebruikt voor het beheren en manipuleren van databases. Het is een essentiële vaardigheid voor elke dataprofessional, omdat het hen in staat stelt om gegevens die in een database zijn opgeslagen effectief op te halen en te analyseren. Als gevolg hiervan is SQL een veel voorkomend onderwerp in technische sollicitatiegesprekken voor functies waarbij met gegevens wordt gewerkt, vergelijkbaar met gegevensanalisten, gegevensingenieurs en databasebeheerders.

SQL-query om het n-de hoogste salaris/betaling of het op twee na hoogste salaris/betaling te vinden

Om het n-de hoogste salaris te vinden, kunt u een subquery met de functie DENSE_RANK() gebruiken om de dichte rangorde van elk salaris te berekenen en vervolgens de resultaten filteren om alleen de rij op te nemen met de rangorde gelijk aan n.

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

U kunt ook de clausules LIMIT en OFFSET gebruiken om het n-de hoogste salaris te vinden, als volgt:

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

Om bijvoorbeeld het op twee na hoogste salaris te vinden, gebruikt u de volgende zoekopdracht:

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

Hoe optimaliseert u SQL-query's voor prestaties?

Er zijn verschillende manieren om SQL-query's te optimaliseren voor betere prestaties, waaronder

Indexeren

Het maken van een index op een kolom of groep kolommen kan de snelheid van query's die op die kolommen filteren aanzienlijk verbeteren.

Partitioning

Het opdelen van een grote tabel in kleinere stukken kan de prestaties verbeteren van query's die alleen toegang nodig hebben tot een subset van de gegevens.

Normalisatie

Normalisatie omvat het organiseren van de gegevens in een database, zodat elk stuk gegevens op slechts één plek wordt opgeslagen, waardoor redundantie wordt verminderd en de integriteit van de gegevens wordt geperfectioneerd.

Gebruik van geschikte gegevenstypen

Het gebruik van het juiste gegevenstype voor elke kolom kan de prestaties verbeteren van query's die op die kolommen filteren of sorteren.

Gebruik van geschikte JOIN-typen

Het gebruik van het juiste JOIN-type (bijv. INNER JOIN, OUTER JOIN, CROSS JOIN) kan de prestaties verbeteren van query's die meerdere tabellen samenvoegen.

Gebruik van geschikte aggregatiefuncties

Het gebruik van de juiste aggregatiefuncties (bijv. SUM, AVG, MIN, MAX) kan de prestaties verbeteren van query's die berekeningen uitvoeren op grote gegevenssets. Sommige statistische functies, vergelijkbaar met COUNT, zijn effectiever dan andere, dus het is belangrijk om de toepasselijke functie voor uw zoekopdracht te kiezen.

Hoe gebruik je de LAG- en LEAD-functies in SQL? Kunt u een voorbeeld geven van het gebruik ervan?

De functies LAG() en LEAD() zijn vensterfuncties in SQL waarmee u waarden in een rij kunt vergelijken met waarden in respectievelijk een voorgaande of volgende rij. Ze zijn handig voor het berekenen van lopende totalen of voor het vergelijken van waarden in een tabel met waarden in een vorige of volgende rij.

De LAG()-functie heeft twee argumenten: de kolom die moet worden geretourneerd en het aantal rijen dat moet worden teruggestuurd. Bijvoorbeeld

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

De LEAD() functie werkt op een vergelijkbare manier, maar gaat eerder vooruit dan achteruit. Bijvoorbeeld

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

Leg het ETL- en ELT-concept in SQL uit

ETL (Extract, Transform, Load) is een proces dat in SQL wordt gebruikt om gegevens uit een of meer bronnen te extraheren, de gegevens om te zetten in een formaat dat geschikt is voor analyse of ander gebruik, en de gegevens vervolgens in een doelsysteem te laden, zoals een magazijn of datameer.

ELT (Extract, Load, Transform) is vergelijkbaar met ETL, maar de Transform-fase wordt uitgevoerd nadat de gegevens in het doelsysteem zijn geladen, in plaats van ervoor. Hierdoor kan het doelsysteem de transformaties uitvoeren, wat efficiënter en schaalbaarder kan zijn dan het uitvoeren van de transformaties in een ETL-tool. ELT wordt vaak gebruikt in moderne data-infrastructuren, die krachtige dataverwerkingsengines (zoals Apache Spark of Apache Flink) gebruiken om de Transform-fase uit te voeren.

Kun je het verschil uitleggen tussen de WHERE- en HAVING-clausules in SQL

De WHERE- en HAVING-clausules worden beide gebruikt om rijen uit een SELECT-instructie te filteren. Het belangrijkste verschil tussen de twee is dat de WHERE-component wordt gebruikt om rijen vóór de groep te filteren op bewerking, terwijl de HAVING-component wordt gebruikt om rijen na de groep te filteren op bewerking.

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

In dit voorbeeld wordt de HAVING-component gebruikt om alle afdelingen uit te filteren waarvan de som van de salarissen voor werknemers in die afdeling kleiner is dan 100000. Dit gebeurt achter de groep op bewerking, dus het heeft alleen invloed op de rijen die elke afdeling vertegenwoordigen.

SELECT * FROM employees WHERE salary > 50000;

In dit voorbeeld wordt de WHERE-clausule gebruikt om alle werknemers met een salaris van minder dan 50000 eruit te filteren. Dit wordt gedaan vóór elke groep per bewerking, dus het is van invloed op alle rijen in de werknemerstabel.

Leg het verschil uit tussen TRUNCATE-, DROP- en DELETE-bewerkingen in SQL

AFKNOPEN

De TRUNCATE-bewerking verwijdert alle rijen uit een tabel, maar heeft geen invloed op de structuur van de tabel. Het is sneller dan DELETE, omdat het geen logboeken voor ongedaan maken of opnieuw uitvoeren genereert en geen verwijderingstriggers activeert.

Hier is een voorbeeld van het gebruik van de TRUNCATE-instructie

TRUNCATE TABLE employees;

Deze instructie verwijdert alle rijen uit de werknemerstabel, maar de tabelstructuur, inclusief kolomnamen en gegevenstypen, blijft ongewijzigd.

DROP

De DROP-bewerking verwijdert een tabel uit de database en verwijdert alle gegevens in de tabel. Het verwijdert ook alle indexen, triggers en beperkingen die aan de tabel zijn gekoppeld.

Hier is een voorbeeld van het gebruik van de DROP-instructie

DROP TABLE employees;

Deze instructie verwijdert de werknemerstabel uit de database en alle gegevens in de tabel worden definitief verwijderd. De tabelstructuur wordt ook verwijderd.

VERWIJDEREN

De DELETE-bewerking verwijdert een of meer rijen uit een tabel. Hiermee kunt u een WHERE-clausule specificeren om de rijen te selecteren die u wilt verwijderen. Het genereert ook logboeken voor ongedaan maken en opnieuw uitvoeren en activeert annuleringstriggers.

Hier is een voorbeeld van het gebruik van de DELETE-instructie

DELETE FROM employees WHERE salary & lt;
50000;

Deze verklaring verwijdert alle rijen uit de werknemerstabel waar het salaris lager is dan 50000. De tabelstructuur blijft ongewijzigd en de verwijderde rijen kunnen worden hersteld met behulp van de ongedaan gemaakte logboeken.

Wat is efficiënter join of subquery?

Het is over het algemeen efficiënter om een JOIN te gebruiken in plaats van een subquery bij het combineren van gegevens uit meerdere tabellen. Dit komt omdat een JOIN de database in staat stelt de query efficiënter uit te voeren door indexen op de samengevoegde tabellen te gebruiken.

Kijk bijvoorbeeld eens naar de volgende twee query's die dezelfde resultaten opleveren:

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' );

De eerste query gebruikt een JOIN om de tabellen met bestellingen en klanten te combineren en filtert vervolgens de resultaten met behulp van een WHERE-component. De tweede query gebruikt een subquery om de relevante klant-ID's uit de klantentabel te selecteren en gebruikt vervolgens de IN-operator om de ordertabel te filteren op basis van die ID's.

Hoe gebruik je vensterfuncties in SQL?

In SQL is een vensterfunctie een functie die werkt op een reeks rijen of een "venster", gedefinieerd door een vensterspecificatie. Vensterfuncties worden gebruikt om berekeningen over rijen uit te voeren en ze kunnen worden gebruikt in SELECT-, UPDATE- en DELETE-instructies, evenals in de WHERE- en HAVING-clausules van een SELECT-instructie.

Hier is een voorbeeld van het gebruik van een vensterfunctie in een SELECT-instructie:

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

Deze instructie retourneert een resultatenset met drie kolommen: naam, salaris en gem_salaris_per_afdeling. De kolom avg_salary_by_department wordt berekend met behulp van de AVG-vensterfunctie, die het gemiddelde salaris voor elke afdeling berekent. De PARTITION BY-clausule specificeert dat het venster wordt gepartitioneerd door department_id, wat betekent dat het gemiddelde salaris voor elke afdeling afzonderlijk wordt berekend.

Leg normalisatie uit

Normalisatie is het proces van het organiseren van een database op een manier die redundantie en afhankelijkheid vermindert. Het is een systematische benadering voor het ontleden van tabellen om gegevensredundantie te elimineren en de gegevensintegriteit te verbeteren. Er zijn verschillende normaalvormen die kunnen worden gebruikt om een database te normaliseren. De meest voorkomende normaalvormen zijn:

Eerste normale vorm (1NF)

Elke cel in de tabel bevat een enkele waarde en geen lijst met waarden
Elke kolom in de tabel heeft een unieke naam
De tabel bevat geen herhalende groepen kolommen

Tweede normale vorm (2NF)

Het is in de eerste normale vorm
Het heeft geen gedeeltelijke afhankelijkheden (dat wil zeggen, een niet-primair kenmerk is afhankelijk van een deel van een samengestelde primaire sleutel)

Derde normale vorm (3NF)

Het is in de tweede normaalvorm
Het heeft geen transitieve afhankelijkheden (dat wil zeggen, een niet-primair kenmerk is afhankelijk van een ander niet-primair kenmerk)

Boyce-Codd normale vorm (BCNF)

Het is in de derde normaalvorm
Elke determinant (een attribuut dat de waarde van een ander attribuut bepaalt) is een kandidaatsleutel (een kolom of reeks kolommen die als primaire sleutel kan worden gebruikt)

Exclusieve vergrendeling en updatevergrendeling in SQL uitleggen

Een exclusief slot is een slot dat voorkomt dat andere transacties lezen of schrijven naar de vergrendelde rijen. Dit type vergrendeling wordt meestal gebruikt wanneer een transactie de gegevens in een tabel moet wijzigen en ervoor wil zorgen dat er geen andere transacties tegelijkertijd toegang hebben tot de tabel.

Een updatevergrendeling is een vergrendeling waarmee andere transacties de vergrendelde rijen kunnen lezen, maar die verhindert dat ze de vergrendelde rijen bijwerken of ernaar schrijven. Dit type vergrendeling wordt meestal gebruikt wanneer een transactie de gegevens in een tabel moet lezen, maar ervoor wil zorgen dat de gegevens niet worden gewijzigd door andere transacties totdat de huidige transactie is voltooid.

Sonia Jamil is momenteel werkzaam als database-analist bij een van de grootste telecommunicatiebedrijven van Pakistan. Naast haar fulltime baan werkt ze ook als freelancer. Haar achtergrond omvat expertise in databasebeheer en ervaring met zowel on-premises als cloudgebaseerde SQL Server-omgevingen. Ze is bedreven in de nieuwste SQL Server-technologieën en heeft een sterke interesse in datamanagement en data-analyse.