[GA] Google Analytics Certificate Program (GA Day) Course 4 SQL basic queries

- to add: insert into 

INSERT INTO customer.data_customer_address

(customer_id, address, city, state, zipcode, country)

VALUES

(2645, "333 SQL Road", "Jackson","MI", 29202, "US")

 

- To update 

 

UPDATE customer_data.customer_address

SET address = "123 New Address"

WHERE customer_id= 2545

 

- To clean up

DROP TABLE IF EXIST

- Remove Duplicates

SELECT distinct customer_id

FROM customer_data.customer_address

- How to name the column? use AS

SELECT

LENGTH(country) AS letters_in_country

FROM customer_data.customer_address

 

- How to bring out a part of String? use substr

 

SELECT customer_id

FROM customer_data.customer_address

WHERE

substr(country,1,2) = "US"

- eliminate extra spaces

SELECT distinct(customer_id)

FROM customer_data.customer_address

WHERE trim(state) ="OH

 

- descending order : desc

SELECT purchase_price FROM `onyx-smoke-380601.customer_datas.customer_purchase`

order by

purchase_price DESC

 

- 소수점을 포함해서 numbers sorting 하려면 : CAST ()

 

SELECT CAST(purchase_price AS FLOAT64)

FROM `onyx-smoke-380601.customer_datas.customer_purchase`

order by

CAST(purchase_price AS FLOAT64) DESC

 

- date + time 을 date 만 빼낼때 : CAST 사용

SELECT

cast(date AS DATE) AS date_only,

purchase_price

FROM `onyx-smoke-380601.customer_datas.customer_purchase`

order by

date BETWEEN "2020-12-01" and "2020-12-31"

- str combine: concat 사용하기 

SELECT

concat(product_code, product_color)

FROM customer_datas.customer_purchase

WHERE

product = "couch"

 

- 만약 원하는 value가 없다면 replace : coalesce

SELECT

coalesce(product, product_code) AS product_info

FROM customer_datas.customer_purchase