Sequel Gem

Question 1

Sequel Gem

what is sequel

Answer 1

SQL database access toolkit for Ruby

Question 2

Sequel Gem

what is a Dataset

Answer 2

a dataset object encapsulates an SQL query;

it supports chainability;

retrieves records only when needed;

accessed using Enumerable interface

ex

DB[:countries].filter(region: ‘Middle East’).avg(:GDP)

equivalent to

SELECT avg(GDP) FROM countries WHERE region = ‘Middle East’;

Question 3

Sequel Gem

how to connect to a database

Answer 3

Sequel.connect(‘url’)

supports other options

ex

DB = Sequel.connect(“postgres://user:password@host:port/database_name”, :max_connections => 10, :logger => Logger.new(‘log/db.log’))

Question 4

Sequel Gem

naming convention for instance of a database connection

Answer 4

DB

Question 5

Sequel Gem

execute raw sql statements in Sequel

Answer 5

DB.run(“create table t (a text, b text)”)

DB.run(“insert into t values (‘a’, ‘b’)”)

Question 6

Sequel Gem

create a dataset with raw sql

Answer 6

dataset = DB[‘select id from items’]

dataset. count
dataset. map(:id)

Question 7

Sequel Gem

fetch records with a dataset created with raw sql

Answer 7

DB[‘select * from items’].each { |row| p row }

Question 8

Sequel Gem

use placeholders in raw sql statements

Answer 8

name = ‘Jim’

DB[‘select * from items where name = ?’, name].each { |row| p row }

Question 9

Sequel Gem

the primary way records are retrieved and manipulated

Answer 9

datasets

Question 10

Sequel Gem

two primary ways datasets are created

Answer 10

Database#from or Database#[]

ex

posts = DB.from(:posts)

posts = DB[:posts] # same

Question 11

Sequel Gem

characteristic of datasets that allows you to manipulate them after they are created: to filter records, change ordering, join tables, etc

Answer 11

datasets only fetch records when you tell them to

Question 12

Sequel Gem

method that retrieves all records from a dataset

Answer 12

Dataset#all

returns an array of hashes, where each hash corresponds to a record

if a block is given, yields all objects after loading them

ex

posts.all

SELECT * FROM posts

Question 13

Sequel Gem

iterate through a dataset one record at a time

Answer 13

Dataset#each

ex

posts.each { |row| p row }

Question 14

Sequel Gem

retrieve the first or last record from a dataset

Answer 14

Dataset#first

ex

posts.first

ex

posts.order(:stamp).last

SELECT * FROM posts ORDER BY stamp DESC LIMIT 1

Question 15

Sequel Gem

return an array of one or more column values from a dataset

Answer 15

Dataset#map

ex

DB[:table].map(:id)

=> [1, 2, 3, …]

Question 16

Sequel Gem

retrieve a single record with a specific value from a dataset

Answer 16

Dataset#[]

ex

posts[:id => 1]

SELECT * FROM posts WHERE id = 1 LIMIT 1

Question 17

Sequel Gem

method that allows you to filter records in a dataset

Answer 17

Dataset#where

Question 18

Sequel Gem

use Dataset#where to filter based on a hash of values

Answer 18

my_posts = posts.where(:category => ‘ruby’, :author => ‘david’)

WHERE category = ‘ruby’ AND author = ‘david’

Question 19

Sequel Gem

use Dataset#where to filter based on a range

Answer 19

my_posts = posts.where(:stamp => (Date.today - 14)..(Date.today - 7))

WHERE stamp >= ‘2010-06-30’ AND stamp

Question 20

Sequel Gem

use Dataset#where to filter based on an array of values

Answer 20

my_posts = posts.where(:category => [‘ruby’, ‘postgres’, ‘linux’])

WHERE category IN (‘ruby’, ‘postgres’, ‘linux’)

Question 21

Sequel Gem

method that allows you to use an inverse filter on a dataset; i.e. opposite of Dataset#where

Answer 21

Dataset#exclude

ex

my_posts = posts.exclude(:category => [‘ruby’, ‘postgres’, ‘linux’])

WHERE category NOT IN (‘ruby’, ‘postgres’, ‘linux’)

Question 22

Sequel Gem

use a custom where clause with Dataset#where

Answer 22

posts.where(‘stamp IS NOT NULL’)

WHERE stamp IS NOT NULL

Question 23

Sequel Gem

return the number of records in a dataset

Answer 23

Dataset#count

ex

posts.where(Sequel.like(:category, ‘%ruby%’)).count

SELECT COUNT(*) FROM posts WHERE category LIKE ‘%ruby%’

Question 24

Sequel Gem

query maximum/minimum values in a dataset

Answer 24

Dataset#max, Dataset#min

ex

max = DB[:history].max(:value)

SELECT max(value) FROM history

min = DB[:history].min(:value)

SELECT min(value) FROM history

Question 25

# Sequel Gem calculate a sum or average from a dataset

Answer 25

Dataset#sum ex sum = DB[:items].sum(:price) SELECT sum(price) FROM items avg = DB[:items].avg(:price) SELECT avg(price) FROM items

Question 26

# Sequel Gem order the records in a dataset

Answer 26

Dataset#order ex posts.order(:stamp) ORDER BY stamp posts.order(:stamp, :name) ORDER BY stamp, name

Question 27

# Sequel Gem methods used to chain additional order by clauses on a dataset

Answer 27

Dataset#order\_append, Dataset#order\_prepend ex posts.order(:stamp).order\_append(:name) ORDER BY stamp, name posts.order(:stamp).order\_prepend(:name) ORDER BY name, stamp

Question 28

# Sequel Gem specify descending order in an order by clause

Answer 28

Dataset#reverse\_order ex posts.reverse\_order(:stamp) ORDER BY stamp DESC

Question 29

# Sequel Gem select specific columns to be returned from a dataset

Answer 29

Dataset#select ex posts.select(:stamp) SELECT stamp FROM posts posts.select(:stamp, :name) SELECT stamp, name FROM posts

Question 30

# Sequel Gem methods used to chain additional select statements on a dataset

Answer 30

Dataset#select\_append ex posts.select(:stamp).select\_append(:name) SELECT stamp, name FROM posts

Question 31

# Sequel Gem delete rows from a table

Answer 31

Dataset#delete posts.where('stamp DELETE FROM posts WHERE stamp call WHERE before DELETE

Question 32

# Sequel Gem insert records into a table

Answer 32

Dataset#insert ex posts.insert(:category =\> 'ruby', :author =\> 'david') INSERT INTO posts (category, author) VALUES ('ruby', 'david')

Question 33

# Sequel Gem update records in a dataset

Answer 33

Dataset#update ex posts.where('stamp 'archived') UPDATE posts SET state = 'archived' WHERE stamp

Question 34

# Sequel Gem update records in a dataset with a value from another column

Answer 34

posts.where{|o| o.stamp Sequel.+(:backup\_number, 1)) UPDATE posts SET backup\_number = backup\_number + 1 WHERE stamp

Question 35

# Sequel Gem method that wraps code in a database transaction

Answer 35

Database#transaction ex DB.transaction do posts. insert(:category =\> 'ruby', :author =\> 'david') posts. where('stamp 'archived') end

Question 36

# Sequel Gem method that joins two tables

Answer 36

Dataset#join ex order\_items = DB[:items].join(:order\_items, :item\_id =\> :id). where(:order\_id =\> 1234) SELECT \* FROM items INNER JOIN order\_items ON order\_items.item\_id = items.id WHERE order\_id = 1234 !! item\_id is automatically qualified with the table being joined, and id is automatically qualified with the last table joined.

Question 37

# Sequel Gem execute a select statement manually with a string

Answer 37

Database#fetch ex DB.fetch("SELECT \* FROM albums") do |row| puts row[:name] end

Question 38

# Sequel Gem use a named placeholder with Database#fetch

Answer 38

DB.fetch("SELECT \* FROM albums WHERE name LIKE :pattern", :pattern=\>'A%') do |row| puts row[:name] end

Question 39

# Sequel Gem what Database#fetch returns when a block is not provided

Answer 39

Dataset ex ds = DB.fetch("SELECT \* FROM albums")

Question 40

# Sequel Gem how to insert, update or delete using SQL

Answer 40

first create the dataset, then execute: ds. insert ds. update ds. delete

Question 41

# Sequel Gem return the SQL that will be used for a given expression

Answer 41

Database#literal ex DB.literal(1) # =\> "1" DB.literal(:column) # =\> "\"column\"" DB.literal('string') # =\> "'string'"

Question 42

# Sequel Gem create a log file

Answer 42

DB.loggers

Question 43

# Sequel Gem how identifies are usually specified in Sequel gem

Answer 43

symbols ex :column # "column"

Question 44

# Sequel Gem syntax to qualifiy a column reference that exists in two different tables

Answer 44

double underscore ex :table\_\_column # "table"."column"

Question 45

# Sequel Gem method that qualifiies a column reference that exists in two different tables

Answer 45

Sequel.qualify ex Sequel.qualify(:table, :column) # "table"."column"

Question 46

# Sequel Gem syntax to alias a column symbol

Answer 46

triple underscore ex :column\_\_\_alias # "column" AS "alias" ex with qualification :table\_\_column\_\_\_alias # "table"."column" AS "alias"

Question 47

# Sequel Gem method that creates an alias

Answer 47

Sequel.as ex Sequel.as(:column, :alias) # "column" AS "alias" Sequel.qualify(:table, :column).as(:alias) # "table"."column" AS "alias"

Question 48

# Sequel Gem syntax to use SQL functions

Answer 48

virtual row ex DB[:albums].select{func.function} # SELECT func() FROM "albums" DB[:albums].select{func(col1, col2)} # SELECT func("col1", "col2") FROM "albums"

Question 49

# Sequel Gem method that allows you to use a SQL function

Answer 49

Sequel.function ex Sequel.function(:func) # func() Sequel.function(:func, :col1, :col2) # func("col1", "col2")

Question 50

# Sequel Gem method that allows you to use SQL aggregate functions

Answer 50

Sequel.function ex Sequel.function(:sum, :column) # sum(column)

Question 51

# Sequel Gem method that adds the 'distinct' modifier to aggregate functions

Answer 51

#distinct ex DB[:albums].select{sum(:column).distinct} # SELECT sum(DISTINCT column) FROM albums

Question 52

# Sequel Gem method that allows you to use the wildcard as the sole argument of the aggregate function

Answer 52

#\* ex Sequel.function(:count).\* # count(\*) DB[:albums].select{count.function.\*} # SELECT count(\*) FROM albums

Question 53

# Sequel Gem equality operator

Answer 53

sequel uses hashes to specify equality ex {:column=\>1} # ("column" = 1)

Question 54

# Sequel Gem not equal operator

Answer 54

Sequel.negate or Sequel.~ ex Sequel.negate(:column =\> 1) # ("column" != 1) Sequel.~(:column =\> 1) # ("column" != 1)

Question 55

# Sequel Gem difference between Sequel.negate and Sequel.~

Answer 55

Sequel.negate negates all entries Sequel.~ does a general inversion ex Sequel.negate(:column =\> 1, :foo =\> 2) # (("column" != 1) AND (foo != 2)) Sequel.~(:column =\> 1, :foo =\> 2) # (("column" != 1) OR (foo != 2))

Question 56

# Sequel Gem method that uses not equal operator in where clause

Answer 56

Dataset#exclude ex DB[:albums].exclude(:column=\>1) # SELECT \* FROM "albums" WHERE ("column" != 1)

Question 57

# Sequel Gem use inclusion and exclusion operators

Answer 57

inclusion and exclusion work the same way as equality and inequality. Sequel swithces from = or != to IN or NOT IN when the hash value is an array or a dataset ex {:column=\>[1, 2, 3]} # ("column" IN (1, 2, 3)) Sequel.~(:column=\>[1, 2, 3]) # ("column" NOT IN (1, 2, 3))

Question 58

# Sequel Gem method that allows use of `EXISTS` operator

Answer 58

#exists ex DB[:albums].exists # EXISTS (SELECT \* FROM albums)

Question 59

# Sequel Gem use identity operators (IS and IS NOT)

Answer 59

identity operators work the same way as equality does ex {:column=\>nil} # ("column" IS NULL) {:column=\>true} # ("column" IS TRUE) {:column=\>false} # ("column" IS FALSE) Sequel.~(:column=\>nil) # ("column" IS NOT NULL) Sequel.~(:column=\>true) # ("column" IS NOT TRUE) Sequel.~(:column=\>false) # ("column" IS NOT FALSE)

Question 60

# Sequel Gem use inversion operator (NOT)

Answer 60

Sequel.~ ex Sequel.~(:column) # NOT "column"

Question 61

# Sequel Gem syntax to use inequality operators ( =)

Answer 61

= directly on expression objects ex Sequel.qualify(:table, :column) \> 1 # ("table"."column" \> 1) Sequel.qualify(:table, :column) Sequel.function(:func) \>= 1 # (func() \>= 1) Sequel.function(:func, :column)

Question 62

# Sequel Gem use inequality operators ( =) on symbols

Answer 62

Sequel.expr ex Sequel.expr(:column) \> 1 # ("column" \> 1)

Question 63

# Sequel Gem syntax to use mathematical operators (+ - \* /)

Answer 63

+ - \* / directly on expression objects ex Sequel.expr(:column) + 1 # "column" + 1 Sequel.expr(:table\_\_column) - 1 # "table"."column" - 1 Sequel.qualify(:table, :column) \* 1 # "table"."column" \* 1 Sequel.expr(:column) / 1 # "column" / 1 Sequel.expr(:column) \*\* 1 # power("column", 1)

Question 64

# Sequel Gem syntax to use boolean operators (AND OR)

Answer 64

& and | methods directly on expression objects ex Sequel.expr(:column1) & :column2 # ("column1" AND "column2") Sequel.expr(:column1=\>1) | {:column2=\>2} # (("column1" = 1) OR ("column2" = 2)) (Sequel.function(:func) \> 1) & :column3 # ((func() \> 1) AND "column3")

Question 65

# Sequel Gem how to cast data types

Answer 65

the cast method directly on expression objects ex Sequel.expr(:name).cast(:text) # CAST("name" AS text) Sequel.expr('1').cast(:integer) # CAST('1' AS integer) Sequel.qualify(:table, :column).cast(:date) # CAST("table"."column" AS date)

Question 66

# Sequel Gem use the LIKE operator

Answer 66

the like and ilike methods directly on expression objects ex Sequel.expr(:name).like('A%') # ("name" LIKE 'A%' ESCAPE '\') Sequel.expr(:name).ilike('A%') # ("name" ILIKE 'A%' ESCAPE '\')

Question 67

# Sequel Gem how to use a regular expression with MySQL or Postgres

Answer 67

by passing a ruby regular expression to like or ilike ex Sequel.like(:name, /^A/) # ("name" ~ '^A') ~Sequel.ilike(:name, /^A/) # ("name" !~\* '^A')

Question 68

# Sequel Gem how to use the SQL CASE statement

Answer 68

Sequel.case(conditions\_hsh, default\_value) # the keys of the hash is WHEN, the values are THEN ex Sequel.case({:column=\>1}, 0) # (CASE WHEN "column" THEN 1 ELSE 0 END) Sequel.case([[column, 1]], 0) # (CASE WHEN "column" THEN 1 ELSE 0 END) Sequel.case({{:column=\>nil}=\>1}, 0) # (CASE WHEN (column IS NULL) THEN 1 ELSE 0 END)

Question 69

# Sequel Gem how datasets are generally created

Answer 69

Database#[] # passing in 1 or more table names ex ds = DB[:albums, :artists] # this dataset is not ready to be manipulated by chaining methods ex ds.select(:id, :name).where(Sequel.like(:name, 'A%')).order(:name) # SELECT id, name FROM albums WHERE (name LIKE 'A%' ESCAPE '\') ORDER BY name

Question 70

# Sequel Gem add additional OR WHERE clause

Answer 70

Dataset#or ex ds.where(:name =\> 'George').or([[:id =\> [2,3]]]) # SELECT \* FROM `people` WHERE ((`name` = 'George') OR (`id` IN (2, 3)))

Question 71

# Sequel Gem method that allows you to use the LIKE operator for string comparison

Answer 71

Dataset#grep(:field, 'match\_string') ex ds.grep(:name, 'G%') # SELECT \* FROM `people` WHERE ((`name` LIKE 'G%'))

Question 72

# Sequel Gem wildcard used with LIKE operator that matches **zero or more** of any character

Answer 72

% ex 'G%' matches George, but does not match SING

Question 73

# Sequel Gem wildcard used with LIKE operator that matches **exactly** **one** of any character

Answer 73

\_ ex 'G\_' matches Go but does not match George

Question 74

# Sequel Gem the main Sequel object that you deal with and how it is instantiated

Answer 74

Sequel::Database ex Sequel.connect('postgres://localhost/dbname') =\> Sequel::Database

Question 75

# Sequel Gem method that can be used to take any object that Sequel handles and literalize the object to an SQL string fragment

Answer 75

Sequel::Database#literal =\> string ex DB.literal(DB[:table]) =\> '(SELECT \* FROM "table")'

Question 76

objects that represent abstract collections of rows in the database

Answer 76

Sequel::Dataset

Question 77

ruby type that represents identifiers (tables, columns, schemas), qualified identifiers (table.column), aliased identifiers (column as alias), and qualified aliased identifiers (table.column as alias)

Answer 77

symbols ex :table # "table" :column # "column" :table\_\_column # "table"."column" :column\_\_\_alias # "column" AS "alias" :table\_\_column\_\_\_alias # "table"."column" AS "alias"

Question 78

used to represent an SQL concept that doesn't map directly to a ruby class

Answer 78

Sequel::SQL::Expression

Question 79

# Sequel Gem create a literal sequel string, copied verbatim into an SQL statement

Answer 79

Sequel::LiteralString ex Sequel.lit("co'de") # co'de ex DB[:albums].select('name') # SELECT 'name' FROM albums DB[:albums].select(Sequel.lit('name')) # SELECT name FROM albums

Question 80

separate subclasses for representing boolean operations such as AND and OR, mathematical operations such as + and -, and string operations such as || and LIKE

Answer 80

Sequel::SQL::ComplexExpression ex Sequel.or(:col1, :col2) # ("col1" OR "col2") Sequel.+(:column, 2) # ("column" + 2)

Question 81

object that represent SQL CASE expressions

Answer 81

Sequel::SQL::CaseExpression ex Sequel.case({2=\>1}, 0, :a) # CASE "a" WHEN 2 THEN 1 ELSE 0 END Sequel.case({{:a=\>2}=\>1}, 0) # CASE WHEN ("a" = 2) THEN 1 ELSE 0 END

Question 82

object that represents CAST expressions in SQL, which does explicit typecasting in the database

Answer 82

Sequel::SQL::Cast ex Sequel.cast(:a, String) # (CAST "a" AS text) Sequel.cast(:a, :int4) # (CAST "a" AS int4)

Question 83

object that represents an evaluation that is delayed until query literalization

Answer 83

Sequel::SQL::DelayedEvaluation ex Sequel.delay{some\_model.updated\_at}

Question 84

object that represents database function calls, which take a function name and any arguments

Answer 84

Sequel::SQL::Function ex Sequel.function(:func, :a, 2) # func("a", 2)

Question 85

object that represents a literal SQL string with placeholders for variables

Answer 85

Sequel::SQL::PlaceholderLiteralString ex Sequel.lit('? = ?', :a, 1) # "a" = 1 Sequel.lit(':b = :v', :b=\>:a, :v=\>1) # "a" = 1 Sequel.lit(['', ' = '], :a, 1) # "a" = 1

Question 86

object that represents ascending or descending sorts, used by the Dataset order methods

Answer 86

Sequel::SQL::OrderedExpression ex Sequel.asc(:a) # "a" ASC Sequel.desc(:a) # "a" DESC Sequel.asc(:a, :nulls=\>:first) # "a" ASC NULLS FIRST Sequel.desc(:a, :nulls=\>:last) # "a" DESC NULLS LAST

Question 87

BasicObject subclass that is the backbone behind the block expression support

Answer 87

Sequel::SQL::VirtualRow ex DB[:table].where{a \< 1} # the block is instance-evaled inside a VirtualRow instance