0
0
Menu

Top SQL concepts to know about

Level of details calculation analysis

Convert a shapefile to Polygon data in BigQuery

Gary
2 minute read
Dark Light

Several important concept in SQL that everyone should know

1. BASICS

SELECT (DISTINCT), FROM, WHERE, HAVING, GROUP BY, ORDER BY

2. Aggregate functions

COUNT, SUM, MAX, MIN, AVG, ROUND

IFNULL, COALESCE: COALESCE is the same as IFNULL when there are only 2 arguments, it can be used when there’s multiple arguments to check NULL. It returns the first non-NULL value in the list.

3. JOINS

INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN

CROSS JOIN: joining all value in both tables

JOIN is newer syntax of using WHERE on calling multiple tables

4. UNION / UNION ALL

Combining fields from different table. UNION ALL will include duplicates value.

5. Subquery

SELECT
    order_id,
    order_date,
    customer_id
FROM
    sales.orders
WHERE
    customer_id IN (
        SELECT
            customer_id
        FROM
            sales.customers
        WHERE
            city = 'New York'
    )
ORDER BY
    order_date DESC;

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

EXISTS search for existence of record and will return TRUE if exist

IN searches the values from the subquery

6. Common table expressions (CTEs)

WITH toronto_ppl as (
   SELECT DISTINCT name
   FROM population
   WHERE country = "Canada"
         AND city = "Toronto"
)
, avg_female_salary as (
   SELECT AVG(salary) as avgSalary
   FROM salaries
   WHERE gender = "Female"
)
SELECT name
       , salary
FROM People
WHERE name in (SELECT DISTINCT FROM toronto_ppl)
      AND salary >= (SELECT avgSalary FROM avg_female_salary)

CTE is the way to factor multiple or complex subquery which allows comprehensible SQL queries.

7. Views

Create a short cut so everytime different users can access the same dataset at different time when accessing the db

8. Routines

9. INDEXes

Index the tables will make faster query possible

10. CASE

SELECT name
       , CASE WHEN tenure < 1 THEN "analyst"
              WHEN tenure BETWEEN 1 and 3 THEN "associate"
              WHEN tenure BETWEEN 3 and 5 THEN "senior"
              WHEN tenure > 5 THEN "vp"
              ELSE "n/a"
         END AS seniority 
FROM employees

Advanced Topics

  • Functions, Stored Procedures, Packages
  • Pivoting data: CASE & PIVOT syntax
  • Hierarchical Queries
  • Cursors: Implicit and Explicit
  • Triggers
  • Dynamic SQL
  • Materialized Views
  • Query Optimization: Indexes
  • Query Optimization: Explain Plans
  • Query Optimization: Profiling
  • Data Modelling: Normal Forms, 1 through 3
  • Data Modelling: Primary & Foreign Keys
  • Data Modelling: Table Constraints
  • Data Modelling: Link/Corrollary Tables
  • Full Text Searching
  • XML
  • Isolation Levels
  • Entity Relationship Diagrams (ERDs), Logical and Physical
  • Transactions: COMMIT, ROLLBACK, Error Handling

Related Posts