SQL Essentials for Data Analysts

What is a Database?

• A database is an organized collection of structured information, or data, typically stored electronically in a computer system.
• Databases are managed by database management systems (DBMS)
• which are software tools that allow users to create, read, update, and delete data in the database.
• Databases are essential for storing, organizing, and managing large amounts of data efficiently,
• and they are used in various applications, from websites to enterprise systems.

Key Components of a Database

• Tables: The primary structure in a database, where data is stored in rows and columns, similar to a spreadsheet.
• Fields (Columns): Define the type of data that can be stored in a table, like names, dates, numbers, etc.
• Records (Rows): Individual entries in a table that contain data for a single item or entity.
• Primary Key: A unique identifier for each record in a table, ensuring that each entry is distinct.
• Relationships: Connections between tables allow data to be linked and queried meaningfully.

Types of Databases

• Relational Databases: Use structured tables and relationships, examples include MySQL, PostgreSQL, and Oracle.
• NoSQL Databases: Store data in formats other than tables, such as documents, key-value pairs, or graphs, examples include MongoDB and Cassandra.
• In-Memory Databases: Store data in the main memory (RAM) for faster access, examples include Redis and Memcached.
• Cloud Databases: Hosted on cloud services and accessible over the internet, examples include Amazon RDS and Google Cloud SQL.

Types of SQL Commands

Data Definition Language (DDL)

• CREATE Creates a new table or database.
• ALTER Modifies the structure of an existing table.
• DROP Deletes tables or databases.
• TRUNCATE Removes all records from a table, but not the table itself.
  

1-- Create a new table
2CREATE TABLE departments (
3    department_id INT PRIMARY KEY,
4    department_name VARCHAR(50),
5    location VARCHAR(100)
6);
7
8-- Alter the table to add a new column
9ALTER TABLE departments
10ADD manager_id INT;
11
12-- Rename the table
13ALTER TABLE departments
14RENAME TO dept_info;
15
16-- Truncate the table (removes all records but keeps the table structure)
17TRUNCATE TABLE dept_info;
18
19-- Drop the table
20DROP TABLE dept_info;
21
22
23

Data Manipulation Language (DML)

• SELECT Retrieves data from a table.
• INSERT Adds new records to a table.
• UPDATE Modifies existing records in a table.
• DELETE Removes records from a table.

1-- Insert a new record into the employees table
2INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
3VALUES (101, 'Jane', 'Doe', '2024-08-13', 60000);
4
5-- Update the salary of the newly added employee
6UPDATE employees
7SET salary = 65000
8WHERE employee_id = 101;
9
10-- Delete the record of the employee with ID 101
11DELETE FROM employees
12WHERE employee_id = 101;
13

Data Control Language (DCL)

• GRANT Gives users access privileges to the database.
• REVOKE Removes access privileges from users.

1-- Grant SELECT and INSERT permissions on the 'employees' table to a user
2GRANT SELECT, INSERT ON employees TO user1;
3
4-- Revoke the INSERT permission from the user
5REVOKE INSERT ON employees FROM user1;
6

Transaction Control Language (TCL)

• COMMIT Saves all changes made in the current transaction.
• ROLLBACK Undoes changes made in the current transaction.
• SAVEPOINT Sets a savepoint within a transaction for partial rollback.
• SET TRANSACTION Defines properties for the transaction.

1-- Start a transaction
2BEGIN;
3
4-- Insert a new record into the employees table
5INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
6VALUES (102, 'Alice', 'Smith', '2024-08-13', 70000);
7
8-- Update the salary of the newly added employee
9UPDATE employees
10SET salary = 75000
11WHERE employee_id = 102;
12
13-- Save the current state of the transaction
14SAVEPOINT before_delete;
15
16-- Delete the record of the employee with ID 102
17DELETE FROM employees
18WHERE employee_id = 102;
19
20-- Rollback to the savepoint if needed
21--ROLLBACK TO SAVEPOINT before_delete;
22
23-- Commit the transaction to save all changes
24COMMIT;
25
26-- Rollback the entire transaction if needed (this would undo all changes)
27--ROLLBACK;
28

Data Query Language (DQL)

• SELECT: The only command in DQL, used to query the database and fetch data based on conditions.

1SELECT department, COUNT(*) AS number_of_employees, AVG(salary) AS average_salary
2FROM employees
3WHERE hire_date > '2023-01-01'
4GROUP BY department
5ORDER BY number_of_employees DESC;
6

Difference Between SQL and MySQL

• It defines how to query, update, and manage data through commands like `SELECT`, `INSERT`, `UPDATE`, and `DELETE`.
• SQL is used by various database systems and follows a universal syntax for interacting with databases.
  

• It provides a specific environment and tools for creating, managing, and querying databases.
• MySQL uses SQL as its language for data manipulation but includes additional features and optimizations specific to the MySQL system.

SQL Roadmap

Introduction to SQL

Basics of SQL

SQL Environment

Data Types

Basic Functions

Intermediate SQL

Joins

Subqueries

Aliases

Aggregations and Grouping

Sorting and Filtering

Advanced SQL

Advanced Functions

Window Functions

CTES (common Table Expressions)

Views

Transactions

Indexes

Database Management

Database Design

Schema Creation

CRUD Operation

Constraints

SQL For Data Analysis

Descriptive Statistics

Data Visualization

Complex Reporting

Case Studies

Real World Application

Data Extraction

Data Transforming

Data Loading

ETL Processes

Project and Practice

Resources to Learn and practice

Conclusion