SQL Quiz

What is SQL Query Optimization

• The way to write SQL query shorter
• The way to write SQL query prettier
• The way to write SQL run faster

What is execution plan

• The step when database check syntax and validation your query
• The blueprint your administrator uses to create a database
• The tool to monitor query run time
• The detailed step-by-step analysis of how to execute a SQL query
• The tool which database engine uses to cache most used queries

Table "product" have these columns below. Which one should be indexed ?

• bar_code
• title
• price
• date
• brand_name

Which query below should efficient than others ?

• SELECT * FROM product WHERE bar_code LIKE '%91%'
• SELECT * FROM product WHERE bar_code LIKE '91%'
• SELECT title, date, price FROM product WHERE bar_code LIKE '91%'
• SELECT title, date, price FROM product WHERE bar_code LIKE '%91'
• SELECT title, date, price FROM product WHERE bar_code LIKE '%91%'

Which query below should efficient than other ?

• SELECT e.employee_id, e.first_name, e.last_name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM managers m WHERE m.manager_id = e.employee_id );
• SELECT e.employee_id, e.first_name, e.last_name FROM employees e WHERE e.employee_id NOT IN ( SELECT m.manager_id FROM managers m );

Which query below should efficient than other ?

• SELECT product_id, sales_amount, 2023 AS year FROM sales_2023 UNION ALL SELECT product_id, sales_amount, 2024 AS year FROM sales_2024
• SELECT product_id, sales_amount, 2023 AS year FROM sales_2023 UNION SELECT product_id, sales_amount, 2024 AS year FROM sales_2024

Partitioning is..

• The process of dividing a table into smaller, more manageable pieces
• The act of creating a new database
• The method of storing data on multiple hard drives.
• A way to improve query performance by indexing data.
• The method of migration database to cloud

The main purpose of partitioning a table is to

• Improve data security
• Reduce storage requirements.
• Enhance query performance.
• All of the above.

Partitioning can be beneficial when …

• The table is very large and frequently accessed
• The table is small and infrequently accessed.
• The table has a high degree of concurrency
• All of the above.

An index is…

• A way to organize and store data in a database.
• A method for improving query performance.
• A supplementary data structure that improves the speed of data retrieval operations.
• All of the above.