Le Wagon | SQL cheatsheet

A. SQL Cheatsheet.md

SQL Cheatsheet

sql_cheat_sheet.rb

# A bit of context:

# SQL is THE language for making queries to the Database (DB)

# *For Food Delivery*
# - We had a fake DB with a Repository coupled to a CSV file
# - We had a `load` mechanism that ran at the launch of the app to load our data into `@elements`
# - The Controller would query the Repo to make CRUD on the `@elements` array (eg: `@repo.add(recipe)`)
# - Then every modification of the `@elements` array was passed on to the CSV thanks to the `save` mechanism coded in the Repo.

# *SQL*
# - The *CSV has disappeared* and is replaced by a DB
# - The *Repository has disappeared* and it's now our *Model that will connect directly to the DB*
# - From the Controller, to retrieve the list of all recipes, for example, we will write: `Recipe.all`
#   and our Model will be responsible for generating the SQL which queries the DB, and to return instances of `Recipe`

# Some helpful SQL commands / info :
# - id of the last row/record we inserted in the database : `DB.last_insert_row_id`
# - Hash instead of Arrays when we READ from the database : `DB.results_as_hash = true`

#DB RECAP#

#-------------------#

## SUMMARY ##

# ## SQL ##
  # TEST DB IN TERMINAL
  # SQL IN IRB
  # SQL IN RUBY
  #


#-------------------#

## SQL ##

# TEST DB IN TERMINAL

# Create a new folder, and go into it.
# Create a DB and start typing SQL queries:
$ sqlite3 db.sqlite

# Example:
$ sqlite3 jukebox.sqlite

# See column and headers
$ sqlite .mode column
$ sqlite .headers on

# See schema
$ sqlite3 .schema

#---------------#

# SQL IN IRB

require "sqlite3"
db_file_path = File.join(File.dirname(__FILE__), "db_file_path")
DB = SQLite3::Database.new(db_file_path)
rows = DB.execute("SELECT QUERY")


#---------------#

# SQL IN RUBY


# IN THE MVC MODEL

# tables = models
# columns = fields = instance variables
# rows = record = new object


require 'sqlite3'

# Returns the list of tracks with their album and artist.
def detailed_tracks(db)
  results = db.execute("SELECT tracks.name, albums.title,
    artists.name FROM tracks JOIN albums ON tracks.album_id = albums.id
    JOIN artists ON artists.id = albums.artist_id")
end

# For the given category of music, returns the number of tracks and the average song length (as a stats hash)
def stats_on(db, genre_name)
  results = db.execute("SELECT genres.name, COUNT(tracks.name), ROUND(AVG(milliseconds) / 60000, 2)
    FROM tracks JOIN genres ON genres.id = genre_id
    WHERE genres.name = '#{genre_name}'").flatten
  { category: genre_name, number_of_songs: results[1], avg_length: results[2] }
end

# Returns list of top 5 artists with the most songs for a given genre.
def top_five_artists(db, genre_name)
  results = db.execute("SELECT artists.name, COUNT(tracks.name) AS track_count
    FROM tracks JOIN albums ON albums.id = tracks.album_id
    JOIN artists ON artists.id = albums.artist_id
    JOIN genres ON genres.id = tracks.genre_id
    WHERE genres.name = '#{genre_name}'
    GROUP BY artists.name
    ORDER BY track_count DESC
    LIMIT 5
    ")
end

# Shorten the calls
# example : Find the first 3 artists with the letter `Z` in their name.
query = <<-SQL
  SELECT * FROM artists
  WHERE name LIKE "%Z%"
  ORDER BY name
  LIMIT 3
SQL
rows = db.execute(query)

# Tracks name and composer from the Classical playlist
<<-SQL
  SELECT tracks.name, tracks.composer FROM tracks
  JOIN playlist_tracks on playlist_tracks.track_id = tracks.id
  JOIN playlists on playlists.id = playlist_tracks.playlist_id
  WHERE playlists.name = "Classical"
SQL

# Artists with 10 mostly playlisted tracks
<<-SQL
  SELECT a.name, COUNT(*) AS c
  FROM playlist_tracks pt
  JOIN tracks t ON t.id = pt.track_id
  JOIN albums b ON b.id = t.album_id
  JOIN artists a ON a.id = b.artist_id
  GROUP BY a.id
  ORDER BY c DESC
  LIMIT 10
SQL

# Tracks purchased more than twice
<<-SQL
  SELECT tracks.name, COUNT(*) AS c
  FROM invoice_lines
  JOIN tracks ON invoice_lines.track_id=tracks.id
  GROUP BY tracks.name
  HAVING c >= 2
  ORDER BY c DESC
SQL