[SQL BASIC] Logical Operations

* like : allows you to match on similar values rather than exact ones.

SELECT *
  FROM table1
  WHERE "group" LIKE 'Snoop%'

% (wildcard) : finds anything that contains the word before or after
"group" is in double quotation because GROUP is actually one of the functions in SQL.
-> duble quotes indicate that you are referring to the column name "group", rather than the function itself.

*ILIKE : not case-sensitive and let you find everything that contains the same letters

SELECT*
  FROM table1
  WHERE "group" ILIKE 'snoop%'

SELECT *
  FROM table1
  WHERE artist ILIKE 'dr_ _ ke'

_ (a single underscore) : substitute for an individual character

*IN : specify a list of values that you'd like to include in the results

SELECT *
  FROM table1
  WHERE year_rank IN (1,2,3)

SELECT *
  FROM table1
  WHERE artist IN ('Taylor swift', 'Usher', 'EXO')

* BETWEEN : Select only rows that are within a specific range

SELECT *
  FROM table1
  WHERE year_rank BETWEEN 5 AND 10

----above query is the same as below ---

SELECT *
  FROM table1
  WHERE year_rank>=5 AND year_rank <=10

* IS NULL : allows you to exclude rows with missing data from your results

SELECT *
  FROM table1
  WHERE artist IS null

! important ! : WHERE artist = NULL does not work - you can't perform arithmetic on null values

* AND : select only rows that satisfy two conditions

SELECT *
  FROM table1
  WHERE year=2012 AND year_rank<=10

SELECT *
  FROM table1
  WHERE itemno = '102348'
  AND "group" ILIKE '%fear%'

*OR : select rows that satisfy either of two conditions
SELECT *
  FROM table1
  WHERE year_rank=5 OR artist='Gotye'

*NOT : select items NOT satisfying the given condition

SELECT *
 FROM table1
 WHERE year = 2013
 AND year_rank NOT BETWEEN 2 AND 3

* ORDER BY : reorder your results based in one or more columns

SELECT *
  FROM table1
ORDER BY artist
--> order artist names in A-Z( asc by default)

SELECT *
  FROM table1
  WHERE year = 2013
  ORDER BY year_rank

SELECT *
  FROM table1
  WHERE year = 2013
  ORDER BY year_rank DESC

* ORDER BY for multiple columns

SELECT *
 FROM table1
 WHERE year = 2010
 ORDER BY year_rank, artist ASC

* COMMENT IN OUT using '--' for one sentence ; /* this is comment */ for multiple lines