Data Analysis SQL for Data Analysis

SQL
for Data Analysis
‫‪Data Analysis‬‬ ‫‪SQL for Data Analysis‬‬

‫‪Module 2‬‬
‫‪Filtering,‬‬
‫‪Sorting & Joins‬‬
‫اﻟﻤﺴﺘﻮى اﻟﺜـــــﺎﻧﻲ‪:‬‬

‫اﻟﻔﻠﺘـــــــــــﺮة واﻟﻔــــــــــﺮز‬
‫‪04‬‬ ‫واﻟﺮﺑــــﻂ ﺑﻴــــــﻦ اﻟﺠـــﺪاول‬
 Data Analysis Module 2

What is Filtering? ‫ﻣـــﺎ ﻫـــﻲ اﻟﻔﻠﱰة؟‬

Filtering means showing only the rows that match certain conditions.
We use the WHERE clause to choose which data to show.
.‫اﻟﻔﻠﱰة ﻣﻌﻨﺎﻫﺎ ﻧﻌﺮض ﻓﻘﻂ اﻟﺼﻔﻮف اﻟﲇ ﺑﺘﺘﺤﻘﻖ ﻓﻴﻬﺎ ﴍوط ﻣﻌﻴﻨﺔ‬
.‫ ﻋﻠﺸﺎن ﻧﺤﺪد اﻟﺒﻴﺎﻧﺎت اﻟﲇ ﻧﻌﺮﺿﻬﺎ‬WHERE ‫ﺑﻨﺴﺘﺨﺪم اﻟﻜﻠﻤﺔ اﻟﻤﻔﺘﺎﺣﻴﺔ‬

WHERE Clause ‫ﺟﻤﻠﺔ اﻟﴩط اﻷﺳﺎﺳﻴﺔ‬

WHERE tells SQL to show only rows that meet a condition.
Shows only students who live in Riyadh.
.‫ ﻋﻠﺸﺎن ﻧﻌﺮض اﻟﺼﻔﻮف اﻟﲇ ﺗﺤﻘﻖ ﴍط ﻣﻌﲔ‬WHERE ‫ﺑﻨﺴﺘﺨﺪم‬
.“‫ﻳﻌﺮض ﻓﻘﻂ اﻟﻄﻼب اﻟﲇ ﻣﺪﻳﻨﺘﻬﻢ ”اﻟﺮﻳﺎض‬

SELECT * FROM students

WHERE city = 'Riyadh';
 Data Analysis Module 2

Comparison Operators ‫ﻣﻌﺎﻣﻼت اﻟﻤﻘﺎرﻧﺔ‬

Operator Meaning Example ‫اﻟﻤﻌﲎ‬

= Equal to city = 'Riyadh' ‫ﻳﺴﺎوي‬

<> or != Not equal age <> 20 ‫ﻻ ﻳﺴﺎوي‬

> Greater than salary > 5000 ‫أﻛﱪ ﻣﻦ‬

< Less than age < 18 ‫أﺻﻐﺮ ﻣﻦ‬

>= Greater or equal price >= 100 ‫أﻛﱪ أو ﻳﺴﺎوي‬

<= Less or equal age <= 30 ‫أﺻﻐﺮ أو ﻳﺴﺎوي‬

 Data Analysis Module 2

LIKE Operator ‫اﻟﺒﺤــــــﺚ اﻟﺠﺰﺋــــﻲ‬

We use LIKE to find text that matches a pattern. .‫ ﻟﻠﺒﺤﺚ ﻋﻦ ﻧﺼﻮص ﺗﺤﺘﻮي ﻋﲆ ﻧﻤﻂ ﻣﻌﲔ‬LIKE ‫ﺑﻨﺴﺘﺨﺪم‬
% means any number of characters. .“‫ ﻣﻌﻨﺎه ”أي ﻋﺪد ﻣﻦ اﻟﺤﺮوف‬٪ ‫اﻟﺮﻣﺰ‬

-- Names starting with 'A'

SELECT * FROM students
WHERE name LIKE 'A%';

-- Names ending with 'n'

SELECT * FROM students
WHERE name LIKE '%n';

-- Names that contain 'am'

SELECT * FROM students
WHERE name LIKE '%am%';
 Data Analysis Module 2

BETWEEN Operator ‫ﺑﻴـــﻦ ﻗﻴﻤﺘـــﻴــــﻦ‬

We use BETWEEN to find data between two numbers or dates.
.‫ ﻹﻇﻬﺎر اﻟﺒﻴﺎﻧﺎت اﻟﲇ ﻗﻴﻤﺘﻬﺎ ﺑﲔ رﻗﻤﲔ أو ﺗﺎرﻳﺨﲔ‬BETWEEN ‫ﺑﻨﺴﺘﺨﺪم‬

-- Age between 18 and 25

SELECT * FROM students
WHERE age BETWEEN 18 AND 25;

-- Orders between two dates

SELECT * FROM orders
WHERE order_date BETWEEN '01-01-2025' AND '31-01-2025';
 Data Analysis Module 2

IN Operator ‫داﺧﻞ ﻗﺎﺋﻤﺔ ﻗﻴﻢ‬

We use IN to match any value from a list. .‫ ﻟﻤﺎ ﻧﺤﺘﺎج ﻧﻄﺎﺑﻖ ﻗﻴﻤﺔ ﻣﻦ ﻣﺠﻤﻮﻋﺔ ﻗﻴﻢ ﻣﺤﺪدة‬IN ‫ﺑﻨﺴﺘﺨﺪم‬

-- Students from Riyadh or Jeddah or Dammam

SELECT * FROM students
WHERE city IN ('Riyadh', 'Jeddah', 'Dammam');
IS NULL / IS NOT NULL ‫اﻟﻘﻴﻢ اﻟﻔــــــﺎرﻏﺔ‬
Use these to check if a column has no value (NULL).
.‫( أو ﻓﻴﻪ ﻗﻴﻤﺔ‬NULL) ‫ﺑﻨﺴﺘﺨﺪﻣﻬﺎ ﻋﻠﺸﺎن ﻧﻌﺮف إذا ﻛﺎن اﻟﻌﻤﻮد ﻓﺎﴈ‬
-- Find students who don’t have an email
SELECT * FROM students
WHERE email IS NULL;

-- Find students who have an email

SELECT * FROM students
WHERE email IS NOT NULL;
Data Analysis Module 2

ORDER BY ‫اﻟﺘـــــﺮﺗﻴــــــﺐ‬
We use ORDER BY to sort results. .‫ ﻟﱰﺗﻴﺐ اﻟﻨﺘﺎﺋﺞ ﺗﺼﺎﻋﺪ ًﻳﺎ أو ﺗﻨﺎزﻟ ًﻴﺎ‬ORDER BY ‫ﺑﻨﺴﺘﺨﺪم‬

Use ASC for ascending (default) and DESC for descending.

SELECT * FROM students ORDER BY age ASC;

SELECT * FROM students ORDER BY name DESC;
LIMIT ‫ﺗﺤﺪﻳﺪ ﻋﺪد اﻟﻨﺘﺎﺋﺞ‬

We use LIMIT to control how many rows to show.

.‫ ﻟﺘﺤﺪﻳﺪ ﻋﺪد اﻟﺼﻔﻮف اﻟﲇ ﺗﻈﻬﺮ ﻓﻲ اﻟﻨﺘﺎﺋﺞ‬LIMIT ‫ﺑﻨﺴﺘﺨﺪم‬

SELECT * FROM students LIMIT 5;

Data Analysis Module 2

LIMIT ‫ﺗﺤﺪﻳﺪ ﻋﺪد اﻟﻨﺘﺎﺋﺞ‬

You have this table employees: (emp_id, name, department, salary, city)
Do these queries:
Show all employees in “IT” department.
Show all employees from “Riyadh” OR “Jeddah.”
Show employees with salary BETWEEN 5000 and 8000.
Show employees whose name starts with “A.”
Show only top 3 highest salaries.
(emp_id, name, department, salary, city) :‫ ﺑﺎﻷﻋﻤﺪة اﻟﺘﺎﻟﻴﺔ‬employees ‫ﻋﻨﺪك ﺟﺪول‬
ّ
:‫ﻧﻔﺬ اﻻﺳﺘﻌﻼﻣﺎت اﻟﺘﺎﻟﻴﺔ‬
.“IT” ‫اﻋﺮض ﻛﻞ اﻟﻤﻮﻇﻔﲔ ﻓﻲ ﻗﺴﻢ‬
.“‫اﻋﺮض اﻟﻤﻮﻇﻔﲔ ﻣﻦ ”اﻟﺮﻳﺎض“ أو ”ﺟﺪة‬
.8000 ‫ و‬5000 ‫اﻋﺮض اﻟﻤﻮﻇﻔﲔ اﻟﲇ رواﺗﺒﻬﻢ ﺑﲔ‬
.“A” ‫اﻋﺮض اﻟﻤﻮﻇﻔﲔ اﻟﲇ أﺳﻤﺎؤﻫﻢ ﺗﺒﺪأ ﺑﺤﺮف‬
.‫ رواﺗﺐ ﻓﻘﻂ‬3 ‫اﻋﺮض أﻋﲆ‬
Data Analysis Module 2

Example Answers ‫أﻣﺜﻠﺔ ﻟﻺﺟﺎﺑﺎت‬

SELECT * FROM employees WHERE department = 'IT';

SELECT * FROM employees WHERE city IN ('Riyadh', 'Jeddah');
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 8000;
SELECT * FROM employees WHERE name LIKE 'A%';
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
‫‪Data Analysis‬‬ ‫‪Module 2‬‬

‫‪SQL Joins‬‬
‫اﻟﺮﺑﻂ ﺑﲔ اﻟﺠــــــــــــــﺪاول‬

‫‪05‬‬
 Data Analysis Module 2

What is a JOIN? ‫؟‬JOIN ‫ﻣـــﺎ ﻫـــــﻮ اﻟـ‬

A JOIN is used to combine data from two or more ‫ ﻫﻮ اﻟﻄﺮﻳﻘﺔ اﻟﲇ ﺑﲊﺑﻂ ﺑﻴﻬﺎ اﻟﺒﻴﺎﻧﺎت ﻣﻦ ﺟﺪوﻟﲔ أو‬JOIN ‫اﻟـ‬
tables using a common column. .‫أﻛﱰ ﻣﻦ ﺧﻼل ﻋﻤﻮد ﻣﺸﱰك ﺑﻴﻨﻬﻢ‬

Why We Use Joins ‫ﻟﻴﻪ ﺑﻨﺴﺘﺨﺪﻣﻬـــــﺎ؟‬

To bring related data together.

To answer real questions (like orders + customer names).

To avoid repeating the same data in one table.

.‫ﻋﻠﺸﺎن ﻧﻌﺮض ﺑﻴﺎﻧﺎت ﻣﺮﺗﺒﻄﺔ ﻣﻦ ﺟﺪاول ﻣﺨﺘﻠﻔﺔ‬

.(‫ﻋﻠﺸﺎن ﻧﺠﺎوب ﻋﲆ أﺳﺌﻠﺔ ﺗﺤﻠﻴﻠﻴﺔ )زي ﻋﺮض اﻟﻄﻠﺒﺎت ﻣﻊ أﺳﻤﺎء اﻟﻌﻤﻼء‬

.‫وﻋﻠﺸﺎن ﻧﻘﻠﻞ اﻟﺘﻜﺮار ﻓﻲ اﻟﺒﻴﺎﻧﺎت وﻧﺒﲏ ﻋﻼﻗﺎت ﺻﺢ‬

 Data Analysis Module 2

Example Setup ‫اﻟﺠﺪاول اﻟﲇ ﻫﻨﺸﺘﻐﻞ ﻋﻠﻴﻬﺎ‬

CREATE TABLE customers (

customer_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES
(1, 'Sara', 'Riyadh'),
(2, 'Omar', 'Dammam'),
(3, 'Laila', 'Jeddah');
INSERT INTO orders VALUES
(01-01-2025' ,1 ,101'),
(05-02-2025' ,1 ,102'),
(10-03-2025' ,2 ,103');
Data Analysis Module 2

INNER JOIN ‫اﻟﺠﻠﺐ اﻟﻤﺘﻄﺎﺑﻖ ﻓﻘﻂ‬

Shows only rows where the data exists in both .‫ﺑﻴﻌﺮض ﻓﻘﻂ اﻟﺴﺠﻼت اﻟﲇ ﻟﻴﻬﺎ ﺗﻄﺎﺑﻖ ﻓﻲ اﻟﺠﺪوﻟﲔ‬
tables.

SELECT [Link], orders.order_id, orders.order_date

FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Name order_id order_date

Sara 101 2025-01-01

Sara 102 2025-02-05

Omar
103 2025-03-10
Data Analysis Module 2

LEFT JOIN ‫ﻛﻞ ﺑﻴﺎﻧﺎت اﻟﺠﺪول اﻷﻳﴪ‬

Shows all data from the left table, even if there’s no ،‫ وﻟﻮ ﻣﻔﻴﺶ ﺗﻄﺎﺑﻖ‬،‫ﺑﻴﻌﺮض ﻛﻞ ﺑﻴﺎﻧﺎت اﻟﺠﺪول اﻷﻳﴪ‬
match in the right table. .‫( ﻣﻦ اﻟﺠﺪول اﻟﺜﺎﻧﻲ‬NULL) ‫ﻳﻌﺮض ﻗﻴﻢ ﻓﺎرﻏﺔ‬

SELECT [Link], orders.order_id

FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Name order_id

Sara 101
Sara 102

Omar 103

Laila NULL
Data Analysis Module 2

FULL JOIN ‫ﻛﻞ اﻟﺒﻴﺎﻧﺎت ﻣﻦ اﻟﺠﺪوﻟﲔ‬

Shows all data from both tables, matching where ‫ﺑﻴﻌﺮض ﻛﻞ اﻟﺒﻴﺎﻧﺎت ﻣﻦ اﻟﺠﺪوﻟﲔ ﺳﻮاء ﻓﻲ ﺗﻄﺎﺑﻖ أو‬
possible, and NULL where not. .NULL ‫ وﺑﻴﻜﻤﻞ اﻟﻘﻴﻢ اﻟﻐﲑ ﻣﻮﺟﻮدة ﺑـ‬،‫ﻷ‬

SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id

UNION

SELECT *
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;
Data Analysis Module 2

SELF JOIN ‫رﺑﻂ اﻟﺠﺪول ﺑﻨﻔﺴﻪ‬

We use it when a table relates to itself — like ‫ﺑﻨﺴﺘﺨﺪﻣﻪ ﻟﻤﺎ ﺟﺪول ﺑﲑﺗﺒﻂ ﺑﻨﻔﺴﻪ )زي اﻟﻤﻮﻇﻔﲔ‬
employees and managers. .(‫واﻟﻤﺪﻳﺮﻳﻦ ﻓﻲ ﻧﻔﺲ اﻟﺠﺪول‬

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employees VALUES

(1, 'Ali', NULL),
(2, 'Sara', 1),
(3, 'Omar', 1),
(4, 'Laila', 2);

SELECT [Link] AS Employee, [Link] AS Manager

FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
Data Analysis Module 2

Summary Table Joins ‫ﺟﺪول ﻣﻠﺨﺺ ﻷﻧﻮاع اﻟـ‬

Join Type Description Example ‫اﻟﻤﻌﲎ‬

INNER JOIN Only matching rows Customer Orders ‫ﻓﻘﻂ اﻟﺒﻴﺎﻧﺎت اﻟﲇ ﻟﻴﻬﺎ ﻋﻼﻗﺔ ﻓﻲ اﻟﺠﺪوﻟﲔ‬

All from left,

LEFT JOIN All Customers ‫ﻛﻞ اﻟﻌﻤﻼء ﺣﱴ اﻟﲇ ﻣﺎ ﻋﻨﺪﻫﻤﺶ ﻃﻠﺒﺎت‬
matches from right
All from right,
RIGHT JOIN All Orders ‫ﻛﻞ اﻟﻄﻠﺒﺎت ﺣﱴ اﻟﲇ ﻣﻔﻴﺶ ﻋﻤﻴﻞ ﻟﻴﻬﺎ‬
matches from left

FULL JOIN All from both tables All Data ‫ﻛﻞ اﻟﺒﻴﺎﻧﺎت ﻣﻦ اﻟﺠﺪوﻟﲔ‬

Employees Managers
SELF JOIN Table joins itself ‫رﺑﻂ اﻟﺠﺪول ﺑﻨﻔﺴﻪ‬
Data Analysis Module 2

Practice Challenge ‫ﺗﻤﺮﻳﻦ ﻋﻤﲇ‬

Use these tables:

Customers (customer_id, name, city)
Orders (order_id, customer_id, amount)
Write queries to:
Show customers who made orders.
Show all customers even if they didn’t make orders.
Show all orders, even if they don’t have a customer.
:‫اﺳﺘﺨﺪم اﻟﺠﺪاول اﻟﺘﺎﻟﻴﺔ‬
Customers (customer_id, name, city)
Orders (order_id, customer_id, amount)
:‫واﻛﺘﺐ اﻻﺳﺘﻌﻼﻣﺎت اﻟﺘﺎﻟﻴﺔ‬
.‫اﻋﺮض اﻟﻌﻤﻼء اﻟﲇ ﻋﻨﺪﻫﻢ ﻃﻠﺒﺎت‬
.‫اﻋﺮض ﻛﻞ اﻟﻌﻤﻼء ﺣﱴ ﻟﻮ ﻣﺎ ﻋﻨﺪﻫﻤﺶ ﻃﻠﺒﺎت‬
.‫اﻋﺮض ﻛﻞ اﻟﻄﻠﺒﺎت ﺣﱴ اﻟﲇ ﻣﺎﻟﻬﺎش ﻋﻤﻴﻞ‬
Data Analysis Module 2

Example Answers: ‫اﻹﺟﺎﺑﺔ ﻋﲇ اﻟﻤﺜﺎل‬

-- 1. INNER JOIN
SELECT [Link], [Link]
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;
-- 2. LEFT JOIN
SELECT [Link], [Link]
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
-- 3. RIGHT JOIN
SELECT [Link], [Link]
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;
Data Analysis Module 2

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

Us Outside Egypt: +966567323307 [Link]