PostgreSQL Practice Exercises

postgresql_practice_exercises.md

Sample solutions to exercises can be found here: https://gist.github.com/kjs222/5169a292d87d1774832b7160f7ba2b49

###Setup:

1. Clone Repo:

git clone git@github.com:turingschool-examples/storedom.git
bundle update

2. Change database from sqlite to Postgres (requires changes to gemfile and database.yml). Bundle again.

3. 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 ` to access your database. Your database name will be in your database.yml file.

NOTE: After I did these exercises in Postgres, I went back and tried to do most of them in ActiveRecord as well.

###Exercises:

1. 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.

2. Fix your existing records to have an expiration date that is 1 year after their created_at date.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. Add another category. Then change items with IDs between 10 and 50 to have that category id.

11. Add another category. Then change items with names that begin with 'A' to have that category id.

12. Find all orders that were placed on a Tuesday.

13. Find all customers who placed orders on a Tuesday.

14. 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.

15. Find all items that have not been ordered.

16. Get a list of users who created an account between Jan 1 2014 and Jan 1 2015

17. Find the earliest date when a user created an account.

18. Find the name of the first user to create an account and the last user to create an account

19. Find the id and name of the first user to place an order with an item from the category "MISC"

20. Find the total dollar amount of all of user 1's orders. Display the total in currency format like $100.00.

21. Display the id, name, and total order amount (in currency format) for all users, ordered by highest total order amount to lowest.

22. 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.

23. Do the same query as above, but display expiration date as days until expiration instead of a timestamp.