Basic SQL

sql oneline -> https://sqliteonline.com/

# Create Table
CREATE TABLE months(id int NOT NULL, name varchar(10), days int, PRIMARY KEY (id));

# Insert Rows
INSERT INTO months VALUES (1,'January',31);
INSERT INTO months (id,name,days) VALUES (2,'February',29);

# Select
SELECT * FROM "characters"
SELECT name, weapon FROM "characters" ORDER BY name DESC

# Where
SELECT * FROM "characters" WHERE weapon = "pistol";

# AND / OR
SELECT * FROM albums WHERE genre = 'rock' AND sales_in_millions <= 50 ORDER BY released

# In/Between/Like
SELECT * FROM albums WHERE genre IN ('pop','soul');
SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;
# % - Represents zero, one, or multiple characters
# _ - Represents a single character (MS Access uses a question mark (?) instead)
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

# Functions
# COUNT() - returns the number of rows
# SUM() - returns the total sum of a numeric column
# AVG() - returns the average of a set of values
# MIN() / MAX() - gets the minimum/maximum value from a column
SELECT MAX(released) FROM albums;

# Nested Select
SELECT artist,album,released FROM albums 
WHERE released = (SELECT MIN(released) FROM albums);

# Joining Tables
SELECT video_games.name, video_games.genre, game_developers.name, 
game_developers.country FROM video_games 
INNER JOIN game_developers 
ON video_games.developer_id = game_developers.id;

# Aliases
SELECT games.name, games.genre, devs.name AS developer, devs.country 
FROM video_games AS games 
INNER JOIN game_developers AS devs 
ON games.developer_id = devs.id;

# Update
UPDATE tv_series SET genre = 'drama' WHERE id = 2;

# Delete Rows
DELETE FROM tv_series WHERE id = 4

# Deleting Tables
TRUNCATE TABLE table_name;  -> delete all the rows of the table
DROP TABLE table_name;  -> delete the table

# OVER()  PARTITION BY  GROUP BY
SELECT car_make, car_model, car_price,
    AVG(car_price) OVER() AS "overall average price",
    AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"
FROM car_list_prices

# PARTITION BY -> show all rows
# GROUP BY -> only one row

SELECT car_make,
       AVG(car_price) AS average_price,
       MAX(car_price) AS top_price
FROM   car_list_prices
GROUP BY car_make

# UNION UNION ALL
# UNION：重複を含めない（重複があったら除外）
# UNION ALL：重複を含む（すべて抽出）
SELECT  userId  FROM  football
UNION
SELECT  userId  FROM  baseball;

sql practice -> https://sqlzoo.net/wiki/SQL_Tutorial
->https://leetcode.com/study-plan/sql/