The Data Analyst Toolkit

SQL

SQL (Structured Query Language) is a powerful domain-specific language used for managing and manipulating relational databases. It serves as the standard language for interacting with database management systems (DBMS) such as MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, and more. SQL allows you to perform a wide range of operations on data stored in a relational database, including retrieving, inserting, updating, and deleting data, as well as defining and modifying the structure of the database itself.

Here’s an overall introduction to SQL:

Key Concepts:

  1. Relational Databases: SQL is closely associated with relational databases, where data is organized into structured tables (referred to as relations) consisting of rows (records) and columns (attributes).
  2. Data Manipulation Language (DML): SQL includes commands for interacting with data stored in the database. The primary DML commands are SELECT, INSERT, UPDATE, and DELETE, which respectively allow you to retrieve, add, modify, and remove data.
  3. Data Definition Language (DDL): SQL is used to define the structure of the database and its objects. DDL commands like CREATE, ALTER, and DROP are used to create, modify, and delete database objects such as tables, indexes, and views.
  4. Data Control Language (DCL): SQL includes commands for controlling access to the database. GRANT and REVOKE are used to assign and revoke permissions on database objects.

Basic SQL Statements:

Beginner:

  • SELECT: Retrieves data from a database.
    • Example: SELECT * FROM Customers;
  • INSERT: Adds new records into a table.
    • Example: INSERT INTO Orders (OrderID, CustomerID) VALUES (12345, 'Cust001');
  • UPDATE: Modifies existing records in a table.
    • Example: UPDATE Products SET Price = 29.99 WHERE ProductID = 1001;
  • DELETE: Removes records from a table.
    • Example: DELETE FROM Employees WHERE EmployeeID = 101;
  • WHERE: Filters rows based on a condition.
    • Example: SELECT * FROM Orders WHERE OrderDate >= '2023-01-01';

Intermediate:

  • JOIN: Combines data from multiple tables based on a related column.
    • Example: SELECT Customers.Name, Orders.OrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
  • GROUP BY: Groups rows with the same values in specified columns.
    • Example: SELECT Category, AVG(Price) FROM Products GROUP BY Category;
  • HAVING: Filters groups produced by GROUP BY using aggregate functions.
    • Example: SELECT Category, AVG(Price) FROM Products GROUP BY Category HAVING AVG(Price) > 50;
  • ORDER BY: Sorts the result set based on specified columns.
    • Example: SELECT ProductName, Price FROM Products ORDER BY Price DESC;
  • LIMIT/OFFSET: Restricts the number of rows returned by a query.
    • Example: SELECT * FROM Customers LIMIT 10 OFFSET 20;

Advanced:

  • UNION: Combines the result sets of two or more SELECT statements.
    • Example: SELECT ProductName FROM Products WHERE Category = 'Electronics' UNION SELECT ProductName FROM Products WHERE Category = 'Appliances';
  • SUBQUERY: A query nested within another query.
    • Example: SELECT ProductName FROM Products WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName = 'Electronics');
  • CASE: Adds conditional logic to queries.
    • Example: SELECT ProductName, CASE WHEN Price > 100 THEN 'Expensive' ELSE 'Affordable' END AS PriceCategory FROM Products;
  • INDEX: Improves query performance by creating an index on one or more columns.
    • Example: CREATE INDEX idx_LastName ON Employees (LastName);
  • VIEW: A virtual table created from a query for easier access and readability.
    • Example: CREATE VIEW HighValueOrders AS SELECT OrderID, TotalAmount FROM Orders WHERE TotalAmount > 1000;

SQL JOINS

Here are examples of different types of joins in SQL:

  1. INNER JOIN:
    • Retrieves matching rows from both tables.
    SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
  2. LEFT JOIN (or LEFT OUTER JOIN):
    • Retrieves all rows from the left table and matching rows from the right table.
    SELECT Employees.LastName, Employees.FirstName, Orders.OrderID FROM Employees LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;
  3. RIGHT JOIN (or RIGHT OUTER JOIN):
    • Retrieves all rows from the right table and matching rows from the left table.
    SELECT Orders.OrderID, Customers.CustomerName FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
  4. FULL JOIN (or FULL OUTER JOIN):
    • Retrieves all rows from both tables, with NULL values where no match is found.
    SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
  5. SELF JOIN:
    • Joins a table with itself, often used for hierarchical relationships.
    SELECT E1.EmployeeName, E2.EmployeeName AS ManagerName FROM Employees AS E1 LEFT JOIN Employees AS E2 ON E1.ManagerID = E2.EmployeeID;
  6. CROSS JOIN:
    • Combines all rows from the first table with all rows from the second table.
    SELECT Products.ProductName, Categories.CategoryName FROM Products CROSS JOIN Categories;
  7. NATURAL JOIN:
    • Automatically matches columns with the same name in both tables.
    SELECT Customers.CustomerName, Orders.OrderID FROM Customers NATURAL JOIN Orders;

WINDOWS FUNCTIONS

Window functions are powerful tools in SQL that allow you to perform calculations across a set of table rows related to the current row. Here are a few examples of window functions:

  1. ROW_NUMBER():
    • Assigns a unique integer to each row within a result set.
    SELECT ProductName, Price, ROW_NUMBER() OVER (ORDER BY Price) AS Rank FROM Products;
  2. RANK():
    • Assigns a unique rank to rows based on specified criteria.SELECT ProductName, Price, RANK() OVER (PARTITION BY CategoryID ORDER BY Price) AS Rank FROM Products;
  3. DENSE_RANK():
    • Similar to RANK(), but without gaps in ranking when there are ties.SELECT ProductName, Price, DENSE_RANK() OVER (PARTITION BY CategoryID ORDER BY Price) AS Rank FROM Products;
  4. NTILE():
    • Divides rows into specified number of roughly equal-sized groups.SELECT CustomerName, OrderID, NTILE(4) OVER (ORDER BY OrderDate) AS Quartile FROM Orders;
  5. SUM() with OVER():
    • Calculates a running total within a partition of rows.
    SELECT OrderDate, TotalAmount, SUM(TotalAmount) OVER (ORDER BY OrderDate) AS RunningTotal FROM Orders;
  6. LEAD() and LAG():
    • Accesses data from subsequent or previous rows within a partition.
    SELECT ProductName, Price, LAG(Price) OVER (ORDER BY Price) AS PrevPrice, LEAD(Price) OVER (ORDER BY Price) AS NextPrice FROM Products;
  7. FIRST_VALUE() and LAST_VALUE():
    • Fetches the first and last value in a partition of rows.
    SELECT ProductName, Price, FIRST_VALUE(Price) OVER (PARTITION BY CategoryID ORDER BY Price) AS FirstPrice, LAST_VALUE(Price) OVER (PARTITION BY CategoryID ORDER BY Price) AS LastPrice FROM Products;
What are your feelings