Impatto dell'ordine delle operazioni sulla struttura della query

La World Bank ha dati su molti paesi, ma mancano valori per alcuni anni. Decidi di cercare i paesi che hanno almeno un anno con dati sulla popolazione mancanti. Scrivi la seguente query, ma NON viene eseguita.

SELECT olympic_cc, COUNT(*)
FROM demographics
WHERE population IS NULL
AND COUNT(*) > 1

Consulti gli appunti sull'ordine delle operazioni in SQL per capire il motivo.

Quale delle seguenti opzioni spiega l'errore logico?

Questo esercizio fa parte del corso

Migliorare le prestazioni delle query in PostgreSQL

Visualizza il corso

Esercizio pratico interattivo

Passa dalla teoria alla pratica con uno dei nostri esercizi interattivi

Inizia esercizio

Questo esercizio fa parte del corso

Migliorare le prestazioni delle query in PostgreSQL

IntermediárioNível de habilidade

4.8+

Inizia il corso gratis

Bundle up as you dive into the Winter Olympics! You will learn how to join, subquery, and create temporary tables while finding which Olympic athletes brave sub-freezing temperatures to train. You will also learn about the query planner and how its functionality can guide your SQL structure to faster queries.

Exercise 1: All about joins Exercise 2: Where are all the athletes from Exercise 3: Using different joins to explore athletes' regions Exercise 4: What about the weather Exercise 5: Subqueries and common table expressions (cte)Exercise 6: Filtering to freezing with a subquery Exercise 7: Where winter is white Exercise 8: Countries with subqueries or CTEs Exercise 9: Working with temporary tables Exercise 10: Canadians temp table Exercise 11: Analyze that temp table

Dig up those past algebra memories while learning the SQL order of operations. Find which countries "should" have the most athletes by looking at population and gross domestic product (GDP) while learning the best way to filter. You will also learn when your query aggregates (sums, counts, etc.) and how you can structure your query to optimize this process.

Exercise 1: Quello che scrivi non è ciò che vede SQL Exercise 2: Impatto dell'ordine delle operazioni sulla struttura della query

Esercizio in corso

Exercise 3: Group by e aggregazioni Exercise 4: COUNT e COUNT DISTINCT Exercise 5: Filtrare nella clausola WHERE Exercise 6: OR vs IN con gli atleti Exercise 7: Filtri per tipo di dato Exercise 8: EXPLAIN il passo del piano di query con filtro Exercise 9: Filtrare durante la JOIN Exercise 10: Dove inserire un filtro per regione Exercise 11: Filtrare nel join, nel where e nel select Exercise 12: Aggregare con diverse granularità dei dati Exercise 13: Aggrega prima di unire le tabelle Exercise 14: Tendenze del Sudafrica

Zero in on the properties that improve database performance. Discover when your table is not a table but a view. Learn how your database's storage structure (row or column oriented) impacts your query structure. You will explore volcanic smog while using partitions and indexes to speed your queries.

Exercise 1: Queries and tables and views, oh my Exercise 2: Data loading and storage Exercise 3: Finding the table type Exercise 4: Row-oriented storage and partitions Exercise 5: Row-oriented storage properties Exercise 6: Previewing a row-oriented table Exercise 7: Partitioning on location Exercise 8: Using and creating indexes Exercise 9: Finding the database indexes Exercise 10: Creating and using an index Exercise 11: Compare runtimes Exercise 12: Using column-oriented storage Exercise 13: Column-oriented storage properties Exercise 14: Using the information schema

Learn the lingo of the Query Lifecycle and dive into the query planner. Explore how the query planner creates and optimizes the query plan. Find your next vacation locale by looking for countries with recent population growth while also seeing how a join impacts the query steps. Fine tune your optimization techniques by seeing how different filters speed your query times.

Exercise 1: Query lifecycle and the planner Exercise 2: Exploring pg_tables Exercise 3: Basic EXPLAIN Exercise 4: Index scans Exercise 5: EXPLAIN the WHERE Exercise 6: A deeper dive into EXPLAIN Exercise 7: EXPLAIN parameters Exercise 8: Aggregating and sorting populations Exercise 9: Joining in the query plan Exercise 10: Query structure and query execution Exercise 11: Subqueries vs. CTEs Exercise 12: Why the difference?Exercise 13: Filtering impacts Exercise 14: Congratulations