[Datacamp] SQL Server

 

 

[Datacamp]  Data Analyst with SQL Server (Career Track)​

 

(1) Introduction to SQL Server ( 🎈 )

(2) Introduction to Relational Databases in SQL

(3) Intermediate SQL Server

(4) Time Series Analysis in SQL Server

---

Selecting : SELECT
Ordering : ORDER BY
Filtering : WHERE, HAVING
Aggregating : SUM, COUNT, MIN, MAX, AVG
Text manipulation : LECT, RIGHT, LEN, SUBSTRING

---

 

1. SELECTion Box

 

- TOP ()

 

return 5 rows

SELECT TOP(5) artist
FROM artists;

 

return top 5% of rows

SELECT TOP(5) PERCENT artist
FROM artists;

 

- DISTINCT

 

return unique rows

SELECT DISTINCT nerc_region
FROM grid;

 

 

- Aliasing column names with AS

SELECT description AS cause_of_outage
FROM grid;

 

- ORDER BY

order by, where 둘 다 SELECT 문에 없는 열 사용 가능

SELECT TOP (10) prod_id, year_intro
FROM products
ORDER BY year_intro, product_id;

 

DESC

SELECT TOP (10) prod_id, year_intro
FROM products
ORDER BY year_intro DESC, prod_id;

 

- Non-equality

SELECT customer_id, total
FROM invoice
WHERE total <> 10;

 

- BETWEEN

SELECT customer_id, total
FROM invoice
WHERE total BETWEEN 20 AND 30;

 

NOT BETWEEN

SELECT customer_id, total
FROM invoice
WHERE total NOT BETWEEN 20 AND 30;

 

- IS NULL

SELECT
	TOP (6) total,
    	biling_state
FROM invoice
WHERE billing_state IS NULL;

 

IS NOT NULL

SELECT
	TOP (6) total,
	biling_state
FROM invoice
WHERE billing_state IS NOT NULL;

 

- AND / OR

SELECT song, artist
FROM songlist
WHERE
	artist = 'AC/DC'
    	AND release_year < 1980;

SELECT song, artist
FROM songlist
WHERE
	artist = 'Green Day'
	AND (
		release_year = 1994
    		OR release_year > 2000;
        );

 

- IN

SELECT song, release_year
FROM songlist
WHERE
	release_year IN (1995, 1991, 1992);

 

- LIKE

SELECT song
FROM songlist
WHERE song LIKE 'a%';

 

---

 

(2) Groups, strings, and counting things

 

- SUM

Don't forget Aliases, 쓰지 않으면 No column name으로 출력됨

 

single column

SELECT
	SUM(affected_customers) AS total_affected
FROM grid;

two or more columns

SELECT
	SUM(affected_customers) AS total_affected,
    	SUM(demand_loss_mw) AS total_loss
FROM grid;

 

- COUNT

SELECT
	COUNT(affected_customers) AS count_affected
FROM grid;

distinct

SELECT
	SUM(DISTINCT affected_customers) AS unique_count_affected
FROM grid;

 

- MIN, MAX, AVG

SELECT
	MIN(affected_customers) AS min_affected_customers
FROM grid
WHERE affected_customers > 0 ;

 

- LEN

SELECT
	description,
    	LEN(description) AS description_length
FROM grid;

 

- LEFT / RIGHT

문자 추출

SELECT
	description,
    	LEFT(description, 20) AS first_20_left
FROM grid;

 

- CHARINDEX

해당 문자 위치

SELECT
	CHARINDEX('_', url) AS description_length,
    	url
FROM courses;

 

- SUBSTRING

(시작 문자 위치, 추출할 문자 수)

SELECT
	SUBSTRING(url, 12, 12) AS target_section,
    	url
FROM courses;

 

- REPLACE

SELECT
	TOP(5) REPLACE(url, '_', '-') AS replace_with_hyphen
FROM courses;

 

- GROUP BY

(X) grouping error Syntax => insert GROUP BY

SELECT
	SUM(demand_loss_mw) AS lost_demand,
	description
FROM grid;

(O)

SELECT
	SUM(demand_loss_mw) AS lost_demand,
	description
FROM grid
GROUP BY description;

 

- Having

GROUP BY 후 그룹화된 쿼리의 결과를 재필터링 (vs. WHERE : 기본 행 값에 영향을 줌)

SELECT
	SUM(demand_loss_mw) AS lost_demand,
	description
FROM grid
GROUP BY description
HAVING SUM(demand_loss_mw) > 1000 ;

 

---

(3) Joining tables

 

 

- INNER JOIN

SELECT
	album_id,
    	title,
    	album.artist_id,
    	name AS artist_name
FROM album
INNER JOIN artist ON artist.artist_id = album.artist_id;

 

Multiple Inner Join

SELECT
	track_id,
	track.name AS track_name,
	title as album_title,
	artist.name AS artist_name
FROM track
INNER JOIN album on track.album_id = album.album_id 
INNER JOIN artist on album.artist_id = artist.artist_id;

 

- LEFT Join

no match : NULL 출력

SELECT
	Admitted.Patient_ID,
	Admitted,
	Discharged
FROM Admitted
LEFT JOIN Discharged ON Discharged.Patient_ID = Admitted.Patient_ID;

 

- RIGHT Join

SELECT
	Admitted.Patient_ID,
	Admitted,
        Discharged
FROM Discharged
RIGHT JOIN Admitted ON Discharged.Patient_ID = Admitted.Patient_ID;

 

- UNION (Slower to run)

중복 제외 결합

다른 테이블의 데이터를 결합하는 경우 동일한 열 갯수, 순서, 데이터 유형을 가져야 함

SELECT
	album_id,
	title,
 	artist_id
FROM album
WHERE artist_id IN (1, 3)
UNION
SELECT
	album_id,
	title,
 	artist_id
FROM album
WHERE artist_id IN (1, 4, 5)

 

- UNION ALL (Faster to run)

중복 포함 두 쿼리의 모든 행 결합

다른 테이블의 데이터를 결합하는 경우 동일한 열 갯수, 순서, 데이터 유형을 가져야 함

SELECT
	album_id,
	title,
 	artist_id
FROM album
WHERE artist_id IN (1, 3)
UNION ALL
SELECT
	album_id,
	title,
 	artist_id
FROM album
WHERE artist_id IN (1, 4, 5)

 

 

---

(4) You've got the power

 

- Data Types

 

(1) Dates

• date(YYYY-MM-DD), datetime(YYYY-MM-DD hh:mm:ss)
• time

(2) Numeric

• integer, decimal, float
• bit (1=TRUE, 0=FALSE, NULL)

(3) Strings

• char, varchar, nvarchar

 

- CREATE (column name, data type, size)

CREATE TABLE test_table(
	test_date date,
	test_name varchar(20),
    	test_int int
 )

 

- INSERT

 

INSERT INTO table_name (col1, col2, col3)
VALUES
	('value1', 'value2', value3)

 

- INSERT SELECT

다른 테이블에서 추가

Don't use SELECT *

순서가 올바른지 확인

INSERT INTO table_name (col1, col2, col3)
SELECT
	column1,
    	column2,
        column3
FROM other_table
WHERE

 

- UPDATE

SET 업데이트하려는 열 = 변경하려는 값

WHERE 업데이트 행 식별 조건 -> WHERE을 하지 않으면 열의 모든 값이 업데이트 됨

UPDATE table
SET
	column1 = value1,
 	column2 = value2
WHERE

 

- DELETE

확인 메시지가 없으니 주의

DELETE 전 미리 테스트

DELETE
FROM table
WHERE

 

- TRUNCATE TABLE

WHERE 절 필요없음, 모든 열 모든 데이터 한번에 제거

TRUNCATE TABLE table_name

 

- Variables

If we change the query, avoid repetition -> create a variable

 

-- Declare @

DECLARE @my_album_id INT
DECLARE @my_artist VARCHAR(100);

SELECT *
FROM artist
WHERE name = @my_artist;

 

DECLARE - SET

 

선언 - 설정

동시에 만족시키는 WHERE절을 쓰고 싶을 때

DECLARE @my_album_id INT
DECLARE @my_artist VARCHAR(100);

SET @my_album_id = 5
SET @my_artist 'AC/DC';

SELECT --
FROM --
WHERE artist = @my_artist
AND album_id = @my_album_id;

 

- Temporary tables

#table은 connection, session end 까지 존재하며, DROP TABLE로 #수동 제거 가능

SELECT
	col1,
    	col2,
        col3 INTO #my_temp_table
FROM my_existing_table
WHERE