Fish Touching🐟🎣

SQL Query

Nov 05, 20232 min read

image.png

Queries

image.png

SELECT [DISTINCT] <column expression list>  
FROM <single table>  
[WHERE <predicate>]  
[GROUP BY <column list>  
[HAVING <predicate>] ]  
[ORDER BY <column list>]  
[LIMIT <integer>];

Misc

  • .schema
  • .tables
  • SQL Null
    • represents an “unknown” or “missing” value.
    • If you do any calculation with NULL, you’ll just get NULL. For instance if x is NULL, then x > 3, 1 = x, and x + 4 all evaluate to NULL. Even x = NULL would evaluate to NULL
    • if you want to check whether x is NULL, then write x IS NULL or x IS NOT NULL instead.
    • when the comparison involves NULL values. UNKNOWN
    • NULL is falsey, meaning that WHERE NULL is just like WHERE FALSE. The row in question does not get included.
    • NULL column values are ignored by aggregate functions.
    • SQL Set
      • UNION
      • INTERSECT
      • EXCEPT
      • <ALL>: All possible value. (Allow Duplicates, Optional)
  • SQL View
  • SQL Common Table Expression
  • SQL Testing
  • Database Query Optimization

Types

Point Query

  • Specific record
-- Finding a specific customer by ID
SELECT * FROM customers WHERE customer_id = 12345;
 
-- Looking up a specific product
SELECT * FROM products WHERE product_code = 'ABC123';

Ad Hoc Query

Not regular, on-the-fly

-- A manager suddenly needs to know sales performance for a specific region last quarter
SELECT region, SUM(sales_amount) 
FROM sales 
WHERE region = 'Northeast' AND date BETWEEN '2024-01-01' AND '2024-03-31';
 
-- Investigating a customer complaint about order delays
SELECT o.order_id, o.order_date, o.shipped_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2024-06-01' AND o.shipped_date IS NULL;

Graph View

Backlinks