플그래밍

예제로 배우는 SQL

훗티v 2024. 3. 3. 19:37
반응형

SELECT

 

FROM

 

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT student_id, name, age
FROM students
WHERE age >= 20;
| student_id | name    | age |
|------------|---------|-----|
| 1          | John    | 18  |
| 2          | Alice   | 21  |
| 3          | Michael | 19  |
| 4          | Sarah   | 22  |
| 5          | David   | 20  |
| student_id | name  | age |
|------------|-------|-----|
| 2          | Alice | 21  |
| 4          | Sarah | 22  |
| 5          | David | 20  |

 

 

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

SELECT category, SUM(revenue) AS total_revenue
FROM sales
GROUP BY category;
| product_id | category | revenue |
|------------|----------|---------|
| 1          | A        | 100     |
| 2          | B        | 150     |
| 3          | A        | 120     |
| 4          | B        | 200     |
| 5          | A        | 180     |
| category | total_revenue |
|----------|---------------|
| A        | 400           |
| B        | 350           |

 

 

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

SELECT category, SUM(revenue) AS total_revenue
FROM sales
GROUP BY category
HAVING SUM(revenue) > 200;
| product_id | category | revenue |
|------------|----------|---------|
| 1          | A        | 100     |
| 2          | B        | 150     |
| 3          | A        | 120     |
| 4          | B        | 200     |
| 5          | A        | 180     |
| category | total_revenue |
|----------|---------------|
| B        | 350           |
| A        | 400           |

 

 

WHERE와 HAVING 동시 사용 예제

SELECT department, AVG(score) AS average_score
FROM students
WHERE score > 65
GROUP BY department
HAVING AVG(score) > 75;
| student_id | department | score |
|------------|------------|-------|
| 1          | Biology    | 85    |
| 2          | Physics    | 70    |
| 3          | Chemistry  | 92    |
| 4          | Biology    | 60    |
| 5          | Physics    | 78    |
| department | average_score |
|------------|---------------|
| Physics    | 78            |

 

 

DECODE(expression, search1, result1, search2, result2, ..., default_result)

SELECT student_id,
       DECODE(
           CASE
               WHEN score >= 90 THEN 'A'
               WHEN score >= 80 THEN 'B'
               WHEN score >= 70 THEN 'C'
               WHEN score >= 60 THEN 'D'
               ELSE 'F'
           END,
           'A', 'Excellent',
           'B', 'Good',
           'C', 'Satisfactory',
           'D', 'Pass',
           'F', 'Fail'
       ) AS grade
FROM students;
| student_id | score |
|------------|-------|
| 1          | 85    |
| 2          | 70    |
| 3          | 92    |
| 4          | 60    |
| 5          | 78    |
| student_id | grade         |
|------------|---------------|
| 1          | Satisfactory  |
| 2          | Satisfactory  |
| 3          | Excellent     |
| 4          | Pass          |
| 5          | Good          |

 

 

NVL(expression, value_if_null)

SELECT student_id, NVL(age, 0) AS age
FROM students;
| student_id | age |
|------------|-----|
| 1          | 18  |
| 2          | NULL|
| 3          | 20  |
| 4          | 22  |
| 5          | NULL|
| student_id | age |
|------------|-----|
| 1          | 18  |
| 2          | 0   |
| 3          | 20  |
| 4          | 22  |
| 5          | 0   |

 

 

NVL2(expression, value_if_not_null, value_if_null)

SELECT student_id,
       NVL2(email, 'Provided', 'Not Provided') AS email_status
FROM students;
| student_id | email                     |
|------------|---------------------------|
| 1          | john.doe@example.com      |
| 2          | NULL                      |
| 3          | jane.smith@example.com    | 
| 4          | NULL                      |
| 5          | sarah.jackson@example.com |
| student_id | email_status   |
|------------|----------------|
| 1          | Provided       |
| 2          | Not Provided  |
| 3          | Provided       |
| 4          | Not Provided  |
| 5          | Provided       |

 

 

COALESCE(expression1, expression2, ...)

SELECT employee_id,
       COALESCE(first_name || ' ' || middle_name, first_name) AS full_name
FROM employees;
| employee_id | first_name | middle_name |
|-------------|------------|-------------|
| 1           | John       | NULL        |
| 2           | Alice      | Marie       |
| 3           | Michael    | James       |
| 4           | NULL       | David       |
| 5           | Sarah      | NULL        |

 

| employee_id | full_name       |
|-------------|-----------------|
| 1           | John            |
| 2           | Alice Marie     |
| 3           | Michael James   |
| 4           | David           |
| 5           | Sarah           |​

 

 

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END 

SELECT student_id,
       CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 80 THEN 'B'
           WHEN score >= 70 THEN 'C'
           WHEN score >= 60 THEN 'D'
           ELSE 'F'
       END AS grade
FROM students;
| student_id | score |
|------------|-------|
| 1          | 85    |
| 2          | 70    |
| 3          | 92    |
| 4          | 60    |
| 5          | 78    |
| student_id | grade |
|------------|-------|
| 1          | B     |
| 2          | C     |
| 3          | A     |
| 4          | D     |
| 5          | C     |

 

 

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

SELECT name, age
FROM students
ORDER BY age DESC;
| student_id | name    | age |
|------------|---------|-----|
| 1          | John    | 18  |
| 2          | Alice   | 21  |
| 3          | Michael | 19  |
| 4          | Sarah   | 22  |
| 5          | David   | 20  |
| name    | age |
|---------|-----|
| Sarah   | 22  |
| David   | 20  |
| Michael | 19  |
| Alice   | 21  |
| John    | 18  |

 

 

(EQUI JOIN)

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

| student_id | name    | department |
|------------|---------|------------|
| 1          | John    | Biology    |
| 2          | Alice   | Physics    |
| 3          | Michael | Chemistry  |
| 4          | Sarah   | Biology    |
| 5          | David   | Physics    |
| student_id | grade |
|------------|-------|
| 1          | A     |
| 2          | B     |
| 3          | A     |
| 4          | C     |
| 5          | B     |
SELECT students.name, grades.grade
FROM students
JOIN grades
ON students.student_id = grades.student_id;

---------------------------------------------------

SELECT students.name, grades.grade
FROM students, grades
WHERE students.student_id = grades.student_id;
| name    | grade |
|---------|-------|
| John    | A     |
| Alice   | B     |
| Michael | A     |
| Sarah   | C     |
| David   | B     |

 

 

(NON-EQUI JOIN)

SELECT c.customer_id, c.join_date, o.order_id, o.order_date, o.order_amount
FROM customers c, orders o
WHERE o.customer_id = c.customer_id
AND o.order_date <= c.join_date + INTERVAL 10 DAY;

| order_id | customer_id | order_date | order_amount |
|----------|-------------|------------|--------------|
| 1        | 101         | 2023-01-05 | 150          |
| 2        | 102         | 2023-01-08 | 200          |
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |
| customer_id | join_date  |
|-------------|------------|
| 101         | 2022-12-20 |
| 102         | 2023-01-01 |
| 103         | 2023-01-03 |
| 104         | 2023-01-10 |
SELECT c.customer_id, c.join_date, o.order_id, o.order_date, o.order_amount
FROM customers c, orders o
WHERE o.customer_id = c.customer_id
AND o.order_date <= c.join_date + INTERVAL 10 DAY;
| customer_id | join_date  | order_id | order_date | order_amount |
|-------------|------------|----------|------------|--------------|
| 101         | 2022-12-20 | 1        | 2023-01-05 | 150          |
| 101         | 2022-12-20 | 4        | 2023-01-12 | 250          |
| 102         | 2023-01-01 | 2        | 2023-01-08 | 200          |
| 103         | 2023-01-03 | 3        | 2023-01-10 | 300          |

 

 

(3개의 테이블 JOIN)

| student_id | name    | age | gender |
|------------|---------|-----|--------|
| 1          | John    | 20  | Male   |
| 2          | Alice   | 22  | Female |
| 3          | Michael | 21  | Male   |
| course_id | course_name  | instructor |
|-----------|--------------|------------|
| 101       | Math         | Smith      |
| 102       | Physics      | Johnson    |
| 103       | Chemistry    | Brown      |
| enrollment_id | student_id | course_id |
|---------------|------------|-----------|
| 1             | 1          | 101       |
| 2             | 2          | 102       |
| 3             | 3          | 101       |
| 4             | 1          | 103       |
| 5             | 3          | 102       |
SELECT students.name AS student_name, students.age, students.gender,
       courses.course_name, courses.instructor
FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.course_id;
| student_name | age | gender | course_name | instructor |
|--------------|-----|--------|-------------|------------|
| John         | 20  | Male   | Math        | Smith      |
| Alice        | 22  | Female | Physics     | Johnson    |
| Michael      | 21  | Male   | Math        | Smith      |
| John         | 20  | Male   | Chemistry   | Brown      |
| Michael      | 21  | Male   | Physics     | Johnson    |

 

 

(SELF JOIN)

| employee_id | employee_name | manager_id |
|-------------|---------------|------------|
| 1           | John          | NULL       |
| 2           | Alice         | 1          |
| 3           | Michael       | 1          |
| 4           | Sarah         | 2          |
| 5           | David         | 3          |
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
| employee | manager |
|----------|---------|
| John     | NULL    |
| Alice    | John    |
| Michael  | John    |
| Sarah    | Alice   |
| David    | Michael |

 

JOIN... ON...

 

JOIN... USING...

 

NATURAL JOIN

 

CROSS JOIN

 

LEFT OUTER JOIN

 

RIGHT OUTER JOIN

 

FULL OUTER JOIN

(LEFT OUTER JOIN + RIGHT OUTER JOIN, 중복된 행은 한번만 표시)

 

SUB QUERY

| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1        | 101         | 2023-01-05 | 150          |
| 2        | 102         | 2023-01-08 | 200          |
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |
SELECT order_id, customer_id, order_date, total_amount,
       (SELECT AVG(total_amount) FROM orders) AS avg_total_amount
FROM orders;
| order_id | customer_id | order_date | total_amount | avg_total_amount |
|----------|-------------|------------|--------------|------------------|
| 1        | 101         | 2023-01-05 | 150          | 216.0            |
| 2        | 102         | 2023-01-08 | 200          | 216.0            |
| 3        | 103         | 2023-01-10 | 300          | 216.0            |
| 4        | 101         | 2023-01-12 | 250          | 216.0            |
| 5        | 104         | 2023-01-15 | 180          | 216.0            |

 

 

SUB QUERY | INLINE VIEW

| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1        | 101         | 2023-01-05 | 150          |
| 2        | 102         | 2023-01-08 | 200          |
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |
SELECT o.order_id, o.customer_id, o.order_date, o.total_amount, avg_total.avg_total_amount
FROM orders o
CROSS JOIN (SELECT AVG(total_amount) AS avg_total_amount FROM orders) avg_total;
| order_id | customer_id | order_date | total_amount | avg_total_amount |
|----------|-------------|------------|--------------|------------------|
| 1        | 101         | 2023-01-05 | 150          | 216.0            |
| 2        | 102         | 2023-01-08 | 200          | 216.0            |
| 3        | 103         | 2023-01-10 | 300          | 216.0            |
| 4        | 101         | 2023-01-12 | 250          | 216.0            |
| 5        | 104         | 2023-01-15 | 180          | 216.0            |

 

 

SUB QUERY | WHERE

| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1        | 101         | 2023-01-05 | 150          |
| 2        | 102         | 2023-01-08 | 200          |
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);
| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |

 

 

SUB QUERY | WHERE, ANY

| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1        | 101         | 2023-01-05 | 150          |
| 2        | 102         | 2023-01-08 | 200          |
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE total_amount > ANY (SELECT total_amount FROM orders WHERE customer_id = 101);
| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |

 

 

SUB QUERY | WHERE, ALL

| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1        | 101         | 2023-01-05 | 150          |
| 2        | 102         | 2023-01-08 | 200          |
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE total_amount > ALL (SELECT total_amount FROM orders WHERE customer_id = 101);
| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 3        | 103         | 2023-01-10 | 300          |

 

 

SUB QUERY | WHERE, IN

| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 1        | 101         | 2023-01-05 | 150          |
| 2        | 102         | 2023-01-08 | 200          |
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |
| 5        | 104         | 2023-01-15 | 180          |
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 200);
| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|--------------|
| 3        | 103         | 2023-01-10 | 300          |
| 4        | 101         | 2023-01-12 | 250          |

 

 

UNION (중복 데이터 한번 출력)

 

UNION ALL (중복 데이터 모두 출력)

 

INTERSECT

 

MINUS

 

COUNT()

 

SUM()

 

AVG() 

 

MIN()

 

MAX()

 

VARIANCE()

 

STDDEV()

 

GROUPING SETS()

| product | region | month | amount |
|---------|--------|-------|--------|
| A       | North  | Jan   | 100    |
| A       | South  | Jan   | 150    |
| B       | North  | Jan   | 200    |
| B       | South  | Jan   | 250    |
| A       | North  | Feb   | 120    |
| A       | South  | Feb   | 180    |
| B       | North  | Feb   | 220    |
| B       | South  | Feb   | 280    |
SELECT product, region, month, SUM(amount) AS total_amount
FROM sales
GROUP BY GROUPING SETS ((product, region, month), (product, month), (product), ());
| product | region | month | total_amount |
|---------|--------|-------|--------------|
| A       | North  | Jan   | 100          |
| A       | South  | Jan   | 150          |
| B       | North  | Jan   | 200          |
| B       | South  | Jan   | 250          |
| A       | North  | Feb   | 120          |
| A       | South  | Feb   | 180          |
| B       | North  | Feb   | 220          |
| B       | South  | Feb   | 280          |
| A       |        | Jan   | 250          |
| B       |        | Jan   | 450          |
| A       |        | Feb   | 300          |
| B       |        | Feb   | 500          |
|         |        |       | 1250         |

 

 

ROLLUP()

| region | product | month | amount |
|--------|---------|-------|--------|
| North  | A       | Jan   | 100    |
| North  | A       | Feb   | 120    |
| North  | B       | Jan   | 200    |
| North  | B       | Feb   | 220    |
| South  | A       | Jan   | 150    |
| South  | A       | Feb   | 180    |
| South  | B       | Jan   | 250    |
| South  | B       | Feb   | 280    |
SELECT region, product, SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(region, product);
| region | product | total_amount |
|--------|---------|--------------|
| North  | A       | 220          |
| North  | B       | 420          |
| North  | NULL    | 640          |
| South  | A       | 330          |
| South  | B       | 530          |
| South  | NULL    | 860          |
| NULL   | NULL    | 1500         |

 

 

CUBE()

| region | product | month | amount |
|--------|---------|-------|--------|
| North  | A       | Jan   | 100    |
| North  | A       | Feb   | 120    |
| North  | B       | Jan   | 200    |
| North  | B       | Feb   | 220    |
| South  | A       | Jan   | 150    |
| South  | A       | Feb   | 180    |
| South  | B       | Jan   | 250    |
| South  | B       | Feb   | 280    |
SELECT region, product, month, SUM(amount) AS total_amount
FROM sales
GROUP BY CUBE(region, product, month);
| region | product | month | total_amount |
|--------|---------|-------|--------------|
| NULL   | NULL    | NULL  | 1500         |
| NULL   | NULL    | Feb   | 1050         |
| NULL   | NULL    | Jan   | 450          |
| NULL   | A       | NULL  | 700          |
| NULL   | A       | Feb   | 300          |
| NULL   | A       | Jan   | 400          |
| NULL   | B       | NULL  | 800          |
| NULL   | B       | Feb   | 750          |
| NULL   | B       | Jan   | 50           |
| North  | NULL    | NULL  | 900          |
| North  | NULL    | Feb   | 340          |
| North  | NULL    | Jan   | 560          |
| North  | A       | NULL  | 220          |
| North  | A       | Feb   | 120          |
| North  | A       | Jan   | 100          |
| North  | B       | NULL  | 680          |
| North  | B       | Feb   | 220          |
| North  | B       | Jan   | 460          |
| South  | NULL    | NULL  | 600          |
| South  | NULL    | Feb   | 710          |
| South  | NULL    | Jan   | 110          |
| South  | A       | NULL  | 480          |
| South  | A       | Feb   | 180          |
| South  | A       | Jan   | 300          |
| South  | B       | NULL  | 120          |
| South  | B       | Feb   | 530          |
| South  | B       | Jan   | 50           |

 

 

SUM OVER

| order_id | date       | amount |
|----------|------------|--------|
| 1        | 2023-01-01 | 100    |
| 2        | 2023-01-02 | 150    |
| 3        | 2023-01-03 | 200    |
| 4        | 2023-01-04 | 250    |
| 5        | 2023-01-05 | 300    |
SELECT order_id, date, amount,
       SUM(amount) OVER (ORDER BY date) AS cumulative_sum
FROM sales;
| order_id | date       | amount | cumulative_sum |
|----------|------------|--------|----------------|
| 1        | 2023-01-01 | 100    | 100            |
| 2        | 2023-01-02 | 150    | 250            |
| 3        | 2023-01-03 | 200    | 450            |
| 4        | 2023-01-04 | 250    | 700            |
| 5        | 2023-01-05 | 300    | 1000           |

 

 

AVG OVER

| order_id | date       | amount |
|----------|------------|--------|
| 1        | 2023-01-01 | 100    |
| 2        | 2023-01-02 | 150    |
| 3        | 2023-01-03 | 200    |
| 4        | 2023-01-04 | 250    |
| 5        | 2023-01-05 | 300    |
SELECT order_id, date, amount,
       AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM sales;
| order_id | date       | amount | moving_average |
|----------|------------|--------|----------------|
| 1        | 2023-01-01 | 100    | 100            |
| 2        | 2023-01-02 | 150    | 125            |
| 3        | 2023-01-03 | 200    | 150            |
| 4        | 2023-01-04 | 250    | 200            |
| 5        | 2023-01-05 | 300    | 250            |

 

MIN OVER

 

MAX OVER

 

COUNT OVER

 

ROWS

| date       | amount |
|------------|--------|
| 2023-01-01 | 100    |
| 2023-01-02 | 150    |
| 2023-01-03 | 200    |
| 2023-01-04 | 250    |
| 2023-01-05 | 300    |
SELECT date, amount,
       AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_average
FROM sales;
| date       | amount | rolling_average |
|------------|--------|-----------------|
| 2023-01-01 | 100    | 100             |
| 2023-01-02 | 150    | 125             |
| 2023-01-03 | 200    | 150             |
| 2023-01-04 | 250    | 200             |
| 2023-01-05 | 300    | 250             |

 

 

ROWS와 RANGE의 차이점

| OrderID | OrderDate  | Quantity |
|---------|------------|----------|
| 1       | 2024-01-01 | 10       |
| 2       | 2024-01-01 | 15       |
| 3       | 2024-01-03 | 20       |
| 4       | 2024-01-03 | 25       |
| 5       | 2024-01-03 | 30       |
SELECT
    OrderID,
    OrderDate,
    Quantity,
    SUM(Quantity) OVER (ORDER BY OrderDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS SumQuantityRows,
    SUM(Quantity) OVER (ORDER BY OrderDate RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) AS SumQuantityRange
FROM
    Orders;
| OrderID | OrderDate  | Quantity | SumQuantityRows | SumQuantityRange |
|---------|------------|----------|----------------|------------------|
| 1       | 2024-01-01 | 10       | 25             | 25               |
| 2       | 2024-01-01 | 15       | 40             | 25               |
| 3       | 2024-01-03 | 20       | 60             | 75               |
| 4       | 2024-01-03 | 25       | 75             | 75               |
| 5       | 2024-01-03 | 30       | 55             | 75               |
| OrderID | OrderDate  | Quantity | SumQuantityRows |
|---------|------------|----------|-----------------|
| 1       | 2024-01-01 | 10       | 25              | -- (10 + 15)
| 2       | 2024-01-01 | 15       | 40              | -- (10 + 15 + 15)
| 3       | 2024-01-03 | 20       | 75              | -- (20 + 25 + 30)
| 4       | 2024-01-03 | 25       | 75              | -- (20 + 25 + 30)
| 5       | 2024-01-03 | 30       | 55              | -- (25 + 30)
| OrderID | OrderDate  | Quantity | SumQuantityRange |
|---------|------------|----------|------------------|
| 1       | 2024-01-01 | 10       | 25               | -- (10 + 15)
| 2       | 2024-01-01 | 15       | 25               | -- (10 + 15)
| 3       | 2024-01-03 | 20       | 75               | -- (20 + 25 + 30)
| 4       | 2024-01-03 | 25       | 75               | -- (20 + 25 + 30)
| 5       | 2024-01-03 | 30       | 75               | -- (20 + 25 + 30)

 

 

UNBOUNDED PRECEDING

 

RANK WITHIN GROUP

 

RANK OVER

| StudentID |   Name  | Marks |
|-----------|---------|-------|
|     1     |   John  |   80  |
|     2     |  Alice  |   90  |
|     3     |   Bob   |   85  |
|     4     |  Emma   |   90  |
|     5     |  Chris  |   75  |
|     6     |  Sophia |   85  |
SELECT 
    StudentID,
    Name,
    Marks,
    RANK() OVER (ORDER BY Marks DESC) AS Rank
FROM 
    Students;
| StudentID |   Name  | Marks | Rank |
|-----------|---------|-------|------|
|     2     |  Alice  |   90  |   1  |
|     4     |  Emma   |   90  |   1  |
|     3     |   Bob   |   85  |   3  |
|     6     |  Sophia |   85  |   3  |
|     1     |   John  |   80  |   5  |
|     5     |  Chris  |   75  |   6  |

 

 

DENSE RANK

| StudentID |   Name  | Marks |
|-----------|---------|-------|
|     1     |   John  |   80  |
|     2     |  Alice  |   90  |
|     3     |   Bob   |   85  |
|     4     |  Emma   |   90  |
|     5     |  Chris  |   75  |
|     6     |  Sophia |   85  |


SELECT 
    StudentID,
    Name,
    Marks,
    DENSE_RANK() OVER (ORDER BY Marks DESC) AS Dense_Rank
FROM 
    Students;


| StudentID |   Name  | Marks | Dense_Rank |
|-----------|---------|-------|------------|
|     2     |  Alice  |   90  |     1      |
|     4     |  Emma   |   90  |     1      |
|     3     |   Bob   |   85  |     2      |
|     6     |  Sophia |   85  |     2      |
|     1     |   John  |   80  |     3      |
|     5     |  Chris  |   75  |     4      |

 

 

LAG

| order_date | amount |
|------------|--------|
| 2024-01-01 | 100    |
| 2024-01-02 | 150    |
| 2024-01-03 | 200    |
| 2024-01-04 | 180    |
| 2024-01-05 | 220    |


SELECT 
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS previous_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS amount_difference
FROM 
    sales_data;


| order_date | amount | previous_amount | amount_difference |
|------------|--------|-----------------|-------------------|
| 2024-01-01 | 100    | 0               | 100               |
| 2024-01-02 | 150    | 100             | 50                |
| 2024-01-03 | 200    | 150             | 50                |
| 2024-01-04 | 180    | 200             | -20               |
| 2024-01-05 | 220    | 180             | 40                |

 

FIRST VALUE

 

LAST VALUE

 

NTILE

 

RATIO_TO_REPORT

 

CUME_DIST

 

PERCENT_RANK

 

ROWNUM(TOP N QUERY)

 

RANK(TOP N QUERY)

 

FETCH(TOP N QUERY)

 

START WITH... CONNECT BY PRIOR

 

LEVEL

 

CONNECT_BY_ISLEAF

 

CONNECT_BY_ROOT

 

SYS_CONNECT_BY_PATH

 

ORDER SIBLINGS BY

 

LONG DATA

 

WIDE DATA

 

PIVOT (LONG -> WIDE)

 

UNPIVOT (WIDE -> LONG)

 

REGEXP_REPLACE

 

REGEXP_SUBSTR

 

REGEXP_INSTR

 

REGEXP_LIKE

 

REGEXP_COUNT

 

INSERT(DML)

 

UPDATE(DML)

 

DELETE(DML)

 

MERGE(DML)

 

COMMIT(TCL)

 

ROLLBACK(TCL)

 

트랜잭션

 

트랜잭션의 특징(원자성, 일관성, 고립성, 지속성)

 

CREATE(DDL)

 

CREATE TABLE AS(DDL)

 

ALTER(DDL)

 

DROP(DDL)

 

TRUNCATE(DDL)

 

ADD/DROP CONSTRAINT(DDL)

 

UNIQUE(DDL)

 

NOT NULL(DDL)

 

FOREIGN KEY(DDL)

 

REFERENCES(DDL)

 

ON DELETE CASCADE(DDL)

 

ON DELETE SET NULL(DDL)

 

CHECK(DDL)

 

뷰(VIEW)

 

시퀀스(SEQUENCE)

 

시노님(SYNONYM)

 

GRANT(DCL)

 

REVOKE(DCL)

 

오브젝트 권한

 

시스템 권한

 

ROLE(DCL)


WITH GRANT OPTION(DCL)

 

WITH ADMIN OPTION(DCL)

728x90
반응형
저작자표시

'플그래밍' 카테고리의 다른 글

무료 SSL 인증서 발급받기 (GoGetSSL.com)  (2) 2025.01.03

'플그래밍'의 다른글

  • 현재글예제로 배우는 SQL

관련글

댓글

티스토리툴바