SQL | Basic

Introduction

• Structured Query Language : SQL is a domain-specific language used in programming and designed for managing data held in a RDBMS

• Purpose

  • DB design, modeling, managing
    • CREATE, ALTER, DROP, INSERT, DELETE
  • Data Analysis
    • SELECT, FROM, WHERE,JOIN,HAVING,GROUP BY,ORDER BY

---

1. SELECT/FROM

/* selecte certain column */
SELECT column1, column2, ...
FROM table_name;

/* select all columns using wildcard */
SELECT *
FROM table_name;

---

2. WHERE

•  The WHERE clause is used to filter records

/* filter columns with certain condition */
WHERE [column_name] BETWEEN [condition1] AND [condition2]
/* filter with pattern (%, _) */
WHERE [column_name] LIKE 'pattern'

---

3. CASE

• CASE : evaluates a list of conditions and returns one of multiple possible result expressions.
  • CASE (Transact-SQL) - SQL Server | Microsoft Learn

SELECT *,
    CASE WHEN [column_name] BETWEEN 0.00 AND 0.25 THEN 'Q1'
         WHEN [column_name] BETWEEN 0.25 AND 0.50 THEN 'Q2'
         WHEN [column_name] BETWEEN 0.50 AND 0.75 THEN 'Q3'
         WHEN [column_name] BETWEEN 0.75 AND 1.00 THEN 'Q4'
         END AS quaritle
FROM [table_name]

---

4. Window Functions

• ROW_NUMBER

/* return the value of rank (row number) ordered by [column_to_sort] in partition of [column_to_group] */
ROW_NUMBER() OVER(PARTITION BY [column_to_group] ORDER BY [column_to_sort] DESC)

---

5. GROUP BY

• GROUP BY: is used to aggregate rows that have the same values in one or more columns
  • collapses multiple rows into groups, and then you can apply aggregate functions
    • e.g. COUNT, SUM, AVG

SELECT col1, col2, col3, COUNT(*)
FROM my_table
GROUP BY col1, col2, col3
/* You can reference SELECT column positions by number in GROUP BY*/
GROUP BY 1, 2, 3;

---

9. Null

• IFNULL(COLUMN, "alternative")