Data Analysis SQL for Data Analysis

SQL
for Data Analysis
 Data Analysis Table of Contents ‫ﺟﺪول اﻟﻤﺤﺘﻮﻳﺎت‬ SQL for Data Analysis

Module 1:SQL Foundations & Data Modeling ‫ وﻧﻤﺬﺟﺔ اﻟﺒﻴﺎﻧﺎت‬SQL ‫أﺳﺎﺳﻴـــــــﺎت‬

What is SQL and why it’s important ‫ وﻟﻤﺎذا ﻧﺴﺘﺨﺪﻣﻬﺎ‬SQL ‫ﻣﺎ ﻫﻲ ﻟﻐﺔ‬

Flat files vs. relational databases ‫اﻟﻔﺮق ﺑﲔ اﻟﻤﻠﻔﺎت اﻟﻤﺴﻄﺤﺔ وﻗﻮاﻋﺪ اﻟﺒﻴﺎﻧﺎت‬
Client–Server architecture explained simply (Client–Server) ‫ﴍح ﻣﺒﺴﻂ ﻟﻬﻨﺪﺳﺔ اﻟﻌﻤﻴﻞ واﻟﺨﺎدم‬
Data modeling concepts: Entities, Attributes, Relationships (‫ اﻟﻌﻼﻗﺎت‬،‫ اﻟﺨﺼﺎﺋﺺ‬،‫ﻣﻔﺎﻫﻴﻢ ﻧﻤﺬﺟﺔ اﻟﺒﻴﺎﻧﺎت )اﻟﻜﻴﺎﻧﺎت‬
Understanding Relationships ‫ﺷـــــــــﺮح اﻟﻌﻼﻗــــــــــــﺎت‬
Entity–Relationship Diagram (ERD) basics (ERD) ‫ﺑﻨﺎء اﻟﻤﺨﻄﻂ اﻟﻌﻼﺋﻘﻲ‬
From ERD to Relational Schema (Relational Schema) ‫ﺗﺤﻮﻳﻞ اﻟﻤﺨﻄﻂ إﱃ ﻣﺨﻄﻂ ﺟﺪاول ﻓﻌﲇ‬
DDL: CREATE, ALTER, DROP ‫ ﻹﻧﺸﺎء وﺗﻌﺪﻳﻞ اﻟﺠﺪاول‬DDL ‫أواﻣﺮ‬
DML: INSERT, UPDATE, DELETE, SELECT ‫ ﻹﺿﺎﻓﺔ وﺗﺤﺪﻳﺚ واﺳﱰﺟﺎع اﻟﺒﻴﺎﻧﺎت‬DML ‫أواﻣﺮ‬
 Data Analysis Table of Contents ‫ﺟﺪول اﻟﻤﺤﺘﻮﻳﺎت‬ SQL for Data Analysis

Module 2: Filtering, Sorting & Joins ‫اﻟﻔﻠﱰة واﻟﻔﺮز واﻟﺮﺑﻂ ﺑﲔ اﻟﺠﺪاول‬

SELECT and WHERE clauses WHERE ‫ و‬SELECT ‫اﺳﺘﺨﺪام أواﻣﺮ‬

Filtering: LIKE, BETWEEN, IN, IS NULL IS NULL ‫ و‬IN ‫ و‬BETWEEN ‫ و‬LIKE ‫اﻟﻔﻠﱰة ﺑﺎﺳﺘﺨﺪام‬
Sorting results with ORDER BY ORDER BY ‫ﺗﺮﺗﻴﺐ اﻟﻨﺘﺎﺋﺞ ﺑـ‬
Applying relationships practically with ‫ﺗﻄﺒﻴﻖ اﻟﻌﻼﻗﺎت ﻓﻌﻠ ًﻴﺎ ﺑﺎﺳﺘﺨﺪام أواﻣﺮ اﻟﺮﺑﻂ‬
JOINS (INNER, LEFT, RIGHT, FULL) (INNER JOIN – LEFT JOIN – RIGHT JOIN – FULL JOIN)
Cross Join & Self Join explained SELF JOIN ‫ و‬CROSS JOIN ‫ﴍح‬

Aliasing and readability improvements (Aliases) ‫ﺗﺒﺴﻴﻂ اﻷﻛﻮاد ﺑﺎﺳﺘﺨﺪام اﻷﺳﻤﺎء اﻟﻤﺴﺘﻌﺎرة‬
 Data Analysis Table of Contents ‫ﺟﺪول اﻟﻤﺤﺘﻮﻳﺎت‬ SQL for Data Analysis

Module 3: Aggregation, Grouping & Set Operations ‫اﻟﻔﻠﱰة واﻟﻔﺮز واﻟﺮﺑﻂ ﺑﲔ اﻟﺠﺪاول‬

Aggregation with COUNT, SUM, AVG, MIN, MAX AVG ‫ و‬SUM ‫ و‬COUNT ‫اﺳﺘﺨﺪام دوال اﻟﺘﺤﻠﻴﻞ ﻣﺜﻞ‬
Grouping data with GROUP BY and HAVING AVG ‫ و‬SUM ‫ و‬COUNT ‫اﺳﺘﺨﺪام دوال اﻟﺘﺤﻠﻴﻞ ﻣﺜﻞ‬
Writing subqueries for deeper analysis ‫ﻛﺘﺎﺑﺔ اﺳﺘﻌﻼﻣﺎت ﻓﺮﻋﻴﺔ ﻟﻠﺤﺼﻮل ﻋﲆ ﺗﺤﻠﻴﻼت أﻋﻤﻖ‬
Using CASE WHEN for conditional logic ‫ ﻟﺘﺼﻨﻴﻒ اﻟﺒﻴﺎﻧﺎت أو ﺑﻨﺎء ﴍوط ﺗﺤﻠﻴﻠﻴﺔ‬CASE WHEN ‫اﺳﺘﺨﺪام‬
Combining datasets with UNION, ‫دﻣﺞ ﻧﺘﺎﺋﺞ ﻣﺘﻌﺪدة ﺑﺎﺳﺘﺨﺪام‬
INTERSECT, and EXCEPT EXCEPT ‫ و‬INTERSECT ‫ و‬UNION
 Data Analysis Table of Contents ‫ﺟﺪول اﻟﻤﺤﺘﻮﻳﺎت‬ SQL for Data Analysis

Module 4: Final Project – SQL for Data Analysis ‫اﻟﻤﴩوع اﻟﺘﻄﺒﻴﻘﻲ ﻟﺘﺤﻠﻴﻞ اﻟﺒﻴﺎﻧﺎت‬

Building a complete database for a retail business ‫ﺗﻨﻔﻴﺬ ﻣﴩوع ﺗﺤﻠﻴﻞ ﻣﺒﻴﻌﺎت ﻣﺘﻜﺎﻣﻞ‬
Writing analytical queries to calculate KPIs (KPIs) ‫إﻧﺸﺎء ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﻛﺎﻣﻠﺔ وﺗﺤﻠﻴﻞ ﻣﺆﴍات اﻷداء‬
Identifying top products, customers, and regions ً
‫ﻣﺒﻴﻌﺎ وأﻓﻀﻞ اﻟﻌﻤﻼء واﻟﻤﻨﺎﻃﻖ‬ ‫اﺳﺘﺨﺮاج أﻋﲆ اﻟﻤﻨﺘﺠﺎت‬
Using subqueries and aggregations in combination ‫دﻣﺞ اﻻﺳﺘﻌﻼﻣﺎت واﻟﺘﺤﻠﻴﻼت ﻟﻠﻮﺻﻮل ﻟﺘﻘﺎرﻳﺮ ﻣﻔﺼﻠﺔ‬

Exporting data for visualization in Power BI or Excel Excel ‫ أو‬Power BI ‫ﺗﺼﺪﻳﺮ اﻟﻨﺘﺎﺋﺞ ورﺑﻄﻬﺎ ﺑﺄدوات ﻋﺮض اﻟﺒﻴﺎﻧﺎت ﻣﺜﻞ‬

Final review and quiz ‫ﻣﺮاﺟﻌﺔ ﺷﺎﻣﻠﺔ واﺧﺘﺒﺎر ﺧﺘﺎﻣﻲ ﺑﺴﻴﻂ‬

‫‪Data Analysis‬‬ ‫‪Module 1 - Beginner Level‬‬

‫‪Module 1‬‬
‫& ‪SQL Foundations‬‬
‫‪Data Modeling‬‬
‫اﻟﻤﺴﺘﻮى اﻷول‪:‬‬

‫أﺳﺎﺳﻴــــــــــــــــــــﺎت ‪SQL‬‬
‫‪01‬‬ ‫وﻧﻤــﺬﺟــﺔ اﻟﺒﻴـــــــــــــﺎﻧﺎت‬
 Data Analysis Module 1 - Beginner Level

What is SQL? ‫؟‬SQL ‫ﻣـــــﺎ ﻫـﻲ ﻟﻐﺔ‬

SQL means Structured Query Language. "‫ ﻫﻲ اﺧﺘﺼﺎر ﻟـ "ﻟﻐﺔ اﻻﺳﺘﻌﻼم اﻟﻤﻬﻴﻜﻠﺔ‬SQL
It is the language we use to talk to a database. ‫وﻫﻲ اﻟﻠﻐﺔ اﻟﲇ ﻧﺴﺘﺨﺪﻣﻬﺎ ﻟﻠﺘﻌﺎﻣﻞ ﻣﻊ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت‬
With SQL, we can create tables, add data, update ‫ ﻧﻀﻴﻒ‬،‫ﻧﻘﺪر ﺑﻴﻬﺎ ﻧﻨﺊﺸ ﺟﺪاول‬
data, and get information from the database. .‫ أو ﻧﺴﱰﺟﻊ ﻣﻨﻬﺎ ﻣﻌﻠﻮﻣﺎت‬،‫ ﻧﺤﺪﺛﻬﺎ‬،‫ﺑﻴﺎﻧﺎت‬

Why do we use SQL? ‫؟‬SQL ‫ﻟﻤﺎذا ﻧﺴﺘﺨﺪم‬

To store a lot of data in one place ‫ﻟﺘﺨﺰﻳﻦ ﻛﻤﻴﺎت ﻛﺒﲑة ﻣﻦ اﻟﺒﻴﺎﻧﺎت ﻓﻲ ﻣﻜﺎن ﻣﻨﻈﻢ‬
To find and analyze information easily ‫ﻻﺳﺘﺨﺮاج وﺗﺤﻠﻴﻞ اﻟﻤﻌﻠﻮﻣﺎت ﺑﺴﻬﻮﻟﺔ‬
To keep data safe and correct ‫ﻟﻠﺤﻔﺎظ ﻋﲆ دﻗﺔ وأﻣﺎن اﻟﺒﻴﺎﻧﺎت‬
 Data Analysis Module 1 - Beginner Level

Example ‫ﻣﺜﺎل ﻋﻤﲇ‬

This command shows all the data from ‫ﻳﻌﺮض ﻫﺬا اﻷﻣﺮ ﻛﻞ اﻟﺒﻴﺎﻧﺎت ﻣﻦ‬
the table called “employees.” .“‫ﺟﺪول اﺳﻤﻪ ”اﻟﻤﻮﻇﻔﲔ‬

SELECT * FROM employees;

Relational Databases vs. ‫ﻗﻮاﻋﺪ اﻟﺒﻴﺎﻧﺎت ﻣﻘﺎﺑﻞ اﻷﻧﻈﻤﺔ‬

File-Based Systems ‫اﻟﻤﻌﺘﻤﺪة ﻋﲆ اﻟﻤﻠﻔﺎت‬

A file system (like Excel) keeps data in one file. (Excel ‫اﻷﻧﻈﻤﺔ اﻟﲇ ﺑﺘﻌﺘﻤﺪ ﻋﲆ اﻟﻤﻠﻔﺎت )زي‬
‫ر‬.‫ﺑﺘﺨﺰن اﻟﺒﻴﺎﻧﺎت ﻓﻲ ﻣﻠﻒ واﺣﺪ‬

A database keeps data in many tables. ،‫أﻣﺎ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت ﻓﺒﺘﺨﺰﻧﻬﺎ ﻓﻲ ﺟﺪاول ﻣﺘﻌﺪدة‬

These tables are connected with ‫واﻟﺠﺪاول دي ﺑﺘﻜﻮن ﻣﱰاﺑﻄﺔ ﺑﺒﻌﺾ‬

each other by keys (IDs). .(IDs) ‫ﻋﻦ ﻃﺮﻳﻖ اﻟﻤﻔﺎﺗﻴﺢ‬
‫‪Data Analysis‬‬ ‫‪Module 1 - Beginner Level‬‬

‫‪Comparison‬‬ ‫ﻣﻘﺎرﻧﺔ ﻣﺒﺴﻄﺔ‬

‫‪Feature‬‬ ‫‪File System‬‬ ‫‪Database‬‬ ‫‪Explanation‬‬

‫اﻟﺨﺎﺻﻴﺔ‬ ‫ﻧﻈﺎم اﻟﻤﻠﻔﺎت‬ ‫ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت‬ ‫اﻟﺘﻮﺿﻴﺢ‬

‫‪Structure‬‬ ‫ﻓﻲ اﻟﻤﻠﻔﺎت اﻟﺒﻴﺎﻧﺎت ﻛﻠﻬﺎ ﻓﻲ ﻣﻜﺎن واﺣﺪ‪ ،‬أﻣﺎ ﻓﻲ‬

‫اﻟﻬﻴﻜﻞ‬ ‫‪One file only‬‬ ‫‪Many linked tables‬‬ ‫ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت ﻓﻜﻞ ﻧﻮع ﻓﻲ ﺟﺪول ﺧﺎص‬

‫?‪Data Repeated‬‬ ‫ﻓﻲ اﻟﻤﻠﻔﺎت اﻟﺒﻴﺎﻧﺎت ﺑﺘﺘﻜﺮر‪ ،‬ﻓﻲ اﻟـ‪ DB‬ﻛﻞ ﻣﻌﻠﻮﻣﺔ‬
‫‪Yes‬‬ ‫‪No‬‬ ‫ﺑﺘﺘﺴﺠﻞ ﻣﺮة واﺣﺪة‬
‫ﺗﻜﺮار اﻟﺒﻴﺎﻧﺎت؟‬

‫?‪Easy to Search‬‬
‫‪Not easy‬‬ ‫‪Very easy‬‬ ‫اﻟﺒﺤﺚ ﻓﻲ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت أﴎع وأﺳﻬﻞ‬
‫ﺳﻬﻮﻟﺔ اﻟﺒﺤﺚ؟‬
‫?‪Secure‬‬
‫‪Not Much‬‬ ‫‪Very secure‬‬ ‫ً‬
‫أﻣﺎﻧﺎ‬ ‫ﻗﻮاﻋﺪ اﻟﺒﻴﺎﻧﺎت أﻛﱶ‬
‫اﻷﻣـــــﺎن‬
 Data Analysis Module 1 - Beginner Level

Example ‫ﻣﺜﺎل واﻗﻌﻲ‬

In Excel, we can have one file for ‫ ﻣﻤﻜﻦ ﻳﻜﻮن ﻋﻨﺪﻧﺎ ﻣﻠﻒ ﻟﻠﻄﻼب‬Excel ‫ﻓﻲ‬
students and another file for courses. ‫وﻣﻠﻒ ﺗﺎﻧﻲ ﻟﻠﻤﻘﺮرات‬
In SQL, we have two tables — students and courses ‫ ﺑﻴﻜﻮن ﻋﻨﺪﻧﺎ ﺟﺪوﻟﲔ‬SQL ‫ﻟﻜﻦ ﻓﻲ‬
— connected by the student ID. .(Student ID) ‫ ﻣﻘﺮرات( ﻣﺮﺗﺒﻄﲔ ﺑﻤﻌ ّﺮف اﻟﻄﺎﻟﺐ‬+ ‫)ﻃﻼب‬

Client–Server Architecture ‫ﺑﻨﻴﺔ اﻟﻌﻤﻴﻞ واﻟﺨﺎدم‬

MySQL works with two parts:

The Server: it keeps and manages the data.
The Client: it sends commands and shows results
(like MySQL Workbench). :‫ ﺑﻴﺘﻜﻮن ﻣﻦ ﺟﺰﺋﲔ‬MySQL ‫ﻧﻈﺎم‬
.‫ ﻫﻮ اﻟﲇ ﺑﻴﺨﺰن وﻳﺪﻳﺮ اﻟﺒﻴﺎﻧﺎت‬:(Server) ‫اﻟﺨﺎدم‬
.(MySQL Workbench ‫ ﻫﻮ اﻟﱪﻧﺎﻣﺞ اﻟﲇ ﺑﻨﻜﺘﺐ ﻓﻴﻪ اﻷواﻣﺮ )زي‬:(Client) ‫اﻟﻌﻤﻴﻞ‬
 Data Analysis Module 1 - Beginner Level

Why this is important ‫ﻟﻴـــــﻪ ده ﻣﻬﻢ؟‬

This system lets many users work together, keeps ‫اﻟﻬﻴﻜﻞ ده ﺑﻴﺴﻤﺢ ﻷﻛﱶ ﻣﻦ ﻣﺴﺘﺨﺪم ﻳﺸﺘﻐﻞ ﻓﻲ ﻧﻔﺲ‬
data safe, and allows remote access. ‫ وﻛﻤﺎن ﻳﺴﻤﺢ‬،‫ وﺑﻴﺤﺎﻓﻆ ﻋﲆ أﻣﺎن اﻟﺒﻴﺎﻧﺎت‬،‫اﻟﻮﻗﺖ‬
.‫ﺑﺎﻟﻮﺻﻮل إﻟﻴﻬﺎ ﻣﻦ أي ﻣﻜﺎن‬

Practice ‫ﺗﻄﺒﻴﻖ ﻋﻤﻠــــﻲ‬

Open MySQL Workbench MySQL Workbench ‫اﻓﺘﺢ‬
Connect to your server (Server) ‫اﺗﺼﻞ ﺑﺎﻟﺨﺎدم‬
Type and run these ‫ﻧﻔﺬ اﻷواﻣﺮ دي ﻹﻧﺸﺎء‬ ّ
commands: .‫ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﺟﺪﻳﺪة‬
CREATE DATABASE company_db;
SHOW DATABASES;
 Data Analysis Data Modeling Concepts (Entities, Attributes, Relationships) Module 1 - Beginner Level

(‫ واﻟﻌﻼﻗﺎت‬،‫ اﻟﺨﺼﺎﺋﺺ‬،‫ﻣﻔﺎﻫﻴﻢ ﻧﻤﺬﺟﺔ اﻟﺒﻴﺎﻧﺎت )اﻟﻜﻴﺎﻧﺎت‬

Data Modeling means planning how the data will be ‫ﻧﻤﺬﺟﺔ اﻟﺒﻴﺎﻧﺎت ﻣﻌﻨﺎﻫﺎ إﻧﻨﺎ ﺑﻨﺨﻄﻂ إزاي ﻫﻨﺨﺰن اﻟﺒﻴﺎﻧﺎت‬
stored in the database. .‫داﺧﻞ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت‬
We decide what tables we need, what data goes in ‫ وإﻳﻪ اﻷﻋﻤﺪة اﻟﲇ‬،‫ﺑﻨﺤﺪد اﻟﺠﺪاول اﻟﲇ ﻫﻨﺤﺘﺎﺟﻬﺎ‬
each table, and how these tables are connected. .‫ وإزاي ﻫﻨﻌﻤﻞ اﻟﻌﻼﻗﺎت ﺑﻴﻨﻬﻢ‬،‫ﻫﺘﻜﻮن ﻓﻴﻬﺎ‬

Key Concepts ‫اﻟﻤﻔﺎﻫﻴﻢ اﻷﺳﺎﺳﻴﺔ‬

An entity is a real thing or object that we want to ‫اﻟﻜﻴــــــﺎن ﻫــﻮ اﻟﴚء اﻟﺤﻘﻴﻘــــــــﻲ اﻟﲇ‬
store data about. .‫ﺑﻨﺠﻤـــــــــــــﻊ ﻋﻨﻪ ﺑﻴﺎﻧﺎت‬

Example: Student, Teacher, Course, Product .‫ ﻣﻨﺘﺞ‬،‫ ﻣﺎدة دراﺳﻴﺔ‬،‫ ﻣﺪرس‬،‫ ﻃﺎﻟﺐ‬:‫ﻣﺜﺎل‬
 Data Analysis Module 1 - Beginner Level

Attribute ‫اﻟﺨـــــــــــــﺎﺻﻴﺔ‬
An attribute is a detail or property of an entity. .‫اﻟﺨﺎﺻﻴﺔ ﻫﻲ اﻟﻤﻌﻠﻮﻣﺔ أو اﻟﺘﻔﺼﻴﻠﺔ اﻟﲇ ﺑﺘﻮﺻﻒ اﻟﻜﻴﺎن‬
Example: For a Student name, age, email, city. .‫ اﻟﻤﺪﻳﻨﺔ‬،‫ اﻟﱪﻳﺪ اﻹﻟﻜﱰوﻧﻲ‬،‫ اﻟﻦﺴ‬،‫اﻻﺳﻢ‬ ً
‫ ﻟﻠﻄﺎﻟﺐ‬:‫ﻣﺜﻼ‬

Relationship ‫اﻟﻌــــــــــﻼﻗﺔ‬
A relationship connects two or more entities, It shows ،‫اﻟﻌﻼﻗﺔ ﻫﻲ اﻟﺮاﺑﻂ اﻟﲇ ﺑﻴﺼﻞ ﺑﲔ ﻛﻴﺎﻧﲔ أو أﻛﱶ‬
how data in one table is linked to data in another. ّ
‫وﺑﻴﻮﺿﺢ إزاي اﻟﺒﻴﺎﻧﺎت ﻓﻲ ﺟﺪول ﻣﻌﲔ ﻣﺮﺗﺒﻄﺔ ﺑﺒﻴﺎﻧﺎت ﻓﻲ‬
.‫ﺟﺪول ﺗﺎﻧﻲ‬

Example: :‫ﻣﺜـــــﺎل‬

A Student enrolls in a Course. .‫اﻟﻄﺎﻟﺐ ﻳﺴﺠﻞ ﻓﻲ ﻣﺎدة دراﺳﻴﺔ‬

A Customer makes an Order. .‫اﻟﻌﻤﻴﻞ ﻳﻘﻮم ﺑـﻄﻠﺐ ﴍاء‬
 Data Analysis Module 1 - Beginner Level

Types of Relationships ‫أﻧـــــﻮاع اﻟﻌﻼﻗﺎت‬

Type Example ‫اﻟﺘﻮﺿﻴﺢ‬

One-to-One (1:1) One person One ID card ‫ﻛﻞ ﺷﺨﺺ ﻟﻪ ﺑﻄﺎﻗﺔ واﺣﺪة ﻓﻘﻂ‬

One-to-Many (1:M) One teacher Many students ‫اﻟﻤﺪرس ﻳﺪرس ﻟﻌﺪة ﻃﻼب‬

‫اﻟﻄﺎﻟﺐ ﻳﺴﺠﻞ ﻓﻲ أﻛﱶ ﻣﻦ ﻣﺎدة‬

Many-to-Many (M:N) Students Courses ‫واﻟﻤﺎدة ﻓﻴﻬﺎ أﻛﱶ ﻣﻦ ﻃﺎﻟﺐ‬

We use these relationships to design how ‫ﺑﻨﺴﺘﺨﺪم اﻷﻧﻮاع دي ﻋﻠﺸﺎن ﻧﺼﻤﻢ اﻟﻌﻼﻗﺎت ﺑﲔ‬
our tables will connect. .‫اﻟﺠﺪاول ﺑﻄﺮﻳﻘﺔ ﺻﺤﻴﺤﺔ‬
Data Analysis How Relationships Appear in a Database Module 1 - Beginner Level

‫ﻛﻴﻒ ﺗﻈﻬﺮ اﻟﻌﻼﻗﺎت داﺧﻞ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت‬

In SQL, relationships are created using keys: :‫ اﻟﻌﻼﻗﺎت ﺑﺘﺘﻨﻔﺬ ﺑﺎﺳﺘﺨﺪام اﻟﻤﻔﺎﺗﻴﺢ‬،SQL ‫ﻓﻲ‬

Primary Key: unique for each record (like student_id)

.‫ رﻗﻢ ﻓﺮﻳﺪ ﻟﻜﻞ ﺳﺠﻞ داﺧﻞ اﻟﺠﺪول‬:(Primary Key) ‫اﻟﻤﻔﺘﺎح اﻷﺳﺎﳼ‬

Foreign Key: connects one table to another (like student_id inside enrollments)
.(‫ ﻋﻤﻮد ﺑﲑﺑﻂ ﺟﺪول ﺑﺂﺧﺮ )زي رﻗﻢ اﻟﻄﺎﻟﺐ داﺧﻞ ﺟﺪول اﻟﺘﺴﺠﻴﻼت‬:(Foreign Key) ‫اﻟﻤﻔﺘﺎح اﻷﺟﻨﱯ‬
 Data Analysis Module 1 - Beginner Level

Example ‫ﻣﺜــــــــــﺎل ﻋﻤﲇ‬

Let’s model a small school database. ‫ﻧﺼﻤﻢ ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﺑﺴﻴﻄﺔ ﻟﻤﺪرﺳﺔ‬

We have two entities: :‫ﻟﺪﻳﻨﺎ ﻛﻴﺎﻧﺎن رﺋﻴﺴﻴﺎن‬

Table Students (student_id, name, age) ‫ﺟـــــﺪول اﻟﻄﻼب‬
Table Courses (course_id, title, teacher) ‫ﺟـــــﺪول اﻟﻤﻘﺮرات‬

Each student can join many courses so we add table Enrollments to link them
‫ﻓﺒﻨﻀﻴﻒ ﺟﺪول اﻟﺘﺴﺠﻴﻼت ﻋﻠﺸﺎن ﻳﺮﺑﻄﻬﻢ ﺑﺒﻌﺾ‬ ‫ﻛﻞ ﻃﺎﻟﺐ ﻣﻤﻜﻦ ﻳﺴﺠﻞ ﻓﻲ أﻛﱶ ﻣﻦ ﻣﻘﺮر‬
Data Analysis Module 1 - Beginner Level

CREATE TABLE students (

student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

CREATE TABLE courses (

course_id INT PRIMARY KEY,
title VARCHAR(50),
teacher VARCHAR(50) SQL Example
);

CREATE TABLE enrollments (

SQL ‫ﻣﺜــــــــــﺎل‬
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Data Analysis One-to-One (1:1) Module 1 - Beginner Level

One record in a table matches only one record in ‫ﻛﻞ ﺳﺠﻞ ﻓﻲ ﺟﺪول ﻣﺮﺗﺒﻂ ﺑﺴﺠﻞ واﺣﺪ ﻓﻘﻂ ﻓﻲ‬
another table. .‫ﺟﺪول آﺧﺮ‬
Example: Each person has one national ID. .‫ ﻛﻞ ﺷﺨﺺ ﻟﻪ ﺑﻄﺎﻗﺔ ﻫﻮﻳﺔ واﺣﺪة ﻓﻘﻂ‬:‫ﻣﺜﺎل‬
One-to-Many (1:M)
One record in a table can be linked to many records in ‫ﺳﺠﻞ واﺣﺪ ﻓﻲ ﺟﺪول ﻣﻤﻜﻦ ﻳﺮﺗﺒﻂ ﺑﻌﺪة ﺳﺠﻼت ﻓﻲ‬
another table. .‫ﺟﺪول آﺧﺮ‬
Example: One teacher teaches many students. .‫ ﻣﺪرس واﺣﺪ ﻳﺪ ّرس ﻟﻌﺪة ﻃﻼب‬:‫ﻣﺜﺎل‬

CREATE TABLE teachers (

teacher_id INT PRIMARY KEY,
name VARCHAR(50)
TEACHER (teacher_id, name)
);
STUDENTS (student_id, name, teacher_id)
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
Data Analysis Many-to-Many (M:N) Module 1 - Beginner Level

Many records in one table are linked to many records ‫ﺳﺠﻼت ﻣﺘﻌﺪدة ﻓﻲ ﺟﺪول ﻣﻤﻜﻦ ﺗﻜﻮن ﻣﺮﺗﺒﻄﺔ‬
in another table. .‫ﺑﺴﺠﻼت ﻣﺘﻌﺪدة ﻓﻲ ﺟﺪول آﺧﺮ‬
Example: Students can take many courses, and each ‫ واﻟﻤﺎدة‬،‫ اﻟﻄﺎﻟﺐ ﻣﻤﻜﻦ ﻳﺴﺠﻞ ﻓﻲ أﻛﱶ ﻣﻦ ﻣﺎدة‬:‫ﻣﺜﺎل‬
course has many students. .‫ﻓﻴﻬﺎ أﻛﱶ ﻣﻦ ﻃﺎﻟﺐ‬
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE courses ( STUDENTS (student_id, name)
course_id INT PRIMARY KEY, COURSES (course_id, title)
title VARCHAR(50) ENROLLMENTS (enrollment_id, student_id, course_id)
);
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Data Analysis Many-to-Many (M:N) Module 1 - Beginner Level

SQL
Type Real-Life Example ‫اﻟﺘﻮﺿﻴﺢ‬
Implementation
One-to-One Foreign Key in ‫ﻋﻼﻗﺔ ﻓﺮدﻳﺔ‬
Person ID Card one table

One-to-Many Foreign Key in the ‫ﻋﻼﻗﺔ ﻓﺮد ﻟﻜﺜﲑ‬

Teacher Students “many” table

Many-to-Many Students Courses Use link table ‫ﻋﻼﻗﺔ ﻛﺜﲑ ﻟﻜﺜﲑ‬

 Data Analysis Module 1 - Beginner Level

Quick Tips ‫ﻧﺼـــﺎﺋﺢ ﴎﻳﻌﺔ‬

Use Foreign Keys to make relationships between .‫( ﻟﺮﺑﻂ اﻟﺠﺪاول‬Foreign Keys) ‫اﺳﺘﺨﺪم اﻟﻤﻔﺎﺗﻴﺢ اﻷﺟﻨﺒﻴﺔ‬
tables.
If it’s Many-to-Many create a middle table (bridge). .‫ﻓﻲ اﻟﻌﻼﻗﺎت ”ﻛﺜﲑ إﱃ ﻛﺜﲑ“ ﻻزم ﻧﺴﺘﺨﺪم ﺟﺪول وﺳﻴﻂ‬

Draw ERD first before writing SQL code. .‫ ﻗﺒﻞ ﻛﺘﺎﺑﺔ اﻷﻛﻮاد‬ERD ‫ارﺳﻢ اﻟﻌﻼﻗﺎت ﻋﲆ‬

Building the ERD (Entity Relationship Diagram)

(ERD) ‫ﺑﻨﺎء اﻟﻤﺨﻄﻂ اﻟﻌﻼﺋﻘﻲ ﺑﲔ اﻟﻜﻴﺎﻧﺎت‬
What is an ERD? ‫؟‬ERD ‫ﻣﺎ ﻫــــــــﻮ‬
An ERD (Entity Relationship Diagram) is a drawing ‫( ﻫﻮ رﺳﻢ ﺑﻴﴩح‬ERD) ‫ﻣﺨﻄﻂ اﻟﻌﻼﻗﺎت ﺑﲔ اﻟﻜﻴﺎﻧﺎت‬
that shows how data is connected in a database. .‫إزاي اﻟﺒﻴﺎﻧﺎت ﻣﱰاﺑﻄﺔ داﺧﻞ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت‬
It helps us design the database before we ‫وﺑﻴﺴﺎﻋﺪﻧﺎ ﻧﺨﻄﻂ ﻟﺘﺼﻤﻴﻢ اﻟﺠﺪاول واﻟﻌﻼﻗﺎت ﻗﺒﻞ ﻣﺎ‬
start writing SQL code. .‫ﻧﺒﺪأ ﻛﺘﺎﺑﺔ اﻷﻛﻮاد‬
 Data Analysis Module 1 - Beginner Level

Why We Use ERDs ‫؟‬ERD ‫ﻟﻤﺎذا ﻧﺴﺘﺨﺪم‬

To see how tables connect to each other ‫ﻋﻠﺸﺎن ﻧﺸﻮف ﺷﻜﻞ اﻟﻌﻼﻗﺎت ﺑﲔ اﻟﺠﺪاول‬
To plan data structure before coding ‫ﻋﻠﺸﺎن ﻧﺨﻄﻂ ﻟﱰﻛﻴﺐ اﻟﺒﻴﺎﻧﺎت ﻗﺒﻞ اﻟﺘﻨﻔﻴﺬ‬
To avoid mistakes and repeating data ‫ﻋﻠﺸﺎن ﻧﺘﻔﺎدى اﻟﺘﻜﺮار واﻷﺧﻄﺎء ﻓﻲ اﻟﺘﺼﻤﻴﻢ‬

Main ERD Components ‫اﻟﻤﻜﻮﻧﺎت اﻷﺳﺎﺳﻴﺔ ﻟﻠﻤﺨﻄﻂ‬

Symbol Name Meaning ‫اﻟﺘﻮﺿﻴﺢ‬

Rectangle Entity Represents a table ‫ﻳﻤﺜﻞ اﻟﺠﺪول أو اﻟﻜﻴﺎن‬

Oval Attribute Represents a field (column) ‫ﻳﻤﺜﻞ ﺧﺎﺻﻴﺔ أو ﻋﻤﻮد‬

Diamond Relationship Shows how entities connect ‫ﻳﻮﺿﺢ اﻟﻌﻼﻗﺔ ﺑﲔ اﻟﻜﻴﺎﻧﺎت‬

١ or ∞ Cardinality Type of relationship (‫ ﻣﺘﻌﺪد‬/ ‫ﻧﻮع اﻟﻌﻼﻗﺔ )واﺣﺪ‬

 Data Analysis Module 1 - Beginner Level

Why We Use ERDs ‫؟‬ERD ‫ﻟﻤﺎذا ﻧﺴﺘﺨﺪم‬

To see how tables connect to each other ‫ﻋﻠﺸﺎن ﻧﺸﻮف ﺷﻜﻞ اﻟﻌﻼﻗﺎت ﺑﲔ اﻟﺠﺪاول‬
To plan data structure before coding ‫ﻋﻠﺸﺎن ﻧﺨﻄﻂ ﻟﱰﻛﻴﺐ اﻟﺒﻴﺎﻧﺎت ﻗﺒﻞ اﻟﺘﻨﻔﻴﺬ‬
To avoid mistakes and repeating data ‫ﻋﻠﺸﺎن ﻧﺘﻔﺎدى اﻟﺘﻜﺮار واﻷﺧﻄﺎء ﻓﻲ اﻟﺘﺼﻤﻴﻢ‬

Main ERD Components ‫اﻟﻤﻜﻮﻧﺎت اﻷﺳﺎﺳﻴﺔ ﻟﻠﻤﺨﻄﻂ‬

Symbol Name Meaning ‫اﻟﺘﻮﺿﻴﺢ‬

Rectangle Entity Represents a table ‫ﻳﻤﺜﻞ اﻟﺠﺪول أو اﻟﻜﻴﺎن‬

Oval Attribute Represents a field (column) ‫ﻳﻤﺜﻞ ﺧﺎﺻﻴﺔ أو ﻋﻤﻮد‬

Diamond Relationship Shows how entities connect ‫ﻳﻮﺿﺢ اﻟﻌﻼﻗﺔ ﺑﲔ اﻟﻜﻴﺎﻧﺎت‬

١ or ∞ Cardinality Type of relationship (‫ ﻣﺘﻌﺪد‬/ ‫ﻧﻮع اﻟﻌﻼﻗﺔ )واﺣﺪ‬

 Data Analysis Module 1 - Beginner Level
Example 1 – One-to-Many Relationship

A teacher can teach many students. ،‫اﻟﻤﺪرس اﻟﻮاﺣﺪ ﻣﻤﻜﻦ ﻳﺪ ّرس ﻟﻌﺪد ﻛﺒﲑ ﻣﻦ اﻟﻄﻼب‬
Each student has one teacher. .‫وﻛﻞ ﻃﺎﻟﺐ ﻳﺨﺺ ﻣﺪرس واﺣﺪ ﻓﻘﻂ‬

CREATE TABLE teachers (

teacher_id INT PRIMARY KEY,
name VARCHAR(50)
);

CREATE TABLE students (

student_id INT PRIMARY KEY,
name VARCHAR(50),
teacher_id INT,
FOREIGN KEY (teacher_id)
REFERENCES teachers(teacher_id)
);
 Data Analysis Module 1 - Beginner Level
Many-to-Many Relationship

Students can take many courses, and each course ‫ وﻛﻞ ﻣﻘﺮر ﻓﻴﻪ‬،‫اﻟﻄﻼب ﻣﻤﻜﻦ ﻳﺴﺠﻠﻮا ﻓﻲ أﻛﱰ ﻣﻦ ﻣﻘﺮر‬
can have many students. .‫ﻣﺠﻤﻮﻋﺔ ﻣﻦ اﻟﻄﻼب‬
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(50)
);
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
 Data Analysis Module 1 - Beginner Level
One-to-One Relationship

Each employee has one ID card. ‫ﻛﻞ ﻣﻮﻇﻒ ﻟﻪ ﺑﻄﺎﻗﺔ‬

.‫ﺗﻌﺮﻳﻒ واﺣﺪة ﻓﻘﻂ‬

Many-to-Many (M:N)
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE id_cards (
card_id INT PRIMARY KEY,
issue_date DATE,
emp_id INT UNIQUE,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
Data Analysis ‫ﻣﻠﺨﺺ ﺑﴫي ﻟﻠﻌﻼﻗﺎت‬ Module 1 - Beginner Level

Visual Summary

Relationship Type Symbol Example

One-to-One 1 —— 1 Employee ID Card

One-to-Many 1 —— ∞ Teacher Students

Many-to-Many ∞ —— ∞ Students Courses

Data Analysis ‫ﺗﻤﺮﻳﻦ ﻋﻤـــــــــﲇ‬ Module 1 - Beginner Level

Exercise
Create an ERD for a small company system with:
Departments
Employees
Projects
ّ
:‫ﺻﻤﻢ ﻣﺨﻄﻂ ﻋﻼﻗﺎت ﻟﴩﻛﺔ ﺻﻐﲑة ﺗﺤﺘﻮي ﻋﲆ‬
‫اﻷﻗﺴﺎم‬
‫اﻟﻤﻮﻇﻔﲔ‬
‫اﻟﻤﺸﺎرﻳﻊ‬

Each department has many employees (1:M)

Each employee can work on many projects (M:N)

 Data Analysis What is a Relational Schema? Module 1 - Beginner Level

‫ﻣــــــــــــــــــــــﺎ ﻫﻮ اﻟﻤﺨﻄﻂ اﻟﺠﺪوﱄ؟‬

A Relational Schema is the technical version of your ERD.

It shows the tables, columns, and relationships in SQL format.
.(ERD) ‫( ﻫﻮ اﻟﻨﺴﺨﺔ اﻟﱪﻣﺠﻴﺔ ﻣﻦ اﻟﻤﺨﻄﻂ اﻟﻌﻼﺋﻘﻲ‬Relational Schema) ‫اﻟﻤﺨﻄﻂ اﻟﺠﺪوﱄ‬
ّ
.‫ ﻓﻌﻠﻴﺔ‬SQL ‫ﺑﻨﺤﻮل اﻟﺮﺳﻢ اﻟﲇ ﻓﻴﻪ ﻛﻴﺎﻧﺎت وﻋﻼﻗﺎت إﱃ ﺟﺪاول وأﻛﻮاد‬ ‫ﻳﻌﲏ‬

Why We Need It | ‫ﻟﻴـــــــﻪ ﺑﻨﺤﺘــــﺎﺟﻪ؟‬

To create real tables in the database.
To link tables using keys (Primary / Foreign).
To make sure the design works correctly in SQL.
.‫ﻋﻠﺸﺎن ﻧﻘﺪر ﻧﻨﺊﺸ اﻟﺠﺪاول ﻓﻌﻠ ًﻴﺎ داﺧﻞ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت‬
.‫ﻋﻠﺸﺎن ﻧﺮﺑﻂ اﻟﺠﺪاول ﺑﺎﺳﺘﺨﺪام اﻟﻤﻔﺎﺗﻴﺢ اﻷﺳﺎﺳﻴﺔ واﻷﺟﻨﺒﻴﺔ‬
ً
.SQL ‫ﻓﻌﻼ داﺧﻞ‬ ‫ﻋﻠﺸﺎن ﻧﺘﺄﻛﺪ إن اﻟﺘﺼﻤﻴﻢ اﻟﲇ رﺳﻤﻨﺎه ﺷﻐﺎل‬
Data Analysis Steps to Convert ERD to Relational Schema Module 1 - Beginner Level

‫ﺧـــﻄـــــــــــــــﻮات اﻟﺘــﺤـــﻮﻳـــــــــﻞ‬

Step Description ‫اﻟﻤﺜــــــــــﺎل‬

1 Each Entity becomes a Table "students" ‫ﺟﺪول‬ "Student" ‫ﻛﻴﺎن‬

2 Each Attribute becomes a Column "name" ‫ﻋﻤﻮد‬ "name" ‫ﺧﺎﺻﻴﺔ‬

3 Define a Primary Key for each table ‫" ﻛﻤﻔﺘﺎح أﺳﺎﳼ‬student_id"

4 Add Foreign Keys to show relationships ‫" داﺧﻞ ﺟﺪول اﻟﺘﺴﺠﻴﻼت‬course_id"

Check data types and naming

5 ‫اﻟﺘﺄﻛﺪ ﻣﻦ ﻧﻮع اﻟﺒﻴﺎﻧﺎت واﻷﺳﻤﺎء‬
Data Analysis Example – School Database Module 1 - Beginner Level

‫ﻣﺜﺎل – ﻗﺎﻋﺪة ﺑﻴﺎﻧـــــــــﺎت اﻟﻤﺪرﺳﺔ‬

CREATE TABLE students (

student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

CREATE TABLE courses (

course_id INT PRIMARY KEY,
title VARCHAR(50)
);

CREATE TABLE enrollments (

enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Data Analysis Module 1 - Beginner Level
Example – School Database

CREATE TABLE customers (

‫ﻣﺜﺎل – ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت اﻟﻤﺪرﺳﺔ‬
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);

CREATE TABLE orders (

order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE products (

product_id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10,2)
);

CREATE TABLE order_items (

order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Data Analysis Module 1 - Beginner Level
Example – School Database

CREATE TABLE customers (

‫ﻣﺜﺎل – ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت اﻟﻤﺪرﺳﺔ‬
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);

CREATE TABLE orders (

order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE products (

product_id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10,2)
);

CREATE TABLE order_items (

order_item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
 Data Analysis Module 1 - Beginner Level

Design Tips ‫ﻧﺼﺎﺋﺢ اﻟﺘﺼﻤﻴﻢ‬

Use short, clear table names. .‫اﺳﺘﺨﺪم أﺳﻤﺎء ﻗﺼﲑة وواﺿﺤﺔ ﻟﻠﺠﺪاول‬

Always use an ID as your Primary Key. ً

.‫ ﻛﻤﻔﺘﺎح أﺳﺎﳼ‬ID ‫داﻳﻤﺎ اﺳﺘﺨﺪم ﻋﻤﻮد‬
Make Foreign Keys match the related Primary Key. ‫ﻻزم اﻟﻤﻔﺘﺎح اﻷﺟﻨﱯ ﻳﻜﻮن ﻣﻄﺎﺑﻖ ﻟﻠﻤﻔﺘﺎح اﻷﺳﺎﳼ‬
.‫ﻓﻲ اﻟﺠﺪول اﻟﺘﺎﻧﻲ‬
Avoid repeating the same data in many tables. .‫ﺗﺠﻨﺐ ﺗﻜﺮار اﻟﺒﻴﺎﻧﺎت ﻓﻲ أﻛﱶ ﻣﻦ ﺟﺪول‬
 Data Analysis Module 1 - Beginner Level

Exercise ‫ﺗﻤﺮﻳﻦ ﻋﻤــــــﲇ‬

Draw a simple ERD for a Library System, then convert it into a Relational Schema.

ّ ‫ وﺑﻌﺪﻫﺎ‬،‫ارﺳﻢ ﻣﺨﻄﻂ ﻋﻼﻗﺎت ﺑﺴﻴﻂ ﻟﻤﻜﺘﺒﺔ‬

.‫ﺣﻮﻟﻪ إﱃ ﻣﺨﻄﻂ ﺟﺪاول ﻓﻌﲇ‬

Books (book_id, title, author) ‫اﻟﻜﺘﺐ‬

Members (member_id, name, phone) ‫اﻷﻋﻀﺎء‬

Borrowings (borrow_id, book_id, member_id, borrow_date) ‫اﻻﺳﺘﻌﺎرات‬

‫‪Data Analysis‬‬ ‫‪Module 1 - Beginner Level‬‬

‫‪DDL Commands‬‬
‫)‪(CREATE, ALTER, DROP‬‬

‫أواﻣﺮ ﺗﻌﺮﻳﻒ اﻟﺒﻴﺎﻧﺎت )‪(DDL‬‬

‫ﻓﻲ ‪ – SQL‬اﻹﻧﺸـــــــــــــــﺎء‬
‫‪02‬‬ ‫واﻟﺘﻌﺪﻳﻞ واﻟﺤـــــــــــــــﺬف‬
 Data Analysis Module 1 - Beginner Level

What is DDL? ‫؟‬DDL ‫ﻣـــــﺎ ﻫـﻲ‬

DDL means Data Definition Language.
It is the part of SQL we use to create and change the structure of the
database (tables, columns, etc.).

،‫ ﻫﻲ اﺧﺘﺼﺎر ﻟـ ﻟﻐﺔ ﺗﻌﺮﻳﻒ اﻟﺒﻴﺎﻧﺎت‬DDL

ّ
‫وﻧﻐﲑ ﻓﻲ ﻫﻴﻜﻞ ﻗﺎﻋﺪة اﻟﺒﻴﺎﻧﺎت‬ ‫ اﻟﲇ ﺑﻨﺴﺘﺨﺪﻣﻪ ﻋﻠﺸﺎن ﻧﻨﺊﺸ اﻟﺠﺪاول‬SQL ‫ودي اﻟﺠﺰء ﻣﻦ‬
.(‫ﻧﻔﺴﻬﺎ )ﻣﺶ اﻟﺒﻴﺎﻧﺎت داﺧﻠﻬﺎ‬
 Data Analysis Module 1 - Beginner Level

Main DDL Commands ‫ اﻷﺳﺎﺳﻴﺔ‬DDL ‫أواﻣﺮ‬

Command Function ‫اﻟﺘﻮﺿﻴﺢ‬

CREATE Create a new table or database ‫ﻹﻧﺸﺎء ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت أو ﺟﺪول ﺟﺪﻳﺪ‬

ALTER Modify a table (add, change, or ‫ﻟﺘﻌﺪﻳﻞ ﺟﺪول ﻣﻮﺟﻮد ﺑﺎﻟﻔﻌﻞ‬

delete columns)

DROP Delete a table or database ‫ﻟﺤﺬف ﺟﺪول أو ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﺑﺎﻟﻜﺎﻣﻞ‬

completely
 Data Analysis Module 1 - Beginner Level

CREATE Command ‫أﻣــــــــﺮ اﻹﻧﺸــــــــﺎء‬

We use CREATE to make a new table or database. .‫ ﻹﻧﺸﺎء ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت أو ﺟﺪول ﺟﺪﻳﺪ‬CREATE ‫ﺑﻨﺴﺘﺨﺪم أﻣﺮ‬
This code creates a new database and an ‫اﻟﻜﻮد ده ﺑﻴﻌﻤﻞ ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﺟﺪﻳﺪة وﺟﻮاﻫﺎ ﺟﺪول‬
“employees” table inside it. .“‫”اﻟﻤﻮﻇﻔﲔ‬

CREATE DATABASE company_db;

USE company_db;

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
name VARCHAR(50),
position VARCHAR(50),
salary DECIMAL(10,2)
);
 Data Analysis Module 1 - Beginner Level

ALTER Command ‫أﻣــــــﺮ اﻟﺘﻌﺪﻳـــــــــﻞ‬

We use ALTER when we want to add, rename, or ‫ ﻟﻤﺎ ﻧﺤﺘﺎج ﻧﻀﻴﻒ أو ﻧﻌ ّﺪل أو ﻧﺤﺬف‬ALTER ‫ﺑﻨﺴﺘﺨﺪم أﻣﺮ‬
delete a column. .‫ﻋﻤﻮد داﺧﻞ ﺟﺪول‬
These commands show how to modify the table ‫ﻧﻐﲑ ﻓﻲ ﺷﻜﻞ اﻟﺠﺪول ﻣﻦ ﻏﲑ ﻣﺎ‬ ّ ‫اﻷواﻣﺮ دي ﺑﺘﻮﺿﺢ إزاي‬
structure without deleting it. .‫ﻧﺤﺬﻓﻪ‬

-- Add new column

ALTER TABLE employees ADD COLUMN hire_date DATE;

-- Rename a column
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- Delete a column
ALTER TABLE employees DROP COLUMN position;
 Data Analysis Module 1 - Beginner Level

DROP Command ‫أﻣــــــﺮ اﻟﺤـــــﺬف‬

We use DROP to delete a table or a database .‫ ﻟﺤﺬف ﺟﺪول أو ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﺑﺎﻟﻜﺎﻣﻞ‬DROP ‫ﺑﻨﺴﺘﺨﺪم‬
completely.
Be careful! This command removes everything — .‫اﻧﺘﺒﻪ! ﻷن اﻷﻣﺮ ده ﺑﻴﻤﺴﺢ اﻟﺒﻴﺎﻧﺎت واﻟﺘﺼﻤﻴﻢ ﻣﻊ ﺑﻌﺾ‬
data and structure.

DROP TABLE employees;

DROP DATABASE company_db;
 Data Analysis Module 1 - Beginner Level

Mini Project Example ّ

‫ﻣﺜﺎل ﻋﻤﲇ ﻣﺼﻐـــﺮ‬

Let’s build a small database for a store: ‫ﻧﻨﺸﺄ ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﺑﺴﻴﻄﺔ ﻟﻠﻤﺘﺠﺮ‬

CREATE TABLE customers (

CREATE DATABASE store_db; customer_id INT PRIMARY KEY,
USE store_db; first_name VARCHAR(50),
last_name VARCHAR(50),
CREATE TABLE products ( city VARCHAR(50)
product_id INT PRIMARY KEY, );
name VARCHAR(50),
category VARCHAR(50), CREATE TABLE orders (
price DECIMAL(10,2), order_id INT PRIMARY KEY,
stock INT customer_id INT,
); order_date DATE,
FOREIGN KEY (customer_id) REFERENCES
customers(customer_id)
);
 Data Analysis Module 1 - Beginner Level

Exercise ‫ﺗﻤـــــــﺮﻳــــــﻦ ﻋﻤﻠـﻲ‬

CREATE DATABASE school_db; Create a new database called school_db.

USE school_db; Add two tables: teachers and students.
CREATE TABLE teachers ( Each student has a teacher (use a Foreign Key).
teacher_id INT PRIMARY KEY,
name VARCHAR(50)
.school_db ‫أﻧﺊﺸ ﻗﺎﻋﺪة ﺑﻴﺎﻧﺎت ﺟﺪﻳﺪة ﺑﺎﺳﻢ‬
);
.students ‫ و‬teachers :‫أﺿﻒ ﺟﺪوﻟﲔ‬
CREATE TABLE students (
student_id INT PRIMARY KEY, .‫ارﺑﻂ ﻛﻞ ﻃﺎﻟﺐ ﺑﻤﺪرس ﻋﻦ ﻃﺮﻳﻖ ﻣﻔﺘﺎح أﺟﻨﱯ‬
name VARCHAR(50),
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
Data Analysis Module 1 - Beginner Level

Quick Tips ‫ﻧﺼــــــﺎﺋﺢ ﴎﻳﻌـــــﺔ‬

Use UPPERCASE for SQL commands.

Use lowercase for table and column names.
Always check your Foreign Keys.
Avoid using spaces in names.
.SQL ‫اﺳﺘﺨﺪم ﺣﺮوف ﻛﺒﲑة ﻷواﻣﺮ‬
.‫اﺳﺘﺨﺪم ﺣﺮوف ﺻﻐﲑة ﻷﺳﻤﺎء اﻟﺠﺪاول واﻷﻋﻤﺪة‬
ً
.‫داﻳﻤﺎ‬ ‫ﺗﺄﻛﺪ ﻣﻦ ﺻﺤﺔ اﻟﻤﻔﺎﺗﻴﺢ اﻷﺟﻨﺒﻴﺔ‬
.‫ﺗﺠﻨﺐ اﻟﻤﺴﺎﻓﺎت داﺧﻞ أﺳﻤﺎء اﻟﺠﺪاول أو اﻷﻋﻤﺪة‬ّ
‫‪Data Analysis‬‬ ‫‪Module 1 - Beginner Level‬‬

‫‪SQL DML Commands‬‬

‫)‪(INSERT, UPDATE, DELETE, SELECT‬‬

‫أواﻣﺮ ﻣﻌﺎﻟﺠﺔ اﻟﺒﻴﺎﻧﺎت ﻓﻲ ‪SQL‬‬

‫اﻹﺿﺎﻓــــــــــــﺔ واﻟﺘﻌﺪﻳــــــــﻞ‬
‫‪03‬‬ ‫واﻟﺤــــــﺬف واﻻﺳﺘﻌــــــــــﻼم‬
 Data Analysis Module 1 - Beginner Level

What is DML? ‫؟‬DML ‫ﻣـــــﺎ ﻫـﻲ‬

DML means Data Manipulation Language. ،‫ ﻫﻲ اﺧﺘﺼﺎر ﻟـ ﻟﻐﺔ ﻣﻌﺎﻟﺠﺔ اﻟﺒﻴﺎﻧﺎت‬DML
It is the part of SQL we use to work with the ‫ودي اﻟﺠﺰء اﻟﲇ ﺑﻨﺴﺘﺨﺪﻣﻪ ﻟﻠﺘﻌﺎﻣﻞ ﻣﻊ‬
data inside tables — not the structure. .‫اﻟﺒﻴﺎﻧﺎت داﺧﻞ اﻟﺠﺪاول — ﻣﺶ ﻟﺘﺼﻤﻴﻤﻬﺎ‬

Main DML Commands ‫ اﻷﺳﺎﺳﻴﺔ‬DML ‫أواﻣﺮ‬

Command Function ‫اﻟﺘﻮﺿﻴﺢ‬

INSERT Add new data to a table ‫ﻹﺿﺎﻓﺔ ﺑﻴﺎﻧﺎت ﺟﺪﻳﺪة إﱃ اﻟﺠﺪول‬

UPDATE Change existing data ‫ﻟﺘﻌﺪﻳﻞ ﺑﻴﺎﻧﺎت ﻣﻮﺟﻮدة ﺑﺎﻟﻔﻌﻞ‬

DELETE Remove data from a table ‫ﻟﺤﺬف ﺑﻴﺎﻧﺎت ﻣﻦ اﻟﺠﺪول‬

SELECT View or extract data ‫ﻟﻌﺮض اﻟﺒﻴﺎﻧﺎت أو ﺗﺤﻠﻴﻠﻬﺎ‬

 Data Analysis Module 1 - Beginner Level

INSERT Command ‫أﻣـــــــﺮ اﻹﺿـــــﺎﻓﺔ‬

We use INSERT to add new data to a table. ‫ ﻹﺿﺎﻓﺔ ﺻﻔﻮف‬INSERT ‫ﺑﻨﺴﺘﺨﺪم‬
.‫ﺟﺪﻳﺪة ﻣﻦ اﻟﺒﻴﺎﻧﺎت داﺧﻞ اﻟﺠﺪول‬
This adds one student record to the table. .‫اﻟﻜﻮد ده ﺑﻴﻀﻴﻒ ﻃﺎﻟﺐ ﺟﺪﻳﺪ إﱃ ﺟﺪول اﻟﻄﻼب‬

INSERT INTO students (student_id, name, age, city)

VALUES (1, 'Sara Ali', 20, 'Riyadh');

INSERT INTO students (student_id, name, age, city)

VALUES
(2, 'Omar Ahmed', 21, 'Dammam'),
(3, 'Laila Hassan', 22, 'Jeddah');
 Data Analysis Module 1 - Beginner Level

UPDATE Command ‫أﻣـــــــﺮ اﻟﺘﻌﺪﻳــــــﻞ‬

We use UPDATE to change existing data in a table. ‫ ﻟﺘﻌﺪﻳﻞ ﺑﻴﺎﻧﺎت‬UPDATE ‫ﺑﻨﺴﺘﺨﺪم‬
.‫ﻣﻮﺟﻮدة ﺑﺎﻟﻔﻌﻞ داﺧﻞ اﻟﺠﺪول‬
This changes the city of the student ّ
."‫ إﱃ "ﻣﻜﺔ‬2 ‫ﺑﻴﻐﲑ اﻟﻤﺪﻳﻨﺔ ﻟﻠﻄﺎﻟﺐ رﻗﻢ‬ ‫اﻟﻜﻮد ده‬
with ID 2 to “Makkah.”
Tip: Always use WHERE with UPDATE, otherwise all rows will be changed.
ً
.‫ وإﻻ ﺳﻴﺘﻢ ﺗﻌﺪﻳﻞ ﺟﻤﻴﻊ اﻟﺼﻔﻮف ﻓﻲ اﻟﺠﺪول‬،UPDATE ‫ ﻣﻊ أﻣﺮ‬WHERE ‫داﺋﻤﺎ‬ ‫ اﺳﺘﺨﺪم‬:‫ﻧﺼﻴﺤﺔ‬

UPDATE students
SET city = 'Makkah'
WHERE student_id = 2;
 Data Analysis Module 1 - Beginner Level

DELETE Command ‫أﻣـــــــﺮ اﻟﺤـــــــــﺬف‬

We use DELETE to remove records from a table. .‫ ﻟﺤﺬف ﺻﻒ أو أﻛﱶ ﻣﻦ اﻟﺠﺪول‬DELETE ‫ﺑﻨﺴﺘﺨﺪم‬

This deletes the record of student number 3. .‫ ﻣﻦ اﻟﺠﺪول‬3 ‫اﻟﻜﻮد ده ﺑﻴﺤﺬف ﺑﻴﺎﻧﺎت اﻟﻄﺎﻟﺐ رﻗﻢ‬

Tip: Be careful — if you forget WHERE, it will delete all rows!

ُ :‫ﻧﺼﻴﺤﺔ‬
.‫ ﻓﺴﻴﺘﻢ ﺣﺬف ﺟﻤﻴﻊ اﻟﺼﻔﻮف‬،WHERE ‫ﻛﻦ ﺣﺬ ًرا — إذا َﻧﺴﻴﺖ اﺳﺘﺨﺪام‬

DELETE FROM students

WHERE student_id = 3;
 Data Analysis Module 1 - Beginner Level

SELECT Command ‫أﻣـــــــﺮ اﻻﺳﺘﻌــــﻼم‬

We use SELECT to read and show data from a table. .‫ ﻻﺳﱰﺟﺎع اﻟﺒﻴﺎﻧﺎت ﻣﻦ اﻟﺠﺪاول وﻋﺮﺿﻬﺎ‬SELECT ‫ﺑﻨﺴﺘﺨﺪم‬
We can choose what to show, filter it, and sort it easily.
.‫ وﻧﺮﺗﺒﻬﺎ ﺑﺴﻬﻮﻟﺔ‬،‫ ﻧﻔﻠﱰ اﻟﻨﺘﺎﺋﺞ‬،‫ﻧﻘﺪر ﻧﻌﺮض اﻷﻋﻤﺪة اﻟﲇ ﻧﺤﺘﺎﺟﻬﺎ‬

-- Show all columns

SELECT * FROM students;
-- Show specific columns
SELECT name, city FROM students;
-- Filter data
SELECT * FROM students WHERE city = 'Riyadh';
-- Sort results
SELECT * FROM students ORDER BY age DESC;
 Data Analysis Module 1 - Beginner Level

Summary Table ‫ﻣﻠﺨـــﺺ اﻷواﻣــــــﺮ‬

Command Use Case Example

INSERT Add data INSERT INTO students VALUES (...);

UPDATE Edit data UPDATE students SET city='Jeddah';

DELETE Remove data DELETE FROM students WHERE id=3;

SELECT Retrieve data SELECT * FROM students;

 Data Analysis Module 1 - Beginner Level

Quick Tips ‫ﻧﺼﺎﺋـــﺢ ﴎﻳﻌــﺔ‬

Always test your query on small data first.

Use WHERE to control which rows you change or delete.

Use ORDER BY to sort and LIMIT to control results.

.‫ﺟ ّﺮب أواﻣﺮك ﻋﲆ ﺑﻴﺎﻧﺎت ﺑﺴﻴﻄﺔ ﻓﻲ اﻷول‬

.‫ ﻟﺘﺤﺪﻳﺪ اﻟﺼﻔﻮف اﻟﲇ ﺗﻌ ّﺪﻟﻬﺎ أو ﺗﺤﺬﻓﻬﺎ‬WHERE ‫اﺳﺘﺨﺪم‬

.‫ ﻟﺘﺤﺪﻳﺪ ﻋﺪد اﻟﻨﺘﺎﺋﺞ‬LIMIT‫ ﻟﻠﱰﺗﻴﺐ و‬ORDER BY ‫اﺳﺘﺨﺪم‬

Data Analysis Module 1 - Beginner Level

Thank You
Contact Inside Egypt: +201022301728 management@[Link]

Us Outside Egypt: +966567323307 [Link]