###Setup:
- Clone Repo:
git clone git@github.com:turingschool-examples/storedom.git
bundle update
- Change database from sqlite to Postgres (requires changes to gemfile and database.yml). Bundle again.
This is what I put in my database.yml file:
development:
adapter: postgresql
encoding: utf8
database: sql_query_development
pool: 5
username:
password:
test: &TEST
adapter: postgresql
encoding: utf8
database: sql_query_test
pool: 5
username:
password:
production:
adapter: postgresql
encoding: utf8
database: sql_query_production
pool: 5
username:
password:
- Change the seed file to have random created_at timestamps for date related queries. Users and Items should have a created_at date between 200 and 1000 days ago. Orders between today and 200 days ago.
created_at: rand(200..1000).days.ago
4. Set up the database:
rake db:setup
From here on out, you will be working directly in the postgres database. Type: psql <your-development-database-name> to access your database. Your database name will be in your database.yml file.
After I did these exercises in Postgres, I went back and tried to do most of them in ActiveRecord as well. Where I did this, I also put my ActiveRecord Solutions below.
###Exercises:
- Add column expiration_date to items as a timestamp. Do not allow null values. Set a default value to one year after the date the item was created.
PG
ALTER TABLE items
ADD COLUMN expiration_date timestamp NOT NULL default CURRENT_TIMESTAMP + '1 year';
AR
def change
add_column :items, :expiration_date, :datetime
execute(“
ALTER TABLE items
ALTER COLUMN expiration_date
SET DEFAULT CURRENT_TIMESTAMP + ‘1 year’
“)
end
Note that the null constraint is not added with the above migration. It needs to come later, after data is populated into that column.
- Fix your existing records to have an expiration date that is 1 year after their created_at date.
PG
UPDATE items
set expiration_date = (created_at + '1 year');
AR
Item.update_all(“expiration_date = created_at + ‘1 year’”)
Now the migration to add the null constraint to this column:
def change
change_column :items, :expiration_date, :datetime, null: false
end
- Change expiration date field to be unix time rather than a timestamp. Do not delete/drop any records, tables, etc. Do not use created_at date to recalculate. Work with your current expiration date column and find a way to transition that data to the new format.
PG
ALTER TABLE items
RENAME COLUMN expiration_date to expiration_date_old;
ALTER TABLE items
ADD COLUMN expiration_date integer NOT NULL
default EXTRACT(epoch from (CURRENT_TIMESTAMP + '1 year'));
UPDATE items
SET expiration_date =
EXTRACT(epoch from expiration_date_old);
ALTER TABLE items
DROP COLUMN expiration_date_old;
AR
def change
rename_column :items, :expiration_date, :expiration_date_old
add_column :items, :expiration_date, :integer
execute(“
ALTER TABLE items
ALTER COLUMN expiration_date
SET DEFAULT EXTRACT(epoch from (CURRENT_TIMESTAMP + ‘1 year’))”)
end
Item.update_all(“expiration_date = extract(epoch from expiration_date_old)”)
def change
change_column :items, :expiration_date, :integer, null: false
remove_column :items, :expiration_date_old
end
- The items table currently has a cost field, but there are no values in any of the records. Populate this field for all records with a random value that reprsents a cost between $0.00 and $10.00. Note the data type.
PG
UPDATE items
set cost =
floor(random() * 1000);
AR
Item.update_all(cost: rand(0..1000))
- Use the cost data you just created to populate the amount field in orders. The amount in orders should be the sum of the costs of the order_items in that order.
PG
UPDATE orders o
SET amount = (select sum(cost) as order_amount from items i
inner join order_items oi on i.id = oi.item_id
inner join orders ord on oi.order_id = ord.id
where ord.id = o.id
GROUP BY o.id);
- Create a new table called categories with an id field as the primary key and a name field that is 40 characters or less and cannot be null.
PG
CREATE TABLE categories
(id serial primary key,
name varchar(40) NOT NULL);
- Add some constraints on the category table. First, make sure the name is unique. Next, make sure the name only consists of upper or lower case letters.
PG
ALTER TABLE categories
ADD CONSTRAINT "unique_name" UNIQUE("name");
ALTER TABLE categories
ADD CONSTRAINT "alpha_name"
CHECK (name ~* '^[a-zA-Z]+$');
-
Add a category with a name of "MISC". Try adding a few other categories that violate your constraints to make sure they are working as you intended.
-
Add category_id as a foreign key on the items table. Do not allow null values and default the value to be the MISC category you just made.
PG
ALTER TABLE items
ADD COLUMN category_id integer NOT NULL default 1;
ALTER TABLE items
ADD CONSTRAINT cat_fk FOREIGN KEY (category_id)
REFERENCES categories (id) MATCH FULL;
- Add another category. Then change items with IDs between 10 and 50 to have that category id.
PG
UPDATE items
SET category_id = 2
where id BETWEEN 10 AND 50;
- Add another category. Then change items with names that begin with 'A' to have that category id.
PG
UPDATE items
set category_id = 3
where name LIKE 'A%';
- Find all orders that were placed on a Tuesday.
PG
SELECT * from orders
WHERE extract(ISODOW from created_at) = 2;
AR
Order.where('extract(ISODOW from created_at) = 2’)
- Find all customers who placed orders on a Tuesday.
PG
SELECT * from users u
INNER JOIN orders o on o.user_id = u.id
WHERE extract(ISODOW from o.created_at) = 2;
- Find all customers who placed orders on Tuesday where the order amount was greater than $50. Display the user's name and the order amount in a currency format like $50.00.
PG
SELECT u.name, '$'||round(o.amount/100.0, 2) as dollars from users u
INNER JOIN orders o on o.user_id = u.id
WHERE extract(ISODOW from o.created_at) = 2 AND o.amount > 5000;
AR
User.joins(:orders)
.where(‘extract(ISODOW from orders.created_at)
= 2’)
.where(‘orders.amount > 5000’)
.pluck(“users.name, ‘$’||round(orders.amount/100.0, 2)”)
- Find all items that have not been ordered.
PG
SELECT i.id as item_id, i.name from items i
LEFT OUTER JOIN order_items oi on i.id = oi.item_id
WHERE oi.item_id IS NULL;
AR
Item.joins(‘LEFT OUTER JOIN “order_items” ON “order_items”.”item_id”
= “items”.”id”’)
.where(order_items: {item_id: nil}
- Get a list of users who created an account between Jan 1 2014 and Jan 1 2015
PG
SELECT * from users
WHERE created_at BETWEEN '2014-01-01 00:00:00' AND '2015-01-01 00:00:00';
AR
User.where(created_at: '2014-01-01 00:00:00'..'2015-01-01 00:08:00')
- Find the earliest date when a user created an account.
PG
SELECT min(created_at) from users;
AR
User.minimum(:created_at)
- Find the name of the first user to create an account and the last user to create an account
PG
SELECT name from users
ORDER BY created_at limit(1);
SELECT name from users
ORDER BY created_at DESC limit(1);
AR
User.select(:name).order(:created_at).limit(1)
User.select(:name).order(created_at: :desc).limit(1)
- Find the id and name of the first user to place an order with an item from the category "MISC"
PG
SELECT u.id, u.name from users u
INNER JOIN orders o on o.user_id = u.id
INNER JOIN order_items oi on o.id = oi.order_id
INNER JOIN items i on i.id = oi.item_id
INNER JOIN categories c on c.id = i.category_id
WHERE c.name = 'MISC'
ORDER BY o.created_at
LIMIT(1);
AR
User.joins(orders: [order_items: [item: [:category]]])
.where(categories: {name: 'MISC'})
.order('orders.created_at')
.limit(1)
- Find the total dollar amount of all of user 1's orders. Display the total in currency format like $100.00.
PG
SELECT '$'||round(sum(amount/100.0), 2) from orders
WHERE user_id = 1;
AR
"$" + (User.joins(:orders).where(users: {id: 1}).sum('orders.amount')/100.0).to_s
I didn't figure out a better way to do that currency formatting within the query itself.
- Display the id, name, and total order amount (in currency format) for all users, ordered by highest total order amount to lowest.
PG
SELECT u.id, u.name, '$'||round(sum(o.amount/100.0), 2) as total from orders o
INNER JOIN users u on u.id = o.user_id
GROUP BY u.id
ORDER BY sum(o.amount) DESC;
AR
User.joins(:orders)
.order('sum_orders_amount DESC')
.group('users.id')
.sum('orders.amount')
I didn't get the currency format to work in ActiveRecord on this one.
- Display the item id, item name, and expiration date (as a timestamp) for all items that are expiring within the next 30 days. Order from nearest expiration date.
PG
SELECT id, name, TIMESTAMP WITH TIME ZONE 'epoch' + expiration_date * INTERVAL '1 second'
as exp_timestamp from items
WHERE (TIMESTAMP WITH TIME ZONE 'epoch' + expiration_date * INTERVAL '1 second')
BETWEEN now() AND (now() + '30 days')
ORDER BY exp_timestamp;
- Do the same query as above, but display expiration date as days until expiration instead of a timestamp.
PG
SELECT id, name, floor((expiration_date - extract('epoch' from now()))/24/60/60)
as days_to_expiration from items
WHERE (TIMESTAMP WITH TIME ZONE 'epoch' + expiration_date * INTERVAL '1 second')
BETWEEN now() AND (now() + '30 days')
ORDER BY days_to_expiration;
AR
Item.where(expiration_date:
Time.now.to_i..(Time.now.to_i + 30*24*60*60))
.order(:expiration_date)
.pluck(:id,
:name,
“floor((expiration_date — extract(‘epoch’ from
now()))/24/60/60) as expiration”)