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
|