sql basic (codecademy 정리)

Manuplation

CREATE TABLE

CREATE TABLE tablename;

INSERT INTO

INSERT INTO tablename (id, name, age)
VALUES (1, "seul", 26);

SELECT

SELECT * FROM tablename;
SELECT id, name FROM tablename;

UPDATE

UPDATE tablename
SET age = 27
WHERE id = 1;

ALTER

ALTER TABLE tablename 
ADD COLUMN email TEXT;

DELETE

DELETE FROM tablename
WHERE email IS NULL;

Queries

SELECT

SELECT name, imdb_rating FROM movies;
SELECT DISTINCT genre FROM movies;

WHERE

SELECT * FROM movies WHERE imdb_rating > 8;

LIKE 
비슷한 value를 찾아낼 때 유용

# using _
SELECT * FROM movies 
WHERE name LIKE "se_en";
# result is
# seven
# se7en

# using %
# start from a
SELECT * FROM movies 
WHERE name LIKE "a%";
# including man
SELECT * FROM movies
WHERE name LIKE "%man%";

# using BETWEEN
SELECT * FROM movies
WHERE name BETWEEN "A" AND "J";
SELECT * FROM movies
WHERE year BETWEEN 1990 AND 2000;

AND 
여러 query들을 묶을 수 있음

SELECT * FROM movies
WHERE year BETWEEN 1990 AND 2000
AND genre = "comedy";

OR

SELECT * FROM movies
WHERE genre = "comedy"
OR year < 1980;

ORDER

# ordering by desc
SELECT * FROM movies
ORDER BY imdb_rating DESC;

# ordernig by asc and get only 3
SELECT * FROM movies
ORDER BY imdb_rating ASC
LIMIT 3;

Aggregate

COUNT

# 가격이 0인 앱들 카운팅
SELECT COUNT(*) FROM fake apps
WHERE price = 0;

GROUP BY

# 가격별로 카운팅
SELECT price, COUNT(*) FROM fake_apps
GROUP BY price;

SELECT price, COUNT(*) FROM fake_apps
WHERE downloads > 20000
GROUP BY price;

SUM

SELECT SUM(downloads) FROM fake_apps;

SELECT category, SUM(downloads) FROM fake_apps
GROUP BY category;

MAX

SELECT name, category, MAX(downloads) FROM fake_apps
GROUP BY category;

MIN

SELECT name, category, MIN(downloads)
FROM fake_apps
GROUP BY category;

AVG

SELECT price, AVG(downloads)
FROM fake_apps
GROUP BY price;

ROUND

정수화시키기

SELECT price, ROUND(AVG(downloads))
FROM fake_apps
GROUP BY price;

Multiple tables

select by other table

SELECT * FROM albums
WHERE artist_id = 3;

Cross join

SELECT artists.name, albums.name
FROM artists, albums

Inner join

SELECT * 
FROM 
	albums
JOIN artist ON
	albums.artist_id = artist.id;

Left outer join

Inner join과 다르게 join condition이 충족되지 않아도 괜찮음

SELECT
	*
FROM
	albums
LEFT JOIN artists ON
	albums.artist_id = artists.id;

Alias

SELECT
	albums.name AS "Album",
	albums.year,
	artists.name AS "Artist"
FROM
	albums
JOIN artists ON
	albums.artist_id = artist.id;
WHERE
	albums.year > 1980;

create table safely with Foreign Key constraint

DROP TABLE IF EXISTS albums;
CREATE TABLE IF NOT EXISTS albums(
  id INTEGER PRIMARY KEY, 
  name TEXT,
  year INTEGER,
  artist_id INTEGER,
  FOREIGN KEY(artist_id) REFERENCES artist(id)
);

---