Data Analysis Tools Breakdown Part 3: SQL

If you missed Part 2 read it first:

Data Analysis Tools Breakdown Part 2: Power BI

Make sure to read to the end.

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases.

We you can SQL to create Tables in the database.

And to also to retrieve an existing from database.

Here’s a detailed breakdown of its components, features, and use cases:

1. Data Definition Language (DDL)

DDL commands are used to define and modify the structure of database objects like tables, indexes, and schemas.

• CREATE: Creates a new database object (e.g., table, index).

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

• ALTER: Modifies an existing database object.

ALTER TABLE Employees ADD COLUMN Email VARCHAR(100);

• DROP: Deletes an existing database object.

DROP TABLE Employees;

• TRUNCATE: Removes all records from a table, but not the table itself.

TRUNCATE TABLE Employees;

2. Data Manipulation Language (DML)

DML commands are used to manage data within database objects.

• SELECT: Retrieves data from one or more tables.

SELECT FirstName, LastName FROM Employees WHERE EmployeeID = 1;

• INSERT: Adds new records to a table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2023-01-01');

• UPDATE: Modifies existing records in a table.

UPDATE Employees SET LastName = 'Smith' WHERE EmployeeID = 1;

• DELETE: Removes records from a table.

DELETE FROM Employees WHERE EmployeeID = 1;

3. Data Control Language (DCL)

DCL commands are used to control access to data in the database.

• GRANT: Gives user access privileges to the database.

GRANT SELECT ON Employees TO 'user1';

• REVOKE: Removes user access privileges to the database.

REVOKE SELECT ON Employees FROM 'user1';

4. Query Clauses and Keywords

• WHERE: Filters records based on specified conditions.

SELECT * FROM Employees WHERE HireDate > '2023-01-01';

• ORDER BY: Sorts the result set in ascending or descending order.

SELECT * FROM Employees ORDER BY LastName ASC;

• GROUP BY: Groups rows that have the same values in specified columns.

SELECT DepartmentID, COUNT(*) FROM Employees GROUP BY DepartmentID;

• HAVING: Filters groups based on specified conditions.

SELECT DepartmentID, COUNT(*) FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 5;

• JOIN: Combines rows from two or more tables based on a related column.

SELECT Employees.FirstName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

• UNION: Combines the result sets of two or more SELECT statements.

SELECT FirstName FROM Employees
UNION
SELECT FirstName FROM Contractors;

5. Functions

• Aggregate Functions: Perform calculations on a set of values and return a single value.

SELECT AVG(Salary) FROM Employees;
SELECT COUNT(*) FROM Employees;
SELECT MAX(Salary) FROM Employees;
SELECT MIN(Salary) FROM Employees;
SELECT SUM(Salary) FROM Employees;

• Scalar Functions: Perform operations on a single value and return a single value.

SELECT UPPER(FirstName) FROM Employees;
SELECT LOWER(LastName) FROM Employees;
SELECT LEN(FirstName) FROM Employees;

6. Constraints

• PRIMARY KEY: Uniquely identifies each record in a table.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50)
);

• FOREIGN KEY: Ensures referential integrity between two tables.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

• UNIQUE: Ensures all values in a column are unique.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

• NOT NULL: Ensures a column cannot have a NULL value.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL
);

7. Indexes

• CREATE INDEX: Creates an index on a table to speed up queries.

CREATE INDEX idx_lastname ON Employees (LastName);

• DROP INDEX: Deletes an index.

DROP INDEX idx_lastname ON Employees;

8. Views

• CREATE VIEW: Creates a virtual table based on the result set of a SQL query.

CREATE VIEW EmployeeView AS
SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 1;

• DROP VIEW: Deletes a view.

DROP VIEW EmployeeView;

9. Subqueries

• A query nested inside another query.

SELECT FirstName FROM Employees WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');

10. Stored Procedures and Functions

• Stored Procedure: A prepared SQL code that you can save and reuse.

CREATE PROCEDURE GetEmployeeByID @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

• Function: A reusable piece of code that returns a value.

CREATE FUNCTION GetEmployeeName (@EmployeeID INT)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN (SELECT FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = @EmployeeID);
END;

11. Triggers

• CREATE TRIGGER: A set of SQL statements that automatically executes in response to certain events on a table.

CREATE TRIGGER trg_after_insert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (Action, Timestamp) VALUES ('INSERT', NOW());
END;

• DROP TRIGGER: Deletes a trigger.

DROP TRIGGER trg_after_insert;

12. Normalization

• 1NF: Eliminate duplicate columns from the same table.

• 2NF: Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

• 3NF: Remove columns that are not dependent on the primary key.

13. Denormalization

• The process of combining tables to improve read performance at the expense of write performance.

14. Security

• Users and Roles: Managing user access and roles.

CREATE USER 'user1' IDENTIFIED BY 'password';
CREATE ROLE 'admin';
GRANT 'admin' TO 'user1';

• Encryption: Encrypting sensitive data.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    SSN VARBINARY(128) ENCRYPTED
);

15. Performance Tuning

• Indexing: Creating indexes to speed up queries.

• Query Optimization: Rewriting queries for better performance.

• Execution Plans: Analyzing execution plans to identify bottlenecks.

16. Advanced Features

• Window Functions: Perform calculations across a set of table rows related to the current row.

SELECT EmployeeID, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

• Common Table Expressions (CTEs): Temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

WITH CTE_Employees AS (
    SELECT EmployeeID, FirstName FROM Employees WHERE DepartmentID = 1
)
SELECT * FROM CTE_Employees;

• Recursive Queries: Queries that refer to themselves.

WITH RECURSIVE Subordinates AS (
    SELECT EmployeeID, ManagerID FROM Employees WHERE EmployeeID = 1
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID FROM Employees e
    INNER JOIN Subordinates s ON e.ManagerID = s.EmployeeID
)
SELECT * FROM Subordinates;

17. Database Design

• ER Diagrams: Entity-Relationship diagrams to design the database schema.

• Normalization: Organizing data to reduce redundancy and improve data integrity.

• Denormalization: Combining tables to improve read performance.

18. Database Administration

• User Management: Creating and managing users and roles.

• Backup and Recovery: Ensuring data is backed up and can be recovered in case of failure.

• Performance Monitoring: Monitoring and tuning database performance.

19. NoSQL vs SQL

• SQL: Relational databases like MySQL, PostgreSQL, SQL Server.

• NoSQL: Non-relational databases like MongoDB, Cassandra, Redis.

20. Cloud Databases

• AWS RDS: Amazon Relational Database Service.

• Azure SQL Database: Microsoft's cloud-based SQL database.

• Google Cloud SQL: Google's cloud-based SQL database.

21. Big Data and SQL

• Hadoop: Distributed storage and processing of large data sets.

• Spark: Fast, general-purpose cluster computing system.

• Hive: SQL-like interface to query data stored in Hadoop.

22. Data Warehousing

• ETL: Extract, Transform, Load processes.

• Data Marts: Subsets of data warehouses focused on specific business lines.

• OLAP: Online Analytical Processing for complex queries.

If YOU read this far Congratulations on mastering SQL.

If want video explanation on each step.

Check out my SQL course in the website link below: