A sample SQL database project that contains 3 sample queries in the queries.py file, written in Python.
To connect to the database, make sure you have psql (https://launchschool.com/blog/how-to-install-postgresql-on-a-mac)
installed on your terminal as a command. Then, unzip the newsdata.zip to get a newsdata.sql file, and move that
file to your directory. cd into the directory, and run the following commands:
psql-d news-f newsdata.sql
This connects to the installed database, creates the tables from the file, and populates
them with data. From there, you can run many psql commands (https://www.postgresql.org/docs/8.4/static/tutorial-select.html)
to query the data, or to explore the relations and their respective columns of data. The following relations contained in the news database are:
logauthorsarticles
psql commands to explore the database:
\d [insert relation name here]to fetch the columns contained in the relation, and their respective datatypes\dtto fetch all the relations in the database\dt+to fetch all the relations in the database with disk storage informationselect * from [insert relation name here]explores all the populated data as tuples in a relation. NOTE: Some relations take a while to query all the data, especiallylog. This is okay as it takes time to load a large amount of populated tuples!
To run this application, change directory into this application via bash and run the command: "python queries.py" from bash. You are then given 3 different queries to choose from:
- Find the top 3 articles of all time based on views.
- Find the top authors of all time on an aggregation count of all article views by the same actor.
- Find the day(s) where the number of errors a website encountered is greater than 1%.
- Exit the program.
For the results on those queries, kindly refer to the results.txt file.
I hope you have a kick ass time with psql!
Special thanks to the Udacity team for fostering incredible education and passion into the hearts of their students.