Learn SQL With Rails 4 - Intermediate Queries

Question Click to View Answer

Create a rails application called intermediate_queries.

 
$ rails new intermediate_queries

Add 'faker' to the Gemfile and install it.

 
# Gemfile
gem 'faker'

# command line
$ bundle install

Create a user model with email, first_name, random_number, and role attributes. Create the table in the database.

 
$ rails g model User email first_name random_number:integer role
$ rake db:migrate

Seed the database with the following script:

# db/seeds.rb
1000.times do
  User.create(
    email: Faker::Internet.email,
    first_name: Faker::Name.first_name,
    random_number: rand(1000),
    role: ['admin', 'author'].sample)
end
User.create(
  email: "bob@fake.com",
  first_name: "bob",
  random_number: rand(1000),
  role: "admin"
)

$ rake db:seed

What SQL is generated by the following command:

User.take

SELECT "users".* FROM "users" LIMIT 1

Write an ActiveRecord query and the associated SQL to locate a user in the database with the first_name "bob".

 
User.find_by(first_name: "bob")
# OR
User.where(first_name: "bob").take

SELECT "users".* FROM "users" WHERE "users"."first_name" = 'bob' LIMIT 1

Write the SQL for the following ActiveRecord query:

User.find([2, 99])

SELECT "users".* FROM "users"  WHERE "users"."id" IN (2, 99)

Write the ActiveRecord and SQL queries to fetch all users in the database that are admins.

 
User.where("role = ?", "admin")
# OR
User.where(role: "admin")

SELECT "users".* FROM "users"  WHERE "users"."role" = 'admin';

Write the ActiveRecord and SQL queries to fetch all users in the database that are authors and have an id less than 100.

 
User.where("role = ? AND id < ?", "author", 100)

SELECT "users".* FROM "users"  WHERE (role = 'author' AND id < 100)

Write ActiveRecord and SQL queries to fetch all users in the database with ids from 450 and 550.

 
User.where(id: (450..550))

SELECT "users".* FROM "users"  WHERE ("users"."id" BETWEEN 450 AND 550)

Write ActiveRecord and SQL queries to fetch all users with roles other than 'admin'.

 
User.where.not(role: "admin")

SELECT "users".* FROM "users"  WHERE ("users"."role" != 'admin')

Write ActiveRecord and SQL queries to sort all users starting with the most recently updated users.

 
User.order("updated_at DESC")

SELECT "users".* FROM "users" ORDER BY updated_at DESC

Write ActiveRecord and SQL queries to return all admin users sorted starting with the users that have the highest random_number.

 
User.where(role: "admin").order("random_number DESC")

SELECT "users".* FROM "users" WHERE "users"."role" = 'admin' ORDER BY random_number DESC

Write ActiveRecord and SQL queries that return the number of different roles that are in the users table.

 
User.select(:role).distinct.count

SELECT DISTINCT COUNT(DISTINCT "users"."role") FROM "users"

Write a User.per_page method that takes the number of records to display (n) and the page_number as arguments. Write the SQL query that is generated to show records 11 through 20 on the second page.

 
class User < ActiveRecord::Base
  def self.per_page(n, page_number)
    limit(n).offset((page_number - 1) * n)
  end
end

SELECT  "users".* FROM "users"  LIMIT 10 OFFSET 10

Show the ActiveRecord and SQL queries to generate a hash that summarizes how many users in the table have the role set to "admin" and how many have the role set to "author".

 
User.group("role").count

SELECT COUNT(*) AS count_all, role AS role FROM "users" GROUP BY role