18th Apr 2024 21 Leseminuten 15 knifflige SQL-Interview-Fragen für erfahrene Benutzer Tihomir Babic SQL Jobinterview Fragen SQL für Fortgeschrittene Inhaltsverzeichnis Interviewvorbereitung als erfahrener SQL-Anwender Frage 1: Auswahl von Freelancern und deren Aufgabeninformationen Frage 2: Was sind OUTER JOINs und wann verwendet man sie? Frage 3: Auswählen von Freelancer- und Aufgabeninformationen, Teil 2 Frage 4: Auswählen von Freelancer-Infos für Projekte, die im Jahr 2024 fällig werden Frage 5: Alle Mitarbeiter und ihre Vorgesetzten anzeigen Frage 6: Therapeuten und ihre erste und zweite Sprache anzeigen Frage 7: Zeigen Sie die Anzahl der Freiberufler mit zugewiesenen Aufgaben an Frage 8: Zeigen Sie die Anzahl der Aufgaben nach Aufgabentyp und Untertyp Frage 9: Zeigen Sie die Anzahl der aktiven Aufgaben nach Aufgabentyp und Untertyp Frage 10: Was stimmt mit dieser Abfrage nicht? Frage 11: Zeige alle Freiberufler und die Anzahl ihrer Aufgaben Frage 12: Zeigen Sie die Anzahl der erledigten Aufgaben nach Erledigungsdatum Frage 13: Mitarbeiter mit ihren Abteilungen und Gehältern anzeigen Frage 14: Was ist der Unterschied zwischen UNION und UNION ALL? Frage 15: Ausgewählte Bücher mit ihrem Autor und Untertitel anzeigen Weitere Interview-Ressourcen für erfahrene SQL-Benutzer SQL-Interviewfragen für erfahrene Benutzer enthalten in der Regel einige Trick(y)-Fragen. Die Interviewer nutzen sie, um Ihren Grips zu testen, der in der Regel das Ergebnis umfangreicher Erfahrung und eines hohen Niveaus an SQL-Kenntnissen ist. In diesem Artikel zeige ich Ihnen 15 knifflige SQL-Interview-Fragen (meist zum Kodieren). Wohlgemerkt, es handelt sich nicht unbedingt um komplexe Fragen. Genau genommen ist das ihr Hauptmerkmal: Sie scheinen sehr einfach und unkompliziert zu sein, aber das ist der Versuch des Interviewers, Sie zu täuschen. Als erfahrener SQL-Anwender sollten Sie jedoch in der Lage sein, die Fallen zu erkennen und sie zu umgehen. Ich zeige Ihnen, wie man das macht, aber ich kann mich nicht an Ihrer Stelle auf das Gespräch vorbereiten. Wie sollten Sie sich also auf ein Vorstellungsgespräch vorbereiten? Interviewvorbereitung als erfahrener SQL-Anwender Solide Grundlagen in SQL-Grundlagen und -Zwischenthemen sind Voraussetzung dafür, dass Sie sich als erfahrener SQL-Anwender betrachten können. Wenn Sie nicht wissen, wo Sie auf dem Spektrum der SQL-Kenntnisse stehen, haben wir etwas Neues für Sie: unser SQL Skills Assessment. Sie können den Test machen und Ihren SQL-Kenntnisstand einschätzen. Diese Funktion ist kostenlos; Sie können alle 30 Tage einen Test machen. Am Ende des Tests erhalten Sie eine Gesamtnote für Ihre SQL-Kenntnisse. Es gibt detaillierte Ergebnisse für sechs Kompetenzbereiche: Grundlegende SQL-Abfragen, SQL-JOINs, Standard-SQL-Funktionen, Grundlegende SQL-Berichte, Fortgeschrittene SQL-Berichte und Komplexe SQL-Berichte. Nach der Bewertung können Sie unseren Fortgeschrittenes SQL Track besuchen, um weiter zu üben. Er besteht aus drei interaktiven Hauptkursen, die Details zu Fensterfunktionen, GROUP BY Erweiterungen und rekursiven Abfragen behandeln. Die Themen sind auf 395 Codieraufgaben verteilt, so dass Sie viel Code schreiben werden - was sich als die effizienteste Art erwiesen hat, SQL zu lernen. Nach dem Kurs werden Sie mit fortgeschrittenen SQL-Themen vertraut sein. Der Lernpfad wird Ihnen Wissen vermitteln, daran besteht kein Zweifel. Arbeitgeber verlassen sich jedoch darauf, dass erfahrene Benutzer SQL bei der Lösung von Problemen im wirklichen Leben einsetzen. Aber im Leben sind die Dinge selten einfach; reale Probleme sind in der Regel keine SQL-Lehrbuchbeispiele, die zum Lernen geeignet sind. Sie sollten also über die Beispiele aus dem Kurs hinausgehen. Sie müssen an Ihrer Flexibilität und Kreativität arbeiten, mögliche Fallstricke im Voraus erkennen und sie in Ihrem SQL-Code vermeiden. Das ist es, was die Interviewer von erfahrenen Anwendern erwarten. Aus diesem Grund reicht es nicht aus, sich mit einfachen SQL-Fragen auf das Vorstellungsgespräch vorzubereiten. Sie sollten sich auch auf die kniffligen Fragen vorbereiten, da die Interviewer diese gerne verwenden, um Sie zu überrumpeln. Im Folgenden werden einige der häufigsten kniffligen SQL-Fragen für erfahrene Benutzer vorgestellt. Frage 1: Auswahl von Freelancern und deren Aufgabeninformationen Schreiben Sie eine Abfrage, die alle Freiberufler zusammen mit ihren Aufgabeninformationen auswählt: Titel der Aufgabe Aufgabentyp und Untertyp Fälligkeitsdatum Schließen Sie Freiberufler ein, denen keine Aufgaben zugewiesen sind. Datensatz: Der Datensatz stammt von einem Unternehmen, das Freiberufler für bestimmte Aufgaben einsetzt. Er besteht aus drei Tabellen. Die erste Tabelle ist freelancer. Das Skript finden Sie hier. idfirst_namelast_name 1BobFranklin 2DionneRavanelli 3MarekLewandowski 4FrancoisCousteau 5EmmaBiesa Die zweite Tabelle ist ein Wörterbuch der verschiedenen Aufgabentypen namens task_category. Hier ist das Skript. idtask_typetask_subtype 1Blog articleSQL 2Blog articlePython 3Blog articleCareer 4Social media postLinkedIn 5Social media postOther social media Die dritte Tabelle zeigt die Details der zugewiesenen Arbeiten, die Freiberufler für unser Unternehmen erledigen. Die Tabelle heißt taskund das Skript finden Sie hier. idtask_category_idtitlefreelancer_iddate_assigneddue_datecompleted_date 12Working With Pandas in Python52023-11-302023-12-152023-12-15 24Promote Fortgeschrittenes SQL Learning Track42023-12-182023-12-202023-12-20 31Working With LEFT JOIN in SQL12023-12-082024-03-01NULL 43What Does a Data Analyst Do?22023-12-202024-02-012024-02-10 54Promote Working With Pandas in Python42024-01-152024-01-182024-01-18 62Python Libraries You Should Know12024-01-152024-02-152024-02-15 71Using COUNT in SQL22024-01-202024-02-152024-02-15 81Filtering Data in SQL52024-02-20NULLNULL Antwort: Diese Frage testet Ihre Fähigkeiten, drei Tabellen zu verbinden und den richtigen Verbindungstyp zu wählen. Hier ist die Lösung: SELECT f.first_name, f.last_name, t.title, t.due_date, tc.task_type, tc.task_subtype FROM freelancer f JOIN task t ON f.id = t.freelancer_id JOIN task_category tc ON t.task_category_id = tc.id; Erläuterung: Um alle erforderlichen Informationen zu erhalten, müssen Sie alle drei Tabellen verknüpfen. Verbinden Sie zunächst die Tabellen freelancer und task über die Freelancer-ID. Um die dritte Tabelle hinzuzufügen, müssen Sie erneut das Schlüsselwort JOIN eingeben. Geben Sie dann in der ON -Klausel an, dass Sie die Tabellen über die Aufgabenkategorie-ID verknüpfen. Der von Ihnen verwendete Verknüpfungstyp muss JOIN sein. Es besteht nämlich die Möglichkeit, dass es Freiberufler gibt, die noch keine Aufgaben haben. Sie brauchen nur die, die eine haben. Ausgabe: Hier ist die Ausgabe der Abfrage: first_namelast_nametitledue_datetask_typetask_subtype EmmaBiesaWorking With Pandas in Python2023-12-15Blog articlePython FrancoisCousteauPromote Fortgeschrittenes SQL Learning Track2023-12-20Social media postLinkedIn BobFranklinWorking With LEFT JOIN in SQL2024-03-01Blog articleSQL DionneRavanelliWhat Does a Data Analyst Do?2024-02-01Blog articleCareer FrancoisCousteauPromote Working With Pandas in Python2024-01-18Social media postLinkedIn BobFranklinPython Libraries You Should Know2024-02-15Blog articlePython DionneRavanelliUsing COUNT in SQL2024-02-15Blog articleSQL EmmaBiesaFiltering Data in SQLNULLBlog articleSQL Frage 2: Was sind OUTER JOINs und wann verwendet man sie? Antwort: Bei dieser Frage geht es darum, ob Sie wirklich verstehen, wie Outer Joins funktionieren und wie sie sich von anderen Joins unterscheiden. OUTER JOINOUTER JOINs sind eine der verschiedenen Join-Kategorien in SQL, zusammen mit INNER JOINs und CROSS JOINs. Die folgenden Joins gehören zur OUTER JOIN Familie: LEFT (OUTER) JOIN RIGHT (OUTER) JOIN FULL (OUTER) JOIN Das Hauptmerkmal aller OUTER JOINs ist, dass sie Tabellen in einer Weise verbinden, bei der eine Tabelle dominant ist, so dass alle ihre Daten angezeigt werden. Die zweite Tabelle ist untergeordnet, so dass die Abfrage nur die übereinstimmenden Zeilen aus dieser Tabelle anzeigt. Wenn es nicht übereinstimmende Zeilen gibt, werden sie als NULL angezeigt. OUTER JOINsollte also verwendet werden, wenn Sie sowohl nicht übereinstimmende als auch übereinstimmende Zeilen innerhalb der Tabellen anzeigen möchten. Jede der oben genannten äußeren Verknüpfungen funktioniert nach diesem Prinzip, aber sie unterscheiden sich in folgenden Punkten: LEFT JOIN werden alle Daten aus der ersten (linken) Tabelle und nur die übereinstimmenden Zeilen aus der zweiten (rechten) Tabelle angezeigt. Wenn es nicht übereinstimmende Zeilen gibt, werden sie als NULL angezeigt. RIGHT JOIN zeigt alle Daten aus der zweiten (rechten) Tabelle und nur die übereinstimmenden Zeilen aus der ersten (linken) Tabelle. Die nicht übereinstimmenden Zeilen werden als NULL angezeigt. FULL JOIN kombiniert LEFT JOIN und RIGHT JOIN. Er zeigt alle Daten aus beiden Tabellen an. Mit anderen Worten, es werden alle Zeilen angezeigt - übereinstimmende und nicht übereinstimmende Zeilen aus der linken Tabelle. Dann werden alle Zeilen aus der rechten Tabelle hinzugefügt, die nicht in der linken Tabelle gefunden werden können. Wo es nicht übereinstimmende Daten gibt, werden Sie NULLsehen. Frage 3: Auswählen von Freelancer- und Aufgabeninformationen, Teil 2 Schreiben Sie eine Abfrage, die Folgendes zurückgibt: Vor- und Nachnamen der Freiberufler. Die Titel der ihnen zugewiesenen Aufgaben. Aufgabentyp und Untertyp. Fälligkeitsdaten der Aufgabe. Schließen Sie alle Freiberufler ein, auch diejenigen, die keine Aufgaben haben. Datensatz: Wie bei Frage 1. Antwort: Eine weitere SQL-Interviewfrage für den erfahrenen Benutzer. Hier müssen Sie zeigen, dass Sie die Beziehungen zwischen den Tabellen verstehen. Sie müssen LEFT JOIN verwenden , um die drei Tabellen zu verbinden. Sie müssen LEFT JOIN als erste Verknüpfung verwenden. Sie müssen sich jedoch darüber im Klaren sein, dass die Beziehung zwischen den Tabellen Sie "zwingt", LEFT JOIN erneut als zweiten Join zu verwenden. SELECT f.first_name, f.last_name, t.title, t.due_date, tc.task_type, tc.task_subtype FROM freelancer f LEFT JOIN task t ON f.id = t.freelancer_id LEFT JOIN task_category tc ON t.task_category_id = tc.id; Erläuterung: Die Abfrage ist der in Frage 1 sehr ähnlich. Ihr erster Join ist also LEFT JOIN, da Sie alle Freiberufler ausgeben müssen, nicht nur die, denen eine Aufgabe zugewiesen wurde. Mit anderen Worten, die Beziehung ist so, dass einer Aufgabe ein Freiberufler zugewiesen sein muss, aber ein Freiberufler muss keine Aufgabe zugewiesen haben. Wenn Sie jedoch die dritte Tabelle verknüpfen, benötigen Sie wiederum LEFT JOIN. Warum ist das so? Es liegt daran, dass eine Aufgabe einen Typ und einen Untertyp haben muss. Gleichzeitig muss nicht jeder verfügbare Aufgabentyp unter den zugewiesenen Aufgaben sein. Wenn Sie hier stattdessen INNER JOIN verwenden würden, würde dies die erste LEFT JOIN "aufheben" und Ihre Ausgabe verzerren. Ausgabe: So sollte Ihre Ausgabe aussehen: first_namelast_nametitledue_datetask_typetask_subtype EmmaBiesaWorking With Pandas in Python2023-12-15Blog articlePython FrancoisCousteauPromote Fortgeschrittenes SQL Learning Track2023-12-20Social media postLinkedIn BobFranklinWorking With LEFT JOIN in SQL2024-03-01Blog articleSQL DionneRavanelliWhat Does a Data Analyst Do?2024-02-01Blog articleCareer FrancoisCousteauPromote Working With Pandas in Python2024-01-18Social media postLinkedIn BobFranklinPython Libraries You Should Know2024-02-15Blog articlePython DionneRavanelliUsing COUNT in SQL2024-02-15Blog articleSQL EmmaBiesaFiltering Data in SQLNULLBlog articleSQL MarekLewandowskiNULLNULLNULLNULL Die Verwendung von INNER JOIN als zweite Verknüpfung würde diese letzte Zeile entfernen, die einen Freiberufler ohne zugewiesene Aufgabe anzeigt. Wenn es keine Aufgabe gibt, gibt es auch keinen Aufgabentyp. Und INNER JOIN zeigt keine nicht übereinstimmenden Zeilen an. Deshalb wird hier LEFT JOIN benötigt. Frage 4: Auswählen von Freelancer-Infos für Projekte, die im Jahr 2024 fällig werden Schreiben Sie eine Abfrage, die auswählt: Alle Freiberufler Ihre Aufgabentitel Fälligkeitsdaten der Aufgaben Schließen Sie nur Projekte mit einem Fälligkeitsdatum im Jahr 2024 ein. Datensatz: Dieselbe wie in der vorherigen Frage. Lösung: Die Frage will Sie dazu verleiten, eine Abfrage zu schreiben, die die WHERE -Klausel zum Filtern der Daten verwendet, wie unten gezeigt: SELECT f.first_name, f.last_name, t.title, t.due_date FROM freelancer f LEFT JOIN task t ON f.id = t.freelancer_id WHERE t.due_date > '2023-12-31'; Aber das ist nicht die richtige Antwort. Um die gewünschte Ausgabe zu erhalten, muss die Filterungsbedingung in WHERE in eine Verknüpfungsbedingung umgewandelt werden, wie hier: SELECT f.first_name, f.last_name, t.title, t.due_date FROM freelancer f LEFT JOIN task t ON f.id = t.freelancer_id AND t.due_date > '2023-12-31'; Erläuterung: In der ersten Abfrage würde die Verwendung von WHERE nur die Daten für Aufgaben mit einem Fälligkeitsdatum im Jahr 2024 zurückgeben. Das würde alle Freiberufler ausschließen, die keine zugewiesene Aufgabe haben, aber auch die Aufgaben, die - aus verschiedenen Gründen - kein Fälligkeitsdatum haben. Stattdessen verschieben wir die Filterbedingung in die ON Klausel. Die erste Bedingung verknüpft die Tabellen über die Freelancer-ID. Die zweite Bedingung wird mit dem Schlüsselwort AND hinzugefügt. Auf diese Weise werden alle Freiberufler einbezogen, aber die Projekte herausgefiltert, die im Jahr 2023 fällig waren. Ausgabe: Hier ist die korrekte Ausgabe: first_namelast_nametitledue_date BobFranklinWorking With LEFT JOIN in SQL2024-03-01 DionneRavanelliWhat Does a Data Analyst Do?2024-02-01 FrancoisCousteauPromote Working With Pandas in Python2024-01-18 BobFranklinPython Libraries You Should Know2024-02-15 DionneRavanelliUsing COUNT in SQL2024-02-15 EmmaBiesaNULLNULL MarekLewandowskiNULLNULL Obwohl Emma Biesa ein Projekt mit dem Titel "Filtern von Daten in SQL" hat, ist ihr Fälligkeitsdatum NULL, also ist der Wert in der Spalte title auch NULL. Mit anderen Worten: Das Projekt von Emma Biesa entspricht nicht der Verknüpfungsbedingung. Für Marek Lewandowski hingegen sieht die Ausgabe genauso aus. Diesmal liegt es daran, dass Marek überhaupt kein Projekt zugewiesen ist. Frage 5: Alle Mitarbeiter und ihre Vorgesetzten anzeigen Datensatz: Die Frage liefert Ihnen die Tabelle employees. Hier ist das Skript. Die Tabelle ist eine Liste von Mitarbeitern. idfirst_namelast_namemanager_id 1JohnBorisov2 2LindaJohnson8 3FrankRanieriNULL 4NinaBowie1 5TamaraFelipeNULL 6SimonFyodorov8 7LanaHopkinsNULL 8TomBonfa1 9MariaFox1 10VictorIvanchich2 Lösung: Da es nur eine Tabelle gibt, müssen Sie zeigen, dass Sie wissen, dass eine Tabelle mit sich selbst verknüpft werden kann. Mit anderen Worten: Lösen Sie die Frage, indem Sie einen Self-Join anwenden. Dies geschieht auf die folgende Weise: SELECT e.first_name AS employee_first_name, e.last_name AS employee_last_name, m.first_name AS manager_first_name, m.last_name AS manager_last_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; Erläuterung: Self-Join ist einfach eine Tabelle, die mit sich selbst verknüpft ist. Indem Sie einer Tabelle verschiedene Aliasnamen geben, lassen Sie SQL glauben, dass Sie zwei verschiedene Tabellen verknüpft haben. Unsere 'erste' Tabelle hat den Alias e. Wir werden sie verwenden, um die Namen der Mitarbeiter anzuzeigen. Der Alias der 'zweiten' verbundenen Tabelle lautet m; sie dient dazu, die Namen der Manager anzuzeigen. In diesem Fall müssen Sie sie mit LEFT JOIN verbinden, weil die Frage verlangt, dass Sie alle Mitarbeiter auflisten. Dazu gehören auch die Mitarbeiter, die keinen Vorgesetzten haben. Wenn Sie INNER JOIN verwenden würden, würden Sie nur die Mitarbeiter erhalten, die einen Vorgesetzten haben. Die Tabelle ist selbst-gejoint unter der Bedingung, dass die ID des Managers gleich der ID des Mitarbeiters ist. Auf diese Weise erhalten Sie die Namen der Vorgesetzten der einzelnen Mitarbeiter. Ausgabe: Hier ist die Liste der Angestellten und ihrer Vorgesetzten: employee_first_nameemployee_last_namemanager_first_namemanager_last_name JohnBorisovLindaJohnson LindaJohnsonTomBonfa FrankRanieriNULLNULL NinaBowieJohnBorisov TamaraFelipeNULLNULL SimonFyodorovTomBonfa LanaHopkinsNULLNULL TomBonfaJohnBorisov MariaFoxJohnBorisov VictorIvanchichLindaJohnson NULLs als Vorgesetztennamen bedeuten, dass der jeweilige Mitarbeiter keinen Vorgesetzten hat. Frage 6: Therapeuten und ihre erste und zweite Sprache anzeigen Schreiben Sie eine Abfrage, die alle Therapeuten mit ihrer ersten und zweiten Sprache ausgibt. Datensatz: Dieser Datensatz stammt aus einer psychotherapeutischen Gemeinschaftspraxis für Auswanderer. Mehrere Therapeuten bieten Therapien an, und jeder von ihnen tut dies in zwei Sprachen. Die Liste der Sprachen befindet sich in der Tabelle language. Hier ist das Skript. idlanguage_name 1English 2Dutch 3Russian 4Polish 5Croatian Die Liste der Therapeuten ist in der Tabelle zu finden therapist. Hier ist das Skript. idfirst_namelast_namefirst_language_idsecond_language_id 1MayaHoekstra21 2LanaMayakovski31 3MarijaAbramović52 4JanNowak41 5FrancisGordon12 Die Lösung: Bei dieser Aufgabe, einer der vielen SQL-Interviewfragen für erfahrene Benutzer, müssen Sie Ihre Fähigkeiten bei der Verknüpfung von drei Tabellen unter Beweis stellen. Hier wird jedoch eine Tabelle zweimal verknüpft. Das müssen Sie erkennen, denn die Tabelle therapist auf die Tabelle language in zwei Spalten verweist: first_language_id und second_language_id. Die Lösung sollte wie folgt aussehen: SELECT t.first_name, t.last_name, fl.language_name AS first_language_name, sl.language_name AS second_language_name FROM therapist t JOIN language fl ON t.first_language_id = fl.id JOIN language sl ON t.second_language_id = sl.id; Erläuterung: Zuerst verknüpfen wir die Tabelle therapist mit der Tabelle languagezusammen, wobei letztere den Alias fl (wie in 'erste Sprache'). Wir verwenden ihn, um die erste Sprache des Therapeuten anzuzeigen, d.h. seine Muttersprache. Deshalb wird in der Verknüpfungsbedingung gesucht, ob die ID der ersten Sprache mit der ID der Sprache übereinstimmt. Dies führt dazu, dass der Name der ersten Sprache angezeigt wird. Im nächsten Schritt verknüpfen wir wieder die Tabelle language. Diesmal hat sie den Alias sl für 'zweite Sprache'. Der Join nimmt die ID der zweiten Sprache und sucht sie in language. Auf diese Weise erhalten wir den Namen der zweiten Sprache. Um die erste und zweite Sprache anzuzeigen, wählen wir die Spalte language_name aus - einmal aus der fl 'Tabelle' und das zweite Mal aus der sl Tabelle' - und geben den Spalten entsprechende Namen. Ausgabe: Hier ist die Ausgabe: first_namelast_namefirst_language_namesecond_language_name JanNowakPolishEnglish LanaMayakovskiRussianEnglish MayaHoekstraDutchEnglish FrancisGordonEnglishDutch MarijaAbramovićCroatianDutch Frage 7: Zeigen Sie die Anzahl der Freiberufler mit zugewiesenen Aufgaben an Datensatz: Der in den Fragen 1, 3 und 4 verwendete Freelancer-Datensatz. Lösung: Diese knifflige Interviewfrage führt Sie zur Verwendung der Aggregatfunktion COUNT(). Es scheint sehr einfach zu sein, mit einer einfachen Abfrage, die nur eine Tabelle verwendet. Die Frage verlangt jedoch, dass Sie die folgende Abfrage in aller Eile schreiben: SELECT COUNT(freelancer_id) AS number_of_working_freelancers FROM task; Sie müssen jedoch zeigen, dass Sie schlauer sind als das und eine Abfrage schreiben, die COUNT(DISTINCT freelancer_id) statt COUNT(freelancer_id) verwendet. SELECT COUNT(DISTINCT freelancer_id) AS number_of_working_freelancers FROM task; Erläuterung: Warum ist die erste Abfrage falsch? Nun, COUNT(freelancer_id) zählt jede Instanz der ID eines Freiberuflers. Das bedeutet, dass sie auch Duplikate als einen anderen Freiberufler zählt. (Denken Sie daran, dass jeder Freiberufler mehrere Aufgaben haben kann.) Um dies zu vermeiden, fügen Sie einfach DISTINCT in diesen Ausdruck ein. Dadurch werden Duplikate eliminiert, d.h. jeder Freiberufler wird nur einmal gezählt. Ausgabe: Die erste Abfrage wird dies zurückgeben: number_of_working_freelancers 8 Sie wissen, dass das falsch ist, weil Sie Ihre Daten kennen. Die Tabelle freelancer sind nur fünf Freiberufler aufgelistet, also kann es nicht wahr sein, dass mehr Freiberufler arbeiten als es Freiberufler gibt. Die korrekte Ausgabe ist also die unten stehende. Es gibt vier Freiberufler, weil wir wissen, dass einer nicht zugewiesen ist, d. h. er arbeitet nicht. number_of_working_freelancers 4 Frage 8: Zeigen Sie die Anzahl der Aufgaben nach Aufgabentyp und Untertyp Datensatz: Dasselbe wie oben. Lösung: Hier müssen Sie erkennen, dass Sie eine Aggregatfunktion verwenden und die Ausgabe nach zwei Spalten gruppieren müssen. SELECT task_type, task_subtype, COUNT(*) AS number_of_tasks FROM task_category tc JOIN task t ON tc.id = t.task_category_id GROUP BY task_type, task_subtype; Erläuterung: Um die Ausgabe zu erhalten, müssen Sie die Tabellen task_category und task über die Aufgabenkategorie-ID verbinden. Wählen Sie dann den Aufgabentyp und den Untertyp aus und verwenden Sie COUNT(*), das einfach die Anzahl der Zeilen zählt, die der Anzahl der Aufgaben entspricht. Jede Zeile ist eine Aufgabe. Verwenden Sie anschließend GROUP BY, um die Daten nach Aufgabentyp zu gruppieren. In der Frage werden Sie jedoch aufgefordert, die Daten auch auf der Ebene der Aufgabensubtypen zu aggregieren, so dass Sie dies in GROUP BY hinzufügen müssen. Alle Spalten in GROUP BY müssen durch ein Komma getrennt werden. Ausgabe: Der Aufgabentyp "Social Media Post" erscheint nur einmal, da es keine anderen Untertypen in den aktiven Aufgaben gibt. Der Aufgabentyp "Blog-Artikel" hingegen erscheint dreimal, jeweils mit einem anderen Aufgabensubtyp. Die Spalte number_of_tasks gibt die Anzahl der Aufgaben pro Untertyp an. task_typetask_subtypenumber_of_tasks Social media postLinkedIn2 Blog articleSQL3 Blog articlePython2 Blog articleCareer1 Frage 9: Zeigen Sie die Anzahl der aktiven Aufgaben nach Aufgabentyp und Untertyp Schreiben Sie eine Abfrage, die die Anzahl der aktiven Aufgaben nach Aufgabentyp und Untertyp anzeigt. Schließen Sie nur die Kategorien mit mehr als zwei Aufgaben ein. Datensatz: Gleiche wie oben. Lösung: Diese allgemeine SQL-Interviewfrage prüft, ob Sie erkennen, dass Sie HAVING anstelle von WHERE verwenden müssen, um die Ausgabe zu filtern. Sie können die Frage wie folgt lösen: SELECT task_type, task_subtype, COUNT(*) AS number_of_tasks FROM task_category tc JOIN task t ON tc.id = t.task_category_id WHERE COUNT(*) > 2 GROUP BY task_type, task_subtype; Das ist falsch, also müssen Sie WHERE durch HAVING ersetzen: SELECT task_type, task_subtype, COUNT(*) AS number_of_tasks FROM task_category tc JOIN task t ON tc.id = t.task_category_id GROUP BY task_type, task_subtype HAVING COUNT(*) > 2; Erläuterung: Diese Abfrage ist im Grunde die gleiche wie die aus der vorherigen Frage. Die zusätzliche Anforderung ist, nur Aufgabentypen und Untertypen mit mehr als zwei aktiven Aufgaben anzuzeigen. Die erste Abfrage gibt nichts zurück, außer einem Fehler, der besagt, dass Aggregatfunktionen nicht in WHERE verwendet werden können . Das liegt natürlich daran, dass WHERE die Daten vor der Aggregation filtert. Sie müssen also zunächst die Daten mit COUNT(*) aggregieren, um die Anzahl der aktiven Aufgaben nach Typ und Untertyp zu ermitteln. Erst danach können Sie nach den Kategorien mit mehr als zwei Aufgaben suchen. Mit anderen Worten: Sie müssen HAVING verwenden, da es die Daten nach der Aggregation filtert. Sie verwenden einfach die Aggregation aus der Spalte number_of_tasks und geben eine Bedingung an, dass die Anzahl größer als zwei sein muss. Ausgabe: task_typetask_subtypenumber_of_tasks Blog articleSQL3 Frage 10: Was stimmt mit dieser Abfrage nicht? Datensatz: Dieselbe wie oben. Lösung: Die Frage gibt Ihnen eine Abfrage vor: SELECT first_name, last_name, task_type, task_subtype, COUNT(task_category_id) AS task_count FROM freelancer f JOIN task t ON f.id = t.freelancer_id JOIN task_category tc ON t.task_category_ID = tc.id GROUP BY first_name, last_name, task_type ORDER BY last_name; Ihre Antwort sollte lauten, dass diese Abfrage nicht funktioniert, weil die Spalte task_subtype nicht in der Klausel GROUP BY aufgeführt ist. Die korrigierte Abfrage sollte wie folgt aussehen: SELECT first_name, last_name, task_type, task_subtype, COUNT(task_category_id) AS task_count FROM freelancer f JOIN task t ON f.id = t.freelancer_id JOIN task_category tc ON t.task_category_ID = tc.id GROUP BY first_name, last_name, task_type, task_subtype ORDER BY last_name; Erläuterung: Warum muss die Spalte task_subtype in GROUP BY erscheinen? In SQL gilt die Regel, dass alle Spalten (außer denen, die Aggregatfunktionen enthalten) in GROUP BY erscheinen müssen. Das sollten Sie wissen und in der Abfrage sofort erkennen können. Ausgabe: Die korrigierte Ausgabe funktioniert nun und liefert das folgende Ergebnis. Es zeigt die Freiberufler und die Anzahl ihrer Aufgaben nach Typ und Untertyp. first_namelast_nametask_typetask_subtypetask_count EmmaBiesaBlog articlePython1 EmmaBiesaBlog articleSQL1 FrancoisCousteauSocial media postLinkedIn2 BobFranklinBlog articlePython1 BobFranklinBlog articleSQL1 DionneRavanelliBlog articleCareer1 DionneRavanelliBlog articleSQL1 Frage 11: Zeige alle Freiberufler und die Anzahl ihrer Aufgaben Datensatz: Dasselbe wie oben. Lösung: Bei dieser Frage könnten Sie leicht dazu verleitet werden, eine Abfrage zu schreiben, die COUNT(*) verwendet, um die Anzahl der Aufgaben zu ermitteln, etwa so: SELECT first_name, last_name, COUNT(*) AS task_count FROM freelancer f LEFT JOIN task t ON f.id = t.freelancer_id GROUP BY first_name, last_name; Ja, Sie haben LEFT JOIN klugerweise verwendet, um Freiberufler ohne Aufgaben zurückzugeben. Sie sollten jedoch COUNT(task_category_id) anstelle von COUNT(*) verwenden ... SELECT first_name, last_name, COUNT(task_category_id) AS task_count FROM freelancer f LEFT JOIN task t ON f.id = t.freelancer_id GROUP BY first_name, last_name; ... richtig? Erläuterung: Fallen Sie nicht auf diesen Trick herein! Sie wissen sicher, dass COUNT(*) nicht in Kombination mit LEFT JOIN verwendet werden sollte. Sie verwenden LEFT JOIN, um Freiberufler ohne die Aufgabe einzuschließen. Diese Freiberufler haben keine übereinstimmenden Werte in der rechten Tabelle, also werden sie als NULL angezeigt. Leider ignoriert COUNT(*) die NULLnicht, so dass sie als reguläre Werte gezählt werden. Stattdessen müssen Sie COUNT(task_category_id) verwenden. Auf diese Weise werden nur nicht-NULL Werte gezählt. Ausgabe: Werfen Sie einen Blick auf die Ausgabe der ersten (falschen) Abfrage: first_namelast_nametask_count DionneRavanelli2 MarekLewandowski1 EmmaBiesa2 BobFranklin2 FrancoisCousteau2 Marek Lewandowski hat eine Aufgabe. Wir wissen aber, dass dies nicht stimmen kann, da ihm keine Aufgaben zugewiesen sind. Die Ausgabe zeigt den Wert eins an, weil COUNT(*) den Wert NULL (nicht übereinstimmende Zeile) gezählt hat. Die Ausgabe der Lösungsabfrage zeigt richtigerweise, dass Mareks Aufgabenanzahl null ist: first_namelast_nametask_count DionneRavanelli2 MarekLewandowski0 EmmaBiesa2 BobFranklin2 FrancoisCousteau2 Frage 12: Zeigen Sie die Anzahl der erledigten Aufgaben nach Erledigungsdatum Schreiben Sie eine Abfrage, die die Anzahl der erledigten Aufgaben nach Erledigungsdatum anzeigt. Schließen Sie NULLals eine separate Datumskategorie ein. Dataset: Dieselbe wie oben. Lösung: Mit dieser Frage wird versucht, Ihnen vorzugaukeln, dass Sie irgendwie explizit eine Bedingung angeben müssen, unter der alle Aufgaben ohne Erledigungsdatum unter der Kategorie NULL als ein Datum gezählt werden. Aber die Lösung ist einfacher als Sie denken: SELECT completed_date, COUNT(id) AS completed_task_count FROM task GROUP BY completed_date ORDER BY completed_date ASC; Erläuterung: Wie Sie sehen können, bezieht sich die obige Abfrage in keiner Weise auf NULLs. Sie wählt einfach das Erledigungsdatum aus und verwendet COUNT() für die Spalte Aufgaben-ID, um die Anzahl der erledigten Aufgaben zu zählen. Natürlich muss die Ausgabe nach dem Erledigungsdatum gruppiert werden. Außerdem wird sie vom ältesten zum neuesten Datum sortiert, was nicht notwendig ist, aber besser aussieht. Indem Sie eine solche Abfrage schreiben, zeigen Sie, dass Sie verstanden haben, dass die NULL Werte nicht separat gezählt werden. Alle Werte von NULL werden als eine Kategorie angezeigt - NULL. Ausgabe: Wie Sie sehen können, werden alle Aufgaben ohne Fertigstellungsdatum in einer Zeile angezeigt: completed_datecompleted_task_count 2023-12-151 2023-12-201 2024-01-181 2024-02-101 2024-02-152 NULL2 Frage 13: Mitarbeiter mit ihren Abteilungen und Gehältern anzeigen Schreiben Sie eine Abfrage, die Mitarbeiter, ihre Abteilungen und ihre Gehälter anzeigt. Schließen Sie nur Mitarbeiter mit einem Gehalt ein, das unter dem Durchschnitt ihrer Abteilung liegt. Datensatz: Diese SQL-Interviewfrage verwendet die Tabelle salaries. Das Skript finden Sie hier. idfirst_namelast_namedepartmentsalary 1BennyGilhespySales5,293.47 2BenettaFeatherstonhaughEngineering2,214.55 3KarlaStiellSales2,070.45 4SissieSeabonAccounting5,077.42 5GennaBecheAccounting7,451.65 6KirstenFernandezEngineering7,533.13 7PenFredySales7,867.54 8TishCalderbankSales4,103.19 9GallardPhilipetAccounting7,220.06 10WalshKleinholzAccounting4,000.18 11CarceWilkensonAccounting3,991.00 12TamiLangrishSales5,588.34 13ShayneDearnEngineering2,785.92 14MerlaIngilsonEngineering2,980.36 15KeelyPatifieldSales2,877.92 Lösung: Der knifflige Teil besteht darin zu erkennen, dass die Abfrage sehr kurz sein kann, wenn Sie wissen, wie man korrelierte Unterabfragen verwendet. Die Abfrage sollte wie folgt aufgebaut sein: SELECT id, first_name, last_name, department, salary FROM salaries s1 WHERE salary < (SELECT AVG(salary) FROM salaries s2 WHERE s1.department = s2.department); Erläuterung: Die Abfrage listet also zunächst alle erforderlichen Spalten der Tabelle salaries auf. Ich habe der Tabelle einen Alias gegeben, s1. Dann verwende ich die WHERE Klausel, um die Gehälter der einzelnen Mitarbeiter mit dem Abteilungsdurchschnitt zu vergleichen. Der Abteilungsdurchschnitt wird in einer speziellen Art von Unterabfrage berechnet - einer korrelierten Unterabfrage. Was ist daran so besonders? Nun, diese Unterabfrage ist korreliert, weil sie auf die Daten der Hauptabfrage verweist. Dies geschieht in der WHERE Klausel einer Unterabfrage: die Abteilung aus der s1 (die in der Hauptabfrage erscheint) muss dieselbe sein wie die Abteilung in der s2 Tabelle sein, die in der Unterabfrage erscheint. Diese Bedingung ermöglicht es der Aggregatfunktion AVG(), den Abteilungsdurchschnitt der Abteilung zu berechnen, in der dieser bestimmte Mitarbeiter arbeitet. Ausgabe: In der folgenden Tabelle sind nur die Mitarbeiter aufgeführt, deren Gehalt unter dem Durchschnitt der Gehälter ihrer jeweiligen Abteilung liegt: idfirst_namelast_namedepartmentsalary 2BenettaFeatherstonhaughEngineering2,214.55 3KarlaStiellSales2,070.45 4SissieSeabonAccounting5,077.42 8TishCalderbankSales4,103.19 10WalshKleinholzAccounting4,000.18 11CarceWilkensonAccounting3,991.00 13ShayneDearnEngineering2,785.92 14MerlaIngilsonEngineering2,980.36 15KeelyPatifieldSales2,877.92 Frage 14: Was ist der Unterschied zwischen UNION und UNION ALL? Antwort: Um diese Frage zu beantworten, müssen Sie natürlich den Unterschied zwischen den beiden gängigsten Mengenoperatoren in SQL kennen. Beide Operatoren fassen die Ergebnisse von zwei oder mehr Abfragen vertikal zusammen. UNION tut dies, indem es doppelte Zeilen ausschließt. Mit anderen Worten: Wenn die gleichen Zeilen in beiden Abfragen vorkommen, werden sie nur einmal angezeigt. Man kann sich das wie DISTINCT in der Welt der Mengenoperatoren vorstellen. Auf der anderen Seite zeigt UNION ALL alle Zeilen aus beiden Abfragen an, einschließlich der doppelten. Sie können mehr über den Unterschied zwischen UNION und UNION ALL in unserem Leitfaden lesen. Frage 15: Ausgewählte Bücher mit ihrem Autor und Untertitel anzeigen Schreiben Sie eine Abfrage, die den Autor, den Titel und den Untertitel eines Buches auswählt - aber nur für die Bücher, bei denen der Untertitel das Wort "Frau" enthält. Schließen Sie Bücher ohne Untertitel ein. Datensatz: Die in diesem Beispiel verwendete Tabelle ist booksund das Skript befindet sich hier. idfirst_namelast_namedepartmentsalary 2BenettaFeatherstonhaughEngineering2,214.55 3KarlaStiellSales2,070.45 4SissieSeabonAccounting5,077.42 8TishCalderbankSales4,103.19 10WalshKleinholzAccounting4,000.18 11CarceWilkensonAccounting3,991.00 13ShayneDearnEngineering2,785.92 14MerlaIngilsonEngineering2,980.36 15KeelyPatifieldSales2,877.92 Lösung: Der einfache Teil ist, dass Sie nach dem Wort "Frau" im Untertitel suchen müssen. Aber wie können Sie auch Bücher ohne Untertitel - d.h. mit NULL - einbeziehen? Die Antwort ist, dass Sie NULLexplizit behandeln müssen, um sie in die Ausgabe einzubeziehen, etwa so: SELECT author, title, subtitle FROM books WHERE subtitle ILIKE '%woman%' OR subtitle IS NULL; Erläuterung: Ihre Antwort enthält zwei Bedingungen in WHERE. Die erste Bedingung sucht nach dem Wort "Frau" im Untertitel. Dazu verwenden Sie entweder LIKE (wenn Ihre Datenbank Groß- und Kleinschreibung nicht berücksichtigt) oder ILIKE (wenn Ihre Datenbank Groß- und Kleinschreibung berücksichtigt, wie PostgreSQL). Um das Wort an einer beliebigen Stelle in einer Zeichenkette zu suchen, müssen Sie es mit '%' umgeben. Da Sie nach einer Zeichenkette suchen, muss alles in einfache Anführungszeichen gesetzt werden. Nun können Sie eine weitere Filterbedingung hinzufügen, in der Sie angeben, dass der Untertitel NULL sein muss, indem Sie den Operator IS NULL verwenden. Die beiden Bedingungen werden mit dem Schlüsselwort OR verknüpft, da sie nicht gleichzeitig erfüllt sein können: Wenn es keinen Untertitel gibt, kann er nicht das Wort "Frau" enthalten. Ausgabe: Hier ist die Ausgabe, die alle Daten zeigt, die eine der beiden Bedingungen erfüllen: authortitlesubtitle Miljenko JergovićSarajevo MarlboroNULL Tilar J. MazzeoIrena's ChildrenThe Extraordinary Story of the Woman Who Saved 2,500 Children from the Warsaw Ghetto Olga TokarczukPrimeval and Other TimesNULL Thomas HardyTess of the d' UrbevillesA Pure Woman Faithfully Presented Sylvia PlathArielNULL Toni MorrisonJazzNULL Haben GirmaHabenThe Deafblind Woman Who Conquered Harvard Law Weitere Interview-Ressourcen für erfahrene SQL-Benutzer Diese 15 Übungen decken einige der häufigsten "Trick"-Fragen in SQL-Interviews für erfahrene Benutzer ab. Wenn Sie all diese Lösungen kennen, sollten Sie wissen, welche Fallen Ihnen gestellt werden und wie Sie sie vermeiden können. Aber Sie sollten jetzt nicht aufhören! Man kann sich nie zu gut auf ein Vorstellungsgespräch vorbereiten. Daher empfehle ich Ihnen, sich weitere 25 Beispiele für fortgeschrittene SQL-Abfragen oder weitere 27 Fragen für fortgeschrittene SQL-Interviews anzusehen. Sie sollten das hier Gelernte auch üben. Hier finden Sie einige Ideen zum Üben von fortgeschrittenem SQL mit unseren Kursen und einige fortgeschrittene SQL-Übungen, die Ihnen den Einstieg erleichtern. Verwenden Sie dies in Verbindung mit unserem Fortgeschrittenes SQL Track und Fortgeschrittenes SQL Practice Track, und Sie werden für Ihr nächstes Vorstellungsgespräch gut vorbereitet sein! Tags: SQL Jobinterview Fragen SQL für Fortgeschrittene