Simplify queries, WITH keyword

Teil des Abschnitts Grundlagen der SQL-Journey von Coddy — Lektion 49 von 72.

Abfragen können durch das Hinzufügen vieler innerer Abfragen zu unübersichtlich werden. Hier ist zum Beispiel eine Abfrage, die viele Unterabfragen enthält:

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

Um es einfacher zu machen, können wir das Schlüsselwort WITH query_name AS (...) verwenden. Es ermöglicht uns, eine Abfrage unter einem Namen zu speichern und sie überall dort zu verwenden, wo wir wollen:

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

Hier haben wir query1 in query2 und in der Hauptabfrage wiederverwendet.

Aufgabe

Einfach

Verfügbare Tabellen und Spalten:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

Schreiben Sie eine Abfrage, die:

Zuerst "große Geräte" identifiziert (Geräte mit einer width größer als 200)
Dann die durchschnittliche Punktzahl für diese großen Geräte berechnet. Benennen Sie diese Spalte als average_score

Verwenden Sie die WITH-Klausel, um dieses Problem zu lösen.

Probier es selbst