CTEs: give a query a name
WITH defines a common table expression for one statement. A CTE names an intermediate result so a complex question can be read in small steps. It is not a permanent table.
हिन्दी में समझें
WITH एक कथन के लिए CTE बनाता है। बीच के परिणाम को नाम देने से कठिन सवाल छोटे कदमों में पढ़ते हैं। यह स्थायी टेबल नहीं है।
अभ्यास: tech नाम की CTE में Tech उत्पाद name और price लें, परिणाम price क्रम में।
Hinglish mein samjhein
WITH ek statement ke liye CTE define karta hai. Intermediate result ko naam dene se complex question easy steps mein padhta hai. Yeh permanent table nahi.
Practice: tech CTE mein Tech products name,price lo aur price sorted return karo.
A small SQL example
WITH totals AS (SELECT customer_id,COUNT(*) AS n FROM orders GROUP BY customer_id) SELECT * FROM totals ORDER BY customer_id;Examples use the fictional shop database bundled with the SQL playground: customers, products, orders, order_items and employees. The engine is SQLite; other SQL dialects may differ.
Try this example in the SQL playground →Practise this idea
Use a CTE named tech to select Tech product name and price; return it ordered by price.
Check answer & explanation
WITH tech AS (SELECT name,price FROM products WHERE category='Tech') SELECT * FROM tech ORDER BY price;The CTE names a filtered intermediate result. The outer SELECT reads that result and applies the requested price ordering.
Stuck in the course? Choose Check answer for either the challenge or quiz. Read the explanation and continue to the next lesson at your own pace.
Check your understanding
A CTE lasts for…
Check the quiz answer
One statement
WITH defines a common table expression for one statement. A CTE names an intermediate result so a complex question can be read in small steps. It is not a permanent table.
References & corrections
Use the official SQLite documentation for full language rules. Read how these lessons are made and checked. Found a mistake? Report it to Excel Keys with this lesson URL and a reproducible example.