SELECT

DISTINCT

TOP

1
2
 DDL
CREATE

ALTER

DROP

3
4
 DML
UPDATE

UPDATE

5
6
 FILTERING
WHERE

BETWEEN

IN - NOT IN

7
LIKE

8
9
COMBINIG DATA

10
JOIN ​ ​ “COLUMNS”

11
INNER

FULL

LEFT ANTI LEFT

12
FULL ANTI JOIN

CROSS JOIN

MULTIPLE ​ LEF​ ​ JOIN

13
SET ​ ​ “ROWS”

UNION
NO DUPLICATES​ ALL ROWS
SLOW

UNION ALL
DUPLICATES​​ ALL ROWS
FAST

EXCEPT
NO DUPLICATES
IDENTIFY NEW DATA IN TABLES
VERIFY THERE ARE NOT MISSING DATA IN BOTH TABLES

14
INTERSECT
COMMON ROWS IN TWO TABLES

15
16
 ROW LEVEL FUNCTIONS
STRING FUNCTION

17
NUMBER FUNCTION

​​ ​

DATE & TIME FUNCTION

SQL Full Course for Beginners (30 Hours) – From Zero to Hero

18
NULL FUNCTION

NULL -> 0

CONCATENAR + NULL

MANIPULATE ID BEFORE JOIN TABLES

19
0 -> NULL NULLIF THIS IS TO DO DIVIDE

SCORE WITH OUT NULL

20
21
CASE STATEMENT

22
23
 AGREGATION & ANALYTICAL FUNCTIONS
AGGREGATE FUNCTIONS

WINDOW

24
25
26
WINDOW AGGREGATE

DUPLICATES

27
28
WINDOW RANKING

29
ONLY THE 2 BEST

30
UNIQUE ID

CLEAN DATA WITH UNIQUE ID

31
32
WINDOW VALUE

33
34
35
36
ADVANCE SQL TECHNIQUES

37
SUBQUERIES

SUBQUERY 1 VALUE

ES LO MISMO DE ARRIBA ^

38
LEFT JOIN

39
IN

​​

ANY​ ALL​ EXIST

40
41
42
CTE

43
 ​

STANDALONE CTE

44
MULTIPLE CTES
​ ONLY A SINGLE “WITH”
​ WITHOUT A WITH IN THE MIDDLE
​ WITHOUT AT THE END WITH A : , “COMMA”

NESTED CTE
​ DEPENDENT

RECURSIVE

45
46
VIEWS

47
CREATE VIEW

DELETE VIEW

UPDATE VIEW

JOIN MANY TABLES:

48
MANAGE LANGUAGES

​ ​

MARTS

​ ​

49
CTAS TABLE & TEMP TABLE

​​

​ ​

50
CREATE

UPDATE

SNAPSHOT​ ​ HAVE DATA WITHOUT MODIFICATION

​ ​ ​ ANALYZE BUGS AND DATA ISSUES

PHYSICAL DATA MARTS IN DWH​ ​ IT RECCOMENDABLE TO USE VIEWS

TEMPORARY CTAS

​ ​

​ ​

51
​ ​ ​ ​ 3​ ​ 2​ ​ 5​ ​ ​ 4​ ​ 1

STORE PROCEDURE
databriks or snowflakes in python

52
 ​

PARAMETERS

53
VARIABLES

54
TRY CATCH

55
TRIGGERS

1. Create log table​ ​ ​

[Link] trigger on Employees table​

when insert data

56
3. Insert Data in Employees​ ​

4. Check the log​ ​ ​

​ ​ ​ ​ ​

57
58
 PERFORMANCE OPTIMIZATION
INDEXES

59
60
61
62
 ROWSTORE​ ​ COLUMNSTORE

ITS MORE FAST COLUMN STORE

63
64
​

65
66
INDEX MANAGEMENT

SQL Full Course for Beginners (30 Hours) – From Zero to Hero

EXECUTION PLAN

SQL HINTS

WITH (FORCESEEK) ​
​ ​ ​ ​ ORDENA A SQL QUE BUSQUE POR ÍNDICE Y NO POR TODA LA TABLA

67
 IN PRODUCTION MAY VARY

INDEX STRATEGY
#1

OLAP

OLTP

​ ​

68
Analyze the following SQL queries and generate a report on table and column usage statistics. For each
table, provide:

●​ The total number of times the table is used across all queries.
●​ A breakdown of each column in the table, showing:
●​ The number of times each column appears.
●​ The primary purpose of the column's usage (e.g., filtering, joining, grouping, aggregating).​
Sort the tables in descending order based on their total usage.

69
70
PARTITIONS

FUNCTION

71
72
SEE IF DATA WAS INSERTED

73
74
PERFORMANCE TIPS
1

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
BRONZE

ANALYSING:​ SOURCE SYSTEM

ANSWERS THIS QUESTIONS

CODING: DATA INGESTION

TRUNCATE
BULK (INSERT)

100
CREATE STORE PROCEDURE

EXECUTE STORE PROCEDURE

ADD COMMENTS TO QUERIES

101
VALIDATING DATA COMPLETENESS AND SCHEMAS CHECKS

DURATION TIME

DECLARE TWO VARIABLES

START

FINISH

GET COMPLETE TIME

DOCUMENT: DRAW DATA FLOW ([Link])

COMMIT CODE IN GIT REPO

102
SILVER

ANALYSING

CODING

103
EXTRA COLUMN FOR METADATA

CHECK FOR NULL OR DUPLICATES

CLEANING

UNWANTED SPACES IN TEXT

​
104
 ​

CLEANING DATA

105
searching Zero or NULL

CASE SHORT FORM

106
WHEN DATE IS GREATER THAN 8 DIGITS AND ARE ZERO

107
OTHER EXAMPLE OF TABLE

OTHER EXAMPLE

108
GOLD

IT’S MORE COMMON DRAW CONCEPTUAL AND LOGICAL MODELS

PHYSICAL MODELS ARE DRAW BY “DATABRICKS”

109
STAR SCHEMA
​ DONT NEED TO WORRY OF STORAGE
​ USE FOR POWER BI

EXPLORE BUSSINES OBJECT

110
DATA INTEGRATIONS

AVOID DO INNER JOIN​ INSTEAD​ DO LEFT JOIN

IF THERE ARE NOT DUPLICATES ​​ THE JOIN WAS DONE CORRECTLY

111
USE TWO COLUMNS TO MAKE ONE

​ ​

112
113
Dimension ​ descriptive information about an object
Fact​ ​ measure

114
No duplicates​ ​ ​ ​ ​ ​ ​ ​ ​ Normal Query

115
 TABLE

ITS IMPORTANT TO CREATE A DATA CATALOG

116
117
 Exploratory Data Analysis (EDA)

C:\Users\Ivan\Documents\SQL\EDA\datasets\csv-files

OPTIONS TO CREATE A PROJECT FROM DATA ALREADY DONE

118
DATA EXPLORATION

DIMENSION EXPLORATION

DATE EXPLORATION

119
MEASURE EXPLORATION

MAGNITUDE ANALYSIS

120
RANKING

121
 ADVANCE ANALYTICS

CHANGE OVER TIME

122
123
TWO COLUMNS

ONE COLUMN

124
CUMULATIVE ANALYSIS

USE WINDOW FUNCTIONS

HOW IS PROGRESS YOUR BUSSINES

PARTITION BY DATE (MONTH)

125
PERFORMANCE ANALYSIS
USE WINDOW FUNCTIONS

126
PART TO WHOLE
USE WINDOW FUNCTIONS

DATA SEGMENTATION
CASE WHEN STATEMENT

127
128
REPORTING

129
 RESOURCES

COURSE
SQL Full Course for Beginners (30 Hours) – From Zero to Hero

Data with Baraa

[Link]

INSTALL SSMS
How to Download & Install (FREE) Microsoft SQL Server, SSMS and 3 AdventureWorks Databases

DATA
[Link]
s

C:\Program Files\Microsoft SQL Server\[Link]\MSSQL\Backup

C:\Program Files\Microsoft SQL Server\[Link]\MSSQL\Backup

GITHUB

[Link]
[Link]
[Link]

130