[SQL] 0. Basic SQL

1. AS

2. (Filtering) Comparison Operators

3. (Filtering)Logical Operators 

1. LIKE: 비슷한 값 매치 ; "column_name" LIKE 'Snoop%' -> Case sensitive

2. ILIKE: "column_name" ILIKE 'snoop%' -> not case sensitive 대소문자 상관없음!

3. IN: 포함하고 싶은 값의 리스트 설정

4. BETWEEN: certain range에 해당하는 rows 셀렉 -> HAS to be paired with AND operator

5. IS NULL: 주어진 칼럼에 데이터 없는 rows 셀렉

6. AND: 2가지 컨디션 모두 충족하는 행 선택

7. OR: either two conditions 컨디션 충족하는 행 선택

8. NOT:certain condition에 match하지 않는 행 선택

4. (Sorting )ORDER BY

 

---

Using AS

SELECT *
  FROM tutorial.us_housing_units
  LIMIT 5

SELECT west AS "West Region"
    FROM tutorial.us_housing_units
**only returns capital letters if covered with double quotes

 

 

---

Comparison Operators

 = , <, >, <=, >=, <>

SELECT west AS West_Region,
      south AS South_Region
  FROM tutorial.us_housing_units

 

SELECT *
  FROM tutorial.us_housing_units
  WHERE west > 50

 

SELECT *
  FROM tutorial.us_housing_units
  WHERE south <= 20

 

SELECT *
  FROM tutorial.us_housing_units
  WHERE month_name = 'February'
** month_name is "n" or earlier

**"n"보다 큰걸로 설정하면 "na"부터 시작

 

SELECT *
  FROM tutorial.us_housing_units
  WHERE month_name < 'o'

---

Arithmetic in SQL

• Using + - * / : ONLY IN SAME ROW
• If you want to combine across diff rows, try aggregate functions

SELECT south + west + midwest + northeast AS Four_Regions
  FROM tutorial.us_housing_units

SELECT *
  FROM tutorial.us_housing_units
  WHERE west > (midwest + northeast)

SELECT south/(south+west+midwest+northeast)*100 AS south_perc,
             west/(south+west+midwest+northeast)*100 AS west_perc,
             midwest/(south+west+midwest+northeast)*100 AS midwest_perc,
             northeast/(south+west+midwest+northeast)*100 AS northeast_perc
  FROM tutorial.us_housing_units
  WHERE year >= 2000

---

 Logical Operators

0.DB
SELECT * FROM tutorial.billboard_top_100_year_end
LIMIT 5

1. LIKE
double quote: used when 칼럼 이름 표시할때 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE "group" LIKE 'Snoop%'

1.1. ILIKE
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE "group" ILIKE 'snoop%'
  LIMIT 40
  
**underscore ; _ used for 특정 문자 대체할때 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE artist ILIKE 'Dr_ke'

Q: Write a query that returns all rows for which Ludacris was a member of the group  
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE "group" ILIKE '%Ludacris%'
Q: Write a query that returns all rows for which the first artist listed in the group has a name that begins with "DJ"
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE "group" LIKE 'DJ%'
  
2. IN
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank IN (1,2,3)
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE artist IN ('Taylor Swift', 'Usher', 'Ludacris')
**Comparison Operator에서처럼 글자는 싱글 quote! 

Q: Write a query that shows all of the entries for Elvis and M.C. Hammer
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE "group" ILIKE '%Hammer%'
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE "group" IN ('M.C. Hammer', 'Hammer', 'Elvis Presley')
  
3. BETWEEN
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank BETWEEN 5 AND 10         

** 5랑 10 포함
** 위 쿼리는 아래 쿼리와 같음: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank >= 5 AND year_rank <= 10
  
  Q: Write a query that shows all top 100 songs from Jan 1, 1985 through Dec 31, 1990
  A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year between 1985 AND 1990

4. IS NULL : allows you to exclude rows with missing data from results

SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE artist IS NULL
** artist = NULL 은 에러남. arithemtic에 NULL VALUE 사용 불가 
  
Q: Write a query that shows all of the rows for which song_name is NULL 
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE song_name IS NULL

5. AND 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year = 2012
  AND year_rank <= 10
  AND "group" ILIKE '%feat%'
Q: Write a query that surfaces all rows for top 10 hits for which Ludacris is part of the group
A: 
SELECT * 
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank <= 10
  AND "group" ILIKE '%ludacris%'

Q: Write a query that surfaces the top-ranked records in 1990, 2000, and 2010
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank = 1
  AND year IN (1990, 2000, 2010)

Q: Write a query that lists all songs from 1960s with "love" in the title
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year BETWEEN 1960 AND 1969
  AND song_name ILIKE '%love%'

6. OR
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2013
   AND ("group" ILIKE '%macklemore%' OR "group" ILIKE '%timberlake%')
**year 2013을 만족하는 것중에서 macklemore, timberlake 들어가는 것들. 2013으로 먼저 걸러냄

Q: Write a query that returns all rows for top 10 songs that featured either Katy PErry or Bon Jovi
A: 
SELECT * 
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank <= 10 
  AND ("group" ILIKE '%katy perry%' OR "group" ILIKE '%bon jovi%')

Q: Write a query that returns all songs with titles that contain the 
word "California" in either the 1970s or 1990s
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE song_name LIKE '%California%'
  AND (year BETWEEN 1970 AND 1979 OR year BETWEEN 1990 AND 1999)
  
Q: Write a query that lists all top-100 recordings that 
   feature Dr. Dre before 2001 or after 2009.
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE "group" ILIKE '%dr. dre%'
   AND (year <= 2000 OR year >= 2010)

7. NOT  : can put before any CONDITIONAL STATEMENT to select rows which that statement is false

SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year = 2013
  AND year_rank NOT BETWEEN 2 AND 3
**This does not make sense, just use comparative operator

SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year = 2013 
  AND year_rank NOT > 3  
  ##This will give out error. Rather, use: 
  AND year_rank <= 3

**NOT commonly used with LIKE & IS NOT NULL
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year = 2013
  AND "group" NOT LIKE '%macklemore%'

Q: Write a query that returns all rows for songs that were on the charts in 2013 and do not contain the letter "a"
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year = 2013
  AND song_name NOT LIKE '%a%'   

SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE song_name NOT ILIKE '%a%'
  AND year = 2013

 

Now done with filtering. Get onto Sorting 

---

ORDER BY 

• reorder results based on the data in one or more COLUMNS!!!
• Default:
  • a to z (ascending order) ; 작은것부터 큰거
  • ORDER BY 'column_name' DESC : 큰것부터 시작

SELECT *
  FROM tutorial.billboard_top_100_year_end
 ORDER BY artist DESC

 

Most recent years come FIRST
Top rank songs comes before lower-ranked songs 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank <=3
  ORDER BY year DESC, year_rank   
**year DESC 먼저 sort 하고 그 다음에 year_rank 
**예를 들어, 2013년에 year rank 1,2,3 그 다음 행에 2012 년에 rank 1,2,3,....
  
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year_rank <= 3
 ORDER BY year_rank, year DESC
** year rank로 먼저 1순위부터 추리고 거기서 year 가장 최근거 sort
** 예를 들어, 2013, 2012, 2011, ...., 1969 1순위....... 2013, 2012, ..., 1969 3순위 순

Q: Write a query that returns all rows from 2010 ordered by rank, with artists ordered alphabetically for each song.
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year = 2010
  ORDER BY year_rank, artist 
-- comments
/*also can leave comment
across different lines */ 

Q: Write a query that shows all rows for which T-Pain was a group memeber, ordered by rank on the charts, from lowest to highest rank (100 to 1)
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE "group" ILIKE '%t-pain%'
  ORDER BY year_rank DESC

Q: Write a query that returns songs that ranked between 10 and 20 (inclusive) in 1993, 2003, or 2013 order by year and rank and leave a comment on each line of WHERE clause to indicate what the line does 
A: 
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank BETWEEN 10 AND 20 
  AND year IN (1993, 2003, 2013)
  ORDER BY year, year_rank