Data Analysis Useful Resources
#dataanalysis
#dataanalysisbooks
#sqlbooks
#pythonbooks
#tableau
#powerbi
#datavisualization
For promotions: @coderfun
Post #1769
1.25K
Complete SQL guide for Data Analytics
1. Introduction to SQL
What is SQL?
• SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases. It allows you to interact with data by querying, inserting, updating, and deleting records in a database.
• SQL is essential for Data Analytics because it enables analysts to retrieve and manipulate data for analysis, reporting, and decision-making.
Applications in Data Analytics
• Data Retrieval: SQL is used to pull data from databases for analysis.
• Data Transformation: SQL helps clean, aggregate, and transform data into a usable format for analysis.
• Reporting: SQL can be used to create reports by summarizing data or applying business rules.
• Data Modeling: SQL helps in preparing datasets for further analysis or machine learning.
2. SQL Basics
Data Types
SQL supports various data types that define the kind of data a column can hold:
• Numeric Data Types:
• INT: Integer numbers, e.g., 123.
• DECIMAL(p,s): Exact numbers with a specified precision and scale, e.g., DECIMAL(10,2) for numbers like 12345.67.
• FLOAT: Approximate numbers, e.g., 123.456.
• String Data Types:
• CHAR(n): Fixed-length strings, e.g., CHAR(10) will always use 10 characters.
• VARCHAR(n): Variable-length strings, e.g., VARCHAR(50) can store up to 50 characters.
• TEXT: Long text data, e.g., descriptions or long notes.
• Date/Time Data Types:
• DATE: Stores date values, e.g., 2024-12-01.
• DATETIME: Stores both date and time, e.g., 2024-12-01 12:00:00.
Creating and Modifying Tables
You can create, alter, and drop tables using SQL commands:
Data Insertion, Updating, and Deletion
SQL allows you to manipulate data using INSERT, UPDATE, and DELETE commands:
3. Data Retrieval
SELECT Statement
The SELECT statement is used to retrieve data from a database:
Filtering Data with WHERE
The WHERE clause filters data based on specific conditions:
Sorting Data with ORDER BY
The ORDER BY clause sorts the result set by one or more columns:
Aliasing
You can use aliases to rename columns or tables for clarity:
4. Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single result.
Common Aggregate Functions
GROUP BY and HAVING
• GROUP BY is used to group rows sharing the same value in a column.
• HAVING filters groups based on aggregate conditions.
5. Joins
Joins are used to combine rows from two or more tables based on related columns.
Types of Joins
1. Introduction to SQL
What is SQL?
• SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases. It allows you to interact with data by querying, inserting, updating, and deleting records in a database.
• SQL is essential for Data Analytics because it enables analysts to retrieve and manipulate data for analysis, reporting, and decision-making.
Applications in Data Analytics
• Data Retrieval: SQL is used to pull data from databases for analysis.
• Data Transformation: SQL helps clean, aggregate, and transform data into a usable format for analysis.
• Reporting: SQL can be used to create reports by summarizing data or applying business rules.
• Data Modeling: SQL helps in preparing datasets for further analysis or machine learning.
2. SQL Basics
Data Types
SQL supports various data types that define the kind of data a column can hold:
• Numeric Data Types:
• INT: Integer numbers, e.g., 123.
• DECIMAL(p,s): Exact numbers with a specified precision and scale, e.g., DECIMAL(10,2) for numbers like 12345.67.
• FLOAT: Approximate numbers, e.g., 123.456.
• String Data Types:
• CHAR(n): Fixed-length strings, e.g., CHAR(10) will always use 10 characters.
• VARCHAR(n): Variable-length strings, e.g., VARCHAR(50) can store up to 50 characters.
• TEXT: Long text data, e.g., descriptions or long notes.
• Date/Time Data Types:
• DATE: Stores date values, e.g., 2024-12-01.
• DATETIME: Stores both date and time, e.g., 2024-12-01 12:00:00.
Creating and Modifying Tables
You can create, alter, and drop tables using SQL commands:
-- Create a table with columns for ID, name, salary, and hire date
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE
);
-- Alter an existing table to add a new column for department
ALTER TABLE employees ADD department VARCHAR(50);
-- Drop a table (delete it from the database)
DROP TABLE employees;Data Insertion, Updating, and Deletion
SQL allows you to manipulate data using INSERT, UPDATE, and DELETE commands:
-- Insert a new employee record
INSERT INTO employees (id, name, salary, hire_date, department)
VALUES (1, 'Alice', 75000.00, '2022-01-15', 'HR');
-- Update the salary of employee with id 1
UPDATE employees
SET salary = 80000
WHERE id = 1;
-- Delete the employee record with id 1
DELETE FROM employees WHERE id = 1;3. Data Retrieval
SELECT Statement
The SELECT statement is used to retrieve data from a database:
SELECT * FROM employees; -- Retrieve all columns
SELECT name, salary FROM employees; -- Retrieve specific columnsFiltering Data with WHERE
The WHERE clause filters data based on specific conditions:
SELECT * FROM employees
WHERE salary > 60000 AND department = 'HR'; -- Filter records based on salary and departmentSorting Data with ORDER BY
The ORDER BY clause sorts the result set by one or more columns:
SELECT * FROM employees
ORDER BY salary DESC; -- Sort by salary in descending orderAliasing
You can use aliases to rename columns or tables for clarity:
SELECT name AS employee_name, salary AS monthly_salary FROM employees;4. Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single result.
Common Aggregate Functions
SELECT COUNT(*) AS total_employees, AVG(salary) AS average_salary
FROM employees; -- Count total employees and calculate the average salaryGROUP BY and HAVING
• GROUP BY is used to group rows sharing the same value in a column.
• HAVING filters groups based on aggregate conditions.
-- Find average salary by department
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
-- Filter groups with more than 5 employees
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;5. Joins
Joins are used to combine rows from two or more tables based on related columns.
Types of Joins
- ❤ 2
- 🔥 1
