PostgreSQL SELECT and JOIN

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

1/23

flashcard set

Earn XP

Description and Tags

Brian's SQL

24 Terms

1
New cards
select concat string
SELECT first_name || ' ' || last_name FROM customer;
2
New cards
column alias
“expression AS column_lias” or shorter “expression column_lias“
3
New cards
without from a function
SELECT NOW();
4
New cards
column alias with spaces
column_name AS "column alias"
5
New cards

order by

SELECT
    first_name,
    last_name
FROM
    customer
ORDER BY
    first_name ASC;
6
New cards

default order direction

ASC

7
New cards

multiple sort

SELECT
    first_name,
    last_name
FROM
    customer
ORDER BY
    first_name ASC,
    last_name DESC;
8
New cards

LENGTH

SELECT
    first_name,
    LENGTH(first_name) len
FROM
    customer
ORDER BY
    len DESC;
9
New cards

Order with null

SELECT
    num
FROM
    sort_demo
ORDER BY
    num DESC NULLS LAST;
SELECT
    num
FROM
    sort_demo
ORDER BY
    num DESC NULLS FIRST;
10
New cards

Distinct one column

SELECT 
  DISTINCT column1 
FROM 
  table_name;
11
New cards

Distinct multile columns

SELECT 
  DISTINCT bcolor, fcolor 
FROM 
  colors
ORDER BY 
  bcolor, 
  fcolor;
12
New cards

select IN

SELECT 
  first_name, 
  last_name 
FROM 
  customer 
WHERE 
  first_name IN ('Ann', 'Anne', 'Annie');
13
New cards

LIKE + length and sort

SELECT 
  first_name, 
  LENGTH(first_name) name_length 
FROM 
  customer 
WHERE 
  first_name LIKE 'A%' 
  AND LENGTH(first_name) BETWEEN 3 
  AND 5 
ORDER BY 
  name_length;
14
New cards

null with and / or

true AND null = null
false AND null = false
false OR null = null
true OR null = true
15
New cards

LIMIT and OFFSET

SELECT 
  film_id, 
  title, 
  release_year 
FROM 
  film 
ORDER BY 
  film_id 
LIMIT 4 OFFSET 3;
16
New cards

FETCH OFFSEt

SELECT
    film_id,
    title
FROM
    film
ORDER BY
    title 
OFFSET 5 ROWS 
FETCH FIRST 5 ROW ONLY; 
17
New cards
cast operator
SELECT 
  payment_id, 
  amount, 
  payment_date 
FROM 
  payment 
WHERE 
  payment_date::date IN ('2007-02-15', '2007-02-16');
18
New cards

NOT IN

SELECT 
  film_id, 
  title 
FROM 
  film 
WHERE 
  film_id NOT IN (1, 2, 3) 
19
New cards

BETWEEN / NOT BETWEEN

SELECT 
  payment_id, 
  amount, 
  payment_date 
FROM 
  payment 
WHERE 
  payment_date BETWEEN '2007-02-15' AND '2007-02-20' 
  AND amount > 10 
ORDER BY 
  payment_date;
20
New cards

LIKE - ILIKE

SELECT 
  first_name, 
  last_name 
FROM 
  customer 
WHERE 
  first_name LIKE '_BAR%';
21
New cards

USING ESCAPE

SELECT * FROM t 
WHERE message LIKE '%10%%' ESCAPE '';
22
New cards

Joins

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
INNER JOIN basket_b
    ON fruit_a = fruit_b;

(LEFT, RIGHT, FULL) / (IS NULL)
23
New cards

Table alias

SELECT 
  c.customer_id, 
  c.first_name, 
  p.amount, 
  p.payment_date 
FROM 
  customer c 
  INNER JOIN payment p ON p.customer_id = c.customer_id 
ORDER BY 
  p.payment_date DESC;
24
New cards

JOIN with USING

SELECT
    select_list
FROM
    table1 t1
    INNER JOIN table2 t2 USING(column_name);