반응형
UPADE / DELETE
DROP TABLE movies;
CREATE TABLE movies(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title TEXT UNIQUE NOT NULL,
released INTEGER NOT NULL CHECK(released >= 1800),
overview TEXT,
rating REAL CHECK(rating BETWEEN 0 AND 10),
director TEXT,
adult INTEGER CHECK(adult BETWEEN 0 AND 1)
);
INSERT INTO movies
(title, released, rating, adult)
VALUES
('ZZANGGU', 1900, 8.1, 0),
('x-mans',2000,7.5,0);
--UPDATE
UPDATE movies SET released = 1991; --모든 영화의 개봉일 1991이 된다.
UPDATE movies SET released = 2001 WHERE title = 'x-mans' ;
UPDATE movies SET director = 'who?' WHERE director IS NULL;
--DELETE
-- DELETE FROM movies; -- 모든 영화가 삭제된다.
DELETE FROM movies WHERE id = 1;SELECT
-- SELECT
SELECT * FROM movies; -- 모든 movies의 데이터를 불러올 수 있다.
SELECT id, title, released, rating, adult FROM movies WHERE title = 'ZZANGGU';
SELECT
id,
title,
released,
rating,
adult
FROM
movies WHERE id = 2;
SELECT
id,
UPPER(title) as upper_title ,
released * 2 AS double_rating,
REPLACE(rating, 7.5, 44) as replace_rate,
adult
FROM
movies WHERE id = 2;
WHERE
SELECT
*
FROM
movies
WHERE
-- title <> 'star' -- <> != 은 같다;
-- release_date > 2023;
-- release_date is null; -- null을 검색하기 위해서는 * = null 아니라 * is null / is not null로 검색해야한다.movies
-- original_language != 'en' AND release_date >2022
-- release_date BETWEEN 2020 AND 2022
-- genres IN ('Documentray' , 'Comedy')
-- title LIKE 'star'
-- title LIKE 'THE%' -- %는 와일드카드
-- title LIKE '%Star%'
title LIKE '___ter'
;Conditional
SELECT
title,
CASE WHEN rating >= 7 THEN
'GOOD'
WHEN rating < 7 THEN
'SOSO'
ELSE
'NAH~'
END AS string_rating
FROM
movies
;Order
SELECT
*
FROM
movies
WHERE
title LIKE 'star wars%'
ORDER BY
rating DESC --- ASC 오름차순 DESC 내침차순
;
OFFSET
SELECT
*
FROM
movies
LIMIT
5 -- 몇개를 보여줄지
OFFSET
5 -- 몇개를 넘어갈지
-- 1*5
-- 2*5
;SELECT
*
FROM
movies
WHERE
title LIKE '%star%'
ORDER BY
rating DESC
LIMIT
15 -- 몇개를 보여줄지
OFFSET
0 * 15 -- 몇개를 넘어갈지
;
GROUP BY
SELECT
director,
-- SUM(revenue) as total_revenue
AVG(revenue) as avg_revenue
FROM
movies
WHERE
director IS NOT NULL
AND
revenue IS NOT NULL
GROUP BY
director
ORDER BY
-- total_revenue DESC
avg_revenue DESC
;HAVING
SELECT
director,
-- SUM(revenue) as total_revenue
AVG(revenue) as avg_revenue
FROM
movies
WHERE
director IS NOT NULL
AND
revenue IS NOT NULL
GROUP BY
director
HAVING
avg_revenue > 800000000
ORDER BY
-- total_revenue DESC
avg_revenue DESC
;728x90
'CODING PRACTICE > SQL' 카테고리의 다른 글
| Basic__sqlite(1) (0) | 2025.02.25 |
|---|
