SQL Proficiency 🚀

Welcome to the SQL Proficiency Document! This document serves to illustrate proficiency in the SQL coding lanuage.. Let's dive in! 💻

Table of Contents 📑

1. Introduction
2. Basic Queries
3. Advanced Queries
4. Data Manipulation
5. Joins & Unions
6. Aggregation
7. Window Functions
8. Indexes and Optimization
9. Transactions
10. Stored Procedures
11. Data Modeling
12. Bonus Tips
13. Resources

Introduction 🌐

SQL (Structured Query Language) is the language of databases. Whether you're fetching data or transforming it, SQL is your go-to tool. Let's explore its depths!

Basic Queries 🕵️‍♂️

Selecting Columns

SELECT column1, column2
FROM table
WHERE condition;

Filtering Results

SELECT *
FROM table
WHERE column = 'value';

Advanced Queries 🚀

Subqueries

SELECT *
FROM table
WHERE column IN (SELECT column FROM another_table);

Common Table Expressions

WITH cte_name AS (
  SELECT column
  FROM table
)
SELECT *
FROM cte_name;

Data Manipulation ✨

Inserting Data

INSERT INTO table (column1, column2)
VALUES ('value1', 'value2');

Updating Data

UPDATE table
SET column = 'new_value'
WHERE condition;

Joins and Unions 🔗

Inner join

SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column;

Union

SELECT column FROM table1
UNION
SELECT column FROM table2;

Aggregation 📊

Group By

SELECT column, COUNT(*)
FROM table
GROUP BY column;

Having clause

SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 1;

Window Functions 🪟

Rank and Partition (could also be Row_Number)

SELECT column, RANK() OVER (PARTITION BY category ORDER BY value DESC) AS ranking
FROM table;

Indexes and Optimization ⚙️

Indexing

CREATE INDEX index_name
ON table (column);

Query Optimization

EXPLAIN SELECT column
FROM table
WHERE condition;

Transactions 🔄

BEGIN TRANSACTION;

-- SQL Statements here

COMMIT;

Stored Procedures 📦

CREATE PROCEDURE procedure_name
AS
BEGIN
  -- SQL Statements here
END;

Data Modeling 🛠️

Bonus Tips

1. Use aliases for readability: SELECT column AS alias_name.
2. Learn to love and hate indexes—they can make or break performance.
3. Embrace the power of window functions for complex analyses.