PostgreSQL part 3

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/3

encourage image

There's no tags or description

Looks like no tags are added yet.

4 Terms

1
New cards
COALESCE and NULLIF
SELECT
    id,
    title,
    COALESCE (
        NULLIF (excerpt, ''),
        LEFT (body, 40)
    )
FROM
    posts;
2
New cards

CASE

SELECT
    SUM (
        CASE
            WHEN rental_rate = 0.99 THEN 1
            ELSE 0
        END
    ) AS "Economy",
    SUM (
        CASE
            WHEN rental_rate = 2.99 THEN 1
            ELSE 0
        END
    ) AS "Mass",
    SUM (
        CASE
            WHEN rental_rate = 4.99 THEN 1
            ELSE 0
        END
    ) AS "Premium"
FROM
    film;
3
New cards

minus 1 day

-- Write your PostgreSQL query statement below
select
    w2.id
from
    weather w1
    join weather w2 on w1.recordDate = w2.recordDate - INTERVAL '1 day'
    and w2.temperature > w1.temperature;
4
New cards
PARTITION BY with ROW_NUMBER
select
    *
from
    (
        SELECT
            Delivery.*,
            ROW_NUMBER() OVER (
                PARTITION BY customer_id
                ORDER BY
                    order_date
            ) as n
        FROM
            Delivery
    )
where
    n = 1