SQL Queries 1

Objetives

Databases

Entity-Relationships

SQL

(Structure Query Language)

Generate, Manipulate, Retrieve

Retrieve

-- Basic structure
SELECT fields FROM table_name

-- SELECT fields FROM table_name

SELECT * FROM weeks; -- All weeks
SELECT id FROM weeks; -- All weeks only show id's
SELECT id, name FROM weeks; -- All weeks & show id, name columns

Retrieve with Condition

-- Structure
SELECT fields FROM table_name WHERE condition

SQL Condition Operators

-- SELECT fields 
-- FROM table_name 
-- WHERE condition

-- Just retrieve the week with id=1
SELECT * FROM weeks WHERE id = 1;

EQUALS "="

-- SELECT fields 
-- FROM table_name 
-- WHERE condition

-- Just retrieve all weeks without the week id=1
SELECT * FROM weeks WHERE id != 1;

NOT EQUALS "!="

-- SELECT fields 
-- FROM table_name 
-- WHERE condition

-- Just retrieve the week with name "week 1"
SELECT * FROM weeks WHERE name LIKE 'week 1';

LIKE

-- SELECT fields 
-- FROM table_name 
-- WHERE condition

-- Just retrieve all weeks without the week name 'week 1'
SELECT * FROM weeks WHERE name NOT LIKE 'week 1';

NOT LIKE

-- Retrieve all weeks that contain "ek" in the middle for the name
SELECT * FROM weeks WHERE name LIKE '%ek%';

-- Retrieve every week name regardless of the first letter
SELECT * FROM weeks WHERE name LIKE '_eek 1';

LIKE with "%" and "_"

SELECT * 
FROM weeks 
WHERE name IN ('week 1', 'week 2');

IN

SELECT * 
FROM weeks 
WHERE name NOT IN ('week 1', 'week 2');

NOT IN

Retrieve with Condition & Order

-- Structure
SELECT fields 
FROM table_name 
WHERE condition 
ORDER BY field ASC|DESC

SELECT * FROM weeks ORDER BY created_at ASC;
SELECT * FROM weeks ORDER BY created_at DESC;

SELECT * FROM weeks 
WHERE name IN ('week 1', 'week 2') 
ORDER BY created_at DESC;

ORDER BY

Filter Data

DISTINCT

-- Structure
SELECT DISTINCT field 
FROM table_name

SELECT DISTINCT created_at FROM weeks;
SELECT COUNT(DISTINCT created_at) FROM weeks;

DISTINCT

LIMIT

-- Structure
SELECT field 
FROM table_name 
LIMIT number OFFSET start_after

SELECT * FROM weeks LIMIT 2;
-- week 1
-- week 2

SELECT * FROM weeks LIMIT 2 OFFSET 1;
-- week 2
-- week 3

LIMIT

CASE

Create custom outputs with this clause

-- Structure
SELECT field CASE
  WHEN field comparison THEN result
  ELSE default
END AS case_alias
FROM table

SELECT name, created_at,
CASE
  WHEN created_at > '2021-03-01' THEN 'Not March'
  WHEN created_at > '2021-02-01' THEN 'Not February'
  WHEN created_at > '2021-01-01' THEN 'Not January'
  ELSE 'Is January'
END AS not_month
FROM weeks;