PERFORMANCE TIPS
75
4
76
7
77
10
11
78
12
79
13
14
80
15
16
17
81
18
19
82
20
83
BEST PRACTICES CREATING TABLES (DDL)
21
22
23
24
25
84
BEST PRACTICES INDEXING
26
27
28
29
30
85
86
87
AI & SQL
PROMPT 1 IMPROVE SQL
In my SQL Server database, we have two tables:
The first table is orders with the following columns: order_id, sales, customer_id, product_id.
The second table is customers with the following columns: customer_id, first_name, last_name, country.
Do the following:
Write a query to rank customers based on their sales.
The result should include the customer's customer_id, full name, country, total sales, and their rank.
Include comments but avoid commenting on obvious parts.
Write three different versions of the query to achieve this task.
Evaluate and explain which version is best in terms of readability and performance
PROMPT 2 REDUCE CTE
The following SQL Server query is is long and hard to understand.
Do the following:
Improve its readability.
Remove any redundancy in the query and consolidate it.
Include comments but avoid commenting on obvious parts.
Explain each improvement to understand the reasoning behind it.
PROMPT 3 OPTIMIZE QUERY
The following SQL Server query is slow.
Do the following:
Propose optimizations to improve its performance.
Provide the improved SQL query.
Explain each improvement to understand the reasoning behind it.
88
PROMPT 4 OPTIMIZE EXECUTION PLAN
The image is the execution plan of SQL Server query.
Do the following:
Describe the execution plan step by step in the logical order
Identify performance bottlenecks and issues.
Suggest ways to improve performance and optimize the execution
plan.
PROMPT 5 DEBUGING
PROMPT 6 EXPLAIN RESULT
I didn't understand the result of the following SQL Server query.
Do the following:
Break down how SQL processes the following query step by step.
Explaining each stage and how the result is formed.
PROMPT 7 INDENT AND FORMAT
The following SQL Server query hard to understand.
Do the following:
Restyle the code to make it easier to read.
Align column aliases.
Keep it compact - do not introduce unnecessary new lines.
Ensure the formatting follows best practices.
PROMPT 8 DOCUMENT AND COMMENT
I'd like to create well-structured documentation for the following SQL
Server query. Do the following:
- Insert a leading comment at the start of the query describing its overall purpose.
Add comments within the query only where clarification is necessary, avoiding obvious statements.
- Create a separate document explaining the business rules implemented by the query.
Create another separate document describing how the query works.
89
PROMPT 9 IMPROVE DATABASE DDL
The following SQL Server DDL Script has to be optimized.
Do the following:
Naming: Check the consistency of table/column names, prefixes, standards.
Data Types: Ensure data types are appropriate and optimized.
Integrity: Verify the integrity of primary keys and foreign keys.
Indexes: Check that indexes are sufficient and avoid redundancy.
Normalization: Ensure proper normalization and avoid redundancy.
PROMPT 10 GENERATE TEST DATASET
I need dataset for testing for the following SQL Server DDL
Do the following:
Generate test dataset as Insert statements.
Dataset should be realstic.
Keep the dataset small.
Ensure all primary/foreign key relationships are valid (use matching IDs).
Dont introduce any Null values.
BEGIN TO LEARN
PROMPT 11 CREATE SQL COURSE
Create a comprehensive SQL course with a detailed roadmap and agenda.
Do the following:
Start with SQL fundamentals and advance to complex topics.
Make it beginner-friendly.
Include topics relevant to data analytics.
Focus on real-world data analytics use cases and scenarios.
PROMPT 12 UNDERSTAND SQL CONCEPTS
I want detailed explanation about SQL Window Functions.
Do the following:
Explain what Window Functions are.
Give an analogy.
Describe why we need them and when to use them.
Explain the syntax.
Provide simple examples.
List the top 3 use cases.
90
PROMPT 13 COMPARING SQL CONCEPTS
I want to understand the differences between SQL Windows and GROUP BY.
Do the following:
Explain the key differences between the two concepts.
Describe when to use each concept, with examples.
Provide the pros and cons of each concept.
Summarize the comparison in a clear side-by-side table.
PROMPT 14 PRACTICE SQL
Act as an SQL trainer and help me practice SQL Window Functions.
Do the following:
Make it interactive Practicing, you provide task and give solution.
Provide a sample dataset.
Give SQL tasks that gradually increase in difficulty.
Act as an SQL Server and show the results of my queries.
Review my queries, provide feedback, and suggest improvements.
PROMPT 15 PREPARE FOR A SQL INTERVIEW
Act as Interviewer and prepare me for a SQL interview.
Do the following:
Ask common SQL interview questions.
Make it interactive Practicing, you provide question and give answer.
Gradually progress to advanced topics.
Evaluate my answer and give me a feedback.
91
92
PROJECTS
TRADITIONAL DWH
93
94
DATAWAREHOUSE
choose data management aproach
DESIGN LAYERS
SOC
95
DRAW THE DATA ARCHITECTURE
PROJECT INITIALIZATION
DEFINE NAMING CONVENTIONS
96
BRONZE
SILVER
97
GOLD
COLUMN NAMING CONVENTIONS
98
CREATE DATABASE “DATAWAREHOUSE”
99