Learning SQL - Basic

 

 * SQL Select

SELECT *

    FROM tutorial.us_housing_units

 WHERE month = 1

Note: the clauses always need to be in this order: SELECT, FROM, WHERE.

 

 * SQL Logical Operators

LIKE allows you to match similar values, instead of exact values.

IN allows you to specify a list of values you'd like to include.

BETWEEN allows you to select only rows within a certain range.

IS NULL allows you to select rows that contain no data in a given column.

AND allows you to select only rows that satisfy two conditions.

OR allows you to select rows that satisfy either of two conditions.

NOT allows you to select rows that do not match a certain condition.

 

    * SQL LIKE

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE "group" ILIKE'snoop%'

Note: "group" appears in quotations above because GROUP is actually the name of a function in SQL. The double quotes (as opposed to single: ') are a way of indicating that you are referring to the column name "group", not the SQL function. In general, putting double quotes around a word or phrase will indicate that you are referring to that column name.

 

    * SQL IN

Numerical

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE year_rank IN (1, 2, 3)

Text

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE artist IN ('Taylor Swift', 'Usher', 'Ludacris')

 

    * SQL BETWEEN

    * SQL IS NULL

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE artist IS NULL

Note: WHERE artist = NULL will not work—you can't perform arithmetic on null values.

 

    * SQL AND

    * SQL OR

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE year = 2013

   AND ("group"ILIKE'%macklemore%'OR"group"ILIKE'%timberlake%')

Note: brackets

 

    * SQL NOT

Together with Bewteen 

SELECT *

  FROM tutorial.billboard_top_100_year_end

 WHERE year = 2013

   AND year_rank NOT BETWEEN 2 AND 3

 

Together with Like

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE year = 2013

   AND"group"NOT ILIKE'%macklemore%'

Together with Null

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE year = 2013

   AND artist IS NOT NULL

 

    * SQL ORDER BY

SELECT *

 FROM tutorial.billboard_top_100_year_end

WHERE year_rank <= 3

ORDER BY year_rank, year DESC

 

Reference:

[1] Mode, The SQL Tutorial for Data Analysis, viewed 13 February 2021,   <https://mode.com/sql-tutorial/introduction-to-sql/>.