Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Challenge: Query Optimization | Query optimization.Indexes
Advanced Techniques in SQL
course content

Зміст курсу

Advanced Techniques in SQL

Advanced Techniques in SQL

1. ACID
Course OverviewWhat is ACID?AtomicityChallenge: Transaction CreationConsistencyIsolation. Read Committed and Uncommitted LevelsIsolation. Repeatable Read and Serializable LevelsChallenge: Specifying Isolation Level for Transaction
2. Query optimization.Indexes
What are IndexesB-tree IndexingChallenge: Creating B-tree IndexHash IndexingData CachingGeneral Optimization StrategiesChallenge: Query Optimization
3. Some Additional Topics
TriggersChallenge: Creating After TriggerWindow FunctionsTypes of Window FunctionsChallenge: Using Window FunctionData Control LanguageViewsNoSQL

Challenge: Query Optimization

Let's discover two tables: BankAccounts and one additional table named Transactions.

BankAccounts table

Transactions table

Assume we want to retrieve only the account numbers that have performed at least one transaction.

In this task, we have a query that is not optimized. Your task is to rewrite it in a more optimal way.

Here is a list of the main query rewriting techniques to enhance optimization:

  1. Explicitly Specify Columns: Instead of using the asterisk (*) wildcard, explicitly mention column names in queries for better performance, readability, and maintainability;
  2. Minimize Subqueries: Reduce the use of subqueries to optimize query performance. Consider alternatives like joins or derived tables to avoid complexity and overhead;
  3. Avoid Repeated IN Operators: Limit the use of the IN operator in queries to prevent performance impact. Instead, consider using JOIN or EXISTS clauses for more efficient execution plans;
  4. Organize Joins Logically: Start SQL joins with the main table and then join with related tables to optimize query organization and database engine optimization;
  5. Use Restrictive WHERE Conditions: Improve query performance by including restrictive conditions in the WHERE clause to filter rows and enhance execution speed;
  6. Refactor Code into Stored Procedures or Functions: Encapsulate repetitive code segments into stored procedures or user-defined functions for code reusability, modularity, and easier maintenance. These can reduce redundancy and optimize SQL queries.

Завдання

Please rewrite the query more optimally. Pay attention to the fact that we need to get only account numbers that have performed at least one transaction (transferred money to another account) sorted in ascending order.

Note

Use LEFT or INNER join type to complete this task.

Завдання

Please rewrite the query more optimally. Pay attention to the fact that we need to get only account numbers that have performed at least one transaction (transferred money to another account) sorted in ascending order.

Note

Use LEFT or INNER join type to complete this task.

Все було зрозуміло?

Секція 2. Розділ 7
toggle bottom row

Challenge: Query Optimization

Let's discover two tables: BankAccounts and one additional table named Transactions.

BankAccounts table

Transactions table

Assume we want to retrieve only the account numbers that have performed at least one transaction.

In this task, we have a query that is not optimized. Your task is to rewrite it in a more optimal way.

Here is a list of the main query rewriting techniques to enhance optimization:

  1. Explicitly Specify Columns: Instead of using the asterisk (*) wildcard, explicitly mention column names in queries for better performance, readability, and maintainability;
  2. Minimize Subqueries: Reduce the use of subqueries to optimize query performance. Consider alternatives like joins or derived tables to avoid complexity and overhead;
  3. Avoid Repeated IN Operators: Limit the use of the IN operator in queries to prevent performance impact. Instead, consider using JOIN or EXISTS clauses for more efficient execution plans;
  4. Organize Joins Logically: Start SQL joins with the main table and then join with related tables to optimize query organization and database engine optimization;
  5. Use Restrictive WHERE Conditions: Improve query performance by including restrictive conditions in the WHERE clause to filter rows and enhance execution speed;
  6. Refactor Code into Stored Procedures or Functions: Encapsulate repetitive code segments into stored procedures or user-defined functions for code reusability, modularity, and easier maintenance. These can reduce redundancy and optimize SQL queries.

Завдання

Please rewrite the query more optimally. Pay attention to the fact that we need to get only account numbers that have performed at least one transaction (transferred money to another account) sorted in ascending order.

Note

Use LEFT or INNER join type to complete this task.

Завдання

Please rewrite the query more optimally. Pay attention to the fact that we need to get only account numbers that have performed at least one transaction (transferred money to another account) sorted in ascending order.

Note

Use LEFT or INNER join type to complete this task.

Все було зрозуміло?

Секція 2. Розділ 7
toggle bottom row

Challenge: Query Optimization

Let's discover two tables: BankAccounts and one additional table named Transactions.

BankAccounts table

Transactions table

Assume we want to retrieve only the account numbers that have performed at least one transaction.

In this task, we have a query that is not optimized. Your task is to rewrite it in a more optimal way.

Here is a list of the main query rewriting techniques to enhance optimization:

  1. Explicitly Specify Columns: Instead of using the asterisk (*) wildcard, explicitly mention column names in queries for better performance, readability, and maintainability;
  2. Minimize Subqueries: Reduce the use of subqueries to optimize query performance. Consider alternatives like joins or derived tables to avoid complexity and overhead;
  3. Avoid Repeated IN Operators: Limit the use of the IN operator in queries to prevent performance impact. Instead, consider using JOIN or EXISTS clauses for more efficient execution plans;
  4. Organize Joins Logically: Start SQL joins with the main table and then join with related tables to optimize query organization and database engine optimization;
  5. Use Restrictive WHERE Conditions: Improve query performance by including restrictive conditions in the WHERE clause to filter rows and enhance execution speed;
  6. Refactor Code into Stored Procedures or Functions: Encapsulate repetitive code segments into stored procedures or user-defined functions for code reusability, modularity, and easier maintenance. These can reduce redundancy and optimize SQL queries.

Завдання

Please rewrite the query more optimally. Pay attention to the fact that we need to get only account numbers that have performed at least one transaction (transferred money to another account) sorted in ascending order.

Note

Use LEFT or INNER join type to complete this task.

Завдання

Please rewrite the query more optimally. Pay attention to the fact that we need to get only account numbers that have performed at least one transaction (transferred money to another account) sorted in ascending order.

Note

Use LEFT or INNER join type to complete this task.

Все було зрозуміло?

Let's discover two tables: BankAccounts and one additional table named Transactions.

BankAccounts table

Transactions table

Assume we want to retrieve only the account numbers that have performed at least one transaction.

In this task, we have a query that is not optimized. Your task is to rewrite it in a more optimal way.

Here is a list of the main query rewriting techniques to enhance optimization:

  1. Explicitly Specify Columns: Instead of using the asterisk (*) wildcard, explicitly mention column names in queries for better performance, readability, and maintainability;
  2. Minimize Subqueries: Reduce the use of subqueries to optimize query performance. Consider alternatives like joins or derived tables to avoid complexity and overhead;
  3. Avoid Repeated IN Operators: Limit the use of the IN operator in queries to prevent performance impact. Instead, consider using JOIN or EXISTS clauses for more efficient execution plans;
  4. Organize Joins Logically: Start SQL joins with the main table and then join with related tables to optimize query organization and database engine optimization;
  5. Use Restrictive WHERE Conditions: Improve query performance by including restrictive conditions in the WHERE clause to filter rows and enhance execution speed;
  6. Refactor Code into Stored Procedures or Functions: Encapsulate repetitive code segments into stored procedures or user-defined functions for code reusability, modularity, and easier maintenance. These can reduce redundancy and optimize SQL queries.

Завдання

Please rewrite the query more optimally. Pay attention to the fact that we need to get only account numbers that have performed at least one transaction (transferred money to another account) sorted in ascending order.

Note

Use LEFT or INNER join type to complete this task.

Секція 2. Розділ 7
Перейдіть на комп'ютер для реальної практикиПродовжуйте з того місця, де ви зупинились, використовуючи один з наведених нижче варіантів
We're sorry to hear that something went wrong. What happened?
some-alt