FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
sql-ultimate-course/scripts/27_AI_and_SQL.sql at main · sindoc/sql-ultimate-course · GitHub
sindoc
/
sql-ultimate-course
Public
forked from
DataWithBaraa/sql-ultimate-course
Notifications
You must be signed in to change notification settings
Fork
0
Star
1
Code
Pull requests
0
Actions
Projects
Security and quality
0
Insights
Additional navigation options
Code
Pull requests
Actions
Projects
Security and quality
Insights
Expand file tree
Breadcrumbs
sql-ultimate-course
/
scripts
/
27_AI_and_SQL.sql
Copy path
More file actions
More file actions
Latest commit
History
History
History
356 lines (320 loc) · 13.3 KB
Breadcrumbs
sql-ultimate-course
/
scripts
/
27_AI_and_SQL.sql
Copy path
File metadata and controls
356 lines (320 loc) · 13.3 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
/*
==============================================================================
SQL AI Prompts for SQL
-------------------------------------------------------------------------------
This script contains a series of prompts designed to help both SQL developers
and anyone interested in learning SQL improve their skills in writing,
optimizing, and understanding SQL queries. The prompts cover a variety of
topics, including solving SQL tasks, enhancing query readability, performance
optimization, debugging, and interview/exam preparation. Each section provides
clear instructions and sample code to facilitate self-learning and practical
application in real-world scenarios.
Table of Contents:
1. Solve an SQL Task
2. Improve the Readability
3. Optimize the Performance Query
4. Optimize Execution Plan
5. Debugging
6. Explain the Result
7. Styling & Formatting
8. Documentations & Comments
9. Improve Database DDL
10. Generate Test Dataset
11. Create SQL Course
12. Understand SQL Concept
13. Comparing SQL Concepts
14. SQL Questions with Options
15. Prepare for a SQL Interview
16. Prepare for a SQL Exam
=================================================================================
*/
/*
==============================================================================
1. Solve an SQL Task
=================================================================================
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
*/
/*
==============================================================================
2. Improve the Readability
=================================================================================
The following SQL Server query 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.
*/
--
Bad Formated Query
WITH CTE_Total_Sales_By_Customer
AS
(
SELECT
c
.
CustomerID
,
c
.
FirstName
+
'
'
+
c
.
LastName
AS
FullName,
SUM
(
o
.
Sales
)
AS
TotalSales
FROM
Sales
.
Customers
c
INNER JOIN
Sales
.
Orders
o
ON
c
.
CustomerID
=
o
.
CustomerID
GROUP BY
c
.
CustomerID
,
c
.
FirstName
,
c
.
LastName
),CTE_Highest_Order_Product
AS
(
SELECT
o
.
CustomerID
,
p
.
Product
, ROW_NUMBER() OVER (PARTITION BY
o
.
CustomerID
ORDER BY
o
.
Sales
DESC
)
AS
rn
FROM
Sales
.
Orders
o
INNER JOIN
Sales
.
Products
p
ON
o
.
ProductID
=
p
.
ProductID
),
CTE_Highest_Category
AS
(
SELECT
o
.
CustomerID
,
p
.
Category
,
ROW_NUMBER() OVER (PARTITION BY
o
.
CustomerID
ORDER BY
SUM
(
o
.
Sales
)
DESC
)
AS
rn
FROM
Sales
.
Orders
o
INNER JOIN
Sales
.
Products
p
ON
o
.
ProductID
=
p
.
ProductID
GROUP BY
o
.
CustomerID
,
p
.
Category
),
CTE_Last_Order_Date
AS
(
SELECT
CustomerID,
MAX
(OrderDate)
AS
LastOrderDate
FROM
Sales
.
Orders
GROUP BY
CustomerID
),
CTE_Total_Discounts_By_Customer
AS
(
SELECT
o
.
CustomerID
,
SUM
(
o
.
Quantity
*
p
.
Price
*
0
.
1
)
AS
TotalDiscounts
FROM
Sales
.
Orders
o
INNER JOIN
Sales
.
Products
p
ON
o
.
ProductID
=
p
.
ProductID
GROUP BY
o
.
CustomerID
)
SELECT
ts
.
CustomerID
,
ts
.
FullName
,
ts
.
TotalSales
,
hop
.
Product
AS
HighestOrderProduct,
hc
.
Category
AS
HighestCategory,
lod
.
LastOrderDate
,
td
.
TotalDiscounts
FROM
CTE_Total_Sales_By_Customer ts
LEFT JOIN
(
SELECT
CustomerID, Product
FROM
CTE_Highest_Order_Product
WHERE
rn
=
1
) hop
ON
ts
.
CustomerID
=
hop
.
CustomerID
LEFT JOIN
(
SELECT
CustomerID, Category
FROM
CTE_Highest_Category
WHERE
rn
=
1
) hc
ON
ts
.
CustomerID
=
hc
.
CustomerID
LEFT JOIN
CTE_Last_Order_Date lod
ON
ts
.
CustomerID
=
lod
.
CustomerID
LEFT JOIN
CTE_Total_Discounts_By_Customer td
ON
ts
.
CustomerID
=
td
.
CustomerID
WHERE
ts
.
TotalSales
>
0
ORDER BY
ts
.
TotalSales
DESC
/*
===========================================================================
3. Optimize the Performance 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.
*/
--
Query with Bar Performance
SELECT
o
.
OrderID
,
o
.
CustomerID
,
c
.
FirstName
AS
CustomerFirstName,
(
SELECT
COUNT
(
o2
.
OrderID
)
FROM
Sales
.
Orders
o2
WHERE
o2
.
CustomerID
=
c
.
CustomerID
)
AS
OrderCount
FROM
Sales
.
Orders
o
LEFT JOIN
Sales
.
Customers
c
ON
o
.
CustomerID
=
c
.
CustomerID
WHERE
LOWER
(
o
.
OrderStatus
)
=
'
delivered
'
OR
YEAR(
o
.
OrderDate
)
=
2025
OR
o
.
CustomerID
=
1
OR
o
.
CustomerID
=
2
OR
o
.
CustomerID
=
3
OR
o
.
CustomerID
IN
(
SELECT
CustomerID
FROM
Sales
.
Customers
WHERE
Country
LIKE
'
%USA%
'
)
/*
===========================================================================
4. Optimize Execution Plan
==============================================================================
The image is the execution plan of SQL Server query.
Do the following:
- Describe the execution plan step by step.
- Identify performance bottlenecks and issues.
- Suggest ways to improve performance and optimize the execution plan.
*/
/*
===========================================================================
5. Debugging
==============================================================================
The following SQL Server Query causing this error: "Msg 8120, Level 16, State 1, Line 5"
Do the following:
- Explain the error massage.
- Find the root cause of the issue.
- Suggest how to fix it.
*/
SELECT
C
.
CustomerID
,
C
.
Country
,
SUM
(
O
.
Sales
)
AS
TotalSales,
RANK() OVER (PARTITION BY
C
.
Country
ORDER BY
O
.
Sales
DESC
)
AS
RankInCountry
FROM
Sales
.
Customers
C
LEFT JOIN
Sales
.
Orders
O
ON
C
.
CustomerID
=
O
.
CustomerID
GROUP BY
C
.
CustomerID
,
C
.
Country
/*
===========================================================================
6. Explain the Result
==============================================================================
I didn't understand the result of the following SQL Server query.
Do the following:
- Break down how SQL processes the query step by step.
- Explaining each stage and how the result is formed.
*/
WITH Series
AS
(
--
Anchor Query
SELECT
1
AS
MyNumber
UNION ALL
--
Recursive Query
SELECT
MyNumber
+
1
FROM
Series
WHERE
MyNumber
<
20
)
--
Main Query
SELECT
*
FROM
Series
/*
===========================================================================
7. Styling & Formatting
==============================================================================
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.
*/
--
Bad Styled Query
with CTE_Total_Sales
as
(
Select
CustomerID,
sum
(Sales)
as
TotalSales
from
Sales
.
Orders
group by
CustomerID),
cte_customer_segments
as
(
SELECT
CustomerID,
case when TotalSales
>
100
then
'
High Value
'
when TotalSales between
50
and
100
then
'
Medium Value
'
else
'
Low Value
'
end
as
CustomerSegment
from
CTE_Total_Sales)
select
c
.
CustomerID
,
c
.
FirstName
,
c
.
LastName
,
cts
.
TotalSales
,
ccs
.
CustomerSegment
FROM
sales
.
customers
c
left join
CTE_Total_Sales cts
ON
cts
.
CustomerID
=
c
.
CustomerID
left JOIN
cte_customer_segments ccs
ON
ccs
.
CustomerID
=
c
.
CustomerID
/*
===========================================================================
8. Documentations & Comments
==============================================================================
The following SQL Server query lacks comments and documentation.
Do the following:
Insert a leading comment at the start of the query describing its overall purpose.
Add comments 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.
*/
WITH CTE_Total_Sales
AS
(
SELECT
CustomerID,
SUM
(Sales)
AS
TotalSales
FROM
Sales
.
Orders
GROUP BY
CustomerID
),
CTE_Customer_Segements
AS
(
SELECT
CustomerID,
CASE
WHEN TotalSales
>
100
THEN
'
High Value
'
WHEN TotalSales BETWEEN
50
AND
100
THEN
'
Medium Value
'
ELSE
'
Low Value
'
END CustomerSegment
FROM
CTE_Total_Sales
)
SELECT
c
.
CustomerID
,
c
.
FirstName
,
c
.
LastName
,
cts
.
TotalSales
,
ccs
.
CustomerSegment
FROM
Sales
.
Customers
c
LEFT JOIN
CTE_Total_Sales cts
ON
cts
.
CustomerID
=
c
.
CustomerID
LEFT JOIN
CTE_Customer_Segements ccs
ON
ccs
.
CustomerID
=
c
.
CustomerID
/*
===========================================================================
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.
==============================================================================
10. Generate Test Dataset
==============================================================================
I need dataset for testing 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.
==============================================================================
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.
==============================================================================
12. Understand SQL Concept
==============================================================================
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.
==============================================================================
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.
==============================================================================
14. SQL Questions with Options
==============================================================================
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.
==============================================================================
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.
==============================================================================
16. Prepare for a SQL Exam
==============================================================================
Prepare me for a SQL exam
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.
Back
|
FazBrowse Home
|
New Git URL