Date¶ ↑

select * from spree_orders where created_at > '19 nov 2013';

[SQLite only supports "...where created_at > '2013-11-19';"]

Functions¶ ↑

select count(*) from spree_addresses where created_at > '1 Dec 2013';

Quotes¶ ↑

String are delimited by single quotes

Double quotes strings are interpreted as object identifiers

Regular identifiers:
  SELECT *
  FROM TableX
  WHERE KeyCol = 124

Delimited identifiers (ie double quotes,"" or square brackets,[]):
  SELECT *
  FROM [TableX]         --Delimiter is optional.
  WHERE [KeyCol] = 124  --Delimiter is optional.

  SELECT *
  FROM [My Table]      --Identifier contains a space and uses a reserved keyword.
  WHERE [order] = 10   --Identifier is a reserved keyword.

Join table¶ ↑

select * from spree_line_items where order_id = (select id from spree_orders where number = '15748490');

• The join condition of an inner join can be written either in the WHERE clause or in the JOIN clause.

Finding missing ID's using 'Left Join'¶ ↑

'Left Join' is form of 'Outer Join'

Null values for T2 columns in every T1 row that fails the ON and WHERE boolean

SELECT r.id as 'r.id', r.id + 1 as FirstAvailableID, r1.id as 'r1.ID'
FROM games r
LEFT JOIN games r1 ON r1.id = r.id + 1
WHERE r1.id IS NULL

Use list from a Join table¶ ↑

• select * from spree_variants where id in (select variant_id from spree_line_items where order_id in (14,15));

• SELECT * FROM results where id between 144 and 150

Find missing IDs (PK) of 'results' table which are not stored in (FK) the 'games' table:

• select id from results where first = 'state' and state is not null and statewithresult is not null and id not in (select result_id from games where result_id in (select id from results where first = 'state' and state is not null and statewithresult is not null))

The Rails equivalent:

resultIDs = Result.where("first = ? and #{args[:first]} is not null and #{args[:second]} is not null",args[:first]).pluck(:id)
gameIDs   = Game.where("player_x = ? and player_o = ? and result_id is not null",player1,player2).pluck(:result_id)
missingIDs = resultIDs - gameIDs

Wildcards¶ ↑

select * from spree_variants where sku like '845-0282%' and deleted_at is null order by sku ASC;

Altering rows¶ ↑

update spree_variants set sku='845-0282' where id=39;

delete FROM results where random is not null and state is null and statewithresult is null;

Misc¶ ↑

select * from spree_line_items where bsc_spec_id is not null;

select * from spree_variants where deleted_at is null order by sku ASC|DESC;

select * from schema_migrations order by version DESC limit 2;